DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: WIO is very high !! how to reduce it?

  1. #1
    Join Date
    May 2007
    Posts
    7

    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.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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

  3. #3
    Join Date
    May 2007
    Posts
    7
    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 ??

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    tune your code to do less IO

  5. #5
    Join Date
    Jun 2006
    Posts
    259
    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.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.
    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.

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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...
    Jeff Hunter

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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
    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.

  9. #9
    Join Date
    Jun 2006
    Posts
    259
    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.

  10. #10
    Join Date
    Jun 2006
    Posts
    259
    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
  •  


Click Here to Expand Forum to Full Width