Profiles and Resource Control - Oracle

The list of resource-control profile options that can appear afterCREATE PROFILE profilename LIMITare explained in Table 31-4. Each of these parameters can either be an integer,UNLIMITED, orDEFAULT.

Table 31-4. Resource-Related Profile Parameters

As with the password-related parameters,UNLIMITEDmeans that there is no bound on how much of the given resource can be used.DEFAULTmeans that this parameter takes its values from theDEFAULTprofile.

TheCOMPOSITE_LIMITparameter allows you to control a group of resource limits when the types of resources typically used varies widely by type; it allows a user to use a lot of CPU time but not much disk I/O during one session, and vice versa, during another session, without being disconnected by the policy.

By default, all resource costs are zero:

SQL> select * from resource_cost;

RESOURCE_NAME                  UNIT_COST
----------------------------- ----------
CPU_PER_SESSION                        0 LOGICAL_READS_PER_SESSION              0 CONNECT_TIME                           0 PRIVATE_SGA                            0
4 rows selected.

To adjust the resource cost weights, use theALTER RESOURCE COSTcommand. In this example, we change the weightings so thatCPU_PER_SESSIONfavors CPU usage over connect time by a factor of 25 to 1; in other words, a user will be disconnected more likely because of CPU usage than because of connect time:

SQL> alter resource cost
  2      cpu_per_session 50
  3      connect_time 2;
Resource cost altered.
SQL> select * from resource_cost;

RESOURCE_NAME                 UNIT_COST
---------------------------- ---------- 
CPU_PER_SESSION                      50 LOGICAL_READS_PER_SESSION             0 CONNECT_TIME                          2 PRIVATE_SGA                           0
4 rows selected.

The next step is to create a new profile or modify an existing profile to use a composite limit:

SQL> create profile lim_comp_cpu_conn limit
  2      composite_limit 250;
Profile created.

As a result, users assigned to the profileLIM_COMP_CPU_CONNwill have their session resources limited using the following formula to calculate cost:

composite_cost = (50 * CPU_PER_SESSION) + (2 * CONNECT_TIME);

In Table 31-5, we provide some examples of resource usage to see if the composite limit of 250 is exceeded.

Table 31-5. Resource Usage Scenarios  

The parametersPRIVATE_SGAandLOGICAL_READS_PER_SESSIONare not used in this particular example, so unless they are specified otherwise in the profile definition, they default to whatever their value is in theDEFAULTprofile. The goal of using composite limits is to give users some leeway in the types of queries or DML they run. On some days, they may run a lot of queries that perform numerous calculations but don’t access a lot of table rows; on other days, they may do a lot of full table scans but don’t stay connected very long. In these situations, you don’t want to limit a user by a single parameter, but instead by total resource usage weighted by the availability of each resource on the server.

Please check back next week for the next part of this series.