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

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

Re: shared pool subpool

From: eagle fan <eagle.f_at_gmail.com>
Date: Tue, 24 Oct 2006 14:34:45 +0800
Message-ID: <4415a5ed0610232334u56a215bbo2d89061d38004fc4@mail.gmail.com>


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_at_yahoo.com> 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_at_gmail.com>
> To: oracle-l_at_freelists.org
> 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

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Oct 24 2006 - 01:34:45 CDT

Original text of this message

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