|
|
|
|
| Re: SQLs (completed & currently running ) in a session [message #326590 is a reply to message #326581] |
Thu, 12 June 2008 01:30   |
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 |
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 |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
