Home » RDBMS Server » Performance Tuning » shared pool size tunning (Oracle9i,Windows2000)
icon5.gif  shared pool size tunning [message #289196] Thu, 20 December 2007 01:49 Go to next message
Messages: 81
Registered: April 2005
Location: th
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)
    (sum_obj_size + sum_sql_size + sum_user_size) * 1.2 min_shared_pool
    (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'; 

---------------- ------------ ------------ ------------- ---------------
       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.

code tags added by moderator

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

Report message to a moderator

Re: shared pool size tunning [message #289201 is a reply to message #289196] Thu, 20 December 2007 02:30 Go to previous message
Messages: 754
Registered: October 2007
Senior Member
40% solution to your problem is here
Apply it.
Previous Topic: Query taking too much time than usual to execute
Next Topic: SGA sizing
Goto Forum:

Current Time: Tue Jan 17 01:09:33 CST 2017

Total time taken to generate the page: 0.07300 seconds