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 


 

 

 


 

 

 
 

Oracle Session I/O

Oracle Tips by Burleson Consulting

Find the Current I/O Session Bandits

To see which users are impacting the system in undesirable ways, the first thing to check is the connected sessions, especially if there are current complaints of poor performance.  In this case, there are a few different avenues that can be taken.

 

Getting an idea of the percentage that each session has taken up with respect to I/O is one of the first steps.

 

If any session consumes 50% or more of the total I/O, that session and its SQL should be investigated further to determine the activities in which it is engaged.  If the DBA is just concerned with physical I/O, the physpctio.sql query will provide the information needed:

 

<      physpctio.sql

 

 

select

   sid,

   username,

   round(100 * total_user_io/total_io,2) tot_io_pct

from

(select

     b.sid sid,

     nvl(b.username,p.name) username,

     sum(value) total_user_io

 from

     sys.v_$statname c, 

     sys.v_$sesstat a,

     sys.v_$session b,

     sys.v_$bgprocess p

 where

      a.statistic#=c.statistic# and

      p.paddr (+) = b.paddr and

      b.sid=a.sid and

      c.name in ('physical reads',

                 'physical writes',

                 'physical writes direct',

                 'physical reads direct',

                 'physical writes direct (lob)',

                 'physical reads direct (lob)')

group by

      b.sid, nvl(b.username,p.name)),

(select

      sum(value) total_io

 from

      sys.v_$statname c,

      sys.v_$sesstat a

 where

SEE CODE DEPOT FOR FULL SCRIPTS

order by

      3 desc;

 

If the DBA wants to see the total I/O picture, the totpctio.sql query should be used instead:

 

<      totpctio.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

       SID,

       USERNAME,

       ROUND(100 * TOTAL_USER_IO/TOTAL_IO,2) TOT_IO_PCT

FROM

(SELECT

        b.SID SID,

        nvl(b.USERNAME,p.NAME) USERNAME,

        SUM(VALUE) TOTAL_USER_IO

FROM

     sys.V_$STATNAME c, 

     sys.V_$SESSTAT a,

     sys.V_$SESSION b,

     sys.v_$bgprocess p

WHERE

      a.STATISTIC#=c.STATISTIC# and

      p.paddr (+) = b.paddr and

      b.SID=a.SID and

      c.NAME in ('physical reads','physical writes',

                 'consistent changes','consistent gets',

                 'db block gets','db block changes',

                 'physical writes direct',

                 'physical reads direct',

                 'physical writes direct (lob)',

                 'physical reads direct (lob)')

GROUP BY

      b.SID, nvl(b.USERNAME,p.name)),

(select

        sum(value) TOTAL_IO

from   

        sys.V_$STATNAME c,

        sys.V_$SESSTAT a

WHERE  

        a.STATISTIC#=c.STATISTIC# and

SEE CODE DEPOT FOR FULL SCRIPTS

ORDER BY

        3 DESC;

 

The output might resemble the following, regardless of which query is used:

 

SID  USERNAME       TOT_IO_PCT

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

9    USR1               71.26

20   SYS                15.76

5    SMON                7.11

2    DBWR                4.28

12   SYS                 1.42

6    RECO                 .12

7    SNP0                 .01

10   SNP3                 .01

11   SNP4                 .01

8    SNP1                 .01

1    PMON                   0

3    ARCH                   0

4    LGWR                   0

 

Following the above example, a DBA would indeed be wise to study the USR1 session to see what SQL calls were made.  The above queries are excellent resources that can be used to quickly pinpoint problem I/O sessions.

 

To see all the actual I/O numbers, the rather large topiousers.sql query can be used if the goal is to gather more detail with respect to the top I/O session in a database:

 

<      topiousers.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

     b.sid sid,

     decode (b.username,null,e.name,b.username)

     user_name,

     d.spid os_id,

     b.machine machine_name,

     to_char(logon_time,'mm/dd/yy hh:mi:ss pm')

     logon_time,

     (sum(decode(c.name,'physical reads',value,0))

     +

     sum(decode(c.name,'physical writes',value,0))

     +

     sum(decode(c.name,

     'physical writes direct',value,0)) +

     sum(decode(c.name,

     'physical writes direct (lob)',value,0)) +

     sum(decode(c.name,

     'physical reads direct (lob)',value,0)) +

     sum(decode(c.name,

     'physical reads direct',value,0)))

     total_physical_io,

     (sum(decode(c.name,'db block gets',value,0))

     +

     sum(decode(c.name,

     'db block changes',value,0))  +

     sum(decode(c.name,'consistent changes',value,0)) +

     sum(decode(c.name,'consistent gets',value,0)) )

     total_logical_io,

     100 100 *(round ((sum (decode

     (c.name, 'physical reads', value, 0))

     sum (decode (c.name,

     'physical reads direct', value, 0))) /

     (sum (decode (c.name, 'db block gets',

    value, 1)) +

    sum (decode (c.name, 'consistent gets',

     value, 0))),3)) hit_ratio,

    sum(decode(c.name,'sorts (disk)',value,0))

    disk_sorts,

    sum(decode(c.name,'sorts (memory)',value,0))

    memory_sorts,

    sum(decode(c.name,'sorts (rows)',value,0))

    rows_sorted,

    sum(decode(c.name,'user commits',value,0))

    commits,

    sum(decode(c.name,'user rollbacks',value,0))

    rollbacks,

    sum(decode(c.name,'execute count',value,0))

    executions,

    sum(decode(c.name,'physical reads',value,0))

    physical_reads,

    sum(decode(c.name,'db block gets',value,0))

    db_block_gets,

    sum(decode(c.name,'consistent gets',value,0))

    consistent_gets,

    sum(decode(c.name,'consistent changes',value,0))

    consistent_changes

from   

   sys.v_$sesstat a,

   sys.v_$session b,

   sys.v_$statname c,

   sys.v_$process d,

   sys.v_$bgprocess e

where

   a.statistic#=c.statistic#

and

   b.sid=a.sid 

and

   d.addr = b.paddr

and

   e.paddr (+) = b.paddr 

and

   c.name in

   ('physical reads',

    'physical writes',

    'physical writes direct',

    'physical reads direct',

SEE CODE DEPOT FOR FULL SCRIPTS

)

group by

   b.sid,

   d.spid,

   decode (b.username,null,e.name,b.username),

         b.machine,

         to_char(logon_time,'mm/dd/yy hh:mi:ss pm')

order by

   6 desc;

 

Output from the query above could look like the following:

 

 

A query such as this reveals details about the actual raw I/O numbers for each connected session.  Armed with this information, it is then possible to drill down into each heavy-hitting I/O session to evaluate what SQL calls are made and which sets of SQL are the I/O hogs. 

 

Even though troubleshooting I/O from a user standpoint has been explained, one should not forget about all the system activity caused by Oracle itself.

 

A cursory, global check of the system level wait events should be performed 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:

 

<      syswaits.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

       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

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 ',

SEE CODE DEPOT FOR FULL SCRIPTS

        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 the DBA has a feel for the I/O numbers at a global level, it is possible to begin working further down into what is really going on below the surface.

 

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 30%-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

 


 

 

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