SET TERMOUT ON FEEDBACK OFF VERIFY OFF NUMWIDTH 20 PAGES 500 LINES 200

VAR SQLTX VARCHAR2(4000)

DEFINE P_HASH=&1.
DEFINE P_SQL_ID=&1.

COL PARSING_SCHEMA_NAME FORMAT A20            HEAD "Usuário"
COL VERSION_COUNT       FORMAT 999G999        HEAD "Versões"
COL EXECUTIONS          FORMAT 99G999G999     HEAD "Execuções"
COL ROWS_PROCESSED      FORMAT 999G999G999    HEAD "Linhas|Processadas"
COL DISK_READS          FORMAT 999G999G999    HEAD "Leituras|Físicas"
COL BUFFER_GETS         FORMAT 99G999G999G999 HEAD "Leituras|Lógicas"
COL DR_EXEC             FORMAT 99G999G999     HEAD "Leit. Fís.|/Execuções"
COL BG_EXEC             FORMAT 99G999G999     HEAD "Leit. Lógica|/Execuções"
COL SHARABLE_MEM        FORMAT 99G999G999     HEAD "Memória|Compartilhada"
COL SQLX                FORMAT A121           HEAD "Comando de SQL"  WORD_WRAP NEW_VALUE P_SQL
COL PLAN_HASH_VALUE     FORMAT 99999999999999
COL dw_exec             FORMAT 99G999G999     HEAD "Direct|Writes/Exec"


COL ADDRESS NEW_VALUE P_ADDR NOPRINT
COL HASH_VALUE NEW_VALUE P_HASH2 NOPRINT FORMAT 99999999999999

SET HEAD OFF
SELECT LPAD( '~', 121, '~' ) FROM DUAL;
SET HEAD ON

SELECT
  ADDRESS, HASH_VALUE/*, PLAN_HASH_VALUE*/, VERSION_COUNT, EXECUTIONS, ROWS_PROCESSED,
  DISK_READS, ROUND(DISK_READS/DECODE(EXECUTIONS,0,1,EXECUTIONS),2) DR_EXEC,
  BUFFER_GETS, ROUND(BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS),2) BG_EXEC,
  --ROUND( SHARABLE_MEM/1048576 ) SHARABLE_MEM,
  ROUND(DIRECT_WRITES/DECODE(EXECUTIONS,0,1,EXECUTIONS),2) DW_exec , SQL_ID
FROM V$SQLAREA
WHERE ( SQL_ID= '&P_SQL_ID.' )
ORDER BY ADDRESS
/

DECLARE
  V_AUX VARCHAR2(100);
BEGIN
  :SQLTX := ''  ;
  FOR C IN ( SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES
             WHERE HASH_VALUE = &P_HASH2. AND   ADDRESS = '&P_ADDR'
             ORDER BY PIECE ) LOOP
    V_AUX := C.SQL_TEXT;
    EXIT WHEN ( LENGTH( V_AUX ) + LENGTH( :SQLTX ) )  > 3000;
    :SQLTX := :SQLTX || V_AUX;
  END LOOP;

  IF :SQLTX IS NULL THEN
    :SQLTX := 'Comando Não Encontrado';
  END IF;

END;
/

SELECT TRIM(:SQLTX) || ';' SQLX FROM DUAL
/

SET HEAD OFF
SELECT LPAD( '~', 121, '~' ) FROM DUAL;
SET HEAD ON

PROMPT

SET PAGES 100 FEEDBACK 6 VERIFY ON

UNDEFINE P_SQL
UNDEFINE P_SQL_ID
UNDEFINE P_HASH
UNDEFINE P_HASH2
UNDEFINE P_ADDR

hostgator