Re: Query capture

From: stephen booth <stephenbooth.uk_at_gmail.com>
Date: Thu, 7 Apr 2005 17:19:44 +0100
Message-ID: <687bf9c405040709192e479f96@mail.gmail.com>

On Apr 7, 2005 4:31 PM, Wolfgang Breitling <breitliw_at_centrexcc.com> 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.
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 07 2005 - 12:23:47 CDT