Re: Lookup/Code table as hot block
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 20 Apr 2006 09:04:19 +0100
Why should references to PKs / IOT have no relevance
just because the table has only 3 rows ?
"Hot Block" may mean cache buffers chains latch -
if you have a tablescan on a 3 row table, you hit
the segment header twice, and the data block once
for a total of 3 CR gets and 6 CBC latch hits. The
segment header may be the hot block, and the presence
of a PK may reduce the problem.
We need to know something about the SQL that
accesses the table, and the access path, and the
explanation of "hot block" before we can make
any sensible suggestions - but if the problem is a
combination of buffer busy waits and CBC latch
contention, then possibly you best option is to
recreate the table in a single table hash cluster
with enough KEYs and a SIZE set to ensure
that each of your rows is in a separate block.
AND have a PK defined on the table. In this
way, a query for a row can do a single-latch
CR to get the row it wants - and any one block
will be subject to a third of the current accesses.
Regards
Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
----- Original Message -----
Date: Wed, 19 Apr 2006 21:06:33 -0400
From: "Ranko Mosic" <ranko.mosic@xxxxxxxxx>
Subject: Re: Lookup/Code table as hot block
Thanks all for their responses. I apologize for not having more detail -
this is yet another
4 pm issue. Version is 9.2.0.x. This table has only 3 ( three ) rows, so any
points relating to pk/indexes/IOT etc don't apply here.
Why is this bad thing ? I don't know, I asked client the same question, but
they seem sure
they are seeing problem. I didn't have chance yet to have direct look at the
problem.
Caching is current idea and also suggested by Oracle Consulting.
I am not sure how much it will help though - block is cached anyway, so
buffer cache latch will be replaced with shared pool latch. Spreading
accross 3 blocks is also considered.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
