Oracle Live

13/04/2016

CAP 5.- Performance Tuning

Filed under: 1z0-060,Certificaciones — mogukiller @ 7:43 pm

Oracle 12c introduce una nueva mejora llamada adaptative sql optimization que engloba a su vez adaptative execution plans y adaptative statistics.

También se introduce mejoras en la captura de estadisticas en tiempo de ejecucion de la sentencias, durante las cargas masivas.

Por ultimo se introducen dos tipos nuevos de histogramas, frequency histograms y hybrid histograms.

1.- Adaptative Query Optimization

Para mejorar los planes de ejecución de las sentencias, ahora con AQP se capturan estadisticas durante el periodo de ejecución de la query, pudiendo modificar el plan de ejecucion en tiempo de ejecucion.

Esta nueva tecnica consiste en dos tecnicas:

Adaptative Execution Plans: Permite al optimizador esperarse a tiempo de ejecución para tomar la decisión final a cerca del plan de ejecución. Una vez elegido un plan de ejecución final se utiliza para el resto de ejecuciones. Normalmente esta tecnica se utiliza para elegir el plan de ejecucion de los joins o la distribucion de las ejecuciones en paralelo.

Adaptative Statistics: Cuando las estadisticas de una tabla no son suficientes el optimizador utiliza Adaptative Statistics. Se compone de:

  • Dynamic Statistics
  • Automatic reoptimization
  • SQL Plan directives.

1.1 Adaptative Execution Plans

Como se ha comentado Adaptative Execution Plan sigue capturando estadisticas en tiempo de ejecución para modificar el plan de ejecución si fuese necesario. Para hacer esto necesitamos:

  • Parametro OPTIMIZER_FEATURES_ENABLE configurado a 12.1.0.1.
  • Parametro OPTIMIZER_ADAPTATIVE_REPORTING_ONLY configurado a FALSE (default). Si se configura a FALSE se pone en modo reporte.

Un subplan es parte de un plan de ejecucion que el CBO puede decidir utilizar o no. Durante la ejecucion se van observando un porcentaje de los valores devueltos, puediendo cambiar de subplan dependiendo de estos valores.

Adaptative Plans es util en operaciones con join, por que el CBO mira el numero de filas que filtra el predicado, si el numero de filas es pequeño es mas eficiente la opcion de nested loop join, pero si por lo contrario tenemos que hacer el join de muchas filas utilizaremos hash join.

Adaptative Plans tambien ayuda en la distribucion de los datos en las ejecuciones en paralelo de una query. Se introduce un nuevo metod HYBRID HASH, donde se puede posponer la distribucion de los procesos en paralelo hasta el tiempo de ejecucion. Si por ejemplo existen pocas filas para distribuir entre los parallel server el optimizador utilizara broadcast distribution method y enviará todas las filas a todos los parallel servers. Si por otro lado se devuelven muchas filas el optimizador utilizará el metodo hash distribution method. El optimizador puede cambiar entre estos dos metodos dependiendo del numero de filas devueltas en el paso anterior.

Cuando un plan de ejecucion ha cambiado se añade una nueva columna en la vista V$SQL, IS_RESOLVED_ADAPTIVE_PLAN, si esta a Y es que se ha llegado al plan optimi.

1.2 Adaptative Statistics

Existen tres tipos de Adaptative Statistics: dynamic statistics, automatic reoptimization, SQL plan directives.

Dynamic Statistics: Es una variación de lo que en 11g se llamaba Dynamic Sampling. En este caso el optimizador elige si necesita mas estadisticas y que nivel de estadisticas tiene que utilizar. Aportando estadisticas adicionales para una tabla o la cardinalidad para un join. A diferencia de la version anterior ahora en 12c los resultados obtenidos con dynamic statistics son almacenados para futuras ejecuciones. Por defecto Dynamic Statistics no esta habilitado, para hacerlo hay que configurar el parámetro OPTIMIZER_STATISTICS_SAMPLING a 11. El valor por defecto es 2, en este caso unicamente se utilizara Dynamic Statistics si alguna de las tablas de la sentencia no tiene estadisticas.

Para deshabilitar Dynamic Statistics se configura el parametro OPTIMIZER_STATISTICS_SAMPLING a 0. Oracle aconseja no utilizar Dynamic Statistics en queries que se tengan que compilar rapido, como, por ejemplo, en entornos OLTP.

Dynamic Statistics es util en predicados con LIKE con wildcards donde el optimizador hace predicciones de lo que va a devolver.

Automatic Reoptimization: A veces no es posible cambiar el plan de ejecucion en medio de la ejecucion de una sentencia. Por lo que se considera Automatic Reoptimization para modificar el plan de ejecucion para futuras ejecuciones. El optimizador puede modificar sus planes de ejecucion a lo largo del tiempo, siempre que detecte que mejorará los tiempos de ejecución. Para el calculo del nuevo plan usara la información obtenida en la primera ejecución. El optimizador puede reoptimizar una query varias veces, siempre aprendiendo de la ejecución anterior.

El optimizador puede utilizar statistics feedback o performance feedback para ajustar las estadisticas.

Statistics Feedback: Esta pensado para ajustar a la cardinalidad correcta. El optimizar habilitara statistics feedback cuando no hay estadisticas, predicados con operaciones conjuntivas o disjuntivas, o con predicados muy complejos Despues de estimar que es necesario utilizar Statistics feedback, el optimizador compara la cardinalidad obtenida con la calculada. Se el contenido difiere mucho, guarda el nuevo valor. Elabora un nuevo plan de ejecución con estos datos. Una vez cambiado el plan de ejecución se desactiva Statistics feedback.

Adicionalmente se creara una SQL Directive con la información por si otras sentencias se beneficiasen de la informacion.

Si se detecta una desviacion de la cardinalidad estimada, frente a la obtenida se marca la columna IS_REOPTIMIZABLE  a Y. Para que en la siguiente ejecucion el optimizador sepa como calcular la cardinalidad.

Performance Feedback: Esta pensado para calcular correctamente el grado de paralelismo. Para dejar al optimizador que decida el grado de paralelismo a utilizar hay que configurar el parametro PARALLEL_DEGREE_POLICY a ADAPTIVE.

SQL Plan Directives: SPD son automaticamente creadas con la informacion generada en Automatic Reoptimization y ayuda a decidir si son necesarias mas estadisticas, o si necesitamos dynamic sampling o si es necesario crear estadisticas de un grupo de columnas. Oracle puede crear SPD durante la compilacion de la sentencia o durante el plan de ejecución. Las directives son almacenadas en el TS de SYSAUX. Se puede consultar las vistas DBA_SQL_PLAN_DIR_OBJECTS y DBA_SQL_PLAN_DIRECTIVES.

Un ejemplo de SPN es por ejemplo cuando el optimizador se da cuenta que un determinado join una de las tablas tiene un skew muy alto (valores no distribuidos) Pues se crea una directiva que le dice al optimizador que utilice dynamic sampling.

SPN se define para una extructura de query y no para un deteminado SQLID por lo que varias queries pueden utilizar la informacion de una determinada directiva.

2.- Mejoras en la captura de estadísticas

Con 12c se mejora la captura de estadísticas en:

  • CREATE TABLE AS .. SELECT  y INSERT .. SELECT: En estos procesos de carga se obtienen las estadisticas online. Hay que tener en cuenta que si que hay que calcular las estadisticas en indices e histogramas.
  • Mejora la concurrencia a la hora de calcular estadisticas de varias tablas a la vez. Pudiendo tomar estadisticas a la vez de la tabla y los indices. Oracle necesita de los componentes Oracle Job Scheduler y de Advanced Queueing. Para configurar la toma de estadisticas por scheduler hay que tener en cuenta el parametro JOB_QUEUE_PROCESSES ya que este marca el numero de jobs ejecutandose de forma concurrente. Tambien hay que tener precaucion sobre los recursos que maneja DBMS_STATS ya que este no tiene control de lo que utiliza. Para habilitar la toma en paralelo de las estadisticas por el proceso automatico hay que configurar el paraemtro DBMS_STATS.SET_GLOBAL_PREFS configurando  CONCURRENT a ALL. Se puede monitorizar el problema en la vista DBA_OPTSTAT_OPERATION_TASKS.

2.1 Nuevos tipos de histogramas

Mejora en los histogramas. Con 12c se introducen dos nuevos tipos de histogramas:

  • Top Frequency Histogram.
  • Hybrid Histogram.

Top Frequency Histogram: Se optará por este histograma cuando tengamos más de 254 valores diferentes, pero mas del 99% de los valores tengan menos del 254 valores diferentes. Por lo que se hara un Frequency Histogram donde en los 253 buckets incluiran los valores mas comunes y en el ultimo bucket iria ese 1% del conjunto de valores con pocos registros. Para mas informacion consultar el laboratorio de histogramas aqui.

Hybrid Histogram: Se introducen para suplir el problema que aportaban los Height Balanced, en estos ultimos los buckets se rellenaban con valores homogeneos de valores, pudiendo quedar un end point un valor poco numeroso y quedar otro valor enmascarado. Para solucionar esto se crean los Hybrid Histograms donde cada valor con muchos registros queda siempre como end point y se indica lo que se repite en la nueva columna DBA_HISTOGRAMS.END_POINT_REPEAT_COUNT que se utiliza para calcular la cardinalidad para ese valor. Para mas informacion consultar el laboratorio de histogramas aqui.

3.- Adaptive SQL Plan Management

SQL Plan Management es el encargado de evitar que cambios en el plan de ejecución de una query degraden su performance. Para que un nuevo plan sea utilizado por una sentencia este tiene que ser evaludado y comprobado que realmente mejora los tiempos de ejecucion. SPM consta de 3 componentes principales:

  1. Plan capture: Crea las SQL Plan Baselines que almacena los planes de ejecución. Las SQL Plan Baselines se guardan en el SQL Management Base en SYSAUX.
  2. Plan selection: Asegura que unicamente los planes elegidos se utilizaran por las sentencias y almacenará los nuevos planes como unaccepted plan.
  3. Evaluará los unaccepted plan y unicamente aceptará aquellos que generan una mejora del rendimiento.

La captura de los planes de ejecucion puede ser automatica o manual.

Para la captura automática hay que configurar el parametro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE  a TRUE. Con este parametro a true, el optimizador mira primero a ver si hay una SQL Plan Baseline de esa query antes de calcular el plan de ejecucion por costes.

La captura manual se suele utilizar en migraciones de versión o cuando se esta desarrollando una aplicación. Los planes de ejecucion se añaden atuomaticamente a la SQL Plan Baseline como planes aceptados. Los planes se pueden cargar desde STSs, stored outlines, desde un cursor cache o una staging table.

Un conjunto de planes aceptados forma una SQL Plan Baseline. Si ese plan no es aceptado unicamente formará parte del SQL Plan History.

Las SQL Plan Baselines se almacenan en la SQL Management Base en SYSAUX.

En el caso de existir una SQL Plan Baseline para una determinada sentencia. El optimizador no utiliza el plan basado en costes calculado para esa sentencia. Lo compara con los planes existentes y sino estuviese lo marca como unaccepted y eligira entre los planes aceptados el que tenga menor coste. Por otro lado podemos marcar planes como Fixed, con esto estamos indicando al optimizador que estos tienen preferencia.

La evaluacion de los planes unaccepted se realiza en horario de baja carga. Los resultados se pueden ver con el proceso DBMS_SPM.REPORT_ENVOLVE_TASK. Con Oracle 12c se introduce el advisor de SPM. Este Advisor se configura en una AutoTask. SYS_AUTO_SPM_ENVOLVE_TASK. Donde se evalua los planes no aceptados. Los planes que mejoren el actual plan de ejecucion son automaticamente aceptados. Los resultados podrán verse en DBMS_SPM.REPORT_AUTO_ENVOLVE_TASK.

Es posible evaluar manualmente un plan de ejecucion utilizando el API del SPM envolve advisor:

  • DBMS_SPM.CREATE_ENVOLVE_TASK
  • DBMS_SPM.SET_ENVOLVE_TASK_PARAMETER
  • DBMS_SPM.EXECUTE_ENVOLVE_TASK
  • DBMS_SPM.IMPLEMENT_ENVOLVE_TASK
  • DBMS_SPM.REPORT_ENVOLVE_TASK

La vista DBA_SQL_PLAN_BASELINES informa de las baselines creadas en la base de datos.

 

Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Blog de WordPress.com.

A %d blogueros les gusta esto: