Oracle Notes

Sharing Oracle technology knowledge and experiences.

Archive for the ‘Oracle Architecture’ Category

A Useful Oracle 11g New Feature

Posted by sdefilip on September 18, 2010

Server Result Cache

  • Enables query result to be cached in memory which can be used during future execution of a similar query by bypassing the regular processing thereby returning the results faster.
  • Decreases the wait time for both physical and logical IO by directly fetching the results from the cached memory.
  • Cached result set is completely shareable between the sessions and various statements as long as they share a common execution plan.
  • Server result cache is the new component of SGA that caches results of queries and is managed by automatic memory management.
  • New parameter RESULT_CACHE_MAX_SIZE is used to enable result cache by setting the maximum size of the cache.
  • A new optimizer hint allows use of result cache at the query level.

Query execution without result cache hint.

Query execution without result cache hint - Oracle 11gQuery execution without result cache hint – Oracle 11g

Query execution with result cache hint.

Query execution with result cache hint - Oracle 11gQuery execution with result cache hint – Oracle 11g

Parameters  related to Result Cache

RESULT_CACHE_MAX_RESULT  :  specifies the percentage of RESULT_CACHE_MAX_SIZE that any single result can use
RESULT_CACHE_MAX_SIZE    :  specifies the maximum amount of SGA memory (in bytes) that can be used by the Result Cache.
RESULT_CACHE_REMOTE_EXPIRATION   : specifies the number of minutes that a result using a remote object is allowed to remain valid
RESULT_CACHE_MODE    : specifies when a ResultCache operator is spliced into a query’s execution plan.

How to find result cache information:
V$RESULT_CACHE_STATISTICS
V$RESULT_CACHE_MEMORY
V$RESULT_CACHE_OBJECTS
V$RESULT_CACHE_DEPENDENCY

DBMS_RESULT_CACHE – PL/SQL API for result cache management:
Functions : Status – displays the current status of the result cache.

SELECT DBMS_RESULT_CACHE.status FROM dual;
STATUS
—————————————————–
ENABLED

Flush: remove all objects from the result cache and release memory.

I will cover some more features in next post . Keep reading

Posted in Oracle 11g, Oracle 11g Database Administration, Oracle 11g New Features, Oracle Architecture, Oracle Performance, Tips | Tagged: , , , | Leave a Comment »

About Oracle 11g Clusters

Posted by sdefilip on April 14, 2010

A cluster provides an optional method of storing table data. A cluster is made up of a group of tables that share the same data blocks. The tables are grouped together because they share common columns and are often used together. For example, the emp and dept table share the deptno column. When you cluster the emp and dept tables (see Figure-1), Oracle Database physically stores all rows for each department from both the emp and dept tables in the same data blocks.

Because clusters store related rows of different tables together in the same data blocks, properly used clusters offer two primary benefits:

• Disk I/O is reduced and access time improves for joins of clustered tables.

• The cluster key is the column, or group of columns, that the clustered
tables have in common. You specify the columns of the cluster key when
creating the cluster. You subsequently specify the same columns when
creating every table added to the cluster. Each cluster key value is stored
only once each in the cluster and the cluster index, no matter how many rows
of different tables contain the value.

Therefore, less storage might be required to store related table and index data in a cluster than is necessary in non-clustered table format. For example, in Figure 1, notice how each cluster key (each deptno) is stored just once for many rows that contain the same value in both the emp and dept tables.

After creating a cluster, you can create tables in the cluster. However, before any rows can be inserted into the clustered tables, a cluster index must be created. Using clusters does not affect the creation of additional indexes on the clustered tables; they can be created and dropped as usual.

You should not use clusters for tables that are frequently accessed individually.

Figure 1 Clustered Table Data

Posted in Oracle 11g, Oracle 11g Database Administration, Oracle Architecture, Oracle Performance | Leave a Comment »

Oracle 11g and Automatic Memory Management (AMM)

Posted by sdefilip on November 13, 2009

I have been very busy for the last few months and haven’t posted much on this blog but I thought I would share some notes on recent experiences installing and upgrading to Oracle 11gR1 and 11gR2 on Decipher,LLC projects. I plan to make this a series of postings.

Carrying forward the theme of improving and automating memory management that started with Oracle 9i, Oracle has included Automatic Memory Management (AMM) in 11g.
The purpose of AMM is to provide dynamic management of the PGA and SGA. The method by which this is done is to allocate one large chunk of memory at database configuration using two new initialization parameters: MEMORY_TARGET and MEMORY_MAX_TARGET. From Oracle 11g Documention:

“you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET). The instance then tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA). Because the target memory initialization parameter is dynamic, you can change the target memory size at any time without restarting the database. The maximum memory size serves as an upper limit so that you cannot accidentally set the target memory size too high, and so that enough memory is set aside for the Oracle Database instance in case you do want to increase total instance memory in the future. Because certain SGA components either cannot easily shrink or must remain at a minimum size, the instance also prevents you from setting the target memory size too low.”

Before you configure your Oracle 11g instance, you should first make sure that you have enough shared memory on your server. On a Linux server, you do this by issuing:

# df -k /dev/shm
which will return the number of blocks in your shared memory filesystem, the number used, the usage percent, and the device on which it is mounted (/dev/shm).

# umount tmpfs
# mount -t tmpfs shmfs -o size=1200m /dev/shm
will adjust the shared memory filesystem size to that required size.

To make this change permanent you need to add the adjustment to the fstab file. To do this just edit the file /etc/fstab and add the line:

none /dev/shm tmpfs size=3000m 0 0
in this case, we set the size to 3GB

The next step is to optimally set your MEMORY_TARGET and MEMORY_MAX_TARGET initialization parameters.

The view V$MEMORY_TARGET_ADVICE provides tuning advice for the MEMORY_TARGET initialization parameter:

SQL> select * from v$memory_target_advice order by memory_size;

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
———– —————— ———— ——————- ———-
180 .5 458 1.344 0
270 .75 367 1.0761 0
360 1 341 1 0
450 1.25 335 .9817 0
540 1.5 335 .9817 0
630 1.75 335 .9817 0
720 2 335 .9817 0

While installing Oracle 11g or using DBCA to create a new database, you have the option of using AMM or not.

The main point of this post is to inform you that Oracle 11g will require quite a bit more shared memory if you choose to use the new Automatic Memory Management feature.

Posted in Oracle 11g, Oracle 11g Database Administration, Oracle 11g Installation, Oracle 11g New Features, Oracle Architecture, Oracle Performance | 2 Comments »