About DATABASE

Oracle 10g R2에서 사람들이 전혀 눈치채지 못한 큰 변화가 있었다.
흔히 MBRC라고 줄여서 부르는 db_file_multiblock_read_count 파리미터가 공식적으로 몰락한 것이다.
물론 여전히 사용 가능하지만, 그 내부 작동 방식으로 보아서 이 파라미터는 사실상 가치가 없어진 셈이다.

사실 이러한 조짐은 이전부터 있었다.
바로 System Statistics가 9i에서 소개된 것이다. 9i에서 System Statistics를 수집하면 다음과 같은 데이터가 sys.aux_stats$ 테이블에 저장된다.

SQL> SELECT * FROM sys.aux_stats$;
SNAME    PNAME    PVAL1    PVAL2
SYSSTATS_INFO    STATUS        COMPLETED
SYSSTATS_INFO    DSTART        12-30-2007 15:21
SYSSTATS_INFO    DSTOP        12-30-2007 15:21
SYSSTATS_INFO    FLAGS    1  
SYSSTATS_MAIN    SREADTIM    1         <-- Single Block Read Time
SYSSTATS_MAIN    MREADTIM    2        <-- Mulit Block Read Time
SYSSTATS_MAIN    CPUSPEED    500     <-- CPU Speed
SYSSTATS_MAIN    MBRC    8               <-- Multi Block Read Count
SYSSTATS_MAIN    MAXTHR    -1  
SYSSTATS_MAIN    SLAVETHR    -1

System Statistics가 없는 상황에서는 db_file_multiblock_read_count, optimizer_index_cost_adj와 같은 파라미터들이 Index Scan과 Table Scan중 어느 것을 선택할지를 결정하는데 큰 역할을 한다.

하지만, System Statistics가 수집된 경우에는?
위의 값들을 보면 알겠지만, System Statistics는 Index Scan과 Table Scan을 결정하는데 필요한 모든 중요한 정보들을 다 가지고 있다. 따라서 Oracle은 SREADTIME, MBREADTIM, CPUSPEED, MBRC 등의 정보를 이용해 비용을 계산한다.

그러면, System Statistics가 활성화되어 있으면 db_file_multiblock_read_count 파라미터는 무시되는 것인가? Optimizer에 의해 실행 계획이 생성될 때는 무시되지만 실제 Fetch과정에는 db_file_multiblock_read_count 크기만큼 Multi Block I/O를 한다.

즉, Optimizer에 의해서는 사용되지 않고 실제 쿼리를 실행하고 Fetch하는 단계에서만 사용된다.

이러한 원리는 Oracle 10g R1에서도 거의 비슷하다. Oracle 9i와 10g의 차이점은 System Statistics가 없을 때의 동작 방식이다. Oracle 10g에서는 System Statistics에 다음과 같은 항목이 추가되었다.

SNAME    PNAME    PVAL1    PVAL2
SYSSTATS_MAIN    CPUSPEEDNW    1845.90945194599  
SYSSTATS_MAIN    IOSEEKTIM    10  
SYSSTATS_MAIN    IOTFRSPEED    4096  

즉, 기본적인 CPU Speed와 IO Seek Time, IO Transfer Speed 값이 추가되었다. 이것을 흔히 "Noworkload" System Statistics라고 부른다. 말 그대로 Oracle이 하는 일과 무관하며 순수하게 Hardware로부터 취득한 정보라는 의미이다. Oracle은 Noworkload에 대해서는 적절한 기본값을 가지고 있으며, 필요하다면 이 값을 이용한다.

Oracle 10g R1은 System Statistics가 없으면 CPUSPEEDNW, IOSEEKTIM, IOTFRSPEED 값과 db_file_multiblock_read_count 값을 함께 이용해서 비용을 계산한다. 즉, db_file_multiblock_read_count 파라미터가 사용되기는 하되 9i와 같이 직접 사용되는 것이 아니라 Noworkload 통계 정보와 함께 조합되어서 사용된다.

하지만, Oracle 10g R2에서 또 한번의 변화가 생겼다. Oracle 10g R2에는 Multi Block I/O와 관련된 파라미터가 다음과 같이 세개로 늘어났다.

    * db_file_multiblock_read_count
    * _db_file_optimizer_read_count
    * _db_file_exec_read_count

즉, db_file_multiblock_read_count라는 파라미터가 Optimizer가 사용할 값(_db_file_optimizer_read_count)과 실행시에 사용할 값(_db_file_exec_read_count)으로 세분화된 것이다.
이 파라미터들의 사용방식은 다음과 같다.

1. _db_file_optimizer_read_count 파라미터는 System Statistics가 없는 경우에 Optimizer가 비용을 계산한기 위해 사용한다(앞서 설명한 바와 같이 Noworkload 통계값과 같이 사용). System Statistics가 수집된 경우에는 이 값은 무시된다.

2. _db_file_exec_read_count 파라미터는 쿼리를 실행하는 과정에서 Multi Block I/O를 수행할 때 한번에 읽을 블록수를 결정한다. 이 값은 System Statistics의 수집 여부와 무관하게 사용된다.

3. db_file_optimizer_read_count 파라미터값을 명시적으로 변경하면 _db_file_optimizer_read_count 값과 _db_file_exec_read_count 값이 모두 같이 변경된다.

전체적인 사용방식은 이전 버전과 동일하지만, db_file_multiblock_read_count 파라미터가 사실상 없어진 것과 마찬가지이며, 목적에 따라 명확하게 구분된 두 개의 히든 파라미터로 나뉘어졌다. 우리가 설사 db_file_multiblock_read_count 값을 지정하더라도 Oracle은 내부적으로 서로 다른 두 개의 히든 파라미터를 사용하는 셈이다.

언뜻 복잡해 보이는 위의 논의들은 사실은 다음과 같은 결론을 위한 것이다.

"System Statistics 정보를 잘 수집하자. 너무나 좋은 기능이다."

System Statistics 정보를 이용하면 기존에 Optimizer의 부족한 판단을 보완해주기 위해 db_file_multiblock_read_count, optimizer_index_cost_adj 같은 파라미터의 값을 변경해줄 수고가 줄어들며, 훨씬 정확한 정보에 기반한 비용 계산이 가능해진다. 더불어 db_file_multiblock_read_count 파라미터는 더 이상 Optimizer에게 영향을 주지 못하고 말 그대로 Multi Block I/O를 실제로 수행할 경우에만 사용되므로 훨씬 직관적이고 오해의 소지가 없는 셈이다.

많은 시스템들이 이 정보를 잘 사용하고 있지만, 특정 시스템은 아직 잘 모르거나 아니면 새로운 기능에 대한 두려움 때문에 사용하지 못하고 있을 것이다. 만일 사용하고 있지 않다면 반드시 테스트해보기 바란다.

Oracle 11g에서는 또 한번의 변화가 있는 것 같다. _db_file_optimizer_read_count 파라미터의 값이 10g R2에서는 8이다. Table Scan을 지나치게 선호하지 않도록 비교적 낮은 값을 지정한 것으로 보인다. 하지만 11g에서는 놀랍게도 기본값이 128로 지정되어 있다. 사용 방식이 바뀐 것인지...? 이 부분은 추후에 테스트가 필요할 것이다.

출처 : http://ukja.tistory.com/85

[Hint]조인 방법 변경(USE_NL)

조인 방법 변경(USE_NL) 테이블을 조인 하는 경우 중첩 루프 조인(Nested Loop Join)이 일어나도록 하는 힌트 문장 입니다. 중첩 루프 조인은 중첩 반복이라고도 하는데 하나의 테이블(outer/driving table)에서 추출된 로우를 가지고 일일이 다른 테이블(inner/probed table)을 반복해서 조회하여 찾아지는 레코드를 최종 데이터로 간주하는 방법 입니다. 즉 조인 입력 한 개를 외부 입력 테이블로 사용하고, 한 개는 내부(최하위) 입력 테이블로 사용하고 외부 루프는 외부 입력 테이블을 행 단위로 사용하고 각 외부 행에 대해 실행되는 내부 루프는 내부 입력 테이블에서 일치되는 행을 검색 하는거죠…  이것을 원시 중첩 루프 조인이라고 하는데 검색에서 인덱스를 사용하는 경우에는 인덱스 중첩 루프 조인이라고 합니다. 예를 들어 EMP 테이블과 DEPT 테이블을 조인하는 경우 dept 테이블이 건수가 작다면 우선 이 테이블을 외부 루프로 해서 하나씩 읽으면서 이에 대응하는 emp 테이블의 데이터를 추출 하는 경우라면 중첩 루프 조인에 해당 합니다. 이때 emp 테이블의 경우 건수가 많다고 가정을 하면 대부분 인덱스를 이용하도록 emp 테이블의 외래키인 deptno 컬럼은 대부분 인덱스를 걸게 되죠^^ 중첩 루프 조인은 테이블중 적어도 하나의 조인 컬럼에 대해 인덱스(or Hash Index)가 존재할 때 연관되는 방식으로 이 중첩 루프 조인에서 테이블중 하나의 테이블 또는 중간 결과 셋을 대상으로 FULL SCAN이 일어나게 됩니다. 이 테이블이 드라이빙 테이블이 되는데… 이 테이블의 데이터 건마다 나머지 테이블에서 원하는 데이터를 추출하기 위해 대부분 인덱스를 사용하게 되는 겁니다. 보통 USE_NL 힌트 구문은 ORDERED 힌트 구문과 같이 사용되는데 USE_NL이 취하는 인자는 FROM절에서 두번째 나오는 테이블(비드라이빙 테이블, inner/probed table)을 명시해 주어야 합니다. 안수로 사용되지 않은 첫 번째 테이블은  outer/driving table이 되는 것입니다. [형식] /*+ USE_NL ( table [table]... ) */ [예] 아래는 Oracle 10g에서 테스트 한 결과 입니다. analyze table emp compute statistics analyze table dept compute statistics select /*+ORDERED USE_NLe) */        e.ename,            d.dname from   dept d, emp e where  e.deptno = d.deptno         ------------------------------------------------------------ Operation        Object Name        Rows        Bytes        Cost         --------------------------------------------------------------- SELECT STATEMENT Optimizer Mode=ALL_ROWS                14                   4   TABLE ACCESS BY INDEX ROWID        SCOTT.EMP        4          32          1               NESTED LOOPS                14          266          4                                         TABLE ACCESS FULL        SCOTT.DEPT        4          44          3                           INDEX RANGE SCAN        SCOTT.IDX_EMP_DEPTNO        5                   0           FROM절에서 처음 나타나는 테이블이 드라이빙 테이블(DRIVING/OUTER? TABLE)이며 비드라이빙 테이블(PROBE/INNER TABLE)이 USE_NL의 인자로 들어갑니다!! select /*+ORDERED USE_NL(D) */        e.ename,            d.dname from   emp e, dept d where  e.deptno = d.deptno         -------------------------------------------------------------- Operation        Object Name        Rows        Bytes        Cost         -------------------------------------------------------------- SELECT STATEMENT Optimizer Mode=ALL_ROWS                14                   3   NESTED LOOPS                14          266          3                                       TABLE ACCESS BY INDEX ROWID        SCOTT.EMP        14          112          2                 INDEX FULL SCAN        SCOTT.IDX_EMP_DEPTNO        13                   1     TABLE ACCESS BY INDEX ROWID        SCOTT.DEPT        1          11          1                 INDEX UNIQUE SCAN        SCOTT.PK_DEPT        1                   0                                                                이번에는 USE_MERGE와 ORDERED가 같이 쓰이는 경우인데 이 경우엔 FROM 절 뒤 테이블의 순서는 실행계획은 다르게 나티날지 모르지만 성능에는 영향을 미치지 않습니다. 왜냐구요? 위 내용을 읽어 보세요!! select /*+ORDERED USE_MERGE(D) */        e.ename,            d.dname from   emp e, dept d where  e.deptno = d.deptno         -------------------------------------------------------------- Operation        Object Name        Rows        Bytes        Cost         ------------------------------------------------------------- SELECT STATEMENT Optimizer Mode=ALL_ROWS                14                   6   MERGE JOIN                14          266          6                                                           TABLE ACCESS BY INDEX ROWID        SCOTT.EMP        14          112          2                 INDEX FULL SCAN        SCOTT.IDX_EMP_DEPTNO        13                   1     SORT JOIN                4          44          4                                                             TABLE ACCESS FULL        SCOTT.DEPT        4          44          3                                                       select /*+ ORDERED USE_MERGE(E) */        e.ename,            d.dname from   dept D, emp E where  e.deptno = d.deptno         ---------------------------------------------------------------- Operation        Object Name        Rows        Bytes        Cost         -------------------------------------------------------------- SELECT STATEMENT Optimizer Mode=ALL_ROWS                14                   5   MERGE JOIN                14          266          5                                                           TABLE ACCESS BY INDEX ROWID        SCOTT.DEPT        4          44          2                 INDEX FULL SCAN        SCOTT.PK_DEPT        4                   1               SORT JOIN                14          112          3                                                             TABLE ACCESS BY INDEX ROWID        SCOTT.EMP        14          112          2                   INDEX FULL SCAN        SCOTT.IDX_EMP_DEPTNO        13                   1

http://blog.paran.com/oraclejava/9910752

 

 

========================================================================================================================
========================================================================================================================

 

 

NESTED LOOP JOIN

 

선행적 특징을 작는데 먼저 액세스되는 테이블의 처리범위에 의해 처리량이 결정됨
Driving Table에 의해 범위가 결정되며 Driving Table의 범위가 적을수록 수행속도는 빨라진다
고로 Driving Table을 어던 테이블로 결정하느냐가 중요하다

-. /*+ use_nl (테이블) */
-. 나중에 처리되는 테이블은 앞서 처리된 값을 받아 액세스하게됨, 즉 값을 받아서 처리범위가 정해짐
-. Driving Table의 인덱스 액세스는 첫번 로우만 Random Access이고, 나머지는 Scan, 연결작업은 Random Access임
-. 연결되는 방향에 따라 사용되는 인덱스들이 달라질 수 있음
-. 연결고리 인덱스 유무에 따라 액세스 방향 및 수행속도에 많은 차이가 있음
-. 연결작업 수행 후 체크되는 조건으로 부분범위처리를 하는 경우에는 조건의 범위가 넓거나 없다면 오히려 빨라짐

-. 전체가 아닌 부분범위 처리를 하는 경우 유리함
-. 조인되는 테이블중 어느 한쪽의 추출된 결과를 받아야 처리범위를 줄일 수 있는 상태라면 항상 유리함
-. Driving Table의 처리량이 많거나 연결 테이블의 Random Access량이 많을 경우에는 분리함
-. 일반적으로 처리량이 적은 경우로서 Random Access를 많이 하므로, 온라인 어플리에서 유리함
-. Driving Table의 선택이 관건임

 

SORT MERGE JOIN

 

일반적으로 배치작업에서 주로 사용되며, 각 테이블을 Sort한 후 Merge 하는 조인을 말한다

 

-. /*+ use_merge(테이블) */
-. 동시에 각각의 테이블이 자신의 처리범위를 액세스하여 정렬해둠
-. 각 테이블은 어떠한 상수값도 서로 영향을 주지 않으며, 주어진 상수값에 의해서만 각자 범위를 줄이게됨
-. 전체범위처리를하며 부분범위처리를 할수 없음
-. 자신의 처리범위를 줄이기 위해 인덱스를 사용하는 경우에만 Random Access이고, Merge작업은 Scan방식
-. 선택적으로 연결고리가 되는 컬럼은 인덱스를 사용하지 않음
-. 조인의 방향과는 상관없음
-. Equal 조인에서만 가능

-. 처리량이 많은 경우로 Random Access를 하지 않음으로 전체범위처리에 유리
-. 자신의 처리범위를 인덱스를 통해 어떻게 줄이느냐가 관건
-. 상수값을 받아 줄여진 범위가 30%이상이면 Sort Merge가 유리

 

 

HASH JOIN

 

Hash Function을 이용해서 메모리와 CPU를 많이 사용해서 일반적으로 배치작업에서 주로 사용됨

-. /*+ use_hash(테이블) */
-. 적은테이블과 큰테이블의 조인시에 유리
-. Equal 조인에서만 가능
-. Driving Table에 인덱스를 필요로 하지 않고 각 테이블을 한번만 읽음
-. 다른조인방법보다 CPU자원을 많이 소비하며 양쪽 테이블의 scan이 동시에 일어남

 

from) http://www.jakartaproject.com/

Nested Loop Join과 Sort Merge Join

 

Nested Loop Join

 

Nested Loop Join이란 먼저 어떤 테이블의 처리범위를 하나씩 액세스하면서 그 추출된 값으로 연결할 테이블을 조인하는 방식이다.

 

1. 특징

1) 순차적으로 처리된다. 선행테이블(Driving table)의 처리범위에 있는 각각의 로우들이 순차적으로 수행될 뿐만 아니라 테이블간의 연결도 순차적이다.

2) 먼저 액세스되는 테이블(Driving Table)의 처리범위에 의해 처리량이 결정된다.

3) 나중에 처리되는 테이블은 앞서 처리된 값을 받아 액세스된다. 즉, 자신에게 주어진 상수값에 의해 스스로 범위를 줄이는 것이 아니라 값을 받아서 처리범위가 정해진다.

4) 주로 랜덤 액세스 방식으로 처리된다. 선행 테이블의 인덱스 액세스는 첫번째 로우만 랜덤 액세스이고 나머지는 스캔이며 연결작업은 모두 랜덤 액세스이다.

5) 주어진 조건에 있는 모든 컬럼들이 인덱스를 가지고 있더라도 모두가 사용되는 것은 아니다. 연결되는 방향에 따라 사용되는 인덱스들이 전혀 달라질 수 있다.

6) 연결고리가 되는 인덱스에 의해 연결작업이 수행되므로 연결고리 상태가 매우 중요하다. 연결고리의 인덱스 유무에 따라 액세스 방향 및 수행속도에 많은 차이가 발생된다.

7) 연결작업 수행 후 마지막으로 check되는 조건은 부분범위처리를 하는 경우에는 조건의 범위가 넓을수록, 아예 없다면 오히려 빨라진다.

 

2. 사용기준

1) 부분범위처리를 하는 경우에 주로 유리해진다.

2) 조인되는 어느 한쪽이 상대방 테이블에서 추출된 결과를 받아야 처리범위를 줄일 수 있는 상태라면 항상 유리해진다.

3) 주로 처리량이 적은 경우(많더라도 부분범위처리가 가능한 경우)에 유리해진다. 그것은 처리방식이 주로 랜덤 액세스방식이므로 많은 양의 랜덤 액세스가 발생한다면 수행속도가 당연히 나빠지기 때문이다.

4) 가능한 한 연결고리 이상 상태를 만들지 않도록 주의해야 한다.

5) 순차적으로 처리되기 때문에 어떤 테이블이 먼저 액세스되느냐에 따라 수행속도에 많은 영향을 미치므로 최적의 액세스 순서가 되도록 적절한 조치가 요구된다.

6) 부분범위처리를 하는 경우에는 운반단위 크기가 수행속도에 많은 영향을 미칠 수 있다. 운반단위가 적을 수록 빨리 운반단위를 채울 수 있으나, 폐치(Fetch) 횟수에서는 불리해지는 이중성을 가지고 있다.

7) 선행 테입ㄹ의 처리 범위가 많거나 연결 테이블의 랜덤 액세스의 양이 아주 많다면 Sort Merge 조인보다 불리해지는 경우가 많다.

 

Sort Merge Join

 

Sort Merge Join이란 양쪽 테이블의 처리범위를 각자 액세스하여 정렬한 결과를 차례로 스캔하면서 연결고리의 조건으로 머지해 가는 방식을 말한다. 이 방식은 경우에 따라 Nested Loop Join보다 훨씬 빨라지는 경우도 많이 있으며 랜덤 액세스가 줄어들어 시스템의 부하를 감소시키지만 일반적으로 Nested Loop Join 보다는 사용되는 빈도가 적은 편이다.

이 방식의 가장 큰 특징은 상대방에게 아무런 값도 받지 않고 자신이 가지고 있는 조건만으로 처리범위가 정해지며, 랜덤 액세스를 줄일 수는 있으나 항상 전체범위처리를 한다는 것이다.

 

1. 특징

1) 동시적으로 처리된다. 테이블 각자가 자신의 처리범위를 액세스하여 정렬해 둔다.

2) 각 테이블은 다른 테이블에서 어떠한 상수값도 제공받지 않는다. 즉, 자신에게 주어진 상수값에 의해서만 범위를 줄인다.

3) 결코 부분범위처리를 할 수가 없으며, 항상 전체범위처리를 한다.

4) 주로 스캔방식으로 처리된다. 자신의 처리범위를 줄이기 위해 인덱스를 사용하는 경우만 랜덤 액세스이고 머지작업은 스캔방식이다.

5) 주어진 조건에 있는 모든 컬럼들이 인덱스를 가지고 있더라도 모두가 사용되는 것은 아니다. 연결고리가 되는 컬럼은 인덱스를 전혀 사용하지 않는다.

6) 조인의 방향과는 전혀 무관하다.

7) 스스로 자신의 처리범위를 줄이기 위해 사용되는 인덱스는 대개 가장 유리한 한가지만 사용되어진다. 그러나 그 외의 조건들은 비록 인덱스를 사용하지 못하더라도 작업대상을 줄여 주기 때문에 중요한 의미를 가진다.

 

2. 사용기준

1) 전체범위처리를 하는 경우에 주로 유리해진다.

2) 상대방 테이블에서 어떤 상수값을 받지 않고도 처리범위를 줄일 수 있는 상태인 경우 주로 유리해 질 수 있다. 상수값을 받아 처리(Nested Loop Join)한 범위의 크기와 처리범위를 줄여 처리(Sort Merge Join)한 범위의 크기를 대비해보아 상수값을 받아 줄여진 범위가 약 30% 이상이라면 Sort Merge Join이 일반적으로 유리해진다. 그러나 부분범위처리가 되는 경우라면 전혀 달라질 수 있다. 이런 경우는 처리할 전체범위를 비교하지 말고 첫번째 운반단위에 도달하기 위해 액세스하는 범위애 대해서 판단해야 한다.

3) 주로 처리량이 많은 경우 (항상 전체범위처리를 해야 하는 경우)에 유리해진다. 그것은 처리방식이 주로 스캔방식이므로 많은 양의 랜덤 액세스를 줄일 수가 있기 때문이다.

4) 연결고리 이상 상태에 영향을 받지 않으므로 연결고리를 위한 인덱스를 생성할 필요가 없을 때 유용하게 사용할 수 있다.

5) 스스로 자신의 처리범위를 어떻게 줄일 수 있느냐가 수행속도에 많은 영향을 미치므로 보다 효율적으로 액세스할 수 잇는 인덱스 구성이 중요한다.

6) 전체범위처리를 하므로 운반단위의 크기가 수행속도에 영향을 미치지 않는다. 가능한 운반단위를 크게 하는 것이 페치(Fetch) 횟수를 줄여준다. 물론 지나치게 큰 운반단위는 시스템에 나쁜 영향을 미친다.

7) 처리할 데이터량이 적은 온라인 애플리케이션에서는 Nested Loop Join이 유리한 경우가 많으므로 함부로 Sort Merge Join을 사용하지 말아야 한다.

8) 옵티마이저 목표(Goal)가  "ALL_ROWS"인 경우는 자주 Sort Merge Join으로 실행계획이 수립되므로 부분범위처리를 하고자 한다면 이 옵티마이져 목표가 어떻게 지정되어 있는지에 주의하여야 한다.

Oracle 10g에서 Online Segment Shrink 기능이 추가되면서, 동적으로 Segment의 크기를 줄여 줄 수 있게 되었다. 그러면 다음과 같은 질문을 할 수 있겠다.

"Shrink 대상이 되는 Segment(Table/Index/Partition) 목록을 어떻게 추출할 것인가?"

이런 작업을 수동으로 하려면 dbms_space 패키지를 이용한 일련의 복잡한 Script 작업이 필요하다. Segment 수가 많고 크기가 크다면 많은 시간과 리소스를 필요로 하는 일이 되어 버린다.

다행히 Oracle 10g에서 이러한 작업을 자동화하는 기능이 추가되었다.
아래와 같이 dba_scheduler_job 뷰를 조회해보면, auto_space_advisor_job이라는 스케쥴 작업이 등록되어 있고, 이 작업은 auto_space_advisor_prog이라는 프로그램을 수행한다.

select job_name, program_name
from dba_scheduler_jobs
where job_name like '%SPACE%';
-----------------------------------------------------
JOB_NAME               : AUTO_SPACE_ADVISOR_JOB      
PROGRAM_NAME           : AUTO_SPACE_ADVISOR_PROG

auto_space_advisor 프로그램은 dbms_space.auto_space_advisor_job_proc이라는 프로시저를 수행한다.

select program_name, program_action
from dba_scheduler_programs
where program_name = 'AUTO_SPACE_ADVISOR_PROG';
------------------------------------------------------------
PROGRAM_NAME          : AUTO_SPACE_ADVISOR_PROG   
PROGRAM_ACTION        : dbms_space.auto_space_advisor_job_proc

Auto Space Advisor는 Advisor의 한 종류로 10g에서 새로 추가된 Advisor이다. 이 Advisor는 Tablespace/Segment의 공간 사용 정도를 분석하여 공간을 절약하기 위해 적절한 충고/가이드를 하는 역할을 제공한다.

Auto Space Advisor에 의해 만들어진 가이드는 다음과 같은 방법으로 간편하게 조회 가능하다.

-- dbms_space.verify_shrink_candidate(_tbf) 이용
select * from
table(dbms_space.verify_shrink_candidate_tbf(user,'BIG_TABLE','TABLE',273395165));

declare
    b_shrinkable boolean;
begin
    b_shrinkable := dbms_space.verify_shrink_candidate
        (user,'T_SHRINK','TABLE',1000);
    if b_shrinkable then
        dbms_output.put_line('Shrinkable');
    else
        dbms_output.put_line('Unshrinkable');
    end if;
end;
/

-- dbms_space.asa_recommendations 이용
select * from table(dbms_space.asa_recommendations());

-- dbms_space.asa_recommendations은 다음과 같이 어떤 Segment가 얼마나 공간을 절약할 수 있고, 어떤 명령문을 사용하면 되는지 친절하게 알려준다.
-------------------------------------------------------------
SEGMENT_NAME                  : BIG_TABLE
SEGMENT_TYPE                  : TABLE
ALLOCATED_SPACE               : 300619974
USED_SPACE                    : 273395165
RECLAIMABLE_SPACE             : 27224809
RECOMMENDATIONS               : OWI.BIG_TABLE 테이블의 행 이동을 가능하게 하고 축소 작업을 수행하는 경우 예상되는 절약 공간은 27224809바이트입니다.
C1                            : alter table "OWI"."BIG_TABLE" shrink space
C2                            : alter table "OWI"."BIG_TABLE" shrink space COMPACT
C3                            : alter table "OWI"."BIG_TABLE" enable row movement

Oracle 이 제공하는 Advisor 기능이 점점 다양해지고 정밀해지면서 데이터베이스 진단에 필요한 각종 검증 작업이 대부분 자동화되고 있다. Buffer Cache의 크기나 Shared Pool 크기 진단에서 시작해서 Segment Space 크기 진단으로, 그리고 SQL 성능 자동 진단으로까지 발전하고 있다. Oracle 11g에서는 SQL 성능 진단에서 Index/Materialized View(10g)에 Partition 추천까지 추가될 정도로 이 자동 진단 기능이 확장되고 있다.

아마 이런 자동 진단 및 추천 기능들이 앞으로는 DBA들이 알아야 할 필수 지식이 되지 않을까.

출처 : http://ukja.tistory.com/89

Oracle 9i에서 Bind Peeking 기능이 소개된 것은 익히 알려진 사실이다.
Bind Peeking이란 Bind 변수를 사용하는 경우, Query가 최초로 실행되는 시점의 실제 Bind 값을 이용해서(Peeking해서) 실행 계획을 세우는 것을 의미한다.

Bind 변수를 사용하는 경우 실제로 실행되는 시점에 어떤 값이 들어오는지 알 수 없기 때문에 컬럼의 평균적인 분포만을 가지고 비용을 계산할 수 밖에 없다. 대부분의 경우에는 큰 문제가 안되지만, 다음과 같은 경우에는 치명적인 단점을 가지고 있다.

가령 status 컬럼의 분포가 다음과 같다고 하면...
 - status = 1  : 99%
 - status = 99 : 1%

이 경우
- Where status = '1' 이면 인덱스를 경유하지 않고 Full Table Scan을 하는 것이 유리하다
- Where status = '99' 이면 인덱스를 경유하는 것이 유리하다.

하지만,
- Where status = :b1 과 같이 Bind 변수를 사용하면 어떤 값이 올지 모르기 때문에 평균적인 분포를 따르게 된다. 따라서 이 경우 반드시 Full Table Scan을 선택한다.

Bind Peeking은 이러한 단점을 피하기 위해 쿼리가 실행되는 최초 시점에 Bind 변수에 들어온 값을 이용하게 된다. 즉, 쿼리가 최초로 실행되는 시점에

exec :b1 := '1';
... Where status = :b1

과 같이 실행되면 Full Table Scan을,

exec :b2 := '99';
... Where status = :b1

과 같이 실행되면 Index Range scan을 선택하게 된다.
단, 위와 같이 작동하려면 컬럼 값이 "1" 인 경우와 "99"인 경우의 분포도의 차이를 Oracle이 명확하게 이해하고 있어야 되므로 Histogram이 반드시 존재해야 한다. 가령

exec dbms_stats.gather_table_stats(user, 'TABLE_NAME', method_opt=>'for columns size 2 status');

와 같이 컬럼에 대해 적당한 크기의 Histogram이 생성되어 있어야 된다.

위의 설명을 이해했다면 Bind Peeking에는 기본적인 딜레마가 있음을 알 수 있다. 위의 예를 보면 쿼리가 최초로 실행되는 시점에 "1"의 값이 오느냐, "99"의 값이 오느냐에 따라 실행 계획이 결정된다. 어떤 쪽이든 한 쪽은 손해를 볼 수 밖에 없는 결정적인 구조적 한계를 가지고 있다.

이런 구조적 한계를 가진 기능을 제공한다는 것은 기본적인 설계의 결함이라고 할 수 있다. 덕분에 많은 시스템에서 Bind Peeking 기능을 비활성화시켜 버리고 있다. _optim_peek_user_binds 파라미터 값을 false로 지정해 버리는 것이다.

이런 경향은 10g에서 특히 심한데, 그 이유는 10g에서 dbms_stats의 method_opt 파라미터의 기본값이 for all columns size auto로 바뀌는 바람에 Histogram의 생성 여부를 Oracle이 자동으로 판단해버리는데 있다. 이전 버전에서는 for all columns size 1 이 기본값으로 Histogram이 생성되지 않았다.
Histogram이 없으면 Bind Peeking에 의한 부작용은 거의 없다. 10g에서 Histogram의 생성 여부를 Oracle이 판단함으로써 이전 버전에서는 존재하지 않던 Histogram이 갑자기 생성되고 이로 인해 Bind Peeking의 결함이 더욱 두드러지게 나타나게 되어 버린 것이다.
이 때문에 Oracle 10g에서는 _optim_peek_user_binds 파라미터를 False로 바꾸는 것이 기본 권고안처럼 되어 버릴 정도가 되었다.

하지만, Oracle 11g에서 아주 큰 희소식이 전달되었다. 이른바 Adaptive Cursor Sharing이라는 기능이 소개된 것인데, 이 기능으로 인해 Bind Peeking과 Histogram의 조합에 의한 부작용이 말끔히 해소될 수 있다.

Adaptive Cursor Sharing이란 말 그대로 상황에 맞게 유연하게 Cursor를 Share하겠다는 의미이다. 이 개념을 구현하기 위해 Oracle은 Bind Sensitive Cursor, Bind Aware Cursor라는 새로운 개념을 도입했다.
Bind Sensitive Cursor란, 말 그대로 Bind 값에 민감한 Cursor라는 의미이다. 즉, Bind 값이 바뀌면 그것을 민감하게 처리하겠다는 의미이다. 1) Equal 조건에서는 조건절에 쓰인 컬럼에 Histogram이 있고 2) Range 조건인 경우 Oracle은 이것을 Bind Senstive Cursor라고 부른다.
Bind Aware Cursor란, Bind Sensitive Cursor에 입력된 Bind 값에 따라 실행 계획이 분화된 Cursor를 의미한다. 즉, Bind Aware Cursor가 생성되었다는 것은 Bind 변수의 값에 따라 Oracle이 적절한 Child Cursor를 생성했다는 것을 의미한다.

Adaptive Cursor Sharing 기법을 사용하면 Bind Peeking에 의해 최초 실행 시점에 실행 계획이 결정되더라도 이후 새로운 Bind 변수 값이 사용되면 이에 맞게 실행 계획을 분화(새로운 Child Cursor 생성)시킨다. 따라서 Bind Peeking에 의한 부작용이 사실상 없어지게 된다.
단, 조건절에 쓰인 컬럼에 Histogram이 있고, Histogram의 분포도에 따라 실행 계획에 큰 차이가 있을 수 있다고 판단된다는 조건이 중요하다. 즉, 적절한 Histogram 없이는 의미가 없다는 것이다. Histogram은 이제 우리가 반드시 이해하고 활용해야 하는 존재가 되었다.
(dbms_stats의 method_opt 파라미터의 기본값이 for all columns size auto로 바뀌는 순간 이미 Histogram은 피할 수 없는 존재가 되어버렸다는 사실을 기억할 필요가 있겠다)

아래 샘플 코드를 이용하면 Oracle 11g의 Adaptive Cursor Sharing 기법을 이해할 수 있다.

-------------------------------------
-- Oracle 11g Bind Aware Cursor
-- Author: 조동욱
--------------------------------------

-- create objects
drop table acs_table;

create table acs_table(id int, name char(10));

create index acs_table_idx on acs_table(id);

insert into acs_table select 1, 'name' from all_objects where rownum <= 100000

insert into acs_table values(99, 'name');

commit;

-- gather statistics with histogram
exec dbms_stats.gather_table_stats(user, 'ACS_TABLE', estimate_percent=>100, method_opt=>'FOR COLUMNS SIZE 2 ID', cascade=>true);

-- check histogram
select * from dba_tab_histograms where table_name = 'ACS_TABLE';

-- Bind query
alter system flush shared_pool

var id number;

-- id == 1
-- 각 단계마다 아래 쿼리 결과 확인
select sql_id, sql_text,is_bind_sensitive,is_bind_aware
from v$sql where sql_text like 'select count(name) from acs_table%';

exec :id := 1;

select count(name) from acs_table where id = :id;

-- id == 99
exec :id := 99;

select count(name) from acs_table where id = :id;

select count(name) from acs_table where id = :id;

-- id == 1 again
exec :id := 1;

select count(name) from acs_table where id = :id;

-- check mismatch
select * from v$sql_shared_cursor where sql_id = '<sql_id>';

Oracle 11g의 Adaptive Cursor Sharing은 Oracle이 Bind 변수와 Histogram의 기능 개선에 얼마나 노력을 기울이고 있는지를 잘 보여주는 단적인 예이다. 아마 기대컨데, 더 이상 Bind Peeking의 부작용에 대해 고민하지 않아도 되기를 기대해본다.

출처 : http://ukja.tistory.com/87

Transaction internals

 

 

목차 1 Redo Layer 1.1 Database Block Address (DBA) 1.2 Appendix of Redo Layer 2 Undo Layer 2.1 Appendix of Undo Layer 3 Enqueue Layer 3.1 Appendix of Enqueue Layer 4 Block Layer 4.1 Appendix of Block Layer 5 PGA Layer 5.1 Appendix of PGA Layer

Redo Layer

Database Block Address (DBA)

오라클에서 제공하는 DBMS_UTILITY 패키지를 이용하여 16진수로 표시되는 DBA를 손쉽게 상대 파일 번호와 블록 번호를 확인하는 방법

SQL> set serveroutput on

DECLARE

    l_dba    NUMBER := TO_NUMBER ('00C00012','XXXXXXXX');

    l_file   NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (l_dba);

    l_block NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (l_dba);

    BEGIN 

       DBMS_OUTPUT.PUT_LINE ('File  : '||l_file); 

       DBMS_OUTPUT.PUT_LINE ('Block : '||l_block);

   END;

Appendix of Redo Layer

1.check_redo_scn.sql

col member for a40

set linesize 140

select a.first_change#, a.status, b.member

from   v$log a, v$logfile b

where  a.group#=b.group#

/

2.dba2_fb.sql

set serveroutput on

DECLARE

        l_dba   NUMBER := TO_NUMBER ('&dba','XXXXXXXX');

        l_file    NUMBER := DBMS_UTILITY. DATA_BLOCK_ADDRESS_FILE (l_dba);

        l_block NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (l_dba);

BEGIN

        DBMS_OUTPUT.PUT_LINE ('File  : '||l_file);

        DBMS_OUTPUT.PUT_LINE ('Block : '||l_block);

END;

/

3.param.sql (SYS User로 수행)

set pages 0

set heading off 

set linesize 120

col name for a40

col value for a60

SELECT ksppinm as name,

            ksppstvl as value

FROM   sys.x$ksppi x , sys.x$ksppcv y

WHERE  ( x.indx = y.indx )

AND    ksppinm like '%&1%'

order by ksppinm

/

4.check_flush.sql

select name, value 

from   v$sysstat 

where name in ('IMU Flushes','IMU commits','redo size','IMU undo allocation size', 'user commits')

order by name

/

Undo Layer

Appendix of Undo Layer

1) rowid2fb.sql

var v_rowid_type number;

var v_object_number number;

var v_relative_fno number;

var v_block_number number;

var v_row_number number;

set serveroutput on

exec dbms_rowid.rowid_info

 ('&1', :v_rowid_type, :v_object_number, :v_relative_fno, :v_block_number,:v_row_number);

/

2) print.sql

print v_relative_fno

print v_block_number

/

3) chk_undostat.sql

set linesize 140

select to_char(a.begin_time,'HH24:MI:SS') begin,

         to_char(a.end_time,  'HH24:MI:SS') end,

         a.maxquerylen max_q_len,

         a.maxqueryid  max_q_id,

         a.tuned_undoretention tuned_ur,

         substr(b.sql_text,1,15) sql_text

from   v$undostat a, v$sql b

where  a.maxqueryid=b.sql_id(+)

and    rownum<=4

/

Enqueue Layer

Appendix of Enqueue Layer

1) v$lock 뷰 정의

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

-- GV$LOCK 정의

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

SELECT s.inst_id ,

            l.laddr  "ADDR", 

            l.kaddr  "KADDR",

            s.ksusenum  "SID",

            r.ksqrsidt  "TYPE",

            r.ksqrsid1  "ID1",

            r.ksqrsid2  "ID2",

            l.lmode  "LMODE",

            l.request  "REQUEST",

            l.ctime , "CTIME",

            decode( l.lmode , 0 , 0 , l.block ) "BLOCK"

FROM    v$_lock l ,

            x$ksuse s ,

            x$ksqrs r

WHERE  l.saddr=s.addr

AND      l.raddr=r.addr

 

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

-- GV$_LOCK 정의

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

SELECT USERENV( 'Instance' ) ,

            laddr ,

            kaddr ,

            saddr ,

            raddr ,

            lmode ,

            request ,

            ctime ,

            BLOCK

FROM   v$_lock1

UNION  ALL

SELECT inst_id ,

            addr ,

            ksqlkadr ,

            ksqlkses ,

            ksqlkres ,

            ksqlkmod ,

            ksqlkreq ,

            ksqlkctim ,

            ksqlklblk

FROM    x$ktadm

WHERE  bitand( kssobflg , 1 ) !=0

AND  ( ksqlkmod!=0  OR ksqlkreq!=0 )

UNION  ALL

SELECT inst_id ,

            addr ,

            ksqlkadr ,

            ksqlkses ,

            ksqlkres ,

            ksqlkmod ,

            ksqlkreq ,

            ksqlkctim ,

            ksqlklblk

FROM   x$ktatrfil

WHERE  bitand( kssobflg , 1 ) !=0

AND  ( ksqlkmod!=0 OR ksqlkreq!=0 )

UNION  ALL

SELECT inst_id ,

            addr ,

            ksqlkadr ,

            ksqlkses ,

            ksqlkres ,

            ksqlkmod ,

            ksqlkreq ,

            ksqlkctim ,

            ksqlklblk

FROM    x$ktatrfsl

WHERE  bitand( kssobflg , 1 ) !=0

AND  ( ksqlkmod!=0 OR ksqlkreq!=0 )

UNION  ALL

SELECT inst_id ,

            addr ,

            ksqlkadr ,

            ksqlkses ,

            ksqlkres ,

            ksqlkmod ,

            ksqlkreq ,

            ksqlkctim ,

            ksqlklblk

FROM    x$ktatl

WHERE  bitand( kssobflg , 1 ) !=0

AND  ( ksqlkmod!=0 OR ksqlkreq!=0 )

UNION  ALL

SELECT inst_id ,

            addr ,

            ksqlkadr ,

            ksqlkses ,

            ksqlkres ,

            ksqlkmod ,

            ksqlkreq ,

            ksqlkctim ,

            ksqlklblk

FROM   x$ktstusc

WHERE  bitand( kssobflg , 1 ) !=0

AND  ( ksqlkmod!=0 OR ksqlkreq!=0 )

UNION  ALL

SELECT inst_id ,

            addr ,

            ksqlkadr ,

            ksqlkses ,

            ksqlkres ,

            ksqlkmod ,

            ksqlkreq ,

            ksqlkctim ,

            ksqlklblk

FROM    x$ktstuss

WHERE  bitand( kssobflg , 1 ) !=0

AND  ( ksqlkmod!=0 OR ksqlkreq!=0 )

UNION  ALL

SELECT inst_id ,

            addr ,

            ksqlkadr ,

            ksqlkses ,

            ksqlkres ,

            ksqlkmod ,

            ksqlkreq ,

            ksqlkctim ,

            ksqlklblk

FROM    x$ktstusg

WHERE  bitand( kssobflg , 1 ) !=0

AND  ( ksqlkmod!=0 OR ksqlkreq!=0 )

UNION  ALL

SELECT inst_id ,

            ktcxbxba ,

            ktcxblkp ,

            ksqlkses ,

            ksqlkres ,

            ksqlkmod ,

            ksqlkreq ,

            ksqlkctim ,

            ksqlklblk

FROM    x$ktcxb

WHERE  bitand( ksspaflg , 1 ) !=0

AND  ( ksqlkmod!=0 OR ksqlkreq!=0 )

2) chk_lock.sql

select a.sid, b.object_name, a.type, a.id1, a.id2, a.lmode, a.request, a.block

from   v$lock a, dba_objects b

where  a.sid in (&sid.....)

and    a.type='TM'

and    a.id1=b.object_id(+)

order by sid

/

 

Block Layer

Appendix of Block Layer

1) dba2fb.sql

set feedback off

set serveroutput on

DECLARE

       l_dba   NUMBER := TO_NUMBER ('&dba','XXXXXXXX');

       l_file    NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (l_dba);

       l_block NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (l_dba);

BEGIN

       DBMS_OUTPUT.PUT_LINE ('alter system dump datafile '||l_file||' block '||l_block||';');

END;

/

set feedback on

2) hex2chr.sql

select chr(to_number('&1', 'XXXXXXXX')) from dual;

PGA Layer

Appendix of PGA Layer

1. getworarea.sql script

col sql            format  a13

col est_opt_sz     heading "est|opt_sz"    format 999.9

col est_one_sz     heading "est|one_sz"    format 999.9

col last_mem_used  heading "last|mem_used" format 999.9

col total_exe      heading "total|exe"     format 999

col opt_exe        heading "opt|exe"       format 999

col onepass_exe    heading "onepass|exe"   format 999

col multipass_exe  heading "multipass|exe" format 999

col active_time    heading "active|time"   format 999.9

col last_tmp_sz    heading "last|tmp_sz"   format 999

SELECT

    --SUBSTR( sql_text , 57 , 11 ) AS SQL , -- for sort test

      SUBSTR( sql_text , 103 , 10 ) AS SQL, -- for hash test

      ROUND( estimated_optimal_size/1024/1024 , 1 ) AS est_opt_sz ,

      ROUND( estimated_onepass_size/1024/1024 , 1 ) AS est_one_sz ,

      ROUND( last_memory_used/1024/1024 , 1 ) AS last_mem_used ,

      optimal_executions AS opt_exe,

      onepass_executions AS onepass_exe,

      multipasses_executions AS multipass_exe,

      ROUND( active_time/1000000 , 1) AS active_time,

      ROUND( last_tempseg_size/1024/1024 , 1 ) AS last_tmp_sz

FROM    v$sql_workarea swa ,

            v$sql sq

WHERE  swa.address = sq.address

AND      swa.hash_value = sq.hash_value

AND      sql_text LIKE 'select count(*) from (select %'

order by 4,1

/

 

 

제공 : DB포탈사이트 DBguide.net

옵티마이저의 비용계산 방법과 실행원리
시스템의 성능 향상을 위한 노력

이번호 technical tips에서는 SQL문의 성능을 결정하는 비용기반 옵티마이저의 핵심 원리 중에 비용계산 방법에 대해 알아 보도록 하겠다. 대부분의 개발자들이 작성하는 SQL문은 비용기반 옵티마이저 환경에서 작성된 실행계획을 통해 실행 되어지는데, 이때 옵티마이저의 비용계산 방법에 대해 정확히 이해한다면 더 좋은 성능을 보장 받는 SQL문을 작성할 수 있을 것이다.

저자_주종면, 오라클 ACE, PLAN 정보기술(www.plandb.co.kr / Jina6678@paran.com)

1. SQL문 처리과정

옵티마이저의 비용계산 방법을 소개하기 전에 우선 SQL문의 처리과정의 대해 알아보자.
사용자가 실행하는 SQL문은 파서(Parser)에게 전달되고 파서는 데이터 딕셔너리 정보를 참조하여 SQL문에 대한 구문분석(Syntax와 Symantics)을 수행한다. 이 결과를 파스-트리(Parse-Tree)라고 한다.
파스-트리는 옵티마이저에게 전달되는데 오라클 데이터베이스에는 공식기반 옵티마이저(Rule-Based Optimizer)와 비용기반 옵티마이저(Cost-Based Optimizer)가 있다. 비용기반 옵티마이저에 의해 산출된 적정 플랜(Optimal Plan)은 로우 소스 생성기(Row Source Generator)에게 전달되고 이것은 실행 계획(Execution Plan)으로 결정된다.
우리가 SET AUTOTRACE, SQL*TRACE와 TKPROF와 같은 튜닝 도구들을 통해 참조할 수 있는 결과에 바로 이 실행계획이 포함되어 있다. 이 실행계획은 SQL 실행엔진(SQL Execution Engine)에 의해 테이블과 인덱스를 참조하여 그 결과를 사용자에게 리턴하게 되는 것이다.

이어서, 비용기반 옵티마이저가 어떤 비용계산 방법을 통해 적절한 실행 계획을 찾아내는지를 소개할 것이다. 개발작업 때 처음부터 좋은 실행계획을 작성할 수 있도록 SQL문을 작성한다면 SQL 튜닝에 대한 불필요한 시간과 비용을 줄여 나감으로써 좋은 성능의 시스템을 개발할 수 있는 첫걸음이 되는 것이다.

 

 

2. 비용기반 옵티마이저의 구조

 

 

이번에는 구체적으로 비용기반 옵티마이저의 아키텍처에 대해 알아보도록 하겠다.
CBO(Cost Based Optimizer)가 어떻게 비용을 계산하고 어떻게 실행계획을 작성하는지를 알기 위해서는 보다 구체적으로 CBO의 아키텍처의 대해 알고 있어야 한다.
<그림 2>에서와 같이 CBO는 쿼리 변형기(Query Transformer), 비용 계산기(Estimator), 쿼리 작성기(Query Generator) 3가지 구조로 구성되어 있다. 그럼, 각 구성 요소와 비용계산 알고리즘을 통해 실행계획 작성 방법에 대해 알아 보자.

 

 

3. 쿼리 변형기(Query Transformer)

 

 

쿼리 변형기는 파서(Parser)에 의해 구문 분석된 결과를 전달 받아 잘못 작성된 SQL문을 정확한 문장으로 변형시키는 역할을 수행한다.

? 잘못된 데이터 타입으로 조건 값을 검색하면 변형된다.
(S_DATE 컬럼은 날짜 컬럼인데 문자 값을 검색할 때 사용하는 인용부호를 사용한 경우)

 

SQL> SELECT * FROM emp WHERE s_date = '1999-01-01';
--> SQL> SELECT * FROM emp WHERE s_date = TO_DATE('1999-01-01');

? LIKE 연산자는 %(와일드 카드)와 함께 검색하는 경우 사용되지만, 그렇지 않은 경우
=(동등) 조건으로 변형되어 검색된다.

SQL> SELECT * FROM emp WHERE ename LIKE '주종면‘;
--> SQL> SELECT * FROM emp WHERE ename = ‘주종면’;

? BETWEEN ~ AND 조건은 > AND < 조건으로 변형되어 검색된다.

SQL> SELECT * FROM emp WHERE salary BETWEEN 100000 AND 200000;
--> SQL> SELECT * FROM emp WHERE salary >= 100000 and salary <= 200000;

? 인덱스가 생성되어 있는 컬럼의 IN 연산자의 조건은 OR 연산자의 조건으로 변형된다.

SQL> SELECT * FROM emp WHERE ename IN ('SMITH', 'KING');
--> SQL> SELECT * FROM emp WHERE ename = 'SMITH' or ename = 'KING';

? 인덱스가 생성되어 있는 컬럼의 OR 연산자의 조건은 UNION ALL로 변형된다.

SQL> SELECT * FROM emp WHERE ename = 'SMITH' or sal = 1000;
SQL> SELECT * FROM emp WHERE ename = 'SMITH'
UNION ALL
SELECT * FROM emp WHERE sal = 1000;

 

이와 같이 쿼리 변형기는 부적절하거나 잘못 작성된 SQL문장을 정확한 문장으로 변형시켜주는 역할을 수행하는 알고리즘이다.

 

 

4. 비용 계산기(Estimator)

 

 

비용 계산기는 비용기반 옵티마이저가 가지고 있는 비용 계산 공식에 의해 다양한 실행방법 중에 가장 좋은 성능의 실행계획을 찾아 주는 알고리즘이다.

 

1) 테이블과 인덱스의 통계정보

 

먼저, ANALYZE 명령어에 의해 수집되는 통계정보의 상태와 용어에 대해 설명하겠다. 먼저, 테이블에 대한 통계정보이다.

 

SQL> ANALYZE TABLE big_emp COMPUTE STATISTICS;
SQL> ANALYZE TABLE big_dept COMPUTE STATISTICS;

SQL> SELECT table_name, blocks, num_rows, avg_row_len
FROM user_tables
WHERE table_name = ‘BIG_EMP’ or table_name = ‘BIG_DEPT’

TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN
--------------------------------------------
BIG_DEPT 1 289 23
BIG_EMP 180 28955 43

NUM_ROWS : 해당 테이블의 전체 행수
AVG_ROW_LEN : 행 하나의 평균 길이

SQL> SELECT table_name, column_name, low_value, high_value, num_distinct
FROM user_tab_columns
WHERE table_name = ‘BIG_EMP‘ or table_name = ‘BIG_DEPT‘;

TABLE_NAME COLUMN_NAME LOW_VALUE HIGH_VALUE NUM_DISTINCT
----------------------------------------------------------------------
BIG_EMP EMPNO C102 C3036464 28955
BIG_EMP ENAME 4144414D53 57415244 14
BIG_EMP JOB 414E414C595354 53414C45534D414E 8
BIG_EMP MGR C24C43 C25003 6
BIG_EMP HIREDATE 77B7060D010101 78680604010101 713
BIG_EMP SAL 80 C24E6233 3982
BIG_EMP COMM 80 C20F 5
BIG_EMP DEPTNO 80 C164 98
BIG_EMP GROUPNO 31 32 2

LOW_VALUE : 해당 컬럼에 저장되어 있는 가장 최소값에 대한 암호화 결과
HIGH_VALUE : 해당 컬럼에 저장되어 있는 가장 최대값에 대한 암호화 결과
NUM_DISTINCT : 해당 컬럼의 저장되어 있는 유일한 값의 개수

 

다음은 인덱스에 대한 통계정보이다.

 

SQL> CREATE INDEX i_big_emp_deptno ON big_emp (deptno);
SQL> ANALYZE INDEX i_big_emp_deptno COMPUTE STATISTICS;

SQL> SELECT index_name index_name,
num_rows num_rows,
avg_leaf_blocks_per_key l_blocks,
avg_data_blocks_per_key d_blocks,
clustering_factor cl_fac,
blevel blevel,
leaf_blocks leaf
FROM user_indexes;

INDEX_NAME NUM_ROWS L_BLOCKS D_BLOCKS CL_FAC BLEVEL LEAF
----------------------------------------------------------------------------------
I_BIG_EMP_DEPTNO 28853 1 51 5036 1 57

NUM_ROWS : 인덱스 행 수
L_BLOCKS : 하나의 LEAF 블록에 저장되어 있는 인덱스 키의 수
D_BLOCKS : 하나의 DATA 블록에 저장되어 있는 인덱스 키의 수
CL-FAC : CLUSTER FACTOR
BLEVEL : INDEX의 DEPTH
LEAF : LEAF 블록의 수

 

통계 정보는 오라클 10g 이전 버전까지는 사용자가 실행하는 ANALYZE 명령어에 의해 생성되었으며 10g 버전부터는 오라클 서버의 자동화된 알고리즘에 의해 자동 생성된다.
오라클 9i 버전 때까지는 사용자에 의해 통계정보를 생성해 주지 않으면 비용기반 옵티마이저는 부정확한 실행계획을 작성함으로써 성능이 저하되는 경우들이 많이 발생했었다.
<그림 3>은 통계정보가 생성되어 있지 않은 경우 비용기반 옵티마이저가 참조하는 통계정보의 기본 값이다. 데이터를 저장하고 있는 테이블과 인덱스의 실제 구조정보와 다른 값을 참조하기 때문에 결론적으로 좋은 실행계획을 작성하지 못하는 것이다.

 

2) 용어에 대한 이해
SQL> SELECT * FROM big_emp;

Execution Plan
----------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=28955 Bytes=1042380)
1 0 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=19 Card=28955 Bytes=1042380)

COST : SQL문을 실행하여 조건을 만족하는 행을 검색하는데 소요되는 횟수
CARDINALITY : 전체 테이블에서 SQL문의 조건을 만족하는 행 수

 

3) Cardinality

일반적으로 cardinality는 SQL문이 실행되었을 때 조건을 만족하는 행수를 의미하는 것이긴 하지만 이것은 검색되는 컬럼이 어떤 속성을 가지고 있느냐에 따라 계산 공식이 달라진다.

 

3-1) Distinct Cardinality(Unique-Key)인 경우

이 경우는 주로 Full Table Scan과 같이 테이블 전체 행을 검색하는 경우의 cardinality를 계산하는 공식이다.

SQL> SELECT count(*) FROM big_dept;
count(*)
------------------
289

Cardinality = 조건을 만족하는 테이블의 행 수 = 289

SQL> ANALYZE TABLE big_dept COMPUTE STATISTICS;
SQL> SELECT * FROM big_dept ;

Execution Plan
--------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=289 Bytes=5202)
1 0 TABLE ACCESS (FULL) OF 'BIG_DEPT' (Cost=1 Card=289 Bytes=5202)

 

3-2) Efficient Cardinality(Non-Unique-Key)인 경우

SQL> SELECT count(*) FROM big_dept; --> 289 행
SQL> SELECT distinct loc FROM big_dept; --> 7 행

Cardinality = 테이블의 전체 행수 / Distinct-Key 수 = 289 / 7
= 41

SQL> ANALYZE TABLE big_dept COMPUTE STATISTICS;
SQL> SELECT * FROM big_dept WHERE loc = ‘LA’;

Execution Plan
---------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=41 Bytes=738)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_DEPT' (Cost=1 Card=41 Bytes=738)

 

3-3) Group Cardinality(Group by 절)인 경우

SQL> ANALYZE TABLE big_emp COMPUTE STATISTICS;

Cardinality = Distinct-Key 수 - 1

SQL> SELECT deptno, sum(sal) FROM big_emp Group by deptno; --> 99 행

Execution Plan
-----------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=131 Card=98 Bytes=588)
1 0 SORT (GROUP BY) (Cost=131 Card=98 Bytes=588)
2 1 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=57 Card=28955 Bytes=173730)

 

4) Selectivity

선택도는 전체 테이블에서 SQL문의 조건을 만족하는 행이 분포되어 있는 비율을 의미하며 검색 되어지는 컬럼의 성격에 따라 계산 공식이 달라진다.

 

4-1) Unique-Key/Primary-Key의 경우

SELECT * FROM emp WHERE empno = 200;
--> Selectivity = 0.01 (좋은 선택도)

 

4-2) Non Unique-Key의 경우

SELECT * FROM emp WHERE ename = ‘SMITH’;
--> Selectivity = 1 / distinct-keys
--> 1/ 4 = 0.25

 

4-3) 값을 가진 비동등 조건식의 경우

SELECT * FROM emp WHERE empno < 200;
--> Selectivity = (범위값 - 최소값) / (최대값 - 최소값)
= (200 - 1) / (29999 - 1)
= 199 / 29998 = 0.007

SELECT * FROM emp WHERE empno > 200;
--> Selectivity = (범위값 - 최소값) / (최대값 - 최소값)
= (29799 - 1) / (29999 - 1)
= 29798 / 29998 = 0.9
SELECT * FROM emp WHERE empno BETWEEN 100 AND 200;
--> Selectivity = (최대 조건값 - 최소 조건값) / (최대값 - 최소값)
= (200 - 100) / (29999 - 1)
= 100 / 29998 = 0.003

 

4-4) 바인드 변수를 가진 비동등식의 경우

SELECT * FROM emp WHERE empno < :a ;
--> Selectivity = 0.25 % (나쁜 선택도)

SELECT * FROM emp WHERE empno BETWEEN :a AND :b ;
--> Selectivity = 0.5 % (나쁜 선택도)

 

5. 비용 계산 방법

 

지금까지 비용기반 옵티마이저가 비용을 계산하기 위해 알아야 할 여러 가지 내용에 대해 알아보았다. 그럼 지금부터는 다양한 SQL문의 비용 계산 공식에 대해 알아보자.

 

5-1) Full Table Scan인 경우

Cost = 전체 블록 수 / DB_FILE_MULTIBLOCK_READ_COUNT의 보정 값

인덱스를 사용하지 않고 해당 테이블의 첫 번째 블록부터 전체 블록을 검색해야 하는 전체 테이블 스캔의 경우에는 init.ora 파일에 정의되어 있는DB_FILE_MULTOBLOCK_READ_COUNT 파라메터 값에 의해 비용이 계산된다.
이 파라메터는 FULL TABLE SCAN의 경우 한번에 하나의 I-O로는 성능을 기대할 수 없기 때문에 보다 빠른 성능을 기대하기 위해 제공되는 다중 블록 읽기를 위한 파라메터이다. 즉, 한번 I-O에 8개, 16개, 32개, 64개의 다중 블록을 읽게 하기 위함이다.

SQL> SHOW PARAMETER db_file_multiblock_read_count
NAME TYPE VALUE
-------------------------------------------------------------------------------------
db_file_multiblock_read_count integer 16
SQL> SELECT * FROM big_emp;

Execution Plan
----------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=28955 Bytes=1042380)
1 0 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=19 Card=28955 Bytes=1042380)

앞에서 소개된 비용 계산 공식을 적용해보면 COST = 180 / 16 = 11.25의 결과가 나와야 하는데 실제 비용은 COST=19의 결과가 계산되었다 !!
이것은 DB_FILE_MULTIBLOCK_READ_COUNT 파라메터의 실제 값처럼 한번 I-O에 8, 16, 32, 64개의 블록을 읽을 수는 없기 때문에 파라메터의 실제 값이 아닌 보정 값으로 비용을 계산했기 때문이다. <그림 4>의 왼쪽 표는 ACTUAL (DB_FILE_MULTIBLOCK_READ_COUNT 파라메터 값)에 따른 Adjusted(보정 값)이며 <그림 4>의 오른쪽 그림은 이 파라메터가 실제로 성능의 영향을 미치게 되는 영향도를 그림으로 나타낸 것이다.
즉, COST = 19는 (180 / 10.398) +1의 계산 공식 결과임을 알 수 있다.
사용자가 실행하는 SQL문의 실행계획이 FULL TABLE SCAN으로 결정되도록 유도하기 위해서는 이 파라메터 값을 조절하면 된다.

 

SQL> ALTER SESSION SET db_file_multiblock_read_count = 8;
SQL> SELECT * FROM big_emp;

Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=28955 Bytes=1042380)
1 0 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=29 Card=28955 Bytes=1042380)

 

위 SQL문의 비용은 Cost = (180 / 6.589) + 1 = 29 이다.
파라메터 값의 변경에 따라 비용이 달라지는 것을 확인할 수 있을 것이다.

 

SQL> ALTER SESSION SET db_file_multiblock_read_count = 32;
SQL> SELECT * FROM big_emp;

Execution Plan
-------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=28955 Bytes=1042380)
1 0 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=13 Card=28955 Bytes=1042380)

위 SQL문의 비용은 Cost = (180 / 16.409) + 1 = 13이다.

 

 

5-2) Unique Index Scan인 경우

Cost = blevel + 1

UNIQUE INDEX를 이용한 비용은 LEAF 블록의 DEPTH +1 이 된다.

SQL> CREATE UNIQUE INDEX I_big_emp_empno ON BIG_EMP (EMPNO);
SQL> ANALYZE INDEX I_big_emp_empno compute statistics;

SQL> SELECT INDEX_NAME, BLEVEL FROM USER_INDEXES
WHERE INDEX_NAME = 'I_BIG_EMP_EMPNO';

INDEX_NAME BLEVEL
-------------------------------------------------
I_BIG_EMP_EMPNO 1

SQL> SELECT /*+index(big_emp I_big_emp_empno )*/ ename
FROM big_emp
WHERE empno = 7499;
--------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_EMP' (Cost=2 Card=1 Bytes=20)
2 1 INDEX (UNIQUE SCAN) OF 'I_BIG_EMP_EMPNO' (UNIQUE) (Cost=1 Card=100)

위 SQL문의 비용은 Cost = 1 + 1 = 2이다.

5-3) Fast Full Index Scan인 경우

Cost = leaf_blocks / db_block_size

SQL> CREATE INDEX emp_job_deptno ON BIG_EMP (job, deptno);
SQL> ANALYZE INDEX emp_job_deptno compute statistics;
SQL> SELECT INDEX_NAME, LEAF_BLOCKS FROM USER_INDEXES
WHERE INDEX_NAME = 'EMP_JOB_DEPTNO';

INDEX_NAME LEAF_BLOCKS
--------------------------------------------------------
EMP_JOB_DEPTNO 89

SQL> SHOW PARAMETER DB_BLOCK_SIZE
NAME VALUES
----------------------------------------------
DB_BLOCK_SIZE 8

SQL> SELECT /*+index_ffs(big_emp big_emp_job_deptno)*/ job, deptno
FROM big_emp
WHERE deptno >= 1 and deptno <= 100
----------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=37 Bytes=703)
1 0 INDEX (FAST FULL SCAN) OF 'EMP_JOB_DEPTNO (Cost=10 Card=37 Bytes=703)

위 SQL문의 비용은 Cost = 89 / 8 = 10 이다.

 

5-4) Index Range Scan인 경우

Cost = blevel + (Selectivity X leaf_blocks) + (Selectivity X Cluster Factor)
SQL> ALTER SESSION SET optimizer_index_cost_adj = 100; SQL> ANALYZE
TABLE big_emp COMPUTE STATISTICS;
SQL> ANALYZE INDEX i_big_emp_deptno COMPUTE STATISTICS;

선택도= 1/98 (Distinct.. deptno.. 98.... ...... .... .. USER_TAB_COLUMNS
참조)
Cluster-Factor= 5036 (USER_INDEXES 참조)

SQL> SELECT INDEX_NAME, BLEVEL, LEAF_BLOCKS FROM USER_INDEXES
WHERE INDEX_NAME = 'I_BIG_EMP_DEPTNO'

INDEX_NAME BLEVEL LEAF_BLOCKS
---------------------------------------------------------------------------
I_BIG_EMP_DEPTNO 1 57

SQL> SELECT /*+INDEX(BIG_EMP i_big_emp_deptno)*/ *
FROM BIG_EMP WHERE DEPTNO = 10 ;

Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=53 Card=294 Bytes=10584)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_EMP' (Cost=53 Card=294 Bytes=10584)
2 1 INDEX (RANGE SCAN) OF 'I_BIG_EMP_DEPTNO' (NON-UNIQUE) (Cost=1 Card=294)

위 SQL문의 비용은 Cost = 1 + (1/98 * 57) +(1/98 * 5036) = 53이다.

SQL> ALTER SESSION SET optimizer_index_cost_adj = 50;
SQL> SELECT /*+INDEX(BIG_EMP)*/ * FROM BIG_EMP WHERE DEPTNO = 10 ;

Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=294 Bytes=10584)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_EMP' (Cost=27 Card=294 Bytes=10584)
2 1 INDEX (RANGE SCAN) OF 'I_BIG_EMP_DEPTNO' (NON-UNIQUE) (Cost=1 Card=294)

위 SQL문의 비용은 Cost = 53 X 0.5 = 27이다.

SQL> ALTER SESSION SET optimizer_index_cost_adj = 150;
SQL> SELECT /*+INDEX(BIG_EMP)*/ * FROM BIG_EMP WHERE DEPTNO = 10 ;

Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=80 Card=294 Bytes=10584)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_EMP' (Cost=80 Card=294 Bytes=10584)
2 1 INDEX (RANGE SCAN) OF 'I_BIG_EMP_DEPTNO' (NON-UNIQUE) (Cost=1 Card=294)

위 SQL 문의 비용은 Cost = 53 X 1.5 = 80이다.

 

5-5) Sort-Merge Join인 경우

다음은 소트-머지 조인의 경우 비용 계산 공식이다.
Cost = (Outer 테이블의 Sort Cost +Inner 테이블의 Sort Cost) -1
SQL> SELECT /*+use_merge(big_dept big_emp)*/ *
FROM big_emp, big_dept
WHERE BIG_EMP.DEPTNO = BIG_DEPT.DEPTNO;

Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=452 Card=28853 Bytes=1558062)
1 0 MERGE JOIN (Cost=452 Card=28853 Bytes=1558062)
2 1 SORT (JOIN) (Cost=7 Card=289 Bytes=5202)
3 2 TABLE ACCESS (FULL) OF 'BIG_DEPT' (Cost=1 Card=289 Bytes=5202)
4 1 SORT (JOIN) (Cost=446 Card=28955 Bytes=1042380)
5 4 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=57 Card=28955 Bytes= 1042380)

위 SQL문의 비용은 Cost = (outer-sort-cost + inner-sort-cost) - 1
= 7 + 446 - 1 = 452 이다.

5-6) Nest-Loop 조인의 경우

다음은 중첩루프 조인의 경우 비용 계산 공식이다.

Cost = Outer 테이블의 Cost + (Inner 테이블의 Cost * Outer 테이블의 Cardinality)

SQL> SELECT /*+ use_nl(big_emp big_dept)
index(big_emp I_big_emp_deptno)*/ *
FROM big_emp, big_dept
WHERE BIG_EMP.DEPTNO = BIG_DEPT.DEPTNO;

Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18786 Card=28853 Bytes= 1558062)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_EMP' (Cost=65 Card=28955 Bytes=1042380)
2 1 NESTED LOOPS (Cost=18786 Card=28853 Bytes=1558062)
3 2 TABLE ACCESS (FULL) OF 'BIG_DEPT' (Cost=1 Card=289 Bytes=5202) <-- OUTER 테이블
4 2 INDEX (RANGE SCAN) OF 'I_BIG_EMP_DEPTNO' (NON-UNIQUE) (Cost=1 Card=28955)

위 SQL문의 비용은 Cost = outer-cost + (inner-cost * outer card)
= 1 + (65 * 289)
= 1 + 18785
= 18786

SQL> CREATE INDEX i_big_dept_deptno ON big_dept (deptno);
SQL> ANALYZE TABLE big_dept COMPUTE STATISTICS;
SQL> SELECT /*+use_nl(big_emp big_dept)
index(big_dept I_big_dept_deptno)
ordered*/ *
FROM big_emp, big_dept
WHERE BIG_EMP.DEPTNO = BIG_DEPT.DEPTNO;

Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=57967 Card=28853 Bytes= 1558062)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_DEPT' (Cost=2 Card=289 Bytes= 5202)
2 1 NESTED LOOPS (Cost=57967 Card=28853 Bytes=1558062)
3 2 TABLE ACCESS (FULL) OF 'BIG_EMP'(Cost=57 Card=28955 Bytes=1042380) <--OUTER ......
4 2 INDEX (RANGE SCAN) OF 'I_BIG_DEPT_DEPTNO' (NON-UNIQUE) (Cost=1 Card=289)

위 SQL문의 비용은 Cost = outer-cost + (inner-cost * outer card)
= 57 + (2 * 28955)
= 57 + 57910
= 57967

5-7) Hash Join인 경우

다음은 해시 조인의 경우 비용 계산 공식이다.

Cost = (Outer 테이블의 Cost × #Hash 파티선수 +Inner 테이블의 Cost) + 2

SQL> SELECT /*+hash(big_emp)*/ *
FROM BIG_EMP, BIG_DEPT
WHERE BIG_EMP.DEPTNO = BIG_DEPT.DEPTNO;

Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=60 Card=28853 Bytes=1558062)
1 0 HASH JOIN (Cost=60 Card=28853 Bytes=1558062)
2 1 TABLE ACCESS (FULL) OF 'BIG_DEPT' (Cost=1 Card=289 Bytes=5202)
3 1 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=57 Card=28955 Bytes= 1042380)

위 SQL문의 비용은 Cost = outer-cost + inner-cost + Sort Cost + 2
= 1 + 57 + 2
= 60

 

 

6. 실행계획 생성기(Plan Generator)

사용자가 실행한 SQL문은 쿼리 변형기의 비용 계산기에 의해 여러 가지 유형의 실행계획으로 비용 분석된다. 그 중에 가장 적은 비용으로 실행되어질 수 있는 실행계획 하나가 선택되는데 이것을 Optimal Plan이라고 한다.
다음 문장들은 동일한 결과를 제공하지만 실행계획 생성기에 의해 가장 적은 비용의 실행계획을 선택한 결과이다.

 

6-1) 적정 플랜(Optimal Plan)

Index Scan인 경우

SELECT ename
FROM big_emp
WHERE deptno = 20 AND empno BETWEEN 100 AND 200
ORDER BY ename;
Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=1 Bytes=12)
1 0 SORT (ORDER BY) (Cost=9 Card=1 Bytes=12)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_EMP' (Cost=5 Card=1 Bytes=12)
3 2 INDEX (RANGE SCAN) OF 'I_BIG_EMP_EMPNO' (UNIQUE) (Cost=2 Card=1)

이 실행계획은 비용기반 옵티마이저에 의해 I_BIG_EMP_EMPNO 인덱스가 선택되었으며 이때 계산된 I-O COST는 9이다.
I_BIG_EMP_DEPTNO 인덱스가 선택된 경우

SELECT /*+index(big_emp I_BIG_EMP_DEPTNO)*/ ename
FROM big_emp
WHERE deptno = 20 AND empno BETWEEN 100 AND 200
ORDER BY ename;

Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=1 Bytes=12)
1 0 SORT (ORDER BY) (Cost=70 Card=1 Bytes=12)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_EMP' (Cost=66 Card=1 Bytes=12)
3 2 INDEX (RANGE SCAN) OF 'I_BIG_EMP_DEPTNO' (NON-UNIQUE) (Cost=2 Card=1)

이 실행계획은 I_BIG_EMP_DEPTNO 인덱스가 선택되었으며 이때 계산된 I-O COST는 70이다.

Full Table Scan인 경우

SELECT /*+full(big_emp)*/ ename
FROM big_emp
WHERE deptno = 20 AND empno BETWEEN 100 AND 200
ORDER BY ename;
Execution Plan
-------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=61 Card=1 Bytes=12)
1 0 SORT (ORDER BY) (Cost=61 Card=1 Bytes=12)
2 1 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=57 Card=1 Bytes=12)

이 실행계획은 Full Table Scan이 선택되었으며 이때 계산된 IO COST는 61이다. 결론적으로, 5-1, 5-2, 5-3의 SQL문장들은 동일한 문장, 동일한 결과를 제공하지만 이 문장이 실행될 수 있는 실행계획은 다양하다는 것을 알 수 있다.
이와 같이, 비용기반 옵티마이저는 여러 가지 실행계획 중에 가장 비용이 적게 발생하는 I_BIG_EMP_EMPNO 인덱스를 이용한 실행계획을 Optimal Plan으로 선택하게 된다.

 

6-2) 비용계산 분석 명령어

다음 문장은 비용분석기(Estimator)와 실행계획 생성기(Plan Generator)에 의해 비용 분석된 결과를 모니터링 하는 방법이다.
SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
SQL> SELECT *
FROM BIG_EMP, BIG_DEPT, ACCOUNT
WHERE BIG_EMP.DEPTNO = BIG_DEPT.DEPTNO
AND ACCOUNT.CUSTOMER = BIG_EMP.EMPNO
SQL> EXIT
[C:\] CD C:\ORACLE\ADMIN\ORA92\UDUMP
[C:\] DIR
ORA92_ORA_xxxx.trc <-- 워드패드 편집기를 통해 결과 확인

 

<분석결과>

*** 2005-07-17 10:41:27.000
*** SESSION ID:(10.976) 2005-07-17 10:41:27.000
QUERY
SELECT * FROM BIG_EMP, BIG_DEPT, ACCOUNT
WHERE BIG_EMP.DEPTNO = BIG_DEPT.DEPTNO AND ACCOUNT.CUSTOMER
= BIG_EMP.EMPNO

***************************************
GENERAL PLANS
***************************************
Join order[1]: BIG_DEPT [BIG_DEPT] BIG_EMP [BIG_EMP] ACCOUNT
[ACCOUNT]
Now joining: BIG_EMP [BIG_EMP] *******
NL Join
Outer table: cost: 2 cdn: 289 rcz: 19 resp: 2
Inner table: BIG_EMP
Access path: tsc Resc: 19
Join: Resc: 5493 Resp: 5493
Join cardinality: 28853 = outer (289) * inner (28955) * sel (3.4480e-003)
[flag=0]
Best NL cost: 5493 resp: 5493
SM Join
Outer table:
resc: 2 cdn: 289 rcz: 19 deg: 1 resp: 2
Inner table: BIG_EMP
Best SM cost : 257

***********************
Join order[2]: BIG_DEPT [BIG_DEPT] ACCOUNT [ACCOUNT] BIG_EMP
[BIG_EMP]
Now joining: ACCOUNT [ACCOUNT] *******

***********************
Join order[3]: BIG_EMP [BIG_EMP] BIG_DEPT [BIG_DEPT] ACCOUNT
[ACCOUNT]
Now joining: BIG_DEPT [BIG_DEPT] *******

***********************
Join order[4]: BIG_EMP [BIG_EMP] ACCOUNT [ACCOUNT] BIG_DEPT
[BIG_DEPT]
Now joining: ACCOUNT [ACCOUNT] *******

***********************
Join order[5]: ACCOUNT [ACCOUNT] BIG_DEPT [BIG_DEPT] BIG_EMP
[BIG_EMP]
Now joining: BIG_DEPT [BIG_DEPT] *******

***********************
Join order[6]: ACCOUNT [ACCOUNT] BIG_EMP [BIG_EMP] BIG_DEPT
[BIG_DEPT]
Now joining: BIG_EMP [BIG_EMP] *******

 

<분석결과 평가>

 

분석된 결과 중에 Join order[n]는 여러 개의 테이블을 조인하는 경우 어떤 테이블부터 검색하여 어떤 순서에 의해 조인해 나가는 방법인지 분석하는 경우를 나타낸다.
Join order[1]에서 Best NL cost: 5493은 중첩루프 조인의 비용 결과이며, Best SM cost : 257은 소트-머지 조인의 경우 비용 결과이다.
비용기반 옵티마이저는 하나의 조인순서가 결정되면 다양한 실행 방법들에 대한 비용을 일일이 계산하게 된다. 그 중에 가장 적은 비용이 발생하는 조인 순서와 실행 방법을 실행 계획으로 선택하게 되는 것이다.

 

 

 

제공 : DB포탈사이트 DBguide.net