프린트 하기

OS환경 : Oracle Linux6.8(64bit)


DB 환경 : Oracle Database 11.2.0.4



설명 : 

테이블의 ROW 수가 1040만건 

이중 where 에 의해 걸려져서 나오는 row는 140만건

MAX 함수로 걸러져 나오는 데이터는 1건 

COLB(컬럼B)가 이 테이블에서 유일한 INDEX



문제 : 해당 쿼리는 매초 실행되는데 physical reads 가 높고 조회 속도가 느림

        => DB 성능 저하의 원인이 됨



문제의 쿼리

SELECT MAX(TO_NUMBER(COLA)) FROM TEST.MAXTEST

WHERE COLB=300000;



테스트 방법 : 

#샘플 테이블 및 인덱스 생성(COLB)

CREATE TABLE MAXTEST(COLA VARCHAR2(20), COLB NUMBER, COLC NUMBER, COLD VARCHAR2(30), COLE VARCHAR2(30), COLF VARCHAR2(30), COLG NUMBER);


CREATE INDEX IDX_COLB ON MAXTEST(COLB); 



#샘플 데이터 삽입(아래 SELECT 절에서 사용할 데이터(COLB=300000)

DECLARE

TYPE tbl_ins IS TABLE OF MAXTEST%ROWTYPE INDEX BY BINARY_INTEGER;

w_ins tbl_ins;

BEGIN

FOR i IN 1..1400000 LOOP 

   w_ins(i).COLA :=i;

   w_ins(i).COLB :=300000;

   w_ins(i).COLC :=99;

   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';

   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';

   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';

   w_ins(i).COLG :=9999999;

END LOOP;

   FORALL i in 1..1400000 INSERT INTO MAXTEST VALUES w_ins(i);

   COMMIT;

END;

/



#샘플 데이터 삽입(1~9000000)

DECLARE

TYPE tbl_ins IS TABLE OF MAXTEST%ROWTYPE INDEX BY BINARY_INTEGER;

w_ins tbl_ins;

BEGIN

FOR i IN 1..1000000 LOOP 

   w_ins(i).COLA :=i;

   w_ins(i).COLB :=10;

   w_ins(i).COLC :=99;

   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';

   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';

   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';

   w_ins(i).COLG :=9999999;

END LOOP;

   FORALL i in 1..1000000 INSERT INTO MAXTEST VALUES w_ins(i);

   COMMIT;

END;

/


DECLARE

TYPE tbl_ins IS TABLE OF MAXTEST%ROWTYPE INDEX BY BINARY_INTEGER;

w_ins tbl_ins;

BEGIN

FOR i IN 1000001..2000000 LOOP 

   w_ins(i).COLA :=i;

   w_ins(i).COLB :=12;

   w_ins(i).COLC :=99;

   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';

   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';

   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';

   w_ins(i).COLG :=9999999;

END LOOP;

   FORALL i in 1000001..2000000 INSERT INTO MAXTEST VALUES w_ins(i);

   COMMIT;

END;

/


DECLARE

TYPE tbl_ins IS TABLE OF MAXTEST%ROWTYPE INDEX BY BINARY_INTEGER;

w_ins tbl_ins;

BEGIN

FOR i IN 2000001..3000000 LOOP 

   w_ins(i).COLA :=i;

   w_ins(i).COLB :=23;

   w_ins(i).COLC :=99;

   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';

   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';

   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';

   w_ins(i).COLG :=9999999;

END LOOP;

   FORALL i in 2000001..3000000 INSERT INTO MAXTEST VALUES w_ins(i);

   COMMIT;

END;

/


DECLARE

TYPE tbl_ins IS TABLE OF MAXTEST%ROWTYPE INDEX BY BINARY_INTEGER;

w_ins tbl_ins;

BEGIN

FOR i IN 3000001..4000000 LOOP 

   w_ins(i).COLA :=i;

   w_ins(i).COLB :=34;

   w_ins(i).COLC :=99;

   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';

   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';

   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';

   w_ins(i).COLG :=9999999;

END LOOP;

   FORALL i in 3000001..4000000 INSERT INTO MAXTEST VALUES w_ins(i);

   COMMIT;

END;

/


DECLARE

TYPE tbl_ins IS TABLE OF MAXTEST%ROWTYPE INDEX BY BINARY_INTEGER;

w_ins tbl_ins;

BEGIN

FOR i IN 4000001..5000000 LOOP 

   w_ins(i).COLA :=i;

   w_ins(i).COLB :=45;

   w_ins(i).COLC :=99;

   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';

   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';

   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';

   w_ins(i).COLG :=9999999;

END LOOP;

   FORALL i in 4000001..5000000 INSERT INTO MAXTEST VALUES w_ins(i);

   COMMIT;

END;

/


DECLARE

TYPE tbl_ins IS TABLE OF MAXTEST%ROWTYPE INDEX BY BINARY_INTEGER;

w_ins tbl_ins;

BEGIN

FOR i IN 5000001..6000000 LOOP 

   w_ins(i).COLA :=i;

   w_ins(i).COLB :=56;

   w_ins(i).COLC :=99;

   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';

   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';

   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';

   w_ins(i).COLG :=9999999;

END LOOP;

   FORALL i in 5000001..6000000 INSERT INTO MAXTEST VALUES w_ins(i);

   COMMIT;

END;

/


DECLARE

TYPE tbl_ins IS TABLE OF MAXTEST%ROWTYPE INDEX BY BINARY_INTEGER;

w_ins tbl_ins;

BEGIN

FOR i IN 6000001..7000000 LOOP 

   w_ins(i).COLA :=i;

   w_ins(i).COLB :=67;

   w_ins(i).COLC :=99;

   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';

   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';

   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';

   w_ins(i).COLG :=9999999;

END LOOP;

   FORALL i in 6000001..7000000 INSERT INTO MAXTEST VALUES w_ins(i);

   COMMIT;

END;

/


DECLARE

TYPE tbl_ins IS TABLE OF MAXTEST%ROWTYPE INDEX BY BINARY_INTEGER;

w_ins tbl_ins;

BEGIN

FOR i IN 7000001..8000000 LOOP 

   w_ins(i).COLA :=i;

   w_ins(i).COLB :=78;

   w_ins(i).COLC :=99;

   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';

   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';

   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';

   w_ins(i).COLG :=9999999;

END LOOP;

   FORALL i in 7000001..8000000 INSERT INTO MAXTEST VALUES w_ins(i);

   COMMIT;

END;

/


DECLARE

TYPE tbl_ins IS TABLE OF MAXTEST%ROWTYPE INDEX BY BINARY_INTEGER;

w_ins tbl_ins;

BEGIN

FOR i IN 8000001..9000000 LOOP 

   w_ins(i).COLA :=i;

   w_ins(i).COLB :=89;

   w_ins(i).COLC :=99;

   w_ins(i).COLD :='DDDDDDDDDDDDDDDD';

   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';

   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';

   w_ins(i).COLG :=9999999;

END LOOP;

   FORALL i in 8000001..9000000 INSERT INTO MAXTEST VALUES w_ins(i);

   COMMIT;

END;

/



#통계정보 수집

ANALYZE TABLE TEST.MAXTEST DELETE STATISTICS;

ANALYZE TABLE TEST.MAXTEST COMPUTE STATISTICS;

ANALYZE TABLE TEST.MAXTEST COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;



#쿼리 실행

set autotrace on

SELECT MAX(TO_NUMBER(COLA)) FROM TEST.MAXTEST

WHERE COLB=300000;


MAX(TO_NUMBER(COLA))

--------------------

      1400000


1 row selected.


Elapsed: 00:00:08.36


Execution Plan

----------------------------------------------------------

Plan hash value: 4023033980


-----------------------------------------------------------------------------------------

| Id  | Operation       | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |  |     1 |     9 | 17470   (1)| 00:03:30 |

|   1 |  SORT AGGREGATE       |  |     1 |     9 |      |  |

|   2 |   TABLE ACCESS BY INDEX ROWID| MAXTEST |  1400K|    12M| 17470   (1)| 00:03:30 |

|*  3 |    INDEX RANGE SCAN      | IDX_COLB |  1400K| |  2751   (1)| 00:00:34 |

-----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


   3 - access("COLB"=300000)



Statistics

----------------------------------------------------------

  26  recursive calls

   0  db block gets

      17391  consistent gets

      17401  physical reads

   0  redo size

 539  bytes sent via SQL*Net to client

 523  bytes received via SQL*Net from client

   2  SQL*Net roundtrips to/from client

   7  sorts (memory)

   0  sorts (disk)

   1  rows processed

#INDEX SCAN 실행 되고 physical reads가 17401 cost가 17470로 나옴



#FULL SCAN 시 결과 확인

SELECT /*+FULL(MAXTEST)*/ MAX(TO_NUMBER(COLA)) FROM TEST.MAXTEST

WHERE COLB=300000;  2  


MAX(TO_NUMBER(COLA))

--------------------

      1400000


1 row selected.


Elapsed: 00:00:05.22


Execution Plan

----------------------------------------------------------

Plan hash value: 157265545


------------------------------------------------------------------------------

| Id  | Operation    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    1 |    9 | 29951   (1)| 00:06:00 |

|   1 |  SORT AGGREGATE    |      |    1 |    9 |    |      |

|*  2 |   TABLE ACCESS FULL| MAXTEST | 1400K|   12M| 29951   (1)| 00:06:00 |

------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


   2 - filter("COLB"=300000)



Statistics

----------------------------------------------------------

   1  recursive calls

   0  db block gets

     109321  consistent gets

     109316  physical reads

   0  redo size

 539  bytes sent via SQL*Net to client

 523  bytes received via SQL*Net from client

   2  SQL*Net roundtrips to/from client

   0  sorts (memory)

   0  sorts (disk)

   1  rows processed

#physical reads가 109316 cost가 29951로 나옴



#기존 INDEX 삭제

DROP INDEX IDX_COLB;



#INDEX COLUMN B, COLUMN A 순으로 재생성

CREATE INDEX IDX_COLBCOLA ON MAXTEST(COLB,COLA);



#통계정보 재수집 후 리부팅

ANALYZE TABLE TEST.MAXTEST DELETE STATISTICS;

ANALYZE TABLE TEST.MAXTEST COMPUTE STATISTICS;

ANALYZE TABLE TEST.MAXTEST COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

SHUTDOWN IMMEDIATE

STARTUP



#쿼리 재 실행

set autotrace on

SELECT MAX(TO_NUMBER(COLA)) FROM TEST.MAXTEST

WHERE COLB=300000;


MAX(TO_NUMBER(COLA))

--------------------

      1400000


1 row selected.


Elapsed: 00:00:04.23


Execution Plan

----------------------------------------------------------

Plan hash value: 2810612275


----------------------------------------------------------------------------------

| Id  | Operation   | Name  | Rows  | Bytes | Cost (%CPU)| Time  |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |   |     1 |     9 |  4270   (1)| 00:00:52 |

|   1 |  SORT AGGREGATE   |   |     1 |     9 |       |   |

|*  2 |   INDEX RANGE SCAN| IDX_COLBCOLA |  1400K|    12M|  4270   (1)| 00:00:52 |

----------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


   2 - access("COLB"=300000)



Statistics

----------------------------------------------------------

  34  recursive calls

   0  db block gets

       4207  consistent gets

       4161  physical reads

   0  redo size

 539  bytes sent via SQL*Net to client

 523  bytes received via SQL*Net from client

   2  SQL*Net roundtrips to/from client

  11  sorts (memory)

   0  sorts (disk)

   1  rows processed

#INDEX SCAN 실행 되고 physical reads가 4161 cost가 4270로 나옴



#FULL SCAN 시

SELECT /*+FULL(MAXTEST)*/ MAX(TO_NUMBER(COLA)) FROM TEST.MAXTEST

WHERE COLB=300000;


MAX(TO_NUMBER(COLA))

--------------------

      1400000


1 row selected.


Elapsed: 00:00:05.65


Execution Plan

----------------------------------------------------------

Plan hash value: 157265545


------------------------------------------------------------------------------

| Id  | Operation    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    1 |    9 | 29951   (1)| 00:06:00 |

|   1 |  SORT AGGREGATE    |      |    1 |    9 |    |      |

|*  2 |   TABLE ACCESS FULL| MAXTEST | 1400K|   12M| 29951   (1)| 00:06:00 |

------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------


   2 - filter("COLB"=300000)



Statistics

----------------------------------------------------------

   1  recursive calls

   0  db block gets

     109321  consistent gets

     109316  physical reads

   0  redo size

 539  bytes sent via SQL*Net to client

 523  bytes received via SQL*Net from client

   2  SQL*Net roundtrips to/from client

   0  sorts (memory)

   0  sorts (disk)

   1  rows processed

#FULL SCAN 결과는 physical reads가 109316 cost가 29951로 동일



+ ANALYZE TABLE 명령어를 사용한 이유는 

 현재 운영에서 지연 발생시 이 방법으로 지연해결 하기 때문



결론

INDEX 추가 생성으로 physical reads 및 cost 확연히 감소


 

 physical read

 cost

 실행계획

 BEFORE

 17401

17470 

SELECT STATEMENT
 SORT AGGREGATE
  TABLE ACCESS BY INDEX ROWID
   INDEX RANGE SCAN

 AFTER

 4161

4270 

SLECT STATEMENT
 SORT AGGREGATE
 
   INDEX RANGE SCAN
 



저작자표시

'ORACLE > Performance Tuning ' 카테고리의 다른 글

enq UL - contention, PLSQL lock Timer  (0) 2018.12.05
SQL을 변경하지 않고 실행계획 변경 테스트(SQL Plan Management)  (0) 2018.06.28
QMON Coordinator 란  (0) 2018.05.30
SQL*Net message from client 란?  (8) 2018.05.30
AWR 스냅샷 관리  (0) 2018.05.09

티스토리툴바