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
Received on Thu Apr 07 2005 - 11:18:39 CDT
