Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: High CPU and IO Waits

Re: High CPU and IO Waits

From: <>
Date: 1 Apr 2005 18:01:48 -0800
Message-ID: <>

Well, yeah, I've seen high CPU and high IO waits have nothing to do with a SAN and everything to do with mistuned Oracle. I've also seen it on extremely busy systems that were properly tuned.

You need to adopt a tuning methodology - any tuning methodology - for anyone here to help you.

Start with the Oracle Performance Tuning Guide, then look at running statspacks. Google this group for book recommendations. Figure out how to have a better relationship with your DBAs. See the faqs. Look at the various sites available - see here for some starting points:

Some specific things to evaluate:

Are you using CBO? What have you done to be sure it is functioning properly? Analyzed?
Which SQL statments are taking up lots of resources? Have they been tuned properly? (Sometimes one lost index makes all the difference in the world, for the whole system).
Have you checked waits for log writing and archiving? You need to figure out where the bottlenecks are. If you are writing redo logs to a RAID five through a controller choked by other requests, that would be wrong, big-time.
Is anyone complaining? Or everyone?
What is your server doing? DW and OLTP have different reqs. Have things gotten worse? You are running an obsolete version, maybe somewhere along the line normal db maintenance has gotten lost? DMT or LMT? If you are pounding the data dictionary for segment extensions, that could be a problem.
You should check if there is contention for undo headers, and know how much I/O is going where. I've found normally of the usual dbf files, the rollback segment tablespace gets the most workout. If you have left the ts's in hot backup mode that could be nasty to your redo logs.

column xn1 format 9999999
column xv1 new_value xxv1 noprint

set head on

select class, count
from v$waitstat
where class in ('system undo header', 'system undo block',

                 'undo header',        'undo block'          )


set head off

select 'Total requests = '||sum(count) xn1, sum(count) xv1 from v$waitstat

select 'Contention for system undo header = '||

       (round(count/(&xxv1+0.00000000001),4)) * 100||'%' from v$waitstat
where class = 'system undo header'

select 'Contention for system undo block = '||

       (round(count/(&xxv1+0.00000000001),4)) * 100||'%' from v$waitstat
where class = 'system undo block'

select 'Contention for undo header = '||

       (round(count/(&xxv1+0.00000000001),4)) * 100||'%' from v$waitstat
where class = 'undo header'

select 'Contention for undo block = '||

       (round(count/(&xxv1+0.00000000001),4)) * 100||'%' from v$waitstat
where class = 'undo block'

select name||' = '||value||chr(10)
from v$sysstat
where name = 'redo log space requests'

select statistic#, name, value
from v$sysstat
where value > 0

select sum(decode(,'parse count',value,0)) /

       sum(decode(,'opened cursors cumulative',value,.00000000001)) pcc,

       sum(decode(,'recursive calls',value,0)) /
       sum(decode(,'opened cursors
cumulative',value,.00000000001)) rcc,
       (1-(sum(decode(,'physical reads',value,0)) /
       sum(decode(,'db block gets',value,.00000000001)) +
  sum(decode(,'consistent gets',value,0))) * (-1)) hr,
       sum(decode(,'physical reads',value,0)) /
       sum(decode(,'physical writes',value,.00000000001)) rwr,
       (sum(decode(,'table scan blocks gotten',value,0)) -
       sum(decode(,'table scans (short tables)',value,0)) * 4) /
       sum(decode(,'table scans (long
from v$sysstat a

set space 1

column pbr       format 99999999  heading 'Physical|Blk Read'
column pbw       format 999999    heading 'Physical|Blks Wrtn'
column pyr       format 999999    heading 'Physical|Reads'
column readtim   format 99999999  heading 'Read|Time'
column name      format a40       heading 'DataFile Name'
column writetim  format 99999999  heading 'Write|Time'

ttitle center 'Tablespace Report' skip 2

compute sum of f.phyblkrd, f.phyblkwrt on report

select name, f.phyblkrd pbr, f.phyblkwrt pbw,

       f.readtim, f.writetim
from v$filestat f, v$datafile fs
where f.file# = fs.file#
order by

 ttitle center 'Wait Statistics for the Instance' skip 2

column class  heading 'Class Type'
column count  heading 'Times Waited'  format 99,999,999
column time   heading 'Total Times'   format 99,999,999

select class, count, time
from v$waitstat
where count > 0
order by class


ttitle off;

set linesize 80

column extents    format 999        heading 'Extents'
column rssize     format 999,999,999  heading 'Size in|Bytes'
column optsize    format 999,999,999  heading 'Optimal|Size'
column hwmsize    format 99,999,999   heading 'High Water|Mark'
column shrinks    format 9,999        heading 'Num of|Shrinks'
column wraps      format 9,999        heading 'Num of|Wraps'
column extends    format 999,999      heading 'Num of|Extends'
column aveactive format 999,999,999 heading 'Average size|Active Extents'
column rownum noprint

select rssize, optsize, hwmsize,

       shrinks, wraps, extends, aveactive from v$rollstat
order by rownum

And of course, show sga.

You can get a lot of info from OEM, too - might be worth it to download the 9.2 version.


-- is bogus.
April fools!
Received on Fri Apr 01 2005 - 20:01:48 CST

Original text of this message