| |
 |
|
Oracle Shared Pool Advisory Utility
Oracle Tips by Burleson Consulting |
Oracle Shared Pool Advisory Utility
Oracle9i release 2 included a new advice called
v$shared_pool_advice , and there is talk of expanding
the advice facility to all SGA RAM areas in future releases of
Oracle. It is also included in the standard AWR reports ($ORACLE_HOME/rdbms/admin/awrrpt.sql).
Starting in Oracle9i release 2, the
v$shared_pool_advice view shows the marginal
difference in SQL parses as the shared pool changes in size from 10%
of the current value to 200% of the current value.
The Oracle documentation contains a complete
description for the set-up and use of shared pool advice, and it is very simple to configure. Once it is
installed, a simple script can be run to query the
v$shared_pool_advice view and locate the marginal
changes in SQL parses for different
shared_pool sizes.
SEE CODE DEPOT FOR FULL SCRIPTS
The following is a sample of the output:
Est Est
Time Parse
Pool Size Est Est LC 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 for the shared pool in this
example fall in a wide range from 50% of the current size to 200% of
the current size. These statistics can give a great idea about the
proper size for the
shared_pool‑size. If
the SGA region sizes are selected automatically with the alter
system commands, creating this output and writing a program to
interpret the results is a great way to ensure that the shared pool
and library cache always have enough RAM.
The next section will provide information on the
Program Global Area (PGA ) RAM regions.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts: |
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
|
and include the URL for the page.
