Re: shared pool subpool

  • From: "eagle fan" <eagle.f@xxxxxxxxx>
  • To: kaygopal@xxxxxxxxx
  • Date: Tue, 24 Oct 2006 14:34:45 +0800

hi:

Thanks for your reply.

I have read your wonderful OWI book chapter 6 "Latch Free" part and we set
_KGHDSIDX_COUNT = 2 on one database(it was 6 subpools before). It worked
well till now.

But we don't want to use this parameter on all databases, as you said, it
may has some bugs.

My question is why subpools are not evenly utilized.

Since one subpool has less free memory than other sub pools, why sql still
goes there?  How oracle determin which subpool the sql should go? By hash
value or other algorithm?

And I also found that some components such as db_block_buffers,
db_block_hash_buckets, misc are not evenly distrubuted in sub pools.


On 10/24/06, K Gopalakrishnan <kaygopal@xxxxxxxxx> wrote: 

Without knowing more details about your application it would be difficult
for us  to come to a conclusion. However to answer your question, the memory
management of shared(sub) pools is similar to shared pool with LRU lists and
reserved lists.

To workaround this issue, you may want to reduce the number of subpools by
setting _kghdsidix_count parameter.  We have a short discussion about inner
workings of shared (sub) pools in our OWI book. Search in metalink for the
above parameter and you may (!) hit some bugs for your version.:)

Good Luck !

Have a nice day !!
------------------------------------------------------------
Best Regards,
K Gopalakrishnan,
Co-Author: Oracle Wait Interface: Oracle Press 2004.
http://www.amazon.com/gp/product/007222729X/

Author: Oracle Database 10g RAC Handbook, Oracle Press 2006
http://www.amazon.com/gp/product/007146509X/

----- Original Message ----
From: eagle fan <eagle.f@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Sent: Monday, 23 October, 2006 9:26:55 PM
Subject: shared pool subpool

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




--
Eagle Fan

Oracle DBA

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 10-2006