Key Points of Oracle performance adjustment SGA (1)

I. Shared pool tunning

The Optimization of the Shared pool should be given priority, because a cache miss occurs in the shared pool, which is more costly than the data buffer, because the dictionary data is generally longer than the data stored in the library cache in the memory, the key is the library cache Optimization.

Gets: Parse) the number of times objects are searched in namespace;

Pins: Execution) the number of times the object is read or executed in the namespace;

Reloads(Reparse) the number of times library cache misses occur during the execution phase, resulting in the re-parsing of SQL statements.

1. Check whether the gethitratio of the SQL area in v $ librarycache exceeds 90%. If not, check the application code to improve the efficiency of the application code.

Select gethitratio from v $ librarycache where namespace = 'SQL region ';

2. The ratio of reloads/pins in v $ librarycache should be less than 1%. If it is greater than 1%, the value of shared_pool_size should be increased.

Select sum (pins) "executions", sum (reloads) "cache misses", sum (reloads)/sum (pins) from v $ librarycache;

Reloads/pins> 1% has two possibilities: insufficient library cache space and invalid objects referenced in SQL.

3. The shared pool reserved size is generally 10% of the shared pool size and cannot exceed 50%. The request misses in V $ shared_pool_reserved is 0 or does not continue to grow, or the free_memory is greater than 50% of the shared pool reserved size, which indicates that the shared pool reserved size is too large and can be compressed.

4. convert large anonymous pl/SQL code blocks into small anonymous pl/SQL code blocks to call the stored procedure.

5. From 9i, you can save the execution plan and SQL statement in the library cache for performance diagnosis. We can see execution plans from v $ SQL _plan.

6. Keep large objects in the shared pool. Large objects are the main cause of memory fragmentation. in order to free up space, many small objects need to be removed from the memory, thus affecting user performance. Therefore, you need to keep some common large objects in the shared pool, and the following objects must be kept in the shared pool:

A.Frequently Used storage process;

B.Compiled triggers for frequently operated tables

C.Sequence, because the number may be lost after Sequence is removed from the shared pool.

Search for large objects not saved in the library cache:

Select * from v $ db_object_cache where sharable_mem> 10000 and type in ('package', 'Procedure ', 'function', 'package body') and kept = 'no ';

Save these objects in the library cache:

Execute dbms_shared_pool.keep ('package _ name ');

Corresponding Script: dbmspool. SQL

7. check whether a large anonymous pl/SQL code block exists. Two solutions:

A.Convert to a small anonymous block to call the Stored Procedure

B.Keep it in the shared pool

Check whether there are too many anonymous pl/SQL blocks:

Select SQL _text from v $ sqlarea where command_type = 47 and length (SQL _text)> 500;

8. Optimization of Dictionary cache

To avoid the occurrence of Dictionary cache misses or the number of misses remains stable, you can adjust the size of the dictionary cache indirectly by adjusting the shared_pool_size.

Percent misses should be low: Most should be less than 2%, and the total should be less than 15%

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

If the value exceeds 15%, the value of shared_pool_size is increased.