数据库性能是一个永恒的话题,那就是如何使用更少的资源以达到更高效的性能。Oracle系统参数RESOURCE_LIMIT是一个用于控制用户对于数据库资源使用的参数,当值为true的时候即为启用,否则禁用。该参数结合profile来可以控制多种资源的使用,如CPU_PER_SESSION, CONNECT_TIME,LOGICAL_READS_PER_SESSION,
PRIVATE_SGA等等从而达到到节省资源来实现高效性能。本文描述了数据资源限制并演示了IDLE_TIME及SESSIONS_PER_USER的用法。
1、数据库资源限制的主要步骤
Implemented by
* Setting RESOURCE_LIMIT = TRUE in the database startup parameter file (spfile or pfile)
* Creating or modifying existing user profiles (DBA_PROFILES) to have one or more resource limit
* Assigning a profile to a user whose resources are wished to be limited
It could happen that if the idle_time has been set on the DEFAULT profile, this can lead to an MTS dispatchers being set to 'sniped' and then getting 'cleaned up' via the shell script.
The removal of the dispatcher will result in other sessions 'dying' .In that case, If you are to implement resource limits, may be advisable to create new profiles that be assigned to users and not to change the characteristics of DEFAULT.
Alternatively, if you do change DEFAULT, ensure that all the properties that you have affected have been fully tested in a development environment.
用户超出限制后的完成的动作
When a resource limit is exceeded (for example IDLE_TIME) ... PMON does the following
* Mark the V$SESSION as SNIPED
* Clean up the database resources for the session
* Remove the V$SESSION entry
2、资源限制的配置
--演示环境SQL> select * from v$version where rownum<2;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production--查看参数resource_limitSQL> show parameter resource_limitNAME TYPE VALUE------------------------------------ ----------- ------------------------------resource_limit boolean FALSE--修改参数resource_limit为trueSQL> alter system set resource_limit=true;System altered.SQL> show parameter resource_limitNAME TYPE VALUE------------------------------------ ----------- ------------------------------resource_limit boolean TRUE--创建profile,其idle_time为3分钟SQL> create profile app_user limit idle_time 3; Profile created.--修改profile,限制每个用户只能开一个sessionSQL> alter profile app_user limit sessions_per_user 1;Profile altered.--将用户指派给特定的profileSQL> alter user scott profile app_user;User altered.--查看刚刚创建的profile,查询结果中的RESOURCE_NAME都可以作相应的设置或修改SQL> select * from dba_profiles where profile='APP_USER';PROFILE RESOURCE_NAME RESOURCE LIMIT------------------------------ -------------------------------- -------- ----------------------------------------APP_USER COMPOSITE_LIMIT KERNEL DEFAULTAPP_USER SESSIONS_PER_USER KERNEL 1APP_USER CPU_PER_SESSION KERNEL DEFAULTAPP_USER CPU_PER_CALL KERNEL DEFAULTAPP_USER LOGICAL_READS_PER_SESSION KERNEL DEFAULTAPP_USER LOGICAL_READS_PER_CALL KERNEL DEFAULTAPP_USER IDLE_TIME KERNEL 3APP_USER CONNECT_TIME KERNEL DEFAULTAPP_USER PRIVATE_SGA KERNEL DEFAULTAPP_USER FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULTAPP_USER PASSWORD_LIFE_TIME PASSWORD DEFAULTAPP_USER PASSWORD_REUSE_TIME PASSWORD DEFAULTAPP_USER PASSWORD_REUSE_MAX PASSWORD DEFAULTAPP_USER PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULTAPP_USER PASSWORD_LOCK_TIME PASSWORD DEFAULTAPP_USER PASSWORD_GRACE_TIME PASSWORD DEFAULT16 rows selected.
3、演示资源被限制的情形
C:\Users\robinson.cheng>sqlplus scott/tiger@oradb1SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 26 18:12:10 2013Copyright (c) 1982, 2010, Oracle. All rights reserved.SQL> host ----->开启一个sessionMicrosoft Windows [Version 6.1.7600]Copyright (c) 2009 Microsoft Corporation. All rights reserved.C:\Users\robinson.cheng>sqlplus scott/tiger@oradb1 --->尝试开启另一个sessioinSQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 26 18:12:21 2013Copyright (c) 1982, 2010, Oracle. All rights reserved.ERROR:ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit --->此时收到资源被限制的提示Enter user-name:--在服务器端查看session的情形,3分钟后用户scott 的session的状态被置为SNIPEDSQL> @comm_sess_users;+----------------------------------------------------+| User Sessions (All) |+----------------------------------------------------+Instance SID Serial ID Status Oracle User O/S User O/S PID Session Program Terminal Machine--------- ------ --------- --------- ----------- ------------ -------- --------------------- ---------- -------------------oradb 1 5 INACTIVE SYS oracle 10090 sqlplus@node1.szdb.co pts/1 node1.szdb.com 35 7 ACTIVE HR robin 10171 sqlplus@SZDB (TNS V1- pts/2 SZDB 40 237 SNIPED SCOTT Robinson.Che 13282 sqlplus.exe PC39 2GOTRADESZ\PC39--Author : Robinson--Blog : http://blog.csdn.net/robinson_0612--获得session的spidSQL> @my_spid_from_sidEnter value for input_sid: 40 SID SERIAL# SPID------ ---------- ------------------------------------- 40 237 13282--此时的时间为20:17:54SQL> ho dateWed Jun 26 20:17:54 CST 2013--查看scott对应的server process,其进程的启动时间为18:12,过了1个多小时,进程依旧没有被释放 SQL> ho ps -ef | grep 13282 | grep -v greporacle 13282 1 0 18:12 ? 00:00:00 oracleoradb (LOCAL=NO)--下面调用shell脚本来杀掉对应的进程SQL> host[oracle@node1 ~]$ ./kill_sniped.sh oradb13282[oracle@node1 ~]$ ps -ef | grep 13282 | grep -v grep--清除服务器进程的shell脚本[oracle@node1 ~]$ more kill_sniped.sh #!/bin/shexport ORACLE_SID=$1tmpfile=/tmp/tmp.$$sqlplus -S /nolog <<EOFconnect / as sysdbaset head off feedback offspool $tmpfileselect p.spid from v\$process p,v\$session swhere s.paddr=p.addrand s.status='SNIPED';spool offEOFfor x in `cat $tmpfile | grep "^[0123456789]"` do kill -9 $xdonerm $tmpfile
4、注意事项
NOTE:
If you are running in a shared server environment, you need to be careful not to accidentally kill your dispatchers and/or shared servers. In Oracle 10.2 (or higher) a dedicated connections V$SESSION + V$PROCESS + OS Process can be cleaned up with
ALTER SYSTEM DISCONNECT SESSION '<SID>,<SERIAL>' IMMEDIATE
At this point in versions prior to 10.2 and for shared server connections the only solution is to kill the session at the OS level (see Kill and ORAKILL above)
* Windows : use the orakill command .... orakill <ORACLE SID> <Thread ID> (see Note 69882.1 for details)
On occasions we see conditions where a database session has a V$SESSION.STATUS = SNIPED ... and the entry never goes away . This condition can be achieved by implementing Database Resource Limits + Profiles without DCD and allow the database session to exceed the limit in the profile
5、小结
a、参数RESOURCE_LIMIT = TRUE用于启用数据库资源配置限制
b、profile用于实现资源配置,创建profile或修改已存在的profile来调整各个具体资源配置
c、将profile指派给那些需要限制的用户
d、一旦被限制的用户超出所设定的阀值将收到资源配置相关的错误提示
e、被限制资源的session状态变成sniped
f、被限制资源的session对应的server process并没有被释放,需要手动释放或结合sqlnet.expire_date来进行释放
g、Reference:[ID 601605.1] Oracle 角色、配置文件 http://psoug.org/reference/profiles.html
更多参考
PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录
再分享一下我老师大神的人工智能教程吧。零基础!通俗易懂!风趣幽默!还带黄段子!希望你也加入到我们人工智能的队伍中来!https://blog.csdn.net/jiangjunshow