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"

Comments