Re: RE: Top 10 DBA Do's and Don'ts anyone

A couple of serious "Don'ts"

1. Don't believe it's true just because it's printed
2. Don't believe it's advanced just because it mentions X$ or underscore parameters.

>From a recent publication:

Paragraph numbers added for reference.
<<quote>>

1. Cache Buffers Chains: The cache-buffers chains latch is needed to scan the SGA buffer cache for database cache buffers.
2. Hot blocks (often accessed) in the buffer cache cause cache-buffers chains latch issues. Hot blocks may also be a symptom of poorly tuned SQL statements. A hot record creates a hot block that can cause issues for other records inside that block as well as any block "hashed" to the same chain.
3. To find the hot block, query V$LATCH_CHILDREN for the address and join it to V$BH to identify the blocks protected by this latch (doing so will show all blocks that are affected by the hot block).
4. You can identify the object by querying DBA_EXTENTS based on the file# and dbablk found from V$BH.
5. Using a reverse-key index—if the hot block is on an index— will move sequential records to other blocks so they are not locked up by the hot block in the chain.
6. If the hot block is the index root block, a reverse-key index won't help.
7. Setting _DB_BLOCK_HASH_BUCKETS to the prime number just larger than twice the number of buffers (DB_CACHE_SIZE/DB_BLOCK_SIZE) will usually eliminate this problem.
8. Prior to Oracle9i, this parameter had a default that caused tremendous contention for this latch; the default is correctly set to a prime number in Oracle9i.

<<end quote>>

Para (3) - should explain how (which columns) to join v$bh to v$latch_children. Unfortunately, the HLADDR (hash latch address) of x$bh is not exposed in v$bh, so the there is nothing available to make the join from v$latch_children.addr to v$bh.

Para (4) - could point out that DBA_EXTENTS includes a UNION ALL of tab$, tabpart$ and many other SYS tables. My very small 8.1.7.4 system, using dba_extents with the classic

     where block_id <= NN
    and block_id + blocks > NN
    and file_id = MMM
took 1,508 physical reads.

Perhaps it would be better to use the value in v$bh.objd to query obj$.dataobj# - even though this will result in a tablescan of obj$.

Para (7) - this paragraph seems to be saying that setting the number of hash chains will stop a hot index root block from being hot. But a block is hot because of the number of times it is accessed, not because of its location.

Of course, if there are two hot blocks on the same hash chain (aka hash bucket) then changing the number of chains/buckets may leave you with one hot block on each of two hash chains - which is a damage-limiting approach.

However, if you want an alternative method of separating two index root blocks that are on the same hash chain, you could rebuild one of the indexes. The block address is used in the hashing function so if you rebuild the index, which makes its root block address change, the index root block usually moves to a different hash chain.

Of course, you shouldn't fiddle with underscore parameters unless Oracle Support tells you to do so - but in this case, there are a couple of extra details to consider. a) although the value of _db_block_hash_buckets is set to twice the number of buffers in Oracle 8, the actual number of hash buckets (chains) IS actually set to the next prime number. You can see this from:

    oradebug dumpvar sga kcbnhb

On my small 8,1,7,4 system, db_block_buffers is 1,000 and _db_block_hash_buckets is 2,000 - but this value (kcbnhb - Number of Hash Buckets) is 000007D3, which happens to be 2003 - the next prime number just larger than twice the number of buffers.

Of course it is possible that (a) this number is lying, and/or (b) only 2,000 of the buckets are used even though there are 2,003 of them.

Never mind - dump the buffer cache at level 4, change the _db_block_hash_buckets parameter to 2,003, bounce the database and run it for a bit, then do another buffer dump at level 4. Does this give us any hints ?

Well in both dumps, block 2/3074 and block 1/9982 are on chain 1992 - the hashing function has put these blocks in the same chain, even though we changed _db_block_hash_buckets. It is a fairly safe conclusion that Oracle is using the kcbnhb, and not the displayed parameter.

But we haven't finished with this paragraph. The author suggests we divide db_cache_size by db_block_size to find the number of blocks. But the db_cache_size is an Oracle 9 parameter, and this is supposed to be addressing an Oracle 8 issue - according to the  para (8). Of course, since the db_cache_size is an Oracle 9 parameter - shouldn't we also have to include the db_2k_cache_size, db_keep_cache_size etc. since all those buffered blocks have to be on hash chains somewhere - perhaps the easy option is to: select buffers from v$buffer_pool;

Moreover this formula is going to give you the wrong answer anyway - even in the simplest case. Under Oracle 9, the SGA is granulated, using 4M, 8M or 16M granules depending on platform and total SGA size. The buffer header array (X$BH) was 'outside' the buffer cache in Oracle 8 - but under Oracle 9, X$BH is localised so that each granule of buffers holds its own section of x$bh - consequently you have to allow about 188 bytes per buffer - so a closer figure would be given by (db_cache_size / (db_block_size + 188)).

Para (8) - as we have seen, the relevant value is correctly set to a prime in Oracle 8 (or, to be totally fair, is set correctly in all the recent versions of Oracle 8 that I have seen). So where does the author find the hot index root block problem ? My best guess is the bug in 8.1.7 and 9.0.1.2 that was fixed in 8.1.7.3 and 9.0.1.3 where the index root block was pinned and unpinned much too frequently under certain conditions (inner table of nested loop with index range scan, I believe). Pinning, requires a latch get, as does unpinning.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: <ORACLE-L_at_fatcity.com> Sent: 26 February 2003 14:18

>
> So -
>
> My shortlist of "Don'ts"
>
> 1) Don't try to do things by lists.
>
>
> Regards

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).