Oracle Performance and Tuning (2 of 3)

Tuning the Shared Pool

Tune the library cache and the data dictonary cache

The Shared pool is a special type of buffer. Whereas a buffer is a "dumb" mechanism, simply providing temporary storage data on its way between fast memory and slow disk, a cache is a "smart" mechanism, retaining memory as to whether it has that information, or part of it, so that it can avoid many unnecessary trips to the disk as possible. When an I/O request is made, the cache checks to see whether it already has it ni memory.If it does,it answers the request itself,returning the requested data. This is know as a hit. If it does not, a trip to the disk is warranted. This is known as a miss. For most cache mechanism a 90+ % hit ratio is very good perfromance.Caches are generally managed by the LRU (Least Recently Used) algorithm, which ensures that, at any given time, the MRU (Most Recently used) data is held in cache, and the LRU data is aged out.

When Oracle parses a SQL statement, it allocates an SQL area in the library cache for it by applying a mathematical formula to the alphanumeric text of the SQL statement and using the result to store (and later find) it in the cache . In other words it uses a hash function In order for the statement to be reused by another, the statements must by identical. For example, is an extra space or different case letter is used in a query then the hash function will not be applicable. Another problem with hash functions is the use of literals in the query, it is recommended that the query use a bind variables in order for the hash statement to be reused, a bind variable is normally a host 3GL variable, such as a C integer. Then the value of the variable can take on any specified integer, with the benfit of reusing the function in the library cache. ( In the case of DSS the use of bind variables is limited )

The Shared pool is composed of the library cache and the data dictonary cache

The library cache: Contains all the recently executed SQL statements, such as stored procedures, functions, packages, triggers, and PL/SQL blocks.
The data dictonary cache portion of the shared pool is sized by the parameter SHARED_POOL_SIZE, and it is the ONLY way to indirectly size this cache. The following are the object that held in the SYS and SYSTEM schemes: X$ Tables, V$ views, DBA_ views, User_ views. Sizing, diagnosing and tuning the library cache so that it performs wll should have the side effect of helping the performance of the data dictonary cache because they both coexist in the shared pool. They are not separatly configurable. There are a couple of ways to measure data dictonary cache performance. One is to query the V$rowcache view:

select sum(GETMISSES)/SUM(GETS) "DC_MISS_RATIO"
from v$rowcache;

The other way is to get the datadictonary section of report.txt (UTLBSTAT/UTLESTAT). Compute the sum of all the GET_MISS and divide that by the sum of all the GET_REQS to get a similar DC_MISS_RATIO.

If either of these two methods yield a DC_MISS_RATIO > .15, increase the SHARED_POOL_SIZE (and retest)

Measure the shared pool hit ratio

select gethitratio       
from v$librarycache
where namespace='SQL AREA';

Size the shared pool apropriatly

To improve the performace of the library cache:

Minimize unnecessary parse calls from within applications : Parsing is CPU intensive. Becasue caching and buffering are involved, it is also memory-intensive as a by-product. Cursor opening and closing should be carefully placed in the application to facilitate reuse of the private SQL area for multiple SQL statements. The parameters OPEN_CURSORS in init.ora , might need to be modified in order to allow for the sufficient allocation of cursor space ( private SQL areas). To determine wheter your application might be inefficient in this regard, run SQL TRACE/TKPROF and examine wheter the count column for parse is near the value for Execute (or Fetch). If so, the application is then reparsing for almost every execute (or fetch).
Maximize reuse of those statements that must be parsed : As mentioned,SQL statements must be identical to be reused. Establish some conventions,like "always code SQL statements in uppercase".Furthermore except for DSS apllications, use bind variables when appropriate.
Pin Frequently Used program objects in memory : Is Oracle a cursor, trigger, procedure or package can be held in memory using a special shared pool package DBMS_SHARED_POOL. To create this package you must run the procedure dbmspool.sql script. You might also need to run the scripts prvtpool.sql (check version on platform to see if this script needs to be run) Run the(se) script(s) as SYS.

EXECUTE DBMS_SHARED_POOL.KEEP('<object name>');

To unpin the object:

EXECUTE DBMS_SHARED_POOL.UNKEEP('<object name>');

To determine if the object was pinned

select substr(NAME,1,25), KEPT from v$db_obeject_cache;

Minimize fragmentation in the library cache : Unless your application is guarded against fragementation, you will commonly receive ORA-04031 errors( not enough contiguos free space ). One way to avoid this fragmentation is by pinning frequently used objects to memory. For less frequently used objects you can use the init.ora parameters SHARED_POOL_RESERVED_SIZE. You set aside a shared pool "reverved area" for your large objects. Essentialy you guarentee that your necessary large object will find space. Set the SHAED_POOL_RESERVER_SIZE to what would be the maximum number of bytes of your largest objects simultaneously loaded. To determine the size of a particular object you want to include in the reserved area, use:

select substr(name,1,25) "NAME", SHAREABLE_MEM
from V$db_object_cache
where name='<object name>';

Also determine the size you need to set SHARED_POOL_RESERVED_SIZE use:

select sum(shareable_mem)
from V$db_object_cache
where shareable_mem >= <SHARED_POOL_RESERVED_MIN_ALLOC> ;

In order to do the previous you need to have an idea of what constitutes a "large object". So take the following steps:

Set SHARED_POOL_RESERVED_MIN_ALLOC to your specification
Set SHARED_POOL_RESERVED_SIZE to the output of the last query, plus a fudge factor of 10%.

You can also set CURSOR_SPACE_FOR_TIME to TRUE to prevent SQL areas associated with cursors from aging out of the library cache before they have been closed by a program. NOTE: Do not change the value for CURSOR_SPACE_FOR_TIME to true if: RELOADS in V$LIBRARY always show a 0 value, you are using Oracle forms or SQL*Forms or You use dynamic SQL.

Pin Object in the shared pool

Rather than reserving space in the Shared pool, you may wish to selectively "pin" packages in memory. Pining packages in memory immediatly after starting the database will increase the likelyhood that a large enough section of contiguos free space is avilable in memory. The KEEP procedure in the DBMS_SHARED_POOL package designates the packages to pin in the shared pool.

alter procedure APPOWNER.ADD_CLIENT compile;
execute DBMS_SHARED_POOL.KEEP('APPOWNER.ADD_CLIENT','P');

Pining of packages is more related to application management than application tunning, but it can have a performance impact.

Tune the shared pool reserved space

Rather than using the large pool, you can reserve an area within the shared pool for large objects via the SHARED_POOL_RESERVED_SIZE parameter in init.ora . The "reserve size" is set aside for the shared pool entries of large object (such as large packages).

Describe the User Global Area (UGA) and session memory consideration

Configure the Large Pool

The large pool in Oracle will be used when Oracle requests large contiguos area of memory within the shared pool ( such as during use of the multithreaded server). To create a large pool, set a value (in bytes) for the LARGE_POOL_SIZE parameter in init.ora. BY default this pool is not created. You can specify the minimum allocation size for an object in the large pool via the LARGE_POOL_MIN_ALLOC parameter in init.ora. This parameter defaults to 16KB is obsolete in Oracle 8i.

Tuning the Buffer Cache

Describe how the buffer cache is managed

The single most important tuning change you can make to improve the performance of your Oracle system is to properly set the size of the database buffer cache accordingly. The database buffer cache is the cache structure in the SGA and holds copies of the memory of the Most Recently Used (MRU) Oracle data blocks. The TWO parameters that determine the size are:

DB_BLOCK_SIZE : Which is the size of an Oracle Block. This can range from 2KB (2048 bytes ) to 64 KB (65536 bytes). For performance generally the higher the better. If your database has already been created with a relatively small block size , consider rebuilding it if that is feasible for your application:

Shut down the Instance
Do a full export of your database (if feasible)
Increase de DB_BLOCK_SIZE in your init.ora
Startup the Instance
Reimport the database as SYS.

DB_BLOCK_BUFFERS: Is the number of Oracle blocks to be held in memory. Each buffer equals one block. This should be sufficiently high to yield en efficient hit ratio, but not so high as to cause OS paging. The last thing you want is the SGA being paged in and out of memory by the OS. Paging is Oracle's primary job when it comes to the DB_BLOCK_BUFFERS and you dont want the OS paging underneath Oracle!. It pages them in on demand. Hence, your database buffer cache, along with the shared pool, should fit comfortably in real (available core) memory. You must also be careful to take into consideration non-Oracle application memory requirements, and the OS requirements. The size of the database buffer cache is : DB_BLOCK_BUFFERS X DB_BLOCK_SIZE

The database buffer cache is somewhat of a misnomer, in that the cache is a special kind of buffer. Hence buffer cache is actually redundant, not to mention a little confusing. The real point to put accros is that it caches Oracle blocks It is different from the shared pool in that it caches data and not programs.

Oracle always reads Oracle blocks into the database buffer cache before passing them on to user processes. A user process, or application, always reads from ( and writes to ) the database buffer cache. The following are the steps in the buffer management of an I/O request:

User select data ( request block)
Server looks in database buffer cache for it
If it finds it (through the hash function) in the LRU list, it retunrs it.
It it doesnt find it,it reads in the block from the datafile on the disk and attaches it (using the hash function) to the MRU or LRU end of the LRU list as appropriate.
If the user does not modify it, its finished.
If the user does modify it, DBWR writes de block (dirty buffer) back to its location in datafile on disk.

Index can be accessesed one block at a time. Full table scans can have multiple blocks read with one request. Set the number of blocks (batch size) by setting: DB_FILE_MULTIBLOCK_READ_COUNT = <number of blocks> .

Buffers can be free (clean), dirty, current, or read-consistent (rollback).

A free buffer is one that has yet to be used since instance startup, or one that has been used and is now available.

A dirty buffer is one that has been used, but has not been flushed , or written out by the DBWR on checkpoint.

A current buffer is one used in service of an INSERT,UPDATE, or DELETE. By their very nature, current buffers more often than not become dirty.

Read-consistent buffers serve SELECT statements and rollback. Blocks read in service of full table scans are placed at the LRU end of the LRU buffer chain. However, you can still cache whole tables on the MRU end of the chain.

Calculate and tune the buffer cache hit ratio

Because memory I/O is several magnitudes faster than disk I/O (nano vs. mili ) , you want I/O requests to be satisfied by memory as often as possible. You want block on average to be fetched 90 % of the time from the database buffer cache ( data block buffer cache) versus the datafile. You also want to minimize latch contention. The LRU buffer chain, o list , is locked through latch mechanisms, jsut like those throughout the Oracle kernel and the library cache ( in the shared pool). As with any latch approach, you must have enough because latches ( or spin locks ) contain no queing mechanisms as with semaphores.

One way to calculate the hit ratio is with:

select 1-(P.VALUE/(D.VALUE+C.VALUE)) "CACHE HIT RATIO"
FROM V$SYSSTAT P, V$SYSSTAT C, V$SYSSTAT D
WHERE P.NAME = 'physical reads'
AND D.NAME = 'db block gets'
AND C.NAME = 'consistent gets';

Here "physical reads" is the number of block read from the disk, "db block gets" is the number of blocks read from current copies of blocks in cache, and "consistent gets" is the number of read consistent (rollback) copies of blocks in cache. It is summed up as 1 - (physical reads / logical reads )

Tune the buffer cache hit ratio by adding or removing buffers

If the database buffer cache hit ratio is less than .90, increase the db_block_buffers and rerun the query, or utlbstat.sql/utlestat.sql.

Increasing this parameter requires an instance shutdown and startup, it is not as complicated as changing the db_block_size . There is also another option that simulates the effect of adding more buffers to the database. First the instance needs to shutdown, then the parameter DB_BLOCK_LRU_STATISTICS = <number of buffers you would want> . Once the instance is started up once again, let your application run again for a reasnable amount of time, just as you would for UTLBSTAT.sql/UTLESTAT.sql. The table X$KCBRBH

Create multiple buffer pools

Size multiple buffer pools

Monitor buffer cache usage

Make appropriate use of table caching

Diagnose LRU latch contention

Avoid free list cotention

Tuning the Redo Log Buffer

Determine if processes are waiting for space in the redo log buffer

Size the redo log buffer appropriatly

Reduce redo operations

Database configuration and I/O issues

Diagnose appropriate use od SYSTEM,RBS,TEMP, DATA and INDEX tablespaces.

Use locally managed tablespaces to avoid space management issues

Detect I/O problems

Ensure that the files are distributed to minimize I/O contention and use appropriate type of devices.

Use striping where appropriate

Tune checkpoints

Tune DBWn process I/O

The parameter LOG_CHECKPOINT_TIMEOUT specifies an upper bound on the time a buffer can be dirty in the cache before DBWn must write it to disk. If you set LOG_CHECKPOINT to 60, then no buffer reamins dirty in the cache for more than 60 seconds. The default value for LOG_CHECKPOINT_TIMEOUT is 1800.