Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> shared pool subpool

shared pool subpool

From: eagle fan <eagle.f_at_gmail.com>
Date: Tue, 24 Oct 2006 12:26:55 +0800
Message-ID: <4415a5ed0610232126sa1c820ei31aa18dcfbf5f246@mail.gmail.com>


hi:

My database version is 9.2.0.5. The share pool still has 115M free memory.

POOL        NAME                                  BYTES

----------- -------------------------- ----------------
shared pool free memory 115,596,712

But from x$ksmss, the free memory of one subpool only has 5M

SQL> select * from x$ksmss where ksmssnam='free memory';

ADDR                   INDX    INST_ID   KSMSSLEN
KSMSSNAM                     KSMDSIDX

---------------- ---------- ---------- ---------- --------------------------
----------
000000010382A6D0 0 1 5190392 free memory 1 000000010382A6D0 38 1 49780448 free memory 2 000000010382A6D0 76 1 38853392 free memory 3 000000010382A6D0 114 1 21255952 free memory 4

And this subpool contiguously flush out chunks and caused a lot of hard parses(from x$kghlu).

            RECURRENT  TRANSIENT    FLUSHED   PINS AND   ORA-4031 LAST ERROR
  KGHLUIDX     CHUNKS     CHUNKS     CHUNKS   RELEASES     ERRORS       SIZE

---------- ---------- ---------- ---------- ---------- ---------- ----------
4 1404 1658 27895 627992485 0 0 3 1064 1866 21377 69010651 0 0 2 1384 1887 20373 81774898 0 0 1 1248 2536 1355842 437478666 5558 27224

Why the sql goes to the subpool which has very less free memory. What's the algorithm of subpool space management?

We have same experiences on many cases before, the only thing what we can do is set shared_pool_size to a bigger value and bounce the database.

I tried to search internet for more shared pool internal infomation, but only found very limited metarials.

Any ideas or metarials are appriciated.

--

Eagle Fan

Oracle DBA

--

http://www.freelists.org/webpage/oracle-l Received on Mon Oct 23 2006 - 23:26:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US