[Index Tunning] Random Access 제거

1. 실습환경 생성

Linux oracle1 3.10.0-514.el7.x86_64 #1 SMP Tue Nov 22 16:42:41 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
Memory 4G

 

1-1. Tablespace 및 계정생성

create tablespace dbms_data datafile '/app/oradata/dbms_data001.dbf' size 4G autoextend off;

create temporary tablespace dbms_temp tempfile '/app/oracle/cfgtoollogs/orcl/dbms_temp001.dbf' size 1G autoextend off;

create user dbms_test identified by "oracle" default tablespace dbms_data temporary tablespace dbms_temp;

grant resource to dbms_test
/
grant connect to dbms_test
/
grant create view to dbms_test
/
grant create synonym to dbms_test
/

 

1-2. Test table 생성, Insert, 제약조건, 통계정보수집)

CREATE TABLE DBMS_TEST.TB_CUST 
(
    CUST_ID VARCHAR2(10), --고객ID
    CUST_NM VARCHAR2(50), --고객명
    BIRTH_DT VARCHAR2(8), --생일
    INST_DT VARCHAR2(8), --입력일자
    INST_ID VARCHAR2(10), --입력자ID
    INST_NM VARCHAR2(50) --입력자명
);

CREATE TABLE DBMS_TEST.TB_ORD
(
    ORD_NO VARCHAR2(15), --주문번호
    ORD_DT VARCHAR2(8), --주문일자
    PRDT_CD VARCHAR2(6), --제품코드
    ORD_AMT NUMBER(15), --주문금액
    DIS_AMT NUMBER(15), --할인금액
    INST_DT VARCHAR2(8), --입력일자
    INST_ID VARCHAR2(10), --입력자ID
    INST_NM VARCHAR2(50), --입력자명
    CUST_ID VARCHAR2(10) --고객ID
);

INSERT INTO DBMS_TEST.TB_CUST --고객테이블
SELECT
    LPAD(TO_CHAR(ROWNUM), 10, '0'), --고객ID
    DBMS_RANDOM.STRING('U', 10), --고객명
    TO_CHAR(SYSDATE-TRUNC(DBMS_RANDOM.VALUE(365,36500)), 'YYYYMMDD'), --생일
    TO_CHAR(SYSDATE, 'YYYYMMDD'), --입력일자
    'DBMS_TEST', --입력자ID
    '문과장'  --입력자명
FROM DUAL CONNECT BY LEVEL <= 1000000;

COMMIT;

ALTER TABLE DBMS_TEST.TB_ORD NOLOGGING;

INSERT /*+ APPEND */ INTO DBMS_TEST.TB_ORD --APPEND힌트 사용
SELECT
  LPAD(TO_CHAR(ROWNUM), 15, '0'),--주문번호
  TO_CHAR(SYSDATE-TRUNC(DBMS_RANDOM.VALUE(365,3650)), 'YYYYMMDD'), --주문일자
  DBMS_RANDOM.STRING('X', 6), --제품코드
  TRUNC(DBMS_RANDOM.VALUE(1000, 100000)), --주문금액
  TRUNC(DBMS_RANDOM.VALUE(100, 10000)), --할인금액
  TO_CHAR(SYSDATE, 'YYYYMMDD'), --입력일자
  'DBMSEXPERT', --입력자ID
  '문과장',  --입력자명
  A.CUST_ID--고객ID
FROM TB_CUST A, ( SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 10);

COMMIT;

※ 참고
1. Nologging 모드는 Insert 작업시 Redo log 작업을 최소화 한다. 대용량 Insert 작업시 입력시간을 줄일 수 있다. 
2. APPEND Hint
	- APPEND힌트를 사용한다면 세크먼트 HWM 바로 뒤부터 데이터를 입력하게 된다. 또한 데이터 버퍼 캐시를 경유하지 않고 바로 데이터를 저장하게 되므로 데이터 입력시간 단축된다. 


ALTER TABLE DBMS_TEST.DTB_CUST
ADD CONSTRAINT TB_CUST_PK 
PRIMARY KEY (CUST_ID);

ALTER TABLE DBMS_TEST.TB_ORD
ADD CONSTRAINT TB_ORD_PK
PRIMARY KEY (ORD_NO);

ALTER TABLE DBMS_TEST.TB_ORD
ADD CONSTRAINT TB_ORD_FK
FOREIGN KEY (CUST_ID) REFERENCES TB_CUST(CUST_ID);

ANALYZE TABLE DBMS_TEST.TB_CUST COMPUTE STATISTICS
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;

ANALYZE TABLE DBMS_TEST.TB_ORD COMPUTE STATISTICS
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;

 

2. 튜닝

-. Random access 제거

 

2-1 튜닝전 SQL문

SELECT /*+ FULL(A) */
   COUNT(*)
FROM TB_CUST A
WHERE
  A.CUST_NM LIKE 'AB%' AND
  EXISTS
    (
        SELECT '1'
         FROM TB_ORD C
         WHERE
       C.CUST_ID = A.CUST_ID AND
       C.PRDT_CD LIKE 'AB%'
     ) ;

*************************[Explain Plan Time: 2022/04/25 17:08:36]*************************
Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=34K Card=1 Bytes=55)
   1    0   SORT (AGGREGATE) (Card=1 Bytes=55)
   2    1     HASH JOIN (RIGHT SEMI) (Cost=34K Card=927 Bytes=50K)
   3    2       TABLE ACCESS (FULL) OF 'TB_ORD' (TABLE) (Cost=31K Card=927 Bytes=16K)
   4    2       TABLE ACCESS (FULL) OF 'TB_CUST' (TABLE) (Cost=2K Card=50K Bytes=2M)
-----------------------------------------------------------

0.66초

■ 문제점
 1. TB_CUST의 CUST_NM 컬럼이 변별력 있는 컬럼임에도 인덱스생성이 안되있다.
 2. TB_ORD의 CUST_ID 컬럼이 변별력 있는 컬럼임에도 인덱스 생성이 안되있다.
 3. OLTP환경에서 빈번하게 사용하는 SQL이 반복적인 FULL SCAN을 타게되면 시스템에 큰 부하를 준다.

 

2-2. 인덱스 생성

CREATE INDEX TB_CUST_IDX01 ON DBMS_TEST.TB_CUST(CUST_NM);

CREATE INDEX TB_ORD_IDX01 ON DBMS_TEST.TB_ORD(CUST_ID, PRDT_CD);

SELECT /*+ INDEX(A TB_CUST_IDX01) */
  COUNT(*)
FROM TB_CUST A
WHERE A.CUST_NM LIKE 'AB%'
AND EXISTS
            (
             SELECT /*+ INDEX(C TB_ORD_IDX01) NL_SJ */ 
                     '1'
             FROM TB_ORD C
             WHERE C.CUST_ID = A.CUST_ID
             AND C.PRDT_CD LIKE 'AB%'
            );
 
*************************[Explain Plan Time: 2022/04/25 17:42:44]*************************
Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5K Card=1 Bytes=55)
   1    0   SORT (AGGREGATE) (Card=1 Bytes=55)
   2    1     HASH JOIN (SEMI) (Cost=5K Card=2K Bytes=89K)
   3    2       NESTED LOOPS (SEMI) (Cost=5K Card=2K Bytes=89K)
   4    3         STATISTICS COLLECTOR
   5    4           TABLE ACCESS (BY INDEX ROWID BATCHED) OF 'TB_CUST' (TABLE) (Cost=2K Card=2K Bytes=60K)
   6    5             INDEX (RANGE SCAN) OF 'SYS.TB_CUST_IDX01' (INDEX) (Cost=7 Card=2K)
   7    3         INDEX (RANGE SCAN) OF 'SYS.TB_ORD_IDX01' (INDEX) (Cost=2 Card=11K Bytes=185K)
   8    2       INDEX (FULL SCAN) OF 'SYS.TB_ORD_IDX01' (INDEX) (Cost=2 Card=11K Bytes=185K)
-----------------------------------------------------------

0.01 초


■ 문제점

4 -> TB_CUST 테이블의 TB_CUST_IDX01 인덱스틑 CUST_NM으로 이루어진 인덱스다. 해당 인덱스 스캔 후, CUST_ID를 가져오기 위해 TABLE ACCESS BY INDEX ROW ID를 합니다.
CUST_ID가 PK이긴 하지만 , 여기서는 CUST_NM 컬럼으로 이루어진 인덱스를 스캔하였기 떄문에 랜덤 엑세스는 불가피하다.

 

 

2-3 추가튜닝

DROP INDEX TB_CUST_IDX01;
CREATE INDEX TB_CUST_IDX01 ON TB_CUST(CUST_NM, CUST_ID);
ANALYZE INDEX TB_CUST_IDX01 COMPUTE STATISTICS;

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4K Card=1 Bytes=39)
   1    0   SORT (AGGREGATE) (Card=1 Bytes=39)
   2    1     HASH JOIN (SEMI) (Cost=4K Card=2K Bytes=70K)
   3    2       NESTED LOOPS (SEMI) (Cost=4K Card=2K Bytes=70K)
   4    3         STATISTICS COLLECTOR
   5    4           INDEX (RANGE SCAN) OF 'SYS.TB_CUST_IDX01' (INDEX) (Cost=11 Card=2K Bytes=37K)
   6    3         INDEX (RANGE SCAN) OF 'SYS.TB_ORD_IDX01' (INDEX) (Cost=2 Card=8K Bytes=148K)
   7    2       INDEX (FULL SCAN) OF 'SYS.TB_ORD_IDX01' (INDEX) (Cost=2 Card=8K Bytes=148K)
-----------------------------------------------------------

▶ TABLE RANDOM ACCESS가 사라진것이 튜닝 포인트다.

 

---

 

Adaptive Plan

으로인해 2-2에서 Statistic Collectort 과 Hash join , index full scan Plan이 표시 되었다. 

다시 처음부터 생성하고  아래와 같이 통계 정보를 생성하니 Plan이 다르게 풀렸다. 

ANALYZE TABLE TB_CUST COMPUTE STATISTICS
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;

ANALYZE TABLE TB_ORD COMPUTE STATISTICS
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254;

## 통계정보를 삭제하면 2-2 Plan으로 돌아감 ##
ANALYZE TABLE TB_CUST DELETE STATISTICS;
ANALYZE TABLE TB_ORD DELETE STATISTICS;

## 또 /*+ NO_ADAPTIVE_PLAN*/  Hint를 추가하여도 아래와 같이 풀린다.

SELECT /*+ INDEX(A TB_CUST_IDX01) */
  COUNT(*)
FROM TB_CUST A
WHERE A.CUST_NM LIKE 'AB%'
AND EXISTS
            (
             SELECT /*+ INDEX(C TB_ORD_IDX01) NL_SJ */ 
                     '1'
             FROM TB_ORD C
             WHERE C.CUST_ID = A.CUST_ID
             AND C.PRDT_CD LIKE 'AB%'
            );


*************************[Explain Plan Time: 2022/05/09 14:11:29]*************************
Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=102K Card=1 Bytes=55)
   1    0   SORT (AGGREGATE) (Card=1 Bytes=55)
   2    1     NESTED LOOPS (SEMI) (Cost=102K Card=927 Bytes=50K)
   3    2       TABLE ACCESS (BY INDEX ROWID BATCHED) OF 'TB_CUST' (TABLE) (Cost=2K Card=50K Bytes=2M)
   4    3         INDEX (RANGE SCAN) OF 'TB_CUST_IDX01' (INDEX) (Cost=30 Card=9K)
   5    2       INDEX (RANGE SCAN) OF 'TB_ORD_IDX01' (INDEX) (Cost=2 Card=17 Bytes=306)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   4 - access("A"."CUST_NM" LIKE 'AB%')
   4 - filter("A"."CUST_NM" LIKE 'AB%')
   5 - access("C"."CUST_ID"="A"."CUST_ID" AND "C"."PRDT_CD" LIKE 'AB%')
   5 - filter("C"."PRDT_CD" LIKE 'AB%')
-----------------------------------------------------------