"Dan's Oracle7 Guide" - README.profile

From: Daniel B. Bikle <dbikle_at_alumni.cco.caltech.edu>
Date: 20 Sep 1993 06:10:44 GMT
Message-ID: <DBIKLE.93Sep19231044_at_alumni.cco.caltech.edu>

Keywords: Oracle7

README.profile

A profile is a database object used to control the amount of resources used by processes connected to the database. Profiles control the resources listed below:

	CPU time
	Idle time
	Connect time
	I/O usage (Logical Reads)
	Memory usage (in each process's PGA)
	Number of sessions by each user

Example syntax for creating a profile is displayed below:

DROP PROFILE PR1
/

CREATE PROFILE PR1 LIMIT

CONNECT_TIME	12345		/* in minutes */
LOGICAL_READS_PER_CALL	12345	/* in data blocks */
LOGICAL_READS_PER_SESSION 123450/* in data blocks */
CPU_PER_CALL	12345600	/* in 1/100 sec */
CPU_PER_SESSION 12345600	/* in 1/100 sec */
IDLE_TIME	12345		/* in minutes */
PRIVATE_SGA	10M		/* in bytes, kbytes, or mbytes */
SESSIONS_PER_USER	123	/* # sessions a user can run */
COMPOSITE_LIMIT	1234567000	/* expressed in "service units" */

The act of creating a profile does not limit usage of database resources; the profile must be assigned to a user before any resource limitation occurs and the init param RESOURCE_LIMIT must be set to TRUE. Example syntax for assigning the profile pr1 to BUFORD is displayed below:

CREATE USER BUFORD IDENTIFIED BY BUBBA
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE PR1
/

Or if BUFORD already exists, use this syntax:

ALTER USER BUFORD
PROFILE PR1
/

Altering a Profile

Changing a Resource Limit may be done with the ALTER PROFILE command. Example syntax for changing a Resource Limit is displayed below:

ALTER PROFILE PR1 LIMIT
CONNECT_TIME 123456
/

Unrestricting a Resource Limit

Changing a Resource Limit so that it is unlimited is accomplished via the syntax displayed below:

ALTER PROFILE PR1 LIMIT
CONNECT_TIME UNLIMITED
/

Querying the Audit Trail and V$ Tables for Resource Usage

Now that the syntax for creating and assigning profiles has been discussed we might be curious about what are appropriate values for the different resource limits. Some of the answers to this question may be obtained by auditing the database for a period of time to gain a small insight into resource usage (one quick way to enable auditing is a three step process: set the Init Param "AUDIT_TRAIL = TRUE", bounce the dbs, and then enter the AUDIT ALL sql command from a DBA account; also don't forget to purge the table SYS.AUD$ periodically to prevent it from gobbling disk space).

CONNECT_TIME

The queries displayed below may be used to gather information about the CONNECT_TIME resource after auditing has been enabled:

SELECT
USERNAME
,NVL (

       (LOGOFF_TIME-TIMESTAMP)*24, 
       0
     ) HOURS_CONNECT_TIME

FROM DBA_AUDIT_SESSION
ORDER BY USERNAME , NVL(LOGOFF_TIME-TIMESTAMP, 0) DESC
/

Example Results:

USERNAME HOURS_CONNECT_TIME

------------------------------ ------------------
OPS$DAN 			       7.09444444
OPS$DAN 			       5.70027778
OPS$DAN 			       3.68166667
OPS$DAN 				       .5
OPS$DAN 			       .388055556
OPS$DAN 			       .211666667
OPS$DAN 			       .162222222
OPS$DAN 			       .099444444
OPS$DAN 			       .078055556
OPS$DAN 			       .077777778
OPS$DAN 			       .043888889
OPS$DAN 			       .001388889
OPS$DAN 					0
OPS$DAN 					0
SCOTT				       .008055556
SCOTT				       .002777778
SCOTT				       .002777778
SCOTT				       .001666667

SELECT
SUBSTR(USERNAME,1,11) USERNAME
,AVG(

      NVL (
           (LOGOFF_TIME-TIMESTAMP)*24,
           0
          )

) AVG_HOURS_CONNECT_TIME
,MAX(

      NVL (
           (LOGOFF_TIME-TIMESTAMP)*24,
           0
          )

) MAX_HOURS_CONNECT_TIME
FROM DBA_AUDIT_SESSION
GROUP BY USERNAME
/

Example Results:

USERNAME AVG_HOURS_CONNECT_TIME MAX_HOURS_CONNECT_TIME

----------- ---------------------- ----------------------
OPS$DAN 		.649424603	       7.09444444
SCOTT			.003819444	       .008055556

LOGICAL_READS_PER_CALL

Gathering statistics on the LOGICAL_READS_PER_CALL resource is not apparent to the author. Statistics on the LOGICAL_READS_PER_SESSION resource, however, are easily available from the Audit Trail. Once the LOGICAL_READS_PER_SESSION resource limit has been determined by the DBA, he/she could decide that the LOGICAL_READS_PER_CALL resource should be 1/10 the value set for the LOGICAL_READS_PER_SESSION resource limit.

LOGICAL_READS_PER_SESSION

The query displayed below may be used to gather information about the LOGICAL_READS_PER_SESSION resource:

SELECT
SUBSTR(USERNAME,1,11) USERNAME

,AVG(LOGOFF_LREAD) AVG_LOGICAL_READS
,MAX(LOGOFF_LREAD) MAX_LOGICAL_READS
,COUNT(LOGOFF_LREAD) NUMBER_OF_LOGINS

FROM DBA_AUDIT_SESSION
GROUP BY USERNAME
/

Example Results:

USERNAME AVG_LOGICAL_READS MAX_LOGICAL_READS NUMBER_OF_LOGINS

----------- ----------------- ----------------- ----------------
OPS$DAN            1493.30435              9255               23
SCOTT                    73.5                97                4

CPU_PER_CALL

Aside from querying the Audit Trail, we may gather resource usage information from the V$ tables. The query displayed below may be used to gather information about the CPU_PER_CALL resource:

SELECT

SUBSTR(S.USERNAME,1,9) 	USERNAME
,SUBSTR(P.PROGRAM,1,18)	PROGRAM
,SUBSTR(N.NAME,1,22) 	STATNAME
,ST.VALUE 		CALL_CPU

FROM V$SESSTAT ST,V$STATNAME N, V$SESSION S, V$PROCESS P

WHERE ST.STATISTIC# = N.STATISTIC#
AND    S.SID        = ST.SID
AND    S.PADDR      = P.ADDR

AND UPPER(N.NAME) LIKE '%CPU%CALL%'
ORDER BY S.USERNAME, N.NAME
/

Example Results:

USERNAME PROGRAM STATNAME CALL_CPU

--------- ------------------ ---------------------- ----------
OPS$DAN   oracle_at_ava2 (Pipe  CPU used when call sta	     0
SCOTT	  oracle_at_ava2 (Pipe  CPU used when call sta	     0
OPS$O7	  oracle_at_ava2 (TNS i CPU used when call sta	     0
	  oracle_at_ava2 (PMON) CPU used when call sta	     0
	  oracle_at_ava2 (DBWR) CPU used when call sta	     0
	  oracle_at_ava2 (LGWR) CPU used when call sta	     0
	  oracle_at_ava2 (SMON) CPU used when call sta	     0
	  oracle_at_ava2 (RECO) CPU used when call sta	     0

CPU_PER_SESSION

The query displayed below may be used to gather information about the CPU_PER_SESSION resource:

SELECT
SUBSTR(S.USERNAME,1,9) USERNAME

,SUBSTR(P.PROGRAM,1,18)	PROGRAM
,SUBSTR(N.NAME,1,22) 	STATNAME
,ST.VALUE SESSION_CPU

FROM V$SESSTAT ST,V$STATNAME N, V$SESSION S, V$PROCESS P

WHERE ST.STATISTIC# = N.STATISTIC#
AND    S.SID        = ST.SID
AND    S.PADDR      = P.ADDR

AND UPPER(N.NAME) LIKE '%CPU%SESSION%'
ORDER BY S.USERNAME, N.NAME
/

Example Results:

USERNAME PROGRAM STATNAME SESSION_CPU

--------- ------------------ ---------------------- -----------
OPS$DAN   oracle_at_ava2 (Pipe  CPU used by this sessi           0
SCOTT     oracle_at_ava2 (Pipe  CPU used by this sessi           0
OPS$O7    oracle_at_ava2 (TNS i CPU used by this sessi           0
          oracle_at_ava2 (PMON) CPU used by this sessi           0
          oracle_at_ava2 (DBWR) CPU used by this sessi           0
          oracle_at_ava2 (LGWR) CPU used by this sessi           0
          oracle_at_ava2 (SMON) CPU used by this sessi           0
          oracle_at_ava2 (RECO) CPU used by this sessi           0

IDLE_TIME

The query displayed below may be used to gather information about the IDLE_TIME resource:

SELECT
SUBSTR(S.USERNAME,1,9) USERNAME

,SUBSTR(P.PROGRAM,1,18)	PROGRAM
,SUBSTR(N.NAME,1,22) 	STATNAME
,ST.VALUE IDLE_INFO

FROM V$SESSTAT ST,V$STATNAME N, V$SESSION S, V$PROCESS P

WHERE ST.STATISTIC# = N.STATISTIC#
AND    S.SID        = ST.SID
AND    S.PADDR      = P.ADDR

AND UPPER(N.NAME) LIKE '%IDLE%'
ORDER BY S.USERNAME, N.NAME
/

Example Results:

USERNAME PROGRAM STATNAME IDLE_INFO

--------- ------------------ ---------------------- ----------
OPS$DAN   oracle_at_ava2 (TNS i process last non-idle	     0
SCOTT     oracle_at_ava2 (TNS i process last non-idle	     0
OPS$O7    oracle_at_ava2 (TNS i process last non-idle	     0
	  oracle_at_ava2 (PMON) process last non-idle	     0
	  oracle_at_ava2 (DBWR) process last non-idle	     0
	  oracle_at_ava2 (LGWR) process last non-idle	     0
	  oracle_at_ava2 (RECO) process last non-idle	     0
	  oracle_at_ava2 (SMON) process last non-idle	     0

PRIVATE_SGA

Gathering statistics on the PRIVATE_SGA resource is not apparent to the author. Gathering statistics on the PGA, however, is easy. With knowledge about each user's PGA statistics, the DBA might make a reasonable guess about what are good values for the PRIVATE_SGA resource.

The query displayed below may be used to gather information about the PGA:

SELECT
SUBSTR(S.USERNAME,1,9) USERNAME

,SUBSTR(P.PROGRAM,1,18)	PROGRAM
,SUBSTR(N.NAME,1,22) 	STATNAME
,ST.VALUE MEM_INFO

FROM V$SESSTAT ST,V$STATNAME N, V$SESSION S, V$PROCESS P

WHERE ST.STATISTIC# = N.STATISTIC#
AND    S.SID        = ST.SID
AND    S.PADDR      = P.ADDR

AND UPPER(N.NAME) LIKE '%MEM%'
AND S.USERNAME IS NOT NULL
ORDER BY S.USERNAME, N.NAME
/

Example Results:

USERNAME PROGRAM STATNAME MEM_INFO

--------- ------------------ ---------------------- ----------
OPS$DAN   oracle_at_ava2 (TNS i max session memory 	202620
OPS$DAN   oracle_at_ava2 (TNS i session max pga memory	446500
OPS$DAN   oracle_at_ava2 (TNS i session memory		164460
OPS$DAN   oracle_at_ava2 (TNS i session pga memory 	435996
OPS$DAN   oracle_at_ava2 (TNS i sorts (memory)		     0

Looking at the above data, the DBA might decide a good value for the PRIVATE_SGA limit would be about ten times the value for 'session max pga memory' which would be 5 Mbytes.

SESSIONS_PER_USER

The query displayed below may be used to gather information about the number of sessions each user is running at this instant:

SELECT USERNAME, COUNT(USERNAME)
FROM V$SESSION
WHERE USERNAME IS NOT NULL
GROUP BY USERNAME
/

Example Results:

USERNAME COUNT(USERNAME)

------------------------------ ---------------
OPS$DAN 				     2
OPS$O7					     3
OPS$ROOT				     1
SCOTT					     5

Creating a History of the V$ Tables

Unlike the Audit Trail, the V$ tables provide only instantaneous data; we need to sample them periodically if we are to gain an accurate picture of resource usage by the dbs users. Once many samples are analyzed, we may then guess at appropriate resource limits for the dbs users. The shell script named v_sampler.sh has been included with this document and is intended to be run from cron. Each time cron runs it, a sample of some V$ data is saved to a set of history tables.

With a discussion about individual Resource Limits behind us, it is now appropriate to illustrate some points about the COMPOSITE_LIMIT.

Managing the COMPOSITE_LIMIT Resource Limit

One of the Resource Limits is named the COMPOSITE_LIMIT. It limits users from using too much of a composite of Resource Limits. The COMPOSITE_LIMIT is made up of the following Resource Limits:

	LOGICAL_READS_PER_SESSION
	CPU_PER_SESSION
	PRIVATE_SGA
	CONNECT_TIME

Perhaps a good name for the above four Resource Limits would be the "Valuable Limits".

Each Valuable Limit is given a weighting; an example of assigning a weighting to the Valuable Limits is displayed in the syntax below:

ALTER RESOURCE COST

LOGICAL_READS_PER_SESSION	10
CPU_PER_SESSION			1
PRIVATE_SGA			5
CONNECT_TIME			1

One necessary feature of the COMPOSITE_LIMIT is that it has an artificial unit of measurement named "Service Units", since it is a composite of Resource Limits with different units of measure.

Deciding on the COMPOSITE_LIMIT Resource Limit

Deciding on a reasonable value for the COMPOSITE_LIMIT Resource Limit should be done after the DBA has picked values for the Valuable Limits which make up the COMPOSITE_LIMIT. The scenario discussed below might illustrate this point.

Bob Decides on His COMPOSITE_LIMIT

After querying the Audit Trail and V$ Tables for resource usage, Bob has picked values for the Resource Limits of a specific profile:

CREATE PROFILE PR_BOB LIMIT

CONNECT_TIME		UNLIMITED
LOGICAL_READS_PER_CALL	12345	
LOGICAL_READS_PER_SESSION 123450
CPU_PER_CALL		12345600	
CPU_PER_SESSION 	12345600	
IDLE_TIME		120
PRIVATE_SGA		20M		
SESSIONS_PER_USER	5

Next, Bob asks a question, "Do I want to prevent a user from launching a session such that all of his/her resource usages are simultaneously near, but still less than, their respective limits?"

Bob answers, "Yes."

If Bob had answered "No", his common sense would have told him that setting a value for the COMPOSITE_LIMIT would be unnecessary.

Then, Bob applies a little arithmetic to the values of the Resource Limits in the PR_BOB profile:

sum_r_l = (LOGICAL_READS_PER_SESSION + CPU_PER_SESSION

+ PRIVATE_SGA + CONNECT_TIME) sum_r_l = (123450 + 12345600 + 20 * 1024 * 1024 + 0)

sum_r_l = 33440570

Bob looks at this number and asks "Which part of the COMPOSITE_LIMIT limits the most valuable resource?"

He answers, "The most valuable resource of my system is I/O so the part I want to give extra weighting to is LOGICAL_READS_PER_SESSION."

As a final step in this scenario, Bob issues some sql statements to implement his wishes:

ALTER PROFILE PR_BOB LIMIT
COMPOSITE_LIMIT 33440570
/

ALTER RESOURCE COST

LOGICAL_READS_PER_SESSION       100
CPU_PER_SESSION                 1
PRIVATE_SGA                     1
CONNECT_TIME                    0

ALTER USER BUFORD
PROFILE PR_BOB
/

Although the management of the COMPOSITE_LIMIT is straightforward, the thinking process behind setting it to an appropriate value is a bit more subtle than determining the values for other Resource Limits.

Received on Mon Sep 20 1993 - 08:10:44 CEST

This message: [ Message body ]
Next message: Damian Hogan: "Re: "Dan's Oracle7 Guide" - README.profile"
Previous message: Daniel B. Bikle: ""Dan's Oracle7 Guide" - mts_setup (enhanced)"
Next in thread: Damian Hogan: "Re: "Dan's Oracle7 Guide" - README.profile"
Reply: Damian Hogan: "Re: "Dan's Oracle7 Guide" - README.profile"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message