SQL ScriptOpen Cursors |
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.
None.
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 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 / spool off
Return to Index of SQL Scripts
Home
|
Company Profile
|
Services
|
Contact Us
|
SQL scripts and tips
|
Quiz
|