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:
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.
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
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 :)
Hola Gonzalo,
Podés comunicarte al 0800-666-0525 para hablar con un representante de Oracle University Argentina.
Saludos
Publicar un comentario