OraFAQ Forum: Performance Tuning » shared pool size tunning

Home » RDBMS Server » Performance Tuning » shared pool size tunning (Oracle9i,Windows2000)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

shared pool size tunning [message #289196]

Thu, 20 December 2007 01:49

chara
Messages: 81
Registered: April 2005
Location: th

Member

Dear all,

I had any question for tunning my shared pool size .My sga size as below :

SQL> show sga
Total System Global Area 1176052716 bytes
Fixed Size                   455660 bytes
Variable Size             880803840 bytes
Database Buffers          293601280 bytes
Redo Buffers                1191936 bytes

I was using the script for checking the
shared pool size as below :

select to_number (value)
    shared_pool_size,
    sum_obj_size,
    sum_sql_size,
    sum_user_size,
    (sum_obj_size + sum_sql_size + sum_user_size) * 1.2 min_shared_pool
    from
    (select sum(sharable_mem) sum_obj_size
   from v$db_object_cache),
   (select sum(sharable_mem) sum_sql_size
   from v$sqlarea),
   (select sum(250 * users_opening) sum_user_size
   from v$sqlarea), v$parameter
  where name='shared_pool_size'; 

SHARED_POOL_SIZE SUM_OBJ_SIZE SUM_SQL_SIZE SUM_USER_SIZE MIN_SHARED_POOL
---------------- ------------ ------------ ------------- ---------------
       738197504    629827004    622473455         63375      1502836601

The result tell me that a curently SHARED_POOL_SIZE = 738m and MIN_SHARED_POOL that required = 1500m.

But when i tried to crosscheck shared pool free memory with a script as below:

SQL> select * from v$sgastat where name ='free memory' and pool= 'shared pool';

POOL        NAME                            BYTES
----------- -------------------------- ----------
shared pool free memory                  55641816

What happend? Why the above scripts tell me required a minimum shared pool=1500 m. and below script tell me there is free shared pool memory=50m.I didn't understand.Pls advice me.
oracle9i with windows2000
Thank you for advance.
Chara

code tags added by moderator

[Updated on: Thu, 20 December 2007 14:46] by Moderator

Report message to a moderator