SQL Script

Open Cursors

Updated : 17-January-1999
Version : 1.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

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 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
/

spool off