From oracle-l-bounce@freelists.org Fri Nov 11 13:33:41 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id jABJXYJA008773 for ; Fri, 11 Nov 2005 13:33:34 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id jABJXJvX008750 for ; Fri, 11 Nov 2005 13:33:19 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C52D5225023; Fri, 11 Nov 2005 14:33:10 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 07867-09; Fri, 11 Nov 2005 14:33:10 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 43AA322514E; Fri, 11 Nov 2005 14:33:10 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 11 Nov 2005 14:31:13 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CB375224F0C for ; Fri, 11 Nov 2005 14:31:13 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 07616-07 for ; Fri, 11 Nov 2005 14:31:13 -0500 (EST) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.197]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 82036224E5E for ; Fri, 11 Nov 2005 14:31:13 -0500 (EST) Received: by zproxy.gmail.com with SMTP id 16so591308nzp for ; Fri, 11 Nov 2005 11:31:13 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:from:to:cc:subject:date:mime-version:content-type:content-transfer-encoding:x-mailer:x-mimeole:thread-index:in-reply-to:message-id; b=ciY4kpze3BwFCHaVX7urNN/fvITplUjyCDVuis4EYbeR5g2AUEGS6LGzy8XORvvD/oVjjTEbkYgttj+P68faJm6HifiXYhTyDisU0OqWnHd9bg64sqVL1htnPAl1XLF3F3pZmxqJqqsoOIhqGwbCDf+pi+V93iO/R4QtzhAedqs= Received: by 10.64.27.16 with SMTP id a16mr1737647qba; Fri, 11 Nov 2005 11:31:13 -0800 (PST) Received: from ASTEN510m ( [86.128.121.191]) by mx.gmail.com with ESMTP id e14sm1990713qba.2005.11.11.11.31.11; Fri, 11 Nov 2005 11:31:12 -0800 (PST) From: "Adrian" To: Cc: "'Oracle-L'" Subject: RE: High wio on New Hitachi SAN Storage of DB Server Date: Fri, 11 Nov 2005 19:31:08 -0000 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Thread-Index: AcXm8aAo3LioaOkkQzyVmyI8EXeV7QAAoQ3g In-Reply-To: Message-ID: <4374f180.367e0407.670d.ffffb5cc@mx.gmail.com> X-archive-position: 28285 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: ade.turner@gmail.com Precedence: normal Reply-To: ade.turner@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-4.7 required=5.0 tests=AWL,BAYES_00 autolearn=ham version=2.63 Hi Vivek, Your note interested me; we have exactly the same configuration and symptoms but we are still running async_io = TRUE. I concur with your findings on the extreme performance issues - short term server freezes are occurring. You mention a bug; is this hearsay or do you have details? If you've turned of async_io (the default on aix), then you may want to use DBWR_IO_SLAVES. I've not tried this yet. Anyway, ours is a HDS9990 with McData Switches attached to a p690 running AIX 5.2 ML4. The filesystems are jfs2. I can reproduce the problem just by creating a 10GB datafile, multiple users doing random i/o can also get the same issue, or indeed when a parallel rman backup is in progress. However concurrent cp's of multiple files do not reproduce the issue (hence I believe it is likely an async i/o problem. Under load Topas shows high wait for i/o and Sar -q shows that swpocc% = 100 and swpocc > 20 My unix admins are currently looking at the async i/o settings as per metalink note 271444.1, but are heavily loaded and this is not prod (yet) so the urgency is low. If you or anyone else has any pointers with this configuration please let me know. Kind Regards Adrian -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of VIVEK_SHARMA Sent: 11 November 2005 18:56 To: Oracle-L Subject: High wio on New Hitachi SAN Storage of DB Server Folks, While doing Application Testing of Hybrid Trans (OLTP mostly though) by 200 users (approx) on a NEWLY configured HITACHI SAN Storage, on DB Server (of AIX) High wait for IO i.e. wio = 70 % till 1400 Hours is observed. NOTE - wio reduced to about 10 % gradually from 1400 Hours to 2000 Hours. Average sar Output from morning to 1400 Hours:- 13:38:00 %usr %sys %wio %idle 13:43:00 6 5 67 22 13:48:00 10 6 74 10 13:53:00 10 5 66 19 13:58:00 7 5 61 27 14:03:00 5 5 67 22 14:08:00 7 5 74 15 14:13:00 9 6 69 15 CONFIG ====== DB Server = 7 CPUs OS AIX 5.2 Oracle 8.1.7 Hitachi Storage for DB Server = 40 underlying Disks Hardware Raid Init.ora disk_asynch_io = FALSE (When disk_asynch_io is set TRUE there is extreme performance degradation with AIX 5.2 [seems a bug]) Comments by IBM =============== The average number of process waiting for IO to complete is 12. This indicates that these processes are waiting for the IO to complete. This is the reason why we are seeing an average iowait of 70%. The seek rate is 95.72% on the hdsdb9960lv LV's indicates a high degree of random IO, usually caused by the application or a high degree of disk fragmentation. STATSPACK report (will provide any other sections as needed) ================ DB Name DB Id Instance Inst Num Release OPS Host ------------ ----------- ------------ -------- ----------- --- ------------ ABNPROD 34298189 abnprod 1 8.1.7.4.0 NO findb Snap Id Snap Time Sessions ------- ------------------ -------- Begin Snap: 5 15-Oct-05 13:00:55 352 End Snap: 6 15-Oct-05 14:00:37 352 Elapsed: 59.70 (mins) Cache Sizes ~~~~~~~~~~~ db_block_buffers: 215000 log_buffer: 18874368 db_block_size: 8192 shared_pool_size: 754288000 Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 298,013.21 837.31 Logical reads: 55,540.47 156.05 Block changes: 2,296.74 6.45 Physical reads: 3,109.99 8.74 Physical writes: 399.33 1.12 User calls: 2,657.16 7.47 Parses: 64.98 0.18 Hard parses: 5.44 0.02 Sorts: 75.56 0.21 Logons: 0.75 0.00 Executes: 1,783.45 5.01 Transactions: 355.92 % Blocks changed per Read: 4.14 Recursive Call %: 15.67 Rollback per transaction %: 94.71 Rows per Sort: 10.87 Top 5 Wait Events ~~~~~~~~~~~~~~~~~ Wait % Total Event Waits Time (cs) Wt Time -------------------------------------------- ------------ ------------ ------- db file sequential read 6,230,712 1,640,351 43.84 log file sync 1,087,475 1,286,467 34.38 db file scattered read 351,411 416,508 11.13 log file parallel write 706,201 288,168 7.70 buffer busy waits 334,943 69,830 1.87 ------------------------------------------------------------- Qs How might this issue be approached? Qs Are there any special O.S. parameters that might be set? **************** CAUTION - Disclaimer ***************** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS******** End of Disclaimer ********INFOSYS*** -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l