Control Resource Utilization in Oracle

Resource utilization can be controlled either through profiles or Oracle Database Resource Manager. Profiles are generally used to control the password protection, cpu usage assigned to users under profile. While Oracle Database Manager makes use
of total resource available in the hardware by distribution of the resources among the groups when certain resource plans are inactive. Managing resources through Database resource manager is the best solution to manage the utilization of cpu
usage, active session pool, degree of parallelism limit, automatic group switching mechanism, execution time limit, undo pool and idle time limit whereas profiles can be only temporary solution to manage resource utilization.

Here is an example to demonstrate manage cpu usage through profile.

Change the resource_limit parameter either in pfile or through the command line by executing

alter system set resource_limit=true;

CREATE PROFILE PROF_QUERY LIMIT
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 1000;

CPU_PER_SESSION UNLIMITED provides cpu usage to the session for UNLIMITED TIME
CPU_PER_CALL 1000 provides cpu usage to the SQL PARSE,EXECUTE,FETCH for 10 secs for every SQL Statement.

Thus the users under the profile PROF_QUERY can use resource for unlimited time in a session and for 10 secs for each Sql Statement.

Check the default profile of the user

select * from dba_users WHERE USERNAME='TEST'

Change the profile of the user

ALTER USER TEST PROFILE PROF_QUERY;

Check the parameters of profile

select * from dba_profiles where profile='PROF_QUERY'

To implement and modify Oracle Database Manager feature for resource utilization, change compatible parameter to 8.1.0.0.0 or above, from pfile

compatible='8.1.0.0.0'