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"