DBA Hub

📋Steps in this guide1/18

Generate/Customize  Explain Plan

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 … Continue reading Generate Explain Plan →

oracle clusteringintermediate
by OracleDba
12 views
1

Overview

Method 1: Generate Explain Plan using SQL Developer Method 2: Generate Explain Plan using Autotrace in SQL Developer
Step 1

Code/Command (click line numbers to comment):

1
2
3
4
5
6
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

select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;
(Press F10)
2

Section 2

Method 3: Generate Explain Plan using SQL*Plus Method 3.1: Running EXPLAIN PLAN Method 3: Generate Explain Plan using SQL*Plus Method 3.1: Running EXPLAIN PLAN — saves the execution plan in the PLAN_TABLE.
Step 2

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1;
(
Press F6)

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);
3

Section 3

output ======= Method 3.2: Using utlxpls.sql output ======

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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
4

Section 4

Method 3.3: Using EXPLAIN PLAN with the STATEMENT ID Clause output =======

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
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>

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'));

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>
5

Section 5

Method 3.4: Customizing PLAN_TABLE Output Output ======= Method 3.5: Using EXPLAIN PLAN with the INTO Clause

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
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;

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>
6

Section 6

Method 3.5: Using EXPLAIN PLAN with the INTO Clause Output ======= Method 3.6: Outline Hit

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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'));

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>
7

Section 7

Output ======= Method 3.7: Advanced Format

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
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'));

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'));
8

Section 8

Output ======= 4. Using SQL*Plus Autotrace 5. Using DBMS_XPLAN.DISPLAY_CURSOR Method 5.1: BASIC Format

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
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
----------------------------------------
9

Section 9

5. Using DBMS_XPLAN.DISPLAY_CURSOR Method 5.1: BASIC Format Output ======= Method 5.2: SERIAL Format

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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'));

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>
10

Section 10

Method 5.2: SERIAL Format Output ======= Method 5.3: TYPICAL Format

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
set echo on
set linesize 200 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'SERIAL'));

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>
11

Section 11

Output: ======= Method 5.4: ALL Format

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
set echo on
set linesize 200 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'TYPICAL'));

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'));
12

Section 12

Output: ======= Method 5.5: ALLSTATS Format Output =======

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
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'));
13

Section 13

Method 5.6: ALLSTATS LAST Format Output =======

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
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>

set echo on
set linesize 200 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALLSTATS LAST'));

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>
14

Section 14

Method 5.7: Enabling Extra Output Output ====== Method 5.8: Removing Output Sections

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
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'));

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>
15

Section 15

Method 5.8: Removing Output Sections Output ====== Method 5.9: Advanced Format

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
set echo on
set linesize 200 pagesize 1000
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'ALLSTATS LAST -NOTE -ROWS -PREDICATE'));

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>
16

Section 16

Output ======= 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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
set echo on
set linesize 200 pagesize 1000
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('dkz7v96ym42c6',0,'Advanced'));

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>
17

Section 17

Output ======= Method 6: Using DBMS_XPLAN.DISPLAY_AWR (From AWR snaps)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
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'));

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'));
----

set echo on
set linesize 200 pagesize 1000
select * from table(dbms_xplan.display_awr('dkz7v96ym42c6'));
18

Section 18

Output ======= 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!