Explain Plan

1
Explain Plan

• Utilización de EXPLAIN PLAN
• Evalúa el trayecto de ejecución de una consulta
  sin ejecutarla realmente
• Coloca su resultado en la tabla PLAN_TABLE
• Consulta del plan de ejecución

EXPLAIN PLAN SET statement_idTEST FOR SELECT
FROM empleado WHERE ciudad gt M
SELECT LPAD( ,2LEVEL)operation
options object_name q-plan FROM
plan_table WHERE statement_idTEST CONNECT BY
PRIOR idparent_id AND statement_idTEST START
WITH id1
2
Parejas Operación/Acción
3
Parejas Operación/Acción
4
Operaciones con JOIN

• Métodos
• MERGE JOIN
• NESTED LOOPS
• HASH JOINS
• Dependiendo de las condiciones límites, índices y
  estadísticas (CBO) se elige uno u otro
• Joins de múltiples tablas
• Se resuelven por emparejamientos
• Dependencia de las filas resultantes para
  establecer el orden

5
Operaciones con JOIN

• MERGE JOIN
• Pasos
• Procesar las entradas separadamente
• Ordenar (SORT JOIN)
• Unir (MERGE JOIN)
• Se utiliza en join de
• Tablas pequeñas
• Tablas muy grandes (usando la opción paralela de
  Oracle)
• Asumimos que no hay índice sobre el campo común

TABLE ACCESS FULL X
SORT JOIN
MERGE JOIN
TABLE ACCESS FULL Y
SORT JOIN
6
Operaciones con JOIN

• NESTED LOOPS
• Pasos
• Recuperar una fila de una tabla
• Para esa fila se accede a las filas de la otra
  tabla
• El acceso a la segunda tabla es via índice si es
  posible
• Útil cuando las tablas difieren en tamaños,
  seleccionado la pequeña como conductora
• Cuanto más selectivo sea el índice más rápido se
  completa la consulta

TABLE ACCESS FULL X (tabla conductora)
NESTED LOOPS
Valor clave a buscar
TABLE ACCESS BY ROWID
INDEX ACCESS sobre tabla Y
7
Operaciones con JOIN

• HASH JOIN
• Compara dos tablas en memoria
• Pasos
• Se lee la primera tabla, y se aplica una función
  hash a los datos para prepararlos
• Se leen los valores de la segunda tabla
  (normalmente TABLE ACCESS FULL) y la función hash
  compara la segunda tabla con la primera
• Se retornan las filas coincidentes
• Optimo si utilizamos la opción paralela
• Hacen uso extensivo de memoria por lo que puede
  ser necesario redimensionar la zona SGA en el
  fichero initltSIDgt.ora
• Apropiado para consultas online sobre tablas
  pequeñas

TABLE ACCESS FULL X
HASH JOIN
TABLE ACCESS FULL Y
8
Hints

• Para redirigir el CBO
• Sintaxis después de SELECT introducimos
• Para acceso a tablas tenemos
• FULL (nombre_tabla)
• ROWID (nombre_tabla)
• Ejemplo
• Para usar reglas ignorando CBO
• / RULE /
• Nota para intercambiar a nivel de sesión podemos
  teclear
• ALTER SESSION SET OPTIMIZER_GOALCHOOSERULE

/ hint /
SELECT / FULL(worker) / FROM worker WHERE
LodgingROSE HILL
9
Hints

• Para redirigir el CBO
• A usar un índice concreto
• Sintaxis
• INDEX (nombre_índice nombre_indice)
• INDEX_ASC (nombre_índice nombre_indice)
• INDEX_DESC (nombre_índice nombre_indice)

SELECT / INDEX(lodging) / Lodging FROM
lodging WHERE ManagerTom
10
Hints

• Sobre objetivos
• ALL_ROWS ejecuta la consulta y retorna cuando
  tiene todas las filas
• Ej usará MERGE JOIN
• FIRST_ROWS ejecuta la consulta de tal forma que
  la primera fila sea retornada tan pronto como sea
  posible
• Ej usará NESTED LOOPS
• Ejemplos

SELECT / ALL_ROWS / Worker.Name ,
Lodging.Manager FROM Worker, Lodging WHERE
Worker.Lodging Lodging.Lodging
SELECT / FIRST_ROWS / Worker.Name ,
Lodging.Manager FROM Worker, Lodging WHERE
Worker.Lodging Lodging.Lodging
11
Hints

• Para sugerir NESTED LOOPS
• USE_NL
• USE_NL (inner table)
• Ejemplos
• Para sugerir el orden de los join en un Nested
  Loop en el mismo orden que están en la cláusula
  FROM
• ORDERED
• Para sugerir MERGE JOIN
• USE_MERGE (nombre_tabla nombre_tabla)
• Para sugerir HASH JOIN
• USE_HASH elige la primera tabla a cargar en
  base a estadísticas
• USE_HASH (nombre_tabla)

SELECT / USE_NL(lodging / Worker.Name ,
Lodging.Manager FROM Worker, Lodging WHERE
Worker.Lodging Lodging.Lodging
SELECT / USE_NL / Worker.Name ,
Lodging.Manager FROM Worker, Lodging WHERE
Worker.Lodging Lodging.Lodging
12
SqlTrace y tkprof

• Similar a EXPLAIN PLAN
• Revela las cifras cuantitativas
• Genera el plan de ejecución
• Genera factores como uso de CPU y disco

13
Diagnóstico del rendimiento

• Pasos
• Establecer parámetros de inicio
• Establecer la traza de la operación
• Ejecutar la sentencia SQL
• Desactivar la traza
• Usar tkprof para formatear el fichero de traza
• Evaluar el rendimiento

14
Parámetros de inicio

• MAX_DUMP_FILE_SIZE
• Tamaño del fichero, en bloques o bien en bytes si
  se especifica en K o M.
• USER_DUMP_DEST
• Directorio de volcado
• TIMED_STATISTICS
• Incluir información de tiempo (milésimas de
  segundo)

15
Trace On / Off

• A nivel de instancia
• SQL_TRACE TRUE FALSE
• Todas las sesiones llevan traza
• Perjudica el rendimiento
• A nivel de sesión de usuario

ALTER SESSION SET sql_trace TRUE FALSE
16
Formateando el fichero de traza
tkprof tracefile outputfile sortoption
printn explainusername/password
insertfilename sysNO recordfilename
tableschema.tablename
ora_2001.trc
myfile.txt
USER_DUMP_DEST
tkprof ora_2001.trc myfile.txt
explainscott/tiger
17
Opciones de tkprof
18
Valores obtenidos

• Count veces ejecutadas
• CPU segundos en procesar
• Elapsed segundos transcurridos
• Disk lecturas físicas
• Query lecturas lógicas consistentes (SELECT)
• Current lecturas lógicas (DML)
• Logical I/O Query Current
• Rows filas procesadas
• Library Cache Misses veces no encontrada

19
Autotrace

• Automáticamente ejecuta la sentencia y muestra el
  plan de ejecución junto con las estadísticas
• Pasos
• Crear PLAN_TABLE
• utlxplan.sql
• Conceder privilegios al usuario sobre las vistas
  V... (ROLE PLUSTRACE)
• plustrce.sql
• Establecer autotrace
• ON ejecuta la sentencia y muestra el plan y/o
  las estadísticas
• TRACEONLY muestra el plan y/o las estadísticas
• Para mostrar tiempos transcurridos

SET AUTOTRACE OFF ON TRACEONLY EXPLAIN
STATISTICS
SET TIMING ON OFF
20
Mecanismos de Paralelismo

• Multiprocesador simétrico
• varios procesadores
• comparten memoria y disco
• único sistema operativo

CPU
CPU
CPU
MEMORIA
S.O
DISCO1
DISCO1
DISCO1
21
Mecanismos de Paralelismo

• Procesadores masivamente paralelos
• varios nodos interconectados
• cada uno con su CPU,memoria, disco propio o no y
  S.O propio
• la planificación se ejecuta concurrentemente en
  los nodos

RED
CPU
CPU
CPU
MEMORIA
MEMORIA
MEMORIA
DISCO
DISCO
DISCO
S.O
S.O
S.O
22
Consultas en Paralelo
Sentencia SELECT nº1
Se divide la consulta
Sentencia SELECT nº1 -1
Sentencia SELECT nº1 -2
Sentencia SELECT nº1 -N
Se resuelve la subconsulta en cada procesador
CPU1
CPU2
CPUn
RESULTADO
Se fusionan los resultados
Areas SQL Compartido y PL/SQL
23
Oracle Clausula PARALLEL

• Tiene que tener la opción de Parallel Query
• Ejemplos
• A) Default Oracle determina el grado de
  paralelismo
• B) n número de procesos a asignar

CREATE TABLE tablename (column1, column2
..) storage clause PARALLEL DEGREE DEFAULT
CREATE TABLE tablename (column1, column2
..) storage clause PARALLEL DEGREE (n)
24
Parámetros en Oracle

• parallel_default_max_instances
• parallel_max_servers
• parallel_min_percent
• parallel_min_servers
• parallel_server_idle_time
• recovery_parallelism

No debe invocarse si sólo tenemos una CPU
degradación del rendimiento
25
Hints

• Control de paralelismo y caché de datos en SGA
• PARALLEL para activarlo
• NOPARALLEL para desactivarlo
• Si estamos en la opción Oracle Parallel Server
  podemos determinar el paralelismo para las
  múltiples instancias
• CACHE hace que los datos permanezcan en cache
  tanto como sea posible

SELECT / PARALLEL (lodging,4) / FROM Lodging
SELECT / PARALLEL (lodging,4,2) / FROM
Lodging