개요
SQL 실행계획은 데이터베이스 옵티마이저가 쿼리를 어떻게 수행할지 보여주는 중요한 정보입니다.
실행계획은 여러 방식으로 확인할 수 있는데, 그 중 TYPE3은 실제 쿼리를 수행하면서, 수행된 실행 계획의 row별 수행 시간도 함께 보여줍니다.
이 방식은 자원 사용량에 더해, 각 단계별 소요 시간과 처리 건수까지 확인할 수 있어 성능 분석에 가장 효과적인 SQL 실행계획입니다.
방법
사용 예제
TYPE3 실행계획은 세션 파라미터를 다음과 같이 설정한 뒤 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)
SQL
복사
실행계획 사례 분석
실행계획 분석 순서는 안에서 밖으로, 위에서 아래로, join은 pair로 각 operation에 따라 분석을 진행합니다. 본 문서에서는 TYPE3 를 사용하여 분석하며, 아래는 개선이 필요한 쿼리와 실행계획 예제 입니다.
select c1, c2, c3
from subquery_t2
where c2 = :b1
and c1 >= :b2
and c1 <= :b3
minus
select c4, c5, c6
from subquery_t1;
SQL
복사
아래의 실행계획의 순서는 “4->3->2->6->5->1” 입니다.
쿼리에서 오래 수행되는 부분은 SUBQUERY_T1대한 처리이며, full table scan 하여 sotr까지 수행을 하고 있습니다.
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)
SQL
복사
실행계획 분석에 따른 개선
위 예시 쿼리는 MINUS 구문을 사용하였고, 드라이빙 테이블의 범위 값이 매우 제한적이었습니다.
해당 쿼리를 not exists 구문을 사용하여 서브쿼리로 풀게 되면 join을 사용 할수 있어서 성능 개선이 용이합니다.
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 );
SQL
복사
아래의 실행계획을 보면 not exists 의 서브쿼리로 풀면서 hash join으로 유도를 하였습니다. SUBQUERY_T1에 대해서 index range scan을 타면서 기존에 문제가 되었던 full table scan과 sort가 없어졌으며 T1, T2 테이블이 HASH JOIN으로 풀리면서 성능 개선이 되었습니다. (기존 : 7초 -> 개선:2초)
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)
SQL
복사