Accessing parts of the database already loaded into memory is much faster than accessing from disk. The other important factor is that of distribution of available virtual memory resources to the different memory structures available in Oracle. Memory should be tuned after SQL and application code and before I/O access speed. Oracle divides memory resources into separate buffers.
The database buffer cache needs to be large enough such that neccessary data is not purged from memory and not too large such that performance is affected. The init.ora parameters file contains four parameters affecting memory assigned to an Oracle database instance.
The following query shows the values for the above parameters.
select name, value from v$parameter where name in ('db_block_buffers','db_block_size','shared_pool_size','sort_area_size'); NAME VALUE ----------------------- ----------------------- shared_pool_size 3500000 db_block_buffers 200 db_block_size 2048 sort_area_size 65536
The SGA contents values are shown in more depth in the query shown below.
select name, value from v$parameter where name in ( 'db_block_buffers' ,'db_block_size' ,'shared_pool_size' ,'shared_pool_size' ,'sort_area_size' ,'sort_area_retained_size' ,'db_file_multiblock_read_count' ,'large_pool_size' ,'log_buffer' ,'java_pool_size' ,'buffer_pool_keep' ,'buffer_pool_recycle' ,'cpu_count' ,'sort_multiblock_read_count' ) order by name; cpu_count 2 shared_pool_size 481380352 large_pool_size 15782880 java_pool_size 20971520 db_block_buffers 176286 buffer_pool_keep buffer_pool_recycle db_block_size 8192 log_buffer 32768 db_file_multiblock_read_count 8 sort_area_size 1572864 sort_area_retained_size 0 sort_multiblock_read_count 2 parallel_automatic_tuning TRUE
The following query is a selection from the V$SGASTAT performance view. The V$SGASTAT view provides a more in depth view of the SGA.
select * from v$sgastat; POOL NAME BYTES ----------- -------------------------- ---------- fixed_sga 65476 db_block_buffers 1444134912 log_buffer 65536 shared pool free memory 477268740 shared pool miscellaneous 362420 shared pool DML locks 30624 shared pool State objects 125740 shared pool KQLS heap 621404 shared pool Checkpoint queue 147528 shared pool PX subheap 6176 shared pool ktlbk state objects 28248 shared pool latch nowait fails or sle 36000 shared pool java static objs 37208 shared pool db_files 426280 shared pool KGFF heap 5900 shared pool db_handles 36400 shared pool enqueue_resources 94176 shared pool PL/SQL MPCODE 6288 shared pool fixed allocation callback 640 shared pool PL/SQL DIANA 226732 shared pool VIRTUAL CIRCUITS 92232 shared pool dlo fib struct 40980 shared pool KGK heap 17568 shared pool SYSTEM PARAMETERS 58800 shared pool dictionary cache 274240 shared pool message pool freequeue 124552 shared pool trigger inform 240 shared pool character set memory 31196 shared pool db_block_buffers 23974896 shared pool library cache 1137992 shared pool transactions 58080 shared pool sql area 1491784 shared pool processes 37600 shared pool sessions 118800 shared pool table columns 16532 shared pool PLS non-lib hp 2096 shared pool event statistics per sess 189120 shared pool db_block_hash_buckets 3082776 shared pool file # translation table 65572 large pool free memory 15659988 large pool PX msg pool 122892
The DB_BLOCK_SIZE determines the database block size, the most granular unit of physical storage in an Oracle database. Thus data is read from the disk in blocks. Therefore the bigger the blocks are then the more data is read for each disk I/O. Oracle8i can be set as high as 32K. Larger block sizes are generally more appropriate for multimedia containing databases or batch processing databases withg large queries. OLTP databases accessing small amounts of data concurrently would be better with a 2K block size since more precise and smaller amounts of data are required to be accessed at once.
The DB_BLOCK_BUFFERS determines the size of the area in memory inside the SGA reserved for the database block buffer cache. The buffer cache temporarily stores the most recently read database blocks in memory such that subsequent access to those same blocks can be read directly from memory without any disk I/O required. Thus the larger the DB_BLOCK_BUFFERS parameter then the more blocks can be retained in memory at once. Setting this parameter too low causes frequently used data to be flushed from the buffer too soon thus requiring disk I/O on subsequent reads. Setting this parameter too high can limit memory space for other requirements.
The DB_BLOCK_BUFFERS setting efficiency can be analysed by measurement of the buffer hit ratio. This ratio is the ratio of accesses from the database block buffer cache to that of disk I/O activity since the required blocks were flushed from memory due to lack of use. A hit ratio of less than 95% is not desirable. Set the database buffer cache size to retain 25% of the SGA memory area depending of user numbers.
The database buffer cache can be avoided. Sorting, parallel reads and full table scans do not utilise the database buffer cache. When increasing the DB_BLOCK_BUFFERS parameter ensure that the operations being tuned for are actually using the database buffer cache. Also ensure that memory available to Oracle is increased aswell if so required otherwise excessive paging and swapping can result. The script CATPARR.SQL will create a view called V$BH. The V$BH view will file and block numbers currently occupying the shared global area.
select v$bh.file# "File", count(v$bh.block#) "Blocks", count(distinct v$bh.file#||v$bh.block#) "Blocks per File" from v$bh group by v$bh.file#; File Blocks Blocks per File --------- --------- --------------- 1 1014 1014 2 2 2 3 8 8
The SHARED_POOL_SIZE parameter allocates memory to caching of the library and data dictionary. Measure the memory to disk I/O hit ratio to determine efficiency. 95% is a desired ratio. If the shared pool is not large enough resources are consumed managing limited space.
Large memory requests can sometimes be unable to find unfragmented sections of memory. The SHARED_POOL_RESERVED_SIZE reserves space in the shared pool for large memory requests. Set SHARED_POOL_RESERVED_SIZE to 10% of SHARED_POOL_SIZE. The higher the value of SHARED_POOL_RESERVED_SIZE will cause the database to request more memory from the shared pool list and less from the reserved list. It is best to set SHARED_POOL_RESERVED_SIZE such memory requests are catered for by the reserved list without causing shared pool flushing. Flushing implies removal of items previously loaded into memory. Flushing can lead to defragmentation of the shared pool and removal from memory of commonly used items. The best tuned system will have no misses and no failures.
SQL> select request_misses,request_failures,free_space from v$shared_pool_reserved; REQUEST_MISSES REQUEST_FAILURES FREE_SPACE -------------- ---------------- ---------- 0 0 512000
When REQUEST_FAILURES is greater than zero and increasing then increase the value of SHARED_POOL_RESERVED_SIZE. Conversely if REQUEST_MISSES is zero and constant or FREE_MEMORY exceeds SHARED_POOL_RESERVED_SIZE by 50% then the SHARED_POOL_RESERVED_SIZE parameter is not large enough.
The library cache will contain parsed SQL statements and PL/SQL objects, ie. procedures, packages and triggers. The hit ratio calculation is shown below. Also check the reload column in the V$LIBRARYCACHE. A reloaded statement is a statement which was in memory but had to be reloaded again after the server had already purged it from memory. Increase the SHARED_POOL_SIZE if the value of the reload column is greater than zero or the hit ratio as shown below is less than 95%.
select (1 - (sum(decode(name, 'physical reads', value, 0)) / (sum(decode(name, 'db block gets', value, 0)) + sum(decode(name, 'consistent gets', value, 0))))) * 100 "Read Hit Ratio" from v$sysstat; Read Hit Ratio -------------- 88.586128
The query below shows a listing of the contents of the v$librarycache performance view.
select namespace,gets,gethits,gethitratio,pins,pinhits,pinhitratio,reloads from v$librarycache; NAMESPACE GETS GETHITS GETHITRATIO PINS PINHITS PINHITRATIO RELOADS --------------- --------- --------- ----------- --------- --------- ----------- --------- SQL AREA 743 667 .89771198 2129 1965 .92296853 10 TABLE/PROCEDURE 402 318 .79104478 543 402 .74033149 0 BODY 2 0 0 2 0 0 0 TRIGGER 0 0 1 0 0 1 0 INDEX 28 0 0 28 0 0 0 CLUSTER 110 105 .95454545 147 142 .96598639 0 OBJECT 0 0 1 0 0 1 0 PIPE 0 0 1 0 0 1 0
The ratio of PIN retrievals to that of PIN reloads should be close to 100% as in the first query below which shows the ratio of hits in the library cache. The second query shows the percentage hits in row cache. When free memory is close to zero and either or both ratios are less than 95% then increase shared pool size.
select to_char((sum(pins-reloads))/sum(pins)*100,990.00)||'%' "Non-reparses" from v$librarycache; select to_char((sum(gets-getmisses-usage-fixed))/sum(gets)*100,990.00)||'%' "Rows in Buffer" from v$rowcache;
Utilise available memory for shared SQL areas by increasing the number of open cursors for each session by increasing the value of the OPEN_CURSORS parameter. Note that allocating too much memory to library cache could lead to paging and swapping if enough virtual memory is not available.
Library cache misses can be generally reduced by ensuring the use of shared SQL areas for SQL statements and PL/SQL blocks. This can be accomplished by making sure that SQL statements and PL/SQL blocks are not reparsed. SQL is matched in the library cache as a text string match. Always use bind variables since differing literal values will not match. Also qualify schema names if identical objects reside in different schemas. Make sure that individual users cannot change optimisation at the session level.
The CURSOR_SPACE_FOR_TIME parameter can be set to TRUE. This setting will ensure that shared SQL areas are only deallocated from when all cursors associated with a SQL statement or PL/SQL vlock are closed. This ensures a library cache hit. However, if the shared pool is not large enough other requests could result in errors due to Oracle being unable to parse new SQL statements or PL/SQL blocks since the shared pool has run out of space. In the same guise, cursors can be cached at session level by setting a value into the SESSION_CACHED_CURSORS parameter. However, the SESSION_CACHED_CURSORS uses an LRU list rather than freezing cursors into the library cache for the duration of a transaction.
select v$sysstat.name,sum(v$sesstat.value) "Hits" from v$sysstat,v$sesstat where v$sysstat.name='session cursor cache hits' and v$sysstat.statistic#=v$sesstat.statistic# group by v$sysstat.name; NAME Hits ---------------------------------------------------------------- --------- session cursor cache hits 0
Lower amounts of reparsing by cache'ing and pinning objects and SQL statements into memory uses more memory but causes higher numbers of disk hits. Unneccessary parse calls can be found and eliminated by using the SQL Trace facility, the V$SQLAREA view and the vSTATNAME view as shown below.
select sql_text,parse_calls,executions from v$sqlarea order by parse_calls; PARSE_CALLS EXECUTIONS ----------- ---------- 12 12 12 12 12 12 12 12 12 12 10 29 12 57 19 19
select * from v$statname where name in ('parse count (hard)','parse count (total)','execute count');
Tables, packages and PL/SQL objects can be pinned into memory thus preventing them from being flushed from memory. Pinning objects into memory implies that these objects are placed into an MRU (most recently used) queue rather than an LRU (least recently used) queue. Thus it is important to note that objects are never be pinned or cached into memory permanently but are more likely to be accessed from memory rather than re-read from disk. Note that an object must be read from disk atleast once, ie. the first time it is accessed.
Small, static tables which are in constant use are good candidates for cache'ing. Use the CREATE/ALTER TABLE tablename CACHE command. Note that the CACHE option ensures that ONLY full table-scan results of these cached tables are placed into the MRU list in memory.
The CACHE hint in a query can also be used to cache a table into memory on first use of the query.
SELECT /*+ CACHE(customer) */ * FROM customer;
Make sure that enough free memory is available before caheing tables into memory. Also ensure that only neccessary tables are cached, ie. small tables with static data such as lookup tables. To find out how much memory is available at any given time query the X$BH table as show below (log in as SYS).
select decode (state, 0, 'FREE', 1, decode(lrba_seq,0,'AVAILABLE','IN USE'), 3, 'IN USE', state ) "Block Status", count(*) from x$bh group by decode (state, 0, 'FREE', 1, decode(lrba_seq,0,'AVAILABLE','IN USE'), 3, 'IN USE', state ); Block Status COUNT(*) ---------------------------------------- --------- AVAILABLE 196 IN USE 4
Execute the above query repetitively if neccessary. If no free buffers are available within a short period of time increase the DB_BLOCK_BUFFERS parameter value. In my case I am running on Personal Oracle. I increased the DB_BLOCK_BUFFERS from 200 to 550 with a 128Mb RAM PC and got the result as shown below.
Block Status COUNT(*) ---------------------------------------- --------- AVAILABLE 400 FREE 150
It may not be possible to maintain the shared pool (SHARED_POOL_SIZE) at a large enough value in order to most efficiently cater for retaining objects and packages in memory. Thus pinning of packages into memory is another option. Pin PL/SQL objects into memory using the DBMS_SHARED_POOL.KEEP procedure. In general, some or all packages, including Oracle built-in packages, can be pinned into memory. Pinning packages and procedures into memory prevents reparsing of these objects when they are once again read into memory for execution for reparsing. The SQL command listed below will pin all packages into memory.
DECLARE owner VARCHAR2(32); name VARCHAR2(32); CURSOR packages IS SELECT owner, name, FROM dba_objects WHERE object_type = 'PACKAGE'; BEGIN OPEN packages; LOOP FETCH packages INTO owner, name EXIT WHEN packages%NOTFOUND; DBMS_SHARED_POOL.KEEP (owner||'.'||name, 'P'); END LOOP; END;
The value of pinning of code objects into memory is that Oracle knows when two statements issued are identical. The matching of currently submitted SQL statements or blocks is tested against those already in memory using previosly calculated hash values. For heavily active OLTP systems pinning of coded objects into memory can make a big difference in performance. Even previously executed SQL statements will not require re-execution.
Note that when pinning objects into memory it is best to do so immediately after instance startup. This will prevent fragmentation in memory with other objects already loaded. Since the pinned objects will be permanently in memory during the life of the instance they are best loaded into memory as a contiguos section.
DBMS_SHARED_POOL.[UN]KEEP (object, type) type is P, C, R or Q for package, cursor, trigger or sequence.
The following code executed in SQL*Plus will show all objects in the shared pool which are larger than a specified size.
Cache behavior of different schema objects are used differently. KEEP small, frequently used objects and place seldom used large segments into the RECYCLE buffer pool. All other segments will be placed into the DEFAULT buffer cache. It is possible to create buffer pools specifically for each instance separately. DB_BLOCK_BUFFERS defines the number of buffers per instance. Each buffer pool is created from the DB_BLOCK_BUFFERS value, the remainder number of buffer pools defined in DB_BLOCK_BUFFERS comprises the DEFAULT buffer pool. DB_BLOCK_LRU_LATCHES is also allocated to the different buffer pools in the same way as DB_BLOCK_BUFFERS. The BUFFER_POOL_KEEP and BUFFER_POOL_RECYCLE parameters can be set for example as shown below. Note that the buffers parameter assigns a number of buffers to the KEEP or RECYCLE cache and the lru_latches parameter assigns the specified number of latches to the KEEP or RECYCLE cache.
BUFFER_POOL_KEEP = (buffers:100, lru_latches:2) BUFFER_POOL_RECYCLE = (buffers:200, lru_latches:1)
Find the BUFFER_POOL_KEEP hit ratio using the query below.
select 1-(physical_reads/(block_gets+consistent_gets)) "Hit Ratio", physical_reads,block_gets,consistent_gets from v$buffer_pool_statistics where name='KEEP';
Beware of objects put into the KEEP buffer cache where these cached objects grow. In general only store static data into the KEEP buffer cache. However, static data is always relative to application functionality.
The V$SESSION_WAIT view can be used to analyse a statistical distribution of I/O's per segment in order to assess whether a particular segment should be placed into the KEEP or RECYCLE buffer pools.
LRU latches control the LRU (least recently used) buffer lists, ie. when a block or row is selected for use it is locked such that another process cannot select that same block or row at the same time. Thus processes can wait (spin) when accessing a row or block which already has obtained a latch. Latch contention is where one process is trying to lock something which another process already has. It appears that Oracle seems to be locking based on blocks. Since rows can span blocks then therefore there is no such thing as row locking in Oracle. What a suprise !!!
Use the queries below to detect latch contention.
select gets,misses,sleeps,spin_gets from v$latch; select v$latch_children.child#,sleeps/gets "Miss Ratio" from v$latch_children where name='cache buffers lru chain';
One cannot place the complete dictionary into memory since the dictionary is too large. However, every SQL statement will access the data dictionary multiple times when executing. Use the query below to show the data dictionary hit ratio. Increase the SHARED_POOL_SIZE if the hit ratio as shown below is less than 95%.
select (1-(sum(getmisses)/sum(gets)))*100 "Hit Ratio" from v$rowcache; Hit Ratio --------- 81.499395
The large pool contains the UGA when sessions are connected to the database using MTS. The large pool was added in Oracle8 as a result of MTS to allow a single process to manage several user connections. The two main purposes of the large pool are as follows. The large pool is sized using the LARGE_POOL_SIZE and the LARGE_POOL_MIN_ALLOC parameters. Note that sizing of the large pool allocates additional memory to the SGA and thus increasing the possibility of disk page swapping. With MTS Oracle allocates space in the SGA storing connections containing user, dispatcher and server processes plus users' private SQL area session level details.
When sessions are connected through dispatchers to shared server processes then memory is utilised in the shared pool (the large pool). When sessions are connected to dedicated server processes (non-MTS) then memory is included with each user process' memory allocation. Therefore MTS obviously reduces memory usage substantially for large numbers of concurrent connections.
Sums the current total amount of memory allocated to all sessions and the value of memory maximum usage sizes allocated to each session.
select sum(value) || ' Bytes' "Total sessions memory" from v$sesstat,v$statname where name in ('SESSION UGA MEMORY','SESSION UGA MEMORY MAX') and v$sesstat.statistic# = v$statname.statistic#;
This parameter allocates memory for sorting in general and for each user. If in-memory sorting cannot be performed for a particular SQL statement then disk temporary segments are allocated to the sort operation. This obviously increases I/O. Set the SORT_AREA_SIZE parameter to a point which limits generation of temporary disk segments. However, do not set SORT_AREA_SIZE so high that not enough memory is available to for other requirements. Try to get in-memory sorts with a memory to disk hit ratio of over 90%. If this is not achieved it may be neccessary to increase the SORT_AREA_SIZE value.
select (1 - (sum(decode(name, 'physical reads', value, 0)) / (sum(decode(name, 'db block gets', value, 0)) + sum(decode(name, 'consistent gets', value, 0))))) * 100 "Read Hit Ratio" from v$sysstat; Read Hit Ratio -------------- 88.596717
Obviously the larger a portion of a sort which is executed in memory and not within a temporary sort segment on disk then the faster the sort will be. The SORT_AREA_RETAINED_SIZE parameter will retain memory for future sorting. Note that the SORT_AREA_RETAINED_SIZE will be retained for the duration of the connection (has implications with MTS ans connection pooling). However, if many large sorts are executed by many concurrent users then all these users will be retaining sort space in memory for the duration of connections. A large part of the time involved in the duration of a connection is idle. Thus large sorts and many concurrent users can use up too much memory for sorting. In fact in a system with large sorts executed by many concurrent users it may be best to force sorting to disk by setting the SORT_AREA_RETAINED_SIZE parameter to zero. Note that each connection will retain an area of memory equal to the SORT_AREA_RETAINED_SIZE parameter. Only ever set SORT_AREA_RETAINED_SIZE equal to SORT_AREA_SIZE for small transactional, high connection level OLTP databases with high exact match row hit rates and little sorting. In fact it may be best to always set SORT_AREA_RETAINED_SIZE to a small percentage of the SORT_AREA_SIZE value on any system other than a batch system.
In high concurrent usage systems database connections affect memory usage most distinctly. MTS can help to alleviate this by use of connection pooling and connection multiplexing, ie. connection sharing. Every connection to the database reserves a section of memory for use by the session utilising the connection. Obviously sharing of those connections can reduce the overall amount of memory required.
The log buffer is a memory area reserved for storage of log entries. Periodically the log buffer is flushed to the log files. Database activity can fill the log buffer faster than the log writer (LGWR) process can write the buffer to disk in the log files. If this happens the buffer will be written to disk first and all other operations will wait. Increasing the redo log buffer decreases the chance of new log entries contending with a part of the buffer being written to disk. However, increasing the log buffer by too much could use up too much memory, typically the log buffer is set to 32K or 64K but larger sizes are acceptable.
select name,value from v$sysstat where name in ('redo log space requests','redo entries') order by name desc; NAME VALUE -------------------------- --------- redo log space requests 0 redo entries 45
The redo log space requests to redo entries ratio should be maintained at less than 1/ 5000. If this ratio is exceeded increase the redo log buffer size. It is tempting to make the redo log buffer cache the same size as the redo log files. Some very large databases are most efficient with very large redo log files, sometimes even as high as 250M for each redo log file. Thus it is tempting to set the redo log buffer cache to 250M. This would cause serious contention and impact performance greatly. This is because the redo log buffer cache is check and written in it's entirety under three separate circumstances. These are when the redo buffer cache is 1/3 full, whenever a commit is performed and at redo log checkpointing. Since the redo log buffer cache is checked from start to finish when being written to redo log files then a 250M redo log buffer will seriously impact database performance by having 1/3 of 250M written to the disk every time a commit is made. NEVER set the redo log buffer cache to anything over 256K. The redo log buffer cache is most efficient when being a mutliple of operating system block size, ie. 8K, 16K, 32K, 64K, 128K, 256K, etc... The highest redo log buffer cache I have ever set is 128K. Sometimes increasing log buffer cache upto 128K can significantly increase performance. Anything over 256K will have dire consequences for performance.
Operating system level paging and swapping can occur when large chunks of information are processed at once. Total hardware and virtual memory must be large enough to accomodate required memory space. A large Oracle SGA will minimise excessive paging and swapping as a result of Oracle operations. It is possible to load the entire SGA into memory on instance startup by the PRE_PAGE_SGA parameter to YES. It is important to note no matter how much the SGA is altered poor application implementation can still cause programs, for instance, continual logging in and off is very resource intensive, continual database reconnections. Execute the SHOW SGA command in sql* to see the sizes of the different parts of the SGA.
show sga Total System Global Area 13929908 bytes Fixed Size 64948 bytes Variable Size 11694080 bytes Database Buffers 2097152 bytes Redo Buffers 73728 bytes