WIO is very high !! how to reduce it?

[raviindbaworld]

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.