| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: High CPU and IO Waits
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: http://www.dbaoracle.net/readme-cdos.htm
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(a.name,'parse count',value,0)) /
sum(decode(a.name,'opened cursors cumulative',value,.00000000001)) pcc,
sum(decode(a.name,'recursive calls',value,0)) /
sum(decode(a.name,'opened cursors
cumulative',value,.00000000001)) rcc,
(1-(sum(decode(a.name,'physical reads',value,0)) /
sum(decode(a.name,'db block gets',value,.00000000001)) +
sum(decode(a.name,'consistent gets',value,0))) * (-1)) hr,
sum(decode(a.name,'physical reads',value,0)) /
sum(decode(a.name,'physical writes',value,.00000000001)) rwr,
(sum(decode(a.name,'table scan blocks gotten',value,0)) -
sum(decode(a.name,'table scans (short tables)',value,0)) * 4) /
sum(decode(a.name,'table scans (long
tables)',value,.00000000001))
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 fs.name 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 fs.name
/
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
/
prompt
prompt ROLLBACK STATISTICS:
prompt
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'
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.
jg
-- @home.com is bogus. April fools!Received on Fri Apr 01 2005 - 20:01:48 CST
 |
 |