La manera mas fácil para obtener un plan de ejecución es usar la sentencia EXPLAIN PLAN FOR y el script utlxpls.sql:
SQL> set linesize 130
SQL> set pagesize 9999
SQL> EXPLAIN PLAN FOR
select OBJECT_NAME from dba_objects where OWNER='SYS' order by OBJECT_NAME;
Explained.
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2325770649
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1025 | 85075 | 7 (29)| 00:00:01 |
| 1 | SORT ORDER BY | | 1025 | 85075 | 7 (29)| 00:00:01 |
| 2 | VIEW | DBA_OBJECTS | 1025 | 85075 | 6 (17)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
| 5 | NESTED LOOPS | | 1179 | 101K| 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 14 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | OBJ$ | 1179 | 87246 | 3 (34)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 27 | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 14 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | I_LINK1 | 1 | 13 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("O"."TYPE#"1 AND "O"."TYPE#"10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM
"SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR
"I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)
7 - access("U"."NAME"='SYS')
8 - filter("O"."NAME"'_NEXT_OBJECT' AND "O"."NAME"'_default_auditing_options_'
AND "O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0 AND "O"."OWNER#"="U"."USER#")
9 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
"I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
10 - access("I"."OBJ#"=:B1)
13 - access("U"."NAME"='SYS')
14 - access("L"."OWNER#"="U"."USER#")
36 rows selected.
Como puedes ver hay mucha información acerca de esta sentencia SQL tan simple, por lo cual no olvides leer la documentación listada al final de este mensaje para tener un mejor conocimiento de los planes de ejecución.
Por cierto, si estás ejecutando sentencias con paralelismo obtendrás más información usando el script utlxplp.sql:
SQL> EXPLAIN PLAN FOR
select /*+ PARALLEL(b,4) */ b.OBJECT_NAME from dba_objects b where OWNER='SYS' order by OBJECT_NAME;
Explained.
SQL> @$ORACLE_HOME/rdbms/admin/utlxplp.sql
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2762843742
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1025 | 85075 | 15 (7)| 00:00:01 | | | |
| 1 | SORT ORDER BY | | 1025 | 85075 | 15 (7)| 00:00:01 | | | |
| 2 | VIEW | DBA_OBJECTS | 1025 | 85075 | 14 (0)| 00:00:01 | | | |
| 3 | UNION-ALL | | | | | | | | |
|* 4 | FILTER | | | | | | | | |
| 5 | PX COORDINATOR | | | | | | | | |
| 6 | PX SEND QC (RANDOM) | :TQ10001 | 1179 | 101K| 12 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 7 | NESTED LOOPS | | 1179 | 101K| 12 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 9 | PX RECEIVE | | | | | | Q1,01 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10000 | | | | | | S->P | BROADCAST |
| 11 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |
|* 12 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 | | | |
| 13 | PX BLOCK ITERATOR | | 1179 | 87246 | 11 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 14 | TABLE ACCESS FULL | OBJ$ | 1179 | 87246 | 11 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 15 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 1 (0)| 00:00:01 | | | |
|* 16 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 | | | |
| 17 | NESTED LOOPS | | 1 | 27 | 2 (0)| 00:00:01 | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |
|* 19 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 | | | |
|* 20 | INDEX RANGE SCAN | I_LINK1 | 1 | 13 | 1 (0)| 00:00:01 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("O"."TYPE#"1 AND "O"."TYPE#"10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM "SYS"."IND$" "I" WHERE
"I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"
=7 OR
"I"."TYPE#"=9))=1)
12 - access("U"."NAME"='SYS')
14 - filter("O"."NAME"'_NEXT_OBJECT' AND "O"."NAME"'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
BITAND("O"."FLAGS",128)=0 AND "O"."OWNER#"="U"."USER#")
15 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
"I"."TYPE#"=9)
16 - access("I"."OBJ#"=:B1)
19 - access("U"."NAME"='SYS')
20 - access("L"."OWNER#"="U"."USER#")
42 rows selected.
Como se mencionó arriba, usar EXPLAIN PLAN FOR es la manera mas fácil para tener una idea sobre el plan de ejecución de una sentencia SQL sin tener que ejecutarla, pero podría no ser el mismo plan usado para ejecutarla porque la selección del plan tiene lugar al momento de la ejecución y depende de muchos factores como carga, exactitud de las estadísticas y hints por nombrar algunos factores. De hecho, podrías estar más interesado en saber el plan de ejecución de un SQL corriendo más que en el plan de ejecución de una sentencia SQL no ejecutada aún; si ese es el caso puedes obtenerlo de esta forma:
SQL> column SID format a6
SQL> column USERNAME format a10
SQL> column PROGRAM format a50
SQL> column EVENT format a30
SQL> select to_char(s.sid) AS sid, s.username, s.status, s.program, s.event
FROM v$session s JOIN v$process p ON (p.addr = s.paddr)
WHERE s.username = 'SYS' ORDER BY 1;
SID USERNAME STATUS PROGRAM EVENT
---- ---------- ---------- ------------------------------------------------ ------------------------------
1065 SYS ACTIVE sqlplus@myracnode (TNS V1-V3) SQL*Net message to client
1087 SYS ACTIVE sqlplus@myracnode (TNS V1-V3) db file scattered read
1259 SYS INACTIVE racgimon@myracnode (TNS V1-V3) SQL*Net message from client
1275 SYS INACTIVE racgimon@myracnode (TNS V1-V3) SQL*Net message from client
1276 SYS ACTIVE racgimon@myracnode (TNS V1-V3) Streams AQ: waiting for messag
es in the queue
1302 SYS INACTIVE racgimon@myracnode (TNS V1-V3) SQL*Net message from client
6 rows selected.
SQL> SELECT p.plan_table_output FROM v$session s, table(dbms_xplan.display_cursor(s.sql_id, s.sql_child_number)) p
where s.sid = 1087;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8qdgqzzzgk4as, child number 0
-------------------------------------
select count(*) from my_big_table where some_date < '25-JAN-08'
Plan hash value: 857495206
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2678 (100)| |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
|* 2 | TABLE ACCESS FULL| MY_BIG_TABLE | 101K| 1382K| 2678 (27)| 00:00:27 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("B"."SOME_DATE"<'25-JAN-08'))
21 rows selected.
Como puedes ver, primero debes identificar el SID de la sesión de la sentencia SQL en ejecución sobre la que estás interesado, y con la función dbms_xplan.display_cursor puedes obtener la sentencia y el plan de ejecución actualmente usado.
Por otro lado, si quieres revisar el plan de ejecución de cierta sentencia que fue ejecutada recientemente puedes intentar encontrar su SQL_ID en v$sqlarea; recuerda que SQL_TEXT es sensible a las mayúsculas:
SQL> column SQL_TEXT format a80
SQL> select SQL_ID, SQL_TEXT from v$sqlarea where upper(SQL_TEXT) like '%DBA_OBJECTS%';
SQL_ID SQL_TEXT
------------- --------------------------------------------------------------------------------
grq6tgwtun603 select SQL_ID, SQL_TEXT from v$sqlarea where upper(SQL_TEXT) like '%DBA_OBJECTS%
'
dfg3s1x621u6b select /*+ PARALLEL(b,4) */ count(*) from dba_objects b where OWNER='SYS'
cgyvau8fub4rq /* OracleOEM */ SELECT P.OBJECT_NAME,O.OBJECT_TYPE,P.OBJECT_OWNER,P.POLICY_NAM
E,P.POLICY_GROUP,P.ENABLE FROM DBA_POLICIES P, DBA_OBJECTS O WHERE P.OBJECT_NAME
=O.OBJECT_NAME
SQL> SELECT p.plan_table_output FROM table(dbms_xplan.display_cursor('dfg3s1x621u6b')) p;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID dfg3s1x621u6b, child number 0
-------------------------------------
select /*+ PARALLEL(b,4) */ count(*) from dba_objects b where OWNER='SYS'
Plan hash value: 3694869755
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 25 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | 17 | | | | | |
| 2 | VIEW | DBA_OBJECTS | 104 | 1768 | 25 (0)| 00:00:01 | | | |
| 3 | UNION-ALL | | | | | | | | |
|* 4 | FILTER | | | | | | | | |
| 5 | PX COORDINATOR | | | | | | | | |
| 6 | PX SEND QC (RANDOM) | :TQ10001 | 122 | 10736 | 24 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 7 | NESTED LOOPS | | 122 | 10736 | 24 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 9 | PX RECEIVE | | | | | | Q1,01 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10000 | | | | | | S->P | BROADCAST |
| 11 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |
|* 12 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | |
| 13 | PX BLOCK ITERATOR | | 122 | 9028 | 23 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 14 | TABLE ACCESS FULL | OBJ$ | 122 | 9028 | 23 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 15 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 2 (0)| 00:00:01 | | | |
|* 16 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 | | | |
| 17 | NESTED LOOPS | | 1 | 27 | 1 (0)| 00:00:01 | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |
|* 19 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | |
|* 20 | INDEX RANGE SCAN | I_LINK1 | 1 | 13 | 0 (0)| | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter((("O"."TYPE#"1 AND "O"."TYPE#"10) OR ("O"."TYPE#"=1 AND =1)))
12 - access("U"."NAME"='SYS')
14 - access(:Z>=:Z AND :Z<=:Z)
filter(("O"."NAME"'_NEXT_OBJECT' AND "O"."NAME"'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
BITAND("O"."FLAGS",128)=0 AND "O"."OWNER#"="U"."USER#"))
15 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
"I"."TYPE#"=9))
16 - access("I"."OBJ#"=:B1)
19 - access("U"."NAME"='SYS')
20 - access("L"."OWNER#"="U"."USER#")
46 rows selected.
Además, si estás ejecutando las sentencias SQL de una por una puedes obtener el plan de ejecución llamando dbms_xplan.display_cursor sin parámetros inmediatamente después de la sentencia SQL a analizar:
SQL> select /*+ PARALLEL(b,4) */ count(*) from dba_objects b where OWNER='SYS';
COUNT(*)
----------
7107
SQL> SELECT p.plan_table_output FROM table(dbms_xplan.display_cursor()) p;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID dfg3s1x621u6b, child number 0
-------------------------------------
select /*+ PARALLEL(b,4) */ count(*) from dba_objects b where OWNER='SYS'
Plan hash value: 3694869755
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 25 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | 17 | | | | | |
| 2 | VIEW | DBA_OBJECTS | 104 | 1768 | 25 (0)| 00:00:01 | | | |
| 3 | UNION-ALL | | | | | | | | |
|* 4 | FILTER | | | | | | | | |
| 5 | PX COORDINATOR | | | | | | | | |
| 6 | PX SEND QC (RANDOM) | :TQ10001 | 122 | 10736 | 24 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 7 | NESTED LOOPS | | 122 | 10736 | 24 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 9 | PX RECEIVE | | | | | | Q1,01 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10000 | | | | | | S->P | BROADCAST |
| 11 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |
|* 12 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | |
| 13 | PX BLOCK ITERATOR | | 122 | 9028 | 23 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 14 | TABLE ACCESS FULL | OBJ$ | 122 | 9028 | 23 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 15 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 2 (0)| 00:00:01 | | | |
|* 16 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 | | | |
| 17 | NESTED LOOPS | | 1 | 27 | 1 (0)| 00:00:01 | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |
|* 19 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | |
|* 20 | INDEX RANGE SCAN | I_LINK1 | 1 | 13 | 0 (0)| | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter((("O"."TYPE#"1 AND "O"."TYPE#"10) OR ("O"."TYPE#"=1 AND =1)))
12 - access("U"."NAME"='SYS')
14 - access(:Z>=:Z AND :Z<=:Z)
filter(("O"."NAME"'_NEXT_OBJECT' AND "O"."NAME"'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
BITAND("O"."FLAGS",128)=0 AND "O"."OWNER#"="U"."USER#"))
15 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
"I"."TYPE#"=9))
16 - access("I"."OBJ#"=:B1)
19 - access("U"."NAME"='SYS')
20 - access("L"."OWNER#"="U"."USER#")
46 rows selected.
Esto puede ser útil aún si estás depurando un script SQL, pero si necesitas obtener planes de ejecución de sentencias SQL ubicadas en un script PL/SQL entonces necesitas imprimir el plan de ejecución línea por línea:
SQL> SET TERMOUT ON
SQL> SET PAGESIZE 0
SQL> SET SERVEROUTPUT ON
SQL> SET LINESIZE 200
SQL> declare
v_count number;
v_plan varchar2(200);
CURSOR c1 IS SELECT p.plan_table_output FROM table(dbms_xplan.display_cursor()) p;
begin
select /*+ PARALLEL(b,4) */ count(*) into v_count from dba_objects b where OWNER='SYS';
dbms_output.put_line ('Number of rows: '||v_count);
OPEN c1;
LOOP
FETCH c1 INTO v_plan;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line (v_plan);
END LOOP;
CLOSE c1;
end;
/
Number of rows: 7107
SQL_ID dfg3s1x621u6b, child number 0
-------------------------------------
select /*+ PARALLEL(b,4) */ count(*) from dba_objects b where OWNER='SYS'
Plan hash value: 3694869755
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 25 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | 17 | | | | | |
| 2 | VIEW | DBA_OBJECTS | 104 | 1768 | 25 (0)| 00:00:01 | | | |
| 3 | UNION-ALL | | | | | | | | |
|* 4 | FILTER | | | | | | | | |
| 5 | PX COORDINATOR | | | | | | | | |
| 6 | PX SEND QC (RANDOM) | :TQ10001 | 122 | 10736 | 24 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 7 | NESTED LOOPS | | 122 | 10736 | 24 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 9 | PX RECEIVE | | | | | | Q1,01 | PCWP | |
| 10 | PX SEND BROADCAST | :TQ10000 | | | | | | S->P | BROADCAST |
| 11 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |
|* 12 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | |
| 13 | PX BLOCK ITERATOR | | 122 | 9028 | 23 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 14 | TABLE ACCESS FULL | OBJ$ | 122 | 9028 | 23 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 15 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 2 (0)| 00:00:01 | | | |
|* 16 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 | | | |
| 17 | NESTED LOOPS | | 1 | 27 | 1 (0)| 00:00:01 | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |
|* 19 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | |
|* 20 | INDEX RANGE SCAN | I_LINK1 | 1 | 13 | 0 (0)| | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter((("O"."TYPE#"1 AND "O"."TYPE#"10) OR ("O"."TYPE#"=1 AND =1)))
12 - access("U"."NAME"='SYS')
14 - access(:Z>=:Z AND :Z<=:Z)
filter(("O"."NAME"'_NEXT_OBJECT' AND "O"."NAME"'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND
BITAND("O"."FLAGS",128)=0 AND "O"."OWNER#"="U"."USER#"))
15 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR
"I"."TYPE#"=9))
16 - access("I"."OBJ#"=:B1)
19 - access("U"."NAME"='SYS')
20 - access("L"."OWNER#"="U"."USER#")
PL/SQL procedure successfully completed.
Finalmente, podría ser útil saber el tiempo de ejecucicón real de las sentencias que estás ejecutando, y puedes saber eso con la sentencia set timing on:
SQL> set timing on
SQL> select /*+ PARALLEL(b,4) */ count(*) from dba_objects b where OWNER='SYS';
COUNT(*)
----------
7092
Elapsed: 00:00:00.46
Más información:
Understanding Explain Plan
DBMS_XPLAN : Display Oracle Execution Plans
Using EXPLAIN PLAN
No hay comentarios.:
Publicar un comentario