Document Type | Technical Information
Category | Tuning
Document Number | TTUTI018
Overview
The SQL execution plan is important information that shows how the database optimizer will execute a query.
Execution plans can be checked in various ways, among which TYPE1 only shows the execution plan predicted by the optimizer without actually running the query.
This method is useful when reviewing performance improvements for batch queries that take a long time to run.
Method
How to Use
The TYPE1 execution plan can be checked by setting the autotrace option as follows.
set autot traceonly exp;
Example
Below is an example of an execution plan checked using the TYPE1 predicted execution plan method.
set autot traceonly exp;
SQL ID: 18014398509482111
Plan hash value: 599365533
Execution Plan
------------------------------------------------------------------------------------
1 INDEX JOIN (Cost:740, %%CPU:1, Rows:92)
2 TABLE ACCESS (FULL): SUBQUERY_T2 (Cost:565, %%CPU:1, Rows:5)
3 TABLE ACCESS (ROWID): SUBQUERY_T1 (Cost:35, %%CPU:0, Rows:11)
4 INDEX (RANGE SCAN): SUBQUERY_T1_IDX_01 (Cost:3, %%CPU:0, Rows:32)
Predicate Information
------------------------------------------------------------------------------------
2 - filter: ("T2"."C3" <= :3) AND ("T2"."C3" >= :2) (0.000 * 1.000)
3 - filter: ("T1"."C6" <= :1) AND ("T1"."C6" >= :0) (0.599 * 0.600)
4 - access: ("T1"."C4" = "T2"."C1") (0.000)