Oracle Cursor / Sql Processing

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

Advertisement