Herramientas para la optimización

Herramientas para la

optimización

Creación de la tabla de Planes

1.  Se debe ejecutar el siguiente script para la

creación de la tabla de los planes (plan_table)

Ejemplo

del EXPLAIN PLAN

SQL> EXPLAIN PLAN

2 set statement_id = 'demo01' for 3 SELECT *

4 FROM sh.products

5 WHERE prod_category = 'Men'

6 AND prod_subcategory = 'Jeans - Men'; Explained.

Nota: El comando EXPLAIN PLAN no ejecuta la instrucción

Salida

del EXPLAIN PLAN

--- | Id| Operation | Name |Rows|Bytes |Cost(%CPU)| --- | 0 | SELECT STATEMENT | | 68| 17340| 23 (9) | | 1 | TABLE ACCESS BY INDEX ROWID | PRODUCTS | 68| 17340| 23 (9) | | 2 | BITMAP CONVERSION TO ROWIDS | | | | | | 3 | BITMAP AND | | | | | | 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |*5 | INDEX RANGE SCAN |PRODUCTS

PROD_

SUBCAT_IX| 270| | 1(0) | | 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |*7 | INDEX RANGE SCAN |PRODUCTS_

PRODCAT IX| 270| | 7(0) | --- Predicate Information (identified by operation id):

--- 5 - access("PRODUCTS"."PROD_SUBCATEGORY"='Jeans - Men')

7 - access("PRODUCTS"."PROD_CATEGORY"='Men') 19 rows selected.

Parse Tree

INDEX RANGE SCAN PRODUCTS_PROD_ CAT_IX BITMAP CONVERSION FROM ROWIDS BITMAP CONVERSION FROM ROWIDS

INDEX RANGE SCAN PRODUCTS_PROD_

Ejemplos de

AUTOTRACE

  Iniciar la traza usando AUTOTRACE:

  Para esconder la salida de la instrucción

  Mostrar únicamente el plan de ejecución:

set autotrace on

set autotrace traceonly

SQL*Plus

AUTOTRACE

:

Statistics

 

SELECT *

 FROM products

 WHERE prod_category = 'Men'

 AND prod_subcategory = 'Jeans - Men';

 Statistics  ---  0 recursive calls  0 db block gets  173 consistent gets  139 physical reads  0 redo size

 38030 bytes sent via SQL*Net to client

 741 bytes received via SQL*Net from client

 24 SQL*Net roundtrips to/from client

 0 sorts (memory)

La Herramienta SQL Trace

  Se habilita a nivel de la instacia o sesión

  Recolecta estadísticas reales para las

instrucciones SQL

  Produce una salida que debe “formatearse”

Como usar la Herramienta SQL Trace

1. Fijar parámetros de inicialización 2. Habilitar las trazas.

3. Ejecutar la aplicación.

4. Desabilitar las trazas y darle formato a la traza. 5. Interpretar la salida. Archivo Reporte Base de Datos Archivo de traza TKPROF Traza SQL

Parámetros de Inicialización

  MAX_DUMP_FILE_SIZE medido en bloques

del sistema de operación

  Por defecto USER_DUMP_DEST se ubica en

directorio de background dump.

TIMED_STATISTICS = {false|true}

MAX_DUMP_FILE_SIZE = {n|unlimited} USER_DUMP_DEST = directory_path

Ejemplos de utilización de

Herramientas

Pasos en las operaciones del

Optimizador

  Evaluación de las expresiones y condiciones.

  Transformación de las condiciones

  Selección del enfoque del optimizador:

reglas/costos

  Selección de los caminos de acceso

  Selección del Orden de Join

Transformación de las condiciones

  IN->OR/JOIN

  Expansión del OR— ORs ->UNION ALL.

  Concatenación: OR, uso de índices vs full table scan.

  ANY/SOME-> OR/EXISTS

  All->AND

  BETWEEN -> RANGE( y<= x and x=>z)

  Simplificación del not: not a>b-> a<=b

  Transitividad (column1 comp_oper constant AND column1

= column2) -> (column2 comp_oper constant)

  Fusión de Vistas

  Reescrituras en función de Vistas Materializadas

  Conversión del operador intersect en join y minus en

Escogencia del método de Join

  Similar a lo descrito en teoría:

  Ineficiente para el manejo de gran cantidad registros. Se

considera gran cantidad mas de10.000 registros.

costo = costo_acceso(A )+ (costo_acceso(B) * #registros(A))

  CBO: hash join es definido como el más eficientepara

retornar gran cantidad de registros a large number or rows. costo = (costo_acceso(A) * #particiones_hash(B)) +

costo_acceso(B)

  RBO: merge join se considera el más eficiente para

retornar gran cantidad de registros.

costo = costo_acceso(A) + costo_acceso(B) +

Escogencia del Plan de Evaluación

 

Consideraciones que aplican a los enfoques

de basado en costos y basado en reglas:

  El optimizador primero determina cuando el

resultado del join entre dos o más tablas retorna más de un registro (UNIQUE y PRIMARY KEY). El optimizador ubica primero en el orden del join

aquellas tablas que retornen un sólo registro y luego optimiza el join del conjunto de tablas restantes.

  Outer joins: la tabla con el operador de outer join,

debe venir después de la tabla que aparece en la condición join. El optimizador no considera joins que violen esa regla.

Escogencia del Plan de Evaluación

  El optimizador también considera otros factores en el momento de determinar los costos de cada operación. Por ejemplo:

  Un área de ordenamiento pequeña incrementa el costo

del merge join (SORT_AREA_SIZE).

  Un gran contador de lecturas multibloque tiende a disminuir el

costo de una operación de sort merge join en relación a nested loop join (DB_FILE_MULTIBLOCK_READ_COUNT).

  Con el CBO, la escogencia del orden del join puede ser sobreescrita con el hint ORDERED. Si el hint ORDERED especifica un orden de join que viola la regla de un outer join, entonces el optimizador ignora el hint y escoge el orden. Se pueden sobreescribir las escogencias del optimizador mediante hints.

Optimizing SQL Statements

 

Mejor rendimiento

  Tiempo requerido para completar una

petición.

  Adecuado para:

  Procesamiento en batch

  Aplicaciones de reportes

 

Respuesta rápida

  Tiempo requerido retornar primeros registros.

  Adecuado para:

  Aplicaciones interactivas

Fijando el enfoque del Optimizador

  Al nivel de la instancia:

  Para una sesión

OPTIMIZER_MODE = {CHOOSE|RULE|FIRST_ROWS_n| ALL_ROWS}

ALTER SESSION SET optimizer_mode =

Recomendaciones de los Hint

  Use hints cuidadosamente porque implican

alta carga de mantenimiento.

  Cuide el impacto en el desempeño de

Sintaxis de Ejemplo

SQL> UPDATE --+ INDEX(p PROD_CATEGORY_IDX) 2 products p

3 SET p.prod_min_price = 4 (select

5 (pr.prod_list_price*.95) 6 from products pr

7 where p.prod_id = pr.prod_id) 8 WHERE p.prod_category = 'Men'

9 AND p.prod_status = 'available, on stock' 10 /

Categorías de Hints

  Enfoque de optimización y objetivos

  Caminos de Acceso

  Transformación de Consultas

  Orden del Join

  Operador del Join

  Ejecución Paralela