SET VERIFY OFF LINES 270 FEED OFF
COL HASH_VALUE FORMAT 999999999999
COL EXECUTIONS FORMAT 99G999G999 HEAD 'Execucoes'
COL BUFFER_GETS FORMAT 9G999G999G999 HEAD 'Leituras Logicas'
COL GETS_BY_EXEC FORMAT 999G999G999 HEAD 'Leit.Logicas|Por Execucao'
COL LINHAS FORMAT 999G999G999G999 HEAD 'Linhas|Processadas'
COL SQL_TEXT FORMAT A80 HEAD 'Inicio do Texto do SQL' TRUNC

set head off
COL HH FORMAT A50
SELECT 'Hora Atual: ' || TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS' ) HH
FROM DUAL
/
set head on


WITH /* GetCursor */ V AS 
(
  SELECT 
     c.sql_id
    ,c.inst_id
    ,c.hash_value
    ,c.user_name
    ,c.address
  FROM GV$OPEN_CURSOR C
  WHERE C.SID = &1. AND C.INST_ID = &2.
), CURSORES AS
(
SELECT /*+ ALL_ROWS NO_MERGE(V) */ 
   v.inst_id
  ,V.SQL_ID
  ,v.user_name
  ,S.ROWS_PROCESSED LINHAS, S.EXECUTIONS, S.BUFFER_GETS
  ,TRUNC(S.BUFFER_GETS/DECODE(S.EXECUTIONS,NULL,1,0,1,S.EXECUTIONS)) GETS_BY_EXEC
  ,S.SQL_TEXT 
  --,S.SQL_FULLTEXT
FROM V
LEFT JOIN GV$SQLAREA S ON ( V.sql_id = s.sql_id AND V.inst_id = s.inst_id )
)
SELECT * FROM CURSORES C
WHERE ( C.BUFFER_GETS >= &L_BUF_GET. OR C.GETS_BY_EXEC >= &L_BUF_GET_BY_EXEC. )
ORDER BY C.GETS_BY_EXEC DESC
/

PROMPT ----------------------------------------------------------------------------
PROMPT Resumo de cursores abertos
PROMPT ----------------------------------------------------------------------------

SELECT O.INST_ID, O.SID, O.SQL_ID, SUM(S.OPEN_VERSIONS) open, COUNT(*) qtde
FROM GV$OPEN_CURSOR O
JOIN GV$SQLAREA S ON (O.SQL_ID = S.SQL_ID AND O.inst_id = S.inst_id)
WHERE O.SID=&1.
AND O.INST_ID = &2.
GROUP BY O.INST_ID, O.SID, O.SQL_ID
.

PROMPT

COL HASH_VALUE CLEAR
COL GETS_BY_EXEC CLEAR
COL LINHAS CLEAR
COL SQL_TEXT CLEAR
COL BUFFER_GETS CLEAR
COL EXECUTIONS CLEAR
SET VERIFY ON FEED 6