오라클 성능 고도화 원리와 해법 II (2012년)
목록
옵티마이저 행동에 영향을 미치는 요소 0 0 99,999+

by 구루비스터디 옵티마이저 OPTIMIZER [2018.04.01]

  1. 옵티마이저 행동에 영향을 미치는 요소
    1. SQL과 연산자 형태
    2. 인덱스 , IOT, 클러스터링, 파티셔닝, MV 등 옵티마이징 팩터
    3. 제약 설정 : PK, FK, NOT NULL, CHECK
    4. 옵티마이저 힌트
    5. 통계정보 : 오브젝트 통계, 시스템 통계
    6. 옵티마이저 관련 파라미터
    7. DBMS 버전과 종류


옵티마이저 행동에 영향을 미치는 요소

  • 옵티마이저의 행동의 차이는 실행계획의 차이를 말하며, 영향을 미치는 결과는 아래와 같다.


SQL과 연산자 형태




-- 연산자에 따른 변화
   CREATE INDEX BSHMAN_IDX ON BSHMAN_TEST(COL1,COL3));
      
   SELECT * 
   FROM BSHMAN_TEST
   WHERE COL1 < 100
     AND COL3 < 100

Plan hash value: 76087578
 
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| BSHMAN_TEST |     1 |    12 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | BSHMAN_IDX  |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("COL1"<100 AND "COL3"<100)
       filter("COL3"<100)


   SELECT * 
   FROM BSHMAN_TEST
   WHERE COL1 <> 100   <-- 연산자를 변경
     AND COL3 < 100

Plan hash value: 1554795123
 
---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |       |       |  8304 (100)|          |
|*  1 |  TABLE ACCESS FULL| BSHMAN_TEST |   991K|    11M|  8304   (2)| 00:01:40 |
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(("COL3"<100 AND "COL1"<>100))


인덱스 , IOT, 클러스터링, 파티셔닝, MV 등 옵티마이징 팩터


   CREATE INDEX BSHMAN_IDX ON BSHMAN_TEST(COL1,COL3));
      
   SELECT * 
   FROM BSHMAN_TEST
   WHERE COL1 < 100
     AND COL3 < 100

Plan hash value: 76087578
 
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| BSHMAN_TEST |     1 |    12 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | BSHMAN_IDX  |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

DROP INDEX BSHMAN_IDX;

   SELECT * 
   FROM BSHMAN_TEST
   WHERE COL1 < 100
     AND COL3 < 100

Plan hash value: 1554795123
 
---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |       |       |  8295 (100)|          |
|*  1 |  TABLE ACCESS FULL| BSHMAN_TEST |     1 |    12 |  8295   (2)| 00:01:40 |
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(("COL1"<100 AND "COL3"<100))


제약 설정 : PK, FK, NOT NULL, CHECK



--PK 제약과 옵티마이저
select sum(주문수량), sum(주문금액), count(*), count(distinct 고객번호)
  from 주문
 where 고객번호 in (select 고객번호 
                        from 고객
                       where 가입일자 >= trunc(add_months(sysdate, -12)))
   and 주문일자 >= trunc(add_months(sysdate, -1))

--FK 제약과 옵티마이저 : 4장 6절 참고


-- Not Null 제약과 옵티마이저
-- 제약조건으로 not null을 설정하지 않으면 옵티마이저는 null 값의 입력여부에 대한 가능성을 염두하고 실행계획을 수립.
-- 그러므로, 테이블 전체 스캔처리한다.

SQL> desc emp
 이름                                      널?      유형
 ----------------------------------------- -------- ---------------------------

 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

select deptno, count(*) from emp group by deptno;

 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    16 (100)|          |
|   1 |  HASH GROUP BY     |      |    14 |   182 |    16   (7)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   182 |    15   (0)| 00:00:01 |
---------------------------------------------------------------------------


SQL> desc emp
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------

 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                    NOT NULL NUMBER(2)

select deptno, count(*) from emp group by deptno;

-----------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |       |       |     1 (100)|          |
|   1 |  SORT GROUP BY NOSORT|            |    14 |   182 |     1   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN    | DEPTNO_IDX |    14 |   182 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------



--Check 제약과 옵티마이저

-- alter 하지않을경우 Filter 로 풀리지않는다.

select * from emp where sal > 5000
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    15 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("SAL">5000)

-- 추가시작

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      1 |    87 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       7 |
--------------------------------------------------------------------------------------------------------------------

-- 추가끝


alter table emp modify sal check (sal <= 5000);

select * from emp where sal > 5000

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     1 (100)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    87 |    15   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(NULL IS NOT NULL)
   2 - filter("SAL">5000)

-- 추가시작

-----------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
-----------------------------------------------------------------------------------------------------------
|*  1 |  FILTER            |      |      1 |        |       |            |          |      0 |00:00:00.01 |
|*  2 |   TABLE ACCESS FULL| EMP  |      0 |      1 |    87 |    15   (0)| 00:00:01 |      0 |00:00:00.01 |
-----------------------------------------------------------------------------------------------------------

-- 추가끝


옵티마이저 힌트



-- 힌트는 매우 강력한 옵티마이저를 가지고노는 기능중에 하나이다.
-- 아래는 왠만해서는 힌트가 우선적이지만, 아닌경우에 대해서 나열했다.

-- 1.문법적으로 맞지않은 힌트 기술
-- ex) /* index */ 라든지, /*+ index /*....

-- 2.잘못된 참조사용
-- ex) 존재하지않은 인덱스명이나 alias에 힌트를 주는경우...

-- 3.의미적으로 맞지 않게 힌트를 기술
-- ex)서브쿼리에 unnext와 push_subq를 같이 기술하는 경우(unnest되지 않은 서브쿼리만이 push_subq 힌트적용대상)

-- 4.논리적으로 불가능한 액세스 경로
-- ex)select /*+ index(e emp_ename) */ count(*) from emp 3

-- 5.버그

create table t1 ( a number, b varchar2(100));
create table t2 ( a number, b varchar2(100));
create table t3 ( a number, b varchar2(100));
create table t4 ( a number, b varchar2(100));
create table t5 ( a number, b varchar2(100));

alter system flush shared_pool;

set timing on;

SQL> declare
  2   i_cnt number;
  3  begin
  4    for i in 1..10000
  5
  6    loop
  7
  8    execute immediate 'select /*+ ordered */ count(*)'||
  9                      ' from t1,t2,t3,t4,t5        '||
 10                      'where t1.a = ' || i ||
 11                      '  and t2.a = ' || i ||
 12                      '  and t3.a = ' || i ||
 13                      '  and t4.a = ' || i ||

 14                      '  and t5.a = ' || i into i_cnt;
 15    end loop;
 16  end;
 17  /

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:06.87


PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:19.89
SQL> declare
  2   i_cnt number;
  3  begin
  4    for i in 1..10000
  5
  6    loop
  7
  8    execute immediate 'select  count(*)'||
  9                      ' from t1,t2,t3,t4,t5        '||
 10                      'where t1.a = ' || i ||
 11                      '  and t2.a = ' || i ||
 12                      '  and t3.a = ' || i ||
 13                      '  and t4.a = ' || i ||
 14                      '  and t5.a = ' || i into i_cnt;
 15    end loop;
 16  end;
 17  /

PL/SQL 처리가 정상적으로 완료되었습니다.

경   과: 00:00:23.50

Ordered 힌트를 명시하면 옵티마이저는 모든 조인 순서를 고려하지 않고 사용자가 지정한 순서로만
실행계획 후보근을 선정하여 계산하므로 빠르고 최적화를 완료했다.

옵티마이저 힌트에 관한 일반적인 사용원칙은
1. 가급적 힌트를 사용을 자제하고, 옵티마이저가 스스로 좋은 선택을 할 수 있도록 돕는다.
2. 옵티마이저가 잘못된 선택을 할 ?만 힌트를 사용한다

한마디로 옵티마이저를 믿고 아니다싶으면 조작해라..


통계정보 : 오브젝트 통계, 시스템 통계

  • 통계정보는 옵티마이저에게 절대적인 영향을 미치는 요소중에 하나이다.
  • 자세한 내용은 4절과, 8절에 나온다.


옵티마이저 관련 파라미터



select name, value, isdefault, default_value
from v$sys_optimizer_env;


NAME                                     VALUE                     ISD DEFAULT_VALUE
---------------------------------------- ------------------------- --- -------------------------
parallel_execution_enabled               true                      YES true
optimizer_features_enable                10.2.0.1                  YES 10.2.0.1
cpu_count                                8                         YES 8
active_instance_count                    1                         YES 1
parallel_threads_per_cpu                 2                         YES 2
hash_area_size                           131072                    YES 131072
bitmap_merge_area_size                   1048576                   YES 1048576
sort_area_size                           65536                     YES 65536
sort_area_retained_size                  0                         YES 0
pga_aggregate_target                     198656 KB                 YES 198656 KB

parallel_query_mode                      enabled                   YES enabled

NAME                                     VALUE                     ISD DEFAULT_VALUE
---------------------------------------- ------------------------- --- -------------------------
parallel_dml_mode                        disabled                  YES disabled
parallel_ddl_mode                        enabled                   YES enabled
optimizer_mode                           all_rows                  YES all_rows
cursor_sharing                           exact                     YES exact
star_transformation_enabled              false                     YES false
optimizer_index_cost_adj                 100                       YES 100
optimizer_index_caching                  0                         YES 0
query_rewrite_enabled                    true                      YES true
query_rewrite_integrity                  enforced                  YES enforced
workarea_size_policy                     auto                      YES auto
optimizer_dynamic_sampling               2                         YES 2

NAME                                     VALUE                     ISD DEFAULT_VALUE
---------------------------------------- ------------------------- --- -------------------------
statistics_level                         typical                   YES typical
skip_unusable_indexes                    true                      YES true
optimizer_secure_view_merging            true                      YES true


오라클을 업그레이드로 인하여 발생되는 문제를 원치않는다면 
optimizer_features_enable 파라미터를 이전버전으로 설정 하면된다.

alter system set optimizer_features_enable = "9.2.0.4";


DBMS 버전과 종류



select max(empno) from emp

-- 위와같은 SQL을 오라클 7버전 까지는 최적화하려고 아래와같이 만들어서 사용

select /*+ index_desc(emp emp_pk) */ empno from emp
where rownum =1

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY   |        |      1 |        |       |            |          |      1 |00:00:00.01 |       1 |
|   2 |   INDEX FULL SCAN| PK_EMP |      1 |     14 |   182 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------------------------

-- 오라클 8버전부터는 index_desc 힌트를 사용하지않아도 최적화처리
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE            |        |      1 |      1 |    13 |            |          |      1 |00:00:00.01 |       1 |
|   2 |   INDEX FULL SCAN (MIN/MAX)| PK_EMP |      1 |     14 |   182 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------------------------------------


-- DBMS 종류에 따라 실행계획확인
-- 쿼리 : select min(empno) mx, max(empno) mn from emp

-- 오라클은 index_desing 형태로 나와야되는거같지만 index full scan을 선택한다.
-- mssql에서는 정상적으로 작동하고있다.
"구루비 데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3276

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입