Re: Viewing the hidden _{Parameters}

From: Rob K <robkato_at_earthlink.net>
Date: Fri, 15 Jun 2001 14:49:21 GMT
Message-ID: <RfpW6.7378$aV1.706809@newsread1.prod.itd.earthlink.net>

We are having a problem with parallel query and latch waits.

This is from statspack while running a parallel query.

                                      Get    Pct Get    Avg Slps  No Wait
                                       req     Miss       /Miss     REq
parallel query alloc buffer 844    12.7       0.0        0
parallel query stats           58      37.9       0.2        0

                                      Get
Spin & Sleep
                                      req          Misses   Sleeps   1->4

cache buffers chains 11,295,272 805,333 1,647 803775/1474/

I was told the oracle DID NOT set the _db_block_hash_buckets to next prime number higher than 2 x DB_BLOCK_BUFFERS

that it only sets it to 2 x DB_BLOCK_BUFFERS

I was also suggested to set
_KGL_LATCH_COUNT - Set to next prime number higher than 2 x CPUs

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:992593921.23156.1.nnrp-07.9e984b29_at_news.demon.co.uk...
>
> select
> ksppinm name,
> ksppstvl value
> from
> (
> select
> x.inst_id,
> x.indx+1,
> ksppinm,
> ksppity,
> ksppstvl,
> ksppstdf,
> decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'),
>

 decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED','FALSE'),


>         decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE'),

>         decode(bitand(ksppstvf,2),2,'TRUE','FALSE'),

>         ksppdesc

> from

>         x$ksppi x,

>         x$ksppsv y        --  corresponds to v$system_parameter

> where
>
> (x.indx = y.indx)
> )
> ;
>
>
> Actual values for _db_block_hash_buckets and latches
> vary with a little more subtlety than you suggest, John.
>
> By default, the bucket count seems to be the smallest
> prime number greater than 2 x db_block_buffers, and
> the associated latch count seems to be the higher of
> db_block_buffers/2 and 256 (in Oracle 8.1.7).
>
> Interesting note - Oracle used to have one latch per
> bucket, but now has multiple buckets per latch.
>
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
> Publishers: Addison-Wesley
>
> Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
>
>
> John Morais wrote in message ...
> >I strongly recommended not changes these unless you talk to Oracle first,
> >easiest way to get de-supported. By the way in 8i you dont need to change
> >this parameter. I have done some testing and you get one latch per
 db_block.
> >Unlike 7.x where you got a ratio of 4/1.. ie 4 db_blocks to one latch.
 The
> >SQL to view the documented parameters is :
> >
> >
> >
> >select ksppinm from x$ksppi
> >
> >where substr(ksppinm,1,1) = '_';
> >
> >
> >
> >"Rob K" <robkato_at_earthlink.net> wrote in message
> >news:HU9W6.5411$aV1.495316_at_newsread1.prod.itd.earthlink.net...
> >
> >> How can I find what the values are for the '_' parameters (hidden) i.e.
> >> _db_block_hash_buckets
> >>
> >> Thanks Rob
> >>
> >>
> >
> >
>
>
>
Received on Fri Jun 15 2001 - 09:49:21 CDT