행위

오라클 퍼포먼스 튜닝

DB CAFE

목차

1 오라클 퍼포먼스 튜닝

1.1 개요

퍼포먼스란?
  1. 이용자가 만족하고 사용하는 기준을 만족시킨다.
  2. 시스템의 장비에 대한 리소스의 범위 내에서 목표로 하는 성능을 끌어낸다.
  3. 시스템을 견실히 한다.
  4. 다음 시스템을 계획할 때 기준치를 설정한다.

1.1.1 퍼포먼스 튜닝의 순서

  1. 구체적인 목표치를 결정한다.
  2. 불필요한 동작이 없는가 점검한다.
  3. 베이스 라인을 수집한다.
     베이스 라인이라고 하는 것은 정상 작동시의 정보를 말한다.
  1. 대체방법에 대한 리스트를 작성하고 최우선 순위를 정한다.

1.1.2 퍼포먼스 튜닝 실행시의 주의점

  1. 파라미터는 한번에 하나씩만 변경한다.
  2. 파라미터의 수치는 원래대로 돌릴 수 있게 해둔다.
  3. 변경된 파라미터의 수치는 문서로 남긴다.
  4. 관련된 수치를 비교할 때는 같은 타이밍에 수치를 수집한다.
  5. 한번으로 끝내지않는다.

1.1.3 MERGE 조인

1.2 튜닝 대상 조회

1.2.1 CPU를 많이 사용하는 세션의 식별

SELECT A.SID
     , C.SERIAL#
     , A.VALUE
     , C.USERNAME
     , C.STATUS
     , C.PROGRAM
  FROM V$SESSTAT A
     , V$STATNAME B
     , V$SESSION C
 WHERE A.STATISTIC# = B.STATISTIC# 
   AND A.SID = C.SID 
   AND B.NAME = 'CPU used by this session' 
   AND A.VALUE > 0 ORDER BY A.VALUE DESC;

1.2.2 Disk Read 가 많은 SQL문 찾기

SELECT DISK_READS, SQL_TEXT 
  FROM V$SQLAREA 
 WHERE DISK_READS > 100 
 ORDER BY DISK_READS DESC;

1.2.3 Rollback Segment를 사용하고 있는 SQL문 조회

SELECT A.NAME , B.XACTS   , C.SID
     , C.SERIAL#   , C.USERNAME  , D.SQL_TEXT
  FROM V$ROLLNAME A    , V$ROLLSTAT B
     , V$SESSION C   , V$SQLTEXT D
     , V$TRANSACTION E
 WHERE A.USN = B.USN 
   AND B.USN = E.XIDUSN 
   AND C.TADDR = E.ADDR 
   AND C.SQL_ADDRESS = D.ADDRESS 
   AND C.SQL_HASH_VALUE = D.HASH_VALUE 
 ORDER BY A.NAME, C.SID, D.PIECE;

1.2.4 오래도록 수행되는 Full Table Scan를 모니터링

 

SELECT SID
     , SERIAL#
     , OPNAME
     , TO_CHAR(START_TIME, 'HH24:MI:SS') AS "START"
     , (SOFAR / TOTALWORK) * 100 AS "PERCENT_COMPLETE"
  FROM V$SESSION_LONGOPS;

1.2.5 CPU를 많이 사용하는 세션의 식별(SQL TEXT 조회)

SELECT A.*

     , (SELECT   SS.SQL_TEXT
           FROM     V$SQLAREA SS
           WHERE    SS.ADDRESS = A.SQL_ADDRESS
           AND      ROWNUM <= 1
          ) AS SQL_TEST

  FROM (

          SELECT A.SID
               , C.SERIAL#
               , A.VALUE
               , C.USERNAME
               , C.STATUS
               , C.PROGRAM
               , C.SQL_ADDRESS
               , ROW_NUMBER() OVER (ORDER BY A.VALUE DESC) RN
          FROM      V$SESSTAT A
               , V$STATNAME B
               , V$SESSION C
          WHERE     A.STATISTIC# = B.STATISTIC#
          AND       A.SID = C.SID
          AND       B.NAME = 'CPU used by this session'
          AND       A.VALUE > 0
          AND       C.STATUS = 'ACTIVE'
          AND       C.USERNAME IS NOT NULL
          ) A

 WHERE A.RN <= 10;

1.2.6 현재 세션에서 10초이상 걸리는 쿼리 조회 (SELECT절)

SELECT ABS(SYSDATE - A.LAST_LOAD_TIME) * 24 * 60 * 60 AS SEC_TIEM, A.* 
  FROM V$SQLAREA A
     , V$SESSION B
 WHERE A.SQL_TEXT LIKE '%SELECT%' AND A.ADDRESS = B.SQL_ADDRESS AND B.STATUS = 'ACTIVE' AND A.ELAPSED_TIME >= 10 * 1000000 
   -- 실행계획에서 10초 이상 걸리는 쿼리를 조회(실제 걸리는 시간은 아님.) 
  AND A.PARSING_SCHEMA_NAME NOT IN ('SYS', 'SYSTEM', 'SYSMAN') 
  AND B.USERNAME IS NOT NULL;

1.2.7 현재 세션에서 PGA, UGA, CPU 사용량 세션별로 조회하는 쿼리

SELECT B.USERNAME
     , A.SID
     , A.PGA_USAGE
     , A.UGA_USAGE
     , A.CPU_USAGE_SECONDS
     , B.MACHINE
     , B.PROGRAM
     , B.MODULE
  FROM (SELECT B.SID
                , MAX(DECODE(C.NAME, 'session pga memory', TRUNC(B.VALUE / 1024 / 1024) || 'MB', 0)) AS PGA_USAGE
                , MAX(DECODE(C.NAME, 'session uga memory', TRUNC(B.VALUE / 1024 / 1024) || 'MB', 0)) AS UGA_USAGE
                , MAX(DECODE(C.NAME, 'CPU used by this session', (B.VALUE / 100) || ' Sec', 0)) AS CPU_USAGE_SECONDS
             FROM V$SESSTAT B
                , V$STATNAME C
           WHERE     B.STATISTIC# = C.STATISTIC#
           GROUP BY  B.SID) A
     , V$SESSION B

 WHERE B.SID = A.SID AND B.STATUS = 'ACTIVE' AND B.USERNAME IS NOT NULL;

1.2.8 sql 플랜 보기

select a.sql_id, a.child_number child,
       a.id
     , a.parent_id pid
      ----------------------------------------------------------------
     ,  lpad(' ', 2*(a.depth))||a.operation || decode(a.id,0,' (Optimizer='||a.optimizer||') ')
	    || decode(a.options,null,null,' (') || a.options || decode(a.options,null,null,')')
        || decode(a.object_name,null,null,' OF ' || a.object_owner||'.'||a.object_name 
                                         || ' ('|| a.object_type ||')')
        ----------------------------------------------------------------(
        || decode(a.cost||a.cardinality||a.bytes,null,null,' (')
        || decode(a.cost, null, null, ' Cost='||a.cost)
        || decode(a.cardinality, null, null, ' Card='||a.cardinality)
        || decode(a.bytes, null, null, ' Bytes='||a.bytes)
        || decode(a.cost||a.cardinality||a.bytes,null,null,' )')
        ----------------------------------------------------------------)
        || decode(a.other_tag,null,null,'(')||a.other_tag||decode(a.other_tag,null,null,')') as "Operation"
       ---------------------------------------------------------------
     , a.object_type         "Type"
       ----------------------------------------------------------------
     , a.cardinality         "E-Rows"
     , a.bytes               "E-Bytes"
     , a.cost
     , a.time                "E-Time"
       ----------------------------------------------------------------
     , a.partition_start     "Pstart"
     , a.partition_stop      "Pstop"
     , a.partition_id        "Pid"
       ----------------------------------------------------------------
     , a.qblock_name            "QB"
     , a.access_predicates
     , a.filter_predicates
       ----------------------------------------------------------------
  from v$sql_plan a
 where 1 = 1
   and a.sql_id = '5q1b8cn1s123u' --and plan_hash_value = ''
--   and a.child_number = 1
 order by a.sql_id, a.id, a.child_number;

1.2.9 인덱스 생성시 플랜 확인

EXPLAIN PLAN FOR
CREATE INDEX IDX_TB_BA_RGLR_99 ON TB_BA_RGLR (YEAR, EMP_NO, AGE) ;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY) ;

1.2.10 엑세스 VS FILTER 비교

https://blogs.oracle.com/sql/query-tuning-101%3a-comparing-execution-plans-and-access-vs-filter-predicates

2 SQL 튜닝 노하우

2.1 뷰 머징(View Merging)

2.1.1 뷰 머징 이란?

옵티마이저는 최적화 쿼리 수행을 위해 서브 쿼리블록을 풀어서 메인 쿼리와 결합(MERGE) 하려는 특성이 있음

  • SQL 원본
SELECT * 
  FROM ( SELECT * FROM EMP WHERE JOB = 'SALESMAN' ) A
     , ( SELECT * FROM DEPT WHERE LOC = 'CHICAGO' ) B
 WHERE A.DEPTNO = B.DEPTNO;
  • 서브쿼리나 인라인 뷰처럼 쿼리를 블록화 할 시, 가독성이 더 좋기 때문에 습관적으로 사용
  • View Merging 으로 오라클 내부에서 아래 형태로 SQL 변환
SELECT *  
  FROM EMP A
     , DEPT B
 WHERE A.DEPTNO = B.DEPTNO
   AND A.JOB = 'SALESMAN'
   AND B.LOC = 'CHICAGO';
  • View Merging 이유 : 옵티마이저가 더 다양한 액세스 경로를 조사대상으로 삼을 수 있음

2.1.2 View Merging 제어 힌트

  1. merge
  2. no_merge

2.1.3 단순 뷰(Simple View) Merging

  1. 가능한 조건
    1. 조건절과 조인문만을 포함하는 단순 뷰(Simple View)일 경우, no_merge 힌트를 사용하지 않는 한 언제든 Merging 발생
    2. group by, distinct 연산을 포함하는 복합뷰(Complex View)는 파라미터 설정 또는 힌트 사용에 의해서만 뷰 Merging 가능
  2. 불가능한 조건
    1. 집합 연산자, connect by, rownum 등을 포함한 복합 뷰(Non-mergeable Views)는 뷰 Merging 불가능
-- Simple View 예제 
create or replace view emp_salesman as
select empno, ename, job, mgr, hiredate, sal, comm, deptno
from   emp
where job = 'SALESMAN';

2.1.3.1 Simple View 뷰 No Merging 최적화

SQL> select /*+ no_merge(e) */ e.empno, e.ename, e.job, e.mgr, e.sal, d.dname
  2  from   emp_salesman e, dept d
  3  where  d.deptno = e.deptno
  4  and    e.sal >= 1500 ;
 
      EMPNO ENAME      JOB              MGR        SAL DNAME
---------- ---------- --------- ---------- ---------- --------------
      7844 TURNER     SALESMAN        7698       1500 SALES
      7499 ALLEN      SALESMAN        7698       1600 SALES

Execution Plan
-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |     2 |   156 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |              |       |       |            |          |
|   2 |   NESTED LOOPS                 |              |     2 |   156 |     4   (0)| 00:00:01 |
|   3 |    VIEW                        | EMP_SALESMAN |     2 |   130 |     2   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| EMP          |     2 |    58 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | EMP_SAL_IDX  |     8 |       |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN           | PK_DEPT      |     1 |       |     0   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | DEPT         |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("JOB"='SALESMAN')
   5 - access("SAL">=1500)
   6 - access("D"."DEPTNO"="E"."DEPTNO")

2.1.3.2 Simple View 뷰 Merging 최적화

SQL> select /*+ merge(e) */ e.empno, e.ename, e.job, e.mgr, e.sal, d.dname
  2  from   emp_salesman e, dept d
  3  where  d.deptno = e.deptno
  4  and    e.sal >= 1500 ;

Execution Plan
---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     2 |    84 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |             |       |       |            |          |
|   2 |   NESTED LOOPS                |             |     2 |    84 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP         |     2 |    58 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_SAL_IDX |     8 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | PK_DEPT     |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPT        |     1 |    13 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("JOB"='SALESMAN')
   4 - access("SAL">=1500)
   5 - access("D"."DEPTNO"="DEPTNO")

<일반 조인문>

SQL> select e.empno, e.ename, e.job, e.mgr, e.sal, d.dname
  2  from   emp e, dept d
  3  where  d.deptno = e.deptno
  4  and    e.job = 'SALESMAN'
  5  and    e.sal >= 1500;
Execution Plan

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     2 |    84 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |             |       |       |            |          |
|   2 |   NESTED LOOPS                |             |     2 |    84 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| EMP         |     2 |    58 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_SAL_IDX |     8 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN          | PK_DEPT     |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPT        |     1 |    13 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("E"."JOB"='SALESMAN')
   4 - access("E"."SAL">=1500)
   5 - access("D"."DEPTNO"="E"."DEPTNO")
* 단순 뷰를 Merging 할 경우, 파라미터 or 힌트 설정을 하지 않을 경우 일반 조인문과 똑같은 형태로 변환 후 처리

2.1.4 복합 뷰(Complex View) Merging

  • group by절 , select-list에 distinct연산자 포함하는 복합 뷰
  • _complex_view_merging 파라미터 값이 true로 설정할 때만 Merging 발생
  • 10g에서는 복합 뷰 Merging을 일단 시도하지만, 원본 쿼리에 대해서도 비용을 같이 계산해 Merging했을 때의 비용이 더 낮을 때만 그것을 채택 (비용기반 쿼리 변환)
  • 10g 이전 _complex_view_merging 파라미터 기본 값 (8i : false, 9i : true)
  • complex_view_merging 파라미터를 true로 설정해도 Merging 될 수 없는 복합 뷰
    • 집합(set)연산자( union, union all, intersect, minus )
    • connect by절
    • ROWNUM pseudo 컬럼
    • select-list에 집계 함수(avg, count, max, min, sum)사용 : group by 없이 전체를 집계하는 경우를 말함
    • 분석 함수
    • 복합뷰를 포함한 쿼리 (뷰 머징 발생 시)
SQL> select d.dname, avg_sal_dept
  2  from   dept d
  3        ,(select deptno, avg(sal) avg_sal_dept from emp group by deptno) e
  4  where  d.deptno = e.deptno
  5  and    d.loc='CHICAGO';

DNAME          AVG_SAL_DEPT
-------------- ------------
SALES            1566.66667
 
Execution Plan
------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     3 |    81 |     5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY                |                |     3 |    81 |     5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS                |                |       |       |            |          |
|   3 |    NESTED LOOPS               |                |     5 |   135 |     4   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL         | DEPT           |     1 |    20 |     3   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | EMP_IDX_DEPTNO |     5 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| EMP            |     5 |    35 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("D"."LOC"='CHICAGO')
   5 - access("D"."DEPTNO"="DEPTNO")



복합뷰를 일반 조인절로 변경한 쿼리

SQL> select d.dname,avg(sal)
  2  from   dept d,emp e
  3  where  d.deptno=e.deptno
  4  and    d.loc='CHICAGO'
  5  group by d.rowid,d.dname;

DNAME            AVG(SAL)
-------------- ----------
SALES          1566.66667
 
Execution Plan
------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     1 |    27 |     5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY                |                |     1 |    27 |     5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS                |                |       |       |            |          |
|   3 |    NESTED LOOPS               |                |     5 |   135 |     4   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL         | DEPT           |     1 |    20 |     3   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | EMP_IDX_DEPTNO |     5 |       |     0   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID| EMP            |     5 |    35 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("D"."LOC"='CHICAGO')
   5 - access("D"."DEPTNO"="E"."DEPTNO")


  • 뷰머징이 발생 할 경우
    • dept테이블에서 loc컬럼이 'CHICAGO'인 데이터를 먼저 필터링하고 조인, 조인대상 집합만 group by 실행
  • 뷰머징이 발생되지 않을 경우
    • emp 테이블의 모든 테이블을 group by 한 후 필터링하게 되면서 불필요한 레코드 엑세스 발생



2.1.5 비용기반 쿼리 변환의 필요성

9i : 복합 뷰를 무조건 머징 => 대부분 더 나은 성능 제공하지만 복합뷰 머징 시 그렇지 못할 때가 많음

 ☞ no_merge 힌트 등 뷰안에 rownum 을 넣어주는 튜닝 기법 활용

10g 이후 비용기반 쿼리 변환 방식으로 처리

  ☞ _optimizer_cost_based_transformation 파라미터 사용 → 설정값 5가지 (on, off, exhaustive, linear, iteraive)


on  : 적절한 것을 스스로 선택 exhaustive : cost가 가장 저렴한 것 선택 linear  : 순차적 비교 후 선택 literation : 변환이 수행 유무에 따른 cost를 비교하기 위한 경우의 수로 listeration 정의



opt_param 힌트 이용으로 쿼리 레벨에서 파라미터 변경가능 (10gR2부터 제공) p.496 참조


2.1.6 Merging 되지 않은 뷰의 처리방식

  1. 1단계 : 뷰머징 시행 시 오히려 비용이 증가된다고 판단(10g이후) 되거나, 부정확한 결과 집합 가능성이 있을 시 뷰머징 포기
  2. 2단계 : 뷰머징이 포기 할 경우 조건절 Pushing 시도 (다음절에서 자세히 다룸)
  3. 3단계 : 뷰 쿼리 블록을 개별적으로 최적화된 개별 플랜을 전체 실행계획에 반영 (즉, 뷰 쿼리 수행 결과를 엑세스 쿼리에 전달)
SQL> select /*+ leading(e) use_nl(d) */ *
  2  from   dept d
  3       ,(select /*+ NO_MERGE */ * from emp) e
  4  where  e.deptno = d.deptno;

14 개의 행이 선택되었습니다.

Execution Plan
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |  1498 |    17   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |       |       |            |          |
|   2 |   NESTED LOOPS               |         |    14 |  1498 |    17   (0)| 00:00:01 |
|   3 |    VIEW                      |         |    14 |  1218 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL        | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("E"."DEPTNO"="D"."DEPTNO")
     ※ 실행계획의 "VIEW" 로 표시된 오퍼레이션 단계가 추가 되었을 시, 실제로 다음 단계로 넘어가기 전 중간집합을 생성하는 것은 아님

2.2 Library Cache 튜닝 방법

2.2.1 SQL의 작성과 파싱

  1. SQL문을 실행하면, 데이터베이스는 아스키 값으로 계산
  2. 대문자, 띄어쓰기, 주석에 따라 아스키 값이 다르므로 표현이 달라지면 다른 SQL문으로 인식
  3. 같은 결과를 얻지만 표현이 다른 SQL문은 실행할때마다 library Cache에서 하드파싱 됨.
  4. 하드파싱은 SQL문의 검색과 공간확보를 위해 Shared Pool Latch와 Library Cache Latch를 필요로 함.
  5. 잦은 하드파싱은 리소스를 과도하게 사용하고 래치를 오래 점유하므로 SQL문의 수행에 지연이 발생
  6. 따라서 SQL문을 재사용하는 소프트 파싱이 필요.
  7. 이를 위해 SQL문 작성시, 대문자, 띄어쓰기, 주석에 대한 원칙을 세워야 함.
  8. 바인드 변수를 사용하면 변수에 들어가는 값에 관계없이 파싱 함.
  9. 파싱이 끝나면 바인드값을 대입하게 되는데, 적용되는 값에 상관없이 SQL을 공유할 수 있게 되는 것.
  10. SQL을 수행하게되면 Library Cache에서 해당 래치를 획득하고 수행하려는 SQL 실행정보(LCO:Library Cache Object)가 있는지 검색.
  11. SQL이 있으면 LCO의 생성과정을 거치지 않고 바로 실행할 수 있음. 이것을 소프트파싱이라고 함.
  12. 그런데, SQL이 Library Cache에 존재하지 않는 새로운 SQL이라면 LCO를 만들어 실행정보를 저장 함.
  13. Shared Pool 래치를 획득하여 저장할 공간을 확보합니다.
  14. 공간이 마련되면 SQL의 LCO가 생성되고, 여기에 SQL문과 실행계획 등의 정보를 저장.
  15. 이렇게 만들어진 LCO를 통해 SQL이 수행. 이와 같이 SQL문이 Library Cache 내에 존재하지 않아 LCO를 만들고, 여기에 실행정보를 저장하는 과정을 하드 파싱이라고 함.
  16. 하드파싱과 소프트파싱은 Library Cache 내의 SQL 존재유무에 따라서 구별.
  17. 최초로 수행되는 SQL은 하드파싱을 피할 순 없음. 그러나 두번째 수행부터는 소프트파싱을 하는게 보다 빠르고 적은 리소스를 사용하니 효율적.


2.2.2 Child LCO 생성

  1. SQL이 parsing이 되면, LCO를 만듬.
  2. LCO도 같은 SQL이라고해서 하나로 같이 쓰는것이 아니라, 유저가 다르고, 환경이 달라도 따로 LCO를 만들어서 관리.
  3. 조건이 다르면 전부 다르게 LCO를 만들게 됨.
  4. child LCO가 다르게 만들어지는 이유는 V$SQL_SHARED_CURSOR란 뷰를 보면 확인이 가능.
  5. 오라클 프로시저나 테이블과 같은 객체에 대해서는 스키마명을 항상 같이 저장하기 때문에 유일성이 보장.
  6. 하지만, SQL 문장의 경우에는 SQL 텍스트 자체가 이름으로 사용되기 때문에 유일성이 보장되지 않음.
  7. 따라서 오라클 SQL 텍스트를 이름으로 갖는 부모 LCO를 생성하고 실제 SQL 커서에 대한 정보는 자식 LCO에 저장.
    1. 가령 두 개의 다른 스키마 A, B에서 텍스트는 동일하지만, 실제로 참조하는 객체는 다른 SQL문장을 수행한 경우, 오라클은 SQL 텍스트에 해당하는 부모 LCO와 스키마 A가 수행한 SQL 커서에 해당하는 자식 LCO, 스키마 B가 수행한 SQL 커서에 해당하는 자식 LCO, 총 세개의 LCO를 생성.

2.2.3 LATCH: library cache

  1. latch: library cache 대기 이벤트는 libarary cache 래치를 획득하는 과정에서 경합이 발생하여 나타나는 대기 이벤트.
  2. Shared Pool 래치가 프리 청크를 찾기 위해 프리리스트를 스캔하고, 적절한 청크를 할당하는 작업을 보호한다면, library cache 래치는 SQL을 수행하기 위해 libarary cache 메모리 영역을 탐색하고 관리하는 모든 작업을 보호.
  3. 이 때, libarary cache 래치는 CPU count 보다 큰 소수(Prime Number) 중 가장 작은 수만큼 자식 래치(child latch)를 가집니다.

2.2.4 Wait Time

  1. 이벤트의 대기시간을 기하급수적으로 증가한다.
  • Parameter

P1(프로세스가 대기하고 있는 래치의 메모리 주소), P2(래치 번호), P3(래치를 획득하기 위해 프로세스가 시도한 횟수)


  • 일반적인 문제상황 및 대처방안
- 원인: 파싱이 과다한 경우
- 진단방법
    latch: library cache 대기가 높은 시점의 파싱에 소요된 시간(parse time elapsed)
    발생한 파싱 횟수(parse count(total), parse count(hard), SQL 수행 횟수(execute count)를 확인)
- 개선방법
    바인드 변수 사용, Web Application Server의 경우, Statement Cache 기능 사용
    애플리케이션 수정, Static SQL을 사용
    session_cached_cursors 파라미터의 조정


- 원인: 버전 카운트(Version count)가 높은 경우
- 진단방법: V$SQLAREA 뷰에서 latch: libarary cache의 보유 시간이 긴 SQL의 VERSION_COUNT 칼럼 값을 확인


- 원인: SGA(System Global Area) 영역의 페이지 아웃(Page out)이 발생하는 경우
- 진단 방법: latch: library cache 대기가 높은 시점 O/S에서 스왑(Swap) 발생
- 개선 방법
    Memory 과다 사용 프로세스 검출
    HP-UX, AIX: LOCK_SGA 파라미터값을 TRUE 값으로 변경(DEFAULT = FALSE)
    SonOS: _USE_ISM 파라미터 값이 TRUE 인지 확인(DEFAULT = TRUE)



버전 카운트(Version Count)

Scott: select * from emp where empno = 1; Mary: select * from emp where empno = 1; John: select * from emp where empno = 1; 위의 세 SQL 문장은 Text가 완전히 동일하므로 동일한 해시 값을 갖습니다. 따라서 동일한 해시 체인(Hash Chain)의 동일한 핸들에 할당됩니다. 하지만 emp 테이블이 모두 스키마가 다른 테이블이므로 실제로는 다른 SQL문장입니다. 이 경우 오라클은 Text에 해당하는 부모 LCO를 두고 그 밑에 세 개의 자식 LCO를 만들어 개별 SQL 정보를 관리합니다. 세 개의 자식 LCO는 실제로는 익명 리스트(Anonymous List)라고 하는 별도의 리스트에 저장됩니다. 세 개의 자식 LCO를 가지므로 V$SQLAREA 뷰의 VERSION_COUNT(버전 카운트) 칼럼 값이 자식 LCO의 개수와 같은 3의 값을 가지게 됩니다. 버전 카운트가 높다는 것은 자식 LCO 탐색으로 인해 library cache를 탐색하는 시간이 그만큼 증가한다는 것이며, 이로 인해 library cache 래치 경합이 증가할 수 있다는 것을 의미합니다. 만일 특정 SQL 문장에서 library cache 래치 경합이 많이 발생한다면 해당 SQL의 버전 카운트 값을 확인해 볼 필요가 있습니다. 오라클의 버그로 인해 버전 카운트가 불필요하게 높아지는 경우가 있기 때문입니다.


SGA 영역의 페이지 아웃(Page Out)

Shared Pool이 디스크로 페이지 아웃된 경우, 해당 영역에 대한 스캔이 발생할 때 다시 디스크의 내용을 메모리로 불러들이는 과정(페이지 인)동안 대기해야 하므로 library cache 래치에 대한 대기시간이 증가할 수 있습니다. 만일 latch: library cache 대기가 높은 시점에, O/S에서 스왑현상이 발생한다면, 페이지 아웃에 의한 성능 저하일 확률이 높습니다.


SESSION_CACHED_CURSORS

SESSION_CACHED_CURSORS 파라미터 값이 세팅되어 있으면 오라클은 세 번 이상 수행된 SQL 커서에 대한 정보를 PGA(Program Global Area) 내에 보관합니다. 사용자가 SQL을 수행 요청할 때 오라클은 PGA에 캐싱된 정보가 있는지 확인하고, 만일 캐싱된 정보가 있다면 캐싱된 정보를 이용합니다. 따라서 library cache 영역을 탐색하는 시간이 줄어들어 상대적으로 library cache 래치를 보유하는 시간이 줄어들게 됩니다. SESSION_CACHED_CURSORS 파라미터의 기본값은 버전마다 다릅니다. 만일 기본 값이 작다면 되도록이면 50 이상의 값을 설정하는 것이 바람직합니다.



LATCH: cache buffers chains

latch: cache buffers chains는 cache buffers chains 래치를 획득하는 과정에서 경합이 발생하여 나타나는 이벤트입니다. 버퍼 캐시를 사용하기 위해 해시 체인을 탐색하거나 변경하려는 프로세스는 반드시 해당 체인을 관리하는 cache buffers chains 래치를 획득해야하는데, 이 과정에서 경합이 발생하면 latch: cache buffers chains 이벤트를 대기하게 됩니다.


  • Wait Time

이벤트의 대기시간은 기하급수적으로 증가한다.


  • Parameter

P1(프로세스가 대기하고 있는 래치의 메모리 주소), P2(래치번호), P3(래치를 획득하기 위해 프로세스가 시도한 횟수)


  • 일반적인 문제상황 및 대처방안
- 원인: 비효율적인 SQL문장 사용
- 진단 방법: cache buffers chains 래치 대기가 발생하는 시기에 V$SQLAREA 뷰를 통하여 SQL을 확인, TRACE를 통하여 과다한 처리범위를 발생시키지 않는지에 대한 여부를 확인
- 개선 방법: SQL문장 튜닝


- 원인: 핫 블록(HOT Block)에 의한 문제
- 진단 방법: V$LATCH_CHILDREN 뷰에서 cache buffers chains 래치에 해당하는 특정 자식 래치의 CHILD#과 GETS, SLEEPS 값이 높은지 확인 V$SESSION_WAIT 뷰에서 래치의 주소를 얻어 과다하게 중복된 주소가 있는지 확인
- 개선 방법: PCTFREE를 높게 주거나 작은 크기의 블록을 사용
                파티셔닝 적용, 해당 블록의 로우들에 대해서만 삭제 후, 재삽입 작업 수행



HOT BLOCK 여부 판단

select * from (select child#, gets, sleeps from v$latch_children

        where name = 'cache buffers chains'
        order by sleeps desc

) where rownum <= 20; V$LATCH_CHILDREN 뷰에서 자식 cache buffers chains 래치에 해당하는 CHILD#과 GETS, SLEEPS 값을 비교하여, 특정 자식 래치에 사용하는 횟수와 경합이 집중되는지 판단하여 Hot block 여부를 알 수 있습니다. 다음 명령문을 이용해서 SLEEPS 회수가 높은 자식 래치를 얻습니다. 만일 특정 자식 래치의 GETS, SLEEPS 값이 다른 자식 래치에 비해서 비정상적으로 높다면 해당 래치가 관장하는 체인에 핫 블록이 있는 것으로 추측할 수 있습니다.



V$BH 뷰로 어떤 블록들이 HOT BLOCK인지 판단

select hladdr, obj, (select object_name from dba_obejcts where (data_object_id is null and object_id = x.obj) or

data_object_id = x.obj and rownum = 1) as object_name,
      dbarfil, dbablk, tch from x$bh x

where hladdr in ('C0000000CDFF24F0', 'C0000000CE3ADDF0', 'C0000000CDF18A98') order by hladdr, obj; X$BH 뷰를 이용하면 정확하게 어떤 블록들이 핫 블록인지 확인할 수 있습니다. X$BH 뷰로부터 1) 사용자 객체(Table, Index)에 해당하며, 2) Touch Count가 높은 블록을 기준으로 핫 블록을 추출할 수 있습니다.



LATCH: cache buffers lru chain

latch: cache buffers lru chain은 cache buffers lru chain 래치를 획득하는 과정에서 경합이 발생하여 나타나는 이벤트입니다. Working Set(lru + lruw)을 탐색하거나 변경하려는 프로세스는 항상 해당 Working Set을 관리하는 cache buffers lru chain 래치를 획득해야 하는데, 이 때 경합이 발생하면 latch: cache buffers lru chain 이벤트를 대기하게 됩니다.


  • Wait Time

이벤트의 대기시간은 기하급수적으로 증가한다.


  • Parameter

P1(프로세스가 대기하고 있는 래치의 메모리 주소), P2(래치번호), P3(래치를 획득하기 위해 프로세스가 시도한 횟수)


  • 일반적인 문제상황 및 대처방안
- 원인: 비효율적인 SQL문장 사용
- 진단 방법: cache buffers lru chain 래치 대기가 발생하는 시기에 V$SQLAREA 뷰를 통하여 SQL을 확인, TRACE를 통하여 과다한 처리범위를 발생시키지 않는지에 대한 여부를 확인
- 개선 방법: SQL문장 튜닝


- 원인: 버퍼 캐시 크기가 너무 작은 경우
- 진단 방법: 버퍼 캐시의 히트율을 확인하기 위해서는 V$SYSSTAT, V$SESSTAT을 확인하고, 버퍼 캐시 영역을 분석하기 위해서 V$BUFFER_POOL, V$BUFFER_POOL_STATISTICS를 확인
- 개선 방법: 버퍼 캐시의 크기를 충분히 크게 한다.


- 원인: 체크 포인트 주기가 지나치게 짧은 경우
- 진단 방법: 버퍼 캐시의 히트율을 확인하고, FAST_START_MTTR_TARGET 또는 LOG_CHECKPOINT_TIMEOUT 파라미터를 통해 체크 포인트 주기를 확인
- 개선 방법: FAST_START_MTTR_TARGET 파라미터를 조정하여, 체크 포인트 주기를 합리ㅏ적으로 지정



cache buffers chains 래치와 cache buffers lru chain 래치 경합간의 차이

cache buffers chains 래치와 cache buffers lru chain 래치 경합간의 차이점에 대해서 이해라 필요가 있습니다. 만일 동일 테이블이나 인덱스를 여러 세션이 동시에 스캔하는 경우라면, cache buffers chains 래치 경합이 발생할 확률이 높습니다. 동일 체인에 대한 경합이 발생하기 때문입니다. 하지만, 다른 테이블이나 인덱스들을 여러 세션이 동시에 스캔하는 경우라면 cache buffers lru chain 래치 경합이 발생할 확률이 높습니다. 여러 세션들이 모두 다른 블록들을 메모리에 올리는 과정에서 프리 버퍼를 확보하기 위한 요청이 많아지고 이로 인해 Working Set에 대한 경합이 발생할 확률이 높아집니다. 특히 데이터의 변경이 빈번해서 더티 버퍼의 개수가 많고 이로 인해 DBWR가 체크 포인트를 위해 lruw 리스트를 탐색하는 횟수가 잦다면 cache buffers lru chain 래치의 경합은 더욱 심해집니다. cache buffers lru chain 래치 경합의 또다른 중요한 특징은 물리적 I/O를 수반한다는 것입니다. 비효율적인 인덱스 스캔에 의한 문제라면 db file sequential read 대기와 lru chain 래치 경합이 함께 발생하게 되고, 불필요한 풀테이블스캔이 많다면 db file scattered read 대기와 lru chain 래치 경합이 함께 발생하게 됩니다.

실제로는 cache buffers chains 래치 경합과 cache buffers lru chain 래치 경합이 같이 발생하는 경우가 많은데, 복잡한 애플리케이션들에서는 위에서 언급한 패턴들이 복합적으로 사용되기 때문입니다.



버퍼 캐시 크기 증가 여부 판단

select to_char((sum(decode(name, 'consistent gets', value, 0)) +

       sum(decode(name, 'db block gets', value, 0)) - 
       sum(decode(name, 'physical reads', value, 0)) - 
       sum(decode(name, 'physical reads direct', value, 0))) / 
       (sum(decode(name, 'consistent gets', value, 0)) + 
        sum(decode(name, 'db block gets', value, 0))) *
       100, '999.99') || ' %' "Buffer Cache Hit Ratio"

from v$sysstat; 다수의 비효율적인 SQL로 인한 프리버퍼를 과도하게 요청하는 경우 버퍼 캐시 히트율을 떨어뜨리는 주요 원인이 됩니다. 그리고 버퍼 캐시의 크기가 지나치게 작을 경우 또한 히트율을 떨어뜨리는 주요 원인입니다. 버퍼 캐시 크기 증가를 고려하기 위해 버퍼 캐시 히트율을 확인해 볼 필요가 있습니다. 또한 free buffer waits, buffer deadlock, buffer busy waits 이벤트 다수 발생 시에도 버퍼 캐시 크기 증가를 고려해 볼 필요가 있습니다.



library cache pin

library cache pin 이벤트 대기는 Library Cache Object의 실행정보를 바꾸거나 참조하는 과정에서 경합이 발생할 때 관찰됩니다. 가령 특정 SQL에 대해 최초로 하드파싱을 수행하는 세션은 해당 Library Cache Object에 대해 library cache pin을 Exclusive 모드로 획득합니다. 하드파싱이 이루어지는 동안 같은 SQL을 수행하고자 하는 세션들을 library cache pin을 Shared 모드로 획득하기 위해 대기해야 합니다. 이때 library cache lock 이벤트를 대기합니다.


  • Wait Time

PMON 프로세스는 1초까지 대기하며, 다른 프로세스들은 3초까지 대기합니다. 해당 대기시간 후에도 핀을 획득하지 못할 경우 반복적으로 대기합니다.


  • Parameter
 참고) library cache pin 대기 이벤트는 대기 파라미터를 사용하지 않습니다.
 P1(핀(pin) 대기와 관련된 오브젝트의 메모리 주소), P2(핀(pin)의 메모리 주소), P3(모드(mode)와 네임스페이스(namespace))


  • Common Causes and Actions
 - 원인: 현재 많이 사용되는 오브젝트에 대한 DDL 명령을 수행
 - 진단 방법: library cache lock/pin 경합 중 x$kglk, x$kglpn, x$kglob, v$session을 통하여 Holder Session 확인, library cache lock/pin 경합 종료 후 사후 분석 시 DBA_HIST_ACTIVE_SESS_HISTORY 뷰를 통하여 Blocking Session을 확인
 - 개선 방법: 업무시간 중 과도한 오브젝트의 변경을 제한



library cache lock과 library cache pin의 상관관계

select a.sid, kglpnmod "Mode", kglpnreq "Req" from x$kglpn p, v$session s where p.kglpnuse = s.saddr and kglpnhdl='$P1RAW';


library cache pin 이벤트의 P1=handle address, P2=lock address, P3=mode*100+namespace로 어떤 객체에 대해 어떤 모드로 락을 획득하는 과정에서 경합이 발생했는지 파악할 수 있습니다.

library cache pin은 library cache lock을 획득한 후, library cache 객체에 대해 추가작업이 필요할 "때 획득하게 됩니다. 가령 특정 프로시저나 SQL 문장을 수행하고자 하는 프로세서는 library cache lock을 Shared 모드로 획득한 후에 library cache pin을 Shared 모드로 획득해야 하며, 프로시저를 컴파일(alter procedure ... compile ...)하는 경우에는 library cache pin을 Exclusive하게 획득해야 합니다. 핀(pin)이라는 용어의 의미는 LCO에 핀을 꽂는다는 것으로, 핀이 꽂혀있는 동안 LCO의 값이 변동되지 않도록 보장받는 역할을 합니다. 한 가지 기억할 사실은 하드파싱이 발생하는 경우, 하드파싱이 이루어지는 동안 해당 SQL 커서에 대해 library cache pin을 Exclusive하게 획득한다는 것입니다. 해당 이벤트가 발생할 경우, 위의 SQL을 수행하여 핀을 점유하고 있는 세션 및 모드를 확인할 수 있습니다.



업무시간 중 DDL의 수행을 피하라

library cache lock 대기에 의한 성능저하 현상은 대부분 부적절한 DDL(create, alter, flush 등)에 의해 발생합니다. 따라서 트랜잭션이 왕성한 시스템에 대해서 DDL을 수행할 때는 이 내용을 충분히 수행해야 합니다. 간혹 하드 파싱이 많은 시스템에서 Shared Pool 메모리 고갈을 피하기 위해(ORA-4031 에러를 피하기 위해) flush를 수행하는 경우가 있으나 시스템에 악영향을 주는 경우가 많습니다. 하드 파싱도 나쁘지만, 하드 파싱이 발생하는 도중에 DDL을 수행하는 것은 피해야 합니다.



LATCH: shared pool(bind mismatch)

Shared Pool 래치는 Shared Pool의 기본 메모리 구조인 힙을 보호하는 역할을 합니다. 프리 청크를 찾기 위해 프리 리스트를 탐색하고, 적절한 청크를 할당하고, 필요한 경우 프리 청크를 분할(Split)하는 일련의 작업들은 모두 Shared Pool 래치를 획득한 후에만 가능합니다. Shared Pool 래치를 획득하는 과정에서 경합이 발생하면 latch: shared pool 이벤트를 대기합니다.


  • Wait Time
이벤트의 대기시간은 기하급수적으로 증가한다.


  • Parameter
P1(프로세스가 대기하고 있는 래치의 메모리 주소), P2(래치번호), P3(래치를 획득하기 위해 프로세스가 시도한 횟수)


  • 일반적인 문제상황 및 대처방안
- 원인: 동시에 여러 세션이 청크를 할당 받아야 하는 경우
          Shared Pool 단편화가 일어날 경우
          Literal SQL로 인한 Hard Parsing의 과다 수행
- 진단 방법: Hard Parsing 추이를 확인하기 위하여 V$SYSSTAT 뷰를 통하여 parse count(hard),
          parse time cpu, parse time elapsed 지표 값 확인
          Hard Parsing이 높게 나타난 세션에 수행된 SQL의 Literal SQL 여부 확인
- 개선 방법: 바인드 변수 사용
          _KGHDSIDX_COUNT 히든 파라미터를 이용하여 서브풀 생성
          Shared Pool의 크기 감소 후 dbms_shared_pool 패키지 사용
          Cursor Sharing 기법 사용
          Prepared Statement의 사용을 통해 JDBC PKG 내의 Literal SQL을 제거



서브풀의 사용

오라클 9i 이상부터는 shared Pool을 여러 개의 서브풀로 최대 7개가지 나누어서 관리할 수 있습니다. _KGHDSIDX_COUNT 히든 파라미터를 이용하면 서브풀의 개수를 관리할 수 있습니다. 오라클은 CPU 개수가 4 이상이고, Shared Pool의 크기가 250M 이상인 경우 _KGHDSIDX_COUNT의 값만큼 서브풀을 생성해서 Shared Pool을 관리합니다. 서브풀은 그 자체가 독립적인 Shared Pool로 관리되며 독자적인 프리리스트(Freelist), lru 리스트, Shared Pool 래치를 가집니다. 따라서 shared Pool의 크기가 큰 경우에는 서브풀로 쪼개서 관리함으로써 Shared Pool 래치 경합을 줄일 수 있습니다.


Shared Pool 크기 감소

하드파싱에 의해 Shared Pool 래치 경합이 발생하는 경우 또 다른 해결책은 Shared Pool의 크기를 줄이는 것입니다. Shared Pool의 크기가 줄어든 만큼 프리리스트에 딸린 프리 청크들의 개수도 감소하고 따라서 프리리스트 탐색에 소요되는 시간이 줄어들기 때문입니다. 하지만 이 경우 ORA-4031 에러가 발생할 확률이 높아지며 Shared Pool에 상주할 수 있는 객체의 수가 줄어들어서 부가적인 하드파싱이 유발될 수 있다는 단점이 있습니다. 이 단점을 해소하기 위해서 dbms_shared_pool.keep 프로시저를 이용해서 자주 사용되는 SQL 커서나 패키지, 프로시저 등을 shared Pool에 영구 상주시키는 방법을 사용할 수 있습니다. dbms_shared_pool.keep을 이용해 지정된 객체들은 Shared Pool에 영구적으로 상주하게 되며, alter system flush shared_pool 명령으로도 내려가지 않습니다. 요약하면, Shared Pool의 크기를 줄이고 동시에 dbms_shared_pool 패키지를 이용해 자주 사용되는 객체를 메모리에 상주시키는 것이 또하나의 방법이 됩니다.


Cursor Sharing 사용

Cursor Sharing 기법을 사용합니다. Cursor Sharing이란 상수(Literal)을 사용한 SQL 문장을 자동으로 바인드 변수를 사용하게끔 치환해서 커서가 공유되도록 해주는 기능을 말합니다. Curosr Sharing 기능은 기존의 Literal SQL을 바인드변수로 변환할 간적 여유가 없는 경우에만 사용하는 것이 바람직합니다.



kksfbc child completion

SQL Cursor 객체는 Parent/Child의 관계로 이루어져 있습니다. Parent Cursor 객체가 여러개의 Child Cursor 객체를 거느립니다. Server Process가 현재 생성(Built) 중인 Child Cursor를 사용하려면 Cursor 생성이 완료될 때까지 기다려야 합니다. 이때 보고되는 이벤트가 kksfbc child completion 이벤트입니다.


  • 과도한 Hard Parse는 kksfbc child completion 대기 이벤트의 가장 중요한 원인
  1. Hard Parse가 많은 경우(Parse count 통계값 및 V$SQLAREA.VERSION_COUNT 값 참조)
  2. Parse failure가 많은 경우
  3. 병렬 실행이 발생하는 경우에도 Child Cursor를 공유하는 과정에서 대기가 발생하는 경우


  • kksfbc child completion 대기 이벤트 해소 방법(Hard Parse의 최소화)
  1. Dynamic SQL을 Static SQL로 변환한다.
  2. Literal SQL 사용을 최소화하고 Bind Variable을 사용한다.
  3. Bind Mismatch에 의한 Hard Parse가 발생하는지 확인한다.



CURSOR: pin s wait on x

오라클 10g 이후부터, Shared Cursor Operation에 대해 기본적으로 Mutex가 동기화 객체로 사용되면서 나타나는 Wait Event입니다. 일종의 library cache pin 이벤트 발생과 성격이 비슷합니다.


  • Shared Cursor Operation이란?
 - library cache latches, library cache pin latches, library cache pinsd를 의미합니다.


  • 뮤텍스(Mutual exclusion)란?
 - 다수의 프로세스가 동일한 리소스를 공유할 때 동시 사용을 피하기 위해 사용되는 알고리즘입니다.
 - 프로그램이 요청한 리소스를 위한 mutext를 하나 생성합니다.
 - 시스템이 고유 ID를 부여합니다.(no wait mode의 latch와 비슷함)
 - latch와 달리 mutext는 시스템이 관장합니다.
 - latch의 spin을 수행하지 않기 때문에 가벼울 수 있습니다.
 - 문제발생 oracle의 해결범위를 넘어섭니다.
 - mutext의 경우 복구가 안됩니다.


뮤텍스의 기능을 이용함으로, Mutex pin을 사용하는 과정에서 이와 같은 현상이 발생하고, cursor pin s wait on x 대기가 발생합니다. 이는 library cache pin 개념과 동일합니다.



Mutex Holder 찾기

Oracle 10g부터 cursor: pin ...과 같은 이름의 대기 이벤트가 많이 관찰됩니다. Mutex가 동기화 객체로 사용되면서 나타난 현상인데, 이 Mutex의 문제는 Holder를 관찰하기가 쉽지 않습니다.


하지만 아래 Metalink Note를 읽어보면 의외로 쉽게 Mutext Holder를 찾을 수 있다는 것을 알 수 있습니다.


-- 64비트 시스템에서는 8자리, 32비트 시스템에서는 4자리를 취한다.


SELECT p2raw, to_number(substr(to_char(rawtohex(p2raw)), 1, 8), 'XXXXXXXX') sid

FROM v$session

WHERE event = 'cursor: pin S wait on X';


P2RAW SID


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

0000001F00000000 31


31은 첫8자리 0000001F 값의 10진수 값입니다. 즉, 현재 Holder가 31번 세션이라는 것을 의미합니다. 10GR2 부터는 V$SESSION.BLOCKING_SESSION 컬럼에 Holder 정보가 기록되어 더욱 손쉽게 Holder Session을 찾을 수 있습니다.



latch free(simulator lru latch)

DB Cache Advisor 기능이 사용하는 메모리 영역을 보호하는 latch입니다.


* 일반적인 문제상황 및 대처방안 
- 원인: 큰 크기(수 GB이상)의 Buffer Cache를 사용 시 simulator lru latch 경함
- 진단 방법: DB Cache Advisor 기능이 활성화되어 있는지 확인
- 개선 방법: DB_CACHE_ADVICE 파라미터를 통하여 DB Caiche Advisor 기능 비활성화


* Latch란?

Latch는 특성상 획득할 때까지 게속해서 CPU를 점유하면서 스핀하여 Latch 획득 시도를 하기 때문에 다수의 세션이 Latch 대기를 하게되면 그만큼 CPU 사용률이 증가하게 도비니다. 그래서 Latch를 해결해야하는데 Simulator lru latch는 쿼리가 수행됐을 때, 해당하는 쿼리에 대해서 일종의 Simulation을 수행해보는 것이 아니라 latch free(simulator lru latch)를 해소하기 위해서는 DB_CACHE_ADVISOR 기능을 OFF 시키면 해당 이벤트 대기현상이 해소됩니다. 만약 운영시스템이 CPU에 민감하다면 이 기능을 OFF하는 것이 좋습니다.


DB_CACHE_ADVICE

oracle 9i 부터는 SGA 영역 크기를 온라인 상태에서 바꿀 수 있습니다. 이를 Dynamic SGA 기능이라고 합니다. 이렇게 바꿀 수 있는 메모리 영역은 Shared Pool, Buffer Cache, Large Pool 이렇게 세 가지입니다. 이 중 Buffer Cache 크기를 조절했을 때의 성능을 예측하는 Advisory 기능을 DB_CACHE_ADVICE 파라미터를 통하여 제공합니다. DB_CACHE_ADVICE = ON인 경우 Buffer Cache Advisory 기능이 enable 되며 V$DB_Cache_Advice 뷰를 통하여 내용을 확인할 수 있습니다. V$DB_Cache_Advice View에는 buffer cache 별로 현재 크기의 10%에서 200%까지 20개의 크기에 대한 simulation 정보를 기록합니다. 각 크기별로 기존 block 참조 정보를 이용해서 예상되는 물리ㅏ적 일기 수를 제공합니다.


* Buffer Cache Advisory 기능 사용은 다음 두가지의 오버헤드를 일으킵니다.
  1) Advisory 기능은 buffer cache 별로 bookkeeping을 위한 아주 약간의 CPU 오버헤드가 필요하다.
  2) MEMORY: Advisory 기능은 buffer block 당 Shared Pool에서 약 700 byte 정도의 메모리를 할당한다.


* parameter는 ON, OFF, READY 세 가지 값을 가질 수 있는데, 각 상태의 의미는 다음과 같습니다.
   1) OFF: Advisory 기능이 disable 되고, CPU나 MEMORY 오버헤드가 없음
   2) ON: Advisory 기능이 enable 되고, CPU나 MEMORY 오버헤드가 발생
   3) READY: Advisory 기능은 disable되나, Shared Pool의 메모리는 할당


READY나 ON의 경우, Shared Pool의 Contention이 발생하므로 오버헤드가 될 수 있습니다. 충분한 여유공간을 확인한 후 작업해야 합니다.



V$DB_CACHE_ADVICE 뷰

 id: Buffer Cache의 id(1~8)
 name: Buffer Cache의 이름
 block size: Buffer Cache의 block 크기
 advice_status: Buffer Cache Advisory 기능의 상태(ON or OFF: Ready 상태도 OFF로 표시)
 size_for_estimate: simulation에 사용한 Buffer Cache의 크기(KB)
 buffers_for_estimate: simulation에 사용한 Buffer Cache의 개수(blocks)
 estd_physical_read_factor: 물리적 읽기 예상#/Buffer Cache 일기#
 estd_physical_reads: 물리적 읽기 예상치


estd_physical_read_factor는, 실제 Buffer Cache Advisory 기능을 enable 시킨 이후, Buffer Cache에 실제 발생한 physical read number 대비, Buffer Cache의 크기를 V$DB_CACHE_ADVICE 뷰의 row에 나와 있는 크기로 조정했을 때 예상되는 physical read number(estd_physical_reads)의 비율을 의미합니다.


출처: https://12bme.tistory.com/313 [길은 가면, 뒤에 있다.]

2.3 분석함수의 튜닝 고려사항

2.3.1 min/max 함수를 Ranking 함수 변경

  1. 최종일자에 해당하는 데이터를 구할 때, MAX(최종일자) 분석함수를 사용하지 말고 Ranking 분석 함수 사용.
  2. WINDOW BUFFER를 WINDOW NOSORT로 바꾸어 SORTING 부하를 줄여 줌.

2.3.2 ORDER BY 절에 NULL FIRST나 LAST를 삭제 검토

  1. 분석함수의 ORDER BY 절에 NULL FIRST나 LAST를 삭제할 수 있는지 검토.
  2. 인덱스 사용에 의한 Sort 유지 되도록 하여 추가적인 sort를 방지.
  • 주의 1) Index ASC로 사용 + 분석함수의 ORDER BY절에 NULL FIRST 는 추가적인 sort발생
  • 주의 2) Index DESC로 사용 + 분석함수의 ORDER BY절에 NULL LAST 는 추가적인 sort발생

2.3.3 여러 개의 분석함수를 하나로 통합

  1. 분석함수를 여러 개 사용할 때, 가능하면 OVER절의 Partition By와 Order By절을 일치 하도록 검토.
  2. 분석함수를 하나만 실행하는 효과를 얻어서 실행시간을 단축하도록 검토 .
 이때 ORDER BY는 완전히 같지 않아도 Operation은 통합 가능 
  • 주의 1) WINDOW NOSORT + WINDOW SORT => WINDOW SORT
  • 주의 2) WINDOW NOSORT + WINDOW BUFFER => WINDOW BUFFER
  • 주의 3) WINDOW BUFFER + WINDOW SORT => WINDOW SORT

2.3.4 Ranking 분석함수 이용 TOP SQL의 Sort 최소화

  1. Ranking 분석함수를 인라인뷰 외부에서 Rownum 처럼 Filter로 사용 시 불필요한 Partition By 절 삭제 검토 .
    1. FULL SCAN을 하거나 혹은 OVER절의 NULL FIRST나 LAST등의 원인으로 sort가 발생될 때 .
      1. Rownum과는 달리 STOPKEY가 발생되지 않으므로 비효율은 존재.
      2. 이 때 발생되는 오퍼레이션 은 WINDOW SORT PUSHED RANK
    2. 적절한 인덱스가 있어서 sort가 발생되지 않는 경우, Rownum 처럼 STOPKEY를 발생시켜 부분범위 처리의 효과.
      1. 분석함수를 Rownum 처럼 사용. WINDOW NOSORT STOPKEY 오퍼레이션 발생.
  • 주의 1) Partition By절 전체를 제거해야 한다는 것.
  : 만약 Partition By절에 컬럼이 하나라도 있으면 Sort가 대량으로 발생.

2.3.5 그룹 분석함수 NO-SORT

  1. Sort를 방지하고, STOPKEY 작동, sum/min/max/avg등의 Group 분석함수를 사용할 경우 명시적으로 WINDOW의 범위를 지정.
  2. WINDOW BUFFER Operation을 WINDOW NOSORT로 바꾸어 불필요한 sort를 방지.(현재 row 까지의 누적집계 시 )
  • 예시)
SUM(AMOUNT_SOLD) OVER(ORDER BY CUST_ID,CHANNEL_ID,TIME_ID 
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                         )
  • 주의 1) Ranking 함수를 사용하여 WINDOW STOPKEY가 발생하는 경우는 SORT가 발생하지 않는 경우(NOSORT)뿐.
      그룹분석함수에 의해서 추가적인 SORT를 해야 한다면, 전체범위로 처리됨으로 STOPKEY가 발생하지 않음.

2.4 병렬 쿼리 튜닝

2.4.1 병렬처리 핵심은?

2.4.1.1 그래뉼

android The basic unit of work in parallelism is a called a granule.

Oracle Database divides the operation executed in parallel (for example, a table scan, table update, or index creation) into granules.


  1. 병렬로 처리할때 일의 최소 단위
  2. 병렬 쿼리 granule
2.4.1.1.1 블록 그래뉼
  1. PX BLOCK ITERATOR 라고 표시
  2. QC는 테이블로부터 읽어야할 범위의 블록 GRANULE로서 각 병렬 서버에게 할당
-------------------------------------------------------------------------------------------------
|Id|      Operation          |  Name  |Rows|Bytes|Cost%CPU|  Time  |Pst|Pst|  TQ |INOUT|PQDistri|
-------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT         |        |  17| 153 |565(100)|00:00:07|   |   |     |     |        |
| 1| PX COORDINATOR          |        |    |     |        |        |   |   |     |     |        |
| 2|  PX SEND QC(RANDOM)     |:TQ10001|  17| 153 |565(100)|00:00:07|   |   |Q1,01|P->S |QC(RAND)|
| 3|   HASH GROUP BY         |        |  17| 153 |565(100)|00:00:07|   |   |Q1,01|PCWP |        |
| 4|    PX RECEIVE           |        |  17| 153 |565(100)|00:00:07|   |   |Q1,01|PCWP |        |
| 5|     PX SEND HASH        |:TQ10000|  17| 153 |565(100)|00:00:07|   |   |Q1,00|P->P | HASH   |
| 6|      HASH GROUP BY      |        |  17| 153 |565(100)|00:00:07|   |   |Q1,00|PCWP |        |
==========> 블록 이터레이터로 표시됨 
| 7|       PX BLOCK ITERATOR |        | 10M| 85M | 60(97) |00:00:01| 1 | 16|Q1,00|PCWC |        |
|*8|        TABLE ACCESS FULL|  SALES | 10M| 85M | 60(97) |00:00:01| 1 | 16|Q1,00|PCWP |        |
-------------------------------------------------------------------------------------------------
2.4.1.1.2 파티션 그래뉼
  1. PX PARTITION RANGE ALL 또는 PX PARTITION RANGE ITERATOR 라고 표시
  2. 사용되는 시기
    1. Partition-Wise조인 시
    2. 파티션 인덱스를 병렬로 스캔할 시
    3. 파티션 인덱스를 병렬로 갱신할 때
    4. 파티션 테이블 또는 파티션 인덱스를 병렬로 생성할 때
---------------------------------------------------------------------------------------------------
|Id|      Operation                |  Name  |Rows|Byte|Cost%CPU|  Time  |Ps|Ps|  TQ |INOU|PQDistri|
---------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT               |        |  17| 153|   2(50)|00:00:01|  |  |     |    |        |
| 1| PX COORDINATOR                |        |    |    |        |        |  |  |     |    |        |
| 2|  PX SEND QC(RANDOM)           |:TQ10001|  17| 153|   2(50)|00:00:01|  |  |Q1,01|P->S|QC(RAND)|
| 3|   HASH GROUP BY               |        |  17| 153|   2(50)|00:00:01|  |  |Q1,01|PCWP|        |
| 4|    PX RECEIVE                 |        |  26| 234|    1(0)|00:00:01|  |  |Q1,01|PCWP|        |
| 5|     PX SEND HASH              |:TQ10000|  26| 234|    1(0)|00:00:01|  |  |Q1,00|P->P| HASH   |
==========> 파티션 RANGE ... 로 표시됨 
| 6|      PX PARTITION RANGE ALL   |        |  26| 234|    1(0)|00:00:01|  |  |Q1,00|PCWP|        |
| 7|       TABLEACCESSLOCAL INDEX ROWID|SALES| 26| 234|    1(0)|00:00:01| 1|16|Q1,00|PCWC|        |
|*8|        INDEX RANGE SCAN       |SALES_CUST|26|    |    1(0)|00:00:01| 1|16|Q1,00|PCWP|        |
---------------------------------------------------------------------------------------------------

2.4.1.2 생산자 VS 소비자

?scode=mtistory2&fname=https%3A%2F%2Fblog.kakaocdn.net%2Fdn%2Fy9Qa0%2FbtqytV7dIAq%2FK95i8dY3X43nzrZhvhkPrk%2Fimg.png

2.4.1.3 DOP 많을수록 좋을까?

  1. 테스트 예제

2.4.1.4  병렬퀴리튜닝의 관점은? 병렬의 갯수인가? 분산의 정도인가?

각 병렬 프로세스들이 독립적으로 일 할 수 있도록 데이터 적절하게 분배


  1. 튜닝 사례

2.4.1.5 병렬 PLAN 해석하는 방법

-- 병렬 쿼리 확인 
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST ADAPTIVE PARTITION PARALLEL OUTLINE'));
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |    14 |  1638 |     7  (29)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR            |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (ORDER)       | :TQ10002 |    14 |  1638 |     7  (29)| 00:00:01 |  Q1,02 | P->S | QC (ORDER) |
|   3 |    SORT ORDER BY           |          |    14 |  1638 |     7  (29)| 00:00:01 |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE             |          |    14 |  1638 |     6  (17)| 00:00:01 |  Q1,02 | PCWP |            |
|   5 |      PX SEND RANGE         | :TQ10001 |    14 |  1638 |     6  (17)| 00:00:01 |  Q1,01 | P->P | RANGE      |
|*  6 |       HASH JOIN            |          |    14 |  1638 |     6  (17)| 00:00:01 |  Q1,01 | PCWP |            |
|   7 |        BUFFER SORT         |          |       |       |            |          |  Q1,01 | PCWC |            |
|   8 |         PX RECEIVE         |          |     4 |   120 |     3   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   9 |          PX SEND BROADCAST | :TQ10000 |     4 |   120 |     3   (0)| 00:00:01 |        | S->P | BROADCAST  |
|  10 |           TABLE ACCESS FULL| DEPT     |     4 |   120 |     3   (0)| 00:00:01 |        |      |            |
|  11 |        PX BLOCK ITERATOR   |          |    14 |  1218 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  12 |         TABLE ACCESS FULL  | EMP      |    14 |  1218 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------


  1. S->P 직렬 PARALLEL_FROM_SERIAL QC가 읽은 데이터를 테이블 큐를 통해 병렬서버 프로세스에게 전송
  2. P->S 병렬 PARALLEL_TO_SERIAL 각 병렬서버 프로세스가 처리한 데이터를 QC에게 전송
  3. P->P 병렬 PARALLEL_TO_PARALLEL 데이터를 재분배(redistribution)하는 오퍼레이션 ,데이터를 정렬 또는 그룹핑하거나 조인을 위해 동적으로 파티셔닝할 때 사용
  4. PCWP 병렬 PARALLEL_COMBINED_WITH_PARENT 한 서버집합이 현재스텝과 부모스텝을 모두 처리
  5. PCWC 병렬 PARALLEL_COMBINED_WITH_CHILD 한 서버집합이 현재스텝과 자식스텝을 모두 처리
  6. [공백] 직렬 SERIAL 공백인 경우 SERIAL 방식으로 처리



  1. RANGE
    1. Order By / Group By 를 병렬로 처리할 때 사용
    2. 정렬작업을 맡은 두번째 서버집합의 프로세스마다 처리범위를 지정하고 나서
    3. 데이터를 읽는 첫번째 서버집합이 정렬키값에 따라 정해진 범위에 해당하는 두번째 프로세스에게 분배하는 방식
    4. QC는 작업범위를 할당하며, 정렬작업에는 참여하지 않는다.
    5. 정렬결과를 순서대로 받아서 사용자에게 전송하는 역할만 담당
  2. HASH
    1. 조인 / Hash Group By 를 병렬로 처리할 때 사용
    2. 조인 키나 Group By 키값을 해시함수에 적용하여 리턴되는 값에 따라 데이터를 분배
    3. P->P 뿐만 아니라 S->P 방식으로 이루어 질수도 있다.
  3. BROADCAST
    1. QC 또는 첫번째 서버집합의 프로세스들이 각각 읽은 데이터를 두번째 서버집합의 "모든" 병렬프로세스에게 전송하는 방식
    2. 병렬 조인에서 크기가 매우 작은 테이블이 있을 때 사용되며 P->P 뿐만 아니라 S->P 방식으로 이루어 진다.
    3. 작은 테이블은 병렬로 읽지 않을 때가 많으므로 오히려 S->P가 일반적임
  4. KEY
    1. 특정 컬럼(들)을 기준으로 테이블 또는 인덱스를 파티셔닝할때 사용하는 분배 방식
    2. 실행계획에는 'PARTITION (KEY)'로 표시된다.(줄여서 'PART (KEY)').
  5. ROUND-ROBIN
    1. 파티션키, 정렬키, 해시함수에 의존하지 않고 반대편 정렬 서버에 무작위로 데이터 분배
    2. 골고루 분배되도록 ROUND-ROBIN 방식 사용




2.4.1.5.1 SQL 플랜상 튜닝 검토 사항

android

  1. broadcast 는 소량테이블에 적합
  2. hash 는 대량 테이블에 적합
  3. S->P 는 튜닝 대상임. P->P로 바꿀수 있는 방법을 검토.sql수정도 좋다.
  4. round-robin 은 튜닝 대상임
  5. merge 나 insert ,delete문에만 주로 Parallel 힌트 사용. 하위 select 문에서는 가급적 지양 , 1:1 관계를 지향한다
  6. part key 플랜도 튜닝 대상. pq_disiribute(A hash hash) 힌트로 검토


  • 오라클은 내부적 으로 어떤 힌트를 사용하고 있는지 볼까 ?
    • OUTLINE

2.4.2 병렬처리 올바른 사용법은?

  1. 병렬도를 같게 지정하는 것이 바람직 함.
  2. 테이블별 개별 힌트, PARALLEL(A 8) 보다 글로벌 힌트, PARALLEL(8)로 적용

2.4.2.1 병렬 힌트를 어디에 어떻게 써야 하나?


2.4.2.2 DDL, DML 병렬처리

  1. DML
    1. SELECT
SELECT 
-- 튜닝 전
/* FULL(BA) FULL (BB FULL (BC FULL (BD)
PARALLEL(BA 16) PARALLEL (BB 16) PARALLEL (BC 16) PARALLEL (BD 16) */
-- 튜닝 후 
/*+ PARALLEL(16) 
LEADING(BB) USE HASH(BA BB BC BD )
FULL(BA) PO DISTRIBUTE (BA HASH HASH) NO SWAP JOIN INPUTS(BA)
FULLIBB) PO DISTRIBUTE(BB HASH HASH) SWAP_ JOIN_INPUTS(BB)
FULL(BC) PQ_DISTRIBUTE (BC HASH HASH) NO_SWAP_JOIN_INPUTS(BC)
FULL (BD) PQ DISTRIBUTE (BD HASH HASH) NO SWAP JOIN INPUTS (BD)
*/
....
 FROM TB_FMMBR_MANT -- 수급자정보 20G
    , TB_WLFM BB    -- 5G
    , TB_WLFMBR BC  -- 8G
    , TB_IDMB BD    -- 13G
WHERE 1 = 1
AND BA.QLFC_ID IN (
SELECT DISTINCT OLFC_ID
......
    1. INSERT
      1. 병렬 힌트 적용 되는 경우
INSERT /*+ PARALLEL (4) ENABLE_PARALLEL_DML */ -- PQ_DISTRIBUTE(T NONE)
  INTO UWM_WM_DTLS T
      1. 병렬 힌트 적용 안되는 경우
-- insert 앞에 힌트 
/*+ PARALLEL (4) ENABLE_PARALLEL_DML*/ -- PQ_DISTRIBUTE(T NONE)
INSERT INTO
UWM_WM_DTLS T
.... 

-- into 뒤에 힌트
INSERT INTO /*+ PARALLEL (4) ENABLE_PARALLEL_DML */ -- PQ_DISTRIBUTE(T NONE)
UWM_WM_DTLS T
....
    1. UPDATE
    2. DELETE
  1. DDL
     : CREATE TABLE [테이블명] (.......) PARALLEL ( DEGREE [병렬범위] ) ;
     : CREATE TABLE [인덱스명] PARALLEL ( DEGREE [병렬범위] ) ;
     : ALTER TABLE [테이블명] PARALLEL ( 병렬범위 ) ;
     : ALTER INDEX[테이블명] PARALLEL ( 병렬범위 ) ;
    1. CREATE 예시
-- 테이블 생성 
CREATE TABLE name ( column1 [data-type], ... )
Parallel 8;

-- 인덱스 생성 DOP 32 
CREATE INDEX CYKIM.IX_TBL_X01
          ON (MY_TABLE)
TABLESPACE TS_CY PARALLEL 32 NOLOGGING;
ALTER INDEX CYKIM.IX_TBL_X01 NOPARALLEL LOGGING;
    1. ALTER 예시
ALTER TABLE name Parallel 8 ;

2.4.2.3 DML문 에서도 SELECT 절만 PARALLEL 힌트를 사용하면 될까?

  1. DML에서 CONVENTIONAL / DIRECT PATH 차이점?
    1. Conventional Path
       :버퍼캐시에 적재, Default 방식
      1. 처리 속도 느림
      2. Row-level Lock
    2. Direct Path
       :병렬방식으로 Full Scan 할 때는 버퍼 캐시를 거치지 않고 곧바로 PGA 영역으로 읽어들이는 Direct Path Read 방식을 사용
      1. Table-level lock
      2. /*+ append */

12c 에서 처리 방법

  1. DML 병렬 처리
    1. DML 작업에서는 Paralle 힌트를 주어도 QC 가 작업 담당
    2. 병렬 DML 가능하도록 처리
      1. 12c 이전 에는 세션에 적용
SQL> alter session enable parallel dml;
      1. 12c 부터는 힌트로 적용
/*+ ENABLE_PARALLEL_DML */

  1. DML 병렬 처리시 주의사항

android

  1. DML 병렬 처리시 주의사항
    1. 테이블 전체에 Exclusive 모드로 Lock 획득하므로 주의
    2. 커밋/롤백을 해야 SELECT 가능 함.


  • DML 처리시 플랜에서 항상 QC 아래에 INSERT/UPDATE/DELETE 가 존재 해야 한다. (QC가 아닌 병렬서버에서 처리 토록 해야 한다.)

2.4.3 진행중인 병렬 처리가 잘되는지 궁금한데 ?

2.4.3.1 관련 뷰

  1. DISK I/O 확인
- V$SESS_IO
  1. LONG OPS
- V$SESSION_LONGOPS
  1. CURRENT STATMENT
- V$PX_SESSION

2.4.3.2 토드에서 모니터링 하는 방법

  1. Database - Session Browser
    1. IO 탭
    2. Waits 탭
    3. Current Statsment 탭
    4. Long Ops 탭

2.4.3.3 REAL MONITOR

  1. 사용법
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR('5yfzxpu5593jw') FROM DUAL;
  • html
select dbms_sqltune.report_sql_monitor(sql_id=>''5yfzxpu5593jw'',type=>'html', report_level=>'ALL') from dual;

2.4.3.4 병렬_쿼리_모니터링

  1. V$PQ_TQSTAT oracle ref.

2.4.3.5 병렬 처리 WAIT EVENT 확인 방법은?

2.4.3.5.1 병렬 처리 대기이벤트 종류
  1. PX Deq: Parse Reply
    - PEC 가 PES 에게 파싱 요청을 한 후 응답이 올 때까지 대기하는 이벤트
    - 10G 에서 도입된 PSC(Parallel Single Cursor) 모델에서는 PEC가 생성한 커서를 공유하기 때문에 이러한 과정은 생략된다.
    - 단. RAC 에서는 여전히 PEC 와 다른 노드에 존재하는 PES는 PEC가 생성한 SQL문을 파싱하는 역할을 수행
  2. PX Deq: Execute Reply
    - PEC가 가장 보편적으로 대기하는 이벤트, PES의 실제 작업이 끝나기를 기다리는 대기이벤트이다.
    - 즉 PEC가 PES가 작업을 끝낸 후 데이터를 보내주기를 기다리는 동안 이 이벤트를 대기 한다.
  3. PX Deq Credit : need buffer
    - PEC / PES 간, PES / PES 간의 통신은, 프로세스 간 존재하는 테이블 큐(Table Q)를 통해 이루어진다.
    - 가령 PES 가 테이블 큐에 데이터를 집어넣으면, PEC 가 테이블 큐에서 그 데이터를 빼가는 형식이다.
    - 오라클은 두 프로세스 중 한 순간에 오직 하나의 프로세스만이 테이블 큐에 데이터를 집어넣을 수 있도록 보장한다.
    - 테이블 큐에 데이터를 집어넣을 수 있는 자격을 확보할 때까지 기다리는 이벤트다.
  4. PX Deq: Execution Msg
    - PES 에게 가장 보편적인 대기 이벤트, PES 가 어떤 작업을 수행하기 위한 메시지를 기다리는 이벤트
    - 병렬 실행에 관계 하는 각 PES들은 특정 작업이 자신에게 할당될 때까지 기다려야 하며,
    그 동안 PX Deq: Execution Msg 이벤트를 대기한다.
  5. PX Deq: Table Q Normal
    - PES 가 테이블 큐에 데이터가 들어오기를 기다리는 이벤트
    - PES 가 다른 PES 로부터 데이터를 받아서 작업을 수행해야 하는 경우에 보편적으로 발생하는 이벤트
    - 생산자/소비자(Producer/Consumer)
    - SELECT /*+ PARALLEL ... */ FROM TABLE A ORDER BY NAME 과 같은 형태(정렬작업 필요한)의 병렬작업을
    수행하면 테이블로부터 데이터를 패치 하는 생산자 PES 와 패치된 데이터를 받아서 소비(ORDER BY) 하는 소비자 PES 가 협력하는 방식으로 작동
  6. direct path read
    - 버퍼 캐시를 경유하지 않고 데이터 파일로부터 직접 데이터를 읽는 과정에서 발생하는 이벤트
    - PES 가 테이블로부터 데이터를 페치하는 작업은 대부분 데이터 파일에서 직접 데이터를 읽는 방식을 사용한다.
  7. enq: TC Contention
    - PES 가 Direct Path I/O를 수행하려면, 해당 테이블에 대한 체크 포인트(Checkpoint)작업이 선행 되어야 한다.
    - 버퍼 캐시의 더티 버퍼가 모두 데이타 파일에 기록되어야 버퍼 캐시를 경유하지 않고 데이터 파일에서 직접 데이터를 읽을 수 있기 때문이다.
    - PEC는 PES 에게 작업을 지시하기 전에 체크포인트 요청을 하고 작업이 끝날 때 까지 기다려야 하며 그 동안 enq: TC Contention 이벤트 대기

2.4.3.5.2 병렬 세션 대기,대기 이벤트,대기 클래스 조회
SELECT DECODE(A.QCSERIAL#, NULL, 'PAREMT', 'CHILD') ST_LVL,
       A.SERVER_SET "SET",
       A.SID,
       A.SERIAL#,
       STATUS,
       EVENT,
       WAIT_CLASS
FROM   V$PX_SESSION   A,
       V$SESSION      B
WHERE  A.SID     = B.SID
AND    A.SERIAL# = B.SERIAL#
ORDER  BY A.QCSID,
          ST_LVL DESC,
          A.SERVER_GROUP,
          A.SERVER_SET
;
2.4.3.5.3 WAIT EVENT - 상세 대기시간 확인
  • V$SESSION + V$SESSSION_EVENT
SELECT S.INST_ID, S.SID, S.SQL_ID
     , S.MACHINE, S.PROGRAM, S.EVENT
     , S.BLOCKING_SESSION_STATUS
     , SE.EVENT AS EVENT_DTL
     , SE.WAIT_CLASS , SE.TIME_WAITED
     , SE.MAX WAIT   , SE.TOTAL_TIMEOUTS
  FROM GV$SESSION S
     , GV$SESSION_EVENT SE
 WHERE S.SID = SE.SID
   AND S.INST ID = 1
   AND S SID=8281
-- AND SE.EVENT = 'gc current grant busy'
-- AND S.SQL ID = ''
   AND SOL_ID IS NOT NULL
ORDER BY SE.TIME_WAITED DESC
2.4.3.5.4 대기항목별 WAIT CLASS 확인
  • V$SESSION_WAIT + V$SESSSION_WAIT_CLASS
SELECT A.SID, A.SEQ#, A.EVENT,P1TEXT
     , A.STATE , A.WAIT_TIME_MICRO
     , A.TIME_REMAINING_MICRO
     , B.WAIT_CLASS#, B.WAIT_CLASS
     , B.TOTAL_WAITS, B. TIME_WAITED
  FROM GV$SESSION WAIT A
     , GV$SESSION_WAIT_CLASS B
 WHERE A.INST_ID = B.INST_ID 
   AND A.INST_ID =1
   AND A.SID = B.SID
   AND A.SID = :SID -- 1510
   AND A.SERIAL# = B.SERIAL#

2.4.4 병렬 처리가 왜 안되지 ?

2.4.4.1 병렬 처리 갯수 확인

  • DOP 최대 갯수 = parallel_threads_per_cpu * cpu_count
SELECT *
  FROM V$PARAMETER
 WHERE NAME in ('parallel_threads_per_cpu','cpu_count')
  • DOP 최대 제약
SELECT * FROM V$PARAMETER 
 WHERE NAME IN ('parallel_degree_limit') ; -- CPU
  • DOP 최적 갯수
/*+ PARALLEL(auto) */
--- 플랜에서 확인 --
Note
I
automatic DOP: Computed Degree of Parallelism is 266
----------------------------------------------------

2.4.4.2 병렬 환경 파리미터 상세 조회

SELECT *
  FROM V$PARAMETER
 WHERE NAME LIKE '%parallel%'


  1. PARALLEL_FORCE_LOCAL
    1. 병렬로 실행되는 SQL 문이 Oracle RAC 환경의 단일 인스턴스로 제한되는지 여부를 지정
    2. 이 매개변수를 TRUE로 설정하면 쿼리 코디네이터가 실행 중인 단일 Oracle RAC 인스턴스로 처리되는 병렬 서버의 범위를 제한
    3. PARALLEL_FORCE_LOCAL 매개변수의 권장 값은 FALSE
  2. PARALLEL_MAX_SERVERS
    1. 인스턴스에 대한 최대 병렬 실행 프로세스 및 병렬 복구 프로세스 수를 지정
    2. 수요가 증가함에 따라 Oracle Database는 인스턴스 시작 시 생성된 수에서 이 값까지 프로세스 수를 늘림.
    3. DOP(병렬 처리 수준) 특성이 더 높은 테이블의 병렬 쿼리에는 기본값이 충분하지 않을 수 있습니다.
    4. 더 높은 DOP의 쿼리를 실행하려면 2 x DOP x NUMBER_OF_CONCURRENT_USERS
  3. PARALLEL_MIN_PERCENT
    1. 사용 중인 애플리케이션에 따라 사용자가 허용 가능한 DOP를 기다림.
    2. PARALLEL_MIN_PERCENT 매개변수의 권장 값은 0.
    3. 0 이외의 값으로 설정하면 지정된 시간에 시스템에서 요청된 DOP를 충족할 수 없을 때 Oracle 데이터베이스에서 오류를 반환 함.
    4. 예를 들어 PARALLEL_MIN_PERCENT를 50으로 설정하고 DOP가 적응형 알고리즘이나 리소스 제한으로 인해 50% 이상 감소하면 Oracle Database는 ORA-12827을 반환.
    5. ORA-12827: insufficient parallel query slaves available
  4. PARALLEL_MIN_SERVERS
    1. 병렬 작업을 위해 예약된 단일 인스턴스에서 시작할 프로세스 수를 지정
    2. PARALLEL_MIN_SERVERS를 설정하면 시작 비용과 메모리 사용량의 균형을 맞춤.
    3. PARALLEL_MIN_SERVERS를 사용하여 시작된 프로세스는 데이터베이스가 종료될 때까지 종료되지 않음.
  5. PARALLEL_MIN_TIME_THRESHOLD
    1. 쿼리가 자동 병렬 처리 수준을 고려하여 쿼리가 가져야 하는 최소 실행 시간을 지정.
    2. 자동 병렬 처리 수준은 PARALLEL_DEGREE_POLICY가 AUTO 또는 LIMITED로 설정된 경우에만 활성화 됨.
  6. PARALLEL_SERVERS_TARGET
    1. 명령문 큐잉이 사용되기 전에 병렬 명령문을 실행할 수 있는 병렬 서버 프로세스 수를 지정
    2. PARALLEL_DEGREE_POLICY가 AUTO로 설정된 경우 시스템에서 현재 사용 중인 병렬 프로세스 수가 PARALLEL_SERVERS_TARGET보다 크거나 같으면 병렬 실행이 필요한 명령문이 대기함.
    3. 시스템에서 허용되는 최대 병렬 서버 프로세스 수가 아님(PARALLEL_MAX_SERVERS에 의해 제어됨).
    4. 그러나 PARALLEL_SERVERS_TARGET 및 병렬 명령문 큐잉은 병렬 실행이 필요한 각 명령문에 필요한 병렬 서버 리소스가 할당되고 시스템이 너무 많은 병렬 서버 프로세스로 플러딩되지 않도록 하는 데 사용
  7. SHARED_POOL_SIZE
    1. 병렬 실행에는 직렬 SQL 실행에 필요한 것 외에 메모리 리소스가 필요
    2. 추가 메모리는 쿼리 서버 프로세스와 쿼리 코디네이터 간의 통신 및 데이터 전달에 사용
    3. Oracle Database는 공유 풀에서 쿼리 서버 프로세스를 위한 메모리를 할당. 다음과 같이 공유 풀을 조정.
      1. 공유 커서 및 저장 프로시저와 같은 공유 풀의 다른 클라이언트를 허용.
      2. 값이 크면 다중 사용자 시스템에서 성능이 향상되지만 값이 작으면 메모리 사용량이 줄어듬.
      3. 그런 다음 병렬 실행에 사용되는 버퍼 수를 모니터링하고 shared pool PX msg pool을 V$PX_PROCESS_SYSSTAT 보기의 출력에 보고된 현재 최고 수위 표시와 비교




2.4.4.3 병렬처리가 안되는경우


2.4.4.3.1 서버에서 프로세스를 할당 받지 못할때

2.4.4.3.2 insert ~ select 의 병렬도가 다를때

2.4.4.3.3 파티션닝 테이블에 1개파티션만 타는경우
  1. 튜닝 실제 예시

2.4.4.3.4 LOB 컬럼 포함시
  1. lob 컬럼 포함시 => 오라클 19c 부터기능

2.4.4.3.5 DB 링크

2.4.5 병렬 힌트사용 방법

2.4.5.1 PQ_DISTRIBUTE

PQ_DISTRIBUTE.png


  1. 병렬 조인 시, Producer 프로세스와 Consumer 프로세스 간의 데이터 전달 방식을 지정합
  2. 용도
    1. 옵티마이져가 파티션된 테이블을 적절히 활용하지 못하고 동적 재분할을 시도할 때
    2. 기존 파티션 키를 무시하고 다른 키 값으로 동작 재분할하고 싶을 때
    3. 통계정보가 부정확하거나 통계정보를 제공하기 어려운 상황에서 실행계획을 고정시키고자 할 때
    4. 기타 여러 가지 이유로 데이터 분배 방식을 변경하고자 할 때
  3. 구문
  • PQ_DISTRIBUTE(inner, none, none)
    • Full-parition Wise 조인으로 유도할 때 사용.양쪽 테이블 모두 조인 컬럼에 대해 같은 기준으로 파티션되어 있어야 작동
    • 각 병렬 쿼리 서버는 각 테이블에서 하나씩 일치하는 한 쌍의 파티션 간에 조인 작업을 수행
    • 반드시 두 테이블 모두 조인 키에 동등하게 분할되어야 함.
  • PQ_DISTRIBUTE(inner, partition, none)
    • inner 테이블의 파티션을 사용하여 outer 테이블의 행을 매핑
    • inner 테이블은 조인 키로 분할되어야 함
    • 이 힌트는 outer 테이블의 파티션 수가 병렬 쿼리 서버 수의 배수와 같거나 거의 같을 때 권장
  • PQ_DISTRIBUTE(inner, none, partition)
    • outer 테이블의 파티션을 사용하여 inner 테이블의 행을 매핑
    • outer 테이블은 조인 키에 분할되어야 함
    • 이 힌트는 outer 테이블의 파티션 수가 쿼리 서버 수의 배수와 같거나 거의 같을 때 권장
  • PQ_DISTRIBUTE(inner, hash, hash)
    • 조인키의 해시 함수를 사용하여 각 테이블의 행을 소비자 병렬 쿼리 서버에 매핑.
    • 매핑이 완료되면, 각 쿼리 서버는 결과 파티션 쌍 사이에 조인을 수행
    • 테이블의 크기가 비슷하고 조인 작업이 해시 조인 또는 정렬 병합 조인으로 구현될 때 권장
  • PQ_DISTRIBUTE(inner, broadcast, none)
    • outer 테이블의 모든 행이 각 병렬 쿼리 서버로 브로드캐스트되고 inner 테이블 행은 무작위로 분할되도록 함
    • 이 힌트는 outer 테이블이 inner 테이블에 비해 매우 작을 때 권장
  • PQ_DISTRIBUTE(inner, none, broadcast)
    • inner 테이블의 모든 행을 각각의 소비자 병렬 쿼리서버로 브로드캐스트하도록 함
    • outer 테이블 행은 무작위로 분할됩니다.
    • inner 테이블이 outer 테이블에 비해 매우 작을 때 권장



2.4.5.1.1 가능한 조합
  1. HASH - HASH : OUTER, INNER 크기가 비슷할 때
  2. BROADCAST - NONE : OUTER 테이블이 작을때
  3. NONE - BROADCAST : INNER 테이블이 작을때
  4. PARTITION - NONE : INNER 테이블 파티션 기준으로 OUTER 테이블을 파티션 하여 분배
  5. NONE - PARTITION : OUTER 테이블 파티션 기준으로 INNER 테이블을 파티션 하여 분배
  6. NONE - NONE : 두 테이블이 조인컬럼 기준으로 파티션 되어 있을때

2.4.5.2 PQ_REPLICATE / NO_PQ_REPLICATE(대량테이블)

  1. 각각 병렬서버에서 테이블 전체를 읽음
  2. BROADCAST 분산 하여 읽지 않음
  3. 로컬 캐시(SGA) 에서 빠르게 읽는 방식
  4. 복제라기보다는 조인처럼 생각
  5. 매우 작은 테이블 처리시 유리
    1. 튜닝 예시

2.4.5.3 PQ_SKEW/NO_PQ_SKEW

  1. 다수의 로우가 같은 조인키값을 가지고 있어서 조인키의 분산값이 한쪽으로 치우친 경우
  2. 오라클에서 히스토그램을 생성해야 하지만 자동으로 병렬조인시 SKEW를 핸들링함
  3. 제약사항
    1. INNER 조인시
    2. 단일 컬럼 조인시만 가능, 여러개 컬럼은 안됨
    3. 병렬 HASH JOIN만 가능
    4. MERGE JOIN 은 안됨
    5. SKEW테이블은 일반 테이블만 (뷰, 결과셋은 기능제한됨)

2.4.5.4 BF 블름필터(Bloom Filter)

  1. 어떤값이 어떤 집합에 속해 있는가를 검사하는 필터
  2. 패러럴 조인시 소비자간의 커뮤니케이션 데이터량 과 해시조인시 부하를 감소하기 위해 사용됨
  3. JOIN FILTER PRUNNING , RESULT CACHE
  4. 플랜에서 JOIN FILTER CREATE / USE
     :BF0000
2.4.5.4.1 PX_JOIN_FILTER / NO_PX_JOIN_FILTER
  1. 사용 조건
    1. 해시/머지 조인시
    2. 파티션 조인시
    3. PARALLEL 쿼리시
    4. 파티션/PARALLEL 둘다 아닌경우, 인라인뷰의 GROUP BY
  2. 선행 테이블의 상수조건이 없는 경우 오라클은 블름필터를 사용하지 않는다.
  3. 블름 필터를 만들때 선행 테이블은 필터집합을 만들때 사용.

2.4.5.5 PQ_DISTRIBUTE_WINDOW

PQ_DISTRIBUTE_WINDOW(@Query_block N) => N=1 for hash, N=2 for range, N=3 for list(예전 방식 9i)

rem ##################################
rem # Objects                        #
rem ##################################

alter session set optimizer_adaptive_plans = false;
alter system flush shared_pool;

drop table asc_dmy1;
drop table asc_dmy3;

create table asc_dmy1
parallel 8
as 
select 'AAA' f001
  from xmltable('1 to 300');
  
--note: this table has no parallel degree
create table asc_dmy3
as
select 'AAA' f001, 1 acc206
  from dual;

rem #############################################
rem # SORT then distribute by HASH (Bug)        #
rem #############################################  
/*
   leads to a HASH JOIN in Line 7, which imo must be a HASH JOIN BUFFERED (due to 2 active PX SENDs at 9 and 13) 
   This SQL hangs and never finishes 
   
   https://oracle-randolf.blogspot.com/2012/12/hash-join-buffered.html
   "At most one data distribution can be active at the same time"
   
   "Since it doesn't seem to be supported to have two PX SEND operations active at the same time, 
    some artificial blocking operation needs to be introduced, in this case the HASH JOIN BUFFERED, 
	that first consumes the second row source completely before starting the actual probe phase"
*/
select /*+ pq_distribute_window(@"SEL$1" 2) */
       max(v.acc206) over (partition by v.f001) max_bew
  from asc_dmy3 v,
       asc_dmy1 e
 where e.f001 = v.f001
   and v.f001 = e.f001;  

/*   
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |   419 |     6  (17)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR              |          |        |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)        | :TQ10003 |      1 |   419 |     6  (17)| 00:00:01 |  Q1,03 | P->S | QC (RAND)  |
|   3 |    WINDOW CONSOLIDATOR BUFFER|          |      1 |   419 |     6  (17)| 00:00:01 |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE               |          |      1 |   419 |     6  (17)| 00:00:01 |  Q1,03 | PCWP |            |
|   5 |      PX SEND HASH            | :TQ10002 |      1 |   419 |     6  (17)| 00:00:01 |  Q1,02 | P->P | HASH       |
|   6 |       WINDOW SORT            |          |      1 |   419 |     6  (17)| 00:00:01 |  Q1,02 | PCWP |            |
|*  7 |        HASH JOIN             |          |      1 |   419 |     5   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   8 |         PX RECEIVE           |          |      1 |   415 |     3   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   9 |          PX SEND HASH        | :TQ10000 |      1 |   415 |     3   (0)| 00:00:01 |  Q1,00 | S->P | HASH       |
|  10 |           PX SELECTOR        |          |        |       |            |          |  Q1,00 | SCWC |            |
|  11 |            TABLE ACCESS FULL | ASC_DMY3 |      1 |   415 |     3   (0)| 00:00:01 |  Q1,00 | SCWP |            |
|  12 |         PX RECEIVE           |          |    300 |  1200 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  13 |          PX SEND HASH        | :TQ10001 |    300 |  1200 |     2   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |
|  14 |           PX BLOCK ITERATOR  |          |    300 |  1200 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  15 |            TABLE ACCESS FULL | ASC_DMY1 |    300 |  1200 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------   
*/

rem #############################################
rem # distribute by HASH then SORT  (Success)   #
rem #############################################  
/*
   leads to a HASH JOIN *BUFFERED* in Line 6, which is inevitably necessary imo
   This SQL finishes immediately
*/ 
select /*+ pq_distribute_window(@"SEL$1" 1) */
       max(v.acc206) over (partition by v.f001) max_bew
  from asc_dmy3 v,
       asc_dmy1 e
 where e.f001 = v.f001
   and v.f001 = e.f001;    

/*
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib |  OMem |  1Mem |  O/1/M   |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |          |        |       |     6 (100)|          |        |      |            |       |       |          |
|   1 |  PX COORDINATOR            |          |        |       |            |          |        |      |            | 73728 | 73728 |          |
|   2 |   PX SEND QC (RANDOM)      | :TQ10003 |      1 |   419 |     6  (17)| 00:00:01 |  Q1,03 | P->S | QC (RAND)  |       |       |          |
|   3 |    WINDOW SORT             |          |      1 |   419 |     6  (17)| 00:00:01 |  Q1,03 | PCWP |            | 20480 | 20480 |     8/0/0|
|   4 |     PX RECEIVE             |          |      1 |   419 |     5   (0)| 00:00:01 |  Q1,03 | PCWP |            |       |       |          |
|   5 |      PX SEND HASH          | :TQ10002 |      1 |   419 |     5   (0)| 00:00:01 |  Q1,02 | P->P | HASH       |       |       |          |
|*  6 |       HASH JOIN BUFFERED   |          |      1 |   419 |     5   (0)| 00:00:01 |  Q1,02 | PCWP |            |  3400K|  3091K|     8/0/0| 
|   7 |        PX RECEIVE          |          |      1 |   415 |     3   (0)| 00:00:01 |  Q1,02 | PCWP |            |       |       |          |
|   8 |         PX SEND HASH       | :TQ10000 |      1 |   415 |     3   (0)| 00:00:01 |  Q1,00 | S->P | HASH       |       |       |          |
|   9 |          PX SELECTOR       |          |        |       |            |          |  Q1,00 | SCWC |            |       |       |          |
|  10 |           TABLE ACCESS FULL| ASC_DMY3 |      1 |   415 |     3   (0)| 00:00:01 |  Q1,00 | SCWP |            |       |       |          |
|  11 |        PX RECEIVE          |          |    300 |  1200 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |       |       |          |
|  12 |         PX SEND HASH       | :TQ10001 |    300 |  1200 |     2   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |       |       |          |
|  13 |          PX BLOCK ITERATOR |          |    300 |  1200 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |       |       |          |
|* 14 |           TABLE ACCESS FULL| ASC_DMY1 |    300 |  1200 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------
*/

2.4.5.6 PQ_EXPAND_TABLE / NO_PQ_EXPAND_TABLE

  1. PQ_EXPAND_TABLE
    1. 파티션테이블인 경우 한곳에 편중된 파티션이 있을때, 옵티마이저가 UNION 절로 변경하여 편중된 파티션과 다른 파티션을 나누어 병렬처리로 수행함.
    2. (예) 인라인뷰내 파티션테이블을 (GROUP BY하는 경우)발생함
  • 튜닝 사례 1 : 한곳에 치중된 파티션을 UNION ALL 로 분리
SELECT *
  FROM 
(SELECT /*+ PQEXPAND_TABLE(A2) */
        AZ.SRV_INSTLID
      , A2.CYKIM_TIMPL_ID
      , A2.CYKIM_WRT_YMD
      , A2.CYKIM_WRT_DGR
      , MIN(A2.DCRY_LNNO) AS DCRY_LNNO 
  FROM TB_BIG_PART A2 -- 파티션테이블 
 WHERE SUBSTR(A2.CYKIM_TMPL_ID, 0, 2) = 'am'
 GROUP BY A2.SRV_INST_ID
        , A2.CYKIM_TMP_ID 
        , A2.CYKIM_WRT_YMD
        , A2.CYKIM_WRT_DGR
) A
, TB_CYKIM_SPRV B
.....
  • 튜닝 사례 2 : 한곳에 치중된 파티션을 UNION ALL 로 분리 하지 않음.
SELECT *
  FROM 
(SELECT /*+ NO_PQEXPAND_TABLE(A2) */
        AZ.SRV_INSTLID
      , A2.CYKIM_TIMPL_ID
      , A2.CYKIM_WRT_YMD
      , A2.CYKIM_WRT_DGR
      , MIN(A2.DCRY_LNNO) AS DCRY_LNNO 
  FROM TB_BIG_PART A2 -- 파티션테이블의  한곳의 파티션에 치중됨, 예를 들어 2000년 이전 데이터는 PT_2001파티션에 1억건 존재 , 나머지는 1백만건 
 WHERE SUBSTR(A2.CYKIM_TMPL_ID, 0, 2) = 'am'
 GROUP BY A2.SRV_INST_ID
        , A2.CYKIM_TMP_ID 
        , A2.CYKIM_WRT_YMD
        , A2.CYKIM_WRT_DGR
) A
, TB_CYKIM_SPRV B
.....

2.4.5.7 PQ_CONCURRENT_UNION

  1. UNION ALL 성능향상
  2. UNION은 각각 SQL을 1개씩 SERIAL 하게 처리하는게 기본방식임
  3. 12C 부터 병렬쿼리 실행시 동시(CONCURRENT)에 처리토록 함
  4. 전체데이터 처리시 유리
  5. 부분범위 처리시 비추
  6. DEFAULT

2.4.5.8 PQ_FILTER

/*+ PQ_FILTER(SERIAL | NONE | HASH | RANDOM) */ -- 4개중 택1
  1. 병렬서버에서 서브쿼리를 필터링할수 있는 기능.
  2. 서브쿼리 필터링은 일반적으로 메인쿼리가 모두 수행된 후 수행함.
  3. HASH방식과 RANDOM방식은 추가적인 버퍼링이 필요하므로 특별한 경우가 아니면 NONE 방식으로 사용하는것이 일반적일것으로 판단함.
  4. 다수의 서브쿼리 수행시 2개의 서브쿼리 모두 병렬서버에서 필터링됨.
=> ORDER_SUBQ 힌트(12c 이후) 로 서브쿼리의 수행순서 조정가능함.
  • PQ_FILTER 사용예시 (with NO_UNNEST 힌트)
SELECT /* + PARALLEL(2) PQ_FILTER(HASH) */
       *
  FROM T1 A
 WHERE EXISTS (SELECT /*+ NO_UNNEST */
                      1
                 FROM T2 B
                WHERE B.C1 = A.C1)
  • 서브 쿼리가 2개일 때 순서 조정 예시
SELECT /*+ PARALLEL(2) PQ_FILTER(NONE) 
           ORDER_SUBQ(@MAIN @SUB2 @SUB1) QB_NAME(MAIN) */
       * 
 FROM T1 A
WHERE EXISTS (SELECT /*+ NO_UNNEST QB_NAME(SUB1) */
                      1
                 FROM T2 B
                WHERE B.C1 = A.C1)
  AND EXISTS (SELECT /*+ NO_UNNEST QB_NAME(SUB2) */
                      1
                 FROM T3 C
                WHERE C.C1 = A.C1)


QC 에서 서브쿼리를 필터링함


FILTER
  PX COORDINATOR
    PX SEND QC(RANDOM)
      PX BLOCK ITERATOR
        TABLE ACCESS FULL T1
  INDEX RANGE SCAN IX_T2


  1. 메인쿼리를 분배하지 않고 병렬서버에서 서브쿼리를 필터링함.
  2. 병렬서버가 T1 테이블을 블록 그래뉼로 조회하고, 조회결과로 서브쿼리를 필터링하여 QC로 결과를 전송하는 방식


PX COORDINATOR
  PX SEND QC(RANDOM)
   FILTER
     PX BLOCK ITERATOR.
        TABLE ACCESS FULL 11
     INDEX RANGE SCAN IX_12
PX COORDINATOR
  PX SEND QC(RANDOM)
    FILTER
      PX BLOCK ITERATOR
         TABLE ACCESS FULL T1
    INDEX RANGE SCAN X_13
    INDEX RANGE SCAN IX_12


  1. 메인 쿼리를 HASH 방식으로 분배 한후, 병렬서버에서 서브쿼리를 필터링함.
  2. HASH방식은 조인조건인 C1 컬럼의 해시값으로 분배 되기 때문에 서브쿼리 캐싱 효율을 높이는 목적으로 활용가능.


PX COORDINATOR
  PX SEND QC(RANDOM)
    BUFFER SORT
      FILTER
        PX RECEIVE
           PX SEND HASH
              PX BLOCK ITERATOR
                TABLE ACCESS FULL T1
           INDEX RANGE SCAN IX_T2


  1. 메인쿼리를 RANDOM 방식으로 분배한후, 병렬서버에서 서브쿼리를 필터링함.
  2. RANDOM 방식은 read by other session등 의 블럭경합을 해소하는 목적으로 활용가능


PX COORDINATOR
  PX SEND QC(RANDOM)
    BUFFER SORT
      FILTER
        PX RECEIVE
           PX SEND ROUND-ROBIN
              PX BLOCK ITERATOR 
                TABLE ACCESS FULL T1
              INDEX RANGE SCAN IX_T2

2.4.5.9 파티션

  1. USE PARTITION_WISE_DISTINCT
  2. USE PARTITION_WISE_GBY
  3. USE PARTITION_WISE_WIF

2.4.6 튜닝포인트를 찾아라

2.4.6.1 테이블 정보를 수집 하라

  1. 테이블 사이즈 조사
  2. 테이블 파티셔닝 여부
  3. 파티션 키
  4. 병렬처리가 가능한 인덱스(index_ffs , 파티션 로컬 인덱스)

2.4.6.2 스칼라퀴리는 인라인뷰로 변경을 검토하라

  1. 쿼리 결과를 전송하는 단계에서 수행되는 스칼라 서브쿼리는 QC가 담당
  2. lateral 쿼리 활용법

2.4.6.3 BROADCAST 테이블을 찾아라

  1. 임시테이블에 주의하라
     : 임시테이블은 주로 통계 정보가 생성되어 있지 않아. 통계정보 오류로 인해 브로드캐스트로 처리되는 경우가 많음.

2.4.6.4 튜닝 대상을 찾아라

  1. rownum => row_number 윈도우 함수로 변경
  2. S->P 분산 프로세스
  3. round - robin
    1. 튜닝 실제 예시

2.4.6.5 조인이 효율적인지 검토 하라

  1. 튜닝 실제 예시

2.4.6.6 심플하게 튜닝 하는 방법은 없을까?

  1. opt_param('_parallel_broadcast_enabled','false')
  2. pq_distribute(A hash hash)
  3. 튜닝 실제 예시

2.4.7 입력/수정 성능저하시 검토 사항


2.4.7.1 INSERT 처리가 느릴때(SELECT~INSERT시 SELECT는 빠른데 INSERT가 느린경우)

  1. hwm / reorg

2.4.7.2 시퀀스를 사용하는경우

  1. sequence cache size 증가 검토
  2. default 20 => 2000이상

2.4.7.3 DB링크 병렬처리 주의사항

dblink로 가져올때는 병렬처리 불가


2.4.8 데이터 전환시 사용하는 병렬처리

-- 세션에서 병렬 쿼리 작업 절차
ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION SET HASH_AREA_SIZE          = 1024000000;
ALTER SESSION SET SORT_AREA_SIZE          = 2147483647;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE = 2147483647;
ALTER SESSION SET WORKAREA_SIZE_POLICY    = MANUAL; 
-- 사용자가 지정한 sort_area_size가 모든  병렬 서버에게 적용. 
-- sort_area_size를 크게 설정한 상태에서 지나치게 큰 병렬도를 지정하면
-- OS 레벨에서 페이징이 발생하고 심할 경우 시스템을 마비시킬 수 있음.
ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=256; -- AIX는 최대 512 

ALTER SESSION SET "_sort_multiblock_read_count"     = 256;
ALTER SESSION SET "_db_file_optimizer_read_count"   = 256;
ALTER SESSION SET "_db_file_exec_read_count"        = 256;
ALTER SESSION SET "_serial_direct_read"             = TRUE;
-- 병렬 실행 메시지 사이즈 32K
alter system set parallel_execution_message_size = 32768; -- 16384 -- 기본

2.4.9 그외 병렬 처리시 주의 사항

  1. 실행계획에서 P ->P가 나타날 때면 지정한 병렬도의 2배수만큼 병렬 프로세스 필요
  2. 쿼리 블록마다 병렬도를 다르게 지정한 경우, 여러 가지 우선 순위와 규칙에 따라 최종 병렬도가 결정됨, 병렬도를 같게 지정하는 것이 바람직 함.
  3. parallel_index 힌트를 사용할 때는 반드시 index 또는 index_ffs 힌트를 사용
    1. 옵티마이저에 의해 Full table Scan이 선택될 경우 parallel_index 힌트는 무시 됨
  4. 병렬 DML 수행시 Exculsive 모드 테이블 Lock이 걸리므로 업무 트랜잭션이 발생하는 주간에는 삼가
  5. 테이블이나 인덱스를 빠르게 생성하려고 parallel 옵션 을 사용했다면 작업을 완료 후 즉시 noparallel로 변경 할것
  6. 부분범위처리 방식으로 조회하면서 병렬 쿼리를 사용한 때에는 필요한 만큼 데이터를 Fetch 하고 나서 곧바로 커서를 닫아 주어야 함
    1. Toad나 Orange 처럼 부분범위처리를 지원하는 쿼리 툴에서는 EOF에 도달하기 전까지
    2. 커서를 오픈한 채로 유지하기 때문에 오라클은 병렬 서버들을 해제하지 못하고 대기 상태에 머물도록 한다.
    3. 이는 불필요한 리소스를 낭비하는 결과를 초래하므로 조회가 끝나자마자
    4. select * from dual 같은 문장을 수행해 병렬 쿼리의 커서를 닫아 주어야 한다.



<script type="text/javascript" src="https://cdnjs.buymeacoffee.com/1.0.0/button.prod.min.js" 데이터 이름="bmc-버튼" 데이터 슬러그="autoexe" data-color="#FFDD00" data-emoji="" data-font="Lato" data-text="커피 한잔 쏩니다." data-outline-color="#000000" data-font-color="#000000" data-coffee-color="#ffffff" ></script>

2.5 쿼리 변환 종류

  1. 10g부터는 서브쿼리 Unnesting과 뷰 Merging이 비용기반 쿼리 변환으로 전환됨
  2. 조건절 Pushing 중 조인 조건 pushdown도 비용기반 쿼리 변화으로 전환
  3. 나머지는 변환된 쿼리가 항상 더 나은 성능을 제공하므로 비용기반으로 전환이 불필요

2.5.1 서브쿼리 Unnesting

  1. 옵티마이져가 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화 하는것 이를 '서브쿼리 Unnesting' 이라고 함.
  2. 서브쿼리를 메인쿼리와 같은 레벨로 풀어 내서 다양한 엑세스 경로와 조인 메소드를 평가 할수 있음.
  3. 옵티마이저는 조인 형태로 변환했을때 더 나은 실행계획을 찾을 가능성이 높다
  4. 변환된 쿼리의 예상 비용이 더 낮을 때만 Unnesting된 버전을 사용, 그렇지 않을 때는 원본 쿼리 그대로 필터 방식으로 최적화 한다.

2.5.1.1 unnest 힌트

서브쿼리를 Unnesting 함을써 조인방식으로 최적화하도록 유도.

2.5.1.2 no_unnesting 힌트

서브쿼리를 그대로 둔 상태에서 필터 방식으로 최적화 하도록 유도.

2.5.2 뷰 Merging

2.5.3 조건절 Pushing

2.5.4 조건절 이행

2.5.5 공통 표현식 제거

2.5.6 outer 조인을 Inner 조인으로 변환

2.5.7 실체화 뷰 쿼리로 재작성

2.5.8 star 변환

2.5.9 outer 조인 뷰에 대한 조인 조건 Pushdown

2.5.10 or-expansion

2.6 쿼리 변환 두 가지 방식

2.6.1 휴리스틱 쿼리 변환

 : 결과만 보장된다면 무조건 쿼리 변환을 수행 

2.6.2 비용기반 쿼리 변환

 : 변환된 쿼리의 비용이 더 낮을 때만 그것을 사용하고, 그렇지 않을 때는 원본 쿼리 그대로 두고 최적화를 수행한다.

2.7 DB LINK를 사용한 분산 쿼리의 튜닝

2.7.1 Nested Loop을 피하고, Hash Join 처리 하여, Network Round Trip 을 줄인다

  1. 네트웍을 통한 분산 SQL 튜닝에서,로컬 DB에서만 수행되는 SQL과 튜닝포인트가 다른 점
    1. 분산 DB QUERY의 NESTED LOOP 조인은 조인 건수만큼의 네트웍 RoundTrip이 발생
    2. 조인건수가 많을 경우 네트웍 Round Trip 에 대량 시간 발생
    3. 가급적 Sort-Merge나 Hash Join으로 수행되도록 PLAN을 조정하여, 조인으로 인한 Network Roundtrip을 줄이도록 유도

2.7.2 Driving_Site 힌트를 이용하여, 리모트 DB가 SQL 수행의 주체가 되도록 한다.

2.7.3 바인드변수나 문자열값의 직접 사용은 PLAN을 고정 시키게 된다.

2.7.3.1 Driving_Site 힌트로 리모트DB를 지정하여, PLAN을 조정 하는 경우,

  1. SQL에 바인드 변수 나 직접적인 문자열 값이 있는 경우 힌트가 원하는대로 적용되지 않음.

2.7.3.2 SQL에 SELECT-LIST에 문자열값 이나 바인드 변수 값이 있으면,

PLAN상 Remote에서 수행이 되지 않고 항상 로컬에서 수행 됨.
  1. 이 경우 문자열이나 바인드 변수값을 제외한 SQL을 인라인 뷰에서 수행하게 하여 , Remote DB에서 해당 SQL이
  2. 수행되도록 하고, 인라인 뷰 밖에서 필요한 문자열 값을 주고, NO_MERGE 힌트를 사용하도록 합니다.
* 변경 전 (DRIVING_SITE 힌트가 반영 되지 않음 )
INSERT INTO T3
SELECT /*+ DRIVING_SITE(T1) */
‘ADD_COLUMN’, T1.*, T2.*
FROM T1@LINK1 T1, T1@LINK1 T2
WHERE A.COL1 = B.COL2;
* 변경 후 (DRIVING_SITE 힌트 반영)
INSERT INTO T3
SELECT /*+ NO_MERGE(A) */ 'ADD_COLUMN', A.*
FROM (
-- 리모트DB 에서 조인이 이루어 지도록 
SELECT /*+ DRIVING_SITE(A) */
T1.*, T2.*
FROM T1@LINK1 T1, T1@LINK1 T2
WHERE A.COL1 = B.COL2) A;
  • DRIVING_SITE 힌트를 줘도 리모트 DB에서 T1과 T2테이블을 로컬 DB로 읽어와서 로컬에서 조인.
  • 리모트DB에서 조인해서 결과값만 받는 것이 일반적으로 유리함.
  • 문자열값을 밖으로 뺀 인라인뷰와 NO_MERGE 힌트를 이용하여 원하는 PLAN으로 수정 가능.

  1. 뷰를 이용하여, PLAN 조정
    1. REMOTE 사이트의 테이블 여러개 RK 조인될 경우 해당 SQL을 해당 리모트사이트에 뷰를 만들어 놓는다면, 한번의 Remote Operation 만이 이루어질 것입니다.
    2. 즉, Driving_site 힌트가 제대로 수행이 되지 않는 경우 수행의 주체가 되기를 원하는 Remote DB상에 View를 생성하여 해당 View 를 SELECT 하여 PLAN을 조정

2.7.4 dblink로 가져올때는 병렬처리 불가

  1. 단일 dblink 통해 "병렬"을 수행할 수 없으며 dblink의 remote서버 병렬을 수행할 수 있지만 로컬로 가져올때는 네트워크 파이프가 하나만 있으므로 직렬로 연결됨
  2. 작업을 더 작은 조각으로 나누어야 함
  3. 가장 쉬운 방법은 각 파티션에 대한 작업(dbms_job, dbms_scheduler)을 설정하고 해당 작업에서 로드를 수행하는 것. 로컬 테이블이 같은 방식으로 분할된 경우 - 각 작업은 다음과 같이 동적 SQL을 사용하여 파티션을 로드합니다.
insert /*+ append */ into localtable partition( PNAME ) 
select * from remotetable@dblink partition(PNAME)

3 Oracle 힌트

3.1 힌트의 종류 별 분류

3.1.1 Optimization Goals and Approaches

  1. ALL_ROWS 혹은 FIRST_ROWS
  2. CHOOSE
  3. RULE

3.1.2 Acess Method Hints

            AND_EQUAL
            CLUSTER
            FULL
            HASH
            INDEX 혹은 NO_INDEX
            INDEX_ASC 혹은 INDEX_DESC
            INDEX_COMBINE
            INDEX_FFS
            ROWID

3.1.3 Join Order Hints

            ORDERED
            STAR

3.1.4 Join Operation Hints

            DRIVING_SITE
            HASH_SJ, MERGE_SJ 혹은 NL_SJ
            LEADING
            USE_HASH 혹은 USE_MERGE
            USE_NL

3.1.5 Parallel Execution Hints

            PARALLEL 혹은 NOPARALLEL
            PARALLEL_INDEX
            PQ_DISTRIBUTE
            NOPARALLEL_INDEX

3.1.6 Query Transformation Hints

            EXPAND_GSET_TO_UNION
            FACT 혹은 NOFACT
            MERGE
            NO_EXPAND
            NO_MERGE
            REWIRTE 혹은 NOREWRITE
            STAR_TRANSFORMATION
            USE_CONCAT

3.1.7 Other Hints

            APPEND 혹은 NOAPPEND
            CACHE 혹은 NOCACHE
            CURSOR_SHARED_EXACT
            DYNAMIC_SAMPLING
            NESTED_TABLE_GET_REFS
            UNNEST 혹은 NO_UNNEST
            ORDERED_PREDICATES
 

3.2 힌트의 설명 및 사용법

3.2.1 ALL_ROWS

/*+ ALL_ROWS */

  • 최소한의 자원을 사용하여 결과값의 전체를 추출하게 합니다.
  1. ALL_ROWS 힌트는 최소 총 리소스 소비 인 최상의 처리량을 목표로 문 블록을 최적화하도록 최적화 프로그램에 지시합니다.
    1. 예를 들어 최적화 프로그램은 쿼리 최적화 접근 방식을 사용하여 최상의 처리량을 위해이 문을 최적화합니다.
  2. SQL 문에 ALL_ROWS 또는 FIRST_ROWS 힌트를 지정하고 데이터 딕셔너리에 액세스 한 테이블에 대한 통계가없는 경우 옵티마이저는 이러한 테이블에 할당 된 스토리지와 같은 기본 통계 값을 사용하여 누락된 통계 및 이후에 실행 계획을 선택합니다.
    1. 이러한 추정치는 DBMS_STATS 패키지에서 수집 한 것만 큼 정확하지 않을 수 있으므로 DBMS_STATS 패키지를 사용하여 통계를 수집해야합니다.
  3. ALL_ROWS 또는 FIRST_ROWS 힌트와 함께 액세스 경로 또는 조인 작업에 대한 힌트를 지정하면 최적화 프로그램이 힌트에 지정된 액세스 경로 및 조인 작업에 우선 순위를 부여합니다.

3.2.2 AND_EQUAL

/*+ AND_EQUAL (table index index [index] [index] [index] ) */

  • 복수의 단일 컬럼을 스캔하여 머지 방식으로 처리하게 합니다.

3.2.3 APPEND HINT

/*+ APPEND */

  1. 시리얼모드 데이터베이스에서 Direct INSERT를 실행하게 합니다.
  2. Enterprise Edition 이 아닌 데이터베이스의 기본 모드는 시리얼 모드입니다. 이러한 직렬 모드 데이터 베이스에서의 INSERT 작업은 Conventional를 기본값으로 하고 병렬 처리 시에는 Direct INSERT를 기본값으로 합니다.
  1. APPEND 힌트는 최적화 프로그램이 직접 경로 INSERT를 사용하도록 지시합니다.
  2. 일반 INSERT는 시리얼 모드의 기본값입니다. 시리얼 모드에서는 APPEND 힌트를 포함하는 경우에만 다이렉트 패스를 사용할 수 있습니다.
  3. 패러럴모드에서 다이렉트 패스INSERT는 기본값입니다. 병렬 모드에서는 NOAPPEND 힌트를 지정한 경우에만 일반 INSERT을 사용할 수 있습니다.
  4. INSERT가 병렬로 진행 될지 여부는 APPEND 힌트와 무관합니다.
  5. 다이렉트 패스INSERT에서 데이터는 현재 테이블에 할당 된 기존 공간을 사용하지 않고 테이블 끝에 추가됩니다. 결과적으로 다이렉트 패스 INSERT는 기존 INSERT보다 훨씬 빠를 수 있습니다.

3.2.4 CACHE HINT

cache_hint.gif

/*+ CACHE (table) +/

  • FULL 테이블 스캔의 사용 시, 테이블에서 읽어온 블럭을 버퍼의 LRU 리스트 의 MRU 쪽에 위치시킵니다. 작은 테이블의 사용 시 유용합니다.
SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name
  FROM employees hr_emp;
  1. CACHE 및 NOCACHE 힌트는 V$SYSSTAT 데이터 딕셔너리 뷰에 표시된대로 시스템 통계 테이블 스캔 (롱 테이블) 및 테이블 스캔 (숏 테이블)에 영향을줍니다.

3.2.5 CHOOSE HINT

/*+ CHOOSE +/

  • Rule-Based 와 Cost-Based 방식 간의 선택을 유도합니다. 선택 기준은 사용 객체의 분석 정보 존재 여부이며, 사용되는 객체들중 하나라도 분석 정보가 존재한다면 Cost-Based 방식을 사용하게 됩니다.

3.2.6 CLUSTER HINT

/*+ CLUSTER (table) +/

  • 지정 테이블의 클러스터 스캔을 유도합니다. 클러스터된 객체에만 사용할 수 있습니다.

3.2.7 CURSOR_SHARING_EXACT

/*+ CURSOR_SHARING_EXACT +/

  • 바인드 변수 값의 교체를 불가능하게 합니다.
  • 기본적으로 CURSOR_SHARING 파라미터를 사용하여, 안전하다고 판단될 시 SQL 내의 바인드 변수 값을 교체할 수 있게 되어 있습니다.
  1. Oracle은 안전한 경우 SQL 문의 리터럴을 바인드 변수로 바꿀 수 있습니다.
  2. 이 교체는 CURSOR_SHARING 초기화 매개 변수로 제어됩니다.
    1. CURSOR_SHARING_EXACT 힌트는 최적화 프로그램에이 동작을 해제하도록 지시합니다.
    2. 이 힌트를 지정하면 Oracle은 리터럴을 바인드 변수로 바꾸지 않고 SQL 문을 실행합니다.

3.2.8 DRIVING_SITE

driving_site_hint.gif

/*+ DRIVING_SITE (table) +/

  • 오라클이 선택한 SITE 대신, 지정한 SITE를 사용하여 쿼리를 실행합니다. Rule-Based 와 Cost-Based, 두 모드 다 사용 가능합니다.
SELECT  /*+ DRIVING_SITE(departments) */ * 
  FROM employees, departments@rsite 
  WHERE employees.department_id = departments.department_id;
  • 이 쿼리가 힌트없이 실행되면 departments의 ROW가 로컬 사이트로 전송되고 여기에서 조인이 실행됩니다.
  • 힌트를 사용하면 employees의 ROW가 원격 사이트로 전송되고 쿼리가 실행되고 결과 집합이 로컬 사이트로 반환됩니다.

3.2.9 DYNAMIC_SAMPLING

/*+ DYNAMIC_SAMPLING ( [table] n ) +/

  • 해당 객체의 Selectivity 와 Cardinality 에 대한 보다 자세한 정보를 자동으로 생성시켜 실행합니다.
  • 값은 0 부터 10 까지 지정할 수 있으며, 높을 수록 보다 자세한 정보를 생성하게 됩니다. 테이블에 해당 값을 지정하지 않았을 경우, 기본 값은 CURSOR 레벨의 값이 쓰여집니다.

3.2.10 EXPAND_GSET_TO_UNION

/*+ EXPAND_GSET_TO_UNION +/

  • GROUP BY GROUPING SET 혹은 GROUP BY ROLLUP 등과 같은 구문을 포함하는 쿼리에 사용할 수 있습니다.
  • 이 힌트는 기존의 쿼리를 개별적인 그룹 생성 후, UNION ALL 방식으로 실행되게 유도합니다.

3.2.11 FACT HINT

/*+ FACT (table) +/

  • 스타 변형 구문에서 사용되며 해당 테이블이 FACT 테이블로 사용되게 유도합니다.

3.2.12 FIRST_ROWS

/*+ FIRST_ROWS (n) +/

  1. FIRST_ROWS 힌트는 Oracle이 빠른 응답을 위해 개별 SQL 문을 최적화하도록 지시하여 처음 n 개 행을 가장 효율적으로 반환하는 계획을 선택합니다.
  2. 정수의 경우 반환 할 행 수를 지정합니다.
SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id
  FROM employees
  WHERE department_id = 20;

이 예에서 각 부서에는 많은 직원이 있습니다. 사용자는 부서 20의 처음 10 명의 직원이 가능한 빨리 표시되기를 원합니다.

  1. 옵티마이져는 DELETE 및 UPDATE 문 블록과 정렬 또는 그룹화와 같은 블록킹 명령을 포함하는 SELECT문 블록에서 이 힌트를 무시합니다.
  2. Oracle Database는 첫 번째 행을 반환하기 전에 명령문에서 액세스 한 모든 행을 검색해야하므로 이러한 명령문은 최상의 응답 시간을 위해 최적화 할 수 없습니다.
  3. 이러한 명령문에이 힌트를 지정하면 데이터베이스가 최상의 처리량을 위해 최적화됩니다.

3.2.13 FULL HINT

/*+ FULL (table) */

  • 지정한 테이블에 대해 풀 테이블 스캔을 유도합니다.

3.2.14 HASH HINT

hash_hint.gif

/*+ HASH (table) */

  • 지정한 테이블에 대해 hash 스캔을 수행하도록 유도합니다.
  • 클러스터 테이블 만을 대상으로 합니다.

3.2.15 HASH_AJ

/*+ HASH_AJ */

  • EXISTS나 IN조건을 사용한 경우 서브쿼리에 UNNEST와 함께 HASH_SJ 힌트를 부여하면 HASH JOIN SEMI로 처리하도록 제어
  • HASH_SJ 은 hash semi-join 이고, MERGE_SJ 은 sort merge semi-join 이며 NL_SJ 은 nested loop semi-join 입니다.

3.2.16 INDEX

index_hint.gif

/*+ INDEX (table index [index] [index] ... ) */

  • 지정한 테이블의 인덱스 스캔을 실행하도록 유도합니다.
  • Domain, B-tree, bitmap, bitmap join 인덱스 등이 사용될 수 있으나, bitmap 인덱스 들의 사용 시, INDEX 힌트보다는 INDEX_COMBINE 힌트 사용이 추천됩니다.
  • /*+ INDEX(A (컬럼1,컬럼2)) */ 컬럼명으로 인덱스 명시 하기
  1. INDEX 힌트는 지정된 테이블에 대해 인덱스 스캔을 사용하도록 최적화 프로그램에 지시합니다.
    1. 함수 기반, 도메인, B- 트리, 비트 맵 및 비트 맵 조인 인덱스에 대해 INDEX 힌트를 사용할 수 있습니다.
    2. 힌트의 동작은 indexspec 사양에 따라 다릅니다.
    3. INDEX 힌트가 사용 가능한 단일 인덱스를 지정하는 경우 데이터베이스는 인덱스에 대해 스캔을 수행합니다.
      1. 옵티마이저는 FULL 테이블 스캔이나 테이블의 다른 인덱스 스캔을 고려하지 않습니다.
    4. 여러 인덱스 조합에 대한 힌트의 경우 Oracle은 INDEX가 아닌 INDEX_COMBINE을 사용할 것을 권장합니다. 이는보다 다양한 힌트이기 때문입니다.
      1. INDEX 힌트가 사용 가능한 인덱스 목록을 지정하는 경우 옵티마이 저는 목록의 각 인덱스에 대한 스캔 비용을 고려한 다음 가장 낮은 비용으로 인덱스 스캔을 수행합니다.
      2. 데이터베이스는 이러한 액세스 경로의 비용이 가장 낮은 경우이 목록에서 여러 인덱스를 스캔하고 결과를 병합하도록 선택할 수도 있습니다.
      3. 데이터베이스는 FULL 테이블 스캔 또는 힌트에 나열되지 않은 인덱스에 대한 스캔을 고려하지 않습니다.
    5. INDEX 힌트가 인덱스를 지정하지 않으면 옵티마이 저는 테이블에서 사용 가능한 각 인덱스에 대한 스캔 비용을 고려한 다음 가장 낮은 비용으로 인덱스 스캔을 수행합니다.
      1. 데이터베이스는 이러한 액세스 경로의 비용이 가장 낮은 경우 여러 인덱스를 스캔하고 결과를 병합하도록 선택할 수 있습니다.
      2. 옵티마이저는 FULL 테이블 스캔을 고려하지 않습니다.

3.2.17 INDEX_ASC

/*+ INDEX_ASC (table [index] [index] ... ) +/

  • 해당 테이블의 인덱스를 순차적 방식으로 스캔하게 합니다.
  • 해당 쿼리가 인덱스 범위 스캔의 사용 시, 인덱스 값의 순차적 방식으로 읽게 됩니다.
  1. INDEX_ASC 힌트는 지정된 테이블에 대해 인덱스 스캔을 사용하도록 최적화 프로그램에 지시합니다.
    1. 명령문이 인덱스 범위 스캔을 사용하는 경우 Oracle 데이터베이스는 인덱스 된 값의 오름차순으로 인덱스 항목을 스캔합니다.
    2. 각 매개 변수는 "INDEX Hint"에서와 동일한 용도로 사용됩니다.
  2. 범위 스캔의 기본 동작은 색인화 된 값의 오름차순 또는 내림차순 색인의 경우 내림차순으로 색인 항목을 스캔하는 것입니다.
    1. 이 힌트는 인덱스의 기본 순서를 변경하지 않으므로 INDEX 힌트 이상을 지정하지 않습니다.
    2. 그러나 기본 동작이 변경되면 INDEX_ASC 힌트를 사용하여 오름차순 범위 스캔을 명시 적으로 지정할 수 있습니다.

3.2.18 INDEX_COMBINE

index_combine_hint.gif

/*+ INDEX_COMBINE (table [index] [index] ... ) +/

  • 해당 테이블에 Bitmap 인덱스의 존재 시, Bitmap 인덱스를 통한 액세스를 유도합니다.
  • 힌트 내에 인덱스의 이름이 쓰여지지 않을 시, 해당 인덱스의 Boolean 값을 사용하여 최적의 Cost를 산출하여 실행하게 됩니다.
  1. INDEX_COMBINE 힌트는 옵티마이저가 테이블에 비트 맵 액세스 경로를 사용하도록 지시합니다.
  2. INDEX_COMBINE 힌트에서 indexspec을 생략하면 옵티마이저는 테이블에 대한 최적의 비용 추정치가있는 인덱스의 Boolean 콤비네이션을 사용합니다.
  3. indexspec을 지정하면 옵티마이저는 지정된 인덱스의 일부 Boolean 콤비네이션을 사용하려고합니다.
  4. 각 매개 변수는 "INDEX Hint"에서와 동일한 용도로 사용됩니다. 예를 들면 :
SELECT /*+ INDEX_COMBINE(e emp_manager_ix emp_department_ix) */ *
  FROM employees e
  WHERE manager_id = 108
     OR department_id = 110;

3.2.19 INDEX_DESC

/*+ INDEX_DESC (table [index] [index] ... ) +/

  • 지정한 인덱스에 대해 인덱스 스캔을 역순으로 실행합니다.
  • 해당 쿼리가 인덱스 범위 스캔의 사용 시, 인덱스 컬럼의 값을 사용하여 역순으로 실행합니다.
  • 파티션 인덱스에서는 파티션 별 개별적인 실행이 이루어집니다.

3.2.20 INDEX_FFS

index_ffs_hint.gif

/*+ INDEX_FFS (table [index] [index] ... ) +/ /*+ NO_INDEX_FFS */

  • Full 테이블 스캔 대신에 빠른 인덱스 패스트 풀스캔의 실행을 유도합니다.
  • 멀티 블럭 I/O로 처리함
  • 고려할 사항
 인덱스 Fast Full Scan은 인덱스에 의해 정렬된 데이터가 추출되지 않는다.
 인덱스 Fast Full Scan만  병렬 프로세싱이 가능하다.
  • 언제 사용하는지?
 인덱스로만 원하는 데이터를 모두 추출하는 경우
 해당 테이블의 데이터 중 대부분을 추출하는 경우
  • BETWEEN,부등호 연산, COUNT, MAX, MIN 질의를 할 때 성능상의 이점
  • 정렬이 불필요한 경우

3.2.21 INDEX_JOIN

index_join_hint.gif

  1. INDEX_JOIN 힌트는 옵티마이저에게 인덱스 조인을 액세스 경로로 사용하도록 지시합니다.
  2. 힌트가 긍정적인 효과를 내려면 쿼리를 해결하는 데 필요한 모든 열을 포함하는 충분히 적은 수의 인덱스가 있어야합니다.
  3. 각 매개 변수는 "INDEX Hint"에서와 동일한 용도로 사용됩니다.
    1. 예를 들어 다음 쿼리는 인덱스 조인을 사용하여 manager_id 및 department_id 열에 액세스합니다. 두 열은 모두 employee 테이블에 인덱싱됩니다.
SELECT /*+ INDEX_JOIN(e emp_manager_ix emp_department_ix) */ department_id
  FROM employees e
  WHERE manager_id < 110
    AND department_id < 50;

3.2.22 INDEX_SS

index_ss_hint.gif

  1. INDEX_SS 힌트는 지정된 테이블에 대해 인덱스 건너 뛰기 스캔을 수행하도록 최적화 프로그램에 지시합니다.
  2. 명령문이 인덱스 range scan을 사용하는 경우 Oracle은 인덱스 값의 오름차순으로 인덱스 항목을 스캔합니다.
  3. 파티션된 인덱스에서 결과는 각 파티션 내에서 오름차순으로 표시됩니다.
SELECT /*+ INDEX_SS(e emp_name_ix) */ last_name
  FROM employees e
  WHERE first_name = 'Steven';


3.2.23 INDEX_SS_ASC

index_ss_asc_hint.gif


3.2.24 INDEX_SS_DESC

index_ss_desc_hint.gif

3.2.25 LEADING_HINT

merge_hint.gif

/*+ LEADING (table) +/

  • 테이블 간의 조인 시에 지정한 테이블을 먼저 수행하도록 유도합니다.
  • 두 개 이상의 LEADING 힌트의 사용 시, 힌트 자체가 사용되어 지지 않습니다.
  • ORDERED 힌트와 더불어 사용시, LEADING 힌트는 사용되지 않습니다.
  1. LEADING 힌트는 지정된 테이블이 조인 그래프의 종속성으로 인해 지정된 순서대로 먼저 조인 될 수없는 경우 무시됩니다.
  2. 두 개 이상의 충돌하는 LEADING 힌트를 지정하면 모두 무시됩니다.
  3. ORDERED 힌트를 지정하면 모든 LEADING 힌트를 재정의합니다.

3.2.26 MERGE

merge_hint.gif

/*+ MERGE (table) +/

  • 뷰/인라인뷰를 해체 하여 메인 쿼리랑 합쳐라. NO_MERGE는 뷰/인라인뷰를 해체하지 말고 합치지 말아라.
  • 각 쿼리의 결과값을 머지합니다.
  • 해당 쿼리 내에 GROUP BY 절의 사용 이나 SELECT 구문에 DISTINCT 가 사용되었을 시, 머지의 실행이 가능할 경우에만 힌트가 실행됩니다.
  • IN 과 서브 쿼리의 사용 시, 서브 쿼리와 상위 쿼리 간의 상호 관계가 없을 때에만 머지의 실행이 가능합니다.
  • 이 힌트는 Cost-based 가 아닙니다. 따라서 액세스하는 실행 쿼리 블럭에 MERGE 힌트가 반드시 명시되어야만 합니다. 그렇지 않을 경우 옵티마이저는 다른 실행 계획을 수립합니다.
SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salary
   FROM employees e1,
   (SELECT department_id, avg(salary) avg_salary 
      FROM employees e2
      GROUP BY department_id) v 
   WHERE e1.department_id = v.department_id AND e1.salary > v.avg_salary;
  1. 뷰의 쿼리 블록이 SELECT 목록에 GROUP BY 절 또는 DISTINCT 연산자를 포함하는 경우 옵티마이 저는 복합 뷰 병합이 활성화 된 경우에만 뷰를 액세스 문에 병합 할 수 있습니다.
    1. 복합 병합은 하위 쿼리가 상관되지 않은 경우 IN 하위 쿼리를 액세스 문에 병합하는 데 사용할 수도 있습니다.

3.2.27 MERGE_AJ

            HASH_AJ 를 참조하십시요.

3.2.28 MERGE_SJ

            HASH_AJ 를 참조하십시요.

3.2.29 NL_AJ

EXISTS나 IN조건 사용시 서브쿼리에 UNNEST(중첩해제) 와 함께 NL_SJ힌트를 사용하면, NESTED LOOPS JOIN SEMI로 처리되도록 유도

3.2.30 NL_SJ

            HASH_AJ 를 참조하십시요.

3.2.31 NOAPPEND

/*+ NOAPPEND +/

  • 병럴 모드에서의 INSERT 작업을 Conventional 방식으로 수행합니다.
  • 병렬 모드에서는 Direct-path INSERT 가, 직렬 모드에서는 Conventional INSERT가 기본값입니다.

3.2.32 NOCACHE

/*+ NOCACHE (table) +/

  • 풀 테이블 스캔의 사용 시, 테이블에서 읽어온 블럭을 버퍼의 LRU 리스트 의 LRU 쪽에 위치시킵니다. 기본 모드입니다.

3.2.33 NO_EXPAND

/*+ NO_EXPAND +/

  • 실행 쿼리 내에 OR 나 WHERE 절의 IN 이 사용되었을 시, Cost-Based 옵티마이저가 쿼리 처리를위해 OR 를 사용한 확장을 사용하는 것을 방지합니다.
  • 일반적으로 옵티마이저는 위와 같은 경우 OR – 확장의 가격이 확장을 사용하지 않는 것보다 적을 시, 확장 방식으로 수행합니다.
  • OR 조건이나 IN List등을 사용할 때 OR확장 (Concatenation등을)을 막는 것인데 플랜에서 Concatenation 발생시 사용하는 힌트. OR를 UNION-ALL로 풀지 말아라
  • 반대는 USE_CONCAT 힌트 : OR를 UNION-ALL로 풀어라

3.2.34 NO_FACT

/*+ NO_FACT (table) +/

  • Star 변형 시, 해당 테이블의 FACT 테이블로서의 사용을 방지합니다.

3.2.35 NO_INDEX

/*+ NO_INDEX (table [index] [index] ... ) +/

  • 지정 테이블의 인덱스 사용을 방지합니다.

3.2.36 NO_MERGE

no_merge_hint.gif

/*+ NO_MERGE (table) +/

  • 메인 퀴리와 merge처리 방지
  • 메인(외부) 쿼리와 서브(인라인 뷰) 쿼리를 단일 쿼리로 merge(결합)하지 않도록 옵티마이저에 지시
  • 뷰에 액세스하는 방식에 영향.
  1. 예로 seattle_dept보기가 병합되지 않도록합니다. :
SELECT /*+NO_MERGE(seattle_dept)*/ e1.last_name, seattle_dept.department_name 
  FROM employees e1, 
    (SELECT location_id, department_id, department_name 
       FROM departments 
       WHERE location_id = 1700) seattle_dept 
  WHERE e1.department_id = seattle_dept.department_id;
  1. (인라인) 뷰 쿼리 블록에서 NO_MERGE 힌트를 사용하는 경우 인수없이 지정
  2. 메인(외부) 쿼리에 NO_MERGE를 지정하는 경우 뷰 이름을 인수로 지정

3.2.37 NOPARALLEL

/*+ NOPARALLEL (table) +/

  • 지정한 테이블의 병렬 처리를 방지합니다.
  • 테이블의 지정된 PARALLEL 값에 대해서 우선권을 가집니다.
  • 중첩 테이블에 대해서는 병렬 처리를 할 수 없습니다.

3.2.38 NOPARALLEL_INDEX

/*+ NOPARALLEL_INDEX (table [index] [index] ... ) +/

  • 인덱스 스캔 작업의 병렬 처리를 방지합니다.
  • 인덱스에 지정된 PARALLEL 값에 우선권을 가집니다.

3.2.39 NO_PUSH_PRED

/*+ NO_PUSH_PRED (table) +/

  • 결과값에 대한 조인 방식 서술의 강제적 수행을 방지합니다.
  1. (인라인)뷰에 메인 조인조건을 미리 넣어 수행하는것을 금지

3.2.40 NO_PUSH_SUBQ

/*+ NO_PUSH_SUBQ +/

  • 서브 쿼리의 결과값을 머지하지 않는 실행 계획이 실행 계획 설립 단계에서 제일 마지막으로 참조되는 것을 방지합니다.
  • 일반적으로 서브 쿼리의 Cost 가 높거나, 처리 로우의 갯수를 크게 줄여주지 못할 때에는 서브 쿼리를 마지막에 참조하는 것이 성능 향상에 도움이 됩니다.


3.2.41 NO_QUERY_TRANSFORMATION

/*+ NO_QUERY_TRANSFORMATION */

  1. Query Transformation을 수행하지 않도록 지정한다.
  2. View Merging, Subquery Unnesting, Push Predicate, Constraint, Star Transformation 등에 의한 Query Transformation이 발생하지 않도록 할 수 있다.
    1. NO_QUERY_TRANSFORMATION 힌트는 OR 확장, 뷰 병합, 하위 쿼리 중첩 해제, 스타 변환 및 구체화 된 뷰 재작성을 포함하되 이에 국한되지 않는 모든 쿼리 변환을 건너 뛰도록 옵티마이저에 지시합니다.
SELECT /*+ NO_QUERY_TRANSFORMATION */ employee_id, last_name
  FROM (SELECT *
        FROM employees e) v
  WHERE v.last_name = 'Smith';

3.2.42 NO_REWRITE

/*+ NO_REWRITE +/

  • 해당 쿼리 블럭의 쿼리 재생성의 실행을 방지합니다.
  • QUERY_REWRITE_ENALBE 파라미터에 대해 우선권을 가집니다.
  • NOREWRITE 힌트의 사용 시, Function-Based 인덱스의 사용이 금지됩니다.(NOREWRITE 힌트는 더이상 사용 안함 11g 이상 NO_REWRITE)
  1. NO_REWRITE 힌트는 QUERY_REWRITE_ENABLED 매개 변수의 설정을 재정 의하여 쿼리 블록에 대한 쿼리 다시 쓰기를 비활성화하도록 최적화 프로그램에 지시합니다.

3.2.43 NO_UNNEST

/*+ NO_UNNEST +/

  • 뷰/서브쿼리를 해체하지 말아라.
  • Filter 동작 방식으로 수행하도록 제어.
    • PUSH_SUBQ : 서브 쿼리 부터 수행 해라
    • NO_PUSH_SUBQ : 메인 쿼리 부터 수행해라.

3.2.44 ORDERED

/*+ ORDERED +/

  • FROM 절에 나열된 테이블의 순서대로 조인 작업을 실행.
  1. ORDERED 힌트는 Oracle이 FROM 절에 나타나는 순서대로 테이블을 조인하도록 지시. Oracle은 ORDERED 힌트보다 다재다능한 LEADING 힌트를 사용할 것을 권장합니다.
  2. 조인이 필요한 SQL 문에서 ORDERED 힌트를 생략하면 옵티마이 저는 테이블을 조인 할 순서를 선택합니다.
  3. 옵티마이저가 각 테이블에서 선택한 행 수에 대해 알지 못하는 것을 알고있는 경우 ORDERED 힌트를 사용하여 조인 순서를 지정할 수 있습니다. 이러한 정보를 사용하면 최적화 프로그램보다 내부 및 외부 테이블을 더 잘 선택할 수 있습니다.
  4. 서브쿼리가 존재한다면 서브쿼리가 가장 먼저 수행됨.

3.2.45 ORDERED_PREDICATE

/*+ ORDERED_PREDICATE +/

  • 옵티마이저에 의한 조인 관계의 Cost를 산출하기 위해 미리 정해둔 조인 관계 별 실행 순서의 사용을 방지합니다.

n 인덱스 키를 사용한 조인 관계들은 제외됩니다.

  • 이 힌트는 쿼리의 WHERE 절에 사용하십시요.

3.2.46 OPT_PARAM

  1. OPT_PARAM 힌트를 사용하면 현재 쿼리 기간 동안 만 초기화 매개 변수를 설정할 수 있습니다.
  2. 이 힌트는 OPTIMIZER_DYNAMIC_SAMPLING, OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_SECURE_VIEW_MERGING 및 STAR_TRANSFORMATION_ENABLED 매개 변수에만 유효합니다.
  • STAR_TRANSFORMATION_ENABLED 매개 변수를 TRUE로 설정합니다.
SELECT /*+ OPT_PARAM('star_transformation_enabled' 'true') */ * FROM ... ;
  • bypass_ujvc 힌트사용시 ORA-00600: 내부 오류 코드, 인수 : [qctstc2o1], [1], [0], [0], [1], [0], [0], [] 발생하면 사용
/*+ opt_param('_optimizer_cost_based_transformation', 'off') bypass_ujvc */

3.2.47 PARALLEL

/*+ PARALLEL (table [ [, n |, DEFAULT |, ] [, n | DEFAULT ] ] ) +/

  • 병렬 처리에 사용될 서버 프로세스의 갯수를 설정.
  • 병렬 처리 조건에 위배될 시, 힌트는 적용되지 않음.
  • TEMP테이블에 대한 PARALLEL_HINT 사용 시에는 적용되지 않음.


3.2.48 PARALLEL_INDEX

/*+ PARALLEL_INDEX (table [ [index] [, index]...] [ [, n |, DEFAULT |, ] [, n | DEFAULT ] ] ) +/

  • 파티션 인덱스의 인덱스 범위 스캔 작업의 병렬 처리에 할당될 서버 프로세스의 갯수를 지정합니다.

3.2.49 PQ_DISTRIBUTE

PQ_DISTRIBUTE.png


/*+ PQ_DISTRIBUTE (table [,] outer_distribution, inner_distribution) +/

  • 병렬 조인 시, Producer 프로세스와 Consumer 프로세스 간의 데이터 전달 방식을 지정합니다.
  1. 용도
    1. 옵티마이져가 파티션된 테이블을 적절히 활용하지 못하고 동적 재분할을 시도할 때
    2. 기존 파티션 키를 무시하고 다른 키 값으로 동작 재분할하고 싶을 때
    3. 통계정보가 부정확하거나 통계정보를 제공하기 어려운 상황에서 실행계획을 고정시고정시 할 때
    4. 기타 여러 가지 이유로 데이터 분배 방식을 변경하고 자 할 때
  2. 구문
    1. 분배방식 지정
  • pq_distribute(inner, none, none)
    • Full-parition Wise 조인으로 유도할 때 사용.양쪽 테이블 모두 조인 컬럼에 대해 같은 기준으로 파티션되어 있어야 작동
    • 각 병렬 쿼리 서버는 각 테이블에서 하나씩 일치하는 한 쌍의 파티션 간에 조인 작업을 수행
    • 반드시 두 테이블 모두 조인 키에 동등하게 분할되어야 함.


  • pq_distribute(inner, partition, none)
    • 내부 테이블의 파티션을 사용하여 외부 테이블의 행을 매핑
    • 내부 테이블은 조인 키로 분할되어야 함
    • 이 힌트는 외부 테이블의 파티션 수가 병렬 쿼리 서버 수의 배수와 같거나 거의 같을 때 권장
  • pq_distribute(inner, none, partition)
    • 외부 테이블의 파티션을 사용하여 내부 테이블의 행을 매핑
    • 외부 테이블은 조인 키에 분할되어야 함
    • 이 힌트는 외부 테이블의 파티션 수가 쿼리 서버 수의 배수와 같거나 거의 같을 때 권장
  • pq_distribute(onner, hash, hash)
    • 조인키의 해시 함수를 사용하여 각 테이블의 행을 소비자 병렬 쿼리 서버에 매핑.
    • 매핑이 완료되면, 각 쿼리 서버는 결과 파티션 쌍 사이에 조인을 수행
    • 테이블의 크기가 비슷하고 조인 작업이 해시 조인 또는 정렬 병합 조인으로 구현될 때 권장
  • pq_distribute(inner, broadcast, none)
    • 외부 테이블의 모든 행이 각 병렬 쿼리 서버로 브로드캐스트되고 내부 테이블 행은 무작위로 분할되도록 함
    • 이 힌트는 외부 테이블이 내부 테이블에 비해 매우 작을 때 권장
  • pq_distribute(inner, none, broadcast)
    • 내부 테이블의 모든 행을 각 소비자 병렬 쿼리 서버로 브로드캐스트하도록 강요함
    • 외부 테이블 행은 무작위로 분할됩니다.
    • 내부 테이블이 외부 테이블에 비해 매우 작을 때 권장

3.2.49.1 가능한 조합

  1. HASH - HASH : OUTER, INNER 크기가 비슷할 때
  2. BROADCAST - NONE : OUTER 테이블이 작을때
  3. NONE - BROADCAST : INNER 테이블이 작을때
  4. PARTITION - NONE : INNER 테이블 파티션 기준으로 OUTER 테이블을 파티션 하여 분배
  5. NONE - PARTITION : OUTER 테이블 파티션 기준으로 INNER 테이블을 파티션 하여 분배
  6. NONE - NONE : 두 테이블이 조인컬럼 기준으로 파티션 되어 있을때

3.2.50 PUSH_PRED

/*+ PUSH_PRED (table) +/ /*+ NO_PUSH_PRED */

  1. The PUSH_PRED hint forces pushing of a join predicate into the view.
  • 결과값에 대한 조인 방식 서술의 강제적 수행을 실행합니다.
  • 메인쿼리에서 서브쿼리를 제어
  • 메인쿼리에 기술
  • 조인조건을 인라인뷰 안으로 PUSH 하는 힌트


SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */ *
   FROM employees e,
            ( SELECT manager_id
              FROM employees
            ) v
 WHERE e.manager_id = v.manager_id(+)
     AND e.employee_id = 100;

3.2.51 PUSH_SUBQ

/*+ PUSH_SUBQ +/ /*+ NO_PUSH_SUBQ */

  1. The PUSH_SUBQ hint causes non-merged subqueries to be evaluated at the earliest possible place in the execution plan. Generally, subqueries that are not merged are executed as the last step in the execution plan. If the subquery is relatively inexpensive and reduces the number of rows significantly, then it improves performance to evaluate the subquery earlier.
    1. PUSH_SUBQ 힌트는 병합되지 않은 서브쿼리가 실행 계획의 가능한 가장 빠른 위치에서 평가되도록 합니다.
    2. 일반적으로 병합되지 않은 서브쿼리는 실행 계획의 마지막 단계로 실행됩니다.
    3. 서브쿼리가 상대적으로 저렴하고 행의 수를 크게 줄이면 서브쿼리를 더 일찍 평가하는 것이 성능을 향상시킨다.
  • unnest되지 않은 subquery를 최대한 빠르게 수행하여 main table이 다른 테이블과 조인하기 전에 필터하여 최대한 건수를 줄여줄 필요가 있을 때 사용하는 힌트.
  • 서브쿼리 조인조건을 메인쿼리에 먼저 조인처리
  • no_unnest 힌트와 같이 사용.
  • 실행 계획에서 가능한 빠른 단계에서 노머지(nomerge)된 서브 쿼리를 평가하기 위해 옵티마이저에 지시
  • 머지가 불가능한 서브 쿼리들의 우선 실행 계획을 실행 계획 수립시 먼저 참조하도록 지시
  • 서브 쿼리가 Remote 테이블,머지 조인의 사용 시 힌트는 실행되지 않음.
  • unnest란 subquery가 풀려서 main query와 조인 되는 것.
  • 작성자가 subquery 형태로 작성하더라도 optimizer는 우선적으로 unnest형태로 쿼리변환 시도.
  • unnest되지 않은 subquery를 조인형태로 풀어주게 되면 다양한 테이블 조인을 구상할 수 있게 되기 때문에 되도록 unnest를 하려고 하는 것.
  • 따라서 optimizer가 unnest 시도하지 않도록 no_unnest 힌트와 같이 사용되는 것.

3.2.52 PULL_PRED

/*+ PULL_PRED(inline_view_alias) */ /*+ NO_PULL_PRED(inline_view_alias) */ => _optimizer_filter_pred_pullup = true;

  • use cost-based flter predicate pull up transformation
  • inline view 안에 있는 비싼 filter 조건을 inline view 밖으로 pull out.
  • inline view 가 merge 되지 않아야 하고(주로 group by inline view) inline view 밖에 ROWNUM 조건이 있어야 가능하다고 함.
  • 인라인뷰 내 여러 조건 중 비용이 비싸지만 Filter 효과가 거의 없는 경우 해당 조건을 인라인뷰 바깥으로 빼는 경우
  • 제약조건
  1. 인라인뷰 필수
  2. 인라인뷰 바깥 쪽에서 ROWNUM 사용
  3. View Merging 이 없어야 함 (아래 예제1 에서는 GROUP BY + ORDER BY 가 막아주는 역할을 함)
  4. 비용이 많이 드는 Filter 필수 (서브쿼리 내 집합함수, 사용자 정의함수)

3.2.53 REWRITE

/*+ REWRITE [ ( [materialized_view] [materialized_view]...) ] +/

  • 실행 계획의 가격에 상관없이 Materialized View 를 사용하여 쿼리 재생성을 하도록 합니다.
  • Materialized View 를 지정할 시, 지정한 Materialized View 의 가격에 상관없이 무조건 쿼리 재생성을 실행합니다.
  • Materialized View 를 지정하지 않을 시, 오라클은 사용 가능한 모든 Materialized View 를 참조하여 그 중 가장 가격이 낮은 Materialized View 를 사용하여 쿼리 재생성을 합니다.
  • Materialized View 를 지정하지 않는 힌트의 사용이 권장됩니다.

3.2.54 ROW_ID

/*+ ROWID (table) +/

  • 지정한 테이블의 스캔을 ROWID 방식으로 수행하게 합니다.

3.2.55 RULE

/*+ RULE +/

  • 실행 계획을 Rule-Based 방식으로 실행하게 합니다.
  • 해당 쿼리 블럭에 다른 힌트 또한 사용되었을 경우, 다른 힌트들은 사용되지 않습니다.

3.2.56 STAR

/*+ STAR +/

  • Star 쿼리 계획이 사용 가능하다면, 실행하게 합니다.
  • Star 쿼리 계획이란 가장 큰 테이블이 마지막 순서로 조인되며, 조인될 시 가장 큰 테이블 내의 Concatenated 인덱스에 대해 Nested Loop 조인 방식으로 실행되는 것을 말합니다.
  • 최소한 세개 이상의 테이블이 사용되며, 제일 큰 테이블의 Concatenated 인덱스의 생성에 최소한 세 개 이상의 컬럼이 사용되어야 하며, 액세스나 조인 방식에 충돌이 없어야만 이 힌트는 사용됩니다.

3.2.57 STAR_TRANSFORMATION

/*+ STAR_TRANSFORMATION +/

  • 옵티마이저가 Star 변형 작업에 최적화된 실행 계획을 수립, 실행하도록 합니다.
  • 힌트를 사용하지 않을 시, 옵티마이저는 일반적인 작업에 최적화된 실행 계획을 수행합니다.
  • 힌트를 사용하였어도 변형 작업에 맞추어진 실행 계획을 실행한다는 보장은 없습니다. 다른 일반적인 힌트의 사용과 마찬가지로 비교 분석 후, 오라클의 판단에 따라 다른 실행 계획이 실행될 수 있습니다.

/*+ SEMIJOIN_DRIVER(@SUB) */ => Star transformation 효과를 낼 수 있는 힌트.

  • 크기가 작은 디멘전 테이블과 팩트테이블의 비트맵(또는 비트리)인덱스를 먼저 조인한 후 그 결과값을 가지고

팩트테이블을 액세스하는 원리.

  • 루프조인의 낮은 성능문제와 해시조인시 팩트테이블을 full scan 하는 문제 두가지를 모두 해결함.
  • _b_tree_bitmap_plans=true 이면 B* tree 인덱스에서도 가능함.
  • Star transformation 과 달리 바인드변수 사용시에도 가능

3.2.58 UNNEST

/*+ UNNEST */ /*+ NO_UNNEST */

Setting the UNNEST_SUBQUERY session parameter to TRUE enables subquery unnesting. Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the optimizer to consider them together when evaluating access paths and joins.

UNNEST_SUBQUERY 세션 매개변수를 TRUE로 설정하면 하위 쿼리 중첩 해제가 활성화됩니다. 하위 쿼리는 중첩을 해제하고 하위 쿼리 본문을 포함하는 명령문의 본문에 병합하여 옵티마이저가 액세스 경로 및 조인을 평가할 때 이들을 함께 고려할 수 있도록 합니다.

  1. UNNEST는 뷰/서브쿼리를 해체한다. NO_UNNEST는 반대로 뷰/서브쿼리를 해체하지 말아라.
  2. 서브쿼리를 메인쿼리로 몰아 내서 조인토록 유도하는 하는 힌트, 즉 중첩(NEST)을 풀어서(UNNEST) 해제 하는 힌트 임.
  3. FILTER 동작방식을 선택하지 않고 조인 동작방식으로 처리하고자 할 때. 서브쿼리에 UNNEST 힌트 사용
  4. 하위쿼리(Sub Query)는 상위 쿼리문의 WHERE 절에 나타날 때 중첩 됨.
    1. Oracle Database가 중첩된 하위 쿼리가있는 문을 측정(평가)할 때 하위 쿼리 부분을 여러 번 평가해야하며 일부 효율적인 액세스 경로 또는 조인을 간과 할 수 있습니다.
  5. 서브쿼리 중첩해제(SubQuery UNNEST)는 서브쿼리의 본문을 포함하는 명령문의 본문에 중첩해제(Unnest) 와 병합(Merge) 하여 옵티마이저가 액세스 경로 및 조인을 평가할 때 함께 고려할 수 있도록합니다.

3.2.58.1 옵티마이저가 중첩해제를 못하는 경우

  1. ROWNUM 가상컬럼
  2. 집합 연산자 중 하나
  3. 중첩집계함수,서브쿼리의 외부(바깥) 쿼리 블록이 아닌 쿼리블록에 상호연관함수가 포함 된 계층적 서브쿼리 및 서브쿼리가 포함됩니다.

3.2.58.2 옵티마이저가 자동 중첩해제(UNNEST) 하는 경우

  1. 연관관계가 없는 IN 절의 하위 쿼리
  2. IN 과 EXISTS 의 연관된 서브쿼리, 집계 함수 또는 GROUP BY 절을 포함하지 않는 경우
  3. 옵티마이저에 추가서브쿼리 유형의 중첩을 해제하도록 지시하여 확장 서브쿼리 중첩해제를 활성화 할 수 있습니다.
    1. 서브쿼리에 HASH_AJ 또는 MERGE_AJ 힌트를 지정하여 상관되지 않은 NOT IN 서브쿼리의 중첩을 해제 할 수 있음.
    2. 서브쿼리에 UNNEST 힌트를 지정하여 다른 서브쿼리를 중첩 해제 할 수 있습니다.
  • 반대로 NO_UNNEST 힌트는 기존 서브쿼리 형태를 유지 하고 필터방식으로 실행계획이 수립되도록 하는 힌트임.
  • WHERE절에 사용되는 서브쿼리를 중첩 서브쿼리(Nested Subquery)라고 하며 IN, EXISTS 관계없이 메인쿼리에서 읽히는 FILTER방식으로 처리되어 메인레코드 하나 읽을 때마다 서브쿼리를 반복적으로 수행하면서 조건에 맞는 데이터를 추출하는 것이다.
  • 이러한 필터방식이 최적의 성능을 보장하지 않으므로 옵티마이저는 조인문으로 변경후 최적화(Unnesting) 하거나 메인과 서브쿼리를 별도의 SUB PLAN으로 분리하여 각각 최적화를 수행하는데 이때 서브쿼리에 FILTER 연산이 나타난다.
  • 서브 쿼리를 Unnesting 하지 않는다면 메인쿼리의 건 마다 서브쿼리를 반복 수행하는 FILTER 연산자를 사용하기에 Unnesting 힌트는 효율적으로 사용한다면 성능 향상을 가져온다.

/*+ UNNEST +/

  • 서브 쿼리에 기술
  • 서브 쿼리 블럭에 대해 인증성 만을 검사하게 합니다.
  • 인증이 되었다면 그 이상의 검증 작업없이 서브쿼리에 대한 UNNESTING 의 설정을 가능하게 합니다.
select *
  from emp
  where deptno in (
                    select /*+ unnest */ deptno
                    from dept
                  );

/*+ NO_UNNEST +/

  • 기존 서브쿼리 형태를 유지 하고 필터방식으로 실행계획이 수립

3.2.58.3 UNNEST 같이 쓰는 힌트

  1. SWAP_JOIN_INPUTS : 해쉬테이블로 올릴 테이블 지정
  2. NO_SWAP_JOIN_INPUTS : 해쉬테이블로 올리지 않을 테이블 지정
  3. NL_SJ : NL SEMI JOIN 으로 수행 되도록 지시
  4. HASH_SJ SWAP_JOIN_INPUTS : HASH SEMI JOIN 으로 수행 되도록 지시 하고 서브쿼리를 먼저수행(해시테이블로) 되도록 지시함.
  5. HASH_SJ NO_SWAP_JOIN_INPUTS : HASH SEMI JOIN 으로 수행 되도록 지시 하고 서브쿼리를 먼저수행(해시테이블로) 되지 않도록 지시함.
  6. NL_AJ : NOT EXISTS 쿼리를 NL JOIN ANTI 로 수행 되도록 함.
  7. HASH_AJ : NOT EXISTS 쿼리를 HASH JOIN ANTI 로 수행 되도록 함.

3.2.59 USE_CONCAT

/*+ USE_CONCAT +/

  • WHERE 절의 IN 이나 OR 조인 을 UNION ALL 로 변경하여 수행하게 합니다.
  • USE_CONCAT(@"MAIN" 8) : Inlist 를 사용할수 있는 경우에는 Union All 로 분리하지 말것을 강제하는 힌트
  • USE_CONCAT(@"MAIN" 1) : Inlist 를 사용할수 있는 경우에는 가능한 Union All 로 분리하라는 힌트
  • 일반적으로 이러한 변경은 결과값의 병합 수행의 가격이 수행하지 않을 시의 가격 보다 낮을 때에만 실행됩니다.

3.2.60 USE_HASH

/*+ USE_HASH (table [table]...) +/ /*+ NO_USE_HASH */

  • Hash 조인 방식으로 각 테이블을 조인.
  • EQUAL JOIN 에서만 가능
  • 작은테이블과 큰테이블 조인시 작은테이블이 드라이빙 테이블에 좋음
  • ORDERED힌트를 사용하여 조인순서 조정하면 좋음
  • HASH AREA SIZE 확인: 작은테입블사이즈 * 1.6 적당
  • 각 테이블 에 대해 1 번만 찾음,NL 조인이 이너테이블을 여러번 중복으로 탐색하는것과는 다름.
  • ALTER SESSION SET HASH_AREA_SIZE= 104857600; -* 세션에 해쉬 사이즈 증가로 속도 개선(배치,ETL처리시)


3.2.61 USE_MERGE

/*+ USE_MERGE (table [table]...) +/ /*+ NO_USE_MERGE */

  • Sort-Merge 방식으로 각 테이블을 조인하게 합니다.
  • 참조테이블이 아우터/드라이빙 테이블 이면 적용되지 않음
  • LEADING and ORDERED 와 함께 사용할것을 권장함
  • 메모리 필요,SORT_AREA_SIZE 비용이 필요
  • 양쪽 테이블 이 모두 sort 한후 merge,별도로 SORT 할필요 없음
  • 넓은범위처리
  • 환경설정
       ALTER SESSION SET SORT_AREA_SIZE= 104857600;
   ALTER SESSION SET SORT_AREA_RETAINED_SIZE= 104857600; (같이 준다)
   ALTER SESSION SET SORT_MULTIBLOCK_READ_COUNT=128;
  • 정렬 메모리의 크기
       (= Target rows×(total selected column’s bytes) ×2) 이상
       PGA Memory Allocation Error가 발생하지 않는 범위


3.2.62 USE_NL

/*+ USE_NL (table [table]...) +/ /*+ NO_USE_NL */

  • Nested-Loop 방식으로 각 테이블을 조인.
  • LEADING and ORDERED 와 함께 사용할것을 권장함
  • 참조테이블이 OUTER 테이블이면 힌트는 무시 됨.
  • 드라이빙 테이블이 중요함
  • CPU 사용하지 않음
  • join시 이너테이블로 강제로 지정해버림(중첩되도록)
  • 드라이빙 테이블은 조회조건으로 집합구성(WHERE절의 관련 컬럼의 인덱스가 중요)
  • 드라이빙 집합으로 후행테이블이 조인키+조회조건 인덱스를 탄다
  • 두테이블에 적용되는 인덱스에 따라 자동 정렬

3.3 참조

3.3.1 쿼리블럭( Query Block )

  1. 여러 힌트에 선택적 쿼리 블록 이름을 지정하여 힌트가 적용되는 쿼리 블록을 지정할 수 있습니다.
  2. 이 구문을 사용하면 인라인뷰에 적용되는 힌트를 외부 쿼리에 지정할 수 있습니다.
  3. 쿼리 블록 인자의 구문은 @queryblock 형식입니다.
  4. 여기서 queryblock은 쿼리에서 쿼리 블록을 지정하는 식별자입니다.
  5. 쿼리블록 식별자는 시스템생성 또는 사용자지정이 될 수 있습니다.
  6. 힌트가 적용되는 쿼리 블록 자체에 힌트를 지정할 때 @queryblock 구문을 생략합니다.
  7. 시스템 생성 식별자는 쿼리에 대해 EXPLAIN PLAN을 사용하여 얻을 수 있습니다.
    1. 변환 전 쿼리 블록 이름은 NO_QUERY_TRANSFORMATION 힌트를 사용하여 쿼리에 대해 EXPLAIN PLAN을 실행하여 확인할 수 있습니다. "NO_QUERY_TRANSFORMATION 힌트"를 참조하십시오.
    2. 사용자 지정 이름은 QB_NAME 힌트로 설정할 수 있습니다. "QB_NAME 힌트"를 참조하십시오.