Re: Query capture
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: jclarke@xxxxxxxxxxxxxxx
- Date: Thu, 07 Apr 2005 09:31:15 -0600
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@xxxxxxxxxxx>
> To: oracle-l@xxxxxxxxxxxxx
> 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
>>--
>>//www.freelists.org/webpage/oracle-l
>>=20
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
--
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
