martes, 19 de mayo de 2009

Costo super alto!

En Febrero del 2008, publiqué un post llamado "¿Tunear en base al COSTO del plan de ejecución?". En ese post, les decía que NO hay que tunear en base al costo del plan de ejecución, sino al trabajo que realiza la consulta en la base de datos.

Voy a mostrarles una consulta en el que el costo es super alto pero el trabajo que realiza en la base de datos no lo es:

SQL_10gR2> explain plan for
2 SELECT (t5.column_value).getstringval() t5
3 FROM TABLE(xmlsequence(extract(xmltype(''),'/x'))) t1,
4 TABLE(xmlsequence(t1.column_value))t2,
5 TABLE(xmlsequence(t2.column_value))t3,
6 TABLE(xmlsequence(t3.column_value))t4,
7 TABLE(xmlsequence(t4.column_value))t5;

Explicado.

SQL_10gR2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'ALL'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
Plan hash value: 4104774429

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18E| 15E| 98P (2)|999:59:59 |
| 1 | NESTED LOOPS | | 18E| 15E| 98P (2)|999:59:59 |
| 2 | NESTED LOOPS | | 4451T| 31P| 12T (2)|999:59:59 |
| 3 | NESTED LOOPS | | 544G| 3045G| 1481M (2)|999:59:59 |
| 4 | NESTED LOOPS | | 66M| 254M| 181K (2)| 00:36:18 |
| 5 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | | | | |
| 6 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | | | | |
| 7 | COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE | | | | |
| 8 | COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE | | | | |
| 9 | COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE | | | | |
----------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$E270DE78

Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
2 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
3 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
4 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40]
5 - VALUE(A0)[40]
6 - VALUE(A0)[40]
7 - VALUE(A0)[40]
8 - VALUE(A0)[40]
9 - VALUE(A0)[40]

Wow!!! Y esos números??? Extremadamente altos no??? Bueno, según el plan de ejecución, esa consulta retorna 18 cuatrillones (18E) de registros, 15 exabytes (15E), tiene un costo de 1.8E19 (98P) y el tiempo de ejecución es de aproximadamente 1 mes (999:59:59)!!!

Veamos qué sucede cuando ejecutamos la consulta y obtenemos las estadísticas de la misma:

SQL_10gR2> SELECT (t5.column_value).getstringval() t5
2 FROM TABLE(xmlsequence(extract(xmltype(''),'/x'))) t1,
3 TABLE(xmlsequence(t1.column_value))t2,
4 TABLE(xmlsequence(t2.column_value))t3,
5 TABLE(xmlsequence(t3.column_value))t4,
6 TABLE(xmlsequence(t4.column_value))t5;

T5
----------------------------------------------------------------
< x/ >

1 fila seleccionada.

Transcurrido: 00:00:00.04

Estadísticas
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Como podemos observar, la consulta retorna un sólo registro y se ejecuta en tan solo 4 milisegundos, consumiendo sólo 3 bloques de datos.

Este ejemplo es otra prueba de cómo el optimizador puede calcular valores totalmente erróneos en el plan de ejecución. Es por eso, que hay que tener especial cuidado al ver esos números cuando obtenemos un plan de ejecución y al tratar de optimizar la consulta en base a esos números.

Para ésta consulta en particular, el problema es que el optimizador desconoce las estadísticas de las tablas (que en éste caso no son tablas, son llamadas a funciones). Fácilmente, podemos "mejorar" el plan de ejecución, agregando el hint CARDINALITY para decirle al optimizador cuántos registros va a retornar esa función... que en éste caso, es un sólo registro:

SQL_10gR2> explain plan for
2 SELECT /*+ cardinality(t1 1) cardinality(t2 1) cardinality(t3 1) cardinality(t4 1) cardinality(t5 1) */
3 (t5.column_value).getstringval() t5
4 FROM TABLE(xmlsequence(extract(xmltype(''),'/x'))) t1,
5 TABLE(xmlsequence(t1.column_value))t2,
6 TABLE(xmlsequence(t2.column_value))t3,
7 TABLE(xmlsequence(t3.column_value))t4,
8 TABLE(xmlsequence(t4.column_value))t5;

Explicado.

SQL_10gR2> SELECT * FROM table(dbms_xplan.display('plan_table',null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
Plan hash value: 4104774429

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 122 (2)| 00:00:02 |
| 1 | NESTED LOOPS | | 1 | 10 | 122 (2)| 00:00:02 |
| 2 | NESTED LOOPS | | 1 | 8 | 97 (2)| 00:00:02 |
| 3 | NESTED LOOPS | | 1 | 6 | 73 (2)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 4 | 49 (3)| 00:00:01 |
| 5 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | | | | |
| 6 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | | | | |
| 7 | COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE | | | | |
| 8 | COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE | | | | |
| 9 | COLLECTION ITERATOR PICKLER FETCH | XMLSEQUENCEFROMXMLTYPE | | | | |
----------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$E270DE78

Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
2 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
3 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40], VALUE(A0)[40]
4 - (#keys=0) VALUE(A0)[40], VALUE(A0)[40]
5 - VALUE(A0)[40]
6 - VALUE(A0)[40]
7 - VALUE(A0)[40]
8 - VALUE(A0)[40]
9 - VALUE(A0)[40]

Recuerden siempre el acrónimo GIGO: Garbage In ... Garbage Out :)

4 comentarios:

tycho dijo...

Leonardo, te queria consultar ,tengo una query a un datawarehouse de muy bajo costo 260 aprox, por la mañana despues del proceso diario de poblado tarda como 20 segundos en realizarla, la haces varias veces y luego empieza a responder en 2 segundos aprox. Por lo que pienso se estaría cacheando, ahora si es asi como puedo hacer para que no se limpie del cache.

Gracias y saludos.

Leonardo Horikian dijo...

Veo 2 posibles cosas que pueden estar sucediendo:

1) El plan de ejecución de la consulta NO se encuentra en la Librery Cache, por lo tanto, Oracle debe generarlo nuevamente. Esto puede ser el motivo por el cual está tardando aprox. 20 segundos. El parseo de la consulta quizás está demorando bastante. Al ejecutarla nuevamente, la representación parseada de la consulta ya se encuentra en memoria, por lo que podemos reutilizarla en vez de generarla nuevamente.

2) Luego de reiteradas ejecuciones de la consulta, los bloques leidos desde disco estan siendo cacheados en memoria.

Lo que yo haría seria lo siguiente: prueba ejecutar la consulta (con un Trace) por la mañana luego del proceso de poblado y fijate en el Trace si lo que está demorando es el parseo o la lectura de bloques a disco. Hay maneras de cachear las tablas (ya que no podemos cachear solo bloques de datos). Pero siendo un datawarehouse, hay que ver si las tablas que queres cachear son pequeñas o grandes ya que de esto dependera si conviene o no hacerlo.

Saludos

GoLiCa dijo...

Leonardo, mi nombre es Gonzalo y soy de Argentina, Bs. As. Perdon por la molestia, pero te hago una consulta: hace unos meses que estoy trabajando como dba junior, me estan formando otro dba y los pdf interminables de Oracle. Quiero rendir las certificaciones necesarias, hasta OCA en principio. Hay algun mail de contacto o tel en Oracle Argentina para hacer consultas sobre institutos avalados por Oracle que den cursos? Ellos brindan ese servicio de información? Porque en la página probé llenando un formulario, y esperé a q alguien se contacte, pero nop... desde ya, muchas gracias, no sabía a quien preguntarle al respecto :)

Leonardo Horikian dijo...

Hola Gonzalo,

Podés comunicarte al 0800-666-0525 para hablar con un representante de Oracle University Argentina.

Saludos