sub heap & ORA-4031

From: eagle fan <eagle.f_at_gmail.com>
Date: Thu, 6 Jul 2006 17:23:10 +0800
Message-ID: <4415a5ed0607060223hffa76d1p1e544190af08eb7c@mail.gmail.com>

hi:

I run a script on x$kghlu to collect the shared pool info:

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"

select

  kghlurcr,
  kghlutrn,
  kghlufsh,
  kghluops,
  kghlunfu,

  kghlunfs
from
  sys.x$kghlu
where
  inst_id = userenv('Instance')
/

SQL> / RECURRENT TRANSIENT FLUSHED PINS AND ORA-4031 LAST ERROR CHUNKS CHUNKS CHUNKS RELEASES ERRORS SIZE ---------- ---------- ---------- ---------- ---------- ----------

       613        740    1481527 6.0489E+10        920      25784
      2625       7264       4326   73028011          0          0
      3973       4387     283981  105939833          0          0
      3795       5289      91121 9508929013          0          0
      2502       4805     136468   93688365          0          0
      8673      10944        392   96186804          0          0

6 rows selected.

I have many questions about the results.

Here I have six sub pools. Why just the first one consistently get the 4031 errors? Does each sub pool have different shared pool components? I didn't find details info about the sub pools, if you have one, please send me a copy.
I did the heapdump at level 2. The dump files is about 60M, so I can't upload it. How can I use the dump file to explain the 4031 errors? I think each line "HEAP DUMP heap name="sga heap(1,0)" is related to one sub pool , but which part is related to the first row in x$kghlu? Is it the "sga heap(1,0)"?
The "LAST ERROR SIZE" column shows the size is 25784 , Does this mean it's located in reserved list (>5000)?

Thanks

-- 
Eagle Fan

Oracle DBA

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jul 06 2006 - 04:23:10 CDT