Oracle Live

11/04/2016

QUERY – SQLs pesadas desde la v$sql

Filed under: Queries — mogukiller @ 11:51 pm
Tags:

Extraemos las queries mas pesadas de la v$sql

/*
                                                                                    Physical Reads Logical Reads Time (misec)
sql_id          Child Hour Loaded         Last Execution                 Executions       Per Exec      Per Exec     Per Exec
------------- ------- ------------------- ------------------------------ ---------- -------------- ------------- ------------
6kha9sfy34bnd       0 2016-03-24/18:34:39 2016/03/28 18:31:36                     5           6842        191476         5449
bvnbuztjktym8       1 2016-03-28/13:03:46 2016/03/28 18:28:24                    13              0        143779          261
c05zuaws7kyag       1 2016-03-28/13:58:12 2016/03/28 18:27:54                    17              0        124875          218
gnr96c3jc9cdh       0 2016-03-24/18:34:40 2016/03/28 18:31:36                     1           6748          8117          691
*/

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; 

Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Blog de WordPress.com.

A %d blogueros les gusta esto: