DBA Scripts - Very Useful - 14. Cursor/SQL Processing

Here are some scripts related to Cursor/SQL Processing .

Disk Intensive SQL

SQL WITH MOST DISK READ NOTES:

  Username - Name of the user

  Disk Reads - Total number of disk reads for this statement

  Executions - Total number of times this statement has been executed

  Reads/Execs - Number of reads per execution

  SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code

select       a.USERNAME,

               DISK_READS,

               EXECUTIONS,

               round(DISK_READS / decode(EXECUTIONS, 0, 1, EXECUTIONS)) "Reads/Execs",

               SQL_TEXT

from        dba_users a, v$session, v$sqlarea

where       PARSING_USER_ID=USER_ID 

and          ADDRESS=SQL_ADDRESS(+) 

and          DISK_READS > 10000

order        by DISK_READS desc, EXECUTIONS desc

Buffer Intensive SQL

SQL WITH MOST BUFFER SCAN NOTES:

  Username - Name of the user

  Buffer Gets - Total number of buffer gets for this statement

  Executions - Total number of times this statment has been executed

  Gets/Execs - Number of buffer gets per execution

  SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code

select       EXECUTIONS,

               BUFFER_GETS,

               round(DISK_READS / decode(EXECUTIONS, 0, 1, EXECUTIONS) / 400,2) "Gets/Execs",

               SQL_TEXT

from        v$sqlarea

where       BUFFER_GETS / decode(EXECUTIONS,0,1, EXECUTIONS) / 400 > 10 

order        by EXECUTIONS desc

Buffer SQL w/ Most Loads

SQL WITH MOST LOAD NOTES:

  Loads - Number of times the cursor has been loaded after the body of the cursor has been aged out of the cache while the text of the SQL statement remained in it, or after the cursor is invalidated

  First Load Time - Time at which the cursor was first loaded into the SGA

  Sorts - Number of sorts performed by the SQL statement

  SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code

select       LOADS,

               FIRST_LOAD_TIME,

               SORTS,

               SQL_TEXT

from        v$sqlarea

where       LOADS > 50 

order        by EXECUTIONS desc

Open Cursors By User

OPEN CURSORS BY USER NOTES:

  Username - Name of user

  SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code

select       nvl(USERNAME,'ORACLE PROC')||'('||s.SID||')' username,

               SQL_TEXT

from        v$open_cursor oc, 

               v$session s

where       s.SADDR = oc.SADDR

order        by 1

Running Cursors By User

RUNNING CURSORS BY USER NOTES:

  Username - Name of user

  SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code

select       nvl(USERNAME,'ORACLE PROC')||'('||s.SID||')' username,

               SQL_TEXT

from        v$open_cursor oc, v$session s

where       s.SQL_ADDRESS = oc.ADDRESS

and          s.SQL_HASH_VALUE = oc.HASH_VALUE

order        by 1

Running Cursors By User

OPEN CURSORS WITH LOW HIT RATIO NOTES:

  Username - Name of user

  SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code

select       nvl(se0.USERNAME,'ORACLE PROC')||'('||se0.SID||')' username,

               SQL_TEXT

from        v$open_cursor oc0, v$session se0

where       se0.SADDR = oc0.SADDR   

and          se0.USERNAME != 'SYS'

and          60 < (

                               select       "Hit Ratio" 

                               from        (

select nvl(se.USERNAME,'ORACLE PROC')||'('|| se.SID||')' "User Session",

               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", 

               (

               (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)

                "Hit Ratio" 

from        v$sesstat ss, v$statname sn, v$session se

where       ss.SID = se.SID

and          sn.STATISTIC# = ss.STATISTIC#

and          VALUE != 0

and          sn.NAME in ('db block gets', 'consistent gets', 'physical reads') 

group       by se.USERNAME, se.SID

) XX

                               where       nvl(se0.USERNAME,'ORACLE PROC')||'('||se0.SID||')' = "User Session")

order        by nvl(se0.USERNAME,'ORACLE'), se0.SID

LR Running Cursors

RUNNING CURSORS WITH LOW HIT RATIO NOTES:

  Username - Name of user

  SQL Text - Text of the SQL statement requiring the cursor, or the PL/SQL anonymous code

select       nvl(se0.USERNAME,'ORACLE PROC')||'('|| se0.SID||'),

               SQL_TEXT

from        v$open_cursor oc0, v$session se0

where       se0.SQL_ADDRESS = oc0.ADDRESS 

and          se0.SQL_HASH_VALUE = oc0.HASH_VALUE 

and          se0.username != 'SYS'

and          60 > (

               select       "Hit Ratio" 

               from (

select nvl(se.USERNAME,'ORACLE PROC')||'('|| se.SID||')' "User Session",

               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", 

                               (

                               (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) "Hit Ratio" 

from        v$sesstat ss, v$statname sn, v$session se

where       ss.SID = se.SID

and          sn.STATISTIC# = ss.STATISTIC#

and          VALUE != 0

and          sn.NAME in ('db block gets', 'consistent gets', 'physical reads') 

group       by se.USERNAME, se.SID

)

        where              nvl(se0.username,'ORACLE PROC')||'('||se0.sid||')' = "User Session")

order        by nvl(se0.username,'ORACLE'), se0.sid

LR Objects Access

OBJECTS BEING USED BY USERS WITH LOW HIT RATIO NOTES:

  Username - Name of the user

  Object Owner - Owner of the object

  Object - Name of the object

select       nvl(se0.USERNAME,'ORACLE PROC')||'('|| se0.SID||')' username,

               OWNER,

               OBJECT

from        v$access ac, v$session se0

where       ac.SID    = se0.SID

and          ac.TYPE   = 'TABLE'

and          60 < (

               select       "Hit Ratio" 

               from

(

select nvl(se.USERNAME,'ORACLE PROC')||'('|| se.SID||')' "User Session",

               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", 

               (

                               (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) "Hit Ratio" 

from        v$sesstat ss, 

               v$statname sn, 

               v$session se

where       ss.SID = se.SID

and          sn.STATISTIC# = ss.STATISTIC#

and          VALUE != 0

and          sn.NAME in ('db block gets', 'consistent gets', 'physical reads') 

group       by se.USERNAME, se.SID

)

               where       nvl(se0.USERNAME,'ORACLE PROC')||'('|| se0.SID||')' = "User Session")

order        by USERNAME,se0.SID,OWNER

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