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.
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..??
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?
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 ??
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.
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.
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.
Reply With Quote
Originally Posted by davey23uk
Bookmarks