Re: Select from V$OPEN_CURSOR chronologically ordered ?

From: <fitzjarrell_at_cox.net>
Date: 4 Mar 2005 10:56:11 -0800
Message-ID: <1109962571.083062.25110@g14g2000cwa.googlegroups.com>

Sybrand Bakker wrote:
> On 4 Mar 2005 03:11:33 -0800, spendius_at_muchomail.com (Spendius)
wrote:
>
> >Hi,
> >The following SELECT returns me all cursors opened by a session,
> >with the string "current" in front of the statement currently
> >being executed:
> >select
> > st.address||st.hash_value n,
> > decode(st.address||st.hash_value, sql_address||sql_hash_value,
'current'),
> > st.sql_text
> >from v$open_cursor oc,
> > v$session s,
> > v$sqltext st
> >where s.sid = &&1
> > and oc.sid = &&1
> > and oc.address = st.address
> >order by st.address, st.hash_value, st.piece
> >
> >Do you think there's a way to get this displayed with a sort
> >on some kind of timestamp stored somewhere in some obscure
> >X$Kxxxx table to get it listed in the chronological sense of
> >cursor openings ? I spent hours trying to figure out whether
> >it's possible but to no avail. (I'm talking about 8i and 9i
> >structures as well)
> >
> >Thanks.
>
> V$sqlarea stores the time when the statement was first parsed. In the
> majority of third party apps, not using bind variables, this would
> provide the correct result.
> Other than that: just trace the session and you will know.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

Re-writing your query to utilise v$sqlarea:

select

      sa.first_load_time,
      st.address||st.hash_value n,
      decode(st.address||st.hash_value, sql_address||sql_hash_value,
'current', 'not current') exec_status,
      st.sql_text
from  v$open_cursor oc,
      v$session s,
      v$sqltext st,
      v$sqlarea sa

where s.sid = &&1

  and oc.sid = &&1
  and oc.address = st.address
  and sa.sql_text like oc.sql_text||'%'

order by sa.first_load_time, st.address, st.hash_value, st.piece;

Output is ordered by first_load_time.

David Fitzjarrell Received on Fri Mar 04 2005 - 12:56:11 CST