Herramientas en Oracle para realizar afinamiento de sentencias SQL (aspectos esenciales) cont.

1. Herramientas en Oracle para realizar afinamiento de sentencias SQL (aspectos esenciales) cont.

2. 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: • Una ruta de acceso se ejecuta antes que las rutas de acceso que están menos espaciadas que ella. • Si dos rutas de acceso están espaciadas a un mismo nivel, la que se encuentre más arriba se ejecutará primero.

3. EXPLAIN PLAN Cómo leer los resultados del EXPLAIN PLAN Supóngase la consulta: SELECT * FROM dept, emp WHERE emp.depto = dept.deptno; Un posible plan de ejecución, es el siguiente: 4SELECT STATEMENT 3 HASH JOIN 1 TABLE ACCESS FULL DEPT 2 TABLE ACCESS FULL EMP

4. EXPLAIN PLAN Cómo leer los resultados del 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

5. EXPLAIN PLAN Cómo leer los resultados del 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

6. EXPLAIN PLAN Cómo leer los resultados del 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

7. EXPLAIN PLAN Cómo leer los resultados del EXPLAIN PLAN Por lo tanto, el orden de los pasos en los que se ejecutará la consulta es 5 SELECT STATEMENT 4 SORT ORDER BY 3 NESTED LOOPS 1 TABLE ACCESS FULL CLIENTE 2 TABLE ACCESS BY ROWID EMPLEADOS INDEX RANGE SCAN EMPLEADO_NOMBRES_IDX Otro ejemplo:

8. EXPLAIN PLAN Cómo leer los resultados del 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

9. 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

10. 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.

11. 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 usar • el método nestedloops, usando la primera tabla como la “drivingtable” (primera tabla) en dicho método. • USE_MERGE(tablas): induce al optimizador a usar el método sortmerge. • USE_HASH(tablas): induce al optimizador a usar el método hash join. Se pueden poner varios hints Estos métodos se explicarán en detalle posteriormente

12. HINTS ¿Cómo afectar el plan de ejecución? • 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).

13. HINTS ¿Cómo afectar el plan de ejecución? • 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.

14. 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 FOR SELECT * FROM test_for_ep_a a, test_for_ep_b b WHERE a.aa = b.ba;

15. Observar el plan de ejecución para cadauna de lassiguientes consultas: EXPLAIN PLAN FOR SELECT /*+ 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

16. EXPLAIN PLAN FOR SELECT /*+ 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

17. EXPLAIN PLAN FOR SELECT /*+ 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

18. Ahora: EXPLAIN PLAN FOR SELECT /*+ 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?