Tuesday, April 04, 2006

Using Resource Profiles

I never had the need to use Resource Profiles extensively. Recently, though, I've had the opportunity to investigate this feature.

First things first, the resouce_limit parameter must be set to TRUE. You can either set it in the init.ora or via ALTER SYSTEM.

Next, you create the profile and assign limits to it. Read the descriptions carefully, though, some of the resource parameters may sound self-explanatory, but aren't. For example, you would think SESSIONS_PER_USER would mean the number of times a particular user can login. In fact, it's the number of concurrent sessions that can run at one time.
SQL> create profile really_small limit
2 sessions_per_user 1
3 cpu_per_session 100
4 cpu_per_call 100
5 connect_time 5
6 /

Profile created.

Then you assign the profile to a particular user:
SQL> alter user jh profile really_small;

User altered.

Just for kicks, you can check that your profile is assigned to your user.
SQL> select username, profile from dba_users where username = 'JH';

USERNAME PROFILE
------------ ---------------
JH REALLY_SMALL

SQL> select resource_name, resource_type, limit
2 from dba_profiles
3 where profile = 'REALLY_SMALL';

RESOURCE_NAME RESOURCE LIMIT
-------------------------------- -------- ------------------
COMPOSITE_LIMIT KERNEL DEFAULT
SESSIONS_PER_USER KERNEL 1
CPU_PER_SESSION KERNEL 100
CPU_PER_CALL KERNEL 100
LOGICAL_READS_PER_SESSION KERNEL DEFAULT
LOGICAL_READS_PER_CALL KERNEL DEFAULT
IDLE_TIME KERNEL DEFAULT
CONNECT_TIME KERNEL 5
PRIVATE_SGA KERNEL DEFAULT
FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
PASSWORD_LIFE_TIME PASSWORD DEFAULT
PASSWORD_REUSE_TIME PASSWORD DEFAULT
PASSWORD_REUSE_MAX PASSWORD DEFAULT
PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
PASSWORD_LOCK_TIME PASSWORD DEFAULT
PASSWORD_GRACE_TIME PASSWORD DEFAULT

16 rows selected.


Your user connects to the database, starts running his monster query, and is promptly disconnected:

$ sqlplus jh/jh@mydb

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Apr 4 20:34:23 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

SQL> select count(*) from all_objects, all_objects, all_objects;
select count(*) from all_objects, all_objects, all_objects
*
ERROR at line 1:
ORA-02392: exceeded session limit on CPU usage, you are being logged off

Sweet.

6 comments:

Anonymous said...

Cool! Thanks for this example!
I just tested it - in 9i it is resource_limit= true.

regards
katrin

Jeff Hunter said...

You are absolutely right, not sure what I was thinking...

Jeff Hunter said...

Hey, I'm not saying it's the answer to all problems. It's just a feature. How you choose to implement it obviously affects its usability.

Beth Breidenbach said...

I vote for a combination of both profiles and resource manager.

We use profiles to enforce the number of concurrent sessions and to snipe sessions that sit idle too long. Otherwise there's a tendency to a) share userids and b) leave a screen with confidential data up when going to lunch. (Yes, I know a well-managed domain could manage the latter, but I don't work in a nicely controlled corporate environment any more....)

Resource Manager looks way cool for controlling run time and cpu priority. Sadly, our testing has hit a bug that Oracle's trying to resolve. If it ever _does_ work for us I'll post an example on my blog.

Anonymous said...

As someone who has had multiple problems and run into some fairly serious oracle bugs using and/or attempting to use resource manager I would point out that while HJR has some academic points worth considering sanity check points make what Jeff noted well worth considering in certain circumstances. Archaic? Deprecated? Those are terms that describe almost every part of oracle technology except the new features in the latest release.

Anonymous said...

And there's the thing about Resource Manager not recommended for use on a system where more than one instance is running. Infinitely preferable except for such systems...

New features are buggy, but if you're not on the latest feature you're archaic?