SQL Script Open Cursors

Updated: 22-May-2003
Version: 2.0

Description

This script reports current open cursors ordered by the the average cost of the cursor. The "Avg Cost" row is basically the No. of Buffer Gets per Rows processed. 

Where no rows are processed, all Buffer Gets are reported for the statement. The statements with the greatest cost are at the top of the report.

Parameters

None

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

spool opencur.lst
set pagesize 66 linesize 132
set echo on

column executions      heading "Execs"      format 99999999
column rows_processed  heading "Rows Procd" format a20
column loads           heading "Loads"      format 999999.99
column buffer_gets     heading "Buffer Gets"
column sql_text        heading "SQL Text"   format a60 wrap
column avg_cost        heading "Avg Cost"   format 99999999
break on report

compute sum      of rows_processed     on report
compute sum      of executions         on report
compute sum  avg of loads              on report
compute avg      of avg_cost           on report

select
    ss.username,se.sid, sum(decode(name,'recursive calls',value)) "Recursive Calls",
    sum(decode(name,'opened cursors cumulative',value)) "Opened Cursors",
    sum(decode(name,'opened cursors current',value)) "Current Cursors"
 from v$session ss, v$sesstat se, v$statname sn
 where  se.statistic# = sn.statistic#
                and (     name  like '%opened cursors current%'
                                OR name  like '%recursive calls%'
                                OR name  like '%opened cursors cumulative%')
                and  se.sid = ss.sid
                and     ss.username is not null
group by ss.username,se.sid
/

select rownum as rank, a.*
from (
   select buffer_gets, lpad(rows_processed ||
       decode(users_opening + users_executing, 0, ' ','*'),20) "rows_processed",
       executions, loads,
       (decode(rows_processed,0,1,1)) *
            buffer_gets/ decode(rows_processed,0,1,
                                  rows_processed) avg_cost,
      sqla.sql_text
from  v$sqlarea sqla, v$open_cursor oc
where sqla.hash_value = sqla.hash_value
and oc.address = sqla.address
and oc.hash_value = sqla.hash_value
order by 5 desc
) a
where rownum < 11
/                           

select
    ss.username,se.sid, sum(decode(name,'recursive calls',value)) "Recursive Calls",
    sum(decode(name,'opened cursors cumulative',value)) "Opened Cursors",
    sum(decode(name,'opened cursors current',value)) "Current Cursors"
 from v$session ss, v$sesstat se, v$statname sn
 where  se.statistic# = sn.statistic#
                and (     name  like '%opened cursors current%'
                                OR name  like '%recursive calls%'
                                OR name  like '%opened cursors cumulative%')
                and  se.sid = ss.sid
                and     ss.username is not null
group by ss.username,se.sid
/
                                 

spool off

Related Links

Oracle 8: (SQL Script) Open Cursors

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links