Here are some scripts related to Cursor/SQL Processing .
SQL WITH MOST DISK READ NOTES:
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
SQL WITH MOST BUFFER SCAN NOTES:
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
SQL WITH MOST LOAD NOTES:
select LOADS,
FIRST_LOAD_TIME,
SORTS,
SQL_TEXT
from v$sqlarea
where LOADS > 50
order by EXECUTIONS desc
OPEN CURSORS BY USER NOTES:
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 NOTES:
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
OPEN CURSORS WITH LOW HIT RATIO NOTES:
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
RUNNING CURSORS WITH LOW HIT RATIO NOTES:
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
OBJECTS BEING USED BY USERS WITH LOW HIT RATIO NOTES:
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
Since you are seeing this, it means that your browser does not support cascading style sheets. Please download and use one of the many browsers that support web standards.