Re: Query capture

From: Mohammad Rafiq <rafiq9857_at_gmail.com>
Date: Thu, 7 Apr 2005 15:28:07 -0400
Message-ID: <28ae33f10504071228215402d4@mail.gmail.com>

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 <stephenbooth.uk_at_gmail.com> wrote:
> On Apr 7, 2005 7:08 PM, Wolfgang Breitling <breitliw_at_centrexcc.com> 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

Received on Thu Apr 07 2005 - 18:13:43 CDT