 |
|
Oracle Tips by Burleson |
Events Related to ORA-4031
Error
If the system is having recurrent ORA-4031 errors, shared pool
excessive fragmentation is usually the cause. The following script
must be run from the SYS user. It will show the errors that have
occurred and this information will help the DBA isolate the issue:
* SEE_4031.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
rem
rem SEE_4031.SQL
rem Mike Ault
rem
column kghlurcr heading "RECURRENT|CHUNKS"
column kghlutrn heading "TRANSIENT|CHUNKS"
column kghlufsh heading "FLUSHED|CHUNKS"
column kghluops heading "PINS AND|RELEASES"
column kghlunfu heading "ORA-4031|ERRORS"
column kghlunfs heading "LAST ERROR|SIZE"
ttitle 'Report on 4031 events (SYS user only)'
spool cpu_stats
select
kghlurcr,
kghlutrn,
kghlufsh,
kghluops,
kghlunfu,
kghlunfs
from
sys.x$kghlu
where
inst_id = userenv('Instance')
;
spool off
clear columns
ttitle off
The following report displays data about the current state of the
shared pool, showing fragmentation issues.
Wed Sep 22 page 1
Report on 4031 events (SYS user only)
RECURRENT TRANSIENT FLUSHED PINS AND ORA-4031 LAST ERROR
CHUNKS CHUNKS CHUNKS RELEASES ERRORS SIZE
---------- ---------- ---------- ---------- ---------- ----------
10608 17797 9191 1410063 0 0
Generally speaking, if there are no 4031 errors, the DBA should
simply look at the distribution of chunks and compare it to the
number of SQL areas used. This ratio will reveal how many chunks per
SQL statements are being utilized and will present a gage as to how
much the pool is fragmenting. If an error has occurred, the report
will show the size of the area requested which will to allow the DBA
to plan space usage more efficiently.
The above book excerpt is from:
Oracle
Tuning Power Scripts
With 100+ High Performance
SQL Scripts
Oracle In-Focus Series
ISBN
0-9744486-7-2
Mike Ault, Donald K.
Burleson. Harry Conway
http://www.rampant-books.com/book_2005_1_power_tuning.htm |
