Generate/Customize Explain Plan
0. Creating a Plan Table
1. Generate Explain Plan using SQL Developer
2. Generate Explain Plan using Autotrace in SQL Developer
3. Generate Explain Plan using SQL*Plus
3.1: Running EXPLAIN PLAN
3.2: Using utlxpls.sql
3.3: Using EXPLAIN PLAN with the STATEMENT ID Clause
3.4: Customizing PLAN_TABLE Output
3.5: Using EXPLAIN PLAN with the INTO Clause
3.6: Outline Hit
3.7: Advanced Format
5. Using DBMS_XPLAN.DISPLAY_CURSOR
5.1: BASIC Format
5.2: SERIAL Format
5.3: TYPICAL Format
5.4: ALL Format
5.5: ALLSTATS Format
5.6: ALLSTATS LAST Format
5.7: Enabling Extra Output
5.8: Removing Output Sections
5.9: Advanced Format
5.10: TYPICAL Format after immediate sql executed
6: Using DBMS_XPLAN.DISPLAY_AWR (From AWR snaps)
0. Creating a Plan Table
The first thing you will need to do is make sure you have a table called PLAN_TABLE available in your schema. The following script will create it for you if you don't have it already @$ORACLE_HOME/rdbms/admin/utlxplan.sql
Method 1: Generate Explain Plan using SQL Developer
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1; (Press F10)
Method 2: Generate Explain Plan using Autotrace in SQL Developer
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1; (Press F6)
Method 3: Generate Explain Plan using SQL*Plus
Method 3.1: Running EXPLAIN PLAN
— saves the execution plan in the PLAN_TABLE.
SET TIMING ON
set echo on
set linesize 200 pagesize 1000
explain plan for
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;
select * from table(dbms_xplan.display);
output
=======
SQL> set echo on SQL> set linesize 200 pagesize 1000 SQL> explain plan for select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------- Plan hash value: 3253233075 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 1928 (99)| 00:00:24 | | 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | HASH JOIN | | 400M| 2288M| 1928 (99)| 00:00:24 | |* 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 | |* 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."C"="T2"."C") 3 - filter("T1"."C"=1) 4 - filter("T2"."C"=1) 18 rows selected. SQL>
SET TIMING ON set echo on set linesize 200 pagesize 1000 explain plan for select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1; @$ORACLE_HOME/rdbms/admin/utlxpls.sql
output
======
SQL> set echo on SQL> set linesize 200 pagesize 1000 SQL> explain plan for select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1; Explained. SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql .. .. SQL> set markup html preformat on SQL> SQL> Rem SQL> Rem Use the display table function from the dbms_xplan package to display the last SQL> Rem explain plan. Force serial option for backward compatibility SQL> Rem SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------- Plan hash value: 3253233075 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 1928 (99)| 00:00:24 | | 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | HASH JOIN | | 400M| 2288M| 1928 (99)| 00:00:24 | |* 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 | |* 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."C"="T2"."C") 3 - filter("T1"."C"=1) 4 - filter("T2"."C"=1) 18 rows selected. SQL>
Method 3.3: Using EXPLAIN PLAN with the STATEMENT ID Clause
SET TIMING ON set echo on set linesize 200 pagesize 1000 EXPLAIN PLAN SET STATEMENT_ID = 'MY_PLAN' FOR select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1; SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'MY_PLAN','TYPICAL'));
output
=======
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'MY_PLAN' FOR select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1; 2 Explained. SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'MY_PLAN','TYPICAL')); 2 PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------- Plan hash value: 3253233075 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 1928 (99)| 00:00:24 | | 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | HASH JOIN | | 400M| 2288M| 1928 (99)| 00:00:24 | |* 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 | |* 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."C"="T2"."C") 3 - filter("T1"."C"=1) 4 - filter("T2"."C"=1) 18 rows selected. SQL>
Method 3.4: Customizing PLAN_TABLE Output
SET TIMING ON set echo on set linesize 200 pagesize 1000 EXPLAIN PLAN SET STATEMENT_ID = 'MY_PLAN' FOR select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1; col Plan for a70 SELECT cardinality "Rows", lpad(' ',level-1)||operation||' '||options||' '||object_name "Plan" FROM PLAN_TABLE CONNECT BY prior id = parent_id AND prior statement_id = statement_id START WITH id = 0 AND statement_id = 'MY_PLAN' ORDER BY id;
Output
=======
SQL> SELECT cardinality "Rows", lpad(' ',level-1)||operation||' '||options||' '||object_name "Plan" 2 3 FROM PLAN_TABLE 4 CONNECT BY prior id = parent_id 5 AND prior statement_id = statement_id 6 START WITH id = 0 7 AND statement_id = 'MY_PLAN' 8 ORDER BY id; Rows Plan ---------- -------------------------------------------------------------- 1 SELECT STATEMENT 1 SORT AGGREGATE 400000000 HASH JOIN 20000 INDEX FAST FULL SCAN TEST_C_INDX 20000 INDEX FAST FULL SCAN TEST_C_INDX SQL> (OR) SQL> EXPLAIN PLAN SET STATEMENT_ID = 'MY_PLAN' FOR select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1; Explained. Elapsed: 00:00:00.01 SQL> COL PLAN FORMAT A80 SELECT LPAD( ' ', 2 * ( LEVEL - 1 ) ) || SQL> 2 OPERATION || ' ' || 3 OPTIONS || ' ' || 4 OBJECT_NAME || ' ' || 5 OBJECT_TYPE PLAN, 6 COST 7 FROM PLAN_TABLE 8 WHERE STATEMENT_ID = 'MY_PLAN' 9 CONNECT BY PRIOR ID = PARENT_ID 10 AND PRIOR STATEMENT_ID = 'MY_PLAN' 11 START WITH ID =1; PLAN COST -------------------------------------------- -------------- SORT AGGREGATE HASH JOIN 1928 INDEX FAST FULL SCAN TEST_C_INDX INDEX 12 INDEX FAST FULL SCAN TEST_C_INDX INDEX 12 Elapsed: 00:00:00.00 SQL>
Method 3.5: Using EXPLAIN PLAN with the INTO Clause
SET TIMING ON set echo on set linesize 200 pagesize 1000 explain plan into PLAN_TABLE for select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1; select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'TYPICAL'));
Output
=======
SQL> explain plan into PLAN_TABLE for select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1; 2 Explained. Elapsed: 00:00:00.01 SQL> select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'TYPICAL')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------- Plan hash value: 3253233075 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 1928 (99)| 00:00:24 | | 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | HASH JOIN | | 400M| 2288M| 1928 (99)| 00:00:24 | |* 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 | |* 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."C"="T2"."C") 3 - filter("T1"."C"=1) 4 - filter("T2"."C"=1) 18 rows selected. Elapsed: 00:00:00.01 SQL>
set echo on
set linesize 200 pagesize 1000
explain plan into PLAN_TABLE for
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;
select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'OUTLINE'));
Output
=======
SQL> select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'OUTLINE'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 3253233075
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1928 (99)| 00:00:24 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | HASH JOIN | | 400M| 2288M| 1928 (99)| 00:00:24 |
|* 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
INDEX_FFS(@"SEL$1" "T2"@"SEL$1" ("TEST"."C"))
INDEX_FFS(@"SEL$1" "T1"@"SEL$1" ("TEST"."C"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C"="T2"."C")
3 - filter("T1"."C"=1)
4 - filter("T2"."C"=1)
35 rows selected.
SQL>
set echo on
set linesize 200 pagesize 1000
explain plan into PLAN_TABLE for
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;
select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'Advanced'));
Output
=======
SQL> select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'Advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3253233075
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 1928 (99)| 00:00:24 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | HASH JOIN | | 400M| 2288M| 1928 (99)| 00:00:24 |
|* 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T1@SEL$1
4 - SEL$1 / T2@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$1" "T2"@"SEL$1")
LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
INDEX_FFS(@"SEL$1" "T2"@"SEL$1" ("TEST"."C"))
INDEX_FFS(@"SEL$1" "T1"@"SEL$1" ("TEST"."C"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.3')
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C"="T2"."C")
3 - filter("T1"."C"=1)
4 - filter("T2"."C"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
2 - (#keys=1)
3 - "T1"."C"[NUMBER,22]
4 - "T2"."C"[NUMBER,22]
50 rows selected.
SQL>
SQL> SET AUTOTRACE TRACEONLY SQL> select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1; Elapsed: 00:02:04.11 Execution Plan ---------------------------------------------------------- Plan hash value: 3253233075 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 1928 (99)| 00:00:24 | | 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | HASH JOIN | | 400M| 2288M| 1928 (99)| 00:00:24 | |* 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 | |* 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."C"="T2"."C") 3 - filter("T1"."C"=1) 4 - filter("T2"."C"=1) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 90 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> -------------------------------------- set autotrace off set autotrace on set autotrace traceonly set autotrace on explain set autotrace on statistics set autotrace on explain statistics set autotrace traceonly explain set autotrace traceonly statistics set autotrace traceonly explain statistics set autotrace off explain set autotrace off statistics set autotrace off explain statistics ----------------------------------------
5. Using DBMS_XPLAN.DISPLAY_CURSOR
Method 5.1: BASIC Format
SQL> SELECT SQL_ID, CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE 'select count(*) from test t1%'; 2 SQL_ID CHILD_NUMBER ------------- ------------ dkz7v96ym42c6 0 SQL> set echo on set linesize 200 pagesize 1000 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'BASIC'));
Output
=======
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'BASIC'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Plan hash value: 3253233075
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | HASH JOIN | |
| 3 | INDEX FAST FULL SCAN| TEST_C_INDX |
| 4 | INDEX FAST FULL SCAN| TEST_C_INDX |
----------------------------------------------
16 rows selected.
SQL>
set echo on
set linesize 200 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'SERIAL'));
Output
=======
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'SERIAL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID dkz7v96ym42c6, child number 0
-------------------------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Plan hash value: 3253233075
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1928 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | HASH JOIN | | 400M| 2288M| 1928 (99)| 00:00:24 |
|* 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C"="T2"."C")
3 - filter("T1"."C"=1)
4 - filter("T2"."C"=1)
23 rows selected.
SQL>
set echo on
set linesize 200 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'TYPICAL'));
Output:
=======
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'TYPICAL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID dkz7v96ym42c6, child number 0
-------------------------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Plan hash value: 3253233075
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1928 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | HASH JOIN | | 400M| 2288M| 1928 (99)| 00:00:24 |
|* 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C"="T2"."C")
3 - filter("T1"."C"=1)
4 - filter("T2"."C"=1)
23 rows selected.
SQL>
set echo on
set linesize 200 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALL'));
Output:
=======
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALL'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID dkz7v96ym42c6, child number 0
-------------------------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Plan hash value: 3253233075
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1928 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | HASH JOIN | | 400M| 2288M| 1928 (99)| 00:00:24 |
|* 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T1@SEL$1
4 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C"="T2"."C")
3 - filter("T1"."C"=1)
4 - filter("T2"."C"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
2 - (#keys=1)
3 - "T1"."C"[NUMBER,22]
4 - "T2"."C"[NUMBER,22]
38 rows selected.
SQL>
set echo on
set linesize 200 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALLSTATS'));
Output
=======
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALLSTATS'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID dkz7v96ym42c6, child number 0
-------------------------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Plan hash value: 3253233075
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | | 7 |00:13:57.84 | 630 | | | |
| 1 | SORT AGGREGATE | | 9 | 1 | 7 |00:13:57.84 | 630 | | | |
|* 2 | HASH JOIN | | 9 | 400M| 2952M|00:08:55.96 | 745 | 1643K| 1643K| 9/0/0|
|* 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 9 | 20000 | 180K|00:00:00.04 | 405 | | | |
|* 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 9 | 20000 | 147K|00:00:00.13 | 340 | | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C"="T2"."C")
3 - filter("T1"."C"=1)
4 - filter("T2"."C"=1)
23 rows selected.
SQL>
Method 5.6: ALLSTATS LAST Format
set echo on
set linesize 200 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALLSTATS LAST'));
Output
=======
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID dkz7v96ym42c6, child number 0
-------------------------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Plan hash value: 3253233075
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 0 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 2 | HASH JOIN | | 1 | 400M| 92M|00:00:16.84 | 59 | 1643K| 1643K| 2040K (0)|
|* 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 1 | 20000 | 20000 |00:00:00.01 | 45 | | | |
|* 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 1 | 20000 | 4608 |00:00:00.01 | 14 | | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C"="T2"."C")
3 - filter("T1"."C"=1)
4 - filter("T2"."C"=1)
23 rows selected.
SQL>
Method 5.7: Enabling Extra Output
set echo on
set linesize 200 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALLSTATS LAST +PEEKED_BINDS +PROJECTION +ALIAS +PREDICATE +COST +BYTES'));
Output
======
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALLSTATS LAST +PEEKED_BINDS +PROJECTION +ALIAS +PREDICATE +COST +BYTES'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dkz7v96ym42c6, child number 0
-------------------------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Plan hash value: 3253233075
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1928 (100)| 0 |00:00:00.01 | 0 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 6 | | 0 |00:00:00.01 | 0 | | | |
|* 2 | HASH JOIN | | 1 | 400M| 2288M| 1928 (99)| 92M|00:00:16.84 | 59 | 1643K| 1643K| 2040K (0)|
|* 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 1 | 20000 | 60000 | 12 (0)| 20000 |00:00:00.01 | 45 | | | |
|* 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 1 | 20000 | 60000 | 12 (0)| 4608 |00:00:00.01 | 14 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / T1@SEL$1
4 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C"="T2"."C")
3 - filter("T1"."C"=1)
4 - filter("T2"."C"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
2 - (#keys=1)
3 - "T1"."C"[NUMBER,22]
4 - "T2"."C"[NUMBER,22]
38 rows selected.
SQL>
Method 5.8: Removing Output Sections
set echo on
set linesize 200 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALLSTATS LAST -NOTE -ROWS -PREDICATE'));
Output
======
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALLSTATS LAST -NOTE -ROWS -PREDICATE'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID dkz7v96ym42c6, child number 0
-------------------------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Plan hash value: 3253233075
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 0 |00:00:00.01 | 0 | | | |
| 1 | SORT AGGREGATE | | 1 | 0 |00:00:00.01 | 0 | | | |
| 2 | HASH JOIN | | 1 | 92M|00:00:16.84 | 59 | 1643K| 1643K| 2040K (0)|
| 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 1 | 20000 |00:00:00.01 | 45 | | | |
| 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 1 | 4608 |00:00:00.01 | 14 | | | |
------------------------------------------------------------------------------------------------------------------
16 rows selected.
SQL>
set echo on
set linesize 200 pagesize 1000
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'Advanced'));
Output
=======
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'Advanced')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------- SQL_ID dkz7v96ym42c6, child number 0 ------------------------------------- select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1 Plan hash value: 3253233075 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1928 (100)| | | 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | HASH JOIN | | 400M| 2288M| 1928 (99)| 00:00:24 | |* 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 | |* 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$1 / T1@SEL$1 4 - SEL$1 / T2@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') ALL_ROWS OUTLINE_LEAF(@"SEL$1") INDEX_FFS(@"SEL$1" "T1"@"SEL$1" ("TEST"."C")) INDEX_FFS(@"SEL$1" "T2"@"SEL$1" ("TEST"."C")) LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1") USE_HASH(@"SEL$1" "T2"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."C"="T2"."C") 3 - filter("T1"."C"=1) 4 - filter("T2"."C"=1) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] 2 - (#keys=1) 3 - "T1"."C"[NUMBER,22] 4 - "T2"."C"[NUMBER,22] 55 rows selected. SQL>
Method 5.10: TYPICAL Format after immediate sql executed
The typical way to display an execution plan for a SQL statement that was just executed
We could have also been set to NULL to produce the same result
set echo on set linesize 200 pagesize 1000 select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
Output
=======
SQL> set echo on SQL> set linesize 200 pagesize 1000 SQL> select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1; COUNT(*) ---------- 400000000 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------- SQL_ID dkz7v96ym42c6, child number 1 ------------------------------------- select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1 Plan hash value: 3253233075 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1928 (100)| | | 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | HASH JOIN | | 400M| 2288M| 1928 (99)| 00:00:24 | |* 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 | |* 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."C"="T2"."C") 3 - filter("T1"."C"=1) 4 - filter("T2"."C"=1) 23 rows selected. SQL> ----- set echo on set linesize 200 pagesize 1000 ALTER SESSION SET STATISTICS_LEVEL='ALL'; select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')); select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'Advanced')); ----
Method 6: Using DBMS_XPLAN.DISPLAY_AWR (From AWR snaps)
set echo on
set linesize 200 pagesize 1000
select * from table(dbms_xplan.display_awr('dkz7v96ym42c6'));
Output
=======
SQL> select * from table(dbms_xplan.display_awr('dkz7v96ym42c6'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID dkz7v96ym42c6
--------------------
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1
Plan hash value: 3253233075
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1928 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | HASH JOIN | | 400M| 2288M| 1928 (99)| 00:00:24 |
| 3 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| TEST_C_INDX | 20000 | 60000 | 12 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
16 rows selected.
SQL>
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/