Re: Viewing the hidden _{Parameters}

The whole strategy for hash buckets and hash latches has changed quite recently, so it is quite possible that even Oracle's own consultants haven't caught up with it.

It is __possible__ that the number of hash buckets is not generated as next_prime(2 x buffers), but a recent buffer dump on my 8.1.7 Nt system with 1024 buffers showed me 2053 hash chains, which happens to be next_prime(2 x 1024). Similarly on another 8.1.7 system with 5120 buffers I got 10,243 chains, which also matches the requirement.

Interestingly the number of hash latches for the smaller system was 256 (buffers/4) whereas the latch count for the larger system was 1024 (buffers/5). I haven't attempted to reverse engineer a formula for latches yet.

(PS - to do the dump to see the hash buckets:

alter system set events
'immediate trace name buffers level 4';

The dump will be about 700 bytes per buffer.

(Don't do a level 10 dump).

Look up _kgl_latch_count on Steve Adam's site, (http://www.ixora.com.au) I think he has some good information on what it is and how it works (but I don't think it has anything to do with your problem).

In your case, I would drill down to v$latch_children to see if any of the cache buffers chains latches has an anomalous number of misses, then join the relevant v$latch_children.addr with x$bh.hladdr to find the ojecet number, file and block addresss of the few problem blocks.

--
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



Rob K wrote in message ...


>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

>