Oracle Database Commands and Practical Examples: PT - Displaying Execution Plan using dbms_xplan.display_cursor BASIC | ALL| TYPICAL

Scenario Preview:

Query output using dbms_xplan.display_cursor

Standard options:

BASIC Display minimum info.
TYPICAL Display most relevant info.
SERIAL Do not display parallel info.
ALL Display maximum user level info.
ADAPTIVE Display Optimizer Adaptive info.

Keywords Option :

ROWS Show the number of rows
PREDICATE Display predicate section
COST Show the cost
MEMSTATS Display memory management stats
NOTE Shot the Note section
IOSTATS Show IO Statistics

SQL> SELECT DNAME, COUNT(*) FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO GROUP BY DNAME ORDER BY COUNT(*) DESC;

DNAME COUNT(*)
---------------------- ----------
Sales 699
Administration 113
IT 27
Operatio 23
Human Resources 10
Finance 7

6 rows selected.

SQL> set linesize 200
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fyvyywyh1uyu0, child number 0
-------------------------------------
SELECT DNAME, COUNT(*) FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO
GROUP BY DNAME ORDER BY COUNT(*) DESC

Plan hash value: 1852086721

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT ORDER BY | | 7 | 112 | 7 (43)| 00:00:01 |
| 2 | HASH GROUP BY | | 7 | 112 | 7 (43)| 00:00:01 |
| 3 | MERGE JOIN | | 879 | 14064 | 5 (20)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 7 | 91 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | DEPT_PK | 7 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 879 | 2637 | 3 (34)| 00:00:01 |
| 7 | INDEX FAST FULL SCAN | DEPT_IX | 879 | 2637 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

6 - access("E"."DEPT_NO"="D"."DEPT_NO")
filter("E"."DEPT_NO"="D"."DEPT_NO")

26 rows selected.

SQL> SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT DNAME, COUNT(*)%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
fyvyywyh1uyu0
SELECT DNAME, COUNT(*) FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO GROUP BY DNAME ORDER BY COUNT(*) DESC

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&ENTER_SQL_ID'));
Enter value for enter_sql_id: fyvyywyh1uyu0
old 1: SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&ENTER_SQL_ID'))
new 1: SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('fyvyywyh1uyu0'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fyvyywyh1uyu0, child number 0
-------------------------------------
SELECT DNAME, COUNT(*) FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO
GROUP BY DNAME ORDER BY COUNT(*) DESC

Plan hash value: 1852086721

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT ORDER BY | | 7 | 112 | 7 (43)| 00:00:01 |
| 2 | HASH GROUP BY | | 7 | 112 | 7 (43)| 00:00:01 |
| 3 | MERGE JOIN | | 879 | 14064 | 5 (20)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 7 | 91 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | DEPT_PK | 7 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 879 | 2637 | 3 (34)| 00:00:01 |
| 7 | INDEX FAST FULL SCAN | DEPT_IX | 879 | 2637 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

6 - access("E"."DEPT_NO"="D"."DEPT_NO")
filter("E"."DEPT_NO"="D"."DEPT_NO")

26 rows selected.

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('fyvyywyh1uyu0'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fyvyywyh1uyu0, child number 0
-------------------------------------
SELECT DNAME, COUNT(*) FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO
GROUP BY DNAME ORDER BY COUNT(*) DESC

Plan hash value: 1852086721

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT ORDER BY | | 7 | 112 | 7 (43)| 00:00:01 |
| 2 | HASH GROUP BY | | 7 | 112 | 7 (43)| 00:00:01 |
| 3 | MERGE JOIN | | 879 | 14064 | 5 (20)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 7 | 91 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | DEPT_PK | 7 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 879 | 2637 | 3 (34)| 00:00:01 |
| 7 | INDEX FAST FULL SCAN | DEPT_IX | 879 | 2637 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

6 - access("E"."DEPT_NO"="D"."DEPT_NO")
filter("E"."DEPT_NO"="D"."DEPT_NO")

26 rows selected.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>'&ENTER_SQL_ID', FORMAT => 'ALL'));
Enter value for enter_sql_id: fyvyywyh1uyu0
old 1: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>'&ENTER_SQL_ID', FORMAT => 'ALL'))
new 1: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>'fyvyywyh1uyu0', FORMAT => 'ALL'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fyvyywyh1uyu0, child number 0
-------------------------------------
SELECT DNAME, COUNT(*) FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO
GROUP BY DNAME ORDER BY COUNT(*) DESC

Plan hash value: 1852086721

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT ORDER BY | | 7 | 112 | 7 (43)| 00:00:01 |
| 2 | HASH GROUP BY | | 7 | 112 | 7 (43)| 00:00:01 |
| 3 | MERGE JOIN | | 879 | 14064 | 5 (20)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 7 | 91 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | DEPT_PK | 7 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 879 | 2637 | 3 (34)| 00:00:01 |
| 7 | INDEX FAST FULL SCAN | DEPT_IX | 879 | 2637 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1 - SEL$1
4 - SEL$1 / D@SEL$1
5 - SEL$1 / D@SEL$1
7 - SEL$1 / E@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("E"."DEPT_NO"="D"."DEPT_NO")
filter("E"."DEPT_NO"="D"."DEPT_NO")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - (#keys=1) COUNT(*)[22], "DNAME"[VARCHAR2,22]
2 - "DNAME"[VARCHAR2,22], COUNT(*)[22]
3 - "DNAME"[VARCHAR2,22]
4 - "D"."DEPT_NO"[NUMBER,22], "DNAME"[VARCHAR2,22]
5 - "D".ROWID[ROWID,10], "D"."DEPT_NO"[NUMBER,22]
6 - (#keys=1) "E"."DEPT_NO"[NUMBER,22]
7 - "E"."DEPT_NO"[NUMBER,22]

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

45 rows selected.

SQL>

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>'fyvyywyh1uyu0', FORMAT => 'BASIC'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT DNAME, COUNT(*) FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO
GROUP BY DNAME ORDER BY COUNT(*) DESC

Plan hash value: 1852086721

--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT ORDER BY | |
| 2 | HASH GROUP BY | |
| 3 | MERGE JOIN | |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT |
| 5 | INDEX FULL SCAN | DEPT_PK |
| 6 | SORT JOIN | |
| 7 | INDEX FAST FULL SCAN | DEPT_IX |
--------------------------------------------------

20 rows selected.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>'fyvyywyh1uyu0', FORMAT => 'TYPICAL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fyvyywyh1uyu0, child number 0
-------------------------------------
SELECT DNAME, COUNT(*) FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO
GROUP BY DNAME ORDER BY COUNT(*) DESC

Plan hash value: 1852086721

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT ORDER BY | | 7 | 112 | 7 (43)| 00:00:01 |
| 2 | HASH GROUP BY | | 7 | 112 | 7 (43)| 00:00:01 |
| 3 | MERGE JOIN | | 879 | 14064 | 5 (20)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 7 | 91 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | DEPT_PK | 7 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 879 | 2637 | 3 (34)| 00:00:01 |
| 7 | INDEX FAST FULL SCAN | DEPT_IX | 879 | 2637 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

6 - access("E"."DEPT_NO"="D"."DEPT_NO")
filter("E"."DEPT_NO"="D"."DEPT_NO")

26 rows selected.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>'fyvyywyh1uyu0', FORMAT => 'SERIAL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fyvyywyh1uyu0, child number 0
-------------------------------------
SELECT DNAME, COUNT(*) FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO
GROUP BY DNAME ORDER BY COUNT(*) DESC

Plan hash value: 1852086721

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT ORDER BY | | 7 | 112 | 7 (43)| 00:00:01 |
| 2 | HASH GROUP BY | | 7 | 112 | 7 (43)| 00:00:01 |
| 3 | MERGE JOIN | | 879 | 14064 | 5 (20)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 7 | 91 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | DEPT_PK | 7 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 879 | 2637 | 3 (34)| 00:00:01 |
| 7 | INDEX FAST FULL SCAN | DEPT_IX | 879 | 2637 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

6 - access("E"."DEPT_NO"="D"."DEPT_NO")
filter("E"."DEPT_NO"="D"."DEPT_NO")

26 rows selected.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID=>'fyvyywyh1uyu0', FORMAT => 'ADAPTIVE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fyvyywyh1uyu0, child number 0
-------------------------------------
SELECT DNAME, COUNT(*) FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO
GROUP BY DNAME ORDER BY COUNT(*) DESC

Plan hash value: 1852086721

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SORT ORDER BY | | 7 | 112 | 7 (43)| 00:00:01 |
| 2 | HASH GROUP BY | | 7 | 112 | 7 (43)| 00:00:01 |
| 3 | MERGE JOIN | | 879 | 14064 | 5 (20)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 7 | 91 | 2 (0)| 00:00:01 |
| 5 | INDEX FULL SCAN | DEPT_PK | 7 | | 1 (0)| 00:00:01 |
|* 6 | SORT JOIN | | 879 | 2637 | 3 (34)| 00:00:01 |
| 7 | INDEX FAST FULL SCAN | DEPT_IX | 879 | 2637 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

6 - access("E"."DEPT_NO"="D"."DEPT_NO")
filter("E"."DEPT_NO"="D"."DEPT_NO")

26 rows selected.

SQL>

#############################################################################

ROWS Show the number of rows

PREDICATE Display predicate section

COST Show the cost

MEMSTATS Display memory management stats

NOTE Shot the Note section

IOSTATS Show IO Statistics

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('fyvyywyh1uyu0',NULL,'ROWS'));

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('fyvyywyh1uyu0',NULL,'PREDICATE'));

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('fyvyywyh1uyu0',NULL,'COST'));

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('fyvyywyh1uyu0',NULL,'MEMSTATS'));

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('fyvyywyh1uyu0',NULL,'NOTE'));

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('fyvyywyh1uyu0', NULL,'IOSTATS'));

SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('fyvyywyh1uyu0',NULL,'ROWS'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID fyvyywyh1uyu0, child number 0

-------------------------------------

SELECT DNAME, COUNT(*) FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO

GROUP BY DNAME ORDER BY COUNT(*) DESC

Plan hash value: 1852086721

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 7 (100)| |

PLAN_TABLE_OUTPUT

| 1 | SORT ORDER BY | | 7 | 112 | 7 (43)| 00:00:01 |

| 2 | HASH GROUP BY | | 7 | 112 | 7 (43)| 00:00:01 |

| 3 | MERGE JOIN | | 879 | 14064 | 5 (20)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 7 | 91 | 2 (0)| 00:00:01 |

| 5 | INDEX FULL SCAN | DEPT_PK | 7 | | 1 (0)| 00:00:01 |

|* 6 | SORT JOIN | | 879 | 2637 | 3 (34)| 00:00:01 |

| 7 | INDEX FAST FULL SCAN | DEPT_IX | 879 | 2637 | 2 (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('fyvyywyh1uyu0',NULL,'PREDICATE'));

PLAN_TABLE_OUTPUT

SQL_ID fyvyywyh1uyu0, child number 0

-------------------------------------

SELECT DNAME, COUNT(*) FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO

GROUP BY DNAME ORDER BY COUNT(*) DESC

Plan hash value: 1852086721

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 7 (100)| |

PLAN_TABLE_OUTPUT

| 1 | SORT ORDER BY | | 7 | 112 | 7 (43)| 00:00:01 |

| 2 | HASH GROUP BY | | 7 | 112 | 7 (43)| 00:00:01 |

| 3 | MERGE JOIN | | 879 | 14064 | 5 (20)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 7 | 91 | 2 (0)| 00:00:01 |

| 5 | INDEX FULL SCAN | DEPT_PK | 7 | | 1 (0)| 00:00:01 |

|* 6 | SORT JOIN | | 879 | 2637 | 3 (34)| 00:00:01 |

| 7 | INDEX FAST FULL SCAN | DEPT_IX | 879 | 2637 | 2 (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

6 - access("E"."DEPT_NO"="D"."DEPT_NO")

filter("E"."DEPT_NO"="D"."DEPT_NO")

26 rows selected.

SQL>

SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('fyvyywyh1uyu0',NULL,'COST'));

PLAN_TABLE_OUTPUT

SQL_ID fyvyywyh1uyu0, child number 0

-------------------------------------

SELECT DNAME, COUNT(*) FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO

GROUP BY DNAME ORDER BY COUNT(*) DESC

Plan hash value: 1852086721

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 7 (100)| |

PLAN_TABLE_OUTPUT

| 1 | SORT ORDER BY | | 7 | 112 | 7 (43)| 00:00:01 |

| 2 | HASH GROUP BY | | 7 | 112 | 7 (43)| 00:00:01 |

| 3 | MERGE JOIN | | 879 | 14064 | 5 (20)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 7 | 91 | 2 (0)| 00:00:01 |

| 5 | INDEX FULL SCAN | DEPT_PK | 7 | | 1 (0)| 00:00:01 |

|* 6 | SORT JOIN | | 879 | 2637 | 3 (34)| 00:00:01 |

| 7 | INDEX FAST FULL SCAN | DEPT_IX | 879 | 2637 | 2 (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

6 - access("E"."DEPT_NO"="D"."DEPT_NO")

filter("E"."DEPT_NO"="D"."DEPT_NO")

26 rows selected.

SQL>

SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('fyvyywyh1uyu0',NULL,'MEMSTATS'));

PLAN_TABLE_OUTPUT

SQL_ID fyvyywyh1uyu0, child number 0

-------------------------------------

SELECT DNAME, COUNT(*) FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO

GROUP BY DNAME ORDER BY COUNT(*) DESC

Plan hash value: 1852086721

--------------------------------------------------------------------------------------

| Id | Operation | Name | E-Rows | OMem | 1Mem | O/1/M |

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | | |

PLAN_TABLE_OUTPUT

| 1 | SORT ORDER BY | | 7 | 2048 | 2048 | 1/0/0|

| 2 | HASH GROUP BY | | 7 | 1246K| 1246K| 1/0/0|

| 3 | MERGE JOIN | | 879 | | | |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 7 | | | |

| 5 | INDEX FULL SCAN | DEPT_PK | 7 | | | |

|* 6 | SORT JOIN | | 879 | 18432 | 18432 | 1/0/0|

| 7 | INDEX FAST FULL SCAN | DEPT_IX | 879 | | | |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

6 - access("E"."DEPT_NO"="D"."DEPT_NO")

filter("E"."DEPT_NO"="D"."DEPT_NO")

Note

-----

- Warning: basic plan statistics not available. These are only collected when:

* hint 'gather_plan_statistics' is used for the statement or

* parameter 'statistics_level' is set to 'ALL', at session or system level

32 rows selected.

SQL>

SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('fyvyywyh1uyu0',NULL,'NOTE'));

PLAN_TABLE_OUTPUT

SQL_ID fyvyywyh1uyu0, child number 0

-------------------------------------

SELECT DNAME, COUNT(*) FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO

GROUP BY DNAME ORDER BY COUNT(*) DESC

Plan hash value: 1852086721

------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 7 (100)| |

PLAN_TABLE_OUTPUT

| 1 | SORT ORDER BY | | 7 | 112 | 7 (43)| 00:00:01 |

| 2 | HASH GROUP BY | | 7 | 112 | 7 (43)| 00:00:01 |

| 3 | MERGE JOIN | | 879 | 14064 | 5 (20)| 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 7 | 91 | 2 (0)| 00:00:01 |

| 5 | INDEX FULL SCAN | DEPT_PK | 7 | | 1 (0)| 00:00:01 |

|* 6 | SORT JOIN | | 879 | 2637 | 3 (34)| 00:00:01 |

| 7 | INDEX FAST FULL SCAN | DEPT_IX | 879 | 2637 | 2 (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

6 - access("E"."DEPT_NO"="D"."DEPT_NO")

filter("E"."DEPT_NO"="D"."DEPT_NO")

26 rows selected.

SQL>

SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('fyvyywyh1uyu0', NULL,'IOSTATS'));

PLAN_TABLE_OUTPUT

SQL_ID fyvyywyh1uyu0, child number 0

-------------------------------------

SELECT DNAME, COUNT(*) FROM EMP E, DEPT D WHERE E.DEPT_NO=D.DEPT_NO

GROUP BY DNAME ORDER BY COUNT(*) DESC

Plan hash value: 1852086721

-----------------------------------------------------------

-----------------------------------------------------------

| 0 | SELECT STATEMENT | | |

PLAN_TABLE_OUTPUT

| 1 | SORT ORDER BY | | 7 |

| 2 | HASH GROUP BY | | 7 |

| 3 | MERGE JOIN | | 879 |

| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 7 |

| 5 | INDEX FULL SCAN | DEPT_PK | 7 |

|* 6 | SORT JOIN | | 879 |

| 7 | INDEX FAST FULL SCAN | DEPT_IX | 879 |

-----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

6 - access("E"."DEPT_NO"="D"."DEPT_NO")

filter("E"."DEPT_NO"="D"."DEPT_NO")

Note

-----

- Warning: basic plan statistics not available. These are only collected when:

* hint 'gather_plan_statistics' is used for the statement or

* parameter 'statistics_level' is set to 'ALL', at session or system level

32 rows selected.

SQL>

PLAN_TABLE_OUTPUT

6 - access("E"."DEPT_NO"="D"."DEPT_NO")

filter("E"."DEPT_NO"="D"."DEPT_NO")

26 rows selected.

SQL>

Oracle Database Commands and Practical Examples

HOME

Wednesday 21 August 2019

PT - Displaying Execution Plan using dbms_xplan.display_cursor BASIC | ALL| TYPICAL | ADAPTIVE

No comments:

Post a Comment