Re: Query capture
- From: Mohammad Rafiq <rafiq9857@xxxxxxxxx>
- To: stephen booth <stephenbooth.uk@xxxxxxxxx>
- Date: Thu, 7 Apr 2005 15:28:07 -0400
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@xxxxxxxxx> wrote:
> On Apr 7, 2005 7:08 PM, Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> 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.
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
