Re: "Dan's Oracle7 Guide" - README.profile

dbikle_at_alumni.cco.caltech.edu (Daniel B. Bikle) writes:  

>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 Tue Sep 21 1993 - 05:47:23 CEST