OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.11.0.0
방법 : sqlp 실기튜닝실습 20-1 인덱스 최적화 구성 devDo
devDo님의 블로그를 참고하여 테이블 생성 후 실습함
sqlp 실기 준비 스터디를 진행하면서 나온 스터디원의 답안과 모법답안을 작성함
* 문제 쿼리는 문제와 동일한 실행계획을 표현하기위해 힌트를 사용해서 고정함
* 쿼리 실행 유저는 IMSI 유저를 사용함(다른 유저명으로 생성해서 사용해도 문제없음)
* 본문 샘플데이터의 분포, DB 버전이 문제와 동일하지 않기 때문에 문제와 답안의 성능(결과)가 devDo님 결과와 다를수있음
문제) 위의 쿼리에 대한 trace를 보고 튜닝을 하시오.
인덱스 구성
|
1
2
3
|
pk_idx_manuf20 : m_code
pk_idx_product20 : p_code + prod_id
idx_order20_01 : orderdt + order_qty + prod_id
|
SQL
|
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
|
SQL>
select /*+ gather_plan_statistics leading(m p o) use_nl(p) use_nl(o) index(p pk_idx_product20) index(m pk_idx_manuf20) index(o idx_order20_01) */
p.prod_nm, p.prod_id, o.order_qty
from manuf20 m, product20 p, order20 o
where m.m_code like 'A%'
and m.m_code = p.p_code
and p.prod_id = o.prod_id
and o.orderdt = '20001114'
and o.order_qty > '9000';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows|E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 10662 (100)| | 4 |00:00:00.01 | 2065 |
| 1 | NESTED LOOPS | | 1 | 397 | 15086 | 10662 (1)| 00:00:01 | 4 |00:00:00.01 | 2065 |
| 2 | NESTED LOOPS | | 1 | 900 | 16200 | 7957 (1)| 00:00:01 | 1000 |00:00:00.01 | 36 |
|* 3 | INDEX RANGE SCAN | PK_IDX_MANUF20 | 1 | 7913 | 47478 | 20 (0)| 00:00:01 | 100 |00:00:00.01 | 4 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCT20 | 100 | 1 | 12 | 2 (0)| 00:00:01 | 1000 |00:00:00.01 | 32 |
|* 5 | INDEX RANGE SCAN | PK_IDX_PRODUCT20 | 100 | 90 | | 1 (0)| 00:00:01 | 1000 |00:00:00.01 | 25 |
|* 6 | INDEX RANGE SCAN | IDX_ORDER20_01 | 1000 | 1 | 20 | 3 (0)| 00:00:01 | 4 |00:00:00.02 | 2029 |
----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("M"."M_CODE" LIKE 'A%')
filter("M"."M_CODE" LIKE 'A%')
5 - access("M"."M_CODE"="P"."P_CODE")
filter("P"."P_CODE" LIKE 'A%')
6 - access("O"."ORDERDT"='20001114' AND "O"."ORDER_QTY">'9000' AND "P"."PROD_ID"="O"."PROD_ID" AND "O"."ORDER_QTY" IS NOT NULL)
filter("P"."PROD_ID"="O"."PROD_ID")
|
샘플 데이터 생성
|
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
|
-------샘플데이터 생성 시작-------
-- 테이블 생성
drop table manuf20 purge;
drop table product20 purge;
drop table order20 purge;
create table manuf20 (m_code varchar2(6)) nologging;
create table product20 (prod_nm varchar2(4), prod_id number, p_code varchar2(6)) nologging;
create table order20 (orderdt varchar2(8), prod_id number, order_qty varchar2(8)) nologging;
-- 데이터 생성
DECLARE
TYPE tbl_ins IS TABLE OF manuf20%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..10000 LOOP
w_ins(i).m_code := 'B'||i;
END LOOP;
FORALL i in 1..10000 INSERT INTO manuf20 VALUES w_ins(i);
COMMIT;
END;
/
DECLARE
TYPE tbl_ins IS TABLE OF manuf20%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..100 LOOP
w_ins(i).m_code := 'A'||i;
END LOOP;
FORALL i in 1..100 INSERT INTO manuf20 VALUES w_ins(i);
COMMIT;
END;
/
DECLARE
TYPE tbl_ins IS TABLE OF product20%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..1000 LOOP
w_ins(i).prod_nm := dbms_random.string('x',4);
w_ins(i).prod_id := i;
w_ins(i).p_code := 'A'||ceil(i/100);
END LOOP;
FORALL i in 1..1000 INSERT INTO product20 VALUES w_ins(i);
COMMIT;
END;
/
DECLARE
TYPE tbl_ins IS TABLE OF order20%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..100000 LOOP
w_ins(i).orderdt := '200011'||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).prod_id := i;
w_ins(i).order_qty := ceil(dbms_random.value(1, 100000));
END LOOP;
FORALL i in 1..100000 INSERT INTO order20 VALUES w_ins(i);
COMMIT;
END;
/
alter table manuf20 logging;
alter table product20 logging;
alter table order20 logging;
drop index pk_idx_manuf20;
drop index pk_idx_product20;
drop index idx_order20_01;
create unique index pk_idx_manuf20 on manuf20(m_code);
create unique index pk_idx_product20 on product20(p_code, prod_id);
create index idx_order20_01 on order20(orderdt, order_qty, prod_id);
conn / as sysdba
select table_name, segment_created, last_analyzed from dba_tables where table_name in ('MANUF20','PRODUCT20','ORDER20');
exec dbms_stats.gather_table_stats('IMSI', 'MANUF20', cascade=>TRUE);
exec dbms_stats.gather_table_stats('IMSI', 'PRODUCT20', cascade=>TRUE);
exec dbms_stats.gather_table_stats('IMSI', 'ORDER20', cascade=>TRUE);
select table_name, segment_created, last_analyzed from dba_tables where table_name in ('MANUF20','PRODUCT20','ORDER20');
conn imsi/imsi
|
샘플 데이터 생성 후 통계정보를 수동으로 수집해줌
스터디원 답안
P님 답안
- 문제점 파악
product20 스캔 시 인덱스 스캔 후 모든 row를 찾았지만 table 엑세스를 해서 cr 블록을 많이읽음
order20 스캔 시 인덱스에서만 읽지만 cr 블록을 많이읽음
predicate 를 보면 QT로 인해 m.m_code like 'A%' 조건이 p.p_code like 'A%'로 복제됨
- 개선 방안
manuf20 테이블은 m_code 조건에서만 사용하고 select 절에는 없으므로 제거
대신 product20테이블에 p.p_code like 'A%' 조건 추가
product20 인덱스 추가 idx2_product : prod_id+p_code+prod_nm
테이블까지 안가고 인덱스에서만 모두 읽게끔 인덱스 생성
- 쿼리
|
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
|
SQL>
drop index idx_product20_01 ;
create index idx_product20_01 on product20(prod_id, p_code, prod_nm);
select /*+ gather_plan_statistics leading(o p) use_nl(p) index(p idx_product20_01) index(o idx_order20_01) */
p.prod_nm, p.prod_id, o.order_qty
from product20 p, order20 o
where p.p_code like 'A%'
and p.prod_id = o.prod_id
and o.orderdt = '20001114'
and o.order_qty > '9000';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 401 (100)| | 4 |00:00:00.01 | 45 | 4 |
| 1 | NESTED LOOPS | | 1 | 397 | 12704 | 401 (0)| 00:00:01 | 4 |00:00:00.01 | 45 | 4 |
|* 2 | INDEX RANGE SCAN| IDX_ORDER20_01 | 1 | 397 | 7940 | 4 (0)| 00:00:01 | 385 |00:00:00.01 | 6 | 0 |
|* 3 | INDEX RANGE SCAN| IDX_PRODUCT20_01 | 385 | 1 | 12 | 1 (0)| 00:00:01 | 4 |00:00:00.01 | 39 | 4 |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."ORDERDT"='20001114' AND "O"."ORDER_QTY">'9000' AND "O"."ORDER_QTY" IS NOT NULL)
3 - access("P"."PROD_ID"="O"."PROD_ID" AND "P"."P_CODE" LIKE 'A%')
filter("P"."P_CODE" LIKE 'A%')
|
J님 답안
|
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
|
SQL>
drop index idx_product20_01;
drop index idx_order20_01;
create index idx_product20_01 on product20 (p_code, prod_id, prod_nm);
create index idx_order20_01 on order20 (orderdt, prod_id, order_qty);
SELECT /*+ gather_plan_statistics
leading (o) use_hash (p) index (p idx_product20_01) index (o idx_order20_01) */
P.PROD_NM, P.PROD_ID, O.ORDER_QTY
FROM PRODUCT20 P, ORDER20 O
WHERE P.P_CODE LIKE 'A%'
AND P.PROD_ID = O.PROD_ID
AND O.ORDERDT = '20001114'
AND O.ORDER_QTY > '9000';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 23 (100)| | 4 |00:00:00.01 | 24 | 21 | | | |
|* 1 | HASH JOIN | | 1 | 397 | 12704 | 23 (0)| 00:00:01 | 4 |00:00:00.01 | 24 | 21 | 1797K| 1797K| 1559K (0)|
|* 2 | INDEX RANGE SCAN| IDX_ORDER20_01 | 1 | 397 | 7940 | 18 (0)| 00:00:01 | 385 |00:00:00.01 | 18 | 17 | | | |
|* 3 | INDEX RANGE SCAN| IDX_PRODUCT20_01 | 1 | 900 | 10800 | 5 (0)| 00:00:01 | 1000 |00:00:00.01 | 6 | 4 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("P"."PROD_ID"="O"."PROD_ID")
2 - access("O"."ORDERDT"='20001114' AND "O"."ORDER_QTY">'9000')
filter("O"."ORDER_QTY">'9000')
3 - access("P"."P_CODE" LIKE 'A%')
filter("P"."P_CODE" LIKE 'A%')
|
S님 답안
|
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
|
SQL>
drop index idx_product20_01;
drop index idx_order20_01;
drop index pk_idx_product20;
create index pk_idx_product20 on product20(p_code,prod_id,prod_nm);
create index idx_order20_01 on order20(prod_id,orderdt,order_qty);
select /*+ gather_plan_statistics leading(m p o) use_nl(p) use_nl(o) index(p pk_idx_product20) index(m pk_idx_manuf20) index(o idx_order20_01) */
p.prod_nm, p.prod_id, o.order_qty
from manuf20 m, product20 p, order20 o
where m.m_code like 'A%'
and m.m_code = p.p_code
and p.prod_id = o.prod_id
and o.orderdt = '20001114'
and o.order_qty > '9000';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8850 (100)| | 4 |00:00:00.01 | 88 | 9 |
| 1 | NESTED LOOPS | | 1 | 397 | 15086 | 8850 (1)| 00:00:01 | 4 |00:00:00.01 | 88 | 9 |
| 2 | NESTED LOOPS | | 1 | 900 | 16200 | 7950 (1)| 00:00:01 | 1000 |00:00:00.01 | 35 | 4 |
|* 3 | INDEX RANGE SCAN| PK_IDX_MANUF20 | 1 | 7913 | 47478 | 20 (0)| 00:00:01 | 100 |00:00:00.01 | 3 | 0 |
|* 4 | INDEX RANGE SCAN| PK_IDX_PRODUCT20 | 100 | 1 | 12 | 1 (0)| 00:00:01 | 1000 |00:00:00.01 | 32 | 4 |
|* 5 | INDEX RANGE SCAN | IDX_ORDER20_01 | 1000 | 1 | 20 | 1 (0)| 00:00:01 | 4 |00:00:00.01 | 53 | 5 |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("M"."M_CODE" LIKE 'A%')
filter("M"."M_CODE" LIKE 'A%')
4 - access("M"."M_CODE"="P"."P_CODE")
filter("P"."P_CODE" LIKE 'A%')
5 - access("P"."PROD_ID"="O"."PROD_ID" AND "O"."ORDERDT"='20001114' AND "O"."ORDER_QTY">'9000' AND "O"."ORDER_QTY" IS NOT NULL)
|
G님 답안
|
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
|
SQL>
drop index idx_product20_01;
drop index pk_idx_product20;
drop index idx_order20_01;
create unique index pk_idx_product20 on product20(p_code, prod_id);
create index idx_order20_01 on order20(prod_id, orderdt, order_qty);
select /*+ gather_plan_statistics leading(m p o) use_nl(p) use_nl(o) index(p pk_idx_product20) index(m pk_idx_manuf20) index(o idx_order20_01) */
p.prod_nm, p.prod_id, o.order_qty
from manuf20 m, product20 p, order20 o
where m.m_code like 'A%'
and m.m_code = p.p_code
and p.prod_id = o.prod_id
and o.orderdt = '20001114'
and o.order_qty > '9000';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8858 (100)| | 4 |00:00:00.01 | 87 | 8 |
| 1 | NESTED LOOPS | | 1 | 397 | 15086 | 8858 (1)| 00:00:01 | 4 |00:00:00.01 | 87 | 8 |
| 2 | NESTED LOOPS | | 1 | 900 | 16200 | 7957 (1)| 00:00:01 | 1000 |00:00:00.01 | 34 | 3 |
|* 3 | INDEX RANGE SCAN | PK_IDX_MANUF20 | 1 | 7913 | 47478 | 20 (0)| 00:00:01 | 100 |00:00:00.01 | 3 | 0 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCT20 | 100 | 1 | 12 | 2 (0)| 00:00:01 | 1000 |00:00:00.01 | 31 | 3 |
|* 5 | INDEX RANGE SCAN | PK_IDX_PRODUCT20 | 100 | 90 | | 1 (0)| 00:00:01 | 1000 |00:00:00.01 | 25 | 3 |
|* 6 | INDEX RANGE SCAN | IDX_ORDER20_01 | 1000 | 1 | 20 | 1 (0)| 00:00:01 | 4 |00:00:00.01 | 53 | 5 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("M"."M_CODE" LIKE 'A%')
filter("M"."M_CODE" LIKE 'A%')
5 - access("M"."M_CODE"="P"."P_CODE")
filter("P"."P_CODE" LIKE 'A%')
6 - access("P"."PROD_ID"="O"."PROD_ID" AND "O"."ORDERDT"='20001114' AND "O"."ORDER_QTY">'9000' AND "O"."ORDER_QTY" IS NOT NULL)
|
모범답안
1. product20 인덱스 추가 idx2_product : p_code+prod_id+prod_nm
2. order20 인덱스 추가 idx2_order : prod_id+orderdt+order_qty
|
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
|
SQL>
drop index idx_product20_01;
drop index idx_order20_01;
create index idx_order20_01 on order20(orderdt, order_qty, prod_id);
create index idx_product20_01 on product20(p_code, prod_id, prod_nm);
create index idx_order20_02 on order20(prod_id, orderdt, order_qty);
select /*+ gather_plan_statistics leading(m p o) use_nl(p) use_nl(o) index(p idx_product20_01) index(m pk_idx_manuf20) index(o idx_order20_02) */
p.prod_nm, p.prod_id, o.order_qty
from manuf20 m, product20 p, order20 o
where m.m_code like 'A%'
and m.m_code = p.p_code
and p.prod_id = o.prod_id
and o.orderdt = '20001114'
and o.order_qty > '9000';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8850 (100)| | 4 |00:00:00.01 | 88 | 9 |
| 1 | NESTED LOOPS | | 1 | 397 | 15086 | 8850 (1)| 00:00:01 | 4 |00:00:00.01 | 88 | 9 |
| 2 | NESTED LOOPS | | 1 | 900 | 16200 | 7950 (1)| 00:00:01 | 1000 |00:00:00.01 | 35 | 4 |
|* 3 | INDEX RANGE SCAN| PK_IDX_MANUF20 | 1 | 7913 | 47478 | 20 (0)| 00:00:01 | 100 |00:00:00.01 | 3 | 0 |
|* 4 | INDEX RANGE SCAN| IDX_PRODUCT20_01 | 100 | 1 | 12 | 1 (0)| 00:00:01 | 1000 |00:00:00.01 | 32 | 4 |
|* 5 | INDEX RANGE SCAN | IDX_ORDER20_02 | 1000 | 1 | 20 | 1 (0)| 00:00:01 | 4 |00:00:00.01 | 53 | 5 |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("M"."M_CODE" LIKE 'A%')
filter("M"."M_CODE" LIKE 'A%')
4 - access("M"."M_CODE"="P"."P_CODE")
filter("P"."P_CODE" LIKE 'A%')
5 - access("P"."PROD_ID"="O"."PROD_ID" AND "O"."ORDERDT"='20001114' AND "O"."ORDER_QTY">'9000' AND "O"."ORDER_QTY" IS NOT NULL)
|
* 본문 샘플데이터의 분포가 문제와 동일하지 않기 때문에 문제와 답안의 성능(결과)가 devDo님 결과와 다를수있음
* 상세 설명은 devDo님 설명참조
참조 :
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c 실행계획 비교 DBMS_XPLAN.COMPARE_PLANS (0) | 2022.03.21 |
|---|---|
| sqlp 실기튜닝실습 17-1 PUSH PREDICATE 문제 devDo (2) | 2022.03.06 |
| 오라클 11gR1 이상버전에서 보이는 Nested Loop 조인 실행계획(Prefetch, vector I/O) (0) | 2021.11.16 |
| 오라클 19c dbms_xplan.display_cursor Hint Report 포맷 (0) | 2021.10.23 |
| 로그스위치 과다 발생 시 확인 사항 (5) | 2020.08.03 |