ORACLE_012. Understanding Excution Plan

Understanding Excution Plan                                                                   

 SQL 구문을 수행하기 위해서 오라클을 많은 단계의 절차를 거칩니다. 어떠한 방법으로든 사용자가 구문을 입력하게 되면 각각의 단계에서는 데이터베이스에서 물리적으로 데이터의 행을 가져오거나 혹은 보낼 준비를 합니다. 각각의 단계를 조합하는 일은 오라클이 실행계획(Execution Plan)이란 구문을 통해 수행하게 됩니다. 실행계획은 구문(Statement)에 의해 읽혀지는 테이블의 접근경로와 Join Method 에서 Join 을 수행할 테이블의 순서를 포함합니다.

OVERVIEW OF EXPLAIN
 EXPLAIN PLAIN 이라는 SQL 구문을 통하여 옵티마이저가 선택한 실행계획을 확인할 수 있습니다. 구문이 입력되면, 옵티마이저는 실행계획을 선택하고 데이터베이스 테이블에 계획에 관한 자료를 삽입합니다. 간단하게 EXPLAIN PLAN 구문을 입력하고 출력할 테이블을 질의(Query) 하면 됩니다.

 다음은 EXPLAIN PLAN 구문의 기본적인 사용 방법 입니다.

 - UTLXPLAN.SQL Script 를 이용하여 스키마에 PLAN_TABLE을 만듭니다. 
 - SQL 구문의 처음에 EXPLAIN PLAN FOR 구문을 포함시킵니다.
 - EXPLAIN PLAN FOR 구문을 수행한후, 오라클이 제공하는 plan table을 보여주는 스크립트를
   사용합니다.
 - EXPLAIN PLAN의 출력 순서는 가장 가까운 쪽에서 먼쪽으로 출력됩니다. 각 라인은 다음 단계의
   부모 단계 입니다. 두 단계의 들여쓰기가 같다면, 상위 라인이 먼저 수행됩니다.

---

      NOTES:
               - 여기에서 EXPLAIN PLAN의 결과 출력은 utlxpls.sql 스크립트를 사용합니다.
               - 각 단계에서의 EXPLAIN PLAN의 출력 테이블은 각 시스템의 환경설정이나 옵티마이저에 따라
                 다르게 보여질 수 있습니다. 

---

 EXAMPLE : Using EXPLAIN PLAN
 EXPLAIN PLAN FOR
 SELECT e.employee_id,  j.job_title, e.salary, d.department_name
   FROM    employees e, jobs j, departments d
   WHERE e.employee_id < 103
        AND e.job_id = j.job_id
        AND e.department_id = d.department_id;

 EXAMPLE : Result of EXPLAIN PLAN Output

 

STEPS IN EXECUTION PLAN
 다음 단계들은 위 예제에서 데이터베이스의 오브젝트에서 물리적으로 데이터를 얻었습니다.
  - Step 3 에서 employees의 모든 행을 읽어들였습니다.
  - Step 5 에서 JOB_ID_PK 인덱스로 job_id를 찾고, jobs 테이블에서 rowid로 연관된 행을 찾습니다.
  - Step 4 에서는 Step 5에서 rowid로 행을 찾아냅니다.
  - Step 7 에서 DEPT_ID_PK 인덱스로 각각의 department_id를 찾고 departments 테이블에서 행과 연관된 rowid를
    찾습니다.
  - Step 6 에서 Step 7에서 획득한 rowid로 departments 테이블에서 행을 찾아냅니다.
 다음 단계들은 위 예제에서 그 전 행의 소스로 반환된 행을 다룹니다.
  - Step2는 employees, jobs 테이블에서 job_id를 이용하여 Nested loop을 수행하고 Step 3,4에서 반환한 행
    소스를 받아들이고, Step 3 소스를 Step 4에서 반환한 행과 Join 합니다.  그리고 결과를 Step 2에 반환 합니다.
  -  Step 1은 Nested loop을 수행하고 2와 6 단계의 소스를 받습니다. Step 2에서의 행을 Step 6의 행과 Join 하고,
     Step 1에거 그 결과 행을 반환합니다.


UNDERSTANDING EXECUTION ORDER
 실행계획의 단계는 위 예제에서 보여주는 것 처럼 순서대로 수행되는 것이 아닙니다. 오른쪽으로 가장 들여쓰기가 되어진 행을 먼저 수행합니다. 각각의 단계의 결과는 부모 단계에게 값을 전달합니다.  쉽게 이하히기 위해 다음 그림을 참고하세요.

       <Pic : Graphical View of SQL Explain Plan in SQL Scratchpad/www.oracle.com>

FIN
REF) Oracle Documents (Server .920)/a96533 "Introduction to the Optimizer"

 

실질적이고 객관적인 동화 - 아깜없이 주는 나무-

 

 

 

 

 

 

ORACLE_011. OPTIMIZER - Understanding the Cost-Based Optimizer

OPTIMIZER - Understanding the Cost-Based Optimizer                        

 CBO는 어떤 접근경로가 가장 효율적인지 결정합니다. 이 경로는 SQL문에 의해 선택되어지는 스키마 오브젝트의 통계에 기반하여 정보를 만들어 냅니다. 또한 CBO는 SQL구문에서 제안하는 HINT 절 역시 접근경로를 결정하는데 있어 하나의 고려대상입니다.

 

 CBO는 다음과 같은 순서로 진행합니다.

  1. 옵티마이저는 유효한 접근경로와 힌트절에 기반하여 SQL 구문을 수행할 수 있는 실행 가능한 계획을 생성합니다.
  2. 옵티마이저는 구문에 의해 접근되어질 테이블, 인덱스, 파티션의 통계에 기반하여 각각의 계획에 따른 비용을 계산합니다.

   비용이란 특정한 계획으로 구문을 수행할때 필요한 예상되어지는 리스소 사용량과 비례합니다.
   옵티마이저는 접근경로의 비용값과 I/O, CPU, Memory 사용값을 합쳐 계산하게 됩니다.

   높은 비용을 소요하는 시리얼 계획은 작은 비용을 소요하는 계획들에 비해 많은 시간이 소요됩
   니다. 하지만 어떤 특정한 계획을 수행할시 자원소모량과 수행시간은 직접적으로 연관되진 않습
   이다. 

  3. 옵티마이저는 이러한 계획들이 소모할 비용을 비교하고 가장 비용이 적게 들어가는 계획을 수행합니다.

COMPONENT OF THE CBO
 CBO는 다음 세가지의 주요 요소로 구성되어 있습니다.
 
  - Query Transformer
  - Estimator
  - Plan Generator

                 그림) CBO Components (www.oracle.com)

QUERY TRANSFORMER

 Query Transformer (이하 QT)로 들어오는 입력값들은 쿼리블럭셋으로 표현된 파싱된 쿼리 입니다.  쿼리블럭들은 독립적이거나 내부적으로 연관지어져 있습니다. 쿼리의 형식은 쿼리블럭들이 어떻게 내부적으로 연관되어 있는지 정의합니다. QT의 주 역할은 쿼리의 폼을 변형시켜 좀더 나은 실행계획을 세울 수 있는지 결정하는 것 입니다. 4개의 방법으로 이를 수행합니다.

 

View Merging  && Predicate Pushing && Subquery Unnesting && Query Rewrite with Materialized  View

 

Detail (English)

View Merging

  Each view referenced in a query is expanded by the parser into a separate query block. The query block essentially represents the view definition, and therefore the result of a view. One option for the optimizer is to analyze the view query block separately and generate a view subplan. The optimizer then processes the rest of the query by using the view subplan in the generation of an overall query plan. This technique usually leads to a suboptimal query plan, because the view is optimized separately from rest of the query.

  The query transformer then removes the potentially suboptimal plan by merging the view query block into the query block that contains the view. Most types of views are merged. When a view is merged, the query block representing the view is merged into the containing query block. Generating a subplan is no longer necessary, because the view query block is eliminated.

Predicate Pushing

  For those views that are not merged, the query transformer can push the relevant predicates from the containing query block into the view query block. This technique improves the subplan of the nonmerged view, because the pushed-in predicates can be used either to access indexes or to act as filters.

Subquery Unnesting

  Like a view, a subquery is represented by a separate query block. Because a subquery is nested within the main query or another subquery, the plan generator is constrained in trying out different possible plans before it finds a plan with the lowest cost. For this reason, the query plan produced might not be the optimal one. The restrictions due to the nesting of subqueries can be removed by unnesting the subqueries and converting them into joins. Most subqueries are unnested by the query transformer. For those subqueries that are not unnested, separate subplans are generated. To improve execution speed of the overall query plan, the subplans are ordered in an efficient manner.

Query Rewrite with Materialized Views

  A materialized view is like a query with a result that is materialized and stored in a table. When a user query is found compatible with the query associated with a materialized view, the user query can be rewritten in terms of the materialized view. This technique improves the execution of the user query, because most of the query result has been precomputed. The query transformer looks for any materialized views that are compatible with the user query and selects one or more materialized views to rewrite the user query. The use of materialized views to rewrite a query is cost-based. That is, the query is not rewritten if the plan generated without the materialized views has a lower cost than the plan generated with the materialized views.
 

 

ESTIMATOR
 ESTIMATOR는 Selectivity, Cardinality, Cost 세가지 방법으로 견적을 뽑아내는데 이 방법들은 서로간 정보를 전달하는 방식으로 연관되어 있습니다.

 

 Selectivity

 첫번째 측정자인 Selectivity는 Row set 에서 단편화된 행을 나타냅니다. Row set은 기본 테이블, 뷰 혹은 Group By 단서를 이용한 JOIN 구문의 결과들을 말합니다. Selectivity 는 last_name='Smith' 혹은 last_name='Smith' AND job_type='Clerk' 과 같은 쿼리의 술부로 엮여 있습니다. 술부는 Row set에서 수개의 행을 솎아내는 역할을 합니다. 따라서 술부의 Selectivity 는 술부 테스트에서 얼마나 많은 행이 Row set에서 통과가 되는지를 나타냅니다. Selectivity는 0.0 부터 1.0까지의 값을 가지는데 0.0은 Row set에서 선택되어지는 행이 하나도 없음을 뜻하고 1.0은 모든 행이 선택되어진다는 의미 입니다.

  Estimator 는 사용 가능한 통계정보가 없다면 Selectivity 의 내부 기본값을 사용합니다. 술부의 형식에 따라서 이 기본값은 다른 값을 이용합니다. 술부 등가식의 기본 내부값은 (ex. last_name='Smith')이 범위식(ex. last_name > 'Smith') 의 값보다 작습니다.  이는 Estimator가 등가식은 범위식보다 다 적은 수의 행을 반환한다고 생각하기 때문입니다.

  통계정보가 유효하다면 Selectivity를 계산하기 위해 이를 이용합니다. 예를들어 등가식의 경우 (last_name='Smith') last_name의 중복되는 n개의 행을 역수로 설정하는데,  이는 중복값 n의 범위 바깥에 있는 하나의 값만 가지고 있는 모든 행을 선택하기 때문입니다. last_name에 히스토그램이 유효하다면 중복값 대신 히스토그램을 사용합니다. 이 히스토그램은 컬럼의 중복되지 않은 값들에 대한 분산정도를 가지고 있어서 Selectivity를 계산하는데 더 좋은 이익을 가져다 줍니다. 넓은 범위의 중복된 많은 n개의 값(Skew data)을 가지고 있는 컬럼에 대한 히스토그램은 CBO를 생성하는데 좋은 selectivity 값을 계산해 냅니다.

 

 Cardinality
  Cardinality는 Row set에 몇개의 행이 있는지 나타냅니다. 여기에서 Row set이란 Table, View, Join, 혹은 Group by 구문을 이용한 질의 결과들의 모음을 말합니다.

 

  Base Cardinality

   기반 테이블의 행 갯수 입니다. Cardinality 의 기본값은 테이블의 통계 정보를 수집함으로써 얻을 수 있습니다. 테이블 통계정보가 존재하지 않으면 (혹은 유효하지 않다면) Estimator는 테이블이 사용하고 있는 Extent 의 갯수를 이용하여 Cardinality 의 기본값을 설정합니다.

  Effective Cardinality
   기반 테이블에서 선택된 행의 갯수 입니다. Effective Cardinality는 기반 테이블에서 각각의 술부가 성공적으로 필터링을 수행한 것과 더불어 기반 테이블의 각각의 컬럼에 정의되어 있는 술부에 따라 변합니다. Effective Cardinality는 기반 Cardinality를 생성하는 것 처럼 산출되어지고 테이블에 정의된 모든 술부의 Selectivity 를 섞습니다. 테이블에 술부가 존재하지 않을경우 Effective Cardinality 는 Base Cardinality 와 같습니다.

  Join Cardinality
   두개의 Row set이 서로 Join 하여 생긴 테이블의 행의 갯수 입니다. JOIN 은 두 Row set의 카르테시안 프로덕트(Cartesian product)와  함께 술부가 필터로서 결과를 생성하는 join 입니다. 따라서 Join Cardinality 는 join 구문의 Selectivity 에 의해 복합적으로 생성된 두 Row set의 Cardinalities 입니다.

  Distinct Cardinality
   Row set 에 존재하는 중복되는 행의 갯수 입니다. Row set 의 Distinct Cardinality 는 컬럼이 가지고 있는 데이터에 기반하고 있습니다. 예를들오 100개의 Row set중에 중복되는 값이 20개이면 Distinct Cardinality 는 20 입니다.

  Group Cardinality
   GROUP BY 명령이 수행된 후 생성된 Row set의 행 갯수 입니다.  GROUP BY 구문은 Row set의 행의 갯수를 줄이는데 효과적입니다. Group Cardinality는 각 그룹핑된 컬럼의 중복된 cardinality 와 Row set의 행 갯수에 따라 변합니다.

   Example of Group Cardinality
    중복되는 Cardinality 값이 20이고 colX를 Group by 로 묶은 100개의 행을 가진 Row set 이라면

    Group Cardinality 값은 30입니다.

    하지만 똑같이 colX와 colY로 그룹된 100개의 행을 가지고 있는 Row set에서 각각의 Distinct
    Cardinality 가 30 과 60이라고 가정해 봅시다. 이 경우 Group Cardinality 는 colX와 colY의 Distinct
    Cardinality 값의 최대값, 그리고 colX와 colY중 작은 Distinct Cardinality 값과 Row set 의 행 갯수
    를 곱한 값의 사이값이 됩니다.

  예문에 있는 Group Cardinality 는 다음과 같은 표현식으로 나타낼 수 있습니다.
     group cardinality lies beetween max ( dist.card.colX , dist.card.colY)
                                                             min ( (dist.card.colX * dist.card.colY), num rows in row set)

 Cost
  Cost는 작업의 단위(Unit of Work) 혹은 자원 사용량을 나타냅니다. CBO는 단위 작업당 Disk I/O, CPU, Memory 사용량을 이용합니다. 따라서 CBO에 의해 사용된 Cost는 명령을 수행하는 동안의 Disk I/O 횟수, CPU와 Memory 사용 횟수를 나타냅니다. 작업은 테이블 스캐닝, 인덱스를 이용한 행으로의 접근, 두 테이블의 Join, 혹은 Row set의 정렬이 될 수 있습니다. 쿼리 계획의 Cost 는 쿼리가 수행되고 결과를 생성할때 야기되는 예상되어지는 작업 단위의 갯수입니다.

  접근 경로(Access Path)는 기반 테이블에서 데이터를 가져올때 요구되는 작업 단위를 결정합니다. 접근 경로는 테이블 스켄, Fast Full Index scan, 혹은 Index Scan 이 될 수 있습니다. 테이블스켄이나 Fast Full Index scan 을 수행하는 동안 많은 블럭들이 싱글 I/O 로 데이터를 읽어드립니다. 그러므로 테이블 스켄과, Fast Full Index scan은 읽혀지는 블럭의 갯수와 multiblock_read_count 의 값에 의해 좌우 됩니다. Index scan의 Cost는 B-tree의 레벨과 스캔된 index leaf 블럭수, 그리고 인덱스 키에 있는 row id를 이용해 추출되는 행의 갯수에 의해 좌우 됩니다. Rowid를 이용하여 행을 뽑아내는 일은 Index clustering Factor 값에 의해 결정됩니다.

  Clustering Factor가 index의 등록된 값 일지라도 Clustering Factor는 테이블의 데이터 블럭에 있는 비슷한 인덱스 컬럼값에 대한 분포도에 영향을 받습니다. 낮은 Clustering factor값은 테이블의 적은 데이터 블럭에 집중적으로 각각의 행이 모여있음을 나타냅니다. 반대로 높은 값은 각각의 행들이 여러 블럭들에 무작위로 퍼져 있음을 나타냅니다. 그러므로 높은 Clustering Factor는 rowid를 이용하여 행을 추출하는 범위 스캔(Range scan)에서 좀더 많은 Cost가 소요되게 됩니다.

 

 

FIN
REF) Oracle Documents (Server .920)/a96533 "Introduction to the Optimizer"

NHN, 오라클에서 큐브리드로 DBMS 교체

NHN, DBMS 3년내 큐브리드로 전면 교체

신규 서비스 중심으로 마이그레이션 추진

2009년 08월 25일 (화) 13:53:40 신혜권기자 hkshin@etnews.co.kr

국내최대 포털 사이트인 네이버를 운영하고 있는 NHN이 늦어도 오는 2011년까지 기존 외산 데이터베이스관리시스템(DBMS)을 국산 제품인 큐브리드로 전면 교체할 계획이다. 교체 대상인 외산 DBMS는 오라클과 마이SQL, SQL서버다. 큐브리드는 NHN이 지난 2008년 10월 인수한 오픈소스 DBMS로, 과거 유니SQL이라는 이름으로 판매됐던 국산 DBMS다. 25일 박원기 NHN비즈니스플랫폼 IT서비스본부장은 “NHN은 DBMS 표준 제품으로 큐브리드 제품을 선정한 상태”라며 “현재 신규 서비스 관련 시스템이나 사내관리시스템, IT인프라관리모니터링시스템 등에 대해 큐브리드 제품으로 교체하는 작업을 진행하고 있다”고 밝혔다. 현재 NHN은 기존 오라클, 마이SQL 등 외산 DBMS 제품을 큐브리드로 교체하는 작업을 20% 정도 진행한 상태다. NHN은 향후 2~3년 내 DBMS 교체 작업을 완료할 계획이다. 이미 큐브리드가 사내 표준 DBMS로 선정된 상태여서 가능한 모든 DBMS를 큐브리드로 교체한다는 방침이지만, 향후 마이그레이션 적합성 여부 등에 대한 평가를 통해 반드시 오라클 DBMS 제품을 사용해야 하는 시스템에 한해서만 기존 제품을 유지하기로 했다. 박 본부장은 “앞서 큐브리드 제품을 기존 오라클 제품 및 마이SQL 제품과 비교해 성능평가를 실시한 결과 성능 면에서 전혀 문제가 없는 것으로 나왔다”고 말했다. 이어 박 본부장은 “큐브리드로 전면 교체가 완료될 경우 라이선스 비용면에 있어 상당한 절감 효과가 있을 것”이라며 “NHN에 특화된 서비스를 개발하는 능력이 향상될 것으로 기대한다”고 덧붙였다. 신혜권기자 hkshin@etnews.co.kr

[ZDNET] MS 인종차별 논란 '휩싸여'

MS, 인종차별 논란 '휩싸여'

송주영 기자 jysong@zdnet.co.kr

2009.08.26 / AM 09:45

[패널토의 동영상보기] 전문가 5인이 말하는 ‘국내 앱스토어의 미래’

[지디넷코리아]마이크로소프트가 웹사이트 사진으로 인종차별 논란에 휩싸였다. MS는 25일(현지시각) 잘못을 인정하고 사과했다고 씨넷뉴스가 보도했다.

 

미국 MS 웹사이트의 사진에는 3명의 인물이 올라와있다. 3명은 흑인, 백인, 아시아계 등 각각 1명이 MS의 업무 소프트웨어 홍보 사진을 통해 보여지고 있다.

 

같은 사진이 MS의 폴란드 지사 사이트에도 올라와 있다. 하지만 이 사진에는 흑인 한명이 백인으로 대체됐다. 심지어 이 사진은 고개 방향만 빼고는 흑인과 바뀐 백인의 앉아있는 모습도 일치한다.

 

이 사진이 트위터 등을 통해 빠르게 확산되면서 MS는 논란에 휩싸였다. 이에 대해 MS는 "이번 상황을 주의 깊게 지켜보고 있다"며 "이번 일에 대해 사과하고 폴란드 사이트의 사진을 삭제하겠다"고 말했다.

 

▲ MS 본사 사이트 사진

▲ MS 폴란드 지사 사이트에 올라온 사진

 

  원본 보기

블리자드 개발자 거짓 인터뷰로 한국 시장 '우롱'

블리자드 개발자 거짓 인터뷰로 한국 시장 `우롱`

입력: 2009-08-26 14:23 | 수정: 2009-08-26 15:25

미 블리자드가 `월드오브워크래프트`(와우) 업데이트 계획과 관련해 한국 매체들을 상대로 `거짓 인터뷰`를 한 것이 드러나면서 파문이 일고 있다.

한국은 `와우` 뿐만 아니라 블리자드 게임에 대한 골수 팬들이 많은 특별한 시장이라는 점을 고려할 때, 블리자드 개발자의 이 같은 행태는 매체 뿐만아니라 국내 게이머들과 시장을 우롱한 처사로 받아들여지고 있어 논란이 예상된다.

24일 관련업계에 따르면 블리자드는 지난주말 미국 현지에서 자체 프로모션 행사인 `블리즈콘 2009`를 진행했다. 이 행사를 통해 블리자드는 `디아블로3` 신규 캐릭터를 소개하는 한편, 새로 서비스될 `와우` 확장팩 등을 공개해 전세계 게임팬들의 주목을 받았다.

문제는 블리자드 핵심 개발자가 한국 매체와 게이머를 상대로 `계획이 없다`고 밝혔던 콘텐츠 업데이트 계획이 당초 얘기와 달리 전격 공개됐다는 점이다. 이 회사는 게임 출시일이나 업데이트 일정과 관련해 당초 언급했던 내용을 `번복`한 경우는 더러 있었지만, 콘텐츠 업데이트와 관련해 이처럼 `뻔한 거짓말`을 하기는 처음이다.

문제는 그 처음이 블리자드를 가장 사랑하는 한국 시장에서 그것도 매체 간담회에서 다수의 기자를 상대로 이뤄졌다는 점이다. `거짓 인터뷰`의 주인공은 알렌 브렉(J. Allen Brack) `와우` 프로덕션 디렉터로 이 사람은 지난달 29일 블리즈콘 행사에 앞서 한국을 방문, `와우3.2` 패치 `십자군의 부름`에 대한 기자간담회를 가졌다.

이 자리에서 알렌 브렉은 `향후 아제로스 대륙에 날아 다니는 것과 같은 형태의 업데이트 계획이 있느냐`는 기자의 질문에 "아제로스에 날것을 도입하려면 많은 기술적 부분이 요구되기에 그러한 부분은 추가되지 않을 것"이라고 답했다.

당시 간담회에 참가했던 국내 게임전문 매체들이 이 내용을 기사화한 것은 물론이다. 각각 어감의 차이는 있지만 `해당(날것) 업데이트에 대한 논의는 있으나 실제 적용이 힘들다`는 게 공통된 내용이었다. (관련 기사 )

하지만 블리자드는 지난주말 열린 블리즈컨 행사를 통해 "세번째 확장팩 `대격변`을 통해 아제로스 대륙에 하늘을 나는 탈 것을 도입하겠다"고 전격 공개했다. 날것을 도입할 계획이 없다던 알렌 브렉의 말과는 180도 다른 내용이다.

이런 내용이 확인되면서 인터뷰에 참가했던 매체와 기자들은 불쾌감을 감추지 못하고 있다. `와우` 개발에 깊숙이 관여하고 있는 알렌 브렉이 확장팩 내용을 모를리 없는 데다 인터뷰를 가진 시점 역시 블리즈컨을 3주 앞둔 상황이었기에 작정하고 거짓말을 한 것으로 밖에 볼 수 없기 때문이다.

일부 매체에서는 "블리즈컨에서의 깜짝쇼를 의식해 고의적으로 답변을 왜곡한 것"이라며 "이는 한국 매체와 게이머 모두를 우롱한 것으로 그냥 넘어갈 수 없는 일"이라고 말했다.

이에 대해 블리자드코리아는 "당장 계획이 없다는 답변이었는데 통역상 오해의 소지가 있었다"며 "한국 기자들과 게이머들에게 거짓을 말한 것은 아니었다"고 해명했다.
    출처 : 디지털 타임즈

 이 기사를 읽고 난 후 드는 느낌은 '글쎄올씨다..' 였습니다. 만약 알렌이 우리나라 인터뷰에서만 '아제로스의 비행은 구현되지 않을것' 이라 하고 외국의 인터뷰에서 '아제로스의 비행은 구현될 것' 이라 했으면 분명 우롱한 것이 맞겠죠. 하지만 그러한 내용이 없는 것으로 미루어 궂이 그러한 인터뷰 내용을 트집잡고 '우롱' 이라는 단어를 써서 괜히 블리자드에 대한 반감을 키울 필요는 없을거라 생각이 드네요.

 인터뷰라고 해서 물어보는 족족 다 진실을 대답해야 할 의무는 어디에도 없다 생각합니다. 아제로스에서의 비행이란 측면은 블리즈컨이 시작할때 깜짝 정보로 사용자들을 놀래켜 주고 싶었을 수도 있고 정말 3개월 전에는 그러한 계획이 없다 1-2개월 전에 계획을 다시 수립했을 수도 있습니다. 그들의 인터뷰를 보면 늘 '아직은 ~~할 계획은 없습니다.' 라는 식으로 말을 하지 '절대 ~~는 구현되지 않을 것 입니다.' 라고 말을 하지는 않습니다. 즉 언제라도 내부 회의를 통해 그들의 계획은 바뀌고 그 바뀐 계획에 따라서 개발이 진행되겠죠.

 내부에서 계획한 일들을 일일이 대외로 공포한다는 것 역시 쓸데없는 에너지 낭비가 아닐까 하는 생각도 문득 듭니다. 어쨌든 그러한 말 한마디에 '우롱'이란 단어를 붙일 필요도, 또 그러한 인터뷰에 대해서 울화를 참을 수 없다는 뉘앙스로 글을 써서 "한국인의 자존심에 상처내기"라는 측면으로 몰고갈 필요도 없는 듯 싶습니다.

ORACLE_010. OPTIMIZER - Choosing Optimizer Approach and Goal

OPTIMIZER - Choosing Optimizer Approach and Goal                        

 기본적으로 CBO의 목표는 최상의 출력결과 입니다. 이는 최소한의 자원을 이용하여 쿼리를 수행하는데 필요한 행을 읽어들이는 방법을 선택하는 것 입니다. 또한 오라클은 반응시간의 최소화를 위한 최적화도 수행합니다. 반응시간이 뜻하는 바는 SQL 구문에 의해 최소의 자원을 이용하여 첫번째 행에 접근하는 방법 입니다.

 

 옵티마이저에 의해 생성된 실행계획은 옵티마이저의 목표에 따라 다양하기도 합니다. 많은 처리량을 위한 최적화는 인덱스 스켄보다 오히려 풀 테이블 스켄이거나 Nested Loop Join 보다 Sort Merge Join 일 수 있습니다. 최상의 반응시간을 위한 최적화는 Index Scan, 혹은 Nested Loop Join 입니다.

 

 예를들어, Sort-Merge 나 Nested Loop 중 하나를 사용하여 Join 구문을 수행한다고 가정해 봅시다. Sort-merge 방식은 아마도 Query 의 모든 결과를 뱉어내는데 빠를 것이고 Nested Loop 방식은 첫번째 행을 뱉어네는데 효율적일 것 입니다. 많은 처리량에 대한 향상이 목표라면 Sort merge join 을 사용하면 될 것 입니다. 반면 좀더 빠른 반응을 원한다면, 옵티마이저는 Nested loop join 을 사용하는게 좋을 것 입니다.

 

  - Oracle Reports Application 과 같은 어플리케이션이 Batch로 동작할때는 처리량에 관해 최적화를 합니다. Batch Application 에서의 처리량은 굉장히 중요한데 사용자는 어플리케이션이 끝마치는 시간이 얼만큼 필요할지만 고려하기 때문입니다. 반응시간은 좀 덜 중요한데, 어플리케이션이 수행되는 동안 개별적으로 실행된 구문의 결과를 계산하지 않기 때문입니다.

 

 - Interactive Applicatoin (대화형 어플리케이션), 예를들어 Oracle Forms App. 나 SQL*Plus 쿼리등은 반응시간에 촛점을 맞춥니다. 사용자들은 구문을 통해 첫번째 행이나 혹은 첫 몇개의 행을 눈으로 확인하기 위해 기다리기 때문입니다.

 

 옵티마이저는 다음과 같은 SQL 문의 요소로 데이터로의 접근과 목표를 최적화 합니다.

  - OPTIMIZER MODE Initialization Parameter

  - CBO Statistics in the Data Dictionary

  - Optimizer SQL Hints for Changing the CBO Goal

OPTIMIZER_MODE INITIALIZATION PARAMETER
 OPTIMIZER_MODE 초기화 파라미터는 인스턴스에서 옵티마이저가 선택할 기본 행동 방법을 설정합니다.

 

Value

Description

CHOOSE                     ALL_ROWS     FIRST_ROWS_n      FIRST_ROWS     RULE

옵티마이저는 Cost-base와 Rule-base중 하나를 선택합니다. 이는 Statistics 가 유효한지에서 결정됩니다.     - DD(Data Dictionary)가 최소 하나의 테이블에 유효한 통계를 가지고 있다면 옵티마이저는 Cost-Based 방식을 사용합니다.   - DD에 아주 소량의 통계만 있다면 그 값이 유효할때까진 Cost-base 를 사용합니다. 하지만 옵티마이저가 반드시 다른 통계 없이 이 구문을 수행하기 위한 통계를 추측합니다. 이는 차선책의 실행계획을 수행할 수 있습니다.   - DD에 통계가 전혀 전재하지 않다면 Rule-base 방식을 사용합니다.    통계의 존재 유무를 떠나서 세션의 모든 SQL문에 Cost-base 방식을 사용합니다. 많은 작업량 처리에 적합한 최적화를 실행합니다.(최소의 자원으로 모든 구문을 해결합니다.)    통계의 유무에 상관없이 Cost-base 를 선택합니다. 또한 첫 n 개의 행을 뱉어내기 위해 최소의 시간을 소요하는 최적화를 수행합니다. n값은 1,10,100 혹은 1000 입니다.    옵티마이저는 COST를 섞어 스스로 첫번째 행을 뱉어네는데 가장 좋은 계획을 결정해 냅니다.    RULE-BASE 방식으로 설정합니다. 통계자료의 유무는 고려대상이 아닙니다.

 

 옵티마이저의 파라메터는 다음과 같은 방법으로 변경할 수 있습니다.

 

  ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1;

 

OPTIMIZER SQL HINTS FOR CHANGING THE CBO GOAL
 개별적으로 수행하는 SQL 문의 CBO 를 변경하기 위해서 다음의 힌트를 개별 SQL에 삽입할 수 있습니다.

 - FIRST_ROWS(n),   n은 어떤 양수도 가능합니다.

 - FIRST_ROWS

 - ALL-ROWS

 - CHOOSE

 - RULE

 

CBO STATISTICS IN THE DATA DICTIONARY

 CBO가 사용하는 통계정보는 DD(Data Dictionary)에 저장되어 있습니다. DBMS_STATS 패키지나 ANALYZE 구문을 통해 물리적 저장장치의 특성이나 스키마 오브젝트의 데이터 분산정도를 수집할 수 있습니다.

 

       * ORACLE은 ANALYZE 구문보다 DBMS_STATS 패키지를 이용하여 통계정보를 모으는 것을

       권장합니다. 이 패키지는 통계정보를 병렬로 수집 가능하게 해 주며 파티션된 오브젝트의 정보

       를 모을 수 있습니다. 또한 다른 여러가지 방법으로 통계정보를 모을 수 있는 수단을 제공합니

       다. 게다가 CBO 는 DBMS_STATS를 이용해 수집한 통계정보만을 사용할 것 입니다.

 

       하지만 DBMS_STATS 보다 ANALYZE를 반드시 사용해야 할 경우가 있습니다.

        - VALIDATE 혹은 LIST CHAINED ROWS 단서를 달아 사용할때

        - FREELIST BLOCKS 의 정보를 수집할 때

 보다 효과적으로 CBO를 사용하기 위해 존재하는 데이터들의 통계정보를 가지고 있어야 합니다. SKEW DATA라 불리우는 중복되는 넓은 범위의 숫자로 이루어진 컬럼이 있다면 히스토그램 정보를 모으길 권장합니다.

 

 통계정보의 결과는 CBO에게 데이터의 유일함과 분산정도의 정보를 제공합니다. 이 정보를 이용하여 CBO는 높은 수준의 정확한 실행계획의 비용을 계산할 수 있습니다. 이는 CBO가 가장 적은 비용을 소요하여 실행 계획을 선택하는 것을 가능하게 해 줍니다.

FIN
REF) Oracle Documents (Server .920)/a96533 "Introduction to the Optimizer"

 

Next => Understanding the Cost-Based Optimizer

 

ORACLE_009. OPTIMIZER - OVERVIEW

OPTIMIZER - OVERVIEW                                                                    

OVERVIEW SQL PROCESSING
 SQL 프로세싱은 SQL문을 실행하기 위해 다음과 같은 절차를 따릅니다.

 - 분석자(PARSER)는 SQL문을 분석합니다. (PARSING)

 - 옵티마이저는 비용기반의 옵티마이저 (COST BASED OPTIMIZER, CBO) 혹은 규칙기반의 옵티마이저 (RULE-BASED OPTIMIZER, RBO)중 최고의 효율을 보여주는 방법중 하나를 선택합니다.

 - RSG(Row Source Generator)는 옵티마이저에게 최적화된 계획을 받고 SQL 실행을 위한 최적화된 실행 계획을 출력합니다.

 - SQL 실행 엔진(SQL Execution Engine)은 SQL문을 실행계획에 따라 실행하고 쿼리의 결과를 생산합니다.

<Picture : SQL Processing Overview : www.oracle.com>

OVERVIEW OF THE OPTIMIZER
 옵티마이저는 SQL 문에 있는 특정한 조건과 참조되는 오브젝트들에 대한 요소들을 분석하고 SQL 문을 가장 효율적으로 수행할 수 있는 방법을 선택합니다. 이러한 선택은 SQL을 실행하는 과정에서 굉장히 중요한 부분이며, SQL을 실항하는 시간에 지대한 영향을 끼칩니다.

 

 SQL은 다음과 같은 여러가지 방법으로 수행될 수 있습니다.

 

 - Full table scans

 - Index scans

 - Nested loops

 - Hash joins

 

 옵티마저에서 생성한 출력물은 쿼리 수행에 있어 가장 최적화된 방법을 설명합니다. 오라클 서버는 비용기반(Cost-based)과 규칙기반(Rule-based)의 최적화 방식을 제공합니다. 일반적으로 최종 목표 데이터에 접근하는데는 비용기반 방식이 사용됩니다.

 

 사용자는 옵티마이저를 세팅하거나 STATISTICS를 수집하여 데이터에 접근하는 방법을 조절할 수 있습니다.

 떄떄로 어플리케이션의 데이터에 대한 충분한 정보를 가지고 있는 어플리케이션 디자이너들은 SQL 문을 수행하는데 좀더 효과적인 경로를 직접 선택할 수 있습니다. 어플리케이션 디자이너는 SQL 문에 HINT 절을 이용하여 그 SQL문이 수행하는데 필요한 정보를 제공할 수 있습니다.

 

FEATURES THAT REQUIRE THE CBO
 다음은 CBO(Cost-Base Optimze)를 수행하는데 필요한 조건들 입니다.

 - Partitioned Table and Index
 - IOT (Index Organized Table)

 - Reverse Key Index

 - Function-based Index

 - SAMPLE clause in a SELECT statement

 - Parallel query and parallel DML

 - Star transformations and star joins

 - Extensible Optimizer

 - Query rewirte with materialized views

 - Enterprise Manager progress meter

 - Hash Joins

 - Bitmap indexes and bitmap join indexes

 - Index skip scans

 OPTIMIZER_MODE 가 RULE로 되어 있다고 해도 위 사항중 하나라도 해당이 되면 CBO로 실행이 됩니다.

OPTIMIZER OPERATIONS
 오라클에 의해 수행되는 모든 SQL문은 옵티마이저에 의해 다음을 수행 합니다.  

---

  Evaluation of expressions and conditions

      옵티마이저는 첫째로 가능한한 모든 조건들과 값들을 평가합니다.
  Statements transformation
      복잡한 구문을 포함한 쿼리 - 서브쿼리 혹은 뷰 (예) -  들은 Join 을 이용한 구문으로 변환합니다.
  Choice of optimizer approches
      옵티마이저는 CBO와 RBO 중 하나를 선택하고 최적화 목표를 결정합니다.
  Choice of access path
      옵티마이저는 테이블 데이터를 얻기 위한 하나 혹은 그 이상의 접근 경로를 결정합니다.
  Choice of join orders
      두 테이블 이상이 결합된 Join 문에서 옵티마이저는 우선 몇개의 테이블을 Join 될 지결정하고,

      결과에 몇개의 테이블이 Join 될 지를 결정합니다.

  Choice of join methods
      어던 구문에서도 Join을 수행하기 위한 동작을 결정합니다.

---

       
 

FIN
REF) Oracle Documents (Server .920)/a96533 "Introduction to the Optimizer"

ORACLE_008. ABOUT LATCH

ABOUT LATCH                                                                                      

LATCH 가 뭐죠?

 LATCH는 매우 간단합니다. SGA에 있는 공유 데이터가 보호될 수 있게 해주는 저레벨의 메커니즘입니다 (- _-;; 이게 간단해?). 예를 들어 사용자가 현제 DATABASE 에서 읽고 있는 데이터의 리스트를 보호하고 버퍼케시 블록에 있는 데이터의 구조를 변형되지 않게 보호하는 것 입니다.

 서버 프로세스나 백그라운드 프로세스가 자료를 변경하거나 조회를 하기 위해서는 반드시 LATCH와 함께 동반해야 하고, 작업이 끝나면 반드시 LATCH를 풀어주어야 합니다.

 

TUNING LATCHES

 LATCH는 튜닝할 필요가 없습니다(..;). LATCH에 대한 경합(CONTENTION)이 일어나게 된다면 이는 SGA에서 잘못된 리소스 사용이 일어났기 때문입니다. V$LATCH 를 조회하는 것은 문제를 해결하는데 도움이 되지 않습니다.

 

WAITING LATCH

 LATCH는 일반적으로 0 값을 가진채로 메모리 한 구석에 박혀있습니다. 만약 LATCH가 0이 아닌 값을 갖게 된다면 이 LATCH는 다른 PROCESS가 납치해 간 것 입니다.

 

 하나의 CPU를 사용하는 환경에서, LATCH를 다른 프로세스가 사용하고 있다면 뒤늦게 LATCH를 요청한 프로세스는 SLEEP 상태로 접어들게 됩니다.

 

 여러개의 CPU 환경하에서는 LATCH를 다른 프로세스가 사용하고 있다면 뒤늦게 LATCH를 요청한 프로세스는 일정 횟수만큼 스핀 상태(할일없이 뱅뱅 노는거죠.)를 지나 다시 LATCH를 획득하기 위해 요청을 합니다. 그런데도 LATCH를 획득할 수 없으면 다시 스핀 상태로 돌아갑니다. 이러한 스핀상태가 여러번 지속된 후에도 LATCH를 획득할 수 없으면 그 프로세스는 SLEEP 상태로 빠저들고 말죠. 이러한 SPIN TIME은 플랫폼이나 O/S가 결정합니다.

 

LATCH REQUEST

 LATCH를 요청하는 두가지 방법중 한가지 방법을 이용해 이를 획득합니다.

 

 WILLING-TO-WAIT

  WILLING-TO-WAIT 으로 요청했는데 LATCH가 사용하지 못한다면, 프로세스는 아주 잠시 대기한 후 다시한번 LATCH를 요청하게 됩니다. 프로세스는 LATCH를 사용할 수 있을때까지 요청과 대기를 계속해서 반복을 합니다. 이 방법은 가장 일반적인 방법입니다.

 

 IMMEDIATE

  IMMEDIATE를 이용한 LATCH 요청이 실패한다면 LATCH 획득시까지 기다리지 않고 다른 명령을 수행합니다. 예를들어 PMON(Process MONitor)이 비정상 종료된 프로세스를 청소하려 했으나 LATCH에 접근할 수 없는 상황이 발생했습니다. 그럼 PMON은 LATCH가 풀릴때까지 기다리지 않고 다음 명령을 수행하게 됩니다.

 

LATCH CONTENTION

 V$LATCH 뷰는 Willing-to-wait 형식의 요청이 GETS, MISSES, SLEEPS, WAIT_TIME, CWAIT_TIME, SPIN_GETS 의 컬럼으로 표현됩니다.  IMMEDIATE 형식의 요청은 IMMEDIATE_GETS, IMMEDIATE_MISSES로 표현됩니다.

 물론 우리의 만능 STATS 팩으로도 확인할 수 있습니다!!(야호!)

 

  GETS : Willing-to-wait 요청이 성공한 횟수

  MISSES : Willing-to-wait 요청이 실패한 횟수

  SLEEPS : Willing-to-wait 으로 기다리다 기다리다 지쳐 잠든 횟수

  WAIT_TIME : Willing-to-wait 으로 기다린 시간 (Milisecond)

  CWAIT_TIME : Spin Time 과 Sleep Time 을 합친 시간 입니다.

  SPIN_GETS : LATCH의 마음을 잡기 위해 붕붕 돌다가 결국 LATCH를 획득한 횟수 입니다.

  IMMEDIATE_GETS : 즉시 LATCH 요청에 성공한 횟수

  IMMEDIATE_MISSES : 즉시 LATCH 요청 실패한 횟수

 

REDUCING CONTENTION FOR LATCHES

 일반적으로, DBA는 LATCH를 튜닝할 필요가 없습니다. 허나 다음의 스텝들은 굉장히 유용합니다.

  - 좀더 자료를 수집하여 LATCH가 경합이 일어나는지 확인하십시요.

  - LATCH에 대한 경합이 SHARED POOL 이나 LIBRARY CAHCE에서 자주 일어나면 APPLICATION 튜닝을 고려해 보십시요.

  - 정보를 수집하여 SHARED POOL 과 BUFFER CACHE의 크기를 조정하십시요.

 

DBA에게 중요한 LATCH

 shared pool latch, and library cache latch :

  이곳에서 일어나는 LATCH의 경합은 SQL, PL/SQL 문이 재사용 되지 않기 때문에 일어납니다. 이러한 일이 일어나는 이유로는 변수가 BIND 되어 있지 않거나 커서(CURSOR) 캐시가 충분하지 않기 때문입니다. 문제를 해결하기 위해서 SHARED POOL을 튜닝하거나 APPLICATION을 튜닝해야 합니다.

 

 cache buffers lru chain latch :

  이곳에서는 더티 블락(dirty blocks)이 디스크에 쓰여질때나 서버 프로세스가 쓰기 위해 블럭을 검색할때 LATCH 가 필요합니다. 이곳에서 LATCH의 경합이 발생한다면 버퍼케시에 지나치게 많은 처리량이 늘어나거나 캐시에서 너무 많은 정렬기능 (Cache-based sort), 잘못된 인덱스의 반복적 (Large index range scans) 접근을 일으키는 SQL, 혹은 너무 많은 테이블 스켄이 일어나는 경우 입니다. 또한 DBWR(DATABASE WRITER)가 잦은 데이터 블록의 변화로 자신의 페이스(PACE)를 유지하지 못할경우와 빈 버퍼를 찾기위해 잡아놓은 LATCH의 시간보다 강제로 더 길게 FOREGORUND PROCESS가 WAIT 하면 발생할 수 있습니다. 이 문제를 극복하기 위해선 BUFFER CACHE와 DATABASE WRITER OPERATION을 튜닝할 것을 고려해 보십시요.

 

 cache buffers chain latch :

  이 Latch는 사용자 프로세스가 버퍼케시에 데이터 블락을 올려 놓을때 필요합니다. 이 Latch에 대한 경합이 일어나는 이유는 특정한 블럭(hot blocks)에 반복적으로 접근하고 있기 때문입니다.

 

FIN
REF) Oracle9i Performance Tunning - Volume I

 

다음편 예고!!)

 OPTIMIZER를 이용한 SQL TUNNING!! 상당히 포스트 연재가 길어질 것 같습니다. 내용이 좀 방대하군요.

ORACLE_008. MONITORING LOCK CONTENTION - Diagnostic

MONITORING LOCK CONTENTION - Diagnostic                              

DIAGNOSTIC

 DBA_WAITER & DBA_BLOCKER를 통해 누가 테이블을 홀딩하고 있고 누가 기다리고 있는지의 정보를 얻을 수 있습니다.  이 뷰를 이용하기 위해서는 CATBLOCK.SQL 을 수행해야 합니다.

            * $ORACLE_HOME/rdbms/admin 에서 찾을 수 있습니다.

 

      TRANSACTION 1

       UPDATE employee SET salary = salary * 1.1;

       //V$LOCK

      TRANSACTION 1

       UPDATE employees SET salary = 1.1;

       //V$LOCKED_OBJECT

 

V$LOCK VIEW

 LOCK TYPE        ID1

 TX                        롤백 세그먼트와 슬롯 번호

 TM                       바뀌기 시작한 테이블의 오브젝트 ID

 

 V$LOCK 뷰의 리소스 ID 1과 부합하는 테이블의 이름을 찾기위해 다음 쿼리를 이용합니다.

 

 SQL> SELECT owner, object_id, object_name, object_type, V$lock.type

    2>    FROM dba_objects, v$lock

    3>    WHERE object_id = v$lock.id1 and object_name = table_name;

 

V$LOCKED_OBJECT VIEW

 LOCK TYPE            ID1

 XIDUSN                  롤백 세그먼트 번호

 OBJECT_ID            변경되기 시작한 오브젝트의 ID

 SESSION_ID          오브젝트를 LOCK한 세션의 ID

 ORACLE_USERNAME

 LOCKED_MODE

 

 V$LOCKED_OBJECT 뷰에 있는 오브젝트 ID와 부합하는 테이블 이름을 찾기.

 

 SQL> SELECT xidusn, object_id, session_id, locked_mode,

    2>    FROM v$locked_object;

       XIDUSN   OBJECT_ID  SESSION_ID  LOCKED_MODE

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

                    3                2711                       9                               3

                    0                2711                       7                               3

 

 SQL>SELECT object_name FROM dba_object WHERE object_id=2711;

      OBJECT_NAME

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

       EMPLOYEE

 

 XIDUSN 의 값이 0이면 XIDUSN 의 값이 0이 아닌 다른 세션에 의해 걸린 LOCK이 WAIT 상태에 있는 세션을 뜻하게 됩니다.

 

UTLLOCKT SCRIPT

 $ORACLE_HOME/rdbms/admin/에 있는 utllockt.sql 스크립트를 이용하는 방법도 좋습니다. LOCK&WAIT 의 상태를 계층적으로 보여주기 때문에 가시적으로 확인하기가 더욱 쉽습니다. UTLLOCKT.SQL 스크립트를 수행하기 전에 CATBLOCK.SQL 스크립트를 SYS권한으로 우선 수행해야 합니다.

 

  WAITING_SESSION        TYPE       MODE  REQUESTED      MODE HELD      LOCK ID1   LOCK ID2

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

  8                                       NONE      None                                    NONE                   0                    0

          9                               TX            Shares (S)                            Exclusive (X)         65547            16

                 7                        RW           Exclusive (X)                       S/ROW-X (SSX)   33554440      2

               10                        RW           Exclusive (X)                       S/ROW-X (SSX)   33554440      2

 

 위 예제에서 보듯이 9번 세션은 8 세션이 TRANSACTION이 끝나길 기다리고 있고 7,10번은 9번 세션을 기다리고 있습니다. (참 쉽죠잉~)

 

RESOVING CONTENTION

 세션을 죽이십쇼!(KILL!!). 그것이 유일한 방법 일 것 입니다. (TRX)가 끝나지 않는다면 말이죠. 이는 (DEAD-LOCK)에서도 마찬가지 입니다.

 

 ALTER SYSTEM KILL SESSION 'SID, SERIAL#';

 

 SID와 SERIAL#을 조회하는 방법입니다.

 

 SQL> SELECT SID, SERIAL# FROM V$SESSION WHERE TYPE='USER';

 

FIN.

REF)Oracle9i Performance Tuning Volume - I

ORACLE_007. MONITORING LOCK CONTENTION - Table Lock, DDL Lock

MONITORING LOCK CONTENTION - Table Lock, DDL Lock            

MANUAL TABLE LOCK MODES (SYNTAX)

 SQL> LOCK TABLE table_name IN mode_name MODE;

            *SQL>LOCK TABLE employee IN exculsive MODE;

 

MODE : SHARE (S) LOCK

 이 모드는 다른 TRANSACTION에게 오직 SELECT . . . FOR UPDATE 만 허용합니다. 당연히 테이블을 변환시키는 일련의 어떠한 행동도 막아버립니다.

 

MODE : SHARE ROW EXCLUSIVE (SRX) LOCK

 DML 명령이나 수동으로 SHARE LOCK 을 획득하는 어떠한 행위도 막아버리는 높은 수준의 테이블 락 입니다. 맹목적으로 데이터의 무결성 참조를 위해 사용합니다.

 

MODE : EXCLUSIVE (X) LOCK

 테이블에 대한 쿼리만 허용합니다. 어떠한 타입의 DML이나 수동 LOCK을 제한합니다.

 

TRANSACTION 1	TRANSACTION 2
LOCK TABLE  department IN                                                EXCLUSIVE MODE:   Table(s) Locked;	SELECT * FROM department                                   FOR UPDATE; Transaction 2 wait.

 

DDL LOCK

 EXCLUSIVE DDL LOCK은 DROP TABLE, ALTER TABLE 문에 필요합니다.

 CREATE, ALTER, DROP 같은 DDL 문은 적용할 오브젝트에 대해 반드시 EXCLUSIVE LOCK 이 필요합니다. 어떠한 레벨의 락이 걸려있다면 ALTER TABLE 구문은 실행되지 않습니다.

 

TRANSACTION 1	TRANSACTION 2
UPDATE employee    SET salary = salary*1.1                                                             3120 rows updated.	ALTER TABLE employee DISABLE PRIMARY KEY; ORA-00054 : Resource busy and                           acquire with NOWAIT specified

 

 SHARE DDL LOCK 은 CREATE PROCEDURE, AUDIT 문에 필요합니다.

 GRANT, CREATE PACKAGE는 Shared DDL Lock을 필요로 합니다. 이러한 종류의 락은 비슷한 DDL 구문을 제한하지 않습니다. 허나 참조하고 있는 오브젝트를 Altering 하거나 Dropping 하는 행위는 제한합니다.

 

fin.

REF) Oracle9i Performance Tuning - Volume I

ORACLE_006. MONITORING LOCK CONTENTION - DML Lock

MONITORING LOCK CONTENTION - DML Lock                              

DML TRANSACTION은 한번에 최소 두개의 LOCK을 획득합니다.

 두종류의 LOCK이 DML 문(INSERT, UPDATE, DELETE or SELECT ... FOR UPDATE)에 사용됩니다.

      - SHARED LOCK & EXCLUSIVE LOCK

 

ENQUEUE MECHANISM

 오라클 서버는 ENQUEUE를 이용하여 모든 LOCK을 다룹니다. ENQUEUE는 다음을 기록합니다.

      - 다른 사용자가 가지고 있는 락을 획득하기 위해 대기중인 사용자

      - 사용자들이 요구하는 락 모드

      - 사용자가 락을 요청한 순서

 

 만약 세명의 사용자가 동시에 한개의 행에 대하여 UPDATE를 시도하면 세명은 모두 TABLE LOCK을 획득하게 됩니다. 하지만 오직 첫번째 UPDATE를 시도한 사용자만이 ROW LOCK을 획득합니다. Table-locking 메커니즘은 누가 Row Lock을 가지고 있는지와 누가 Row Lock 을 기다리고 있는지 기록합니다.

 

  DML_LOCKS 와 ENQUEUE_RESOURCES 파라메터를 증가시켜 인스턴스가 허용할 수 있는 LOCK의 최대 허용갯수를 늘릴 수 있습니다. 이는 PARALLEL SERVER 환경이 요구됩니다.

 

TABLE LOCK MODE

 오라클 서버에 의해 다음의 Table Lock은 자동으로 지정됩니다.

 

 Row Exclusive (RX) : INSERT, UPDATE, DELETE

 Row Share (RS) : SELECT ... FOR UPDATE

 

AUTOMATIC TABLE LOCK MODES

 Row Exclusive (RX)

  - 같은 테이블의 다른 행에 대하여 insert, update, delete 등의 쿼리를 허용합니다

  - 읽기나 쓰기를 위해 수동으로 테이블을 Lock 하는 Transaction 을 제한합니다.

 

  EXAMPLE)

TRANSACTION 1	TRANSACTION 2
UPDATE EMPLOYEE  SET SALLARY=SALLARY *1.1                              WHERE ID=24877;  1 Rows updated.	UPDATE EMPLOYEE  SET SALLARY=SALLARY*1.1                            WHERE ID=24878;  1 Rows updated.

 

 Row Share (RS)

  - SELECT . . . FOR UPDATE 구문을 사용하여 쿼리를 수행할때 행을 Lock 할 수 있습니다.

  - 이는 Exclusive Write 를 위해 Table Lock을 수동으로 잡는 Transaction을 제한할 수 있습니다.

 

  EXAMPLE

TRANSACTION 1	TRANSACTION 2
SELECT ID, SALARY                                                  FROM EMPLOYEE  WHERE ID=28873  FOR UPDATE;          ID      SALARY --------  ----------------  28873               1100  COMMIT;  Commit complete.	LOCK TABLE EMPLOYEE                                 IN EXCLUSIVE MODE; TRANSACTION2 WAITS.     Table(s) Locked

 

fin.

REF) Oracle9i Performance Tuning Volume I

 

참고) SELECT FOR UPDATE

SELECT ... FOR UPDATE
 SELECT ... FOR UPDATE 는 SELECT 쿼리를 이용해 반환된 행에 대해 LOCK을 겁니다. 이 쿼리문은 자신이 수정할(UPDATE할) 행의 값을 확인후 그 값을 기준으로 값을 수정하기 위함입니다. 값을 확인후 그 값을 기준으로 UPDATE를 수행하는 도중 다른 TRANSACTION이 그 값을 변경한다면 결과는 내가 예상하지 못한 값을 만들어 내겠죠. 이러한 행위를 미연에 방지하고자 하는 구문입니다.

 

ORACLE_005. MONITORING LOCK CONTENTION - Lock Mechanism

MONITORING LOCK CONTENTION - Lock Mechanism                     

LOCK MANAGEMENT

 오라클 서버는 자동으로 락을 제어합니다. 최하위의 기본적인 락 메커니즘은 높은 수준에서의 데이터 작업에서 읽기 일관성을 제공합니다.

 

  NOTE 기본 메커니즘은 ROW_LOCKING을 이용해 변경할 수 있습니다. 기본값은 ALWAYS 이고 DML 구문이 실행될 때 최소한의 레벨에 락을 겁니다.  다른 가능한 값으로 INTENT 가 있는데 이는 좀더 상위의 레벨에 대해 락을 겁니다(TABLE LEVEL, 단 SELECT FOR UPDATE 구문은 제외).

 

QUIESCED DATABASE

 DBA는 시스템을 QUIESCED(얼어버린 상태, 즉 변경 불가능 상태란 이야기죠) 상태로 변경할 수 있습니다. SYS와 SYSTEM을 제외한 어떠한 세션도 존재하지 않다면 시스템은 QUIESCED 상태가 됩니다. 현제 진행중인 트랜젝션, 쿼리, 페치( FETCH), PL/SQL 프로시져나 공유 자원을 가지고 있는 ACTIVE 세션은 큐(QUEUE)에 들어갑니다. 오직 DBA만이 시스템을 QUIESCE 상태로 변경 가능 합니다.

 

DATA CONCURRENCY

 락은 높은 수준의 데이터 작업을 가능하게 디자인 되어 있습니다. 즉 많은 사람들이 동시에 같은 데이터에 접근이 가능합니다.

 

  - Data Manupulationg Language (DML) 은 Low Level 락 입니다.    

TRANSACTION 1	TRANSACTION 2
UPDATE EMPLOYEE  SET SALLARY=SALLARY *1.1                                          WHERE ID=24877;  1 Rows updated.	UPDATE EMPLOYEE  SET SALLARY=SALLARY*1.1                             WHERE ID=24878;  1 Rows updated.

 

TRANSACTION 1	TRANSACTION 2
UPDATE EMPLOYEE                              SET SALLARY=SALLARY + 1200;                                    13120 rows updated.	SELECT SALARY  FROM EMPLOYEE                                                   WHERE ID = 10;                  SALARY -----------------------                     10000

DURATION 

 LOCK은 TRANSACTION이 COMMIT, ROLLBACK, 혹은 TERMINATE 될 때까지 유지됩니다. TRANSACTION이 비정상 종료 된다면 PROCESS MONITOR가 이를 감지하고 LOCK을 해제합니다.

 

LOCKING MODE

 EXCLUSIVE LOCK

  Exclusive Lock 은 공유된 자원이 Exclusive Lock이 해제되기 전까지 못 사용하게 합니다.

  Example ) Exclusive Lock 은 Row Level 의 DML Transaction 에서 SET 됩니다.

TRANSACTION 1	TRANSACTION 2
UPDATE EMPLOYEE                              SET SALLARY=SALLARY * 1.1                                        WHERE ID=24877;                                   1 rows updated	UPDATE EMPLOYEE  SET SALLARY=SALLARY*1.1                                 WHERE ID=24877; Transactions 2 waits.

 

 SHARED LOCK

  수개의 Transaction 은 같은 자원에 Shared Lock을 겁니다.

  Example ) Shared Lock 은 테이블 레벨의 DML Transaction에서 SET 됩니다.

TRANSACTION 1	TRANSACTION 2
UPDATE EMPLOYEE                              SET SALLARY=SALLARY * 1.1                                        WHERE ID=24877;                                   1 rows updated	UPDATE EMPLOYEE  SET SALLARY=SALLARY*1.1                                 WHERE ID=24878; 1 rows updated

 

LOCK DURATION

 우선적으로 LOCK 을 잡은 TRANSACTION이 COMMIT, 혹은 ROLL BACK 할 동안 입니다.

TRANSACTION 1	TRANSACTION 2
UPDATE EMPLOYEE                              SET SALLARY=SALLARY * 1.1                                        WHERE ID=24877;                                    1 rows updated.  COMMIT;  Commit complete.	UPDATE EMPLOYEE  SET SALLARY=SALLARY*1.1                                 WHERE ID=24877;  Transactions 2 waits until transaction 1  is committed.  1 rows updated.

 

 

Fin.

Ref) Oracle 9i Performance Tunning - Volume 1

ORACLE_004. EXPLAIN PLAN

EXPLAIN PLAN                                                                                     

THE EXPLAIN PLAN STATEMENT

 TRACING 을 사용하지 않고 SQL*Plus (그냥 오라클에서 사용하는 SQL프로그램 상표입니다.)에서 EXPLAIN PLAN 구문을 사용할 수 있습니다. 이 구문을 사용하기 위해서는 PLAN_TABLE이라는 테이블이 필요한데 이 테이블을 만들기 위한 스크립트가 제공되어 있습니다. utlxplan.sql 스크립트가 바로 plan_table을 만드는 스크립트입니다.

 *utlxplan.sql 은 $ORACLE_HOME/rdbms/admin 에 위치합니다.

SYNTAX

 EXPLAIN PLAN [SET STATEMENT_ID='...'] [INTO my_plan_table]

 FOR SELECT ... .

 

 EXAMPLE)

  EXPLAIN PLAN FOR

     SELECT d.name

     FROM dept d

     WHERE d. depno IN (

         SELECT deptno FROM emp@orc2.world

         GROUP BY deptno HAVING COUNT (deptno) > 3);

 

VIEWING THE EXECUTION PLAN

 SQL구문을 실행했다면 그 실행계획과 결과가 utlxplan.sql 스크립트로 만들어 놓은 plan_table에 기록이 됩니다.  실행 결과를 보기 위해서는 utlxpls.sql 스크립트를 실행합니다.

 utlxpls.sql 스크립트는 사용자가 정한 특정 select 문에 대한 실행 계획을 보여줍니다. 다음과 같은 형식으로 보여줍니다.

 

 Plan Table

 리모트 사이트에 대한 SQL의 실행계획에 대한 정보는 다음과 같이 쿼리를 작성하세요.

 

  SELECT OTHER FROM PLAN_TABLE WHERE operation = 'REMOTE';

 

 * OTHER 컬럼에 대해 보기가 불편하다면 다음의 SQL*Plus 명령을 입력해 보세요.

     SET LONG  9999999

ORACLE_003. STATS_PACK - GATHER - PART2

STATS_PACK - GATHER - PART 2                                                       

DBMS_STATS.GATHER_DATABASE_STATS (

estimate_percent            block_sample      method_opt          degree    granularity    cascade      stattab              statid      options        OUT    objlist            gather_sys    no_invalidate    gather_temp

NUMBER BOOLEAN VARCHAR2 NUMBER VARCHAR2           BOOLEAN VARCHAR2 VARCHAR2 VARCHAR2 ObjectTab, VARCHAR2 BOOLEAN BOOLEAN BOOLEAN

DEFAULT NULL, DEFAULT FALSE, DEFAULT 'FOR ALL COLUMNS SIZE 1', DEFAULT NULL, DEFAULT 'DEFAULT', DEFAULT FALSE, DEFAULT NULL, DEFAULT NULL, DEFAULT 'GATHER',   DEFAULT NULL, DEFAULT FALSE, DEFAULT FALSE, DEFAULT FALSE );

 

DBMS_STATS.GATHER_DATABASE_STATS (

estimate_percent            block_sample      method_opt          degree    granularity    cascade      stattab              statid      options           statown    gather_sys    no_invalidate    gather_temp

NUMBER BOOLEAN VARCHAR2 NUMBER VARCHAR2           BOOLEAN VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 BOOLEAN BOOLEAN BOOLEAN

DEFAULT NULL, DEFAULT FALSE, DEFAULT 'FOR ALL COLUMNS SIZE 1', DEFAULT NULL, DEFAULT 'DEFAULT', DEFAULT FALSE, DEFAULT NULL, DEFAULT NULL, DEFAULT 'GATHER', DEFAULT NULL, DEFAULT FALSE, DEFAULT FALSE, DEFAULT FALSE );

 

---

    

DBMS_STATS.GATHER_SYSTEM_STATS (

gathering_mod                  interval    stattab    statid    statown

VARCHAR2         INTEGER VARCHAR2 VARCHAR2 VARCHAR2

DEFAULT 'NOWORKLOAD'                          DEFAULT NULL, DEFAULT NULL, DEFAULT NULL DEFAULT NULL );

---

           GATHERING_MODE

                GATHERING_MODE 의 값은 다음과 같습니다.

 

                NOWORKLOAD

                     시스템 활동을 캡춰하는데 워크로드가 필요하지 않습니다. 오라클 내부의 기본값을 이용해

                     시스템의 STATISTICS 를 생성합니다. 이 모드는 워크로드를 서브밋 할 수 없는 상황에 딱 알

                     맞는 옵션입니다 (예를들어, 개발 프로세스 중). 실제 작동중인 시스템의 활동에 기반한 시스템

                     STATISTICS 를 위해서는 INTERVAL 혹은 START | STOP 모드를 사용하십시요.

 

                INTERVAL

                     특정한 간격으로 시스템 활동을 캡춰합니다. 이 옵션은 INTERVAL 파라미터와 합쳐져 사용

                     합니다. 간격값은 분 단위 입니다. 시스템 STATISTICS 는 DICTIONARY 혹은 STATAB 에

                     생성됩니다. 정해진 스케쥴보다 일찍 수집활동을 멈추고 싶다면

                     EXEC DBMS_STATS.GATHER_SYSTAM_STATS(GATHERING_MODE=>'STOP') 구문을

                     이용하여 정지할 수 있습니다.

 

                START | STOP

                     원하는 시점에서 시스템 활동을 캡춰하고 DICTIONARY 혹은 STATTAB 에 정보를 업데이트

                     합니다. INTERVAL 값은 무시되어집니다. (당연하겠죠?)

 

          

     

        본문보다 더 쓸모있는 TIP! (본문은 그럼 뭐냐..) >

        전에 설명했던 값들의 설명은 제외하였고,  새로운 설정값에 관해서만 기술했습니다.

      GATHER_TABLE_STATS 의 STATTAB 과 GATHER_INDEX_STATS 의 STATTAB 은 그 사용법이

      동일합니다.

 

        기본적으로 STATS_PACK 을 사용하고 싶으면

 

                 SQL>EXEC DBMS_STATS.GATHER_[ TABLE | INDEX | DATABASE | SYSTEM]_STATS

                           (OWNNAME =>'VALUE', ESTIMATE_PERCENT=>'10', ... ) ;

 

      과 같이 사용하시면 됩니다.

        또한 일일이 REFERENCE 를 찾아볼 필요 없이 DESC[RIBE] 명령문을 통해 사용할 수 있는 방법을

      참고할 수 있습니다. DESC[RIBE]는 VIEW나 TABLE 등의 구성 정보만 보는 것이 아니라 이렇게 패키

      지의 정보도 확인할 수 있습니다.

 

      STATS_PACK.FINISH_GATHER_OPTIONS___________________________________________________

ORACLE_002. STATS PACK - GATHER - PART 1

STATS_PACK - GATHER - PART 1                                                       

STATS PACK

  STATS PACK 이 어떤 역할을 하는지는 알아보았습니다. 거기에 더해 GATHER_INDEX_STATS 구문을 통한 INDEX 의 STATISTICS를 모으는 방법을 알아 보았습니다. 이번 포스팅에서는 그 외에 TABLE, SCHEMA, DATABASE, SYSTEM STATISTICS 를 수집하는 방법을 알아보겠습니다.

 

 사용 방법은 INDEX 와 거의 유사합니다. 그렇기에 중복되는 설명은 피하도록 최대한 노력해 보겠습니다. (라고쓰고 '귀찮습니다.' 라고 읽습니다. ~_~)

 

 

DBMS_STATS.GATHER_TABLE_STATS (

ownname              tabname              partname    estimate_percent            block_sample      method_opt          degree    granularity    cascade      stattab              statid      statown              no_invalidate

VARCHAR2, VARCHAR2, VARCHAR2 NUMBER BOOLEAN VARCHAR2         NUMBER VARCHAR2 BOOLEAN VARCHAR2 VARCHAR2 VARCHAR2 BOOLEAN

DEFAULT NULL, DEFAULT NULL, DEFAULT FALSE, DEFAULT 'FOR ALL COLUMNS SIZE 1' DEFAULT NULL, DEFAULT 'DEFAULT', DEFAULT FALSE, DEFAULT NULL, DEFAULT NULL, DEFAULT NULL, FALSE);

---

     BLOCK_SAMPLE

           무작위 행을 샘플링 할 것인지, 혹은 무작위 블록 단위의 샘플링을 할 것인지 결정할 수 있습니다.

          무작위 블록 샘플링이 물론 더 효과가 좋습니다만 데이터가 디스크에 무작위하게 분포되어 있지 않

          다면 샘플값은 알지 못하는 값으로 될 것 입니다. ESTIMATE STATISTICS 를 수행할 때만 적절합니다.

 

     CASCADE

           해당 테이블과 관련한 INDEX의 STATISTICS 를 같이 수집합니다. 이 옵션을 사용하겠다는 것은

          GATHER_INDEX_STATS 를 수행하는 효과 역시 받겠다는 뜻 입니다.

 

 

DBMS_STATS.GATHER_SCHEMA_STATS (

ownname              estimate_percent            block_sample      method_opt          degree    granularity    cascade      stattab              statid      options    objlist    statown              no_invalidate    gather_temp

VARCHAR2, NUMBER BOOLEAN VARCHAR2         NUMBER VARCHAR2 BOOLEAN VARCHAR2 VARCHAR2 VARCHAR2 OUT ObjectTab, VARCHAR2 BOOLEAN BOOLEAN

DEFAULT NULL, DEFAULT FALSE, DEFAULT 'FOR ALL COLUMNS SIZE 1' DEFAULT NULL, DEFAULT 'DEFAULT', DEFAULT FALSE, DEFAULT NULL, DEFAULT NULL, DEFAULT 'GATHER',   DEFAULT NULL, DEFAULT FALSE DEFAULT FALSE );

 

DBMS_STATS.GATHER_SCHEMA_STATS (

ownname              estimate_percent            block_sample      method_opt          degree    granularity    cascade      stattab              statid      options    statown    no_invalidate             gather_temp

VARCHAR2,        NUMBER BOOLEAN VARCHAR2 NUMBER VARCHAR2 BOOLEAN VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 BOOLEAN BOOLEAN

DEFAULT NULL DEFAULT NULL, DEFAULT 'FOR ALL COLUMNS SIZE 1', DEFAULT NULL, DEFAULT 'DEFAULT', DEFAULT FALSE, DEFAULT NULL, DEFAULT NULL, DEFAULT 'GATHER', DEFAULT NULL, DEFAULT FALSE DEFAULT FALSE );

---

           OPTIONS

               다음 옵션을 통해 STATISTICS를 수집하는 방법을 정의할 수 있습니다.

 

               GATHER

                    스키마의 모든 오브젝트에 관한 정보를 얻습니다.

               GATHER AUTO

                   필요한 STATISTICS 정보를 자동으로 얻습니다. 오라클은 어떠한 오브젝트가

                   새로운 STATISTICS 정보를 필요하는지 결정하고 어떻게 모을지를 결정합니다. 이 옵션을 사용

                   하면 추가적으로 필요한 파라메터는 ownname, stattab, objlist, 그리고 statown 입니다.

                   그 외의 다른 파라메터는 무시해버리죠.

               GTHER_STALE

                   STALE 은 우리나라 말 뜻으로 '낡은, 오래된, 싱싱치 못한, 곰팡내 나는' 등의 뜻을 가지고 있죠.

                   말 그대로 너무 사용하지 않아서 곰팡내 나는 오브젝들의 STATISTICS를 수집합니다.

                   *_tab_modifications 뷰를 이용하여 곰팡내 나는 오브젝트가 무엇인지 결정합니다.

                GATHER_EMPTY

                   STATISTICS 정보를 가지고 있지 않은 오브젝트의 STATISTCS 정보를 갱신합니다.

                LIST_AUTO

                   GATHER_AUTO 옵션을 통해 수집될 오브젝트의 리스트를 반환합니다.

                LIST_STALE

                   GATHER_STALE 옵션을 통해 수집될 오브젝트의 리스트를 반환합니다.

                LIST_EMPTY

                   GATHER_EMPTY 옵션을 통해 수집될 오브젝트의 리스트를 반환합니다.

 

            OBJLIST

                STALE 혹은 EMPTY 한 오브젝트의 리스트

 

            NO_INVALIDATE

               TRUE 로 지정되어 있을시 커서(CURSORS)가 INVALIDATED 가 되지 말아야 합니다.

               CASCADE 가 정의되어 있다면 GATHER_INDEX_STATS 에 설정되어져 있는 몇몇의 특정

               한 타입의 인덱스들에게 적절하지 않습니다.

 

            GATHER_TEMP

               GLOBAL TEMPORARY TABLESPACE 의 STATISTICS 정보를 수집합니다. 이 임시 테이블은

               생성시 반드시 "ON COMMIT PRESERVE ROWS" 단서를 달아서 생성한 테이블이어야 합니다.

               STATISTICS는 이 PROCEDURE(프로시저)를 수행한 세션의 테이터만을 이용하여 STATISTICS

               를 수집합니다만, 수집된 정보는 모든 세션과 함께 공유합니다.