Oracle Database Commands and Practical Examples

Wednesday, 21 August 2019

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

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>

PT - Execution Plan using EXPLAIN PLAN FOR Command

SQL> EXPLAIN PLAN FOR select e.ename, d.dname from emp e, dept d where e.dept_no=d.dept_no and e.dept_no=50;

Explained.

SQL> set linsize 200
SP2-0158: unknown SET option "linsize"
SQL> set linessize 200
SP2-0158: unknown SET option "linessize"
SQL> set linesize 200
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1706465873

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 1173 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 23 | 1173 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 26 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 23 | 575 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | DEPT_IX | 23 | | 1 (0)| 00:00:01 |

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

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

3 - access("D"."DEPT_NO"=50)
5 - access("E"."DEPT_NO"=50)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

22 rows selected.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1706465873

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 1173 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 23 | 1173 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 26 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 23 | 575 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | DEPT_IX | 23 | | 1 (0)| 00:00:01 |

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

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

3 - access("D"."DEPT_NO"=50)
5 - access("E"."DEPT_NO"=50)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

22 rows selected.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1706465873

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23 | 1173 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 23 | 1173 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 26 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 23 | 575 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | DEPT_IX | 23 | | 1 (0)| 00:00:01 |

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

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

3 - access("D"."DEPT_NO"=50)
5 - access("E"."DEPT_NO"=50)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

22 rows selected.

SQL> SELECT LPAD(' ', 2*(LEVEL-1)) || OPERATION || ' ' || OPTIONS || ' ' ||OBJECT_NAME || ' ' || DECODE(ID,0,'COST = ' || POSITION) "QUERY PLAN" FROM PLAN_TABLE START WITH ID=0 CONNECT BY PRIOR ID = PARENT_ID;

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT STATEMENT COST = 3
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID DEPT
INDEX UNIQUE SCAN DEPT_PK
TABLE ACCESS BY INDEX ROWID BATCHED EMP
INDEX RANGE SCAN DEPT_IX

6 rows selected.

SQL>

PT- Enable plustrace for Autotrace schema

Senario Preview:

Grant plustrace to schema
Run script to enable plustrace @/u01/app/oracle/product/12.1.0/dbhome_2/sqlplus/admin/plustrce.sql
Run script @$ORACLE_HOME/rdbms/admin/utlxplan.sql for creating plan_table

[oracle@srv1 ~]$ . oraenv
ORACLE_SID = [rac1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@srv1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 20 19:06:24 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> grant plustrace to soe;
grant plustrace to soe
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

SQL> sho user
USER is "SYS"

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
CON_ID
----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
0

PL/SQL Release 12.1.0.2.0 - Production
0

CORE 12.1.0.2.0 Production
0

BANNER
--------------------------------------------------------------------------------
CON_ID
----------
TNS for Linux: Version 12.1.0.2.0 - Production
0

NLSRTL Version 12.1.0.2.0 - Production
0

[oracle@srv1 admin]$ pwd
/u01/app/oracle/product/12.1.0/dbhome_2/sqlplus/admin

SQL> @/u01/app/oracle/product/12.1.0/dbhome_2/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

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.

SQL>
SQL> set echo off
SQL> grant plustrace to soe;

Grant succeeded.

SQL> conn soe/soe
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql

Table created.

SQL> desc plan_table
Name Null? Type
----------------------------------------- -------- ----------------------------
STATEMENT_ID VARCHAR2(30)
PLAN_ID NUMBER
TIMESTAMP DATE
REMARKS VARCHAR2(4000)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(255)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(128)
OBJECT_NAME VARCHAR2(128)
OBJECT_ALIAS VARCHAR2(261)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38)
PARENT_ID NUMBER(38)
DEPTH NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONG
DISTRIBUTION VARCHAR2(30)
CPU_COST NUMBER(38)
IO_COST NUMBER(38)
TEMP_SPACE NUMBER(38)
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)
PROJECTION VARCHAR2(4000)
TIME NUMBER(38)
QBLOCK_NAME VARCHAR2(128)
OTHER_XML CLOB