Uso de AUTOTRACE

Cafe nocturno - Vincent Van Gogh

Cafe nocturno - Vincent Van Gogh

Como dijimos en el post Como ver el plan de ejecucion Oracle provee diversas herramientas para visualizar el plan de ejecucion de una sentencia SQL.

Para los usuarios de SQL*Plus Oracle ofrece AUTOTRACE.

AUTOTRACE es una variable de entorno de SQL*Plus que nos permite obtener un reporte del plan de ejecucion y algunos datos estadisticos de la sentencia ejecutada.

A continuacion vemos un ejemplo del uso de AUTOTRACE.

SQL> set autotrace on

SQL> select emp_apellido from empleados;
EMP_APELLIDO
------------------------------
PEREZ
GARCIA
GOMEZ

Execution Plan
----------------------------------------------------------

Plan hash value: 3220727562
--------------------------------------------------------------------
|Id | Operation         | Name      |Rows|Bytes|Cost(%CPU)| Time   |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT  |           |  3 |  51 |    3  (0)|00:00:01|
| 1 |  TABLE ACCESS FULL| EMPLEADOS |  3 |  51 |    3  (0)|00:00:01|
--------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        599  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

Hay varias configuraciones de AUTOTRACE que permiten controlar la salida del reporte:

  • SET AUTOTRACE OFF Desactiva la generacion del reporte. Es el valor por defecto.
  • SET AUTOTRACE ON EXPLAIN El reporte muestra solamente el plan de ejecucion.
  • SET AUTOTRACE ON STATISTICS El reporte muestra solo las estadisticas de la sentencia SQL ejecutada
  • SET AUTOTRACE ON El reporte muestra las estadisticas y el plan de ejecucion de la sentencia SQL ejecutada
  • SET AUTOTRACE TRACEONLY Es similar a SET AUTOTRACE ON pero se suprime la impresion de la salida del query. Si STATISTICS esta activado, la sentencia se ejecuta pero no se muestra el conjunto resultado.
Anuncios

Como ver el plan de ejecucion

Moulin Rouge, la Goulue - Henri de Toulouse-Lautrec

Moulin Rouge, la Goulue – Henri de Toulouse-Lautrec

Ahora puedes ver nuestros vídeos cortos acerca del lenguaje SQL

Como vimos en el articulo “El comando EXPLAIN PLAN“, dicho comando inserta el plan de ejecucion de una sentencia SQL en la tabla PLAN_TABLE. Oracle provee ademas un conjunto de herramientas para visualizar el plan de ejecucion en un formato que resulte practico a quien deba interpretarlo.

Una vez ejecutado el comando EXPLAIN PLAN se puede utilizar alguno de los siguientes scripts o package para visualizar el plan de ejecucion:

  • UTLXPLS.SQL este script reside en el servidor de base de datos y se puede utilizar para visualizar la PLAN_TABLE cuando se ejecutan sentencias en forma serializada.
  • UTLXPLP.SQL reside en el servidor de base de datos y se puede utilizar para ver el plan de ejecucion cuando se ejecutan sentencias que se procesan en paralelo.
  • DBMS_XPLAN.DISPLAY esta funcion del package DBMS_XPLAN acepta diversas opciones de visualizacion de la PLAN_TABLE.

Ejemplo de visualizacion del plan de ejecucion utilizando UTLXPLS.SQL

SQL> explain plan for select EMP_APELLIDO from EMPLEADOS;
Explained.
SQL>  @$ORACLE_HOME/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3220727562
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    17 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLEADOS |     1 |    17 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement
12 rows selected.

Ejemplo de visualizacion del plan de ejecucion utilizando UTLXPLP.SQL

SQL> @$ORACLE_HOME/rdbms/admin/utlxplp
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3220727562
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    17 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLEADOS |     1 |    17 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement
12 rows selected.

Ejemplo de visualizacion del plan de ejecucion utilizando DBMS_XPLAN.SQL

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3220727562
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    17 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMPLEADOS |     1 |    17 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement
12 rows selected.

Creacion de la PLAN_TABLE

Tres musicos - Pablo Picasso

Tres musicos - Pablo Picasso

La PLAN_TABLE es creada automaticamente para todos los usuarios a fin de almacenar el output de la sentencia EXPLAIN PLAN. La PLAN_TABLE es la tabla de salida por defecto en la cual la sentencia EXPLAIN PLAN inserta las filas que describen el plan de ejecucion seleccionado por el generador de planes del optimizador de una base de datos Oracle.

Si bien la tabla PLAN_TABLE es creada automaticamente para cada usuario, es posible utilizar el script utlxplan.sql para crear manualmente una tabla local dentro de un esquema determinado. El nombre y ubicacion exacta del script utlxplan.sql depende de cada sistema operativo. En Unix, esta ubicado en $ORACLE_HOME/rdbms/admin. En general la ejecucion de utlxplan.sql es llevada adelante por el DBA ya que dicho script esta ubicado en un directorio local al servidor de la base de datos.

Por ejemplo, para ejecutar el script desde SQL*Plus:

CONNECT usuario/password
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created.

Si por algun motivo se desea crear la tabla con otro nombre, es posible ejecutar utlxplan.sql y luego renombrar la tabla con la sentencia RENAME:

RENAME PLAN_TABLE TO mi_plan_table;

El comando EXPLAIN PLAN

Ahora puedes ver nuestros vídeos cortos acerca del lenguaje SQL

Para ejecutar una sentencia SQL Oracle necesita llevar adelante varios pasos. Cada uno de estos pasos recupera fisicamente de la base filas de datos o los prepara de alguna manera para entregarlos al usuario que ejecuto la sentencia. La combinacion de pasos que Oracle lleva adelante para ejecutar una sentencia constituye el plan de ejecucion.

¿Como visualizamos dicho plan de ejecucion? La sentencia EXPLAIN PLAN permite examinar el plan de ejecucion determinado por el optimizador. Cuando se ejecuta EXPLAIN PLAN el optimizador selecciona un plan de ejecucion y lo inserta en una tabla de la base de datos. Para visualizar el plan de ejecucion simplemente hay que ejecutar el comando EXPLAIN PLAN y luego hacer un query sobre la tabla de salida.

Los pasos basicos para utilizar la sentencia EXPLAIN PLAN son:

  1. Crear la tabla de salida denominada PLAN_TABLE con el script UTLXPLAN.SQL
  2. Incluir la clausula EXPLAIN PLAN antes de la sentencia SQL cuyo plan se pretende visualizar.
  3. Una vez ejecutada la sentencia con la clausula EXPLAIN PLAN, visualizar la tabla PLAN_TABLE con alguno de los scripts o packages provistos por Oracle para tal fin. Existen otras herramientas para visualizar planes de ejecucion, Productos como TOAD o SqlDeveloper ofrecen dicha posibilidad facilmente a traves de opciones de menu.

En general, la creacion de la PLAN_TABLE es realizada por el DBA. Para crearla, el administrador de base de datos suele ejecutar en forma manual el script utlxplan.sql. El nombre y ubicacion exacto de dicho script depende del sistema operativo. En Unix esta ubicado en el directorio $ORACLE_HOME/rdbms/admin.

Por ejemplo, estando conectado desde el mismo servidor de base de datos,

CONNECT usuario/password 
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created.

Una vez creada la PLAN_TABLE, es posible obtener un plan de ejecucion anteponiendo la clausula EXPLAIN PLAN FOR inmediatamente antes de la sentencia. Por ejemplo:

EXPLAIN PLAN FOR
  SELECT empleado_apellido FROM empleados;

La ejecucion de esta sentencia inserta el plan de ejecucion en la tabla PLAN_TABLE.

Por ultimo, se puede consultar la PLAN_TABLE utilizando alguno de los siguientes scripts o packages provistos por Oracle para obtener el plan de ejecucion mas reciente:

  • Script UTLXPLS.SQL
  • Script UTLXPLP.SQL
  • Funcion DBMS_XPLAN.DISPLAY

Que es el plan de ejecucion?

Mujer de rojo y verde - Fernand Leger

Mujer de rojo y verde - Fernand Leger

Una sentencia SQL submitida por un usuario se puede ejecutar de varias maneras diferentes. Por ejemplo, una sentencia sencilla podria resolverse recorriendo una tabla completa o accediendo por un indice. El optimizador de sentencias, que es parte del motor de base de datos Oracle, es quien determina la forma mas eficiente para ejecutar una sentencia SQL. Para determinar la forma mas eficiente, el optimizador debe considerar diversos factores relacionados con los objetos referenciados y las condiciones especificadas en el query. La eleccion de la forma en que se resolvera una sentencia constituye un paso muy importante en el procesamiento de una sentencia SQL dado que tiene un efecto muy importante en el tiempo de ejecucion.

La forma en que Oracle resolvera la sentencia constituye el plan de ejecucion. Podriamos decir que el optimizador recibe como input una sentencia SQL y luego de determinar la forma mas eficiente para ejecutarlo devolvera como output un plan de ejecucion. Dicho plan describe el metodo optimo para la resolucion de la sentencia.

El plan de ejecucion es una combinacion de pasos que llevara adelante Oracle para ejecutar una sentencia SQL.

Para determinar el plan de ejecucion de una sentencia SQL, el optimizador debe considerar varios factores. En primer lugar debe evaluar todas las expresiones y condiciones. En el caso de queries complejos que involucran vistas o subqueries correlacionados, el optimizador puede llegar a hacer una transformacion de la sentencia original. Tambien debe elegir los metodos de acceso, es decir que para cada tabla presente en la sentencia debe determinar como acceder para obtener los datos del modo mas eficiente. Por ultimo, el optimizador debe seleccionar el orden en que hara el join de las tablas. Si por ejemplo el query tiene tres tablas, el optimizador debe seleccionar las dos que joineara primero para luego hacer un nuevo join de la tercer tabla con el resultado del join anterior.

Como se puede apreciar, la seleccion del plan de ejecucion no es una tarea sencilla. Es una actividad que demanda recursos y tiempo. Como explicamos en el post parseo, la determinacion del plan de ejecucion se realiza en la fase de parsing y es la parte mas costosa de dicha etapa; en algunas ocasiones, incluso puede llegar a demorar mas tiempo que la misma ejecucion de la sentencia.

Variables de binding (bind variables)

La travesia dificil - Rene Magritte

La travesia dificil - Rene Magritte

A lo largo de los posts acerca del procesamiento de sentencias SQL hemos mencionado las variables de binding, tambien llamadas bind variables. Sin embargo, todavia no hemos dicho de que se tratan especificamente estas bind variables. En este post tratare de aclarar este concepto.

Antes de avanzar especificamente en el concepto de bind variable es importante recordar las etapas involucradas en la ejecucion de una sentencia SQL (parse, bind, execute y fetch) como asi tambien el concepto de cursor. Normalmente el desarrollador hace un uso implicito de los cursores y de las etapas de una sentencia a traves de APIs. Entre las APIs que frecuentemente utilizan los desarrolladores podemos mencionar ODP, JDBC, PL/SQL, etc.

Como dijimos en el post “Como escribir código para compartir cursores“, solamente las sentencias SQL completamente identicas son reconocidas como tales cuando el parser de SQL consulta la library cache en busca de cursores para compartir. En consecuencia las sentencias

select * from empleado where empno=1234
y
select * from empleado where empno=5678

seran consideradas diferentes. Ejecutar estas dos sentencias implicara dos parseos diferentes y dos entradas diferentes en la library cache. Sin embargo, estas sentencias hacen lo mismo; la unica diferencia radica en el valor.

Para evitar estas duplicidades, las aplicaciones deben utilizar algun mecanismo de variables que tome los valores reales en tiempo de ejecucion. La sentencia SQL deberia ser del tipo:

select * from emp where empno=:x

para que pueda ser ejecutada multiples veces con diferentes valores para la variable :x.

Una aplicacion bien diseñada haria un solo parse de esta sentencia y repetiria la ejecucion tantas veces como sea necesario con distintos valores para la variable de bind que utilice en el programa de la aplicacion. Esta forma de programar no solo hace que un simple programa funcione mejor sino que tambien asegura la escalabilidad cuando se ejecuten multiples instancias concurrentes del mismo programa compartiendo el mismo codigo SQL en la library cache. Esta ha sido siempre la recomendacion de Oracle para la programacion de aplicaciones.

Hay dos formas que las diversas APIs (ODBC, JDBC, PL/SQL) utilizan para hacer binding: por valor o por referencia. Cuando el binding se hace por valor se debe proveer el valor de todas las variables que estan en la sentencia SQL antes de cada ejecucion. Cuando el binding es por referencia, se utilizan las direcciones de memoria de las variables con los valores reales.

El parametro CURSOR_SHARING

Como vimos en el post Parseo, la etapa de parseo compara el texto de una sentencia con las sentencias existentes en la shared pool a fin de determinar si la sentencia puede ser compartida. Si el texto de la sentencia difiere, Oracle no comparte el cursor. El parametro CURSOR_SHARING permite al DBA modificar este comportamiento de “matching” del parser de SQL de Oracle. El valor por defecto de este parametro es EXACT, el cual fuerza al parser a buscar una sentencia exactamente igual para que el cursor pueda ser compartido. Dicho comportamiento puede ser modificado cambiando el valor del parametro CURSOR_SHARING a SIMILAR o FORCE, el cual permite que el parser considere como identicas todas las sentencias que sean iguales en todos los aspectos excepto en los valores literales.

De este modo, dos sentencias que son iguales en todo excepto en los literales, podran compartirse si se modifica el comportamiento del parser de SQL a traves del parametro CURSOR_SHARING.

Por ejemplo, las sentencias

a) select * from clientes where cliente_id = 14;
b) select * from clientes where cliente_id = 66;

seran tomadas como diferentes si CURSOR_SHARING = EXACT y por lo tanto no compartiran cursor. En cambio seran consideradas como identicas si CURSOR_SHARING = SIMILAR o FORCE, y por lo tanto compartiran cursor.

Existen muchisimas aplicaciones ya desarrolladas que no hacen uso de variables de bind (bind variables) o utilizan sql dinamico. Estas aplicaciones muy probablemente esten haciendo mal uso de la memoria de Oracle y tengan problemas de performance debido al incremento del tiempo de respuesta de las sentencias SQL por no encontrar, en la library cache, cursores para compartir. El problema puede haber sido diagnosticado correctamente aunque modificar toda la aplicacion para que haga uso de variables de bind puede requerir de la inversion de un esfuerzo enorme. En estos casos la utilizacion de CURSOR_SHARING puede resultar sencilla y dar resultados inmediatos. Sin embargo, el uso de cursor sharing no esta recomendado para todos los ambientes ni todas las versiones de Oracle.

La diferencia entre SIMILAR y FORCE esta fuera del alcance de este post. Como mencionamos en el parrafo anterior, el uso de cursor sharing no esta recomendado para todos los ambientes ni versiones de Oracle. El lector puede recurrir a la documentacion oficial de Oracle para mas detalle.