The main point of Oracle Performance tuning is the SGA

One, Shared pool tunning

The optimization of shared pool should be prioritized because a cache miss occurs more costly in shared pool than in data buffer because dictionary data is generally more expensive than the library The data in cache is kept in memory for a long time, so the key is the optimization of library cache.

Gets: (parse) The number of times the object was found in namespace;

Pins: (execution) The number of times the object was read or executed in namespace;

Reloads: (reparse) The number of times the runtime library cache misses, causing SQL to need to be parsed.

1. Check the SQL area in the V$librarycache Gethitratio is more than 90%, if not more than 90%, you should check the application code, improve the efficiency of the application code.

Select gethitratio from V$librarycache where namespace= ' sql area ';

2. The ratio of reloads/pins in V$librarycache should be less than 1%, if greater than 1%, the value of the parameter shared_pool_size should be increased.

Select SUM (Pins) "executions", sum (reloads) "Cache misses", sum (reloads)/sum (pins) from V$librarycache;

reloads/pins>1% There are two possibilities, one is the library cache space is insufficient, one is the object referenced in SQL illegal.

3. The shared pool reserved size is typically 10% of the shared pool size and cannot exceed 50%. The request misses=0 in v$shared_pool_reserved is either not continuously growing, or the free_memory is greater than 50% of the shared pool reserved size, indicating that the shared pool reserved Size is too large to compress.

4. The large anonymous Pl/sql code block is converted to a small anonymous Pl/sql code block to invoke the stored procedure.

5. Starting from 9i, execution plan can be saved with the SQL statement in the library cache to facilitate performance diagnostics. Execution plans can be seen from the V$sql_plan.

6. Keep Large objects in shared pool. Large objects are the main cause of memory fragmentation, in order to make room for many small objects need to move out of memory, thereby affecting the user's performance. It is therefore necessary to keep some of the most commonly used objects in the shared pool, and the following objects need to remain in the shared pool:

A. Regular use of the storage process;

B. Compiled triggers on frequently-manipulated tables

C. Sequence, because the Sequence may be lost after it has been removed from the shared pool.

Find large objects that are not saved in the library cache:

Select * from V$db_object_cache where sharable_mem>10000 and type in (' PACKAGE ', ' PROCEDURE ', ' FUNCTION ', ' PACKAGE ') ) and kept= ' NO ';

Save these objects in the library cache:

Execute dbms_shared_pool.keep (' package_name ');

corresponding script: Dbmspool.sql

7. Find out if there are too large anonymous pl/sql code blocks. Two kinds of solutions:

A. Convert to small anonymous block call stored procedure

B. Keep it in shared pool

Find out if there are too large anonymous pl/sql blocks:

Select Sql_text from V$sqlarea where command_type=47 and Length (sql_text) >500;

8. Dictionary Cache Optimization

Avoid the occurrence of dictionary cache misses, or the number of misses to maintain a stable, only by adjusting the shared_pool_size to indirectly adjust the size of the dictionary cache.

Percent misses should be very low: most should be below 2%, the total should be less than 15%

Select sum (getmisses)/sum (gets) from V$rowcache;

If more than 15%, increase the value of shared_pool_size.