herramientas en oracle para realizar afinamiento de sentencias sql (aspectos esenciales) cont

Herramientas en Oracle para realizar afinamiento de sentencias

SQL (aspectos esenciales) cont.

EXPLAIN PLAN

Cómo leer los resultados del EXPLAIN PLAN

Para interpretar un plan de ejecución se requiere práctica. Algunas indicaciones generales que ayudan:

1. Una ruta de acceso se ejecuta antes que las rutas de acceso que están menos espaciadas que ella.

2. Si dos rutas de acceso están espaciadas a un mismo nivel, la que se encuentre más arriba se ejecutará primero.

EXPLAIN PLAN


Supóngase la consulta:

SELECT *FROM dept, empWHERE emp.depto = dept.deptno;

Un posible plan de ejecución, es el siguiente:

4 SELECT STATEMENT3 HASH JOIN1 TABLE ACCESS FULL DEPT2 TABLE ACCESS FULL EMP

EXPLAIN PLAN


En ocasiones, es necesario considerar otros factores al interpretar un plan de ejecución.

Considérese el siguiente plan de ejecución:

SELECT STATEMENT SORT ORDER BY NESTED LOOPS TABLE ACCESS FULL CLIENTE TABLE ACCESS BY ROWID EMPLEADOS INDEX RANGE SCAN EMPLEADO_NOMBRES_IDX

EXPLAIN PLAN


Una ruta de acceso puede estar compuesta por varios pasos en el plan de ejecución. Si se observa el resultado del EXPLAIN PLAN anterior y se siguen las indicaciones dadas se pensaría que lo primero que se ejecuta es:

INDEX RANGE SCAN EMPLEADO_NOMBRES_IDX

Sin embargo, este paso hace parte (se ejecuta de manera conjunta con) de la ruta de acceso:

TABLE ACCESS BY ROWID EMPLEADOS

EXPLAIN PLAN


Al ser una operación conjunta, esta se tomará como un grupo, es decir, como una sola operación.

Así, la primera operación que se ejecuta en este plan es:TABLE ACCESS FULL CLIENTE

Ya que se encuentra al mismo nivel que la operación conjunta formada por:

TABLE ACCESS BY ROWID EMPLEADOS INDEX RANGE SCAN EMPLEADO_NOMBRES_IDX

EXPLAIN PLAN


Por lo tanto, el orden de los pasos en los que se ejecutará la consulta es

5 SELECT STATEMENT4 SORT ORDER BY3 NESTED LOOPS1 TABLE ACCESS FULL CLIENTE2 TABLE ACCESS BY ROWID EMPLEADOS INDEX RANGE SCAN EMPLEADO_NOMBRES_IDX

Otro ejemplo:

EXPLAIN PLAN


SELECT STATEMENT SORT UNIQUE UNION ALL MERGE JOIN SORT JOIN TABLE ACCESS FULL COMPANIA SORT JOIN TABLE ACCESS FULL VENTAS TABLE ACCESS BY ROWID COMPETIDOR INDEX UNIQUE SCAN COMPETIDOR_PK

EXPLAIN PLAN

El EXPLAIN PLAN ofrece datos que pueden dar una idea sobre el rendimiento de una consulta. Dichos datos provienen de las columnas de la tabla plan_table y son:

Costo - Cardinalidad - Bytes

Sin embargo, estos datos simplemente ofrecen una primera aproximación sobre el costo de la consulta y de lo que ocurre al ejecutarla, es necesario apoyarse en la teoría, fórmulas de costos y otras herramientas (se verá luego el TKPROF).

Se ven luego

HINTS

¿Cómo afectar el plan de ejecución?

• En forma predeterminada, el SGBD considera el plan de ejecución determinado por el optimizador.

• Sin embargo, por medio de hints se puede inducir a que el SGBD ejecute una sentencia con métodos deseados por el usuario.

• Los hints se colocan en la sentencia SQL a ejecutar.

HINTS¿Cómo afectar el plan de ejecución?

La sintaxis es:

SELECT /*+ [HINTS]*/ [columnas] FROM…

Los hints que se usarán en algunos de los ejemplos son

USE_NL(tablas): induce al optimizador a usarel método nested loops, usando la primera tabla como la “driving table” (primera tabla) en dicho método. USE_MERGE(tablas): induce al optimizador a usar el método sort merge.USE_HASH(tablas): induce al optimizador a usar el método hash join.

Estos métodos se explicarán en detalle posteriormente

Se pueden poner varios hints

• Si en la consulta se usan alias para las tablas, estos se deben usar en el hint (en lugar de los nombres de las tablas).

• A veces es imposible que el optimizador obedezca el hint (ver un ejemplo más adelante), en estos casos, el hint se ignora.

• Si un hint está mal escrito, se ignora (pero no se genera error).



ORDERED: induce al optimizador a reunir las tablas en el orden en el que aparecen en la cláusula FROM.

INDEX(tabla [índice]): induce al optimizador a usar el índice especificado de la tabla. Si no se especificó un índice en particular, se supone que se usará el índice de la clave primaria.

NO_INDEX(tabla [índice]): hace la operación opuesta al hint anterior.

Ejemplos:

• DROP TABLE test_for_ep_a;• CREATE TABLE test_for_ep_a (aa NUMBER PRIMARY KEY, ab VARCHAR2(100));

• DROP TABLE test_for_ep_b;• CREATE TABLE test_for_ep_b

(bb VARCHAR2(100) PRIMARY KEY, ba NUMBER REFERENCES test_for_ep_a);

EXPLAIN PLAN FORSELECT *FROM test_for_ep_a a, test_for_ep_b b WHERE a.aa = b.ba;

Observar el plan de ejecución para cada una de las siguientes consultas:

EXPLAIN PLAN FORSELECT /*+ USE_NL(a b) */ * FROM test_for_ep_a a, test_for_ep_b b WHERE a.aa = b.ba;

SELECT STATEMENT NESTED LOOPS TABLE ACCESS FULL TEST_FOR_EP_B TABLE ACCESS BY INDEX ROWID TEST_FOR_EP_A INDEX UNIQUE SCAN SYS_C005207

EXPLAIN PLAN FORSELECT /*+ USE_MERGE(a b) */ * FROM test_for_ep_a a, test_for_ep_b b WHERE a.aa = b.ba;

SELECT STATEMENT MERGE JOIN TABLE ACCESS BY INDEX ROWID TEST_FOR_EP_A INDEX FULL SCAN SYS_C005207 SORT JOIN TABLE ACCESS FULL TEST_FOR_EP_B

EXPLAIN PLAN FORSELECT /*+ USE_HASH(a b) */ * FROM test_for_ep_a a, test_for_ep_b b WHERE a.aa = b.ba;

SELECT STATEMENT HASH JOIN TABLE ACCESS FULL TEST_FOR_EP_A TABLE ACCESS FULL TEST_FOR_EP_B

Ahora:

EXPLAIN PLAN FORSELECT /*+ USE_HASH(a b) */ * FROM test_for_ep_a a, test_for_ep_b b WHERE a.aa < b.ba;

¿Por qué aquí no se hace el hash join?

ALTER SYSTEM flush buffer_cache;

• “The FLUSH BUFFER_CACHE clause lets you clear all data from the buffer cache in the System Global Area (SGA), including the KEEP, RECYCLE, and DEFAULT buffer pools.”

Precaución:• “This clause is intended for use only on a test

database. Do not use this clause on a production database, because as a result of this statement, subsequent queries will have no hits, only misses.”

• “This clause is useful if you need to measure the performance of rewritten queries or a suite of queries from identical starting points.”

ALTER SYSTEM flush shared_pool;

“The FLUSH SHARED POOL clause lets you clear all data from the shared pool in the system global area (SGA). The shared pool stores: Cached data dictionary information and Shared SQL and PL/SQL areas for SQL statements, stored procedures, function, packages, and triggers.”

herramientas en oracle para realizar afinamiento de sentencias sql (aspectos esenciales) cont

Documents