martes, 26 de febrero de 2008

¿Tunear en base al COSTO del plan de ejecución?

Todos los días observo que alguien está queriendo tunear una consulta en base al costo del plan de ejecución. Pero... ¿Qué es el COSTO? ¿Qué representa? La respuesta es simple: El costo representa unidades de trabajo o recursos utilizados. El optimizador usa I/O a disco, CPU y memoria como unidades de trabajo. Entonces, el costo para una determinada consulta representa una estimación de la cantidad de I/O a disco, de CPU y memoria que se utilizará para la ejecución de la consulta.

Bien, con ésto ya dicho, porqué hay personas que tratan de tunear una consulta en base al costo??? El costo es simplemente un número que le asigna el optimizador de costos (CBO) a la consulta para saber qué plan de ejecución elegir entre todos los planes que genera en el momento de la optimización (el plan de ejecución que se genera con el menor costo es el que Oracle utiliza para ejecutar nuestra consulta), pero no existe un "mejor número" que debemos tener en mente para deducir si una consulta es óptima o no.

NO debemos tunear en base al costo. SI debemos tunear en base a los I/O lógicos (LIO's).

Veamos un ejemplo:

SQL_9iR2> CREATE TABLE test AS
2 SELECT level id, 'nombre_'||level nom
3 FROM dual
4 CONNECT BY level <= 100000 ;

Table created.

SQL_9iR2> EXEC dbms_stats.gather_table_stats(user,'TEST') ;

PL/SQL procedure successfully completed.

SQL_9iR2> SET AUTOTRACE TRACEONLY

SQL_9iR2> SELECT nom
2 FROM test
3 WHERE id = 50000 ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=1 Bytes=17)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=49 Card=1 Bytes=17)

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

Como podemos observar, el costo de la consulta es de 49. Si yo tuneo en base al costo no puedo saber si el plan de ejecución que se está utilizando para ésta consulta es óptimo o no porque no sé que costo sería el ideal para saberlo. Es por eso que NO debemos tunear en base al costo. Por otro lado, podríamos tunear en base a la cantidad de I/O lógicos que se estén realizando. En éste ejemplo, se realizan 323 LIO's. Si observamos la consulta, estoy seleccionando la columna NOM que corresponde al ID 50000 . Como creé la tabla de forma tal que todos los ID's sean únicos, ésta consulta me debería traer un solo registro. Si nos ponemos a pensar, hacer 323 LIO's para traer sólo un registro es demasiado. En éste momento es en donde nos damos cuenta que tenemos un problema de performance porque estamos haciendo un Full Scan de una tabla de 100.000 registros para buscar solamente el ID 50000 que nos devuelve un sólo registro.

Veamos qué sucede si creamos un índice único por la columna ID,NOM...

SQL_9iR2> CREATE UNIQUE INDEX test_id_nom_uq ON test(id,nom) ;

Index created.

SQL_9iR2> EXEC dbms_stats.gather_index_stats(user,'TEST_ID_NOM_UQ') ;

PL/SQL procedure successfully completed.

SQL_9iR2> SELECT nom
2 FROM test
3 WHERE id = 50000 ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17)
1 0 INDEX (RANGE SCAN) OF 'TEST_ID_NOM_UQ' (UNIQUE) (Cost=2 Card=1 Bytes=17)

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

Luego de crear un índice único por la columna ID,NOM, ejecutando nuevamente la consulta, notamos que ahora sólo estamos realizando 3 LIO's y, por consiguiente, el costo se decrementó ya que estamos utilizando menos recursos que el caso anterior.

8 comentarios:

marel dijo...

hola leonardo.

Me llamo Erika, mexicana y debo confesar que estoy iniciando a tunear mis consultas y tu blog me ha parecido de lo más interesante además de ilustrativo..mi duda es que al momento de querer visualizar las estadisticas para ver el valor de los I/O lógicos me marca el siguiente error

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

También me gustaría me explicaras un poco más que son los LIO's.

En verdad te agradezco el tiempo que te des para contestarme y te felicito por tu blog.

marel dijo...

Hola Leonardo.

Me llamo Erika, mexicana y estoy iniciandome en esto de tunear y mi problema es con una consulta que consume mucha memoria en su ejecución y pues leyendo tu artículo estaba cayendo yo también en checar el COSTO, pero al querer ver las estadisticas me sale un error y no se como resolverlo..me puedes ayudar por favor. El error es el siguiente:
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

Otra cosa, me podrías también orientar acerca de que son los LIO's.

En verdad agradezco tu ayuda y te felicito por tu blog.

Leito dijo...

Leonardo:

Muy bueno tu blog, mi monbre es Leandro.

Te queria preguntar algo:

Al agregarle un indice unico, el plan de ejecucion arrojo 3 LIO's.
Me podes explicar por que son 3 las lecturas? y no 2. No deberia ser una lectura para el indice y otra para obtener el valor donde apunta el indice?

Saludos.

Leonardo Horikian dijo...

Hola Leandro,

Paso a explicarte. El índice que creaste vos, seguramente es un índice del tipo B*Tree (en tu caso un índice único). Supongamos que creaste éste índice:

CREATE UNIQUE INDEX id_uq ON t(id);

Si por ejemplo, ahora ejecutas:

SELECT 'test' FROM t WHERE id = 100;

Para encontrar el ID 100, Oracle comienza leyendo la estructure del árbol del índice B*Tree. Empieza por el nodo root, luego navega por el nodo branch y finalmente lee los registros necesarios en los nodos leaf (son los nodos que contienen los registros indexados).
Por cada vez que Oracle lee un nodo/bloque, se realiza un LIO.
En el ejemplo que muestro, la consulta realiza 3 LIO's porque accede a 3 nodos para encontrar el ID 100.
Si por el contrario, ejecutamos ésta consulta:

SELECT nombre FROM t WHERE id = 100;

Estariamos realizando 4 LIO's porque la columna "nombre" no es parte del índice, por lo cual, tenemos que realizar 1 LIO adicional para ir a buscar a la tabla el valor de esa columna.

La siguiente consulta puede ayudarte un poco más a entender el árbol del índice B*Tree:

SELECT blevel, blevel+1 height, leaf_blocks
FROM dba_indexes
WHERE index_name = 'ID_UQ';

BLEVEL = Este número representa el nivel del nodo root más el nodo branch.
HEIGHT = Este número representa la altura total del árbol del índice.
LEAF_BLOCKS = Este número representa la cantidad de nodos leaf del árbol del índice.

Saludos.

Anónimo dijo...

Hola Leonardo:
Me llamo Puri Ruiz. Llevo años trabajando con Oracle, desarrollando pl/sql, tu blog me parece fántástico actualmente estoy empezando a introducirme en el tuning y la información que transmites me parece clara y organizada, la seguiré detalladamente.
Un saludo, Puri Ruiz

Leonardo Horikian dijo...

Hola Erika,

El error SP2-0618 es debido a que el usuario que está ejecutando el AUTOTRACE no tiene granteado el rol PLUSTRACE...

SELECT grantee,
FROM dba_role_privs
WHERE granted_role = 'PLUSTRACE';

Saludos

Silvina dijo...

Hola Leonardo,
mi nombre es Silvina, y antes que nada quiero felicitarte por tu blog, me fue muy util, me ayudo a resolver algunas dudas.
Y quisiera hacerte una consulta sobre performance.
Cuando se realiza una consulta para recuperar un registro de una tabla, conviene usar un SELECT...INTO o un cursor???
Y me gustaria que me comentaras porque es mas conveniente uno que otro.

Muchas gracias.
Silvina.

Leonardo Horikian dijo...

Hola Silvina,

Tu pregunta va referida a cuándo conviene utilizar cursores implicitos (SELECT...INTO / FOR...LOOP) y cuándo conviene utilizar cursores explicitos (cursor).
La verdad que este tema es para hablarlo bastante, pero voy a tratar de ser lo más claro y resumido que sea posible.

CURSORES IMPLÍCITOS:
--------------------

Con ésta clase de cursores, PL/SQL hace la mayoría del trabajo por nosotros ya que no tenemos que abrir, cerrar, declarar ni hacer un fetch explícitamente con un cursor implícito.

Tendríamos que utilizar ésta clase de cursores si...

1) Cuando estamos leyendo al menos un registro de la consulta.
2) Cuando estamos leyendo un número limitado de registros.

CURSORES EXPLÍCITOS:
--------------------

Con ésta clase de cursores, PL/SQL nos deja que hagamos todo el trabajo nosotros. Tenemos que abrir, cerrar, declarar, fetchear los registros y por sobre todo, controlar un cursor explícito.

RESUMEN:
--------

Si tenemos que hacer un fetch de un sólo registro, deberíamos utilizar cursores implícitos (SELECT...INTO). No sólo ganamos performance, sino que también, ganamos el código extra que PL/SQL agrega internamente que nos ayuda a protegernos para que no nos olvidemos datos, o datos que por algún motivo tienen más registros de lo esperado.
Si estamos procesando pocos registros, los cursores implícitos (FOR...LOOP) son excelentes ya que hace sencilla la programación, reduce el código a programar y por sobre todo, nos ayuda a evitar errores que muchas veces suceden al utilizar cursores explícitos.
Si estamos procesando cientos de registros o más, nos conviene utilizar cursores explícitos con la cláusula BULK COLLECT y LIMIT. Por otro lado, si estamos utilizando SQL dinámicos (que nunca aconsejo utilizarlos a no ser que sea estrictamente necesario), no podemos utilizar cursores implícitos, si o si, tenemos que utilizar cursores explícitos.

Saludos