jump to content

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
    
    

    LR Open Cursors

    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