DBA Scripts - Very Useful - 5,6. Hit/Miss Ratios, User Information

5. Hit/Miss Ratios

Here are some scripts related to Hit/Miss Ratios .

Buffer Hit Ratio

BUFFER HIT RATIO NOTES:

  Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.

  DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).

  Physical Reads - The cumulative number of blocks read from disk.

  Logical reads are the sum of consistent gets and db block gets.

  The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.

  Hit Ratio should be > 80%, else increase DB_BLOCK_BUFFERS in init.ora

select       sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",

               sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",

               sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",

               round((sum(decode(name, 'consistent gets',value, 0)) + 

                      sum(decode(name, 'db block gets',value, 0)) - 

                      sum(decode(name, 'physical reads',value, 0))) / 

                     (sum(decode(name, 'consistent gets',value, 0)) + 

                      sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"

from   v$sysstat

Data Dict Hit Ratio

DATA DICTIONARY HIT RATIO NOTES:

  Gets - Total number of requests for information on the data object.

  Cache Misses - Number of data requests resulting in cache misses

  Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora

select       sum(GETS),

               sum(GETMISSES),

               round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)

from        v$rowcache

SQL Cache Hit Ratio

SQL CACHE HIT RATIO NOTES:

  Pins - The number of times a pin was requested for objects of this namespace.

  Reloads - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

  Hit Ratio should be > 85%

select       sum(PINS) Pins,

               sum(RELOADS) Reloads,

               round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio

from        v$librarycache

Library Cache Miss Ratio

LIBRARY CACHE MISS RATIO NOTES:

  Executions - The number of times a pin was requested for objects of this namespace.

  Cache Misses - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

  Hit Ratio should be < 1%, else increase SHARED_POOL_SIZE in init.ora

select       sum(PINS) Executions,

               sum(RELOADS) cache_misses,

               sum(RELOADS) / sum(PINS) miss_ratio

from        v$librarycache

6. User Information

Here are some scripts related to User Information .

User Objects

USER OBJECT NOTES:

  Username - Owner of the object(s)

  Tabs - Table(s)

  Inds - Index(es)

  Syns - Synonym(s)

  Views - Views(s)

  Seqs - Sequence(s)

  Procs - Procedure(s)

  Funcs - Function(s)

  Pkgs - Packages(s)

  Trigs - Trigger(s)

  Deps - Dependencies

select       USERNAME,

               count(decode(o.TYPE#, 2,o.OBJ#,'')) Tabs,

               count(decode(o.TYPE#, 1,o.OBJ#,'')) Inds,

               count(decode(o.TYPE#, 5,o.OBJ#,'')) Syns,

               count(decode(o.TYPE#, 4,o.OBJ#,'')) Views,

               count(decode(o.TYPE#, 6,o.OBJ#,'')) Seqs,

               count(decode(o.TYPE#, 7,o.OBJ#,'')) Procs,

               count(decode(o.TYPE#, 8,o.OBJ#,'')) Funcs,

               count(decode(o.TYPE#, 9,o.OBJ#,'')) Pkgs,

               count(decode(o.TYPE#,12,o.OBJ#,'')) Trigs,

               count(decode(o.TYPE#,10,o.OBJ#,'')) Deps

from        obj$ o,

               dba_users u

where       u.USER_ID = o.OWNER# (+)

group       by USERNAME

order        by USERNAME

Invalid Objects

INVALID OBJECT NOTES:

  Owner - Owner of the object

  Object Type - Type of object

  Object Name - Name of the object

  Status - Status of the object

select       OWNER,

               OBJECT_TYPE,

               OBJECT_NAME,

               STATUS

from        dba_objects

where       STATUS = 'INVALID'

order        by OWNER, OBJECT_TYPE, OBJECT_NAME

Object Modification

OBJECT MODIFICATION NOTES: (Modified in last 7 days)

  Owner - Owner of the object

  Object Name - Name of the object

  Object Type - Type of the object

  Last Modified - Last modification date/time

  Created - Object creation date/time

  Status - Status of the object

select       OWNER,

               OBJECT_NAME,

               OBJECT_TYPE,

               to_char(LAST_DDL_TIME,'MM/DD/YYYY HH24:MI:SS') last_modified,

               to_char(CREATED,'MM/DD/YYYY HH24:MI:SS') created,

               STATUS

from        dba_objects

where       (SYSDATE - LAST_DDL_TIME) < 7

order        by LAST_DDL_TIME DESC

User Privileges

USER PRIVILEGES NOTES:

  Grantee - Grantee name, user or role receiving the grant

  Granted Role - Granted role name

  Admin - Grant was with the ADMIN option

  Default - Role is designated as a DEFAULT ROLE for the user

  Privilege - System privilege

select       rp.GRANTEE,

               GRANTED_ROLE,

               rp.ADMIN_OPTION,

               DEFAULT_ROLE,

               PRIVILEGE

from        dba_role_privs rp, dba_sys_privs sp

where       rp.GRANTEE = sp.GRANTEE

and          rp.GRANTEE not in ('SYS','SYSTEM','DBA')

order        by  rp.GRANTEE, GRANTED_ROLE, PRIVILEGE