Re: Viewing the hidden _{Parameters}

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 14 Jun 2001 09:19:41 +0100
Message-ID: <992593921.23156.1.nnrp-07.9e984b29@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 Thu Jun 14 2001 - 03:19:41 CDT