Algunos conceptos basicos en Performance Oracle    

Por que no empezamos por el principio definiendo que es tuning?.

 

Se lo podría definir como el cambio en el setup que se hace a un sistema en hardware o software para modificar sus características con el objeto de lograr alguna mejora.

 

Trate de no usar la palabra "performance" ya que el tuning puede tener diferentes razones o metas:

 

- Por rendimiento , para cumplir con grandes cargas de trabajo en el menor tiempo posible.

 

- Por tiempo de respuesta , para retornar los datos de las consultas lo mas rápido posible.

 

- Por grandes cantidades de usuarios , para que el sistema pueda soportar y funcionar  normalmente con grandes cantidades de usuarios.

 

- Por tiempo de carga de trabajo , para distribuir la carga de trabajo en forma optima en el tiempo.

 

- Para Backup y recovery , se ajusta el sistema para hacer las operaciones de backup y recovery en el menor tiempo posible.

 

-Otras metas definidas por el negocio.

 

Dejando de lado el tuning de instancia de la base de datos y de hardware ( que son temas para largas discusiones y para escribir libros también).

 

Podemos continuar con el optimizador de Oracle:

 

El optmizador es usado para generar el plan de ejecución de las sentencias sql que corren en la base de datos.Usando las estadísticas obtenidas de los objetos en la base de datos, el optimizador genera un plan de acceso ( que objetos son usados ) y un plan de ejecución ( que operaciones son usadas).

 

Cómo funciona el optimizador?

 

Cuando una sentencia SQL es parseada y pasada al optimizador sucede lo siguiente:

 

1 - Analiza y evalúa la sentencia SQL.

 

2- El optimizador modifica la sentencia , si la sentencia es compleja , hace los cambios necesarios para que sea más efectiva de procesar.

 

3- Luego realiza una fusión o merging de vistas de existir en la consulta.

 

4 - Elige entre los métodos basado en costos (CBO) o reglas,

 

5 - Elige entre las formas de acceso de cada tabla referenciada en la sentencia SQL.

 

6 - Elige el orden de los joins , si hay más de uno en la sentencia de SQL , elige el orden apropiado en que deben de ejecutarse.

 

7- -elije las operaciones apropiadas para ejecutar los joins.

 

Básicamente , el optimizador divide las sentencias en sus componentes y determina su costo individual.

 

Optimizador de Oracle:

 

Una de las primeras tareas que el DBA Oracle debe verificar es el modo por defecto para el optimizador de base de datos. Los parámetros de inicialización de Oracle ofrecen  opciones  de modos basados en los costos , así también  basado en reglas:

 

Los modos que se pueden setear en el ora.ini   (parámetro optimization_mode) son los siguientes:

 

-Choose

 

Esta opción permite al optimizador elegir entre el método CBO ( si existieran las estadísticas para los objetos llamados en las sentencias sql) , de no existir estadísticas elegiría el enfoque basado en reglas.

 

-RULE

 

Esta opción causa que el optimizador siempre use el enfoque basado en reglas , sin importar si existen o no estadísticas en los objetos de la base de datos.

 

A pesar de que esta opción fue desoportada en 11g por obsolescencia , ( esta funcionando desde Oracle 6 y anteriores ..... esto me delata un poco la edad pero bueno nada es gratis en la vida :)    )  sigue funcionando hasta 11g R2 y en 12c también según me comentaron varios colegas.

 

- ALL_ROWS

 

Esta opción causa que el optimizador siempre use el enfoque CBO , sin importar si existen estadísticas de las tablas que son accesadas , esta opción tiene la meta de del mejor rendimiento con el menor uso posible de los recursos del sistema.

 

- FIRST_ROWS

 

Esta opción causa que el optimizador siempre use el enfoque CBO , sin importar si existen estadísticas de las tablas que son accesadas , esta opción tiene la meta de del mejor tiempo de respuesta.

 

Generalmente genera sobrecarga  de I/O.

 

*Nota : para setear a nivel de session se hace con el parametro optimizer_goal

 

Metodos del Optimizador  :

 

Basado en Reglas ( o normas según traducción)

 

El enfoque basado en reglas es el más simple de los dos enfoques ( el otro enfoque es CBO) , en este método se deriva el plan de ejecución

 

examinando las formas de acceso disponibles para la sentencia de sql procesada , y comparando esas formas de acceso contra una tabla de rankings de esas formas de acceso.

 

El optimizador PL/SQL basado en reglas hace uso del siguiente orden de prioridades ( o ranking) para determinar cuál va a ser la forma de acceder a las tablas y obtener finalmente cual va a ser el plan de ejecución:

 

Prioridad  Forma de acceso

 

---------------------------------------------------

 

  1       Single row by ROWID

 

  2       Single row by cluster join

 

  3       Single row by hash cluster key with unique or

 

          primary key

 

  4       Single row by unique or primary key

 

  5       Cluster join

 

  6       Hash cluster key

 

  7       Indexed cluster key

 

  8       Composite index

 

  9       Single-column index

 

 10       Bounded range search on indexed column

 

 11       Unbounded range search on indexed column

 

 12       Sort-merge join

 

 13       MAX or MIN of indexed column

 

 14       ORDER BY on indexed column

 

 15       Full table scan

 

El método sigue los siguientes pasos para determinar el plan de ejecución:

 

1 - Determina los posibles planes de ejecución.

 

2 - Rankea los planes de ejecución de acuerdo a la tabla de arriba.

 

3- Selecciona el plan con el menor ranking.

 

Basado en mi experiencia , puedo afirmar que el optimizador basado en reglas  utilizara siempre que puede los índices, incluso cuando las tablas son pequeñas o cuando el número de registros que devuelve la sentencia SELECT es un porcentaje alto con respecto al número total de registros de la tabla, caso en  que es mejor realizar un escaneado total (full scan) ya que la respuesta es más rápida.

 

Como se puede deducir , esto ocurre por que el  optimizador basado en normas no hace uso de valores estadísticos, tales como el número total de registros de una tabla.

 

Este método es eficiente y funciona muy bien , pero si existen estadísticas en la base de datos , el método de costos puede ser más eficiente.

 

Una vez mas les recuerdo que Oracle anuncio desoportarlo en 11g y en 12c ( al menos en 12.1) ....pero aun no lo ha echo por que funciona , para probarlo basta con cambiar el método del optimizador y evaluar la ejecución de un query,

 

Para cambiarlo basta con la siguiente instruccion para la session

 

alter session set optimizer_mode=rule;

 

Igualmente les recomiendo hacerlo en una base que tengan para jugar ( todos los dba tenemos algunas para nuestra diversión solamente) para no causar problemas en entornos de desarrollo y productivos ( así no gritan los molestos usuarios ).

 

Si ven que al final del explain aparece el mensaje

 

" Note

 

-----

 

- rule based optimizer used (consider using cbo)"

 

Lograron hacer funcionar el optimizador en modo Rule.

 

Para volver a CBO basta con la siguiente sentencia:

 

alter session set optimizer_mode=all_rows;

 

A pesar de que este modo va a ser desoportado  , es muy útil dependiendo de la del problema y ademas es conveniente saber como funciona el optimizador de Oracle  para entender las técnicas de optimizar de SQL.( Es como gatear antes de caminar).

 

Basado en Costos ( o costes según traducción).

 

Este enfoque usa información disponible de la estructura y contenido de la base de datos para elegir el plan de ejecución mas eficiente.

 

El optimizador basado en costos utiliza las "estadísticas" que se recogen en las tablas con el comando "analyze" o el paquete dbms_stats.El optimizador basado en costos  utiliza la información obtenida en las estadísticas para obtener el plan de ejecución mas optimo.

 

Puede suceder  casos en los que el optimizador basado en reglas tendrá como resultado más rápido las consultas de Oracle.

 

Este enfoque (CBO) se cumple en 3 etapas

 

1 - Genera un conjunto de posibles planes de ejecución , tal como los hace el enfoque del optimizador basado en reglas.

 

2 - El costo de cada plan es determinado por las estadísticas obtenidas de la base de datos ,  considera como factores a tener en cuenta

 

los tiempos de CPU , I/O , y la memoria requerida para ejecutar el plan.

 

3 - EL optimizador compara los costos y elige el plan de menor costo.

 

El optimizador por CBO es el default en las ultimas versiones de Oracle y el recomendado para utilizar , pero nuevamente depende del tipo de modelo de negocios que soporte el modelo en las bases que están bajo nuestra administración.

 

Los modos de optimizacion si bien son intuitivos , también son limitados , no conocen las características de la aplicación como nosotros podemos hacerlo , y también no pueden entender si la consulta está escrita de la mejor forma para cumplir con las reglas de negocios , solo intentan encontrar la mejor solución para cada consulta.

.

En resumen, el DBA Oracle siempre tratará de cambiar el modo de optimizador de consultas como el primer paso  en las mejoras de performance de SQL de Oracle , verificando que el uso de los mismos sean acordes con el tipo de aplicaciones que trabajamos , ya que no es igual una base de datos de EBS a una que contiene un modelo desarrollado Ad-Doc a las necesidades de un negocio en particular , o un datawarehouse , o algún sistema de toma de decisiones desarrollado en función a una industria en particular.

 

Por ultimo quisiera agregar que  se pueden lograr mejoras en consultas usando los Hints de Oracle , para lo cual deben saber previamente como funciona el optimizador de Oracle ( de lo que se trato el post ) .

 

Sobre el uso de los  Hints se podría escribir otro post ( por que es mucha información para incluirlo en este Post).