sample_io (catio.sql) question

[efrijters]

Hi guys,

I'm trying to get some info on the IO per object, so I installed catio.sql.
Next I ran "exec sample_io(5,60);" a few times at busy and not-so-busy moments. But when I look at the result-view "IO_PER_OBJECT", I always get the same result:

SQL> select * from sys.io_per_object;

NAME             PARTITION_NAME  KIND             IO_TYPE              BLOCKS_READ
---------------- --------------- ---------------- -------------------- -----------
C_TS#                            CLUSTER          sequential                     1

Database: 8.1.7.0.0 on Wi(n)dows 2000 Adv.Server
Buffer cache:

SQL> select * from vw_ideal_buffer_cache ;

DATE     BUFFER_POOL        CURRENT      IDEAL
-------- --------------- ---------- ----------
...
13-04-04 DEFAULT               8192       7256

I think I'm following the correct procedure... How must I interpret this info?

I hope someone can help me...

[marist89]

I've never had any luck with catio. As far as I remember, you're pulling stuff off the backend of the LRU. This inherently under-reports objects that are used quite frequently. There are a couple of notes on metalink that explain it, although I can't put my browser on a good one right now...

[efrijters]

Whoops... Metalink mentions more errors in the procedure :

In the declare-section of 'sample_io' I already altered
tail_of_lru raw(4);
this_tail_of_lru raw(4);
into
tail_of_lru raw(8);
this_tail_of_lru raw(8);

and now I altered:
tail_of_lru := hextoraw('4000000000'); -- init to very large value
into
tail_of_lru := hextoraw('ffffffff'); -- init to very large value

I'll test again and post results...

[efrijters]

Well... that bug-fix was a big improvement: going from 1 result row to zero rows

I just tested it for 10 minutes with a 60 second interval, but view io_per_object returns nothing, nada, niente... Grrrr!

[possible explanation]
Maybe my buffer cache is big enough and no data was kicked out during the sampling... My buffer cache is probably well-tuned
[/possible explanation]

Is there another way to monitor I/O at object-level?

PS: thanks Marist89 for your reply!