오라클 (Oracle) Rule Based Optimizer

오라클 (Oracle) ∵ Rule Based Optimizer

RBO 실행 우선 순위

1. ROWID에 의한 단일행 검색

2. Cluster-Join에 의한 단일행 실행

3. Unique-Key, Primary-Key를 사용한 Hash-Cluster Key에 의한 단일행 실행

4. Unique-Key, Primary-Key에 의한 단일행 검색

5. Cluster 조인

6. Hash-Cluster Key

7. 인덱스화된 Cluster-Key

8. 복합 인덱스

9. 단일 컬럼 인덱스

10. 인덱스가 생성된 컬럼에 대한 제한된 범위 검색

11. 인덱스가 생성된 컬럼에 대한 무제한 범위의 검색

12. 정렬-병합 조인

13. 인덱스가 생성된 컬럼에 대한 MAX, MIN

14. 인덱스가 생성된 컬럼에 대한 ORDER BY

15. Full-Table Scan

---

Oracle Optimizer #3

 | ERP/SQL Tuning

2009/04/20 09:15

Rule Base Optimizer

요즘에도 Oracle Database Application을 개발하다 보면 RBO(Rule Base Optimizer)를 사용 하는 경우가 만다. RBO를 사용한다는 이야기는 사전에 정의 되어 있는 순의에 따라서 Query 최적화를 한다는 이야기 이다.

같은 순위라면 Where절의 뒤부터, From절 뒤의 객체가 우선 순위를 갖는다. 한 Table에서 같은 순위의 인덱스가 있다면 가장 최근에 만들어진 인덱스를 사용한다.

그러나 1992년 Oracle 7에서 CBO가 지원되면서 CBO는 계속적인 신기능의 적용으로 발전해 온 반면, RBO는 더 이상의 기능 향상은 없으며, 향후는CBO만 지원될 계획이다

Oracle 7을 Oracle 10g로 Upgrade 하시면 가장 두려워 하는 부분이 RBO에서 CBO로 변경 되나는 것에 많은 분들이 두려워 하는 것이다. 그러나 대세가 CBO임 만큼 대세에 역류 해서는 안된다고 생각 한다.

---

http://cafe.naver.com/ilovedata/6

미리 규정해 놓은 규칙대로 수행하는 Rule-based optimizer를 사용할것인지, 아니면 좀더 똑똑한 Cost-based optimizer를 사용할것인지 결정하는 것은 매우 중요한 일이다. 아직도 많은 사용자들이 rule-based optimizer를 사용하고 있는 것으로 보이는데 그 이유는 무었일까?

Cost-based optimizer로 변환을 시도한 많은 사용자들이 기대하였던 바와는 달리 성능의 저하를 경험하였을 것이다. 그리고 이것을 tuning할 시간을 갖지 못하거나 새로운 optimizer를 골치아프게 생각하게된 사용자들은 과거의 익숙한 경험인 rule-based optimizer를 고수하게 되는 경향이 있다.

Rule-Based Optimizer란 무었인가?

INIT.ora 에 OPTIMZER_MODE=RULE이라고 정의하던가, 아니면 OPTIMIZER_MODE를 CHOOSE, FIRST_ROWS 또는 ALL_ROWS라고 정의하였어도 ANALYZE를 수행하지 않아서 테이블및 인덱스의 통계치가 없으면 오라클은 rule-based optimizing을 하게 된다. 아니면 SELECT문의 hint로써 RULE을 정의하여도 rule-based optimizer를 사용할 수 있게 된다.

Rule-based optimizer는 다음과 같이 이미 정의되어 있는 규칙에 따른다. 규칙은 테이블내의 로우 갯수라든가 컬럼값의 분포같은 통계치와는 무관하다. Optimizer는 아래 규칙에서 가장 낮은 순위를 우선적으로 선택한다. 예를 들면 쿼리를 하는데 single column index를 이용하는 방법과 single row by unique or primary key를 이용하는 방법이 가능하다면 optimizer는 순위 4인 single row by unique or primary key를 선택하게 된다.

1. Single row by ROWID
2. Single row by cluster join
3. Single row by hash cluster with unique or primary key
4. Single row by unique or primary key
5. Cluster join
6. Hash cluster key
7. Indexed cluster key
8. Composite key
9. Single column indexes
10. Bounded range on index columns
11. Unbounded range on indexed columns
12. Sort merge join
13. MAX or MIN on indexed column
14. ORDER BY on indexed columns
15. FULL TABLE SCAN

하지만 이 규칙에서 벗어나는 경우도 있기는 하다. 어떤 경우에는 순위 15위인 FULL TABLE SCAN이 query를 drive하기도 한다. 예를 들면 테이블의 하나가 WHERE절을 가지지 않는 경우에서 이런 현상을 볼수 있다. 한테이블은 유니크 키를 가지고 다른 테이블은 single column non-unique index를 가지는 두개의 테이블을 조인하는쿼리에서 보통 낮은 순위의 규칙이 drive로 사용된다. 다음 예에서 각 테이블은 acct_no 컬럼을 index로 가지고 있다.

SELECT   /*+ RULE */  COUNT(*)
  FROM  acct , trans, acct_details
 WHERE  acct.acct_no = trans.acct_no
   AND  acct.acct_no = acct_details.acct_no
/

HASH JOIN
    TABLE ACCESS FULL ACCT_DETAILS
    NESTED LOOPS
         TABLE ACCESS FULL TRANS
         INDEX UNIQUE SCAN ACCT_NDX1

우리는 이러한 규칙 목록을 공부하여야 하고, 이것을 SQL Coding 할때 고려하여야 된다. 규칙 목록이 rule-based optimizer의 전부는 아니다. 이것을 이야기 해보도록 하자.

알아야 할 규칙 (하나)

테이블이 two-column index와 one-column index를 가지고 있고, WHERE절이 이들 3개의 column을 모두 참조한다고 했을때, one-column index가 unique 또는 primary key가 아닌 한 single index는 사용되지 않는다.

많은 사용자들이 오라클이 두개의 index를 모두 사용하여 index merge를 할것이라고 믿고 있다. 하나의 테이블에서 index merge는 index가 single column일 경우에만 수행된다. 이것은 cost-based나 rule-based에 모두 적용된다.

Rule-based optimizer가 driving table을 선정할때 다른 join table이 single column nonunique inde를 가지고 있다면 two-column index를 가진 table이 항상 driving table이 된다.

EMP_NDX1 (DEPT_NO)
EMP_NDX2(EMP_NO, EMP_NAME)
        
WHERE EMP_NAME = 'HAN'
  AND EMP_NO = 1
  AND DEPT_NO = 1

인덱스 EMP_NDX2만 사용된다.

알아야 할 규칙 (둘)

WHERE절이 two-column index의 한 컬럼과 single-column index의 컬럼을 사용할 경우에는 single-column index만이 사용된다.

EMP_NDX1 (EMP_NAME)
EMP_NDX2(EMP_NO, DEPT_NO)
        
WHERE EMP_NAME = 멒URRY?
   AND EMP_NO = 1
        
인덱스 EMP_NDX1만 사용된다.

알아야 할 규칙 (셋)

조건이 같은 경우에는 가장 최근에 생성된 인덱스만을 사용한다.

아래 예에서 같은 날 오후 4시와 5시에 각각 two-column index가 생성되었다. WHERE절이 이들 4개의 컬럼을 모두 참조할때 5시에 생성된 인덱스를 사용하게 된다.

EMP_NDX1 (EMP_NAME, EMP_CATEGORY) created 4pm
EMP_NDX2(EMP_NO, DEPT_NO) created 5pm
        
WHERE EMP_NAME = 'HAN'
  AND EMP_NO = 1
  AND DEPT_NO = 1
  AND EMP_CATEGORY = 'CLERK'

인덱스 EMP_NDX2만 사용된다.

알아야 할 규칙 (넷)

Three-column(A,B,C) index와 two-column(D,E) index가 있고 아래 예와 같이 WHERE절에서 5개의 컬럼을 모두 사용하는 경우일때, LIKE로 인하여 two-column index를 사용하게 된다. LIKE가 =보다 순위가 낮기 때문이다.

WHERE A=1
  AND B=1
  AND C LIKE '한%'
  AND D=1
  AND E=1

인데스(D,E)가 사용된다.

알아야 할 규칙 (다섯)

WHERE절이 사용하는 컬럼이 많이 포함된 인덱스가 사용된다.

WHERE A=1
  AND B=1
  AND C=1
  AND F=1
  AND G=1
        
75% 컬럼이 사용된 인덱스(A,B,C,D)가 선택된다.
65% 컬럼이 사용된 인덱스(F,G,H)는 선택되지 않는다.

알아야 할 규칙 (여섯)

드라이빙 테이블을 결정할때 첫번째 목록이 두번째 목록보다 우선한다.

1. Full Unique or Primary Key
2. All columns in index =
3. Higher % of columns

1. Not Unique or Primary Key
2. Most columns in index =
3. Lower % of columns

순위가 동등한 인덱스 경우에는 rule-based optimizer는 FROM 절에서 마지막에 기술한 인덱스를 선택한다.

일반적인 오해

• Oracle8은 rule-based optimizer를 지원하지 못한다......NOT TRUE
• Rule-based optimizer에서는 hint를 사용하지 못한다......NOT TRUE
• Rule-based optimizer로 잘 tuning된 application은 cost-based optimizer에서도 잘 돌아간다......불행하게도 그렇지 못하다. 추가적인 tuning작업이 필요하다.

Rule-based optimizer의 일반적인 문제

1.	잘못된 드라이빙 테이블	55%
2.	잘못된 드라이빙 인덱스	25%
3.	부적당한 인덱스 사용	15%
4.	기타	5%

1. 잘못된 드라이빙 테이블

Cost-based optimizer에서 rule-base optimizer로 변경하였을때 잘못된 드라이빙 테이블이 선정되는것을 자주 경험하게 된다. 드라이빙 테이블이 잘못 선정되었을때 그 영향을 알아보기 위하여 다음을 고려하여 보자

TRANS 테이블을 검색하는 문장이 있는데 TRANS의 COST_CENTRE는 ACCT테이블의 ACCT_ID와 같은 의미로 두개의 테이블을 JOIN할수 있게 한다. TRANS 테이블에는 '대구'라는 값을 가진 20,000 이상의 row가 있다. ACCT 테이블에는 ACCT_ID=9의 값을 가진 row가 단 9row뿐이다. TRANS 테이블에는 ACCT_ID=9이고 COST_CENTRE='대구'인 row는 하나도 없다.

ACCT 테이블은 인덱스 ACCT_NDX1(ACCT_ID)를 가지고, TRANS 테이블은 인덱스 TRANS_NDX1(ACCT_ID)와 TRANS_NDX2(COST_CENTRE)가 있다.

다음 문장은 cost-based optimizer로 수행되었고 두개의 table은 모두 ANALYZE되어 있었다.
EXPLAIN PLAN을 보면 ACCT가 드라이빙 테이블이 되었고,TRANS 테이블은 NAD-EQUAL에서 보듯이 두개의 인덱스가 merge되었다.

   1 select count(*) from trans a, acct b
   2 where a.cost_centre = '대구'
   3   and b.acct_id = 9
   4 * and a.acct_id = b.acct_id

   real: 1977

Execution Plan
----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE
       1    0   SORT (AGGREGATE)
       2    1     NESTED LOOPS
       3    2       INDEX (RANGE SCAN) OF 'ACCT_NDX1' (NON-UNIQUE)
       4    2       AND-EQUAL
       5    4         INDEX (RANGE SCAN) OF 'TRANS_NDX1' (NON-UNIQUE)
       6    4         INDEX (RANGE SCAN) OF 'TRANS_NDX2' (NON-UNIQUE)

다음은 테이블의 통계치를 삭제하여 rule-based optimizer를 적용시킨 것이다. 나는 ANALYZE TABLE ACCT DELETE STATISTICS 와 ANALYZE TABLE TRANS DELETE STATISTICS를 수행시켰다.

EXPLAIN PLAN을 보면 TRANS를 드라이빙 테이블로 사용하였다. Rule-based에서 인덱스들의 순위가 동등할 경우 FROM 절에 기술된 마지막 테이블부터 선택된다는것을 기억할 것이다. 결과는 cost-based에 비하여 12배의 시간을 필요로 하게 되었다.

   1 select count(*) from acct a, trans b
   2 where b.cost_centre = '대구'
   3   and a.acct_id = 9
   4 * and a.acct_id = b.acct_id

   real: 19772

Execution Plan
----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE
       1     0  SORT (AGGREGATE)
       2     1    NESTED LOOPS
       3     2      TABLE ACCESS (BY ROWID) OF 'TRANS'
       4     3        INDEX (RANGE SCAN) OF 'TRANS_NDX1' (NON-UNIQUE)
       5     2      INDEX (RANGE SCAN) OF ACCT_NDX1' (NON-UNIQUE)

TRANS 테이블에는 ACCT_ID와 COST_CENTRE 두개의 컬럼이 모두 있다. 문장에서 3번째 ACCT_ID가 ACCT.ACCT_ID가 아니라 TRANS.ACCT_ID로 되었더라면 rule-based optimizer는 TRANS 테이블을 드라이빙 테이블로 선택하지 않았을 것이다. TRANS.ACCT_ID=9인 경우에 EXPLAIN은 다음과 같았다.

   1 select count(*) from trans a, acct b
   2 where a.cost_centre = '대구'
   3   and a.acct_id = 9
   4 * and a.acct_id = b.acct_id

   real: 1904

Execution Plan
----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE
       1    0   SORT (AGGREGATE)
       2    1     NESTED LOOPS
       3    2       AND-EQUAL
       4    3         INDEX (RANGE SCAN) OF 'TRANS_NDX1' (NON-UNIQUE)     
       5    3         INDEX (RANGE SCAN) OF 'TRANS_NDX2' (NON-UNIQUE)
       6    2       INDEX (RANGE SCAN) OF 'ACCT_NDX1' (NON-UNIQUE)

이렇게 하기 위하여 문제는 SQL을 변경하여야 한다는 것이다. Package로 된 SQL을 변경한다는것은 불가능하다. 드라이빙 테이블을 어떻게 변경할 것인가?
우 리가 할수 있는것은 TRANS 테이블에서 COST_CENTRE 인덱스를 삭제하고 ACCT_ID, COST_CENTRE 인덱스를 새로 만드는 것이다. 이 방법은 COST_CENTRE값이 적어도 10,000 rows이상일 경우에 효과가 있을 수 있다.

       SQL>   1   select count(*) from acct a, trans b
       SQL>   2   where b.cost_centre = '대구'
       SQL>   3     and a.acct_id = 9
       SQL>   4*    and a.acct_id = b.acct_id;


       real: 1653

       Execution Plan
       ----------------------------------------------------------
          0      SELECT STATEMENT Optimizer=CHOOSE
          1    0   SORT (AGGREGATE)
          2    1     NESTED LOOPS
          3    2       INDEX (RANGE SCAN) OF 'ACCT_NDX1' (NON-UNIQUE)
          4    2       INDEX (RANGE SCAN) OF 'TRANS_NDX3' (NON-UNIQUE)

2. 잘못된 드라이빙 인덱스

TRANS 테이블이 두개의 single-column index를 가지고 있다고 가정하자. TRANS 는 TRANS_NDX2(ACCT_ID)와 TRANS_NDX3(COST_CENTRE) 인덱스를 가지고 있다. Rule-based optimizer입장에서 이 두개의 index는 acess하는데 차이가 없다. 모두 규칙 순위 9 아래이다.

Rule-based optimizer에서 WHERE절의 순서는 중요하다. TRANS 테이블은 600,000 rows를 가지고 있는데 COST_CENTRE='대구'값을 갖는것은 170,000 rows이고, ACCT_ID=9인 것은 단 3개뿐이다. COST_CENTRE가 WHERE절 선두에 기술된 다음 문장은 그 인덱스가 드라이빙 인덱스로 사용되었음을 주목하라. Response time은 4초를 상회하였다.

select count(*) from trans
 where cost_centre='대구'
   and acct_id = 9;
        
real: 4225
        
Execution Plan
----------------------------------------------------------
      0      SELECT STATEMENT Optimizer=CHOOSE
      1    0   SORT (AGGREGATE)
      2    1     AND-EQUAL
      3    2       INDEX (RANGE SCAN) OF 'TRANS_NDX3' (NON-UNIQUE)
      4    2       INDEX (RANGE SCAN) OF 'TRANS_NDX2' (NON-UNIQUE)

WHERE절의 순서를 보다 적은 row를 가지는 ACCT_ID로 변경하니까 런타임이 25%이상 줄었다. 드라이빙 인덱스 역시 변경되었다.

select count(*) from trans
  where acct_id = 9
   and cost_centre='대구';
        
 real: 1044
        
Execution Plan
----------------------------------------------------------
      0      SELECT STATEMENT Optimizer=CHOOSE
      1    0   SORT (AGGREGATE)
      2    1     AND-EQUAL
      3    2       INDEX (RANGE SCAN) OF 'TRANS_NDX2' (NON-UNIQUE)
      4    2       INDEX (RANGE SCAN) OF 'TRANS_NDX3' (NON-UNIQUE)

3. 부적당한 인덱스 사용

1,000,000 rows를 가진 TRANS 테이블을 생각해보자. 이 테이블에는 BUSINESS_CENTRE, ACCT_ID, TRANS_DATE의 3개 컬럼으로 이루어진 인덱스가 있다. 각각 대략 330,000 rows씩을 가지는 3개의 business center가 있다. 각 ACCT_ID와 TRANS_DATE의 조합은 최대 20 rows정도가 된다. 이 인덱스는 아래 쿼리를 위한 것이었다.

SELECT BUSINESS_UNIT, ACCT_ID, SUM(AMOUNT)
  FROM TRANS
 WHERE ACCT_ID BETWEEN 1000 AND 1100
   AND BUSINESS_UNIT='TIN'
   AND TRANS_DATE > SYSDATE - 7
 GROUP BY BUSINESS_UNIT, ACCT_ID;

인덱스는 이 문장을 위해서 아주 효과적으로 사용되었다.

어떤 배치 작업이 다음 쿼리를 수행한다면 여기서도 rule-based optimizer는 인덱스를 사용하였을 것이고, 테이블을 full scan하는것보다 훨씬 오랬동안 배치 작업은 끝나지 못했을 것이다.

SELECT SUM(AMOUNT)
  FROM TRANS
 WHERE BUSINESS_UNIT='TIN'
 GROUP BY BUSINESS_UNIT;

그 이유는 UNIT을 위한 330,000 번에다가 인덱스를 또 읽어야 했기 때문이다. 이것은 330,000번의 물리적인 read를 한것과 같다. FULL TABLE SCAN을 하였을 경우 한 블록에 100개의 row가 있다고 했을때 330,000 row를 읽는데는 약 10,000번만 읽으면 족했을것이다.



출처 : http://home.hanmir.com/~mhan

 

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

흑기사
날짜:2004-03-09 11:03:00
조회수:20