Database Tuning (MOSC)

MOSC Banner

shared pool fragmentation monitor

edited Mar 17, 2011 3:09AM in Database Tuning (MOSC) 11 commentsAnswered
 After the most recent ora-04031 error, I am closely monitoring the shared pool. My library cache ratio is fine less than 1%.

But when I issue the following query, I am getting one row returned:

select substr(sql_text,1,40) "SQL", count(*), sum(executions) "TotExecs"
from v$sqlarea
where executions <5
group by substr(sql_text,1,40) having count(*) >30 order by 2;

SQL                                        COUNT(*)   TotExecs
---------------------------------------- ---------- ----------
select t$item Item , initcap(t$dsca) dsc         57         82

Also, the following query returns:

SQL> select * from v$shared_pool_reserved where request_failures >0;

FREE_SPACE AVG_FREE_SIZE FREE_COUNT MAX_FREE_SIZE USED_SPACE AVG_USED_SIZE

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center