Re: Memory-related issues from "Expert Oracle 9i Administration" by Sam Alapati

> SQL> select bytes
> 2 from v$sgastat
> 3 where pool = 'shared pool'
> 4 and name = 'free memory';
>
> BYTES
> ----------
> 12769368

I use this sort of query for figuring out if my Shared Pool is large enough. How much free memory do I have right now in the Shared Pool? Monitor this over time to see if the Shared Pool has enough free memory.

> SQL> l
> 1 select to_number ( value ) shared_pool_size,
> 2 sum_obj_size,
> 3 sum_sql_size,
> 4 sum_user_size,
> 5 ( sum_obj_size + sum_sql_size + sum_user_size ) * 1.2
> min_shared_pool
> 6 from ( select sum ( sharable_mem ) sum_obj_size
> 7 from v$db_object_cache ),
> 8 ( select sum ( sharable_mem ) sum_sql_size
> 9 from v$sqlarea ),
> 10 ( select sum ( 250 * users_opening ) sum_user_size
> 11 from v$sqlarea ),
> 12 v$parameter
> 13* where name = 'shared_pool_size'
> SQL> /
The problem with the above query is that there is no read consistency when querying dynamic performance views. There are three inline views here. When V$DB_OBJECT_CACHE is queried, it returns a value. Between the time that value is returned and the value is returned from V$SQLAREA, the Shared Pool could have undergone changes so as to take memory from one area and assign it to another. This can give misleading results.

You did not post your Oracle version, but if you are running Oracle 10g, then you may want to consider using the SGA_TARGET initialization parameter to size your entire SGA and let it self-manage itself.

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown