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).
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
Posts
