Document Type | Technical Information
Category | Tuning
Document Number | TTUTI019
Overview
The SQL execution plan is important information showing how the database optimizer will execute a query.
Execution plans can be checked in various ways, among which TYPE2 displays the resource usage along with the execution plan predicted by the optimizer while actually running the query. In other words, unlike the predictive execution plan (Type1) that only shows a simple prediction, you can also see statistics reflecting the execution results.
Method
How to Use
Set the autotrace option as follows to get the TYPE2 execution plan.
set autot on;
Usage Example
Below is an example of an execution plan checked using the TYPE1 predictive execution plan method.
Execute the actual query and simultaneously output the execution plan and resource usage.
set autot on;
320 rows selected.
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)
NAME VALUE
------------------------------ ----------
db block gets 18
consistent gets 1789
physical reads 7
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 320