Re: Query capture
- From: stephen booth <stephenbooth.uk@xxxxxxxxx>
- To: breitliw@xxxxxxxxxxxxx
- Date: Thu, 7 Apr 2005 17:19:44 +0100
On Apr 7, 2005 4:31 PM, Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:
>
> 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).
>
I had a bit of a play around with the SQL John posted and came up with this:
--------------------------------------------------------------------------------------------------------------
set linesize 132
set pagesize 100
break on query
column query format a50 wrap heading "Query Text"
column timeinseconds format 999999999.99 heading "Time In Seconds"
column place format 9999999 heading "Place"
select rownum place, query, timeinseconds, cur_id, address
from (
select a.sql_text query,
a.elapsed_time/(1000000*a.executions) timeinseconds,
a.hash_value cur_id,
a.address
from v$sql a
where a.executions>0
and (a.elapsed_time/(1000000*a.executions)) > 5
order by timeinseconds desc)
/
----------------------------------------------------------------------------------------------------------
I noticed I was getting some rows which had nothing in the SQL_TEXT
column, when I looked them up in v$sqltext, via the hash_value or the
address, the text was there.
Stephen
--
It's better to ask a silly question than to make a silly assumption.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
