El comando EXPLAIN PLAN muestra la ejecución de los planes elegidos por el optimizador de Oracle para SELECT, UPDATE, INSERT y DELETE. El plan de ejecución es la secuencia de operaciones que Oracle realiza para ejecutar la instrucción.
La salida del plan de ejecución muestra la siguiente información:
- Un ordenamiento de las tablas de referencia en la declaración.
- Un método de de cada cuadro mencionado en instrcción.
- Un método de las tablas afectadas por operaciones de combinación en la instrucción
- Datos de operaciones como filtrar, ordenar o agregación.
Además el cuadro del plan contiene información sobre los siguientes aspectos:
- Optimización, tales como el costo y la cardinalidad de cada operación.
- Particionamiento, como el conjunto de particiones visitada.
- Ejecución paralela, tales como el método de distribución de combinación.
Los resultados del EXPLAIN PLAN te permiten determinar si el optimizador elige un plan de ejecución en particular, como, combinación de bucles anidados. También te ayuda a comprender las decisiones del optimizador, como por qué el optimizador eligió una combinación de bucles anidados en lugar de una combinación hash, y te permite entender el rendimiento de una consulta.
Pasos a seguir:
Recomiendo crear un dedicado para este proposito.
1. Creamos el role PLUSTRACE. 2n1t5a
Hacemos en SQL*PLUS como sys y ejecutamos lo siguiente para crear la PLAN_TABLE:
SQL>@$ORACLE_HOME/sqlplus//plustrce.sql
SQL> drop role plustrace;
Role dropped.
SQL> create role plustrace;
Role created.
SQL>
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with option;
Grant succeeded.
2. Creamos un , por ejemplo ORATUNING. 3c3j59
CREATE ORATUNING
PROFILE DEFAULT
IDENTIFIED BY "oratuning"
DEFAULT TABLESPACE system
TEMPORARY TABLESPACE TEMP
UNLOCK;
3. Crear la PLAN_TABLE. 4j5o11
Hacemos en SQL*PLUS con el que acabamos de crear y ejecutamos lo siguiente:
SQL> @$ORACLE_HOME/rdbms//utlxplan.sql
Table created.
4. Damos los siguientes privilegios al ORATUNING. 23x61
GRANT CONNECT TO ORATUNING
GRANT CREATE SESSION TO ORATUNING;
GRANT PLUSTRACE TO ORATUNING;
GRANT SELECT ANY DICTIONARY TO ORATUNING;
5. Le asignamos también el privilegio SELECT ANY TABLE para que pueda realizar cualquier select. 4e3h5i
GRANT SELECT ANY TABLE TO ORATUNING;
6. Si además necesitaís poder realizar explain plan de UPDATES, INSERT y DELETE añadir estos privilegios (precaución con estos privilegios). 6v503q
GRANT UPDATE ANY TABLE TO ORATUNING;
GRANT INSERT ANY TABLE TO ORATUNING;
GRANT DELETE ANY TABLE TO ORATUNING;
7. Ver los explain plan. 1d435y
Existen varios metodos para ver el explain plan generardo por Oracle, el más basico es consultar la tabla PLAN_TABLE, pero para mi el más sencillo es ativar el autotrace de la siguiente manera:
SET AUTOTRACE ON;
Si solo queremos obtener el plan de ejecución sin la ejecución de la Query este,
SET AUTOTRACE ON;
Para desactivarlo:
SET AUTOTRACE TRACEONLY;
Ejemplo
SQL>set linesize 200
SQL>set autotrace on
SQL>SELECT NAME, _STATUS FROM DBA_S WHERE NAME='ORATUNING';
NAME _STATUS
------------------------------ --------------------------------
ORATUNING OPEN
Execution Plan
----------------------------------------------------------
Plan hash value: 1592013029
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name |Rows|Bytes |Cost (%U)|Time |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 95 | 12 (9)| 00:00:01 |
| 1 | MERGE CARTESIAN | | 1 | 95 | 12 (9)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 87 | 11 (10)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 84 | 10 (10)| 00:00:01 |
|* 4 | HASH OUTER | | 1 | 81 | 9 (12)| 00:00:01 |
|* 5 | HASH | | 1 | 56 | 7 (15)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | PROFILE$ | 1 | 8 | 1 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 54 | 4 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 46 | 3 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| $ | 1 | 27 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | I_1 | 1 | | 0 (0)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | _ASTATUS_MAP | 1 | 19 | 2 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | I_PROFILE | 17 | | 0 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | PROFNAME$ | 1 | 2 | 2 (0)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | RESOURCE_GROUP_MAPPING$ | 1 | 25 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS CLUSTER | TS$ | 1 | 3 | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
| 17 | TABLE ACCESS CLUSTER | TS$ | 1 | 3 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
| 19 | BUFFER SORT | | 1 | 8 | 11 (10)| 00:00:01 |
|* 20 | TABLE ACCESS BY INDEX ROWID | PROFILE$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | I_PROFILE | 17 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
4 - access("CGM"."VALUE"(+)="U"."NAME")
5 - access("U"."RESOURCE$"="P"."PROFILE#")
6 - filter("PR"."RESOURCE#"=1 AND "PR"."TYPE#"=1)
9 - filter("U"."TYPE#"=1)
10 - access("U"."NAME"='ORATUNING')
11 - filter("U"."ASTATUS"="M"."STATUS#")
12 - access("U"."RESOURCE$"="PR"."PROFILE#")
14 - filter("CGM"."VALUE"(+)='ORATUNING' AND "CGM"."STATUS"(+)='ACTIVE' AND
"CGM"."ATTRIBUTE"(+)='ORACLE_')
16 - access("U"."TEMPTS#"="TTS"."TS#")
18 - access("U"."DATATS#"="DTS"."TS#")
20 - filter("DP"."RESOURCE#"=1 AND "DP"."TYPE#"=1)
21 - access("DP"."PROFILE#"=0)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
487 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
