jump to content

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

    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