Sesiones que utilizan planes de ejecución con FULL TABLE ACCESS | ExpoDBA

Sesiones que utilizan planes de ejecución con FULL TABLE ACCESS

Sesiones que utilizan planes de ejecución con FULL TABLE ACCESS

Hay veces en la que los usuarios se quejan de lentitud sobre aplicaciones que pegan sobre una tabla en particular.

 

Esta lentitud la podríamos ver del lado de la base de datos buscando por esperas del tipo db file scattered read.

Esta espera se da justamente cuando hay accesos Full a una tabla, y seguramente (a menos que la lógica de la aplicación lo requiera) no necesite hacer un full y podamos salvar la situación. (Si hace un select * from TABLA, no hay nada que investigar).

 

Para poder identificar a los mismos, podemos buscar en la tabla V$sql_plan que planes de ejecución están haciendo FULL SCAN sobre esta tabla.

 

select distinct s.HASH_VALUE, s.OBJECT_OWNER, s.object_name, executions,
round(sa.DISK_READS/sa.executions,2) PIO, round(sa.buffer_gets/sa.executions,2) LIO,
decode (
    round(sa.DISK_READS/sa.executions,2),0,-1,
        decode(
            round(sa.buffer_gets/sa.executions,2),0,-2,
            round(( sa.buffer_gets/sa.executions )/(sa.DISK_READS/sa.executions ),2)
            )
    ) "Cache_hit"
     -- -1: Leido todo de buffer
     -- -2: leyó todo de disco
     -- entre 0 y 1 leyó más disco que buffer
     -- >1 Leyó más buffer que disco
from v$sql_plan s,  v$sqlarea sa
where s.address=sa.address
and s.hash_value=s.hash_value
and sa.executions >0
and OPTIONS = 'FULL'
and object_owner <> 'SYS'
and object_owner <> 'SYSTEM'
and OPERATION = 'TABLE ACCESS'
and OBJECT_NAME like '%&TABLE_NAME'
order by 7 desc;

(http://www.expodba.com/script/performance/tablas-accedidas-por-full-0)

 

 

Este script nos va a pedir el nombre de la Tabla a buscar (Esto no es obligatorio y puede dejarse el lugar vacío).

 

El script mostrará todos los hash_value que tienen en alguna parte de su plan de ejecución un TABLE ACCESS FULL a la tabla que hayamos especificado (Si no especificamos, buscará en todas). Además mostrará por cada plan de ejecución cuantas veces fue ejecutado, cuantas lecturas hizo de memoria, cuantas de disco y el cache_hit que tuvo esa query, ordenado por cache_hit.

 

Seguramente de todos los accesos full que tengamos a la tabla podremos identificar cual es el que más nos afecta teniendo en cuenta el que tenga más bajo cache_hit, e investigar que pasa con esa consulta.

 

Para poder saber que consulta es podemos ejecutar el script

 

SET LINESIZE 500
SET PAGESIZE 1000

SELECT a.sql_text
FROM   v$sqltext a
WHERE  a.hash_value = &hash
ORDER BY a.piece;

 

 

http://www.expodba.com/script/troubleshooting/sql-partir-de-un-hash

 

Ahora que ya sabemos cual es el Hash, la consulta que ejecuta y que accede a nuestra tabla por FULL, seguramente queramos saber en forma global que otros accesos tiene y como accede, para eso necesitamos el plan de ejecución completo en memoria de la query.

 

SET linesize 163
SET pagesize 1000
col ACCESO format a50
 
SELECT b.COST,a.EXECUTIONS,a.child_number,b.id,b.parent_id,b.operation,
b.options || '(' || b.object_name || ')' AS ACCESO
FROM V$SQL a, V$SQL_PLAN b
WHERE
a.hash_value=b.hash_value AND
a.hash_value='&numero' AND
a.ADDRESS=b.ADDRESS AND
a.CHILD_NUMBER = b.child_number
ORDER BY child_number, id;

 

 

¿Porque diferencio "El plan en memoria"?  lo dejamos para otra nota, pero el plan de ejecución que se encuentra en memoria puede ser distinto al plan de ejecución consutlado en otro momento. Y más aún si la consulta utiliza Bind Variables.

Más adelante escribiré un articulo y lo anexaré a este para que quede referencia.

 

Ok, ya tenemos el Hash, el SQL, el Plan de ejecución, ahora necesitamos saber quienes son los que lo están ejecutando.

Para eso consultamos la V$session buscando sesiones que utilizan un hash_value en particular:

SELECT sid, serial#, username, osuser, program, logon_time
FROM v$session s
WHERE sql_hash_value = '&HASH_VALUE';

http://www.expodba.com/script/performance/filtrar-sesiones-por-hashvalue-ejecutado

 

Ya tenemos todo lo que necesitamos, ahora es solo cuestión de ver las query que lanzan las sesiones adquiridas, ver de que forma se puede mejorar el plan, ya sea reescribiendo la consulta o modificando la estructura de la tabla, creando indices adecuados, haciendo un shrink, particionado...etc...