Re: Query capture

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 07 Apr 2005 09:31:15 -0600
Message-ID: <42555243.6060001@centrexcc.com>

elapsed_time and cpu_time are always in microseconds since they didn't exist in v$sql in the centiseconds era before Oracle9i. Of course, v$sql suffers from the same aggregation effect as most v$ tables. It contains the sum of execution details of all sql which hash to the same hash_value-child_number - providing the application is written such that sql is being reused.
v$sql_plan_statistics has separate last_xxx statistics for the most recent execution of the sql, but it is not generally populated unless you set statistics_level to all ( not recommended system wide AFAIK ) and on a busy system you may have difficulties catching anything there as it changes rather quickly.

Just as an aside, be aware that sql_text in v$sql contains only the first 1000 bytes of the sql. If it is longer you'll have to retrieve it from v$sqltext (by address or hash_value-child_number).

John Clarke wrote:
> select sql=5Ftext, elapsed=5Ftime/1000000/executions
> from v$sql
> where executions>0
> and (elapsed=5Ftime/1000000/executions) > 5 (and 10, and 20, etc)
> /
>
> You can probably do it fancier. The 1000000 is for 9i+, as elapsed=5Fti=
> me is in microseconds. I believe it's centisconds in previous releases =
> but am not positive. I'm also not sure whether any issues exist with th=
> e accounting in V$SQL, but I'm pretty sure that the execution and elapse=
> d=5Ftime columns won't be "good" until the active calls complete.
>
> - John
>
> ----- Original Message -----
> From: Seema Singh <oracledbam_at_hotmail.com>
> To: oracle-l_at_freelists.org
> Sent: Thu, 7 Apr 2005 10:59:40 -0400
> Subject: Query capture=20
>
>
>

>>Hi,
>>=20
>>I want to capture those queries on basis of following criteria.
>>=20
>>1)SQLs that take more than 5 seconds
>>2)SQLs that take more than 10 seconds
>>3)SQLs that take more than 20 seconds
>>=20
>>Can I do it without explain plan=3F
>>I want to set up script which will capture only those query which are =

>
> taking=20
>

>>more than 5,10,20 minutes to execute whether the table is small or lar=

>
> ge.
>

>>thanks
>>=20
>>=20
>>--
>>http://www.freelists.org/webpage/oracle-l
>>=20

>
>
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 07 2005 - 11:35:17 CDT