SQL 실행과정

1. SQL Parser

• 사용자가 SQL문을 요청하면 맨 먼저 Paser엔진이 SQL문을 받아서 parsing 한다.
• SQL문장을 개별 구성요소를 분석하고 파싱해서 파싱트리를 만든다.(syntax체크, semantic체크)
• 해싱 알고리즘을 이용해서 SQL 커서(SQL 구분 및 실행계획등 SQL이 실행하기 위한 정보가 있는 AREA)가 Shared Pool에 캐시되어 있는지 확인한다.
• SQL 커서를 찾았다면 바로 실행단계로 넘어가고, 최적화가 필요하면 옵티마이저에게 넘긴다.

2. Optimizer

• SQL Transformer : 최적화하기 쉬운 형태로 변화 시도.
• Plan Generator : 실행하기 위해 후보군이 될만한 실행계획 생성
• Estimator : 선택도, 카디널리티(전체row수중 출력되는 예상row수), 비용을 계산, 총 비용 계산

3. Row-Source Generator : Optimizer가 생성한 실행계획을 실행가능한 형태로 포맷팅 한다.

4. SQL Engine : SQL를 실행

Soft Parsing

1. 사용자가 SQL수행 요청
2. 기본적인 문법 체크 및 권한 적용
3. SQL에 대해 Hash 함수 적용 후 Bucket 할당
4. library cache latch 획득 후 LCO탐색, parse count(total) 통계 증가
5. 동일한 LCO 발견
6. SQL커서에 대해 library cache lock/pin을 shared모드로 획득하고 SQL문장을 실행 (DDL문은 해당 객체에 해당하는 LCO에 대해서 library cache lock/pin를 exclusive모드로 획득)

Hard Parsing

1. 사용자가 SQL수행 요청
2. 기본적인 문법 체크 및 권한 적용
3. SQL에 대해 Hash 함수 적용 후 Bucket 할당
4. library cache latch 획득 후 LCO탐색, 동일한 LCO 발견 실패
5. shared pool latch를 획득하고 freelist에서 free chunk 검색
6. 적절한 free chunk를 찾으면 library cache handle에 대해 library cache lock을 exclusive모드로 획득
7. LCO정보를 생성
8. library cache lock을 null모드로 변경하고, library cache pin을 exclusive모드로 획득한 후 실행계획 생성

과도한 Hard Parsing의 문제점

1. 실행계획 생성시 과다한 CPU 부하 유발

• 인덱스 구조, 조인방법등에 따라 복잡도가 증가한다.
• OPTIMIZER_MAX_PERMUTATIONS : 옵티마이저 플랜 관련 파라메터로 옵티마이저가 고려할 수 있는 테이블 조인 순서의 최대값 설정

2. library cache chain 확장으로 인한 검색 시간 증가

• 새로운 커서를 생성하므로 library cache chain의 길이 증가
• 체인에 대한 검색 시간 증가 ( 체인 검색 시간을 줄이기 위해서 shared pool 크기를 감소시키는 방법도 있음)

3. shared pool 딘편화

• free chunk 확보과정에서 chunk가 작은 크기로 쪼개지면서 단편화 발생
• 단편화에 따라 chunk수가 증가하고, 체인 길이도 증가하여 체인 검색 속도의 저하 유발
• chunk 크기가 작아져서 필요한 chunk를 찾지 못하는 문제 발생 확률 증가( ORA-4031발생 )

과도한 Hard Parsing을 발생시키는 Literal SQL의 성능 개선 방법

1. Cursor Sharing 파라메터 설정 변경 : 참고

• alter session set cursor_sharing = force;

2. Bind 변수로 변경 ( using, static sql )

과도한 Soft Parsing의 문제점

1. library cache chain 탐색 반복
2. library cache latch 보유시간 증가

문서에 대하여

• 최초작성자 : ~kwlee55
• 최초작성일 : 2010년 09월 07일
• 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 (주)엑셈에서 출간한 'PRACTICAL OWI IN ORACLE 10G'와 'Advanced OWI in Oracle 10g'를 참고하였습니다.*