-
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.
Last edited by raviindbaworld; 05-31-2007 at 12:18 AM.
-
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?
Jeff Hunter
-
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 ??
-
tune your code to do less IO
-
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.
-
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.
Last edited by PAVB; 05-24-2007 at 06:39 PM.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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...
Jeff Hunter
-
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
Last edited by PAVB; 05-24-2007 at 10:01 PM.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|