El optimizador basado en costes (Cost Based Optimizer, CBO) se introdujo en Oracle 7 y se basa en calcular el coste de distintos planes de ejecución y eligir para la ejecución el plan de ejecución con menor coste. En su contra, está el optimizador basado en reglas (Rules Based Optimizer, RBO), que se basa en una reglas prefijadas para decidir el plan de ejecución de una consulta.

Para calcular el coste de una consulta, el optimizador se basa en las estadísticas almacenadas en el catálogo de Oracle, a través de la instrucción:

  ANALYZE [TABLE,INDEX] <object_name> [COMPUTE, ESTIMATE] STATISTICS;

Si no existen datos estadísticos para un objeto (por ejemplo, porque se acaba de crear), se utilizarán valores por defecto. Además, si los datos estadísticos está anticuados, se corre el riesgo de calcular costes basados en estadísticas incorrectas, pudiendo ejecutarse planes de ejecución que a priori pueden parecer mejores.
Por esto, si se utiliza el optimizador basado en costes, es muy importante analizar los objetos periodicamente (como parte del mantenimiento de la base de datos). Como las estadísticas van evolucionando en el tiempo (ya que los objetos crecen o decrecen), el plan de ejecución se va modificando para optimizarlo mejor a la situación actual de la base de datos. El optimizador basado en reglas hacía lo contrario: ejecutar siempre el mismo plan, independientemente del tamaño de los objetos involucrados en la consulta.

Dentro de la optimización por costes, existen dos modos de optimización, configurables desde el parámetro OPTIMIZER_MODE:

- FIRST_ROWS: utiliza sólo un número determinado de filas para calcular los planes de ejecución. Este método es más rápido pero puede dar resultados imprecisos.

- ALL_ROWS: utiliza todas las filas de la tabla a la hora de calcular los posibles planes de ejecución. Este método es más lento, pero asegura un plan de ejecución muy preciso. Si no se indica lo contrario, este es el método por defecto.

El optimizador basado en costes se utilizará en alguna de las siguientes situaciones:

1.- Si el parámetro OPTIMIZER_MODE del archivo INIT.ORA está definido como ALL_ROWS o FIRST_ROWS.
2.- Si existen estadísticas para alguna de las tablas involucradas en la consulta y el parámetro OPTIMIZER_MODE es distinto a RULE.
3.- Si se incluye un "hint" distinto a +RULE.
4.- Si se ha establecido la variable OPTIMIZER_GOAL a ALL_ROWS o FIRST_ROWS a nivel de sesión.
5.- En Oracle 7.3, si alguna tabla tiene un grado de paralelismo distinto de 0.

Hay que generar un plan de ejecución de la consulta a través del comando EXPLAIN PLAN. Una vez generado, se debe comprobar la columna POSITION de PLAN_TABLE para aquellas filas que ID=0. Si el valor de POSITION es NULL, se está usando el optimizador basado en reglas, en el resto de los casos se utiliza el basado en costes.

El siguiente SELECT nos puede servir para ver el tipo de optimizador utilizado en los distintos planes:

    SELECT DECODE( NVL(position,-1),-1, 'Reglas','Costes')
    FROM   plan_table
    WHERE  id = 0;

Esto se puede hacer con distintos métodos:

1.- Activando la traza de SQL a nivel de sesión.
2.- Utilizando la instrucción EXPLAIN PLAN.
3.- Utilizando el comando SET AUTOTRACE de SQL*Plus.

1.- Activar la traza de SQL a nivel de sessión:

Consultar el FAQ sobre trazas a nivel de sesión.

2.-Utilizar la instruccion EXPLAIN PLAN:

La instrucción EXPLAIN PLAN nos permite llenar la tabla PLAN_TABLE con datos sobre el plan de ejecución. La tabla PLAN_TABLE debe crearse, y para ello podemos ir al fichero UTLXPLAN.SQL del directorio $ORACLE_HOME/rdbms/admin. Una vez creada la tabla, ejecutaremos la instrucción:

    EXPLAIN PLAN
    [SET STATEMENT ID = 'identificador']
    INTO esquema.PLAN_TABLE
    FOR <instruccion SQL>;

Con esto llenaremos la tabla PLAN_TABLE con un número de registros indeterminado. Cada registro, tendrá en la columna STATEMENT_ID el valor 'identificador'. Para ver el plan de ejecución, podremos mostrar las distintas filas formateadas con la siguiente consulta:

    SELECT LPAD(’ ’, 2 * (level-1)) || operation operation,
           options,
           object_name,
    FROM   output
    START  WITH id = 0 AND 
           statement_id = 'identificador'
    CONNECT BY PRIOR id = parent_id AND
           statement_id = 'identificador';

3.- Utilizar el comando AUTOTRACE (a partir de Oracle 7.3)

El programa SQL*Plus nos permite mostrar el plan de ejecución de todas las instrucciones que vamos lanzando. Para ello basta con activar la auto-traza a través del comando:

    SET AUTOTRACE [ON,OFF] [EXPLAIN] [STATISTICS]

Si indicamos la opción EXPLAIN, nos mostrará el plan de ejecución, y si indicamos la opción STATISTICS, nos presentará una tabla de estardísticas tales como número de ordenaciones, lecturas a disco, bloques recuperados, etc. Para poder ejecutar este comando, es necesario tener el permisos sobre V$SESSION. La mejor manera de conseguir esto es concediendo el rol completo PLUSTRACE, y estar creada la tabla PLAN_TABLE dentro del esquema actual (ver el punto 2 para consultar cómo crear esta tabla).

Oracle nos permite obtener una traza de todas las instrucciones que se lancen en una sesión determinada. Puede ser útil para ver el plan de ejecución de cada una de ellas, para averiguar las instrucciones SQL que son lanzadas contra una base de datos (cuando no disponemos de los fuentes de un programa concreto), o incluso para detectar qué instrucción ralentiza un proceso complejo.

Los pasos a dar son:

a) Obtener el 'sid' y 'serial#' del proceso sobre el que queremos hacer la traza. Para ello debemos hacer la siguiente consulta sobre V$SESSION:

    SELECT username, program, sid, serial#
    FROM   v$session
    ORDER BY 1, 2;

Dentro de la tabla resultado, buscaremos nuestro proceso, por ejemplo:

USERNAME        PROGRAM                    SID   SERIAL#
--------------- -------------------- --------- ---------
SCOTT           DELPHI32.EXE                 9        30
 

b) Ejecutar el siguiente bloque de código PL/SQL:

BEGIN
    SYS.DBMS_SYSTEM.set_sql_trace_in_session(9, 30, TRUE);
END;

Esto nos activa la traza para una sesión identificada por sid y serial#

c) Ejecutar todas las instrucciones sobre las que queremos hacer la traza. Esto puede ser una instrucción concreta, un proceso completo, o incluso una jornada entera de trabajo con un programa.

d) Ejecutar de nuevo el bloque de código para desactivar la traza:

BEGIN
    SYS.DBMS_SYSTEM.set_sql_trace_in_session(9, 30, FALSE);
END;

e) En el directorio configurado para la salida aparecerá nuestro archivo de traza ORAxxx.trc. Este directorio se configura desde la opción USER_DUMP_DEST del archivo INIT.ORA. Por defecto tiene el valor ORACLE_HOME/rdbms/trace.

f) Formatear el archivo de traza a través de la utilidad TKPROF, situada en el directorio BIN de la instalación de Oracle. Una llamada básica podría ser la siguiente:

    TKPROF <fichero_traza> <fichero_salida> [explain=usuario/password] [sys=no]

Con el parámetro "explain=usuario/password" indicamos que nos muestre el plan de ejecución de todas las instrucciones, conectándose para ello al usuario/password indicados.
Con el parámetro "sys=no" indicamos que no nos muestre las instrucciones realizadas por el usuario SYS.

Además de las instrucciones y su plan de ejecución, el archivo de traza nos proporciona información sobre las sentencias erroneas, los tiempos de ejecución, el optimizador utilizado, etc.

Cuando se hace una consulta y la condición de filtro incluya una función de conversión (TO_NUMBER, TO_DATE, etc.), esta se debe aplicar siempre que se pueda sobre un valor constante en vez de sobre una columna.
Por ejemplo, la siguiente consulta no utilizará ningún indice sobre <COLUMNA_VARCHAR>

    SELECT <COLUMNAS>
    FROM   <TABLA>
    WHERE  TO_NUMBER(<COLUMNA_VARCHAR>) = 2;

Sin embargo, la siguiente consulta sí que utiliza los índices sobre la <COLUMNA_VARCHAR>

    SELECT <COLUMNAS>
    FROM   <TABLA>
    WHERE  <COLUMNA_VARCHAR> = TO_CHAR(2);

Las conversiones implícitas siguen unas normas parecidas, pero teniendo en cuenta que Oracle aplicará la función de conversión sobre el valor alfanumérico:
Por ejemplo, esta consulta:

    SELECT <COLUMNAS>
    FROM   <TABLA>
    WHERE  <COLUMNA_VARCHAR> = 2;

será interpretada del siguiente modo:

    SELECT <COLUMNAS>
    FROM   <TABLA>
    WHERE  TO_NUMBER(<COLUMNA_VARCHAR>) = 2;

Por lo tanto, desactivará los índices. Sin embargo, esta otra consulta:

    SELECT <COLUMNAS>
    FROM   <TABLA>
    WHERE  <COLUMNA_NUMBER> = '2';

será interpretada del siguiente modo:

    SELECT <COLUMNAS>
    FROM   <TABLA>
    WHERE  <COLUMNA_VARCHAR> = TO_NUMBER('2');

Por lo que no desactivará los índices, ya que la función se aplica sobre el valor constante y no sobre la columna.