Re: "Dan's Oracle7 Guide" - README.profile
Date: Tue, 21 Sep 1993 03:47:23 GMT
Message-ID: <s1344913.748583243_at_giaeb>
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?"
>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