WIO is very high !! how to reduce it?

Printable View

05-24-2007, 12:05 PM
raviindbaworld

WIO is very high !! how to reduce it?

Dear All,

Im having problem with IO wait .Everytime its very high with around 64% and the user utilisation in the system is just 10%.There are around 100 parallel process running on the system which will reads the data from 7 -8 tables and will update on the custom tables.I guess this is impacting lots of performance.

PReviously the same scripts used to update 50K records per minute but now its just 30K ..

wht can be the reason for this ? IS it problem with redologs ? when i check df -k its the space occupied by the data si quite high more than 90% in all directories.

The server configuration 72 hyperthreaded cpu's with a huge hard disk capacity.

SunOS cocpdb1 5.9 Generic_118558-26 sun4u 05/24/2007

23:04:23 %usr %sys %wio %idle
23:04:24 - 6 - 3 - 67 - 24
23:04:25 7 - 2 - 68 - 23
23:04:26 6 - 5 - 65 - 25
23:04:27 7 - 5 - 65 - 24
23:04:28 6 - 6 - 62 - 27
23:04:29 7 - 6 - 60 - 27
23:04:30 7 - 5 - 65 - 24
23:04:31 6 - 6 - 66 - 22
23:04:32 7 - 3 - 67 - 24
23:04:34 6 - 3 - 71 - 21

Average 6 - 4 66 24

Well I appreciate all ur reply(s) on the stuff which I posted I would like to add some more things which will give better insights to all of u people on this forum who are providing some valuable comments.

Well all my custom tables and indexes are sitting in one table space, i think this is not the best way to design is that the reason why im getting huge IO wait ?? someone in his posts has also pointed this .here are some statistics from AWR reports.

Guru's I need ur suggestions to tune this, I think if i move out some data from one table space and spread out to another..all my problems will solve..please join in this discussion

Time Model Statistics
•ordered by Time (seconds) desc
Statistic Name Time (seconds) % Total DB Time
DB time - 232,001.61 - 100.00
sql execute elapsed time - 231,688.23 - 99.86
DB CPU - 15,293.65 - 6.59
PL/SQLexecution elapsed time 3,845.67 - 1.66

The query which is on top by execution
INSERT INTO G2I_FIGURE_PRODUCT1 VALUES (:B5 , :B4 , :B3 , :B2 , :B1 ) - is using parallel hint on this a gud option to reduce the execution time

The query which is comsuming most of the CPU time ,by gets , by Reads is
SELECT /*+ choose ordered use_nl(a, b, c, d, e)*/ DISTINCT A.CUSTOMER_REF, ATTRIBUTE_VALUE FROM G42PRODUCTATTRIBUTE E, G42CUSTHASPACKAGE A, G42CUSTHASPRODUCT C, G42CUSTPRODUCTDETAILS B, G42CUSTPRODUCTATTRDETAILS D WHERE A.CUSTOMER_REF = :B2 AND B.ACCOUNT_NUM =:B1 AND A.CUSTOMER_REF = B.CUSTOMER_REF AND B.CUSTOMER_REF = C.CUSTOMER_REF AND A.PACKAGE_SEQ = C.PACKAGE_SEQ AND B.PRODUCT_SEQ = C.PRODUCT_SEQ AND A.CUSTOMER_REF = D.CUSTOMER_REF AND C.PRODUCT_SEQ = D.PRODUCT_SEQ AND D.PRODUCT_ID+0 = 1 AND D.PRODUCT_ATTRIBUTE_SUBID = E.PRODUCT_ATTRIBUTE_SUBID AND D.PRODUCT_ID+0 = E.PRODUCT_ID AND E.ATTRIBUTE_UA_NAME = 'CUST_ORD_NUMBER' AND A.PACKAGE_ID IN (1, 14, 15, 16, 19, 22, 23, 24, 26, 37, 38) - i shld tune it at any cost . any ideas on this..??

Tablespace IO stats

GNV42_PRD - 5,322,976 - 1,498 - 24.83 - 1.00 - 257 -0 - 1,779,250 -16.31
GNV42_IPRD -1,685,501 - 474 - 25.26 - 1.00 - 2 - 0 -235,100 - 20.47

Segments by Logical Reads
•Total shows % of logical reads for each top segment compared with total logical reads for all segments captured by the Snapsot
[SIZE="3"]Owner Tablespace Name Object Name Obj. Type Logical Reads %Total
MIG2IRB - GNV42_IPRD - G42CUSTHASPRODUCT_PK1 - INDEX - 240,289,680 - 21.13
MIG2IRB - GNV42_IPRD - G42CUSTPRODUCTATTRDETAILS_PK1 - INDEX - 182,746,144 - 16.07
MIG2IRB - GNV42_PRD - G42CUSTPRODUCTDETAILS - TABLE - 158,891,392 - 13.98
MIG2IRB - GNV42_PRD - G42CUSTHASPRODUCT - TABLE - 154,108,240 - 13.55
MIG2IRB - GNV42_PRD - G42CUSTPRODUCTATTRDETAILS - TABLE - 56,124,464 - 4.94[/SIZE="3"]

Segments by Buffer Busy Waits
Owner Tablespace Name Object Name Subobject Name Obj. Type Buffer Busy Waits %Total
MIG2IRB MIG_TAB_2 G2I_FIGURE_PRODUCT1 TABLE 119,350 67.01
MIG2IRB MIG_TAB_2 G2I_FIGURE_PRODUCT4 TABLE 44,040 24.73
MIG2IRB MIG_TAB_2 G2I_FIGURE_PRODUCT3 TABLE 6,304 3.54
MIG2IRB MIG_TAB_2 G2I_FIGURE_PRODUCT6 TABLE 4,381 2.46
MIG2IRB MIG_TAB_2 G2I_FIGURE_PRODUCT2 TABLE 3,119 1.75

Segments by Row Lock Waits
Owner Tablespace Name Object Name Subobject Name Obj. Type Row Lock Waits %Total
MIG2IRB MIG_IND_2 G2I_MASTER_AUDITSUB_I4 INDEX 29 59.18
MIG2IRB MIG_TAB_2 G2I_FIGURE_EVENTSOURCE6_AK1 INDEX 11 22.45
MIG2IRB MIG_IND_2 G2I_MASTER_AUDITPRD_I4 INDEX 9 18.37

Any suggestion on this are welcome and appreciated.
05-24-2007, 12:09 PM
marist89

You're banging at your disks pretty hard, that's all. Assuming you're using Async I/O, Direct I/O, and a filesystem that supports them, you're probably getting the best you can get out of those disks.

Your question as a DBA is WHY am I banging on the disks so hard?
05-24-2007, 12:15 PM
raviindbaworld

Thxx a lot actually im the developer i will put this question to my DBA ..!! Can u give me some tips or areas to luk on to combat this.Whts the resolution fior this ..shud i reduce the load on the server ??
05-24-2007, 12:16 PM
davey23uk

tune your code to do less IO
05-24-2007, 06:29 PM
ixion

Quote:

Originally Posted by davey23uk

tune your code to do less IO

Agreed
You probably have some really badly performing SQL statements that are consuming I/O...

But I've also seen some pretty botched jobs done by the DBA for the datafile layout.
05-24-2007, 06:35 PM
PAVB

Quote:

Originally Posted by ixion

But I've also seen some pretty botched jobs done by the DBA for the datafile layout.

so, so you think you can tell a good datafile layout from a bad one, heaven from hell, blue skies from pain?

How can you tell? enlight us, please.
05-24-2007, 09:06 PM
marist89

Quote:

Originally Posted by PAVB

so, so you think you can tell a good datafile layout from a bad one, heaven from hell, blue skies from pain?

How can you tell? enlight us, please.

How many you want to talk about?

How about the client that had both sets of redo log members on a single RAID 5 device?

Or, how about the client that put their TEMP and UNDO tablespace on the same filesystem?

Or, how about the person that put DATA on one filesystem and INDEX on another?

It's hot in hell, comfortable in heaven.

C'mon man, don't be naive...
05-24-2007, 09:58 PM
PAVB

Quote:

Originally Posted by marist89

C'mon man, don't be naive...

...and, how any of that increases I/O which -by the way- was the original issue?

On the other hand... mhhh... let me put this very politely, who ratled your cage man? let the guy defend himself :rolleyes:
05-24-2007, 10:11 PM
ixion

Quote:

Originally Posted by PAVB

so, so you think you can tell a good datafile layout from a bad one, heaven from hell, blue skies from pain?

How can you tell? enlight us, please.

I can't tell specifically for his system weather his disk/datafile layout is good or bad. Just that its something to look into.

Have a nice day.
05-24-2007, 10:20 PM
ixion

Now to the OP:

run the following command:
iostat -cdx 4

you might want to run it through awk to filter out disk spindles that are not above say 30%, ... and see whats on those drives, that should at least point you in the right direction.
05-24-2007, 10:24 PM
PAVB

Focus! Focus! you have to read the original question.

You guys can't just machine-gun answer anything that comes to your minds LOL ... very good answers I would say, for a totally different set of questions!!!

The guy wanted to "decrease I/O"... now, go ahead.

Man, this is getting better by the minute :) I'm loving it.
05-25-2007, 09:48 AM
waitecj

Quote:

Originally Posted by PAVB

Focus! Focus! you have to read the original question.

You guys can't just machine-gun answer anything that comes to your minds LOL ... very good answers I would say, for a totally different set of questions!!!

The guy wanted to "decrease I/O"... now, go ahead.

Man, this is getting better by the minute :) I'm loving it.

His question seems to suggest to me that he is looking to reduce Waits for I/O (WIO in the title), so therefore, speading I/O around different filesystems would be a valid answer to the question...
05-25-2007, 03:56 PM
ixion

Quote:

Originally Posted by PAVB

Focus! Focus! you have to read the original question.

You guys can't just machine-gun answer anything that comes to your minds LOL ... very good answers I would say, for a totally different set of questions!!!

The guy wanted to "decrease I/O"... now, go ahead.

Man, this is getting better by the minute :) I'm loving it.

Instead of the above dribble, why not contribute something useful?
05-25-2007, 04:01 PM
PAVB

Up in the fourth post Davey said "tune your code to do less IO"
There is where this whole thing should have ended ;)
05-25-2007, 04:19 PM
ixion

I disagree.

How do you Tune an insert to do less I/O or an update or a delete???

I/O sometimes can not be avoided in order to do meaningful work (hmm what ever that means)... In those cases when the I/O waits are high then proper disk managment and analysis of the system layout is required to improve system performance and response times.
05-25-2007, 04:25 PM
davey23uk

well you can, better indexes etc, use better structures - but agree, look a the io sub system as well, always an important part
05-28-2007, 07:12 PM
slimdave

Quote:

Originally Posted by raviindbaworld

wht can be the reason for this ? IS it problem with redologs ? ... The server configuration 72 hyperthreaded cpu's with a huge hard disk capacity.

Run statspack to find out the kind of i/o you're waiting on. Huge disk capacity != huge disk bandwidth.
05-30-2007, 10:00 AM
ndisang

Quote:

Originally Posted by ixion

I disagree.

How do you Tune an insert to do less I/O or an update or a delete???

I/O sometimes can not be avoided in order to do meaningful work (hmm what ever that means)... In those cases when the I/O waits are high then proper disk managment and analysis of the system layout is required to improve system performance and response times.

I think one can tune inserts and updates...since you can generate explain plan for them.

what about nolloging, parallism, indexes, paralell dml?
05-30-2007, 10:26 AM
Bore

Well, tuning insert - OK direct load insert could do
Tuning deletes - index and truncate where appropriate
but this is not what ixion means I believe, just there is a job to be done and sometimes we can do nothing about that.
Then we can look at the layout.
If it's good as well, then we go for faster disks and more cache
05-30-2007, 10:40 AM
PAVB

Guys... I still don't get it.

Poster has a single query which performance is loosing steam. "Used" to run OK, now is getting lazy.

Tell me the truth, when this happens in your own shop... is the first thing you do to go and check physical layout?

Wouldn't any fo the following actions come before looking at physical layout in you standard troubleshooting approach??? 1) Explain plan the query and comopare it with baseline (if you have it)... 2) Look if performance stats are up-to-date... 3) Review Change Control logs looking for what changed since the query was performing OK... 4) Trace the query... 5) Look at run book looking for new processes added since query was performing OK... /// Etc Etc Etc

Shouldn't you be aware of any disk bottle neck well in advance and not waiting for a crappy piece of SQL to sound the alarm? Believe me, in our shop we know what is happening with our physical I/O without having to wait for a query to blow it out.
05-30-2007, 06:27 PM
ixion

Given the OP's lack of posting relevant details and updates, its unlikely we will solve the issue, other than pointing out a few areas to look into (see posts 4 and 5)

The OP never did respond back with what type of Wait I/O was occurring, was it Write or Read?
05-30-2007, 06:39 PM
ixion

Quote:

Originally Posted by Bore

Well, tuning insert - OK direct load insert could do
Tuning deletes - index and truncate where appropriate
but this is not what ixion means I believe, just there is a job to be done and sometimes we can do nothing about that.
Then we can look at the layout.
If it's good as well, then we go for faster disks and more cache

Yes thats true.. But not only faster disks but where things are located and the types of physical structures used, matching the usage intent with the physical characteristics.