From oracle-l-bounce@freelists.org Tue Apr 13 19:02:22 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i3E02HE12172 for ; Tue, 13 Apr 2004 19:02:22 -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 i3E02G612167 for ; Tue, 13 Apr 2004 19:02:16 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B87B4635999; Tue, 13 Apr 2004 18:56:00 -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 26059-76; Tue, 13 Apr 2004 18:56:00 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0DEE36359A9; Tue, 13 Apr 2004 18:55:59 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 13 Apr 2004 18:54:50 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 082D76357A6 for ; Tue, 13 Apr 2004 18:54:50 -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 26059-38 for ; Tue, 13 Apr 2004 18:54:49 -0500 (EST) Received: from mail.acelerate.com (mail.acelerate.com [200.105.128.132]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 366A86356B1 for ; Tue, 13 Apr 2004 18:54:49 -0500 (EST) Received: (qmail 11201 invoked from network); 13 Apr 2004 23:29:59 -0000 Received: from dazasoftware.com (HELO org48l7d9ara8b) (200.105.151.94) by 0 with SMTP; 13 Apr 2004 23:29:59 -0000 Message-ID: <003f01c421b3$b5078cd0$2501a8c0@dazasoftware.com> From: "Juan Cachito Reyes Pacheco" To: References: <20040413204530.DTWO21228.lakermmtao08.cox.net@smtp.central.cox.net> Subject: Re: please help analyzing a runstats report Date: Tue, 13 Apr 2004 20:01:27 -0400 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 3016 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jreyes@dazasoftware.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org HI Ryan I think you should give more information to give an opinion. Runstats is a fast way to compare two solutions, statpacks could give the answer. If they are two queries the execution plan and statistics could give more clues. Is very difficult to give an opinion based on only this. I had a similar problem using bulk collect, even when bulk took less resources (reading statpacks reports), it took some more time but took really less redo, and that is what I was searching, a fast rollback if there is a problem. Then I supposed it was because the test database hadn't enough memory, but i didn't needed to investigate more. Juan Carlos Reyes Pacheco OCP Database 9.2 Standard Edition ----- Original Message ----- From: To: Sent: Tuesday, April 13, 2004 4:45 PM Subject: please help analyzing a runstats report What is troubling me here is that run2 is using only 5% of the consistent gets that run1 does. I thought consistent gets was the most important statistic for queries? All of the logical I/Os of run2 are smaller, but overall run1 uses less resources. I've run this a few times so the queries are compiled and its not factoring in parsing. QL> exec runstats_pkg.rs_stop(500); Run1 ran in 4455 hsecs Run2 ran in 4040 hsecs run 1 ran in 110.27% of the time Name Run1 Run2 Diff LATCH.cache buffers lru chain 1,904 1,391 -513 LATCH.multiblock read objects 462 1,169 707 LATCH.session idle bit 6,150 7,045 895 LATCH.checkpoint queue latch 4,791 6,557 1,766 LATCH.enqueues 4,612 6,592 1,980 LATCH.messages 18,793 21,623 2,830 STAT...Cached Commit SCN refer 5,691 9,110 3,419 STAT...buffer is not pinned co 5,879 9,415 3,536 STAT...no work - consistent re 5,964 9,710 3,746 LATCH.session allocation 8,890 12,845 3,955 LATCH.library cache pin alloca 13,302 18,089 4,787 LATCH.sequence cache 10,596 15,389 4,793 LATCH.simulator hash latch 5,311 10,225 4,914 LATCH.undo global data 14,089 19,874 5,785 STAT...table scan blocks gotte 5,874 0 -5,874 LATCH.row cache enqueue latch 8,571 15,301 6,730 LATCH.row cache objects 21,791 33,373 11,582 LATCH.redo allocation 42,806 60,353 17,547 LATCH.shared pool 43,828 61,702 17,874 LATCH.dml lock allocation 43,880 63,303 19,423 LATCH.enqueue hash chains 59,423 85,041 25,618 LATCH.cache buffers chains 594,437 620,207 25,770 LATCH.library cache pin 79,556 113,499 33,943 LATCH.library cache 99,851 140,603 40,752 STAT...buffer is pinned count 82,183 149,898 67,715 STAT...table fetch by rowid 2 79,600 79,598 STAT...index fetch by key 82,187 2 -82,185 STAT...table scan rows gotten 82,185 0 -82,185 STAT...session logical reads 170,885 10,383 -160,502 STAT...consistent gets 170,353 9,845 -160,508 STAT...consistent gets - exami 164,381 15 -164,366 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 1,085,167 1,315,744 230,577 82.48% PL/SQL procedure successfully completed. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------