Oracle Long Running Queries

I will explain Oracle Long Running Queries in this post.

 

Oracle Long Running Queries

You can list the long running queries in the Oracle using the following scripts.

SELECT INST_ID,SID, SERIAL#, SQL_ID,CONTEXT,TIME_REMAINING, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM GV$SESSION_LONGOPS
WHERE OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0 AND TIME_REMAINING>0
AND SOFAR <> TOTALWORK order by 9 desc;

 

 

 

You can also use the following script to see TOP Elapsed Time SQL Statements using the following script.

 SELECT st.sql_text,
sub.sql_id,
sub.ELAPSED_TIME PER_EXEC_ELAPSED_TIME_MINUTES
FROM DBA_HIST_SQLTEXT st,
( SELECT t.sql_id,
ROUND (
SUM (t.elapsed_time_delta / 60000000)
/ SUM (t.executions_delta))
ELAPSED_TIME
FROM dba_hist_sqlstat t, dba_hist_snapshot s, DBA_HIST_SQLTEXT st
WHERE t.snap_id = s.snap_id
AND t.dbid = s.dbid
AND t.instance_number = s.instance_number
AND t.executions_delta > 0
AND s.BEGIN_INTERVAL_TIME >
TO_DATE ('06/08/2021 13:00:00',
'mm/dd/yyyy hh24:mi:ss')
AND END_INTERVAL_TIME <
TO_DATE ('06/08/2021 16:01:00',
'mm/dd/yyyy hh24:mi:ss')
GROUP BY t.sql_id
ORDER BY 2 DESC) sub
WHERE sub.sql_id = st.sql_id
ORDER BY 3 DESC;

 

 

 

 

 

Sometimes you need killing some long running sessions that coming from specific machines and specific Queries.

 

 

If you want to kill some long running sessions that coming from specific machines and specific Queries, then you can create dbms Oracle job for this.

 

Before creating Oracle dbms job, firstly we need to create procedure which will perform this job, then create dbms_scheduler job and use this procedure to kill related sessions.

 

 

 

 

 

I will keep session informations in the kill_sessions_info table, so I have create it like following.

create table sys.kill_sessions_info(killId number,username varchar2(100),stext varchar2(100),stime date,duration date)

 

Then create Procedure like following. This procedure will kill sessions that coming from ‘MEHMETSALIH\DEVECI’ machine and running more than 1200 second and sql_text not like ‘%SALES%’. You can change these conditions according to your needs.

 

CREATE OR REPLACE procedure SYS.kill_long_sessions as
BEGIN

FOR i IN (select s.sid||','||s.serial# killId,s.username username,ss.sql_fulltext stext,s.logon_time stime,s.last_call_et durat from 
v$session s, v$sql ss where s.machine='MEHMETSALIH\DEVECI' and s.last_call_et>1200 and
s.sql_id=ss.sql_id and ss.sql_fulltext not like '%SALES%' )

LOOP
declare
l_error VARCHAR2(2000):='';
begin
insert into sys.kill_sessions_info values(i.killId,i.username,i.stext,i.stime,i.durat);
commit;
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION '''||i.killId||''' IMMEDIATE';
EXCEPTION
WHEN OTHERS THEN
l_error:=SQLERRM;

end;
END LOOP;

COMMIT;

END;
/

 

After creating procedure, you can create DBMS_SCHEDULER job like following.

 

 

 

BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'SYS.KILL_LONG_SESSION'
,start_date => TO_TIMESTAMP_TZ('2018/12/06 16:00:00.000000 +03:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
,repeat_interval => 'FREQ=MINUTELY;INTERVAL=1'
,end_date => TO_TIMESTAMP_TZ('2090/08/18 00:00:00.000000 +03:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'STORED_PROCEDURE'
,job_action => 'SYS.KILL_LONG_SESSIONS'
,comments => NULL
);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'RESTARTABLE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'LOGGING_LEVEL'
,value => SYS.DBMS_SCHEDULER.LOGGING_OFF);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'MAX_FAILURES');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'MAX_RUNS');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'STOP_ON_WINDOW_CLOSE'
,value => FALSE);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'JOB_PRIORITY'
,value => 3);
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'SCHEDULE_LIMIT');
SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
( name => 'SYS.KILL_LONG_SESSION'
,attribute => 'AUTO_DROP'
,value => FALSE);

SYS.DBMS_SCHEDULER.ENABLE
(name => 'SYS.KILL_LONG_SESSION');
END;
/

 

 

you can change interval and start time according to your needs.

 

 

 

You can access the Oracle DBA scripts related to Performance Tuning using the following link.

 

Performance Tuning and SQL Monitoring Scripts | Oracle DBA Scripts All in One -4

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then read the following articles.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database