Re: How to Determine Table I/O ?

From: kyle hailey <oraperf_at_yahoo.com>
Date: Thu, 01 Mar 2001 23:23:19 -0800
Message-ID: <5jiu9t8m13suid1glk38cd5hcj1g6tagn1@4ax.com>

There are several ways. If you want to find out what objects are getting read off disk you could poll v$session_waits, which is what I'd do. There is and example of this on

http://www.geocities.com/oraperf/seminar/collect.txt

This script does alot more than that, but you could just set the variables   

    SLOW_SAMPLE=""
    FAST_SAMPLE="waits"

and this will output a file with all the v$session_waits every 5 seconds. You can then search through for scattered reads and sequential reads by reading the block# and file# from the wait events.

Another way would be to use the script in ?/rdbms/admin/catio.sql called sample_io. The only problem is, as far as I know, this script has never worked. The script is suppose to sample the end of the LRU and see what new blocks are read in. It seems to me from emperical results that they are sampling the head instead of the tail! THere are also a couple of blatent mistakes like their check for duplicate blocks uses "and" instead of "or" and their check for scattered verses sequential reads was backwords (maybe they were confused by the naming convetions - I'm following those from the wait stats), not to mention all the problems with raws that seem to be needless, at least on v8.1. Here is a version that I wroted that seems to work fine for me on 8.1:

drop table my_io_histogram;
create table my_io_histogram ( fileid number,

                                  blockid number, 
                                  io_type varchar2(20));

create or replace procedure my_sample_io (run_secs number, sleep_secs number)
is

   tail varchar2(10);
   taddr varchar2(10);

   file     number;
   block    number;
   flag     number;
   ofile    number;
   oblock   number;
   tot_secs number;

begin

   tot_secs := 0;
   ofile    := 0;
   oblock   := 0;

   select max(NXT_REPL) into tail from x$bh;    select addr into taddr from x$bh where nxt_repl = tail;    while ( tot_secs < run_secs ) loop

     begin
        select file#, dbablk, flag into file, block, flag from x\$bh 
            where addr = taddr and file# !=0 and nxt_repl != tail;
     exception when no_data_found then null;
     end;
     begin
        select addr into  taddr from x$bh where nxt_repl = tail;
     exception when no_data_found then null;
     end;
     if (file != ofile or block != oblock) then
       dbms_output.put_line(to_char(file)||' '||to_char(block)||'
insert');
       insert into my_io_histogram
            values (file, block,

decode(to_number(bitand(flag,524288)),0,'sequential','scattered'));
       commit;
     end if;
     ofile := file;
     oblock := block;
     dbms_lock.sleep(sleep_secs);
     tot_secs:=tot_secs+sleep_secs;

   end loop;
  end;
/

Best

Kyle Hailey

On Wed, 28 Feb 2001 19:01:53 -0800, "g.t." <greg_at_NOSPAMadvmkt.com> wrote:

> Hello All,
> From Bstat/Estat (and other methods) I can tell which datafile is
>getting hit the hardest. How do you tell which tables(or indexes) within
>that file are getting hit the most?
>
>TIA,
>Gregg
>
Received on Fri Mar 02 2001 - 01:23:19 CST