From oracle-l-bounce@freelists.org Wed Oct 6 18:09:30 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i96N9TU04449 for ; Wed, 6 Oct 2004 18:09:29 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i96N9TI04444 for ; Wed, 6 Oct 2004 18:09:29 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 681EE72D960; Wed, 6 Oct 2004 18:15:34 -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 07701-41; Wed, 6 Oct 2004 18:15:34 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C3E4A72D939; Wed, 6 Oct 2004 18:15:33 -0500 (EST) In-Reply-To: To: J.Velikanovs@alise.lv Cc: oracle-l@freelists.org, oracle-l-bounce@freelists.org Subject: Re: Can I tryst in "physical ... direct" figures? MIME-Version: 1.0 Message-ID: From: J.Velikanovs@alise.lv Date: Thu, 7 Oct 2004 02:12:18 +0300 X-MIMETrack: Serialize by Router on ROSS/IT ALISE/LV(Release 5.0.11 |July 24, 2002) at 2004.10.07 02:12:18, Serialize complete at 2004.10.07 02:12:18 Content-type: text/plain Content-Transfer-Encoding: 8bit X-archive-position: 10765 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: J.Velikanovs@alise.lv Precedence: normal Reply-To: J.Velikanovs@alise.lv X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Many thanks to peoples how some time ago have taken part in this thread. Thanks to zhu, Jonathan and Terry. I would like to share my experience and final result ;) At the beginning I was impressed by the figures and wondered if there any system, which can have the figures, like I provided. Especially those figures looked exotic in OLTP system, which I was asked to tune. >Statistic Total per Second per >Trans >--------------------------------- ------------------ -------------- >------------ >physical writes 16,465,203 365.9 175.3 >physical writes direct 16,343,723 363.2 174.0 After some investigation and tuning figures dropped to >physical writes 839,229 18.7 9.3 > physical writes direct 719,063 16.0 8.0 For the identical time period of the day and the same load of users. And I have achieved more then it can seams from the first look. By reducing load on IO subsystem I achieved other IO related waits reduction. Take a look: Before Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- db file sequential read 2,916,769 0 10,719 4 31.1 log file sync 88,952 2,480 6,046 68 0.9 After db file sequential read 2,634,909 0 5,039 2 29.3 log file sync 85,041 137 843 10 0.9 The root of the problem was inefficient SQL, which used HASH JOIN on the xM rows big tables. This SQL run have taken 2s from the user response time, but it was very popular (almost each form have run it). Interested part of my story is the path how I have found the SQL. Oracle doesn’t provide the Write statistics for each SQL in v$sqlarea and v$sql (the only view I have found with Writes statistics per SQL is V$SQL_PLAN_STATISTICS, which not felled until your init.ora parameter statistics_level is set to ALL). So it was a little bit challenging to find write intensive SQL. Oracle have the information regarding sorting per SQL, but in my case there HASH has place. I have taken Terry advice and find most intensive write session (even it is J2EE application on front-end with connection polling) and traced it with Cary. Then I was able to identified couple of suspicious SQL and deliver them to the developers. Interesting how one small SQL can be so inefficient. Hope my experience will help to anybody, Jurijs +371 9268222 (+2 GMT) ============================================ Thank you for teaching me. http://otn.oracle.com/ocm/jvelikanovs.html PS FYI 1. Full Statspack report Before ftp://ftp.alise.lv/pub/oracle/to_oral/perf_IKSP_20040923.txt After ftp://ftp.alise.lv/pub/oracle/to_oral/perf_IKSP_20040927.txt 2. How dramatically figures can drop ftp://ftp.alise.lv/pub/oracle/to_oral/dwr_new.JPG On 24.09.2004 01:09:44 oracle-l-bounce wrote: >9.2.0.5 EE on Lintel RH AS 2.1 >OLTP ~200 connections. >Today a have observed figures below in statspack report >Statistic Total per Second per >Trans >--------------------------------- ------------------ -------------- >------------ >physical reads 19,327,036 429.5 205.8 >physical reads direct 10,482,393 232.9 111.6 >physical writes 16,465,203 365.9 175.3 >physical writes direct 16,343,723 363.2 174.0 > >It seams very strange to me that almost all writes and more then 1/2 of >reads is direct. >What do you think, can I trust on those figures? Can it be truth that all >writes are direct “bypassing the buffer cache, as written in Docs? > >PS Report interval 750.02 (mins) >PS I know - SP aggregates live. Don’t tell me do not look on it. > > >Jurijs >+371 9268222 (+2 GMT) >============================================ >Thank you for teaching me. >http://otn.oracle.com/ocm/jvelikanovs.html > > >-- >http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l