Home » SQL & PL/SQL » SQL & PL/SQL » how to find the sql statement and how long it is running
how to find the sql statement and how long it is running [message #187888] Wed, 16 August 2006 04:15 Go to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Hi all,

i want find out the all sql statement(or proc/function) which is currently running and how long it is being running in the server.

how to do it?

Thanks,
Thangam
Re: how to find the sql statement and how long it is running [message #187889 is a reply to message #187888] Wed, 16 August 2006 04:23 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Using Oracle Enterprise Manager Console u can achieve this..
If u want to see from backend use the below query.

SELECT sql_text, address
       ,hash_value ,a.sid,a.user_name,machine,terminal,program
       ,b.type,logon_time
 FROM v$open_cursor a , v$session b 
WHERE a.sid = b.sid 
  AND a.address = b.sql_address 
  AND a.hash_value = b.sql_hash_value
  AND b.status = 'ACTIVE'


But the above will not provide u with the full sql statement..
I am not sure how to get the sql statement fully..

Let's wait till some one comes with the accurate answer...

Naveen



[Updated on: Wed, 16 August 2006 04:24]

Report message to a moderator

Re: how to find the sql statement and how long it is running [message #187891 is a reply to message #187889] Wed, 16 August 2006 04:33 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Naveen,
here i did not get "how long is query running"

Re: how to find the sql statement and how long it is running [message #187932 is a reply to message #187888] Wed, 16 August 2006 07:00 Go to previous messageGo to next message
vino4ever
Messages: 11
Registered: July 2006
Location: Chennai
Junior Member
Hi Naveen,

Just add v$SQL as given in the below query,

SELECT a.sql_text, a.address, a.hash_value, a.SID, a.user_name, machine,
       terminal, program, b.TYPE, logon_time, c.elapsed_time
  FROM v$open_cursor a, v$session b, v$sql c
 WHERE a.SID = b.SID
   AND a.address = b.sql_address
   AND a.hash_value = b.sql_hash_value
   AND a.address = c.address
   AND b.status = 'ACTIVE'


Thanks
Vinod
Re: how to find the sql statement and how long it is running [message #188030 is a reply to message #187888] Wed, 16 August 2006 17:08 Go to previous message
oracle_scorpiongirl
Messages: 39
Registered: November 2005
Member
How about something like:

column osuser format a15;
column username format a10;
column status format a8;
column logon format a14;
column SESSION format a15;
set pagesize 150;
set linesize 120;
set echo off
select
s.osuser,
s.username,
s.program,
''''||s.sid||','||s.serial#||'''' "SESSION",
s.process,
p.spid,
p.pid,
s.status,
q.ROWS_PROCESSED,
q.executions,
q.sql_text
from v$session s, v$process p, v$sqlarea q
where (s.paddr=p.addr)
and (s.sql_address = q.address)
and s.username is not null
and s.status = 'ACTIVE'
and s.username <> 'SYS'
/

Previous Topic: DBMS_LOCK.sleep(5) giving error
Next Topic: Pivot Select query for a Report
Goto Forum:
  


Current Time: Thu Dec 08 18:29:54 CST 2016

Total time taken to generate the page: 0.12897 seconds