lunes, 3 de septiembre de 2007

Explain Plan vs. Autotrace Explain Plan

Hace unos meses me hicieron una pregunta que hoy en día veo que muchas personas no saben la respuesta. La pregunta era: ¿ Cuál es la diferencia de ejecutar el Explain Plan en SQL*Plus de ejecutar el Explain Plan desde la herramienta Autotrace ? Bueno, para contestar esta pregunta vamos a crear un muy sencillo ejemplo:

SQL_9iR2> CREATE TABLE estudiantes
2 (
3 legajo NUMBER(5),
4 nombre VARCHAR2(30),
5 ingreso DATE
6 )
7 PARTITION BY RANGE(ingreso)
8 (
9 PARTITION ingreso_2006 VALUES LESS THAN(TO_DATE('01/01/2007','DD/MM/YYYY'))
,
10 PARTITION ingreso_2007 VALUES LESS THAN(TO_DATE('01/01/2008','DD/MM/YYYY'))

11 ) ;

Table created.

SQL_9iR2> INSERT INTO estudiantes
2 SELECT level, 'nom_'||level, DECODE(MOD(level,2),0,sysdate,1,sysdate-500)
3 FROM dual
4 CONNECT BY level <= 1000 ;

1000 rows created.

SQL_9iR2> EXEC dbms_stats.GATHER_TABLE_STATS(user,'ESTUDIANTES') ;

PL/SQL procedure successfully completed.

Ya creamos nuestro ambiente de prueba. Veamos que sucede si vemos el Explain Plan desde el Autotrace:

SQL_9iR2> SET AUTOTRACE TRACEONLY EXPLAIN

SQL_9iR2> SELECT legajo, nombre
2 FROM estudiantes
3 WHERE ingreso = to_date('03/09/2007 12:11:27','dd/mm/yyyy hh24:mi:ss') ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=500 Bytes=9500)
1 0 TABLE ACCESS (FULL) OF 'ESTUDIANTES' (Cost=2 Card=500 Bytes=9500)

Vemos que estamos haciendo un full scan de la tabla, pero si observamos bien, podemos notar que en realidad no estamos leyendo la tabla entera (que tiene 1.000 registros), sino que sólo estamos leyendo 500 registros. Esto nos esta indicando que no estamos leyendo las 2 particiones que creamos, sólo estamos leyendo 1 de ellas... pero cuál?

Veamos que sucede si ejecutamos el Explain Plan:

SQL_9iR2> EXPLAIN PLAN FOR
2 SELECT legajo, nombre
3 FROM estudiantes
4 WHERE ingreso = to_date('03/09/2007 12:11:27','dd/mm/yyyy hh24:mi:ss') ;

Explained.

SQL_9iR2> @explains

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 9500 | 2 | | |
|* 1 | TABLE ACCESS FULL | ESTUDIANTES | 500 | 9500 | 2 | 2 | 2 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ESTUDIANTES"."INGRESO"=TO_DATE(' 2007-09-03 12:11:27', 'syyyy-mm-ddhh24:mi:ss'))

Claramente vemos que en el Explain Plan tenemos 2 columnas nuevas: Pstart y Pstop. Estas columnas nos están indicando que sólo estamos haciendo un full scan de la partición 2, no de la tabla completa.

Moraleja: Utilicen el Explain Plan cuando estén interesados en el plan de ejecución de la consulta. Utilicen el Autotrace cuando estén interesados en las estadísticas de la consulta.

11 comentarios:

Manuel dijo...

Tengo una consulta, espero me puedas dar una mano.
Se me esta presentando el error 1722 al trabajar con una aplicacion.
Hice la traza de la siguiente forma BEGIN SYS.DBMS_SYSTEM.set_sql_trace_in_session(9, 30, TRUE);END;

Habrá forma de saber el valor de las variables que se muestran en la traza.

gracias

Leonardo Horikian dijo...

Hola Manuel, lo que queres saber son los valores de las variables que se estan utilizando las consultas?

Por ejemplo, si haces una consulta de este tipo:

SELECT *
FROM empleados
WHERE id = v_id ;

Vos queres que te muestre en el trace el valor de la variable v_id?

Saludos

jason dijo...

gracias mano me sirbio de mucho tu aporte es la 1:46 de la mañana y no hallaba donde buscar lo del plan de ejecucion y lo encontre en tu blog gracia buen aporte

Anónimo dijo...

hola Leonardo, te escribo para ver si tú me puedes ayudar. Loque pasa que estoy tomando un diplomado de Oracle 10g y pues me dejaron realizar una tesina aserca de optimización de consultas. Y no he encontrado todavia la información necesaria, crees poder ayudarme diciendome en donde puedo encontrar?, o tú me puedes aportar?, de atemano gracias por tu atención.

Leonardo Horikian dijo...

Hola, en el siguiente link: http://www.oracle.com/pls/db102/portal.portal_db?selected=4 tienes todos los manuales de 10g. Allí hay una sección que es "Performance".

Saludos

1 dijo...

Hola, yo si quiero saber como obtener los valores de las variables en los planes de ejecución o en las trazas.

Como el usuario llamado Manuel nunca pregunto de nuevo, ni Leonardo le respondió, me queda la duda de si es posible o no. Le agradecería me ayudara a resolver esta inquietud.

Gracias.

1 dijo...

Hola Leonardo. Muchas gracias por su explicación. Se me presenta un error al realizar lo siguiente:

EXEC SYS.DBMS_SYSTEM.SET_ENV( 96 , 10299 , 10046 , 4 , NULL );

El error es:

ORA-06550: line 1, column 23:
PLS-00302: component 'SET_ENV' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Lo estoy ejecutando desde TOAD. Parece ser algún tipo de privilegio, o no es así?

Leonardo Horikian dijo...

Hola 1,

Para obtener las variables (bind variables) con la que ejecutaste las consultas, debes activar el trace en nivel 4 o 12.


-- Para activar el trace de una sesión logrando que capture las bind variables de las consultas ejecutadas:

- Para sesiones remotas ...
EXEC SYS.DBMS_SYSTEM.SET_EV( SID , SERIAL# , 10046 , 4 , NULL );

- Para la sesión actual ...
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER,LEVEL 4';



-- Para activar el trace de una sesión logrando que capture las bind variables y eventos de esperas de las consultas ejecutadas:

- Para sesiones remotas ...
EXEC SYS.DBMS_SYSTEM.SET_EV( SID , SERIAL# , 10046 , 12 , NULL );

- Para la sesión actual ...
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER,LEVEL 12';


Saludo

Leonardo Horikian dijo...

Hola 1,

Coloqué accidentalmente una N de más en el procedimiento SET_ENV. La manera correcta es SET_EV.

Prueba ejecutarlo de esta manera:

EXEC SYS.DBMS_SYSTEM.SET_EV( 96 , 10299 , 10046 , 4 , NULL );


Saludos

Carlos dijo...

Hola,

Te pregunto existe alguna herramienta o método para evaluar los costos de un programa pl/sql, así como lo es el explain plan en sql, si tienes algún ejemplo para mostrarlo te agradezco.

Muchas gracias

Leonardo Horikian dijo...

Hola Carlos,

Podes utilizar "PL/SQL Profiler". En versiones anteriores a 11g se ejecuta con el paquete DBMS_PROFILER y en 11g con el paquete DBMS_HPROF.

Ejemplo en 10g:

1) exec dbms_profiler.start_profiler('Ejemplo1');
2) Ejecutás un código PL/SQL (puede ser un paquete, procedimiento, función, etc).
3) exec dbms_profiler.stop_profiler;
4) exec dbms_profiler.flush_data;

Para ver el RUNID de la ejecución actual, ejecutás la siguiente consulta:

SELECT runid, run_date, run_comment, run_total_time
FROM plsql_profiler_runs
ORDER BY runid;

Para ver el resultado de la ejecución del código PLSQL, ejecutás la siguiente consulta:

SELECT u.runid, u.unit_number, u.unit_type, u.unit_owner, u.unit_name, d.line#, d.total_occur, d.total_time, d.min_time, d.max_time
FROM plsql_profiler_units u, plsql_profiler_data d
WHERE u.runid = &1
AND u.runid = d.runid AND u.unit_number = d.unit_number
ORDER BY u.unit_number, d.line#;


Saludos