[오라클]비용기반 옵티마이저

============================================================================== 
옵티마이저 종류 

 - 공식기반 옵티마이저 : 오라클 6.x대 버젼까지 쓰던 옵티마이저로써, 공식에 의해 
                                실행. 
 - 비용기반 옵티마이저 : 오라클 7.x대 버젼부터 ~ 현재까지 쓰는 것으로써, 유리한 
    처리방식을 선택하여, 실행 
                                테이블의 데이터크기, 상황에 따라서 처리방식이 달라진다. 
=============================================================================== 

비용기반 옵티마이저 환경설정 

    - 환경설정 : 기본설정은 공식기반하고 같다. 하지만 중요한것은 룰베이스는 공식에의해 
  적절하게 작성하면 되지만, cost베이스는 반드시해야할 것이 있다. 
  ANALYZE TABLE에 의해 해당되는 테이블에 대한 통계정보를 반드시 만들어야 
                한다. 상황에 따라 처리방식이 달라지게 때문에 그러한 이유때문에 그때마다 
                통계를 작성해야한다. 밑의 설정의 3번. 

    1) SESSION-LEVEL : ALTER SESSION SET OPTIMIZER_MODE = CHOOSE; 
    2) INSTANCE-LEVEL : OPTIMIZER_MODE = CHOOSE 
    3) ANALYZE TABLE emp [ COMPUTE STATISTICS ]; 
    [ ESTIMATE STATISTICS n [ percent | rows ]; 
    [ DELETE STATISTICS ]; 
 1번이나 2번중 아무거나 설정해도 상관없음. 

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

실행계획 

    : 분석하고자 하는 테이블에 index도 있다면 index도 반드시 통계를 작성해야한다. 

 CREATE INDEX I_emp_deptno ON big_emp(deptno); 
 CREATE unique INDEX I_emp_empno ON big_emp(empno); 

 ANALYZE TABLE big_emp COMPUTE STATISTICS; 
 ANALYZE INDEX I_emp_deptno COMPUTE STATISTICS; 
 ANALYZE INDEX I_emp_empno COMPUTE STATISTICS; 

 SELECT ename 
 FROM big_emp 
 WHERE deptno = 20 
 AND empno BETWEEN 100 AND 200 
 ORDER BY ename; 

 Execution Plan 
 ------------------------------------------------------------------------ 
 0 SELECT STATEMENT Optimizer=CHOOSE 
 1 0 SORT (ORDER BY) 
 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_EMP' 
 3 2 INDEX (RANGE SCAN) OF 'I_EMP_EMPNO' (NON-UNIQUE) 
    실행통계를 보면 Rule base에서 deptno = 20가 우선순위면에서 높아서 deptno를 기준으로 
    조회를 하는 반면 Cost base에선 데이터양에 따라 AND empno BETWEEN 100 AND 200 적게 조회 
    되어 INDEX (RANGE SCAN) OF 'I_EMP_EMPNO' (NON-UNIQUE) scan을 하게 된다. 
============================================================================== 

1) Full-Table Scan 

    Cost = 전체 블록수 / DB_FILE_MULTIBLOCK_READ_COUNT 

 (1) 전체 블록수는 ANALYZE TABLE 명령어 수행 후 USER_TABLES의 BLOCKS 컬럼의 값 
 (2) DB_FILE_MULTIBLOCK_READ_COUNT는 INIT<DB명>.ora 파일에 정의(기본값 : 8) 

    Full-Table Scan의 경우 한 블럭당 검색을 하려면 너무 늦기 때문에 .ora파일에 설정되어 
    있는 기본값 8개씩 검색을 한다. 

    ANALYZE TABLE big_emp COMPUTE STATISTICS; 
 SELECT blocks FROM user_tables 
 WHERE table_name = ‘BIG_EMP’; 
 Blocks                                        370 
------------- COST = ------------ = 46 
 370                                                8 
============================================================================== 

2) INDEX SCAN 
    Cost = (선택도 * Cluster-Factor) 

  1.선택도(SELECTIVITY)는 해당 행이 검색될 수 있는 비율을 의미한다.(조건에 의해 달라짐) 
  2. CLUSTER-FACTOR는 해당 테이블의 행이 몇 개의 블록에 저장되어 있는지를 나타내는 비율 
      ANALYZE TABLE 명령어 수행 후 User_tables에 NUM_ROWS/BLOCKS-EMPTY_BLOCKS 
      컬럼의 값 

 1) ANALYZE TABLE big_emp COMPUTE STATISTICS; 
 2) ANALYZE INDEX I_emp_deptno COMPUTE STATISTICS; 
 3) 선택도= 1/6 
  예): deptno(부서코드)가 6가지 종류가 있을때, where deptno = '3'이라고 했다면, 
      결국 6개중의 하나를 선택했으므로 1/6이된다. 
 Cluster-Factor= 80(800개의 행, 블록당 10개의 행)이면 


선택도(SelectVity) 
  : 내가 어떤 조건들을 부여했을때 데이터가 선택될수 있는 확률 

  예제) Selectivity 는 1에 가까울수록 실행속도가 빠르다. 

  1) Unique-Key/Primary-Key의 경우 
    SELECT * FROM emp WHERE empno = 200; 
 => Selectivity = 1 (좋은 선택도) 
      이유 : Unique-Key 거나 Primary-Key는 유일한 값으로 하나밖에 나올수 없는 값이기 때문에 
            Selectivity = 1 

  2) 2) Non Unique-Key의 경우 
    SELECT * FROM emp WHERE ename = ‘SMITH’; 
 => Selectivity = 1 / distinct-keys => 1/ 4 = 0.07 (좋은 선택도) 
      이유 : emp테이블에 enme이 전부 4명이 있다면.. 그 확률상 
            1/4이 되어서 Selectivity = 0.07이 된다. 

  3) 값을 가진 비동등 조건식의 경우 
  SELECT * FROM emp WHERE empno < 200; 
 => Selectivity = (범위값 ? 최소값) / (최대값 ? 최소값) 
 = (200 ? 1) / (29999 ? 1) = 199 / 29998 = 0.007 
      이유 : 데이터가 29999건이 들어있는 테이블이라 가정한다.. 

 SELECT * FROM emp WHERE empno BETWEEN 100 AND 200; 
 => Selectivity = (최대조건값 ? 최소조건값) / (최대값 ? 최소값) 
 = (200 ? 100) / (29999 ? 1) = 100 / 29998 = 0.003 

    4) 바인드 변수를 가진 비동등 조건식의 경우 
 SELECT * FROM emp WHERE empno < :a ; 
 => Selectivity = 25 % (나쁜 선택도) 
 이유 : 바인드변수를 사용했다면 29999건이 있다고 가정한다면 바인드 변수의 
              값이 무엇이 나올지 알수 없어서 오라클 서버가 가정치(추정치)를 선택하는데 
              Defalut값이 25%가중치를 선택한다. 

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

============================================================================== 
 비용계산 COST = ( 1/6 * 80 ) = (0.17 * 80) = 14 
    결국 이 조건은 14번의 access로 원하는 결과를 얻을 수 있다.