Home » SQL & PL/SQL » SQL & PL/SQL » SQLs (completed & currently running ) in a session (Oracle 10g)
SQLs (completed & currently running ) in a session [message #326576] Thu, 12 June 2008 00:55 Go to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Is there a query which will give me the list of SQLs that have run & currently running since the start of a session ? I also need to know the start and end times (or) the time taken for each of the SQLs.

[Updated on: Thu, 12 June 2008 00:56]

Report message to a moderator

Re: SQLs (completed & currently running ) in a session [message #326581 is a reply to message #326576] Thu, 12 June 2008 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No query.
The only way is to put all session in trace mode.

Regards
Michel

[Updated on: Thu, 12 June 2008 01:05]

Report message to a moderator

Re: SQLs (completed & currently running ) in a session [message #326590 is a reply to message #326581] Thu, 12 June 2008 01:30 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Thanks Michel.
But I tried querying V$SESSION_LONGOPS, V$SQL, V$SESSION but couldn't single out the SQL that has taken very long time to complete or even currently long running SQLs.

I want to try out options before requesting for a trace output which is a bit difficult in terms of getting approvals.
Re: SQLs (completed & currently running ) in a session [message #326593 is a reply to message #326581] Thu, 12 June 2008 01:42 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
I want an output similar to the following query in DB2,


===============================
Here is an example query that shows you any statements that have been executing for more than one minute along with the authid, the status of the application and the first few characters of the statement text.
SELECT ELAPSED_TIME_MIN,SUBSTR(AUTHID,1,10) AS AUTH_ID, 
AGENT_ID,       
APPL_STATUS,
SUBSTR(STMT_TEXT,1,20) AS SQL_TEXT  
FROM SYSIBMADM.LONG_RUNNING_SQL 
WHERE ELAPSED_TIME_MIN > 0 
ORDER BY ELAPSED_TIME_MIN DESC

here are the results for this example

ELAPSED_TIME_MIN AUTH_ID AGENT_ID APPL_STATUS SQL_TEXT
---------------- -------- -------- ------------ ----------------- 6 EATON 878 LOCKWAIT update org set deptn


Here you can see that an application has been running for 6 minutes and is currently waiting on a lock.

================================

[Updated on: Thu, 12 June 2008 01:49] by Moderator

Report message to a moderator

Re: SQLs (completed & currently running ) in a session [message #326599 is a reply to message #326593] Thu, 12 June 2008 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put ONLY code inside code tags and your lines in 80 characters width.

v$session_longops only contains long operations. An operation is a part of query. Query may be long with many short operations, in this case it is not in v$session_longops.

v$session, from 10g, or v$session_wait contains current wait for each session.

Regards
Michel

[Updated on: Thu, 12 June 2008 01:53]

Report message to a moderator

Re: SQLs (completed & currently running ) in a session [message #326609 is a reply to message #326599] Thu, 12 June 2008 02:06 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Will v$sql joined with v$session help at the SQL level in a session ?
something like

sid SQL Start_time End_time
304 select c1,d1... 10:34:56 22:45:45

304 select a1,b1 10:34:56 10:34:59

Re: SQLs (completed & currently running ) in a session [message #326612 is a reply to message #326609] Thu, 12 June 2008 02:08 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Answer in my first post.

Regards
Michel
Previous Topic: Update using join of tables
Next Topic: help in trigger craetion.
Goto Forum:
  


Current Time: Sun Dec 11 02:40:40 CST 2016

Total time taken to generate the page: 0.19860 seconds