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
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)
select gethitratio from v$librarycache where namespace='SQL AREA';
To improve the performace of the library cache:
dbmspool.sqlscript. 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;
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:
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.
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.
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).
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.
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_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:
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.
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 )
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
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.