RE: Which SQL is executing

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Wed, 06 Jun 2001 01:07:55 -0700
Message-ID: <F001.0031D7C7.20010606004547@fatcity.com>

Try
this out my friend:
<FONT face=Arial color=#0000ff

size=2> 
select
s.sid sid,
<FONT face=Arial color=#0000ff

size=2>          c.sql_text
sql_text,
<FONT face=Arial color=#0000ff

size=2>          c.address address,

<FONT face=Arial color=#0000ff

size=2>         
ltrim(to_char(c.hash_value,'9999999999'),' ') hash_value,
<FONT face=Arial color=#0000ff

size=2>          substrb(' 
',1,2) execcur     from v$open_cursor c,
<FONT face=Arial color=#0000ff

size=2>           v$session 
s  where c.saddr = s.saddr
<FONT face=Arial color=#0000ff

size=2>   and c.hash_value != s.sql_hash_value
<FONT face=Arial color=#0000ff

size=2>   and s.sid in
(THE_SID_U_ARE_INTERESTED_IN) union select s.sid sid,

<FONT face=Arial color=#0000ff

size=2>           c.sql_text
sql_text,
<FONT face=Arial color=#0000ff

size=2>           c.address
address, ltrim(to_char(c.hash_value,'9999999999'),' ') hash_value,

<FONT face=Arial color=#0000ff

size=2>           substrb('*
',1,2) execcur    from v$open_cursor c, v$session  s  where c.saddr = s.saddr
<FONT face=Arial color=#0000ff

size=2>   and c.hash_value = s.sql_hash_value
<FONT face=Arial color=#0000ff

size=2>   and s.sid in
(THE_SID_U_ARE_INTERESTED_IN);
<FONT face=Arial color=#0000ff

size=2> 
The
currently executing cursor will be the one with * in the execcur column.
<FONT face=Arial color=#0000ff

size=2> 
<FONT face=Arial color=#0000ff

size=2>HTH
<FONT face=Arial color=#0000ff

size=2> 
<FONT face=Arial color=#0000ff

size=2>Mark

  <FONT face=Tahoma
  size=2>-----Original Message-----From: root_at_fatcity.com   [mailto:root_at_fatcity.com]On Behalf Of Chuan ZhangSent:   Wednesday, June 06, 2001 08:25To: Multiple recipients of list   ORACLE-LSubject: Re: Which SQL is executing   Hi, Sam,

   
   Thanks for your information. 
   

  To my understanding, the script you gave is   for all the queries. There is still no way to figure out which query is   running.
   
  Chuan,
  <BLOCKQUOTE
  style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

• Original Message ----- <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: Sam Roberts To: <A title=ORACLE-L_at_fatcity.com href="mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list ORACLE-L

    Sent: Monday, June 04, 2001 5:00
    PM
    Subject: Re: Which SQL is
    executing     

    SELECT T.SQL_TEXT FROM V$SQLTEXT T,V$SESSION S      WHERE  S.SQL_ADDRESS=T.ADDRESS ORDER BY T.PIECE;      
    Sam
    <BLOCKQUOTE
    style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

• Original Message ----- <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: Chuan Zhang To: <A title=ORACLE-L_at_fatcity.com href="mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list ORACLE-L Sent: Monday, June 04, 2001 9:30 AM Subject: Which SQL is executing

      Hi All,
       
       
      From v$open_cursor, I know every SQL  
      opened and parsed in one session. Is there any way to know which SQL is 
      running. Or put another way, can all the SQLs in one session be 
      sorted in timing order dynamically?
       
      Any clue would be much 
      appreciated.
       
      <FONT face=Arial 

size=2>Chuan Received on Wed Jun 06 2001 - 03:07:55 CDT