SHARED_POOL_SIZE

[Ablakios]

Hi all
I am using Quest Software Spotlight on Oracle to monitor my database and my shared_pool_size is 249M and 94% used as per the monitoring tool.

It seems to me that 249 M is big enough. yet the alert alarm from quest is stating that shared_pool too small.

Do you thing increasing the size to 500 M will have any impact
on the database or performance?

Please advise

[Edited by Ablakios on 06-07-2002 at 02:22 PM]

[pando]

it´s complaining because of his default parameters, its like TOAD, it complains because some hit ratio is below 95% but the database is running fine or just in a point of time it falls below that threshold

dont trust everything it complains, put some common sense as well

[Ablakios]

I flushed the shared_pool and the percentage used decreased from 94% to 45%

Just thinking of flushing the shared_pool constantly maybe.
thanks

[Ablakios]

Pando
Thank for the response.
What do think about flushing the shared_pool twice or 3X daily?
Any impact? or performance degradation?

[jmodic]

Originally posted by Ablakios
Just thinking of flushing the shared_pool constantly maybe.

Yeah, the second best thing you can do with your database! But you should maybe try the best thing to obtain your goal: you could restart the database after every single command! That way you won't only flush the shared pool, you will also flush the buffer cache and other parts of SGA!

No, seriously, haven't you read what pando suggested? "Use some common sense"! I suggest you read the Concepts manual to get fammiliar with what shared pool is and what is it used for....

[Edited by jmodic on 06-07-2002 at 03:07 PM]

[mrvajrala]

Jean,
The flusing of shared Pool 3 times a day will cause a perfromance HIT. The only reason is all the SQLs have to be reparsed and reloaded, which is very costly..

You may wish to consider the following
1. percentage of literal SQl statements
2. Versions Counts
3. Invalidations

And if you are able to minimize the above then you may not need the flushing at all.

if you are unable to reduce the literal sql statements and if are seeing ORA-04031 (shared pool fragmentation ), then you have consider that option , once in a while .

Hope this adds

[patnams]

Investigate why your shared pool is reaching 249 mb.
Depends on #of Sessions, #of Packages, Procedures, SQL that are executing. Also you might be fragmenting your shared pools, try pinning some packages into the shared pool.

[Calvin_Qiu]

Query V$librarycahce to check which namespace has a poor peformance.

Query v$sesstat to check which session "Parse Count" statistics to find out which session cause it.

Use SQL Trace to find out the poorly designed SQL statement.