¿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.