Shared Pool Tuning

Tuning Library Cache:

1. Increase the size of the shared pool if GETHITRATIO is less than 90%.

Check gethitratio in v$librarycache:

SQL> select gethitratio

from v$librarycache

where namespace='SQL AREA';

 

GETHITRATIO

-----------

.924415714

 

2. Reloads-to-pins ratio should be less than 1%, otherwise need to increase SHARED_POOL_SIZE.

SQL> select SUM(pins) "Executions",

SUM(reloads) "Cache Misses",

(SUM(reloads)/SUM(pins))*100 "Reload-to-pins Ratio"

From v$librarycache;

 

Executions Cache Misses Reload-to-pins Ratio

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

21014 14 .066622252

 

SQL> select sum(pins)/(sum(pins)+sum(reloads))*100 "Cache Hit Ration"

From v$librarycache;

 

Cache Hit Ration

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

99.9127943

 

3. Avoid Invalidations

SQL> select namespace,pins,reloads,invalidations

From v$librarycache;

 

NAMESPACE PINS RELOADS INVALIDATIONS

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

SQL AREA 16950 14 0

TABLE/PROCEDURE 5098 0 0

BODY 0 0 0

TRIGGER 4 0 0

INDEX 1407 0 0

CLUSTER 213 0 0

OBJECT 0 0 0

PIPE 0 0 0

JAVA SOURCE 0 0 0

JAVA RESOURCE 0 0 0

JAVA DATA 0 0 0

 

4. Take Advice

SQL> select shared_pool_size_for_estimate AS po_size,

estd_lc_time_saved

from v$shared_pool_advice;

 

PO_SIZE ESTD_LC_TIME_SAVED

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

44 333

52 334

60 335

68 335

76 335

84 335

92 335

100 335

108 335

116 335

124 335

 

11 rows selected.

 

Tuning Data Dictionary Cache:

SQL> select (1-(sum(getmisses)/sum(gets)))*100 "Dic Cache Hit Ratio"

From v$rowcache;

 

Dic Cache Hit Ratio

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

99.1790882

This ratio should be >85%, otherwise need to increase SHARED_POOL_SIZE.

 

SQL> select parameter,gets,getmisses

from v$rowcache;

 

PARAMETER GETS GETMISSES

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

dc_free_extents 0 0

dc_used_extents 0 0

dc_segments 26129 926

dc_tablespaces 1776 4

dc_tablespace_quotas 0 0

dc_files 0 0

dc_users 57246 16

dc_rollback_segments 2293 21

dc_objects 162462 3541

dc_global_oids 40082 724

dc_constraints 0 0

 

PARAMETER GETS GETMISSES

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

dc_object_ids 575322 2013

dc_sequences 8 2

dc_usernames 38351 18

dc_database_links 0 0

dc_histogram_defs 160 160

dc_table_scns 5 5

dc_outlines 0 0

dc_profiles 0 0

dc_encrypted_objects 0 0

dc_encryption_profiles 0 0

dc_qmc_cache_entries 0 0

 

PARAMETER GETS GETMISSES

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

dc_users 0 0

dc_histogram_data 0 0

dc_histogram_data_values 0 0

dc_partition_scns 0 0

dc_user_grants 2258 1

dc_app_role 0 0

 

28 rows selected.

 

Getmisses should be<15% other wise need to increase SHARED_POOL_SIZE.

 

CURSOR_SHARING=EXACT/SIMILAR

 

If it is EXACT then parsing will be different for the query shown below, if it is SIMILAR then parsing will be same.

 

SQL> show parameter cursor_sharing;

NAME TYPE VALUE

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

cursor_sharing string EXACT

 

SQL> select * from dual CS_FORCE where dummy = 'A';

no rows selected

 

SQL> select * from dual CS_FORCE where dummy = 'B';

no rows selected

 

SQL> select sql_text

from v$sql

where sql_text like 'select * from dual CS% where dummy = %';

 

SQL_TEXT

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

 

SQL> alter session set cursor_sharing=similar;

Session altered.

 

SQL> alter system flush shared_pool;

System altered.

 

SQL> select * from dual CS_SIMILAR where dummy = 'A';

no rows selected

 

SQL> select * from dual CS_SIMILAR where dummy = 'B';

no rows selected

 

SQL> select sql_text

from v$sql

where sql_text like 'select * from dual CS% where dummy = %';

 

SQL_TEXT

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

select * from dual CS_SIMILAR where dummy = :"SYS_B_0"