Oracle Shared Pool Advisory Utility

Oracle Shared Pool Advisory Utility

Oracle Shared PoolAdvisory Utility

Oracle9i release 2included a new advice calledv$shared_pool_advice , and there is talk of expanding the advice facility to all SGA RAM areasin future releases of Oracle.  It is also included in the standard AWRreports ($ORACLE_HOME/rdbms/admin/awrrpt.sql).

Starting inOracle9i release 2, the v$shared_pool_advice  view showsthe marginal difference in SQL parses as the shared pool changes in size from10% of the current value to 200% of the current value.

The Oracledocumentation contains a complete description for the set-up and use of sharedpool advice, and it is very simple to configure.  Once it is installed, asimple script can be run to query the v$shared_pool_advice view and locatethe marginal changes in SQL parses for different shared_pool  sizes. 

            shared_pool_advice.sql

SEE CODE DEPOT FOR FULL SCRIPTS

The following is asample of the output:

 

                                        Est     Est

                                       Time   Parse

  Pool    Size      Est     EstLC    Saved  Saved          Est

Size(M)  Factor LC(M)    Mem. Obj.    (sec)  Factor Object Hits

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

    48      .5      48      20839 1459645       1  135,756,032

     64  .6667       63     28140  1459645       1  135,756,101

     80  .8333       78     35447  1459645       1  135,756,149

    96       1      93      43028 1459645       1  135,756,253

    112 1.1667      100     46755  1459646       1  135,756,842

    128 1.3333      100     46755  1459646       1  135,756,842

   144     1.5     100      46755 1459646       1  135,756,842

    160 1.6667      100     46755  1459646       1  135,756,842

    176 1.8333      100     46755  1459646       1  135,756,842

   192       2     100      46755 1459646       1  135,756,842

The statistics forthe shared pool in this example fall in a wide range from 50% of the currentsize to 200% of the current size.  These statistics can give a great ideaabout the proper size for the shared_pool‑size.  If the SGA region sizes areselected automatically  with the alter system commands, creating thisoutput and writing a program to interpret the results is a great way to ensurethat the shared pool and library cache always have enough RAM.

The next sectionwill provide information on the Program Global Area   (PGA ) RAMregions.