Oracle profile parameter IDLE_TIME and CONNECT_TIME

通过创建profile然后授权给某个用户,可以限制用户的资源使用情况,限制的内容可以是CPU资源使用、内存资源使用和会话时间等,Oracle官方手册可以参考CREATE PROFILE
本文中只对IDLE_TIME和CONNCT_TIME进行测试。
手册中对这2个参数做了简单的说明

CONNECT_TIME Specify the total elapsed time limit for a session, expressed in minutes.

IDLE_TIME Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.

下面是实验过程,实验环境是11.2.0.4
在将这2个参数开启之间需要在会话级别或者全局级别开启resource_limit,默认为FALSE即为不开启。

SQL> show parameter resource_limit

PARAMETER_NAME                                               TYPE                   VALUE
------------------------------------------------------------ ---------------------- --------------------
resource_limit                                               boolean                FALSE
SQL> alter system set resource_limit = true scope=both;

系统已更改。

SQL> show parameter resource_limit

PARAMETER_NAME                                               TYPE                   VALUE
------------------------------------------------------------ ---------------------- --------------------
resource_limit                                               boolean                TRUE

测试脚本

rem
rem     Script: demo_profile.sql
rem     Author: Leo Zhang
rem     Dated:  2017.03.08
rem     Purpose:   Demostrate the profile parameter IDLE_TIME and CONNECT_TIME

--场景1:测试IDLE_TIME为1分钟
--session 1
alter user t_user profile default;
drop profile pro_test;
create profile pro_test limit
idle_time 1;

alter user t_user profile pro_test;

--session 2
sqlplus  t_user/t_user@t

--session1
col program for a20
col username for a10
select sid,serial#,username,status,program,type from v$session where username  = 'T_USER';
select sysdate from dual;
exec DBMS_LOCK.SLEEP(60);
select sysdate from dual;
select sid,serial#,username,status,program,type from v$session where username  = 'T_USER';
select sysdate from dual;


--场景2:测试CONNECT_TIME为1分钟

--session 1
alter user t_user profile default;
drop profile pro_test;
create profile pro_test limit
connect_time 1;
alter user t_user profile pro_test;

--session 2
sqlplus  t_user/t_user@t
create sequence SEQ_T
minvalue 1
nomaxvalue 
start with 1
increment by 1
cache 50;

create table T
(
  id     NUMBER,
  t_time TIMESTAMP(6)
);

set timing on

DECLARE
BEGIN
  FOR i IN 1 .. 1000000000 LOOP
      INSERT INTO T
        SELECT SEQ_T.NEXTVAL, SYSDATE FROM DUAL;
  END LOOP;
  COMMIT;
END;
/

--session 1 
col program for a20
col username for a10
select sid,serial#,username,status,program,type from v$session where username  = 'T_USER';
select sysdate from dual;
exec DBMS_LOCK.SLEEP(60);
select sysdate from dual;
select sid,serial#,username,status,program,type from v$session where username  = 'T_USER';
select sysdate from dual;

测试结果

场景1:

SQL> select sid,serial#,username,status,program,type from v$session where username  = 'T_USER';

       SID    SERIAL# USERNAME   STATUS   PROGRAM              TYPE
---------- ---------- ---------- -------- -------------------- ----------
        48        471 T_USER     INACTIVE sqlplus.exe          USER

SQL> select sysdate from dual;

SYSDATE
-------------------
2017-03-09 09:00:05

SQL> exec DBMS_LOCK.SLEEP(60);

PL/SQL 过程已成功完成。

SQL> select sysdate from dual;

SYSDATE
-------------------
2017-03-09 09:01:05

SQL> select sid,serial#,username,status,program,type from v$session where username  = 'T_USER';

       SID    SERIAL# USERNAME   STATUS   PROGRAM              TYPE
---------- ---------- ---------- -------- -------------------- ----------
        48        471 T_USER     INACTIVE sqlplus.exe          USER

SQL> select sysdate from dual;

SYSDATE
-------------------
2017-03-09 09:01:13

SQL> select sysdate from dual;

SYSDATE
-------------------
2017-03-09 09:01:21

SQL> select sid,serial#,username,status,program,type from v$session where username  = 'T_USER';

       SID    SERIAL# USERNAME   STATUS   PROGRAM              TYPE
---------- ---------- ---------- -------- -------------------- ----------
        48        471 T_USER     INACTIVE sqlplus.exe          USER

在1分钟多之后,会话并没有被KILL掉。再等待一会之后,PMON进程的trc文件中出现了

*** 2017-03-09 09:02:28.604
killing idle session 0xd14616a0 num=48 serial=471 uid=239

SID=48的会话被KILL掉。
再过一会进行查询

SQL> select username,status,program,type from v$session where username  = 'T_USER';

USERNAME   STATUS           PROGRAM              TYPE
---------- ---------------- -------------------- --------------------
T_USER     SNIPED           sqlplus.exe          USER

会话状态从INACTIVE变成了SNIPPED。
按照v$session手册中对status的描述,SNIPED – An inactive session that has exceeded some configured limits (for example, resource limits specified for the resource manager consumer group or idle_time specified in the user’s profile). Such sessions will not be allowed to become active again.

在session 2会话窗口随便查询一个语句,PMON后台trc中继续出现

*** 2017-03-09 09:04:40.875
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=30, post_num=19
deleting session 0xd14616a0 sid=48 serial=471
deletion of orphaned session 0xd14616a0 successful

对IDLE_TIME是1分钟、2分钟都进行了测试,都无法在接近时间内pmon回收掉这样的会话。

场景2

--session 2 output
SQL> set timing on
SQL>
SQL> DECLARE
  2  BEGIN
  3    FOR i IN 1 .. 1000000000 LOOP
  4        INSERT INTO T
  5          SELECT SEQ_T.NEXTVAL, SYSDATE FROM DUAL;
  6    END LOOP;
  7    COMMIT;
  8  END;
  9  /
DECLARE
*
第 1 行出现错误:
ORA-02399: 超出最大连接时间, 您将被注销
ORA-06512: 在 line 4


已用时间:  00: 00: 59.39

--session 1 output
SQL> select sid,serial#,username,status,program,type from v$session where username  = 'T_USER';

       SID    SERIAL# USERNAME   STATUS   PROGRAM              TYPE
---------- ---------- ---------- -------- -------------------- ----------
        88       2765 T_USER     ACTIVE   sqlplus.exe          USER

SQL> select sysdate from dual;

SYSDATE
-------------------
2017-03-09 09:26:57

SQL> exec DBMS_LOCK.SLEEP(60);

PL/SQL 过程已成功完成。

SQL> select sysdate from dual;

SYSDATE
-------------------
2017-03-09 09:27:57

SQL> select sid,serial#,username,status,program,type from v$session where username  = 'T_USER';

未选定行

会话已经被回收

PMON进程trace信息

*** 2017-03-09 09:26:58.899
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=71

*** 2017-03-09 09:27:01.144
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=72
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=73
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=73
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=74
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=75

*** 2017-03-09 09:27:16.668
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=76

*** 2017-03-09 09:27:17.087
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=77
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=78
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=79
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=80
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=81
PMON ignoring post

*** 2017-03-09 09:27:42.632
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=82

*** 2017-03-09 09:27:43.112
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=83
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=84
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=85
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=86
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=87
PMON ignoring post

*** 2017-03-09 09:27:46.427
PMON woken up to process network events

*** 2017-03-09 09:27:49.429
PMON woken up to process network events

*** 2017-03-09 09:27:52.279
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=88
PMON woken up to process network events
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=89
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=90
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=91
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=92
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=93
PMON ignoring post

*** 2017-03-09 09:27:58.826
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=94
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=95
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=96
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=97

*** 2017-03-09 09:28:06.859
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=98
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=99
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=100
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=101
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=102
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=103
PMON ignoring post

*** 2017-03-09 09:28:10.260
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=104
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=105
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=106
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=107
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=108
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=109
PMON ignoring post

*** 2017-03-09 09:28:13.720
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=110

*** 2017-03-09 09:28:13.820
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=111
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=112
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=113
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=114
PMON last posted from location=FILE:/ade/b/2960201551/oracle/rdbms/src/hdir/ksu.h LINE:14056 ID:ksupsc, process=11, post_num=115
PMON ignoring post

Oracle推荐使用Database Resource Manager 来建立资源限制管理,DBRM可以提供更细致的管理方式,具体请查看Managing Resources with Oracle Database Resource Manager.

此条目发表在DBRM, Trace分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s