 Oracle
Tips by Burleson
Oracle10g Shared Pool Advice
Oracle9i release 2 introduced a new advice utility called
v$shared_pool_advice. This utility is the mechanism used by Oracle
Database 10g to determine a RAM shortage in the shared pool.
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 setup and use of shared pool advice, and it is very simple to
configure. Once it is installed, you can run a simple script to
query the v$shared_pool_advice view and see the marginal changes
in SQL parses for different shared_pool sizes.
shared_pool_advice.sql
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
Here we see the statistics for the shared pool in a range from
50% of the current size to 200% of the current size. These
statistics can give you a great idea about the proper size for the
shared_pool_size.
If you are using AMM, Oracle Database 10g will adjust the
shared_pool_size area with automated “alter system” commands,
always ensuring that there is an optimal balance between the SGA
RAM regions.
|
