Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

Donald K. Burleson

Oracle Tips

Tracking I/O for specific Tables

 

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:

§       Track reads or writes for specific segments

§       Track buffer busy waits by table or index

§       Collect historical SQL execution plans using the level 8 snapshot

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.

 

SQL> execute statspack.snap (i_snap_level=>7, i_modify_parameter=>'true');

 

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:

 

<      toptables.sql

 

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

-- Copyright © 2005 by Rampant TechPress

-- 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;

 SEE CODE DEPOT FOR FULL SCRIPTS

Output from the above query might look like this:

 

 

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:

 

<      largescan9i.sql

 

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 );  

 SEE CODE DEPOT FOR FULL SCRIPTS

The following is sample output:

 

 

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

SEE CODE DEPOT FOR FULL SCRIPTS


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 


”call

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.