76 - [Oracle 19C] Resource Parameters

 

 

• Resource Parameters

Resource Parameters
• In a profile, you can control:
  ° CPU resources - may be limited on a per-session or per-call basis
  ° Network and memory resources - you can specify the following:
    ° Connect time
    ° Idle time
    ° Concurrent sessions
    ° Private SGA
  ° Disk I/O resources - limit the amount of data a user can read at the per-session level or per-call level
• Profiles cannot impose resource limitations on users unless the RESOURCE_LIMIT initialization parameter is set to TRUE
  ° With RESOURCE_LIMIT at its default value of FALSE, profile resource limitations are ignored.
• Profiles also allow composite limits, which are based on weighted combinations of CPU/session, reads/session, connect time. and private SGA.


CPU_PER_SESSION/ CPU_PER_CALL

Example:
CPU_PER_CALL 3000
A single call made by the user cannot consume more than 30 seconds of CPU time.

 

 

 

CREATE PROFILE app_user LIMIT
SESSIONS_PER_USER	UNLIMITED
CPU_PER_SESSION	UNLIMITED
CPU_PER_CALL	3000
CONNECT_TIME	45
LOGICAL _READS_PER_SESSION DEFAULI
LOGICAL READS_PER_CALL	1000
PRIVATE_SGA	15K
COMPOSITE_LIMIT	5000000;

 

 

If you assign the app_user profile to a user, then the user is subject to the following limits in subsequent sessions:

•   The user can have any number of concurrent sessions.
•   In a single session, the user can consume an unlimited amount of CPU time.
•   A single call made by the user cannot consume more than 30 seconds of CPU time.
•   A single session cannot last for more than 45 minutes.
•   In a single session, the number of data blocks read from memory and disk is subject to the limit specified in the DEFAULT profile.
•   A single call made by the user cannot read more than 1000 data blocks from memory and disk.
•   A single session cannot allocate more than 15 kilobytes of memory in the SGA.
•   In a single session, the total resource cost cannot exceed 5 million service units. The formula for calculating the total resource cost is specified by the ALTER RESOURCE COsT statement.
•   Since the app_user profile omits a limit for IDLE _TIME and for password limits, the user is subject to the limits on these resources specified in the DEFAULT profile.