From mark@cool-tools.co.uk Wed, 06 Jun 2001 01:07:55 -0700
From: "Mark Leith" <mark@cool-tools.co.uk>
Date: Wed, 06 Jun 2001 01:07:55 -0700
Subject: RE: Which SQL is executing
Message-ID: <F001.0031D7C7.20010606004547@fatcity.com>
MIME-Version: 1.0
Content-Type: text/plain



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@fatcity.com 
  [mailto:root@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@fatcity.com 
    href="mailto:ORACLE-L@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@fatcity.com 
      href="mailto:ORACLE-L@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