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 acceso de cada cuadro mencionado en instrcción.
- Un método join 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 usuario dedicado para este proposito.
1. Creamos el role PLUSTRACE.
Hacemos login en SQL*PLUS como sys y ejecutamos lo siguiente para crear la PLAN_TABLE:
SQL>@$ORACLE_HOME/sqlplus/admin/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 admin option;
Grant succeeded.
2. Creamos un usuario, por ejemplo ORATUNING.
CREATE USER ORATUNING
PROFILE DEFAULT
IDENTIFIED BY "oratuning"
DEFAULT TABLESPACE system
TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK;
3. Crear la PLAN_TABLE.
Hacemos login en SQL*PLUS con el usuario que acabamos de crear y ejecutamos lo siguiente:
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created.
4. Damos los siguientes privilegios al usuario ORATUNING.
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.
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).
GRANT UPDATE ANY TABLE TO ORATUNING;
GRANT INSERT ANY TABLE TO ORATUNING;
GRANT DELETE ANY TABLE TO ORATUNING;
7. Ver los explain plan.
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 user este,
SET AUTOTRACE ON;
Para desactivarlo:
SET AUTOTRACE TRACEONLY;
Ejemplo
SQL>set linesize 200
SQL>set autotrace on
SQL>SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME='ORATUNING';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
ORATUNING OPEN
Execution Plan
----------------------------------------------------------
Plan hash value: 1592013029
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name |Rows|Bytes |Cost (%CPU)|Time |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 95 | 12 (9)| 00:00:01 |
| 1 | MERGE JOIN 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 JOIN OUTER | | 1 | 81 | 9 (12)| 00:00:01 |
|* 5 | HASH JOIN | | 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| USER$ | 1 | 27 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | USER_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_USER')
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
