< media.techtarget.com/searchOracle/downloads/10g_shared_pool.doc
< shared_pool_advice.sql
-- *************************************************
-- Copyright © 2003 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
set lines 100
set pages 999
column c1 heading 'Pool |Size(M)'
column c2 heading 'Size|Factor'
column c3 heading 'Est|LC(M) '
column c4 heading 'Est LC|Mem. Obj.'
column c5 heading 'Est|Time|Saved|(sec)'
column c6 heading 'Est|Parse|Saved|Factor'
column c7 heading 'Est|Object Hits' format 999,999,999
SELECT
shared_pool_size_for_estimate c1,
shared_pool_size_factor c2,
estd_lc_size c3,
estd_lc_memory_objects c4,
estd_lc_time_saved c5,
estd_lc_time_saved_factor c6,
estd_lc_memory_object_hits c7
FROM
v$shared_pool_advice;
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.
No comments:
Post a Comment