QUERY – SQLs pesadas desde la v$sql

Extraemos las queries mas pesadas de la v$sql

Set verify off

SET LINES 200 
COL SQL_ID HEADING 'sql_id'
COL child_cursor FOR 999999 HEADING 'Child'
COL FIRST_LOAD_TIME HEADING 'Hour Loaded'
COL last_execution FOR A30 HEADING 'Last Execution'
COL executions HEADING 'Executions'
COL pio_per_exec HEADING 'Physical Reads|Per Exec'      
COL lio_per_exec HEADING 'Logical Reads|Per Exec'
COL msec_exec HEADING 'Time (misec)|Per Exec'  
column sample_end format a21 
SELECT * 
FROM
(SELECT SQL_ID, 
CHILD_NUMBER as child_cursor, 
FIRST_LOAD_TIME, 
TO_CHAR(LAST_ACTIVE_TIME,'YYYY/MM/DD HH24:MI:SS') as last_execution ,EXECUTIONS, 
ROUND(DISK_READS/DECODE(EXECUTIONS,0,1,EXECUTIONS)) as pio_per_exec, 
ROUND(BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS)) as lio_per_exec, 
ROUND(ELAPSED_TIME/DECODE(EXECUTIONS,0,1,EXECUTIONS)/1000) as msec_exec 
FROM
 V$SQL 
WHERE   (ELAPSED_TIME >= (SELECT MEDIAN(ELAPSED_TIME) FROM V$SQL) OR    BUFFER_GETS >= (SELECT MEDIAN(BUFFER_GETS) FROM V$SQL) )   
and LAST_ACTIVE_TIME >= sysdate-NVL(&mins_back,1)/1440 
ORDER BY lio_per_exec DESC) 
WHERE ROWNUM<=20;



luego buscamos la mas grosa en la V$SQL.

select SQL_TEXT from v$SQL where SQL_ID ='62hjck53xcubz';