Oracle tables i/o tracking monitoring

If you are using STATSPACK on Oracle release 2 or beyond is in use, I/O can be tracked for specific Oracle tables and indexes. This allows the DBA to see the specific sources of physical I/O. In Oracle release 2, the most notable enhancements to Oracle STATSPACK are:

Using the level 7 STATSPACK collection, it is now possible to track I/O at the individual segment level, showing disk I/O for any Oracle table or index.

A level 7 STATSPACK snapshot collects all segment level statistics, including logical and physical reads, row lock, and buffer busy waits. The ability to track buffer busy waits at the table and index level is especially important for removing segment header contention.

To get an idea of which objects have been the favorite of a database's SQL calls, the following toptables.sql query, which retrieves the top 100 objects as determined by SQL statement execution can be run:

-- *************************************************

-- This script is free for non-commercial purposes

-- with no warranties. Use at your own risk.

-- To license this script for a commercial purpose,

-- contact info@rampant.cc

-- *************************************************

select

table_owner "table owner",

table_name "table name",

command "command issued",

0 - executions "executions",

disk_reads "disk reads",

gets "buffer gets",

rows_processed "rows processed"

from

(select

distinct executions,

command,

table_owner,

table_name,

gets,

rows_processed,

disk_reads

from

(select

decode (a.command_type ,

2, 'insert ' ,

3,'select ',

6, 'update ' ,

7, 'delete ' ,

26,'table lock ') command ,

c.owner table_owner,

c.name table_name ,

sum(a.disk_reads) disk_reads ,

sum(0 - a.executions) executions ,

sum(a.buffer_gets) gets ,

sum(a.rows_processed) rows_processed

from

sys.v_$sql a ,

sys.v_$object_dependency b ,

sys.v_$db_object_cache c

where

a.command_type in (2,3,6,7,26)and

b.from_address = a.address and

b.to_owner = c.owner and

b.to_name= c.name and

c.type = 'table' and

c.owner not in ('SYS','SYSTEM')

group by

a.command_type , c.owner , c.name ) )

where

rownum <= 100;

One way to uncover a potential bottleneck for any system is to observe a single table with a lot of DML activity. Other things to consider when reviewing output from this query include:

§ Small, regularly-accessed tables should be reviewed as candidates for the Oracle KEEP buffer pool in Oracle8i and higher or be set to CACHE for Oracle7 and higher.

§ To determine if they can be partitioned, large tables that are often accessed and scanned should be reviewed. Partitioning can reduce scan times but only one or a handful of partitions can be scanned instead of the entire table.

If the DBA suspects that there are unnecessary large-table full-table scans, suspicions can be validated by making use of the new v_$sql_plan view. The largescan9i.sql query uses this new view to reveal which large tables, defined in the query as tables over 1MB, are being scanned in the database:

select

table_owner,

table_name,

table_type,

size_kb,

statement_count,

reference_count,

executions,

executions * reference_count total_scans

from

(select

a.object_owner table_owner,

a.object_name table_name,

b.segment_type table_type,

b.bytes / 1024 size_kb,

sum(c.executions ) executions,

count( distinct a.hash_value ) statement_count,

count( * ) reference_count

from

sys.v_$sql_plan a,

sys.dba_segments b,

sys.v_$sql c

where

a.object_owner (+) = b.owner

and

a.object_name (+) = b.segment_name

and

b.segment_type IN ('TABLE', 'TABLE PARTITION')

and

a.operation LIKE '%TABLE%'

and

a.options = 'FULL'

and

a.hash_value = c.hash_value

and

b.bytes / 1024 > 1024

group by

a.object_owner,

a.object_name,

a.operation,

b.bytes / 1024,

b.segment_type

order by

4 desc, 1, 2 );

Once one uncovers what is being accessed the most, one can then attempt to reveal who is causing all the activity.

Tracking I/O for specific Tables