Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Examine Oracle I/O statistics

Oracle Tips by Burleson Consulting
June 22, 2003

 

The first step in unraveling any I/O puzzles in your database is to make a quick check of some of the global database I/O metrics.  A query such as the globiostats.sql  script can be used to get a bird's eye view of a database's I/O:

select
   name,
   value
from
   sys.v_$sysstat
where
   name in
    ('consistent changes',
     'consistent gets',
     'db block changes',
     'db block gets',
     'physical reads',
     'physical writes',
     'sorts (disk)',
     'user commits',
     'user rollbacks'
    )
 order by
1;

 

The script queries the sys.v_$sysstat view and output from the query might look like the following:

NAME                    VALUE

------------------------------

consistent changes           1

consistent gets          70983

db block changes           243

db block gets              612

physical reads           11591

physical writes             52

sorts (disk)                 0

user commits                26

user rollbacks               1

Although there are some database experts who do not believe the buffer cache hit ratio  is of much value anymore (and there are valid reasons for assuming such a stance), you can still perform a cursory check to get an idea of overall disk I/O activity by using the buffratio.sql  script:

The full listing of the buffratio.sql  script can be obtained from the online Code Depot.  see code depot for full script

This script also queries the sys.v_$sysstat view and some quick things to look for in the statistics include:

  • Increasing numbers of physical reads and a low hit ratio may indicate insufficient settings for db_block_buffers  or db_cache_size (Oracle).  The hit ratio reading in particular should be observed over a decent time period to see if the ratio is representative of the database?s ?personality?, so keep in mind that readings below the normal ?rule of thumb? (90%) can be OK.

  • High volumes of disk sorts could be indicative of either a setting for sort_area_size  (Oracle8i and below) that is too low or unnecessary sort activities.  Seeing large numbers of physical writes in a read-only database may also be indicative of excessive sorting.

  • Large numbers of user rollbacks can be undesirable, since it indicates that user transactions are not completing for one reason or another.  

You should also do a cursory, global check of the system-level wait events to get an idea of the I/O bottlenecks that may be occurring.  A script like the syswaits.sql  script can be used to perform such a check:

select
       event,
       total_waits,
       round(100 * (total_waits / sum_waits),2) pct_tot_waits,
       time_wait_sec,
       round(100 * (time_wait_sec / sum_secs),2) pct_secs_waits,
       total_timeouts,
       avg_wait_sec
from
(select
       event,
       total_waits,
       round((time_waited / 100),2) time_wait_sec,
       total_timeouts,
       round((average_wait / 100),2) avg_wait_sec
from
      sys.v_$system_event
see code depot for full script
where
      event not in
      ('lock element cleanup
',
       'pmon timer
',
       'rdbms ipc message
',
       'smon timer
',
       'SQL*Net message from client
',
       'SQL*Net break/reset to client
',
       'SQL*Net message to client
',
       'SQL*Net more data to client
',
       'dispatcher timer
',
       'Null event
',
       'parallel query dequeue wait
',
       'parallel query idle wait - Slaves',
       'pipe get
',
       'PL/SQL lock timer
',
       'slave wait '
       'virtual circuit status,
       'WMON goes to sleep') and
       event not like 'DFS%' and
       event not like 'KXFX%'),
(select
        sum(total_waits) sum_waits,
        sum(round((time_waited / 100),2)) sum_secs
 from
        sys.v_$system_event
 where 
        event not in
       ('lock element cleanup
',
        'pmon timer
',
        'rdbms ipc message
',
        'smon timer
',
        'SQL*Net message from client
',
        'SQL*Net break/reset to client
',
        'SQL*Net message to client
',
        'SQL*Net more data to client
',
        'dispatcher timer
',
        'Null event
',
        'parallel query dequeue wait
',
        'parallel query idle wait - Slaves
',
        'pipe get
',
        'PL/SQL lock timer
',
        'slave wait
',
        'virtual circuit status
',
        'WMON goes to sleep') and
        event not like 'DFS%' and
        event not like 'KXFX%')
order by
   2 desc;

 

The script queries the sys.v_$system_event view and here are a few quick things to note about the output from the waits SQL script:

  • Numerous waits for the db file scattered read  event may indicate a problem with table scans

  • Many waits for the latch free  event could indicate excessive amounts of logical I/O activity

  • High wait times for the enqueue  event pinpoints a problem with lock contention

Once you have a feel for the I/O numbers at a global level, you can begin to work your way further down into what is really going on under the covers. 

 

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

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


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational