Home » RDBMS Server » Performance Tuning » How do I know who are running the long running ops sql? (12.1.0.2 RH7)
How do I know who are running the long running ops sql? [message #672083] |
Tue, 02 October 2018 02:32 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/82920145e40e0c65c671e4d0b8212ab7?s=64&d=mm&r=g) |
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
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 21:24:30 CDT 2024
|