High Wait I/O

[Stefano_65]

Hi,
sar -d shows high %wio, the average value is 40.
using glance, in the GLOBAL WAIT STATES I found Blocked On -> System is the highest value, 42%.

I suppose that CPUs are in wait for a process (or more than one) that use system resource, but how can I identify it?

the system is an 11.0, oracle is running, ECM disks.
hi, and thanks.
s.

[Simon Hargrave]

Check the disk IO in glance, (u). See if there's high %age utilisation, and also high queue length.

[Stefano_65]

There are 261 disks. Qlen is always zero, higest value of about 70.. some disks zero and medium value 20-30..


thanks.
s.

[Stefano_65]

"..highest value of UTIL is..."

sorry.
s.

[Eric Antunes]

261 disks??

From Oracle you can check those ratios:

select sum(gets) "Dictionary gets" , sum(getmisses) "Dictionary misses",
sum(gets-getmisses)*100/sum(gets) "Dict. cache hit rate > 90"
from sys.v_$rowcache

select ds.value/ decode( (ds.value+ms.value), 0,1,(ds.value+ms.value))*100 "Disk sort ratio indic. < 0,22"
from v$sysstat ds, v$sysstat ms
where ms.name='sorts (memory)'
and ds.name='sorts (disk)';

select (bbc.total_waits*100/(cg.value+dbg.value)) "Buff busy ratio ind. <= 0,007"
from v$system_event bbc,
v$sysstat cg, v$sysstat dbg
where bbc.event='buffer busy waits'
and cg.name ='consistent gets'
and dbg.name='db block gets'

select nvl( (nvl(bbw.total_waits,0)*100/(nvl(cg.value,0)+nvl(dbg.value,0))), 0) "Free buff. ratio ind. < 0,0009"
from v$system_event bbw,
v$sysstat cg, v$sysstat dbg
where bbw.event='free buffer waits'
and cg.name ='consistent gets'
and dbg.name='db block gets'; -- IF YOU GET NO ROWS, GREAT!

select sum(gethits)*100/sum(gets) "lib. cache get hit ratio > 94"
from v$librarycache;

select sum(pinhits)*100/sum(pins) "lib. cache pin hit ratio > 92"
from v$librarycache;

select sum(pinhits)*100/sum(nvl( pins, 1)) "lib. cache pin hit ratio > 92", namespace
from v$librarycache
where pins > 0 and pinhits > 0
group by namespace
order by 1;

select nvl( sw.value, 0)*100/nvl( lw.value,0) "Redo space wait ratio < 0,2"
from v$sysstat sw, v$sysstat lw
where sw.name='redo log space requests'
and lw.name='redo writes'

select rfcr.value*100/(tsrg.value+tfbr.value) "Chained fetch ratio < 0,05?",
rfcr.value "table fetch continued row",
tsrg.value "table scan rows gotten",
tfbr.value "table fetch by rowid"
from v$sysstat rfcr,
v$sysstat tsrg,
v$sysstat tfbr
where rfcr.name='table fetch continued row'
and tsrg.name='table scan rows gotten'
and tfbr.name='table fetch by rowid' -- HERE I'M NOT SURE IF < 0,05 IS A GOOD VALUE BUT I THINKS IT ISN'T A BAD ONE...

[Nicolas Dumeige]

Hello Stefano

If you want to see wich datafiles (filesystem) are the most heavyly used and a lot of usefull stuff, install the STATPACK.

You'll find relevant information in "Using Statspack" in "Database Performance Tuning Guide and Reference".

With 271 disk, 40 %wio seems very hight in deed.

Cheers

Nicolas

[Stefano_65]

Thanks everybody, I'm waiting a feedback from DBAs.

s.

[Ted Buis]

While 271 is a nice number of spindles, it doesn't help if there is a bottleneck on just one or even a few. Glance can show you utilization by physical disk or file system and you want to look at both. If some physical disks are maxed out, then you want to see the file system(s) associated with those disks are spread that file system out over more disks. Index, tmp, and logs can be bottlenecks that take relatively small space and often don't get striped as much as is needed. I have seen where it was necessary to stripe across more spindles than was needed for the space capacity of the file system, just to get the I/O performance up.

If you are not doing raw disk access, do you have Online JFS? If so, have you set the mount options, so that you are not double buffering the ORACLE I/O in ORACLE and in the HP-UX buffer cache(mincache=direct if I remember correctly). For any tmp storage, is it mounted without file system logging.
Typically, there is no need to do an intent log of tmp, since if there is a crash, tmp files would be useless.

Naturally, this assumes you are not doing I/O as page-outs. If you do have many page-outs then buy more RAM. You can see this in Glance under swap.

[KapilRaj]

I would,

iostat -t # Find the *hot* disks
pvdisplay # To find out which lv in the *hot* disk.
pvmove # To distribute the *hot* lv's into a number of disks

Online change it may cost a few disks if there is no free space

Regds,

Kaps

[Stefano_65]

Hi, DBAs made the test. The only vlaue out of range is:

SQL> select rfcr.value*100/(tsrg.value+tfbr.value) "Chained fetch ratio < 0,05?",
2 rfcr.value "table fetch continued row",
3 tsrg.value "table scan rows gotten",
4 tfbr.value "table fetch by rowid"
5 from v$sysstat rfcr,
6 v$sysstat tsrg,
7 v$sysstat tfbr
8 where rfcr.name='table fetch continued row'
9 and tsrg.name='table scan rows gotten'
10 and tfbr.name='table fetch by rowid';

Chained fetch ratio < 0,05? table fetch continued row table scan rows gotten table fetch by rowid
--------------------------- ------------------------- ---------------------- --------------------
30,6831545 3664005770 7150020209 4791404412


Any suggest?

s.

[Eric Antunes]

Hi,

It seems you have a chainning problem. To solve it you must do the following:

--CREATE TABLE chained_rows. Run the following as system:

sqlplus system/ @$ORACLE_HOME/rdbms/admin/utlchain.sql

-- SEE WITCH TABLES ARE CREATING MORE CHAINED ROWS AND SAVE THE OUTPUT TO EXECUTE IT NEXT:

select 'ANALYZE table '||owner||'.'||table_name||' LIST CHAINED ROWS;'
from all_tables
where owner not in ('SYS', 'SYSTEM')
order by owner;

-- EXECUTE THE PREVIOUS OUTPUT...

ANALYZE table OWNER1.TABLE1 LIST CHAINED ROWS;
ANALYZE table OWNER1.TABLE2 LIST CHAINED ROWS;
...
ANALYZE table OWNERN.TABLEN LIST CHAINED ROWS;

-- SEE WHERE IS HAPPENING THE CHAINNING:

select count(*), table_name
from chained_rows
group by table_name
ORDER BY 1 DESC

-- AT THIS POINT YOU HAVE 2 THINGS TO DO:

- PREVENT MORE CHAINNING:

for each table you must increase (here the amount of the increase depends on the current value: if you have %free=10 and %used=85 you may change it to %free=20 and %used=75; if you have %free=20 and %used=80, you may alter to %free=30 and %used=70) the %free on the table storage:

alter table . pctfree ;


- REPAIR THE EXISTING CHAINNING:

alter table .
disable all triggers

select count(*) from .

drop table migrated_rows

create table migrated_rows as
select orig.*
from . orig, chained_rows cr
where orig.rowid = cr.head_rowid and
cr.table_name = .

select count(*) from migrated_rows

delete from .
where rowid in (select head_rowid from chained_rows where table_name = '')

commit

insert into .
select * from migrated_rows

commit

alter table .
enable all triggers

Hope this will help you, good luke!

[Eric Antunes]

Hi Stefano,

Any news from DBAs?

[Stefano_65]

Hi,
at the moment I haven't news from dbas.. I'm still waiting for a feedback.

stef.

[Eric Antunes]

Your dba's works very slowly... :-)

[Stefano_65]

yes... they are on vacation...
now the situation similar, but value of wait io is lower than before.. maybe in august oracle works less.. average is 15%.

s.