whisler@naver.com

LOCK?

 latch가 SGA를 보호한다면, lock은 database 전체를 보호한다.
태이블, 트랜잭션, 언두 세그먼트, 테이블 스페이스, 잡(job) 등 데이터 베이스 단위에서
피요한 대부분의 리소스들은 락의 보호를 필요로 하며 복잡하다.

간략히 정리해보면

 LOCK 획득 매카니즘

                                                                  - (주)엑셈 practical OWI 15P 참조-
 
순서

1. Process A가 shared 모드로 락(lock)획득 시도

2. 해당 락을 점유한 프로세스가 존재하지 않으므로 Process A가 shared 모드로 락 획득하며     Process A를 보유프로세스 목록(OWNER LIST)에 등록 한다.

3. Process W가 exclusive 모드로 락 획득 시도

4. Process A가 shared 모드로 락을 획득하고 있으므로 락 획득 실패하며 Process W는     대기 프로세스 목록에(Waiter list)에 등록 한다.

5. 보유 프로세스목록에 등록되어 있는 프로세스가 작업을 완료(commit, roll back등)하면      대기프로세스 목록에 존재하는 다른 프로세스를 깨운다.

6. 대기프로세스 목록에 등록된 프로세스는 타임아웃 시간이 경과 후에도 깨워주지 않으면 스스로 깨어나서      데드락(dead lock)이 발생했는지 여부를 확인 후 다시 대기 상태로 빠진다.

타임 아웃

데드락을 피하기 위해 대기목록의 프로세스는 3초마다 깨어남 데드락 체크 후 다시 대기목록에 들어감 데드락이 확인 되면, 락 요청은 취소되고 현재 SQL문을 roll back 한다.

타임 아웃 시간

enqueue lock : 3초 buffer lock  : 일반 1초, 연속적으로는 3초 row cache lock : 60초 library cache lock / pin : 일반프로세스는 3초. PMON은 1초

(주)엑셈 practical OWI 15P를 참조













출처 : http://baind.tistory.com/entry/LOCK

 오라클에서 CURSOR란 시스템 글로벌 영역의 공유 풀 내에 저장공간을 사용하여 사용자가 SQL 문을 실행시키면 결과값을 저장공간에 가지고 있다가 원하는 시기에 순차적으로 fetch해 처리하여 해당 결과 셋을 프로그래밍적으로 접근할수 있게 도와주는 기능이다.

 이해를 돕기위해 아래의 명시적 커서 예문을 보자.
 하단의 선언문(DECLARE)에서 SELECT한 결과값을 실행문(BEGIN)에서 FETCH 하여 한 결과값을 순차적으로 처리할 수 있다. 뭐 이해가 안가도 그냥 보자.

CURSOR는 묵시적커서(Implicit Cursor)와 명시적커서(Explicit Curosr)로 나뉜다.

묵시적 커서 (Implicit Cursor)

 묵시적 커서는 각 SQL문장의 실행 결과에 접근하여 그 결과값을 이용하기 위한 내부적 커서이다. 간단히 변수를 이용한다고 생각해도 좋겠다. 일반적으로 SELECT문, 혹은 다른 속성에서 값을 얻어와 변수에 저장하는데 사용된다.

예문)
SELECT name
          into v_name
 FROM members
 WHERE student_no = 13;
 v_count = SQL%ROWCOUNT;

상단의 예문의 빨간 부분은 모두 묵시적 커서가 사용된 문장이다. 예문 내에는 커서가 선언된 부분이 없지만 v_name변수로 해당 SELECT문의 결과값이 (당연히 저 SELECT문의 결과는 1개여야 한다. 그 이상일 경우 에라.) 저장된다. 또 하단의 SQL%ROWCOUNT의 값이 v_count변수에 할당된다. 물론 SELECT문에서 나온 행수는 1개이므로 v_count에는 1이 들어간다.


묵시적 커서에서는 위에 사용된 SQL%ROWCOUNT와 같이 4가지 속성을 제공한다.

SQL%ROWCOUNT : 최근 실행된 SQL문의 결과 행 갯수를 리턴
SQL%FOUND : 최근에 실행된 SQL문의 결과 행 존재 유무, 결과값이 있을때 TRUE를 리턴.
SQL%NOTFOUND : SQL%FOUND와 반대
SQL%ISOPEN : 최근에 실행된 SQL문의 묵시적 커서의 종료 유무

명시적 커서 (Explicit Cursor)

 명시적 커서는 처음의 맨 상단의 예제와 같이 일반적으로 어떠한 결과값을 글로벌 영역에 저장해놓고 순차적으로 값을 Fetch해 이용하기 위해 사용된다. 명시적 커서라고 불리우는 이유는 묵시적 커서와는 다르게 명시적으로 CURSOR라고 선언하고 사용하기 때문에 누가봐도 커서니깐. 그렇게 불린다. 머. 아님말고.

 명시적 커서는 간단히 아래와 같이 4단계로 나뉜다.

 CURSOR :  커서 선언
 OPEN : 커서 열기
 FETCH ~ INTO : 커서가 가리키는 곳의 결과 값을 엑세스
 CLOSE : 커서 닫기


 다시 한번 상단의 그림으로 한단계씩 살펴보자.

Chunk

특징

1. extent에 할당되는 메모리 조각. 블록보다 큰 단위
2. X$KSMSP : Shared pool 메모리에 할당된 chunk와 상태, 사용현황 확인
3. KSMCHCOM 컬럼 : 메모리 할당 목적 확인
4. 고유한 이름을 갖고 있으며 view로 내용 확인 가능
5. Chunk 종류
   

문서정보

• 이 문서는 오라클클럽에서 작성하였습니다.
• 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• 출처 : http://www.gurubee.net/display/DBSTUDY/Chunk?
• 오라클클럽 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
  

Library Cache

1. System Global Area 구성

1. SGA(System Global Area)
   1. 디스크에서 읽어온 데이터를 저장하는 메모리 영역
   2. 저장된 데이터를 읽거나 변경 작업에서 사용되는 공용 메모리 영역
   3. SGA사용 목적 : 메모리 사용을 최소화하면서 처리성능 최대화
      
      
      
2. Shared Pool - SQL, PL/SQL 수행에 필요한 정보 저장
   1. 목적 : 한번 처리된 SQL의 실행 정보를 공유함으로써 자원의 사용을 최소화하고 SQL 수행속도 증가
   2. Variable 영역 : Shared Pool + Java Pool + Large Pool + Streams Pool
   3. Permanent Area(시스템 영역, 고정영역)ㅍㄷㄱ냐ㅐㅜ
      • SGA 관리 메커니즘 및 오라클 파라미터 정보 저장
      • 자동 할당, 사용자 지정 No
      • SGA구성 요소 중 Fixed Size에 해당
   4. Library Cache
      • PL/SQL, SQL에 대한 분석 정보(Parse Tree) 및 실행계획 저장
   5. Dictionary Cache
      • 테이블, 인덱스, 뷰, 함수 및 트리거 등의 사용자, 구조, 권한 등의 dictionary data 정보 저장
      • Row Cache : 참조 데이터를 row 단위로 가짐
   6. Reserved Area(예약 영역)
      • 크기가 큰 객체 저장
      • 동적 메모리 할당 시에 메모리 조각 부족으로 인한 SQL실행 실패(ORA-4031) 방지하기 위한 공간
   7. Spare Free Memory
      • 단편화 최소화를 위해 일정 크기를 고정 영역에 숨겨둠
      • 메모리 할당이 꼭 필요한 경우 이 영역에서 할당 받음
        

2. Library Cache 구성

1. 특징
   1. SQL문 수행과 관련된 모든 정보 저장
   2. LRU 알고리즘을 사용하여 관리(Database Buffer Cache와 공통점)
   3. shared pool latch : shared pool에서 특정 object 정보 혹은 SQL 커서를 위한 free chunk 할당 받을 때 필요
      • 9i 이전 : 하나의 shared pool latch로 전체 관리
      • 9i 이후 : shared pool을 여러 개의 sub pool로 나누어 관리할 수 있게되면서 latch도 7개까지 사용 가능
      • 동시 사용자가 순간적으로 과도한 하드파싱 부하를 일으킨다면 shared pool latch에 대한 경합 현상 발생 가능
2. 목적 : library cache에 저장된 정보를 빠르게 찾고 저장
3. Library Cache Object(LCO) : 라이브러리 캐시에 저장되는 정보의 단위
   1. Caching 정보 분류 I
      • Library Cache 안에서 공유되는 부분 : SQL 텍스트, Execution Plan, Bind Variable Data Type과 Length 등
      • 실행가능한 LCO : 실행가능
        • Package
        • Procedure
        • Function
        • Trigger
        • Anonymous PL/SQL Block (여기에 속하는게 맞는지?? @.@)
      • Object LCO : object 정보
        • Shared Cursor
        • Table Definition
        • View Definition
        • Form Definition

          schema object information 1. data dictionary cache에도 저장됨(저장 포맷이 다름) 2. library cache에도 저장하는 목적 : LCO간 의존성을 관리하기 위함

   2. Caching 정보 분류 II

      분류	특징	종류
      Stored Object	생성 후 drop 하기 전까지 데이터베이스에 영구적으로 보관되는 object 생성될 때부터 이름을 갖음	테이블, 인덱스, 클러스터, 뷰, 트리거, 패키지, 사용자 정의함수, 프로시저
      Transient Object	실행시점에 생성돼서 인스턴스가 떠있는 동안에만 존재하는 일시적인 object 별도로 이름을 지정하지 않음 문장을 구성하는 전체 문자열 그대로가 이름 역할함	커서, anonymous PL/SQL

4. 작동 방법
   1. Heap Manager와 Library Cache Manager가 관리
   2. Library Cache Manager는 Hashing 기법 이용해서 Object에 대한 이름을 갖는 Handle을 찾고, 이 Handle은 다시 해당 Object를 가리킴

      Handle(= Library cache Handel) 1. Library cache object(LCO) 관리 2. 실제 정보가 있는 LCO에 대한 메타정보 및 위치값 저장 3. SQL : SQL 텍스트를 상수로 변환해서 bucket결정 4. SQL 이외(table, view etc) : "user + object name + DB link"를 상수로 변환해서 bucket결정

   3. Library Cache Manager가 object 찾는 순서
      1. 필요한 Object의 Namespace, Object Name, Owner, Database Link 값에 Hash Function 적용
      2. 해당 Object가 존재하는 Hash Bucket 찾기 (SQL 문장의 경우 앞뒤 64바이트의 글자 이용)
      3. Hash Bucket의 Linked List를 따라 원하는 Object의 실제 존재 유무 체크
      4. Object 존재 : 찾은 Object를 사용
           Object 존재하지 않음 : Library Cache Manager는 주어진 이름으로 Empty Object 생성
      5. 생성된 Empty Object를 Hash Table에 포함시킴
      6. 오라클 프로세스에게 해당 Object를 로드하도록 요청
      7. 오라클 프로세스가 해당 Object를 디스크에서 읽어, Heap Manager에 의해 새로이 할당된 메모리에 올려놓음
      8. 해당 Object 사용

         Child LCO  ⓐ SQL 커서 : 부모 LCO 밑에 schema 별로 자식 LCO소유. version count = 자식 LCO수  ⓑ table, procedure etc : schema명과 함께 저장되므로 유일성이 보장되어 자식 LCO가 없음

         
         

   4. SQL 문 찾는 순서
      • Bucket 찾기 : SQL 문에 Hash Function 적용 후 SQL 문의 Handle이 있는 Bucket 찾아감
      • 공유 가능한 SQL 문 찾기 : 해당 Bucket 안에는 다른 SQL 문이면서 Hash Function 값만이 같은 여러 SQL들이 있을 수 있으므로 이 Bucket List를 따라가면서 공유 가능한 같은 SQL이 있는지를 확인
      • 같은 문장을 발견하였지만 서로 다른 Version 존재 가능성 있음

        Version SQL 문은 같지만 서로 다른 유저의 Object를 사용한 경우, Bind Variable Data Type이 다른 경우, 서로 다른 Application Info를 사용하는 경우 서로 다른 Version으로 존재하며, 이는 공유되지 않는다.

      • 같은 문장을 찾지 못한 경우는 SQL 문은 다시 Parsing 됨
        
5. Shared Pool Latch & Library Cache Latch
   1. Library Cache Latch : 라이브러리 캐시 체인을 탐색하고 변경할 때 획득
   2. latch:library cache 대기 이벤트 : Library Cache Latch 경합 발생 시
   3. Library Cache Latch 개수도 몇 개(CPU 개수에 근접)에 불과하므로 하드 파싱 및 소프트파싱이 많이 발생해도 이 latch에 대한 경합 증가 함
      
      
6. Library Cache Lock & Library Cache Pin
   1. 목적 : LCO 보호
   2. 순서 : handle Lock 획득 ⇒ Pin 획득 : LCO의 실제 내용이 담긴 heap에서 정보를 읽거나 변경 시
      • pin 획득 목적 : LCO를 읽고 쓰고, 실행하는 동안 다른 프로세스에 의해 정보가 변경되거나 캐시에서 밀려나는 것 방지

3. 라이브러리 캐시 최적화를 위한 개발자의 노력

1. 커서 공유 가능한 형태의 SQL 작성 : 바인드 변수 사용으로 하드파싱 감소
2. 세션 커서 캐싱 기능 활용 : 라이브러리 캐시에서 SQL 찾는 비용 감소
3. 에플리케이션 커서 캐싱 이용 : Parse Call 발생 감소

참고 문서

• 서적(오라클 성능 고도화 원리와해법 I)

문서정보

• 이 문서는 오라클클럽에서 작성하였습니다.
• 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• 출처 : http://www.gurubee.net/pages/viewpage.action?pageId=4948304&
• 오라클클럽 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

SQL 처리 과정

Fig. SQL 처리 단계

【예제】 emp_new테이블에 24522건 삽입하기 : 첫 번째 parse call 에서 hard parsing 수행

SQL> insert into emp_new select * from patient where coh_no='019';

24522 rows created.

Elapsed: 00:00:01.34

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.08       0.16          1          1          0           0
Execute      1      0.76       1.03       3830       5174      11390       24522
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.85       1.19       3831       5175      11390       24522

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 62  (NEWNC01)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   MODE: ALL_ROWS
  24522   TABLE ACCESS   MODE: ANALYZED (FULL) OF 'PATIENT' (TABLE)

I. SQL Parsing

1. SQL Parser의 역할

1. SQL 문 ⇒ SQL Parser : SQL 문장의 분석하고 parsing(tokenize)해서 parsing tree 생성
   • SQL 문장 분석 : SQL 문법 오류 확인
2. semantic 확인 : 존재하지 않거나 권한 없는 object 사용 혹은 존재하지 않은 컬럼 참조 등 확인
3. SQL cursor가 shared pool에 caching되어 있는지 여부 확인
   1. 해싱 알고리즘 사용
   2. parsing 요청한 사용자, optimizer 관련 파라미터 설정 등 확인 ⇒ 하나라도 다르면 새로운 SQL cursor 생성
   • shared pool에서 정확히 일치하는 SQL cursor를 찾은 경우 : 실행단계
   • shared pool에서 매칭되는 SQL문을 못 찾았거나 새로운 SQL cursor 생성 : SQL Optimization 단계

II. Optimization

1. Query Transformation(쿼리 변환기)

1. 사용자가 던진 SQL문을 최적화하기 쉬운 형태로 변환 수행
2. 쿼리 변환 전후 결과가 동일할 경우에만 수행

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

1. 후보군 실행계획 생성

3. Estimator(비용계산기)

1. 실행계획 전체에 대한 총 비용 계산
2. 비용 측정 기준 : 선택도(selectivity), cardinality, 비용(cost)
   1. 선택도(selectivity) : 처리할 집합에서 해당 조건이 만족되는 row가 차지하는 비율
   2. cardinality : 판정대상이 가진 결과건수 혹은 다음단계로 들어가는 중간결과건수
   3. 비용(cost) : 실행계획상의 연산을 수행할 때 소요되는 시간비용을 상대적으로 계산한 예측치

      예제 query】select * from where empno='0001'; 전제】emp 테이블의 전체 row수는 200, 그 중에서 empno='0001'인 row수는 10건 선택도】5% cardinality】10 rows

3. 예상치
   1. 목적 : 각 단계를 수행하는데 필요한 I/O, CPU, 메모리 사용량 등을 예측
   2. object 통계정보, 하드웨적인 시스템 성능 통계정보(CPU 속도, Single blcok read time, multiblock read time 등) 이용
   3. object 혹은 시스템 통계정보는 오라클이 자동 수집 혹은 정책에 따라 주기적으로 수집됨
4. 최적화 소요 시간 단축 전략
   1. Adaptive search strategy : 예상 쿼리 수행 시간과 쿼리 최적화에 소요되는 시간이 일정비율을 넘지 않도록 적응적 탐색 전략 사용
   2. Multiple Initial orderings heuristic : 조인 순서는 최적의 실행계획을 발견할 가능성이 높은 순서대로 비용 산정해서 평가

III. Row-Source Generation

1. 역할 : 생성된 실행계획을 실행 가능한 코드 또는 프로시저 형태로 포맷팅하는 작업을 담당
2. Row-Source : 사용자가 요구한 최종 결과집합을 실제적으로 생성하는데 사용되는 제어 구조

참고 문서

• 서적(오라클 성능 고도화 원리와해법 I)

문서정보

• 이 문서는 오라클클럽에서 작성하였습니다.
• 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• 출처 : http://www.gurubee.net/pages/viewpage.action?pageId=4948273&
• 오라클클럽 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

출처 : http://www.gurubee.net/pages/viewpage.action?pageId=4948273

Optimizer

왜 우리는 CBO의 작동원리를 알아야하는가? optimizer가 아주 잘못된 실행계획을 생성하여 어떤 문제가 발생했을 때, 그 문제를 제대로 파악하고 올바른 해결책을 제시하기 위함이다!! CBO의 근본동작을 교정하면 한 번의 조치로 문제가 발생하는 모든 경우를 해결 할 수 있다.           -- Jonathan Lowis【Cost-Based Oracle Fundamentals】 왜 optimizer에 대해서 알아야 할까? CBO 작동의 핵심 기능인 optimizer를 알아야 문제를 파악하고 해결책을 제시할 수 있다!!           -- 내 생각~~^^*

What's the Optimizer?

• 최소비용, 최적의 경로를 선택해서 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 프로시저를 자동으로 생성해는 DBMS의 핵심 기능

• 목표 : 가장 효율적인 Execution Plan을 찾아내는 일

• 한계
  1. Computing power의 부족
  2. Optimizing algorithm의 한계
  3. 제한된 시간 내에 방법 결정
  4. User data에 대한 이해 부족
  5. 실행방법을 결정하는데 있어서 오판이 많음

• 실행계획(Execution Plan) : 옵티마이저에 의해 생성된 처리절차를 사용자가 확인할 수 있도록 트리구조로 표현한 것

• 사람이 SQL 실행 ⇒ 옵티마이저가 결과를 얻기 위해 실행계획 및 프로시저(프로그래밍) 생성 ⇒ 사용자가 원하는 결과

• 예제
  
  
  

최적화 수행 단계

1. 실행계획 후보군 선별
   
2. 실행계획의 예상비용 산정 : data dictionary에 미리 수집해 놓은 object 통계 및 시스템 통계정보 이용
   
3. 최소비용의 실행계획 선정

참고 문서

• 서적(오라클 성능 고도화 원리와해법 I)

문서정보

• 이 문서는 오라클클럽에서 작성하였습니다.
• 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• 출처 : http://www.gurubee.net/pages/viewpage.action?pageId=4948105&
• 오라클클럽 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

출처 : http://www.gurubee.net/pages/viewpage.action?pageId=4948105

The HOLD_CURSOR and RELEASE_CURSOR Options