El significado de estos campos es el siguiente:
|| Campo || Descripción ||
|| STATEMENT_ID || El identificador de sentencia utilizado para el plan de ||
|| || ejecución. Este identificador será un nombre que daremos ||
|| || nosotros mismo al plan. ||
|| TIMESTAMP || Fecha y hora del momento en el que se generó el plan de ejecución. ||
|| REMARKS || Una columna “comentario” que se puede utilizar para añadir comentarios a los registros de PLAN_TABLE, utilizando la instrucción UPDATE como con cualquier otra tabla. ||
|| OPERATION || La operación SQL realizada en la etapa. ||
|| OPTIONS || El modo utilizado para la operación, como puede ser ||
|| || UNIQUE SCAN, SORT JOIN, etc. ||
|| OBJECT_NODE || EL database link usado para acceder al objeto. ||
|| OBJECT_OWNER || El propietario del objeto referenciado en la operación. ||
|| OBJECT_NAME || El nombre del objeto referenciado en la operación. ||
|| OBJECT_INSTANCE || La posición ordinal del objeto en el SQL analizado. ||
|| OBJECT_TYPE || Un atributo del objeto, como UNIQUE para los índices. ||
|| OPTIMIZER || El modo en que se ha utilizado el optimizador: CHOOSE ó RULE. ||
|| SEARCH_COLUMNS || No se utiliza. ||
|| ID || Un número único asignado a cada etapa en el plan. ||
|| PARENT_ID || El ID de la etapa que es el “padre” de la etapa actual en la ||
|| || jerarquía del plan de ejecución. Una etapa de ejecución se ||
|| || puede resolver en sub-etapas, formando así una jerarquía. ||
|| POSITION || El orden de proceso para etapas con el mismo Parent_ID. ||
|| COST || Coste relativo de la etapa. ||
|| CARDINALITY || Cardinalidad de un índice o el número de filas esperado que devuelva la operación. ||
|| BYTES || El tamaño (en bytes) de cada fila devuelta. ||
|| OTHER_TAG || Si el valor es SERIAL_FROM_REMOTE, el SQL en la ||
|| || columna Other se ejecutará en el nodo remoto. Otros valores ||
|| || describen el uso de la operación en Parallel Query Option ||
|| OTHER || Para consultas distribuidas, Other contiene el texto del SQL ||
|| || que es ejecutado en el nodo remoto. ||
Una vez que la tabla está creada en el usuario donde vamos a ejecutar la consulta, de debe ejecutar la siguiente sentencia:
EXPLAIN PLAN SET STATEMENT_ID = ‘identificador de sentencia’ FOR ;
El identificador de sentencia tiene que ser una cadena descriptiva para nuestra sentencia. Se utilizará más tarde para recuperar el plan entre todos los almacenados dentro de la consulta SELECT.
Esta sentencia lo que hará es almacenar en la tabla PLAN_TABLE un registro por cada paso en el plan de ejecución. El campos STATEMENT_ID de los pasos de nuestro plan de ejecución estará al valor indicado en ‘identificador de sentencia’.
Para mostrar el plan de ejecución se debe hacer un SELECT filtrando aquellos registros de nuestro plan de ejecución.
Una sentencia típica que nos muestra el plan de ejecución formateado podría ser:
SELECT id, parent_id, LPAD(’ ’, 2*(level-1)) ||operation|| ’ ’ ||options|| ’ ’ ||object_name|| ’ ’ || DECODE(id, 0, ’Cost = ’||position ) "Plan de consulta"
FROM PLAN_TABLE START WITH id = 0 and statement_id = ‘identificador de sentencia’ CONNECT BY prior id = parent_id and statement_id = ‘identificador de sentencia’;
Con esta instrucción, obtendremos un plan parecido a este:
QUERY PLAN
|| SORT || ORDER BY ||
|| NESTED LOOPS || ||
|| FILTER || ||
|| NESTED LOOPS || OUTER ||
|| TABLE ACCESS || FULL || HEADER ||
|| TABLE ACCESS || BY ROWID || DETAIL ||
|| INDEX || RANGE SCAN || DETAIL_PK ||
|| TABLE ACCESS || FULL || HEADER ||
|| TABLE ACCESS || BY ROWID || DETAIL ||
|| INDEX || RANGE SCAN || DETAIL_PK ||
|| NESTED LOOPS || OUTER || ||
|| TABLE ACCESS || FULL || HEADER ||
|| TABLE ACCESS || BY ROWID || DETAIL ||
|| INDEX || RANGE SCAN || DETAIL_PK ||
|| TABLE ACCESS || FULL || HEADER ||
|| TABLE ACCESS || BY ROWID || DETAIL ||
|| INDEX || RANGE SCAN || DETAIL_PK ||
|| TABLE ACCESS || BY ROWID || DETAIL ||
|| INDEX || RANGE SCAN || DETAIL_PK ||
Interpretando el plan de ejecución
Una de las tareas que más confusión crea es la interpretación y lectura de un plan de ejecución. Generar este plan es relativamente fácil, y basta con seguir ciertos pasos para obtenerlo. Sin embargo, leer e interpretar correctamente un plan de ejecución es una tarea compleja, en la que la experiencia es lo más importante. De todas formas, todo se puede aprender, y se pueden dar algunas indicaciones para ir introduciéndonos en este mundo.
La lectura del plan de ejecución se hace de arriba a abajo, y de izquierda a derecha. Como unas instrucciones están anidadas dentro de otras, esto significa que una instrucción se resuelve en instrucciones más pequeñas, que son las que están dentro de la más externa. A continuación se representa un plan de ejecución de ejemplo, en el que para resolver una operación A, es necesario resolver las instrucciones más sencillas “a1”, “a2” y “a3”, y si para resolver “a2” es necesario ejecutar “a21” y “a22”:
A a1 a2
a22 a23 a3
En plan se organiza en tres columnas, de mayor a menor generalidad. Estas columnas que indican:
|| ƒ || La operación a realizar ||
|| ƒ || Las opciones (o modo) que se aplican a la operación ||
|| ƒ || El objeto sobre el que se realiza la operación ||
Por ejemplo, la siguiente puede ser una línea de un plan de ejecución:
TABLE ACCESS BY ROWID DETAIL
Se muestran estas tres columnas, que significan: Operación: acceso a tabla (TABLE ACCESS) Modo: se accede a la tabla utilizando el ROWID (BY ROWID) Objeto: Se accede a la tabla "DETAIL" (DETAIL)
Las operaciones y modos más típicos que podemos encontrarnos son:
|| Operación y modo || Descripción ||
|| FILTER || Se descartan aquellas filas de la tabla que no cumplen con una condición impuesta en la cláusula WHERE. ||
|| INDEX UNIQUE || Se accede a la información localizada a través de una clave primaria o un índice único. Es uno de los modos más rápidos de acceder a una fila (además del acceso por ROWID). ||
|| INDEX RANGE SCAN || Se accede a la información localizada a través de un índice con repeticiones. Es típico que aparezca esta operación cuando utilizamos los operadores BETWEEN, <, >, <=, >= sobre columnas indexadas con repeticiones. ||
|| MERGE JOIN || Se combinan dos tablas ordenadas para dar como resultado una tabla ordenada y sin repeticiones. ||
|| HASH JOIN || Se combinan dos tablas utilizando un algoritmo de hash. ||
|| NESTED LOOP JOIN || Se combinan dos tablas utilizando bucles anidados. Por cada fila de la tabla padre, se recorren todas las filas de la tabla hija. ||
|| SORT ORDER BY || Se ordenan los datos de una tabla según lo indicado en la cláusula ORDER BY. ||
|| SORT GROUP BY || Se ordenan los datos de una tabla según lo indicado en la cláusula GROUP BY. ||
|| SORT JOIN || Se ordenan los datos de una tabla. El modo (JOIN) indica que la ordenación se hace para preparar los datos para un join de tipo MERGE JOIN. ||
|| SORT UNIQUE || Se ordenan los datos de una tabla. El modo (UNIQUE) indica que la ordenación se hace para eliminar duplicados, bien por un DISTINTC o el uso del operador UNION. ||
|| TABLE ACCESS FULL || Se hace una lectura secuencial (desde el primer registro hasta el último) de los datos de la tabla. Este es el modo más lento de acceder a los datos de una tabla. ||
|| TABLE ACCESS BY ROWID || Se hace una lectura de un registro concreto accediendo por su ROWID o por un índice único. Es el modo más rápido de acceder a un registro. ||
Trazas de ejecución
Las trazas de ejecución son una posibilidad que incluye Oracle para mostrar todas las sentencias y su plan de ejecución de programas que acceden a bases de datos Oracle. Es decir, no es necesario disponer del código fuente, ni de la sentencia SQL para saber qué y cómo se ha ejecutado. Además de las instrucciones y su plan de ejecución, el archivo de traza nos proporciona información sobre las sentencias erróneas, los tiempos de ejecución, el optimizador utilizado, etc.
Básicamente, activar la traza de ejecución consiste en ejecutar un procedimiento que tiene predefinido Oracle dentro del paquete DBMS_SYSTEM. El procedimiento tiene la siguiente cabecera:
SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION( sid, serial#, TRUE/FALSE );
El parámetro sid (session identifier) indica un número único para cada sesión establecida con Oracle. El parámetro serial# indica un número de serie para la sesión indicada. Ambos parámetros se podrán consultar en la tabla del catálogo SYS.V_$SESSION con la siguiente consulta:
SELECT SID, SERIAL#, MACHINE, TERMINAL, PROGRAM FROM SYS.V_$SESSION WHERE USERNAME = ‘usuario_conectado’;
Esta consulta nos mostrará los valores que debemos indicar en SID y SERIAL# para la sesión a la cual queremos hacer la traza. El tercer parámetro indica si queremos activar/desactivar la traza de ejecución.
Suponiendo que la anterior consulta ha retornado un SID de 9 y un SERIAL# de 30, activaremos la traza utilizando la siguiente llamada desde SQL*Plus:
BEGIN SYS.DBMS_SYSTEM.set_sql_trace_in_session(9, 30 TRUE); END;
Una vez que hemos ejecutado el procedimiento set_sql_trace_in_session, toda sentencia ejecutada sobre la sesión indicada quedará registrada en un archivo ORAxxxxx.TRC, normalmente bajo el directorio ORACLE_HOME/trace80, aunque esto depende de la opción USER_DUMP_DEST del archivo INIT.ORA.
Este fichero de traza contiene información detallada, pero ilegible, de los pasos seguidos por el plan de ejecución. Para conseguir una salida legible de esta traza se debe ejecutar la utilidad TKPROF, que podremos encontrar en el directorio BIN de Oracle. Una llamada típica podría ser la siguiente:
TKPROF [explain=usuario/password] [sys=no]
Con el parámetro "explain=usuario/password" indicamos que nos muestre el plan de ejecución de todas las instrucciones, conectándose para ello al usuario/password indicados. Con el parámetro "sys=no" indicamos que no nos muestre las instrucciones realizadas por el usuario SYS.
Existe otro modo más sencillo de obtener la traza de una sola instrucción, desde SQL*Plus con el auto-trace. Otro modo de activar el auto-trace es a través de la instrucción
Set auto trace ON/OFF
Una vez activada la auto-traza, toda sentencia ejecutada en SQL*Plus vendrá acompañada de su plan y estadísticas de ejecución. Además, con la instrucción
set timing ON/OFF
Se activará el reloj interno de Oracle con el que se podrá cronometrar el tiempo de ejecución de cada consulta.
