ORACLE Tip2008. 11. 13. 10:00

새로쓴 대용량 데이터베이스 : 뷰 병합

뷰 병합 (View Merging) 
: 뷰쿼리를 엑세스쿼리에 병합해 넣는 방식

* 일반적인 뷰병합

 뷰  뷰쿼리  뷰병합
 CREATE VIEW emp_10
(e_no, e_name, job, manager,
salary, commission, deptno)
AS
SELECT empno, ename, job, mgr, sal,
 comm, deptno
FROM emp
WHERE deptno = 10;		 SELECT e_no, e_name, salary, deptno
FROM emp_10
WHERE salary > 1000000;

SELECT empno, ename, sal, hiredate
FROM emp
WHERE deptno = 10
  AND sal > 1000000;

엑세스쿼리를 기준으로 뷰쿼리의 대응인자를 병합
- 엑세스쿼리에 있는 뷰를 원래 테이블인 emp로 변환
- 남아있는 조건절을 다시 엑세스쿼리에 병합
- 컬럼들도 대응되는 원래 테이블의 컬럼들로 병합

* View Merging이 불가능한 경우 : 엑세스쿼리에 있는 조건들을 뷰쿼리에 진입(Pushing predicate)
- 집합연산(UNION, UNION ALL, INTERSECT, MINUS)
- CONNECT BY
- ROWNUM을 사용한 경뷰우
- SELECT-List의 그룹함수(AVG, COUNT, MAX, MIN, SUM)
- GROUP BY , SELECT-List의 DISTINCT : 단, Merge힌트를 사용했거나 관련 파라미터가 Enable이면 뷰 병합 가능

* Pushing predicate : 뷰병합을 할 수 없는 경우를 대상으로 뷰쿼리 내부에 엑세스쿼리의 조건절을 진입
 뷰 뷰쿼리  뷰병합 
 CREATE VIEW emp_union_view
 (e_no, e_name, job, mgr, sal, comm, deptno)
AS
 SELECT empno, ename, job, mgr, sal, comm, deptno
 FROM regular_emp
 UNION ALL
 SELECT empno, ename, job, mgr, sal, comm, deptno
 FROM temporary_emp		 SELECT e_no, e_name, mgr, sal
FROM emp_union_view
WHERE deptno = 20;		  SELECT empno, ename, mgr, sal
FROM (
   SELECT empno, ename, mgr, sal
   FROM regular_emp
   WHERE detpno = 20
   UNION ALL
   SELECT empno, ename, mgr, sal
   FROM temporary_emp
   WHERE 90 = 20
) 

* GROUP BY 뷰의 병합 (Pushing predicate Merge)
 뷰 뷰쿼리  뷰병합 
CREATE VIEW emp_group_by_deptno
AS
SELECT deptno, AVG(sal) avg_sal, MIN(sal) min_sal, MAX(sal) max_sal
FROM emp
GROUP BY deptno		 SELECT e_no, e_name, mgr, sal FROM emp_group_by_deptno WHERE deptno = 10 SELECT deptno, AVG(sal) avg_sal, MIN(sal) min_sal, MAX(sal) max_sal
FROM emp
WHERE deptno = 10;
GROUP BY deptno   
위의 작업이 가능하려면 아래의 파라미터가 Enable 되어야 한다.
- complex_view_merging = enable
- Optimizer_secure_view_merging = enable

출처 : 새로쓴 대용량 데이터베이스 솔루션 vol.1

Posted by 항아리고미
ORACLE Tip2008. 11. 12. 14:05

새로쓴 대용량 데이터베이스 - 질의의 변환(Query Transforming)

질의변환의 목적은 옵티마이져가 보다 양호한 실행계획을 얻을 수 있도록 가능한 최대한 적절하게 SQL 형태를 변환하는 것

예1>
① sales_qty > 1200/12
② sales_qty > 100
③ sales_qty * 12 > 1200

①과 ②는 동일하게 취급.
③은 연산을 통하여 ②와 동일한 조건을 만들지 않는다. (소수점 등의 문제가 있음)
 
예2>
① job like 'SALESMAN'      
② job = 'SALESMAN' 

①과 ②는 동일하게 취급 단, 가변길이 타입만 가능하다. 

예3> 
① job IN ('CLERK', 'MANAGER')  
② job = 'CLERK' OR job='MANAGER' 

① sales_qty > ANY (:in_qty1, :in_qty2) 
② sales_qty > :in_qty1 OR sales_qty > :in_qty2

① WHERE 1000000 > ANY (SELECT sal FROM emp WHERE job='CLERK')
② WHERE EXISTS (SELECT sal FROM emp WHERE job = 'CLERK' AND 1000000 > sal)

①을 ②와 같은 형식의 조건절로 확장시킨다. 

예4> 
① sales_qty > ALL (:in_qty1, :in_qty2)
② sales_qty > :in_qty1 AND sales_qty > :in_qty2

①을 ②와 같은 형식의 조건절로 확장시킨다. 

① WHERE 100000 > ALL (SELECT sal FROM emp WHERE job='CLERK')
② WHERE NOT (1000000 <= ANY (SELECT sal FROM emp WHERE job = 'CLERK'))
③ WHERE NOT EXISTS (SELECT sal FROM emp WHERE job = 'CLERK' AND 1000000 <= sal)

①을 ②, ③과 같은 형식의 조건절로 확장시킨다. 

예5>
① sales_qty BETWEEN 100 AND 200
② sales_qty >= 100 AND sales_qty <= 200

①을 ②와 같은 형식의 조건절로 확장시킨다. 

① NOT (sal <30000 OR comm IS NULL) 
② NOT sal <30000 AND comm IS NOT NULL
③ sal >= 30000 AND comm IS NOT NULL

①을 ②로 변환하고 이것을 다시 ③으로 변환한다.

예6>
① NOT deptno = (SELECT deptno FROM emp WHERE empno=7689)
② deptno <> (SELECT deptno FROM emp WHERE empno=7689)
 
반대 비교연산을 찾아 대체시키는 변환을 한다.


이행성 규칙 (Transitivity principie)
: 쿼리변환의 한 경우 (비용기준 옵티마이져에서만 가능)

1. A = B and B = C 이면 A = C
WHERE column1 comparision_operators constant AND column1 = column2
=> column2 comparison_operators constant
comparision_operators : =, !=, ^=, <, <>, >, <=, >=
constant : 연산, SQL 함수, 문자열, 바인드 변수, 상관관계 변수를 포함하는 상수 수식

SELECT *
FROM emp e, dept d
WEHRE e.deptno = 20
  AND e.deptno = d.deptno;
=> d.deptno = 20 을 추론 
만약 e.deptno 인덱스가 없다면 EMP 테이블을 전체 테이블 엑세스를 하면서 매번 dept 테이블 연결
이행성 규칙에 의해 실행계획이 DEPT 테이블을 먼저 인텍스로 엑세스하는 실행계획이 가능

* 비교값이 상수 수식이 아닌 컬럼이라면 이행성 규칙은 적용되지 않는다.
WHERE column1 comparision_operators column3 AND column1 = column2
=> (X) column2 comparison_operators column3

2. OR 조건들의 UNION ALL 분기
: UNION ALL로 각각의 인덱스를 경유하는 실행계획을 수립하고 이를 결합
변환기준
- OR를 사용한 조건절을 분기시켰을 때 각각이 인덱스 접근 경로로 이용할 수 있다면 변환을 수행
- 인덱스 사용불가로 전체 테이블을 스캔하거나, OR 조건이 검증기능만 수행하면 변환하지 않음.
- 즉, 분기된 각각의 엑세스가 불필요한 범위를 엑세스하지 않는다고 판단한 경우에만 적용
실행계획
- 'IN-LIST ITERATOR' (IN사용시)나 'CONCATENATION'(OR사용시)으로 실행계획이 나타남
- 강제적인 적용을 위해 'USE_CONCAT' 힌트를 사용
- 함부로 사용하면 엑세스가 불필요하게 분기되어 오히려 처리량이 크게 증가

* 각각이 독립적인 인덱스가 있을경우
SELECT * FROM emp WHERE job='CLERK' OR deptno = 10;
=>
SELECT * FROM emp WHERE deptno = 10
UNION ALL
SELECT * FROM emp WHERE job='CLERK' and deptno<> 10;

3. 서브쿼리를 가진 복잡한 문장을 조인 문장으로 변환   
- 같은 기능의 조인 문장으로 변환하여 최적화 (서브쿼리가 '1' 집합이므로 조인을 해도 메인쿼리에 영향을 주지 않음)

* 서브쿼리의 연결고리가 '1'이 보장되어야 함.
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE loc='NEW YORK')  
=> SELECT emp.* FROM emp, dept WHERE emp.deptno = dept.deptno AND dept.loc = 'NEW YORK'

- 현재의 문장을 그대로 최적화하는 방법
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = 20)
 제공자 역할
 - 서브쿼리를 먼저 수행하여 결과를 메인쿼리에 제공
 - 서브쿼리 실행결과를 메인쿼리의 처리주관 인덱스에 제공 할 수 있을 떄 적용
 확인자 역할가
 - 메인쿼리가 실행되면서 서브쿼리를 확인하는 필터링 처리
 - 각의 집합을 엑세스한 후 머지, 혹은 해쉬조인으로 수행

출처 : 새로쓴 대용량 데이터베이스 솔루션 vol.1

Posted by 항아리고미
ORACLE Tip2008. 11. 12. 12:03

새로쓴 대용량 데이터베이스 - 옵티마이져의 최적화 절차

SQL -> 
        Parser -> (Parsed Query) -> 
                질의변환기:Query Transformer -> (Transformed Query) -> 
                        비용산정기:Estimator -> (Query + Estimates) -> 
                                실행계획 생성기:Plan Generator -> (Query Plan) -> 
                                        Row Source Generator
     
- 사용자가 실행한 SQL은 데이터 딕셔너리를 참조하여 파싱을 수행
- 옵티마이져는 파싱 결과를 이용해 논리적으로 적용 가능한 실행계획 형태를 선택하고, 힌트를 감안하여 
  일차적으로 잠정적인 실행계획들을 생성
- 데이터 딕셔너리의 통계정보(데이터의 분포도, 테이블 저장구조, 인덱스 구조, 파티션 형태, 비교연산자) 등을 감안하여 
  각 실행계획의 비용을 계산
- 실행계획들의 산출된 비용을 비교하여 가장 최소의 비용을 가진 실행계획을 선택(최저가 입찰 방식이므로 항상 최적의 
  결정이라고만 할 수는 없음)

1) 질의 변환기 (Query Tranformer)
: 보다 양호한 실행계획을 얻을 수 있도록 적절한 SQL 형태를 변환하는 것.

뷰병합 : View Merging
: 뷰 정의 시에 지정한 쿼리(뷰쿼리)를 엑세스가 수행행되는 쿼리(엑세스쿼리)에 병합(Merge)
- 수행시킨 기준으로 뷰에 지정한 부분만 보정하므로 뷰로 발생되는 불이익이 크게 감소
- 엑세스 쿼리의 조건들이 제 역할을 할 수 있고, 뷰 쿼리의 조건까지 추가
- 그러나 몇 가지 제한요소가 있어 이를 준수하지 않으면 뷰병합은 불가능

조건절 진입 : Predicate Pusing
: 뷰 병합을 할 수 없는 경우를 대상으로 뷰쿼리 내부에 엑세스쿼리의 조건절을 진입시키는 질의 변환
- 엑세스쿼리를 뷰쿼리로 진입시키는 방법
- 이렇게 가능한 모든 방법을 동원해서 부여 조건을 뷰쿼리 내에 최대한 반영시켜 보다 양호한 실행계획을 유도

서브쿼리 비내포화 : Subquery Unnesting
: 서브쿼리는 경우에 따라서 내표관계를 해제하여 조인형식으로 대체함으로써 보다 양호한 수행속도를 얻을 수 있음.
- 실제로 대부분의 서브쿼리는 논리적으로 가능하다면 이러한 변환을 거침
- 서비쿼리 비내포화가 불가능하면 서브쿼리를 먼저 수행하거나 나중에 수행하는 실행계획을 수립
- 서브쿼리가 수행되는 순서에 따라서 수행속도에 매우 큰 차이가 나타날 수 있음

실체뷰의 쿼리 재생성 : Query Rewrite
: 실체뷰는 테이블과 밀접한 논리적 관계를 가진 물리적 집합이므로 최적의 집합을 처리하도록 쿼리를 재생성
- 일종의 확장된 뷰병합 기능
- 상황에 따라 어떤 물리적 집합을 엑세스할 것인지 고민할 필요가 없으므로 매우 유용한 기능
- 실체뷰의 쿼리 재생성은 비용 기준에 의해 결정 (테이블 엑세스가 적은 비용을 가지면 쿼리 재생성 하지 않음)

OR 조건의 전개 : OR expansion
: OR 조건이 처리주관 조건이 되면 여러 개의 단위 쿼리로 분기하고 UNION ALL로 연결하는 질의로 변환
- 단지 체크 조건으로만 사용되는 경우에 이와 같은 전개가 발생하면 오히려 큰 비효율이 발생
- 옵티마이져는 비용기준에 의해 전개여부를 결정

사용자 정의 바인드 변수 엿보기 : Peeking
: 쿼리 내에 사용자가 조건절에 지정한 바인드 변수가 있을 때 최초에 수행될 때 적용되었던 값을 이용해 실행계획을 수립하고,
  다음 수행부터는 공유하는 방법이다.


2) 비용 산정기 (Estimator)

선택도 : Selectivity
: 대상 집합에서 해당 조건을 만족하는 로우가 차지하는 비율
- 선택도 판정 단위는 개별 컬럼이 아니라 해당 엑세스를 주관할 수 있는 조건들.
- 선택도는 0.0 ~ 1.0 사이의 값을 갖도록 생성
- 0.0 은 대상 집합에서 전혀 존재하지 않는다는 것을 의미
- 1.0 은 모든 대상 집합이 모두 해당된다는 것을 의미
- 선택도의 값이 낮다는 것은 전체에서 차지하는 비율이 낮다는 것, 이는 곧 변별력이 좋다(읽을 양이 적다)는 것을 의미
- 좋은 선택도를 가진 것을 처리주관으로 결정하면 보다 적은 처리 범위를 엑세스
예>
SELECT *
FROM EMP
WHERE DEPTNO = 20
  AND JOB = 'CLERK'
  AND ENAME LIKE 'SMITH%'
EMP Table 의 인덱스 : 선택도
- EMPNO     : 해당사항 없슴.
- ENAME      : 0.02 (2%)
- DEPTNO + JOB   : 0.1 (10%)
- FULL SCAN     : 1.0 (100%)

카디널리티 : Cardinality
: 판정 대상이 가진 결과 건수 혹은, 다음 단계로 들어가는 중간결과 건수
- 산정방법 : 선택도(Selectivity)와 전체 로우수(Num_rows)로 계산
카디널리티가 필요한 이유
- 선택도는 단지 비율일 뿐임. 백만건의 1%와 백 건의 1%는 비율은 같지만 절대량은 같지 않다.
- 같은 대상 집합에 대해서는 비율만으로도 충분하다.
- 그러나 만약 조인의 순서나 방향 등의 결정을 위해 먼저 수행될 집합을 선택해야 한다면 비율만으로는 결정 할 수 없다.
  
비용 : Cost 
: 실행계획 상의 각 연산들을 수행할 때 소요 시간비용을 상대적으로 계산한 예측치
- 산정방법 : 통계정보에 CPU와 메모리 상황, 디스트 I/O 비용도 고려하여 계산
- 동일한 평가결과의 우선순위 결정은 규칙기준의 경우 로우 캐시(Cache)에 나타난 순서로 비용기준은 인덱스명의 ASCII 값으로 한다.
신뢰의 한계성
- 비용산정 과정에서 수많은 가정들을 세우고 다양한 계산식을 적용
- 그러나 가정들이 완벽할 수 없는 불완전한 가정이므로 때로는 잘못된 비용을 산정하게 되는 한계가 발생
- 이런 문제의 보완을 위해서 다양한 힌트들과 다양한 초기화 파라미터들이 계속 추가되고 있다.  (옵티마이져의 진화)


3) 실행계획 생성기 (Plan Generator)
: 쿼리를 처리할 수 있는 적용 가능한 실행계획을 선별하고, 그들에 대한 비교검토를 거쳐 가장 최소의 비용을 가진 것을 선택
- 최적화에 최대한의 사간을 투자 한다면 조금 더 나은 실행계획을 얻을 수 있을지는 모르지만 이로 인한 부하가 전체 수행시간에 너무 많은 비중을 차지한다면 결코 적절하다고 할 수 없다.
- 모든 경우를 다 검토할 수는 없다 따라서 적응적 탐색(Adaptive Search)과 경험적(Heuristic) 기법을 적용하여 초기치를 선택(Cutoff)하는 전략을 사용한다.

적응적 탐색 : Adaptive Search
- 쿼리수행의 총 예상수행시간에 대해 최적화를 하는 시간이 일정비율을 넘지 않도록 하는 탐색 전략
- 마치 대국시간이 짧은 속기바둑의 제한시간과 대국 시간이 1-2일인 세계대회의 제한시간이 다르게 부여되는 것과 매우 유사함.

경험적 선택 : Heuristic Cutoff
- 탐색도중이더라도 최적이라고 판단되는 실행계획을 발견하면 더 이상 진행하지 않고 멈추는 것)
- 최적이거나 최소한 아주 좋은 실행계획일 것이라고 판단되는 실행계획들의 일정량을 선별하고 정렬한후에 그 중에서 가장 양호한 것을 선택

출처 : 새로쓴 대용량 데이터베이스 솔루션 vol.1

Posted by 항아리고미
ORACLE Tip2008. 11. 12. 10:30

Stored Outlines (실행계획 고정)

특정 SQL의 실행계획 번번히 바뀌는 경우가 발생하였다.
바뀔때 plan이 이상하게 풀릴경우 응답시간이 10분을 초과하는 일이 발생하여.
특정 SQL에서 plan을 고정시킬때 오라클에서는 stored outlines 를 제공한다.
 
-- outline 생성  (동적 SQL에 대한 plan 고정)
create or replace outline mlb_bp_contract_1  for category mlb
on
SELECT /*+ NO_MERGE(A) NO_PUSH_PRED(A) */ A.SERVICE_CODE, A.SERVICE_NAME,
    DECODE(C.POTTERY, 'O', DECODE(C.PC_CODE, :1, DECODE(NVL(B.SERVICE_CODE, 'NULL'), 'NULL', 'X', DECODE(A.MUSIC_CODE, 'NULL', 'X', 'O')), 'X'),
                        DECODE(NVL(B.SERVICE_CODE, 'NULL'), 'NULL', 'X', DECODE(A.MUSIC_CODE,'NULL', 'X', 'O')))  SERVICE_STATUS,
    (SELECT DECODE(COUNT(1), 0, 'X', 'O') FROM TMLBG010 WHERE MUSIC_CODE = :2 AND SERVICE_CODE = A.SERVICE_CODE AND PC_CODE = :3) CONTENTS_STATUS
FROM (
   SELECT NVL(C.MUSIC_CODE, 'NULL') MUSIC_CODE, S.SERVICE_CODE, S.SERVICE_NAME
   FROM TMLBC010 C, TMLBS010 S
   WHERE C.MUSIC_CODE(+) = :4
     AND C.SERVICE_CODE(+) = S.SERVICE_CODE
     AND C.PC_CODE1(+) IS NOT NULL
     AND C.PC_CODE2(+) IS NOT NULL
     AND C.PC_CODE3(+) IS NOT NULL
) A, (
   SELECT SERVICE_CODE
   FROM TMLBS050
   WHERE USER_ID=:5
     AND SERVICE_YN = 'Y'
) B, (
   SELECT MUSIC_CODE, SERVICE_CODE, PC_CODE, 'O' POTTERY
   FROM TMLBC050
   WHERE MUSIC_CODE = :6
     AND TO_CHAR(SYSDATE, 'YYYYMMDD') BETWEEN START_DATE AND END_DATE
     AND USE_YN = 'Y'
) C
WHERE A.SERVICE_CODE = B.SERVICE_CODE(+)
  AND A.SERVICE_CODE = C.SERVICE_CODE(+);
==> Outline created.   
       
-- 생성한 outline 조회         
select * from user_outlines;
-- 처음에는 USED 값이 UNUSED 로 지정되어있다.

-- outline에 저장된 SQL의 plan확인.
select * from user_outline_hints;

-- 생성한 outline이 여러개 일때 그중 특정 category의 outline을 사용                  
alter system set use_stored_outlines = mlb;
 
--  특별한 session에 outline을 적용하고자 한다면 아래를..
alter session set use_stored_outlines = mlb;

-- use_stored_outlines 를 한후에 저장된 SQL을 실행하고 나서 user_outlines 테이블의 USED 컬럼 값이 USED로 바뀌면 outline이 제대로 사용됨을 확인 할 수 있다.
 
참고로 outline을 생성하고자 할때는 생성할 수 있는 권한을 가지고 있어야 한다.

Posted by 항아리고미

티스토리툴바