Home arrow Oracle arrow Page 2 - Profiles, Password and Resource Control for Oracle Database XE

Profiles and Resource Control - Oracle

In this fifth part of a ten-part series on securing Oracle Database XE, you'll learn about profiles as they pertain to password and resource control. This article is excerpted from chapter 31 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

TABLE OF CONTENTS:
  1. Profiles, Password and Resource Control for Oracle Database XE
  2. Profiles and Resource Control
By: Apress Publishing
Rating: starstarstarstarstar / 2
January 06, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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

 

Resource Parameter

Description

SESSIONS_PER_USER

Maximum number of sessions a user can simultaneously have

CPU_PER_SESSION

Maximum CPU time allowed per session, in hundredths of a second

CPU_PER_CALL

Maximum CPU time for a statement parse, execute, or fetch operation, in hundredths of a second

CONNECT_TIME

Maximum total elapsed time, in minutes

IDLE_TIME

Maximum continuous inactive time in a session, in minutes, while a query or other operation is not in progress

LOGICAL_READS_PER_SESSION

Total number of data blocks read per session, either from memory or disk

LOGICAL_READS_PER_CALL

Maximum number of data blocks read for a statement parse, execute, or fetch operation

COMPOSITE_LIMIT

Total resource cost, in service units, computed as a composite weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_ READS_PER_SESSION, and PRIVATE_SGA

PRIVATE_SGA

Maximum amount of memory a session can allocate in the shared pool, in bytes, kilobytes, or megabytes

 

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  

 

CPU (Seconds)

Connect (Seconds)

Composite Cost

Exceeded?

0.05

100

(50 * 5) + (2 * 100) = 450

Yes

0.02

30

(50 * 2) + (2 * 30) = 160

No

0.01

150

(50 * 1) + (2 * 150) = 350

Yes

0.02

5

(50 * 2) + (2 * 5) = 110

No

 

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.



 
 
>>> More Oracle Articles          >>> More By Apress Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: