OraFAQ Forum: Performance Tuning » How do I know who are running the long running ops sql?

Home » RDBMS Server » Performance Tuning » How do I know who are running the long running ops sql? (12.1.0.2 RH7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How do I know who are running the long running ops sql? [message #672083]

Tue, 02 October 2018 02:32

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear all,

When I want to find out who is responsible for long running ops sql, I used the following query:


SELECT * FROM(
SELECT row_number() OVER (ORDER BY longops.start_time) rn
,sql.sql_id, longops.sid, longops.serial#, longops.opname, longops.target, to_char(longops.start_time, 'YYYY-MM-DD HH24:MI:SS') start_time_c
,to_char(LAST_UPDATE_TIME, 'YYYY-MM-DD HH24:MI:SS') LAST_UPDATE_TIME_c, to_char(timestamp, 'YYYY-MM-DD HH24:MI:SS.FF TZR') timestamp_c
, longops.time_remaining, longops.elapsed_seconds, sql.sql_fulltext FROM V$SESSION_LONGOPS longops JOIN v$SQL sql ON sql.sql_id = longops.sql_id
ORDER BY longops.start_time DESC
) WHERE rn>=1 AND rn<=10;

However if I want to find out who responsible for it can I join with gv$session as follow:


SELECT * FROM(
SELECT row_number() OVER (ORDER BY longops.elapsed_seconds DESC, longops.time_remaining DESC, longops.start_time) rn
,instance.inst_id, instance.instance_number, instance.instance_name
,gv_session.username, gv_session.osuser, gv_session.machine,gv_session.program
,sql.sql_id, longops.sid, longops.serial#, longops.opname, longops.target, to_char(longops.start_time, 'YYYY-MM-DD HH24:MI:SS') start_time_c
,to_char(LAST_UPDATE_TIME, 'YYYY-MM-DD HH24:MI:SS') LAST_UPDATE_TIME_c, to_char(timestamp, 'YYYY-MM-DD HH24:MI:SS.FF TZR') timestamp_c
, longops.time_remaining, longops.elapsed_seconds, sql.sql_fulltext FROM gV$SESSION_LONGOPS longops JOIN gv$SQL sql ON sql.sql_id = longops.sql_id
AND sql.inst_id = longops.inst_id
JOIN gv$session gv_session ON gv_session.sid=longops.sid AND gv_session.serial#=longops.serial#
AND gv_session.inst_id=longops.inst_id AND gv_session.inst_id=longops.inst_id
JOIN gv$instance instance ON instance.inst_id =gv_session.inst_id
AND instance.inst_id =sql.inst_id
AND instance.inst_id =longops.inst_id
ORDER BY longops.elapsed_seconds DESC, longops.time_remaining DESC, longops.start_time
) WHERE rn>=1 AND rn<=10;

To me it does not seems correct because from https://docs.oracle.com/database/121/REFRN/GUID-28E2DC75-E157-4C0A-94AB-117C205789B9.htm#REFRN30223

and https://docs.oracle.com/database/121/REFRN/GUID-F2802C36-066C-493E-8255-C08CC79B87FB.htm#REFRN30227

if it does mentioned about join these views using sid and serial#.

many thanks in advance

[Updated on: Tue, 02 October 2018 02:33]

Report message to a moderator

Re: How do I know who are running the long running ops sql? [message #672084 is a reply to message #672083]

Tue, 02 October 2018 02:45

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Format your query, if you don't know how to do it, learn it using SQL Formatter.

Report message to a moderator

Previous Topic:	Transaction Isolation Level
Next Topic:	No more data to read from socket

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 09 00:56:04 CST 2025