From oracle-l-bounce@freelists.org Thu Apr 7 18:13:43 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j37NDhFN014985 for ; Thu, 7 Apr 2005 18:13:43 -0500 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 j37NCnem014919 for ; Thu, 7 Apr 2005 18:12:50 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1355892A30; Thu, 7 Apr 2005 14:30:06 -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 08292-04; Thu, 7 Apr 2005 14:30:05 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 84B3F92A32; Thu, 7 Apr 2005 14:30:05 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=B+RO44fE5ieAfoinVP5GEMNdRe4/B+5MuwqOirH1sdvzIXPoUZh34e9HOwVv1UpQUib4fC/LreiWfOLaSbjYAo85OiyCSfbO/H7XCIh+gaWGAOFwXAQBrKnclwOLpIHuyk/0g8w7Hb1dBLrbPoUpcB7cotkRo55cuWS9H+iOEWg= Message-ID: <28ae33f10504071228215402d4@mail.gmail.com> Date: Thu, 7 Apr 2005 15:28:07 -0400 From: Mohammad Rafiq To: stephen booth Subject: Re: Query capture Cc: breitliw@centrexcc.com, oracle-l@freelists.org In-Reply-To: <687bf9c40504071146194a1da1@mail.gmail.com> Mime-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit References: <42556441.6030401@centrexcc.com> <28ae33f105040711022248b29d@mail.gmail.com> <42557715.5030603@centrexcc.com> <687bf9c40504071146194a1da1@mail.gmail.com> X-archive-position: 18195 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: rafiq9857@gmail.com Precedence: normal Reply-To: rafiq9857@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=1.0 required=5.0 tests=AWL,FROM_ENDS_IN_NUMS autolearn=no version=2.60 X-Spam-Level: * Thanks. Now it ran fine. Regards Rafiq 1 select rownum place, query, timeinseconds, cur_id, address 2 from ( 3 select a.sql_text query, 4 a.elapsed_time/(1000000*a.executions) timeinseconds, 5 a.hash_value cur_id, 6 a.address 7 from v$sql a 8 where a.executions > 0 9 -- and (a.elapsed_time/(1000000*a.executions)) > 5 10 and (a.elapsed_time/(1000000*greatest(a.executions,1))) > 5 11* order by timeinseconds desc) SQL> / Place Query Text Time In Seconds CUR_ID ADDRESS -------- -------------------------------------------------- --------------- ---------- -------- 1 select substr(owner,1,4),substr(segment_name,1,30) 7.60 849562658 69B472B0 SEGMENT_NAME,substr(segment_type,1,10 )SEG_TYPE, s ubstr(tablespace_name,1,10)TBS_NAME,round(bytes/(1 024*1024),2)MB, extents EXTENTS from dba_segments where extents > 50 and owner not like 'SYS%' 2 select /*+ RULE */ sysdate"Date",substr(a.tablespa 6.90 2331294554 699909B4 ce_name,1,30) "Table_Space_Name", round( a.byt es/( 1024*1024 ), 0) " Avail(MB)", round( b.by tes/( 1024*1024 ), 0) " Used (MB)", round( c.b ytes/( 1024*1024 ), 0) " Free (MB)", round( ( round(b.bytes / ( 1024*1024 ), 0 )*100 ) / round( a.bytes / ( 1024*1024 ),0 ),0 ) " % Full" from sy s.sm$ts_avail a, sys.sm$ts_used b, sys. sm$ts_free c where a.tablespace_name = b.tablespa ce_name(+) and a.tablespace_name = c.tablespace_ name(+) On Apr 7, 2005 2:46 PM, stephen booth wrote: > On Apr 7, 2005 7:08 PM, Wolfgang Breitling wrote: > > Yes, I was wondering about that. I always use > > "elapsed_time/greatest(executions,1)" to avoid division by zero problems. > > > > > > Mohammad Rafiq wrote: > > > > > Stephen > > > > > > Are you sure it ran successfully? I got error on a 9206 database on W2000. > > > Regards > > > and (a.elapsed_time/(1000000*a.executions)) > 5 > > > * > > > ERROR at line 9: > > > ORA-01476: divisor is equal to zero > > I ran it on 9206 on Solaris. That error is basically saying that > a.executions=0. Thing is the preceding part of the where clause is > a.executions>0 so there should be no way a.executions=0. The only > thing that comes to mind is that Oracle is trying to evaluate the > "(a.elapsed_time/(1000000*a.executions)) > 5" before the "a.executions > > 0". > > You could change it to and > (a.elapsed_time/(1000000*greatest(a.executions,1))) > 5 but that > shouldn't be necessary. > > I'm home now and don't have access to an Oracle server but I'll test > it again in the morning. > > Stephen > -- > It's better to ask a silly question than to make a silly assumption. > -- http://www.freelists.org/webpage/oracle-l