Operación del plan que concentra el trabajo

El plan de una sentencia pesada suele gastar casi todo en una o dos operaciones. El hallazgo indica cuál, da una pista de lo que suele haber detrás y lleva directo a la fila del plan.

Síntomas

En el Diagnóstico aparece el hallazgo Sentencia #n: la operación id del plan concentra el p% del tiempo (PLANO_PONTO_QUENTE). Cuando la parte de la operación en las lecturas lógicas es mayor que en el tiempo, el título termina en de las lecturas lógicas. El número de la sentencia es el de la lista de sentencias SQL, el de la operación es su Id en el plan, y el porcentaje es la parte propia de la operación en el plan. En el informe de ejemplo, el hallazgo es Sentencia #1: la operación 1 del plan concentra el 98,0% del tiempo.

Junto al título, el porcentaje del tiempo total es el peso de la sentencia, por el tiempo inclusivo (todas sus llamadas, en cualquier profundidad). El texto empieza con el nombre de la operación, da la pista, lista las otras operaciones que también pesan (También pesan) y termina con Ver en el plan de la sentencia #n.

El hallazgo aparece para las sentencias que pesan en el tiempo total, por el tiempo inclusivo, cuando el plan, armado con las líneas STAT del trace, tiene más de una operación y una de ellas concentra el tiempo o las lecturas lógicas. Un plan de una sola operación no tiene adónde apuntar. Sale un hallazgo por sentencia, del primer plan de ella con una operación destacada, empezando por las sentencias que más pesan.

La gravedad es baja y pasa a media cuando la sentencia pesa más en el tiempo total.

Causa

El valor propio de una operación descuenta el de las operaciones que están debajo de ella y la alimentan. Las operaciones destacadas son las que más pesan en el plan por ese valor propio. La pista del hallazgo depende del tipo de operación:

La pista puede juntar la del tipo de operación con las de escritura en el TEMP, de muchas ejecuciones y de estimación errónea.

Solución

1. Haga clic en Ver en el plan de la sentencia #n.

   Resultado: el detalle de la sentencia se abre en la fila de la operación. En Dónde gasta el plan, las operaciones destacadas aparecen numeradas, de la más pesada a la menos pesada, con la parte de cada una en el tiempo y en las lecturas lógicas.

2. Haga clic en el nombre de la operación para abrir la tarjeta con lo que hace, cuándo es buena, cuándo es mala y qué columnas del plan revisar.
3. Compare la columna Filas de la operación con la de la operación justo debajo de ella. Si la tabla devuelve muchas menos filas de las que trajo el índice, el filtro está en la tabla y al índice le falta la columna del filtro.
4. Vea los predicados de acceso y de filtro, que las líneas STAT del trace no traen. Con el cursor todavía en la shared pool, use el SQL ID de la parte superior del detalle de la sentencia:

   select *
     from table(dbms_xplan.display_cursor('&sql_id', null, 'TYPICAL'));

5. Aplique la corrección del tipo de operación:
   • acceso por ROWID con filtro en la tabla: un índice con las columnas del filtro, las de igualdad primero;
   • lectura FULL con filtro selectivo: un índice para el filtro;
   • skip scan: un índice que empiece por la columna filtrada;
   • rango amplio del índice: la columna de igualdad antes de la columna de rango;
   • escritura en el TEMP: más memoria para la workarea (pga_aggregate_target) o menos filas llegando a la ordenación o al hash join;
   • muchas ejecuciones: la operación es el lado interno de un NESTED LOOPS. Corrija la estimación del lado externo o cambie el join por un HASH JOIN.
6. Para ver las columnas y los índices de la tabla, haga clic en el número de la columna obj del plan. La página del objeto, con consultas listas, existe solo en la consola; en el HTML descargado, el número abre el detalle del objeto, sin las consultas.

Si el Diagnóstico también muestra estimación de filas errónea para la misma sentencia, empiece por ella: con la estimación correcta, el optimizador puede elegir otro plan por sí solo.

En una fila con la etiqueta N variantes, que suma SQL con solo los literales diferentes, el peso de la sentencia es la suma de las variantes. En el detalle, cada plan diferente aparece una vez, con las grabaciones de las variantes sumadas y las filas de la ejecución grabada más lenta entre ellas: es en ese plan donde el hallazgo indica la operación.

Cómo confirmar

1. Aplique un cambio por vez.
2. Active el trace de la misma operación. Con el valor predeterminado de DBMS_MONITOR, el trace graba el plan de la primera ejecución de cada cursor; para grabar el de todas, use plan_stat => 'ALL_EXECUTIONS':

   begin
     dbms_monitor.session_trace_enable(session_id => &sid, serial_num => &serial,
                                       waits => true, binds => false,
                                       plan_stat => 'ALL_EXECUTIONS');
   end;
   /

3. Envíe el archivo nuevo y abra el detalle de la misma sentencia.
4. Compare Tiempo propio (s) y LIO propio de la operación y el Inclusivo de la sentencia, en la parte superior del detalle.

Resultado: la operación deja de concentrar el plan, o el plan cambia (otro número en Plan de ejecución). Cuando la sentencia deja de pesar en el tiempo total, sale del hallazgo.

En el informe

El informe de ejemplo tiene este hallazgo, hecho con el trace sintético; los enlaces abren cada parte de él.

• Diagnóstico: el hallazgo Sentencia #1: la operación 1 del plan concentra el 98,0% del tiempo, con la pista del TABLE ACCESS BY INDEX ROWID y la operación 2 en También pesan.
• Detalle de la sentencia #1: Peso en el tiempo total, Inclusivo, perfil de la sentencia y Dónde gasta el plan (detalle de la sentencia).
• Operación 1 del plan: la fila destacada, con Tiempo propio (s), % tiempo, LIO propio y % LIO (plan de ejecución).
• Sentencias SQL por contribución: el número de la sentencia y su peso (sentencias SQL).

En el informe de la consola, el enlace del hallazgo abre la página de la sentencia; en el HTML descargado, abre el detalle de la sentencia en lugar del informe, ya en la operación del plan.

Los informes de traces reales también tienen este hallazgo: vea el Diagnóstico del caso 1 y el del caso 2.