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