How to Limit data warehouse DB query to run more than one hour


Jephe Wu - http://linuxtechres.blogspot.com

Objective: to limit a particular schema/user to run not more than one hour
Environment: Oracle 11.2.3.0 enterprise edition 64bit on AIX 7.1


Method 1: Require Oracle Enterprise Edition  - use OEM DB console

1. create a Oracle database user/schema
sqlplus / as sysdba
sql> create user user1 identified by oracle;
sql> grant connect,resource to user1

2.  use Enterprise Manager DB Console, server tab, Consumer Groups

create a special group called 'test'. Then click user button to add 'user1' to this group.

3. go to 'Consumer Group Mappings', this is critical, select 'Oracle user', click on button 'Add Rule for Selected Type', then choose user 'user1'.

4. click on Plan, create a plan called 'myplan', besides 'Other_groups' group, add user 'test' into this plan. Make 'Execution Time Limit(Sec) as 3600, and choose 'Cancel SQL' as action.

5. activate plan 'myplan', only one of the plans can be activated anytime.

Note: if you need to modify 'myplan', you need to deactivate plan first.

6. double check settings from command line

sqlplus test1
SQL> select username,resource_CONSUMER_GROUP,count(*) from v$session group by username,resource_CONSUMER_GROUP;

USERNAME       RESOURCE_CONSUMER_GROUP  COUNT(*)
------------------------------ -------------------------------- ----------
1
DBSNMP       OTHER_GROUPS 3
SYSMAN       OTHER_GROUPS 7
SYS       OTHER_GROUPS 1
      _ORACLE_BACKGROUND_GROUP_ 21
USER1       TEST 1

6 rows selected.

Note: you should see user1 is tied to 'test' resource group, not 'other_groups'.

7. testing (you can use 1 sec for testing purpose , instead of 3600s)

sqlplus test1
SQL> select count(*) from dba_segments, dba_extents, dba_tables;
select count(*) from dba_segments, dba_extents
                     *
ERROR at line 1:
ORA-00040: active time limit exceeded - call aborted

Method 2: Require Oracle Enterprise Edition - use resource manager SQL script CLI, refer to http://www.pythian.com/news/2740/oracle-limiting-query-runtime-without-killing-the-session/


[oracle@oratest bin]$ more resman.sh
#!/bin/sh

sqlplus / as sysdba @resman.sql

sleep 2

sqlplus jephe/jephe @resman2.sql

[oracle@oratest bin]$ more resman.sql
set serverout on size 5555
--
-- first remove an existing active plan
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='';
--
-- delete any existing plan or group
-- we have to create a pending area first
exec dbms_resource_manager.clear_pending_area();
exec dbms_resource_manager.create_pending_area();
exec dbms_resource_manager.DELETE_PLAN ('LIMIT_EXEC_TIME');
exec dbms_resource_manager.DELETE_CONSUMER_GROUP ('GROUP_WITH_LIMITED_EXEC_TIME');
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;

exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
begin
  dbms_resource_manager.create_pending_area();
  --
  -- we need a consumer group that maps to the desired oracle user:
  dbms_resource_manager.create_consumer_group(
    CONSUMER_GROUP=>'GROUP_WITH_LIMITED_EXEC_TIME',
    COMMENT=>'This is the consumer group that has limited execution time per statement'
    );
  dbms_resource_manager.set_consumer_group_mapping(
    attribute => DBMS_RESOURCE_MANAGER.ORACLE_USER,
    value => 'JEPHE',
    consumer_group =>'GROUP_WITH_LIMITED_EXEC_TIME'
  );

  -- and we need a resource plan:
  dbms_resource_manager.create_plan(
    PLAN=> 'LIMIT_EXEC_TIME',
    COMMENT=>'Kill statement after exceeding total execution time'
  );

  -- now let's create a plan directive for that special user group
  -- the plan will cancel the current SQL if it runs for more than 120 sec
  dbms_resource_manager.create_plan_directive(
    PLAN=> 'LIMIT_EXEC_TIME',
    GROUP_OR_SUBPLAN=>'GROUP_WITH_LIMITED_EXEC_TIME',
    COMMENT=>'Kill statement after exceeding total execution time',
    SWITCH_GROUP=>'CANCEL_SQL',
    SWITCH_TIME=>3600,  # adjust this to your case
    SWITCH_ESTIMATE=>false
  );

  dbms_resource_manager.create_plan_directive(
    PLAN=> 'LIMIT_EXEC_TIME',
    GROUP_OR_SUBPLAN=>'OTHER_GROUPS',
COMMENT=>''
  );

  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

end;
/

exec dbms_resource_manager_privs.grant_switch_consumer_group('JEPHE','GROUP_WITH_LIMITED_EXEC_TIME',false);

exec dbms_resource_manager.set_initial_consumer_group('JEPHE','GROUP_WITH_LIMITED_EXEC_TIME');

-- to enable it:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='LIMIT_EXEC_TIME';

select username,resource_CONSUMER_GROUP,count(*) from v$session group by username,resource_CONSUMER_GROUP;
-------------------------
exit;

[oracle@oratest bin]$ more resman2.sql
select username,resource_CONSUMER_GROUP,count(*) from v$session group by username,resource_CONSUMER_GROUP;
select count(*) from dba_segments, dba_extents,dba_tables;
exit;

[oracle@oratest bin]$ more remove_all.sql
set serverout on size 5555
--
-- first remove an existing active plan
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='';
--
-- delete any existing plan or group
-- we have to create a pending area first
exec dbms_resource_manager.clear_pending_area();
exec dbms_resource_manager.create_pending_area();
exec dbms_resource_manager.DELETE_PLAN ('LIMIT_EXEC_TIME');
exec dbms_resource_manager.DELETE_CONSUMER_GROUP ('GROUP_WITH_LIMITED_EXEC_TIME');

Method 3:  use 'alter system kill session' to kill the entire session, not query itself, doesn't require Oracle Enterprise Edition


# cat  /home/oracle/scripts/limit_exec_time.sh
#!/usr/bin/bash

# Variables
export PATH=/usr/bin:/usr/local/bin:/usr/ccs/bin:/u01/app/oracle/product/11.2.0/dbhome_1/bin/
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS' # So time is shown in logs as well as date
export ORACLE_SID=oradb

ORAENV_ASK=NO
. oraenv
unset ORAENV_ASK

export PREPATH=/home/oracle/scripts

sqlplus / as sysdba @/home/oracle/scripts/query_exec_time.sql
sleep 1
grep alter $PREPATH/limit_exec_time.sql 
if [ $? -eq 0 ];then
sqlplus / as sysdba @$PREPATH/limit_exec_time.sql > $PREPATH/result.txt 2>&1
mail -s "session killed" jwu@domain.com < $PREPATH/result.txt 
fi



oracle@oradev1 (oradb)> cat query_exec_time.sql 
set head off feedback off echo off term off pagesize 0  linesize 32767 trimsp on tab off;
spool /home/oracle/scripts/limit_exec_time.sql
select 'select sid,sql_text from v$sqltext , v$session where v$sqltext.address = v$session.sql_address and sid in ' || sid || ';' from v$session where status='ACTIVE' AND username ='JEPHE' and last_call_et/120 > 1;
select 'alter system kill session ''' || sid ||','|| serial# || ''';' from v$session where status='ACTIVE' AND username ='JEPHE' and last_call_et/1800 > 1;
select 'exit;' from dual;
spool off;
exit;


* * * * *  /home/oracle/scripts/limit_exec_time.sh 

Note: above scripts will detect user 'JEPHE' , kill the session which run statement more than 1800s (half hour).


References:

1.  v$session explanation

LAST_CALL_ET NUMBER If the session STATUS is currently ACTIVE, then the value represents the elapsed time in seconds since the session has become active.
If the session STATUS is currently INACTIVE, then the value represents the elapsed time in seconds since the session has become inactive.

2. long running sessions




4. Should Sessions be Killed in OS or Using Alter System Kill Session? [ID 161794.1]
Do not kill the sessions at the OS level.
Use ALTER SYSTEM KILL SESSION 'sid, serial#';   

This acts in the following manner:

1. It terminates a session, rolls back ongoing transactions, releases all 
session locks, frees all session resources.

2. If the session is performing some activity that must be completed (eg 
waiting for a reply from a remote database or rolling back a transaction), 
Oracle waits for this activity to complete, kills the session then returns 
control.

3).If the wait lasts for 60 seconds then Oracle marks the session to be killed, 
and returns control with a message that the session is marked to be killed. It 
then gets killed when the activity is complete.

and 

IMMEDIATE Specify IMMEDIATE to instruct Oracle Database to roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately.'

6. search 'cancel long run query' in oracle support

Method 4: use event to simulate ^C operation to cancel SQL only, not entire session. It will cancel the whole session of PL/SQL, not inside individual statement. For normal scipts, it will cancel individual statement.

*/5 * * * * /home/oracle/bin/cancel_query/limit_exec_time.sh > /dev/null 2>&1


oracle@ora1 > cat /home/oracle/bin/cancel_query/limit_exec_time.sh
#!/usr/bin/bash
# purpose: detect if there's any statement running more than 1 hours under user FINANCE, cancel those SQL statementes if any.
# Variables
export PATH=/usr/bin:/usr/local/bin:/u01/app/oracle/product/11.2.0/bin
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS' # So time is shown in logs as well as date
export ORACLE_SID=PROD
ORAENV_ASK=NO
. oraenv
unset ORAENV_ASK

export PREPATH=/home/oracle/bin/cancel_query
rm -f $PREPATH/limit_exec_time.sql
sqlplus / as sysdba @$PREPATH/query_exec_time.sql
sleep 1
fgrep -q ',' $PREPATH/limit_exec_time.sql 
if [ $? -eq 0 ];then

cd $PREPATH

while read line
do 
SID=`echo $line | awk -F',' '{print $1}'`
SERIAL=`echo $line | awk -F',' '{print $2}'`
sed -e "s#SID#$SID#g" -e "s#SERIAL#$SERIAL#g" template.sql > 1.sql
sqlplus / as sysdba @$PREPATH/1.sql >/dev/null 2>&1
mail -s "query cancelled" jephe@domain.com < $PREPATH/limit_exec_time.sql
done  < limit_exec_time.sql

fi


oracle@ora1 > cat query_exec_time.sql 
set head off feedback off echo off term off pagesize 0  linesize 32767 trimsp on tab off;
spool /home/oracle/bin/cancel_query/limit_exec_time.sql
select sid ||','|| serial# from v$session where status='ACTIVE' AND username ='FINANCE' and last_call_et/3600 > 1;
spool off;
exit;


$ cat /home/oracle/bin/cancel_query/template.sql


DECLARE
  l_status  v$session.status%TYPE;
BEGIN

  dbms_system.set_ev( SID, SERIAL, 10237, 1, '');

  LOOP
    SELECT status INTO l_status FROM v$session
      WHERE sid = SID and serial# = SERIAL;
    EXIT WHEN l_status='INACTIVE';
  END LOOP;

  dbms_system.set_ev( SID, SERIAL, 10237, 0, '');
END;
/
exit;


8. References:

http://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm001.htm
http://www.pythian.com/news/2740/oracle-limiting-query-runtime-without-killing-the-session/
http://www.oracle.com/technetwork/database/performance/resource-manager-twp-133705.pdf
Oracle University course materials