Tuning Oracle Database Basics

1       Introduction

Purpose

One of the biggest responsibilities of a DBA is to ensure that the Oracle database is tuned properly. The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance.

Definition

Oracle stores information in memory caches and on disk. Memory access is much faster than disk access. Disk access (physical I/O) take a significant amount of time, compared with memory access, typically in the order of 10 milliseconds. Physical I/O also increases the CPU resources required, because of the path length in device drivers and operating system event schedulers. For this reason, it is more efficient for data requests for frequently accessed objects to be satisfied solely by memory, rather than also requiring disk access.

2       Implementation

Library cache hit ratio

select namespace,gets,pins,reloads,invalidations,gethitratio from v$librarycache;

GETHITRATIO should be above 90%

select sum(pins-reloads)/sum(pins)*100 "library cache hit ratio" from v$librarycache;

library cache hit ratio should be near 100%

select sum(pins) "executions",sum(reloads) "misses",sum(reloads)/sum(pins) "reload ratio" from v$librarycache;

If reloads to pins ratio is grater than 1 percent increase the shared_pool _size

From statspack report “library cache activity pct misses” should be very low

Select owner,name,type,sharable_mem,executions from v$db_object_cache where sharable_mem>10000

And type in (‘PACKEGE’,’PACKAGE BODY’,PROCEDURE’,’FUNCTION’,’TRIGGER’) and kept=’NO’ order by sharable_mem desc;

This gives large objects that are candidate for pinning are not pinned.

Use DBMS_SHARED_POOL package to keep or pin an object

($ORACLE_HOME/rdbms/admin/dbmspool.sql)

Exec sys.dbms_shared_pool.keep(‘sys.standard’);

Now the keep column in v$db_object_cache will show YES

Exec sys.dbms_shared_pool.unkeep(‘sys.standard’);

This does not remove now unpinned object from the shared pool use

alter system flush shared_pool;

does flush unpinned objects not pinned ones and before pinning any large object.

Before pinning an large object it should be available in shared pool(Just query it)

Query the v$db_object_cache to verity it is available in shared pool.

Use dbms_shared_pool.sizes procedure (which take one number parameter ie size in K)

Returns those objects that are larger than k size available in shared pool.

Set serveroutput on

Exec sys.dbms_shared_pool.sizes(150);

Pinning anonymous blocks

If having large PL/SQL blocks either convert them into smaller blocks that call packaged functions and procedures or

Identify pl/sql blocks from v$sqlarea  and use dbms_shared_pool  to pin it.

Select address,hash_value,sql_text from v$sqlarea where command_type=47 and length(sql_text)>100;

Exec sys.dbms_shared_pool.keep(‘address,hash_value’);

TUNING shared pool reserve space

Requests for large chunks of memory are broken into smaller chunks. Compiling PL/SQl or temporary storing java objects need large continuous memory allocation for this set SHARED_POOL_RESERVED_SIZE to 10 percent of shared_pool_size if shared pool is already tuned. By default it is 5 percent.

This memory is is allocated from shared pool only there fore increasing this decreases the unreserved size of shared pool.

Use v$shared_pool_reserved to find request_misses and request_failures . If request_failures is grater than zero and increasing then reserved pool is too small and possibily the shared pool as well.

DBMS_SHARED_POOL.aborted_request_threshold procedure is used to limit the amount of shared pool cache to flush before ORA-04031 ie if an attempt to load large object causes too much shared pool cleanout.

DICTIONARY CACHE HIT RATIO (query after some database activity)

Select parameter,gets,getmisses,100*(gets-getmisses)/(gets) hit_rate,modifications from v$rowcache where gets>0;

If misses are high in extents and segment parameters, we might suspect large amount of dynamic extent allocation, this can be reduced by accurately sizing the objects in the design phase. Less than 2 percent misses should be there. This is accomplished by increasing the size of shared_pool_size.

Overall dictionary cache hit ratio is calculated as

Select (sum(gets-misses))/sum(gets) “dic cache hit ratio” from v$rowcache;

IMPROVING DICTIONARY CATCHE PERFORMANCE

• Use CACHE option in  CREATE SEQUENCE
• Do not  perform DDL on heavily  used objects when they are heavily used the most

UGA AND SESSION MEMORY

PGA in dedicated server = stack space +UGA (user session data + cursor state)

PGA in shared server consist of stack space only UGA is part of shared pool there

Oracle recommends using the shared server environment because it reduces overall memory and cpu consumption; fewer processes are used then in the dedicated server environment, so less PGA memory is used. You may need to increase SHARED_POOL_SIZE, thereby increasing the overall size of the SGA, but total memory consumption by the instance and server processes will be less.

UGA statistics

V$SESSTAT, V$MYSTAT, V$STATNAME views are joined to report on session UGA memory usage. V$MYSTAT shows the information for the current session; V$SESSTAT shows information for all sessions. Substitute V$MYSTAT for V$SESSTAT if you want to see just your session information.

Select sum(value) “total sessions UGA memory” from v$sesstat A, v$statname B where NAME=’session uga memory’ and A.STATISTIC# = B.STATISTIC#;

Select sum(value) ‘total session UGA memory max” from v$SESSTAT A, v$STATNAME B where NAME=’session uga memory max’ and A. STATISTIC# = B.STATISTIC#;

If using dedicated server you can use the two numbers in above queries to estimate the additional memory to allocate to the shared pool when configuring the shared servers. The first query indicates the current memory consumption; the second indicates the maximum that each session has used. Somewhere between the two you will probably find the optimal increase in the shared pool size.

LARGE POOL

• minimum size is 300 kb and maximum is 2000mb(but may be higher depending on OS) (LARGE_POOL_SIZE is the parameter)
• No LRU used
• Useful for parallel execution, RMAN, shared server
• If not configured then shared pool contains the memory structures needed for these operations.
• If PARALLEL_AUTOMATIC_TUNING=TRUE then value of LARGE_POOL_SIZE is automatically computed and parallel execution buffers are allocated from the LARGE POOL.
• If PARALLEL_AUTOMATIC_TUNING=FALSE then LARGE_POOL_SIZE parameter must be configured in the parameter file and parallel execution buffers will be allocated from the SHARED POOL.
• Oracle recommends you let PARALLEL_AUTOMATIC_TUNING set the value of large pool for you but sometimes allocated size is too large and SGA ends up more then available real memory and due to it instance fails to start. In this case set the LARGE_POOL_SIZE to a low value such as 1M and start instance. If you start seeing ORA-04031 in alert log and message specifies large pool then query V$SGASTAT and adjust size of large pool.
• Select name , bytes from V$SGASTAT where POOL=’large pool’;
• Oracle recommends that you use the large pool for RMAN only if the instance is unable to allocate memory from shared pool for I/O slaves, indicated by the message in the alert log. When RMAN needs I/O buffer for slaves following algorithm is used so as where to get the buffer from.

1)    If large pool configure and large enough get the buffers there.

2)    If configured but not large enough then it does not attempt to use shared pool.

3)    If large pool not configured then use shared pool.

4)    IF it can not allocate memory any where then it uses local process memory and writes in the alert log that synchronous I/O is used and backup slaves are not used.

5)    If you plan to use large pool for RMAN here’s the formula for determining how much space to add to the LARGE_POOL_SIZE

• If BACKUP_TAPE_IO_SLAVES=TRUE then allocate 4MB per channel
• If BACKUP_DISK_IO_SLAVES=TRUE then allocate 512KB per channel times the value of MAXOPENFILES.
• Oracle recommends that you configure the large pool for use with the shared servers. (still about 10kb per session is allocated from the shared pool)
• You can calculate the shared server requirement of large pool by determining the per session memory utilization and multiply that number by peak number of concurrent users or session high water mark.
• If library cache hit ratio is 100% then set CURSOR_SPACE_FOR_TIME=TRUE Cursor can not be deallocated from memory while the application cursor that is associated with it is open.
• CURSOR_SHARING=EXACT (means sql statement text match exactly if it is to be reduced.)
• CURSOR_SHARING= SIMILAR or FORCE reduces hard parsing and can benefit applications where the queries are similar but may differ in literal values, or where the library cache hit ratio is very low SIMILAR and FORCE should not be used in DSS systems or with star transformation queries.
• SESSION_CACHED_CURSORS if set to a positive number improves performance of repeated parse call to the same sql.
• OPEN_CURSORS is the number of cursors that a user process is allowed to use to reference private sql areas. Increasing this number allows the session to open more cursors simultaneously. The trade-off is that more open cursors means more memory required. The application code should be written to close cursors when not needed.

SIZING THE BUFFER CACHE

Look for cache hit% towards end of statspack report in buffer pool statistics section.

Increasing the size of buffer cache generally improves performance but not if the application performs mostly full table scans as in DSS or data ware house.

Caching tables in memory is good if for small tables that are generally accessed by full table scans.

When we first configure an oracle instance DB_CACHE_SIZE is not known to assist us we have DB_CACHE_ADVICE (ON OFF READY). If ON or READY about 100 bytes per buffer is allocated from the shared pool.

• OFF Advisory disabled no memory is allocated in the shared pool.
• READY Shared pool memory is allocated but statistics are not gathered.
• ON Memory is allocated and statistics are gathered.

SELECT 1- ((p.value - l.value - d.value) / s.value) "Buffer Cache Hit Ratio"
FROM v$sysstat s, v$sysstat l, v$sysstat d, v$sysstat p
WHERE s.name = 'session logical reads'
AND d.name = 'physical reads direct'
AND l.name = 'physical reads direct (lob)'
AND p.name = 'physical reads'

The following SQL statement returns the predicted I/O requirement for the default buffer pool for various cache sizes:

COLUMN size_for_estimate          FORMAT 999,999,999,999 heading 'Cache Size (MB)'

COLUMN buffers_for_estimate       FORMAT 999,999,999 heading 'Buffers'

COLUMN estd_physical_read_factor  FORMAT 999.90 heading 'Estd Phys|Read Factor'

COLUMN estd_physical_reads        FORMAT 999,999,999 heading 'Estd Phys| Reads'

SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads

   FROM V$DB_CACHE_ADVICE

   WHERE name          = 'DEFAULT'

     AND block_size    = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')

     AND advice_status = 'ON';

The following output shows that if the cache was 212 MB, rather than the current size of 304 MB, the estimated number of physical reads would increase by a factor of 1.74 or 74%. This means it would not be advisable to decrease the cache size to 212MB.

However, increasing the cache size to 334MB would potentially decrease reads by a factor of .93 or 7%. If an additional 30MB memory is available on the host machine and the SGA_MAX_SIZE setting allows the increment, it would be advisable to increase the default buffer cache pool size to 334MB.

Estd Phys    Estd Phys

 Cache Size (MB)      Buffers Read Factor        Reads

---------------- ------------ ----------- ------------

              30        3,802       18.70  192,317,943      10% of Current Size

              60        7,604       12.83  131,949,536

              91       11,406        7.38   75,865,861

             121       15,208        4.97   51,111,658

             152       19,010        3.64   37,460,786

             182       22,812        2.50   25,668,196

             212       26,614        1.74   17,850,847

             243       30,416        1.33   13,720,149

             273       34,218        1.13   11,583,180

             304       38,020        1.00   10,282,475      Current Size

             334       41,822         .93    9,515,878

             364       45,624         .87    8,909,026

             395       49,426         .83    8,495,039

             424       53,228         .79    8,116,496

             456       57,030         .76    7,824,764

             486       60,832         .74    7,563,180

             517       64,634         .71    7,311,729

             547       68,436         .69    7,104,280

             577       72,238         .67    6,895,122

             608       76,040         .66    6,739,731      200% of Current Size

This view assists in cache sizing by providing information that predicts the number of physical reads for each potential cache size. The data also includes a physical read factor, which is a factor by which the current number of physical reads is estimated to change if the buffer cache is resized to a given value.

Minimum configuration of sga is 3 granules (1 for buffer cache, 1 for shared pool, 1 for fixed sga which includes redo buffers). So minimum configuration of sga is 3 granules or 12 MB.