Document Type | Technical Information
Category | Tuning
Document Number | TTUTI024
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 TYPE3 shows the actual execution plan performed along with the execution time per row for the executed plan.
This method is the most effective SQL execution plan for performance analysis because it allows you to check not only resource usage but also the time spent and the number of processed rows at each step.
Method
Usage Example
The TYPE3 execution plan is obtained by setting the session parameters as follows and then running autotrace.
set linesize 150
set timing on
set rows off
alter session set GATHER_SQL_PLAN_STAT=Y;
alter session set GATHER_SQL_EXEC_TIME=Y;
set autot on exp stat plans;
320 rows selected.
Total elapsed time 00:00:00.074127
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 1639
physical reads 0
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 320
Execution Stat
--------------------------------------------------------------------------------
1 INDEX JOIN (Time:.05 ms, Rows:320, Starts:1)
2 TABLE ACCESS (FULL): SUBQUERY_T2 (Time:72.04 ms, Rows:5, Starts:1)
3 TABLE ACCESS (ROWID): SUBQUERY_T1 (Time:.78 ms, Rows:320, Starts:5)
4 INDEX (RANGE SCAN): SUBQUERY_T1_IDX_01 (Time:.06 ms, Rows:320, Starts:5)
Execution Plan Case Analysis
The order of execution plan analysis proceeds from inside to outside, top to bottom, and joins are analyzed by pairs according to each operation. This document uses TYPE3 for analysis, and below is an example of a query and execution plan that need improvement.
select c1, c2, c3 from subquery_t2 where c2 = :b1 and c1 >= :b2 and c1 <= :b3 minus select c4, c5, c6 from subquery_t1;
The order of the execution plan below is “4->3->2->6->5->1”. The part of the query that takes a long time is the processing of SUBQUERY_T1, which performs a full table scan and then a sort.
1923 rows selected.
Total elapsed time 00:00:07.651383
SQL ID: 18
Plan hash value: 3888463868
Execution Plan
---------------------------------------------------------------------------------
1 MINUS (Cost:79971, %%CPU:3, Rows:3836)
2 ORDER BY (SORT) (Cost:274, %%CPU:0, Rows:3846)
3 TABLE ACCESS (ROWID): SUBQUERY_T2 (Cost:274, %%CPU:0, Rows:3846)
4 INDEX (RANGE SCAN): SUBQUERY_T2_IDX_01 (Cost:12, %%CPU:0, Rows:3846)
5 DISTINCT (SORT) (Cost:78774, %%CPU:2, Rows:16000000)
6 TABLE ACCESS (FULL): SUBQUERY_T1 (Cost:15696, %%CPU:0, Rows:16000000)
Predicate Information
--------------------------------------------------------------------------------
4 - access: ("SUBQUERY_T2"."C2" = :0) AND ("SUBQUERY_T2"."C1" >= :1) AND ("SUBQUERY_T2"."C1" <= :2) (0.038 * 0.600 * 0.600)
NAME VALUE
------------------------------ ----------
db block gets 404
consistent gets 37805
physical reads 37857
redo size 0
sorts (disk) 1
sorts (memory) 1
rows processed 1923
Execution Stat
----------------------------------------------------------------------------------
1 MINUS (Time:13.5 ms, Rows:1923, Starts:1)
2 ORDER BY (SORT) (Time:1.98 ms, Rows:3846, Starts:1)
3 TABLE ACCESS (ROWID): SUBQUERY_T2 (Time:11.25 ms, Rows:3846, Starts:1)
4 INDEX (RANGE SCAN): SUBQUERY_T2_IDX_01 (Time:1.23 ms, Rows:3846, Starts:1)
5 DISTINCT (SORT) (Time:6773.22 ms, Rows:250000, Starts:1)
6 TABLE ACCESS (FULL): SUBQUERY_T1 (Time:833.95 ms, Rows:16000000, Starts:1)
Improvements Based on Execution Plan Analysis
The example query above uses the MINUS clause, and the range of the driving table was very limited. By rewriting the query using the not exists clause as a subquery, joins can be used, making performance improvements easier.
select distinct c1, c2, c3
from subquery_t2 t2
where c2 = :b1
and c1 >= :b2
and c1 <= :b3
and not exists ( select /*+ unnest HASH_SJ */ 'x'
from subquery_t1 t1
where t1.c4 = t2.c1
and t1.c5 = t2.c2
and t1.c6 = t2.c3 );The execution plan below shows that the subquery in the not exists clause is rewritten to induce a hash join.
For SUBQUERY_T1, an index range scan is used, eliminating the previously problematic full table scan and sort. The T1 and T2 tables are joined using a HASH JOIN, resulting in performance improvement. (Original: 7 seconds - Improved: 2 seconds)
1923 rows selected.
Total elapsed time 00:00:02.003673
SQL ID: 26
Plan hash value: 3970036369
Execution Plan
------------------------------------------------------------------------------------
1 HASH JOIN (REVERSE ANTI) (Cost:279, %%CPU:0, Rows:3845)
2 TABLE ACCESS (ROWID): SUBQUERY_T1 (Cost:5, %%CPU:0, Rows:1)
3 FILTER (Cost:3, %%CPU:0, Rows:1)
4 INDEX (RANGE SCAN): SUBQUERY_T1_IDX_01 (Cost:3, %%CPU:0, Rows:51)
5 TABLE ACCESS (ROWID): SUBQUERY_T2 (Cost:274, %%CPU:0, Rows:3846)
6 INDEX (RANGE SCAN): SUBQUERY_T2_IDX_01 (Cost:12, %%CPU:0, Rows:3846)
Predicate Information
------------------------------------------------------------------------------------
1 - access: ("T1"."C4" = "T2"."C1") AND ("T1"."C5" = "T2"."C2") AND ("T1"."C6" = "T2"."C3") (0.000 * 1.000 * 1.000)
3 - filter: ("T2"."C2" = :0) (0.038)
4 - access: ("T2"."C1" >= :1) AND ("T2"."C2" = :0) AND ("T2"."C1" <= :2) (0.200 * 0.038 * 0.800)
6 - access: ("T2"."C2" = :0) AND ("T2"."C1" >= :1) AND ("T2"."C1" <= :2) (0.038 * 0.200 * 0.800)
NAME VALUE
------------------------------ ----------
db block gets 0
consistent gets 21763
physical reads 8121
redo size 0
sorts (disk) 0
sorts (memory) 0
rows processed 1923
Execution Stat
-------------------------------------------------------------------------------------
1 HASH JOIN (REVERSE ANTI) (Time:33.63 ms, Rows:1923, Starts:1)
2 TABLE ACCESS (ROWID): SUBQUERY_T1 (Time:120.58 ms, Rows:123072, Starts:1)
3 FILTER (Time:329.17 ms, Rows:123072, Starts:1)
4 INDEX (RANGE SCAN): SUBQUERY_T1_IDX_01 (Time:1507.97 ms, Rows:3200064, Starts:1)
5 TABLE ACCESS (ROWID): SUBQUERY_T2 (Time:2.84 ms, Rows:3846, Starts:1)
6 INDEX (RANGE SCAN): SUBQUERY_T2_IDX_01 (Time:.15 ms, Rows:3846, Starts:1)