Re: Lookup/Code table as hot block

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_at_gmail.com>
  > 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.
  >
  

--
http://www.freelists.org/webpage/oracle-l