Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, RMZ.

Asked: June 02, 2004 - 9:22 am UTC

Last updated: June 18, 2008 - 1:04 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom
are there a way to find out the hot blocks on the database
monitoring v$session_wait for events db_file_scattered_read and db_file_sequential read ,use the p1 and p2 and use the following query to determine the object

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = <AFN>
and <BL> between block_id AND block_id + blocks - 1 ;
but in case i didnt see events db_file_scattered_read and db_file_sequential read in v$session_wait how to point to the hot objects?

Thanks in advance



and Tom said...

9i allows you to identify hot segments easily in v$ tables. prior to that it is hit or miss, pure and simple.

If you don't see those waits, you are in the "miss" stage.


A hot block is going to manifest itself as a cache buffers chains latch -- it is doubtful you would be performing IO to read in a hot block, it would be definition be in the cache.

see support note:
Article-ID: <Note:163424.1>

for some details.


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Hatem

Hatem, June 09, 2004 - 10:02 am UTC

Hi Tom,
whats the best way to eliminate or reduce the contention on those hot blocks?

Thanks

Tom Kyte
June 09, 2004 - 10:14 am UTC

depends on why the block is hot. One needs to understand the application and how it uses the data in order to come up with a method.

For example "hot index root block" -- possible solution is partitioning (you now have N index blocks)

"hot right hand side of index block" -- possible solutions are reverse key indexes (if application doesn't range scan) or partitioning

"hot block in a small table that gets updated lots" -- table has 10 rows, all 10 rows fit into a single block. block gets slammed. possible solution is to "alter table minimize records_per_block" and have just 2 records/block -- or set pctfree really high to go one record per block.


"application level change" comes into play as well -- change the way the application accesses the data.

A reader, February 02, 2005 - 2:52 pm UTC

Tom,

Referring to the link below from DB

</code> http://www.builderau.com.au/architect/database/0,39024547,20264439,00.htm <code>

<QUOTE>
Use large data blocks on indexes to minimise disk I/O
Oracle index access performs best with 16K and 32K Oracle block sizes. You should consult your application-specific OS documentation and create index tablespaces of the largest value for your environment.
</QUOTE>

Isn't it recommended to have a smaller or optimal size for index blocks rather than big as that make the block hot?

Thanks.

Tom Kyte
February 03, 2005 - 1:29 am UTC

consider the source, and ask them "so -- where are the 'proofs in the pudding' and is it always a good idea, when might it be a bad idea, and who proved that oracle index access performs best with 16/32k blocks, and is it always true that it will minimize disk i/o, are there cases where it won't minimize, in fact would cause more"

........

Too many CR block clones.

Sai, February 03, 2005 - 4:10 am UTC

Tom,

We have a table with one row, which will be accessed as "select for update" and simple selects executing at 200 times a second. We are seeing "latch free" (cache buffer chains) waitevent at 50% of the total waits. There were more than 50,000 CR buffer block images of this table block. For some reason, "db_block_max_cr_dba" isn't restricting clones at 6.

We are trying to change this table to KEEP pool with only 50 blocks assigned to it, hence maximum of 50 clones. By the way, we are working on a fix for this problem at application level.

Any other approach?

Is it possible to flush this table buffer blocks without truncating the table?

Tom Kyte
February 03, 2005 - 1:34 pm UTC


you have filed a tar right.

short of putting the table into it's own tablespace and offline/onlining the tablespace... no, i don't see a way to flush it.

A reader, February 03, 2005 - 10:38 am UTC

So glad to have this website where all claims are based on proofs and examples.

Thanks for all your help to Oracle Community.





How it works?

Marat, June 18, 2008 - 1:22 am UTC

Dear Tom,
we have a problem with cache buffers chains sleeps, and I think the cause is in hot blocks.
So, I have made a test script to observe how it works. I run many sessions concurrently (about 50) which execute the same code accessing the same row in the same table: 

declare
    n number;
begin
    for i in 1..100000 loop
            select empno 
               into n
              from emp 
             where empno=7369;
    end loop;
end;




Then I try to identify hot blocks. To accomplish this I use scripts from Metalink, Doc Id 163424.1 ("How To Identify a Hot Block Within The Database Buffer Cache") : 


/*
First determine which latch id(ADDR) are interesting by examining the number of
sleeps for this latch. The higher the sleep count, the more interesting the
latch id(ADDR) is:
*/
select CHILD#  "cCHILD"
     ,      ADDR    "sADDR"
     ,      GETS    "sGETS"
     ,      MISSES  "sMISSES"
     ,      SLEEPS  "sSLEEPS" 
     from v$latch_children 
     where name = 'cache buffers chains'
     order by 5, 1, 2, 3;


/*
The query below should be run just after determining the ADDR with
the highest sleep count.
*/

column segment_name format a35
     select /*+ RULE */
       e.owner ||'.'|| e.segment_name  segment_name,
       e.extent_id  extent#,
       x.dbablk - e.block_id + 1  block#,
       x.tch,
       l.child#
     from
       sys.v$latch_children  l,
       sys.x$bh  x,
       sys.dba_extents  e
     where
       x.hladdr  = :ADDR and
       e.file_id = x.file# and
       x.hladdr = l.addr and
       x.dbablk between e.block_id and e.block_id + e.blocks -1
     order by x.tch desc ;



I noticed touch count (x$bh.tch) after each of several runing my script.
What I see is that the block touch count increases in just few points or even zero. How is it possible? I thought it would be increased by 1 every iteration of the loop. Is this the right way to find hot blocks? We use Oracle 8.1.7.4

Thank you!

Tom Kyte
June 18, 2008 - 1:04 pm UTC

the touch count is modified using unlatched access (not serialized) so in a heavy multi-user situation, many people might be adding 1 at the same time meaning they all take it from 50 to 51 - instead of 50, to 51, to 52 to .....

and it is going to increase/decrease/whatever over time as well since it is a function of time as well as touches. it is not a precise accurate number, it is what we use to age things around.

<quote src=Expert Oracle Database Architecture>

You can even watch as Oracle increments the touch count on a block that you query repeatedly. We will use the magic table ¿DUAL¿ in this example ¿ we know it is a one row, one column table. What we need to know about it is the block information for that single row. The built-in DBMS_ROWID package is good for getting that. Additionally, since we query ROWID from DUAL, we are making Oracle actually read the real DUAL table from the buffer cache, not the ¿virtual¿ DUAL table enhancement of Oracle 10g

NOTE: Prior to Oracle 10g, querying DUAL would incur a full table scan of a real table named DUAL stored in the data dictionary. If you set autotrace on and query ¿SELECT DUMMY FROM DUAL¿, you will observe some IO in all releases of Oracle (consistent gets). In 9i and before, if you query ¿SELECT SYSDATE FROM DUAL¿, you will also see real IO occur. However, in Oracle 10g, that SELECT SYSDATE is recognized as not needing to actually query the DUAL table (since we are not asking for the column or rowid from dual) and is done in a manner similar to calling a function. The table is not full scanned, just SYSDATE is returned to the application. This small change can dramatically decrease the amount of consistent gets a system that uses DUAL heavily performs.

So every time we run the following query, we should be hitting the real DUAL table:

sys@ORA10G> select tch, file#, dbablk
  2    from x$bh
  3   where (dbablk,file#) in
  4   (select dbms_rowid.rowid_block_number(rowid),
  5           dbms_rowid.rowid_to_absolute_fno(rowid,'SYS','DUAL')
  6          from dual)
  7     and state = 1
  8  /
 
       TCH      FILE#     DBABLK
---------- ---------- ----------
        18          1       1858
 
sys@ORA10G> /
 
       TCH      FILE#     DBABLK
---------- ---------- ----------
        19          1       1858
 
sys@ORA10G> /
 
       TCH      FILE#     DBABLK
---------- ---------- ----------
        19          1       1858
 
sys@ORA10G> /
 
       TCH      FILE#     DBABLK
---------- ---------- ----------
        20          1       1858



Almost every time I touch that block, the counter goes up. Note that I said ¿almost¿, the touch count is, by design, an imprecise number. It will be incremented most of the times, but it is not considered important that the number be 100% accurate, it is close. If you run this on your system, you may well see different numbers.

</quote>
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.