Use resource_limit and profile to restrict user connections

Database performance is an eternal topic, that is, how to use less resources to achieve more efficient performance. The Oracle system parameter resource_limit is a parameter used to control the user's usage of database resources. It is enabled when the value is true. Otherwise, it is disabled. This parameter can be used with profile to control the use of multiple resources, such as cpu_per_session, connect_time, logical_reads_per_session,
Private_sga and so on to save resources to achieve efficient performance. This article describes data resource restrictions and demonstrates idle_time and sessions_per_user usage.

 

1. Steps for database resource restrictions
Implemented
* 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 cocould 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' 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.

Actions completed after the user exceeds the limit
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. Resource Restriction Configuration

-- Demo environment SQL> select * from V $ version where rownum <2; banner alias Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-production -- View parameter resource_limitsql> show parameter resource_limitname type value =----------- modify resource_limit Boolean false -- modify parameter resource_limit to truesql> alter system set limit = true; system altered. SQL> show parameter resource_limitname type value exceed ----------- ------------------------------ resource_limit Boolean true -- create profile, and its idle_time is 3 minutes SQL> Create profile app_user limit idle_time 3; Profile created. -- modify the profile to restrict each user to only one sessionsql> alter profile app_user limit sessions_per_user 1; profile altered. -- assign the user to a specific profilesql> alter user Scott profile app_user; user altered. -- view the created profile. You can set or modify resource_name in the query result. SQL> select * From dba_profiles where profile = 'app _ user '; profile resource_name resource limit has been submitted -------- your app_user has kernel 1app_user has kernel defaultapp_user has kernel has idle_time kernel 3app_user connect_time kernel has encrypted password already password defaultapp_user password_reuse_time password defaultapp_user password_reuse_max password defaultapp_user login password defaultapp_user password_lock_time password defaultapp_user password_grace_time password default16 rows selected.

3. demonstrate resource restrictions

C: \ Users \ Robinson. cheng> sqlplus Scott/tiger @ oradb1sql * Plus: Release 11.2.0.1.0 production on Wed Jun 26 18:12:10 2013 copyright (c) 1982,201 0, Oracle. all rights reserved. SQL> host -----> enable a sessionmicrosoft windows [version 6.1.7600] Copyright (c) 2009 Microsoft Corporation. all rights reserved. c: \ Users \ Robinson. cheng> sqlplus Scott/tiger @ oradb1 ---> try to enable another sessioinsql * Plus: Release 11.2.0.1.0 produc Tion on Wed Jun 26 18:12:21 2013 copyright (c) 1982,201 0, Oracle. all rights reserved. error: ORA-02391: exceeded simultaneous sessions_per_user Limit ---> at this time, the message "enter user-Name: -- view the session on the server" is received, 3 minutes later, Scott's session status is set to snipedsql> @ comm_sess_users; + -------------------------------------------------- + | user sessions (all) | + region + instance SID s Erial ID status Oracle user o/s PID session program terminal machine --------- ------ --------- ----------- ------------ -------- ---------- role 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 2 gotr Adesz \ pc39 -- Author: Robinson -- Blog: Author> @ my_spid_from_sidenter value for input_sid: 40 Sid serial # spid ------ ---------- limit 40 237 13282 -- the current time is 20: 17: 54sql> Ho datewed Jun 26 20:17:54 CST 2013 -- View Scott's corresponding server process. The start time of the process is. After more than an hour, the process is still not released SQL> Ho PS-Ef | grep 13282 | grep-V greporacle 13282 1 0 1 8: 12? 00:00:00 oracleoradb (local = No) -- call the shell script below to kill the corresponding process SQL> host [Oracle @ node1 ~] $./Kill_sniped.sh oradb13282 [Oracle @ node1 ~] $ PS-Ef | grep 13282 | grep-V grep -- clear the shell script of the server process [Oracle @ node1 ~] $ More kill_sniped.sh #! /Bin/shexport oracle_sid = $1 tmpfile =/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. Notes
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
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. Summary
A. the parameter resource_limit = true is used to enable database resource configuration restrictions.
B. The profile is used to configure resources, create a profile, or modify an existing profile to adjust the specific resource configuration.
C. Assign the profile to the users who need the restriction.
D. Once the restricted user exceeds the set threshold, the system will receive an error message related to resource configuration.
E. Change the session Status of the resource to sniped.
F. The server process corresponding to the session of the resource to be restricted is not released. You need to manually release the server or use sqlnet. expire_date to release the server.
G, reference: [ID 601605.1] Oracle role, profile http://psoug.org/reference/profiles.html

 

 

More references

DML error logging

PL/SQL --> cursor

PL/SQL --> implicit cursor (SQL % found)

Batch SQL forall statements

Bulk collect clause for batch SQL

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan