데이터베이스 (2017-2)
SQL 파싱과 실행계획 13
a. SQL의 수행
b. 파싱의 종류와 특징
c. 옵티마이저와 SQL 실행계획
데이터베이스 (2017-2)
SQL의 실행 순서 (최초 수행시)
1. 파싱 (Parsing)
• 문법 오류(Syntex Error) 체크
• 의미 오류(Semantic Error) 체크
• 파싱 수행 (하드 파싱)
• Hash 및 커서를 Library Cache에 등록 (이후 소프트 파싱)
2. 최적화 (Optimization)와 실행 계획 수립 (Row Generation Plan)
• 탐색 트리 생성 및 실행 계획 수립
3. 실행 (Execution)
• Server Process가 필요한 자원을 취득한 뒤 작업을 수행
4. 추출 (Fetch)
• 결과 값을 반환
1
데이터베이스 (2017-2) 2
Shared Pool 구조
Shared Pool
Library Cache Shared SQL Area ( SQL 쿼리/실행 계획 )
Data Dictionary Cache
Result Cache Reserved Pool
SQL 또는 PL/SQL 코드를 모두 보관하는 장소 사용된 SQL 및 실행계획이 파싱 되어 보관됨.
Select * from customer where id=‘005’
Hard Parsing Soft Parsing 해당 SQL이 Library
Cache에 존재하는가 ?
No Yes
테이블/인덱스등의 Ojbect 정의, 사용자명, Role , 권한등의 정보를 보관하는 장소
주로 사용자가 해당 Object에 접근 가능 권한이 있는지 확인하는데 사용
사용자 SQL의 결과 값을 지속적으로 보관 동일한 SQL이 요청될 경우 Buffer 나 Disk I/O Access 작업을 수행하지 않고 Result Cache에 있는 값을 그대로 반환
데이터베이스 (2017-2) 3
Hard Parsing 과 Soft Parsing
Hard Parsing Soft Parsing
SQL Parser Query
Transformer Estimator Plan Generator
Schema
Objects Statistics
생성된 실행계획을 Library Cache에 보관
SQL 및 실행계획이 Library Cache에 존재하지 않아 SQL의 실행계획을 처음부터 Parsing, Transforming ,
Estimating , Plan Generation 등의 복잡한 단계를 거쳐서 만들어 지는 Parsing
SQL Parser
Object 에 대한 사 용자 접근 권한
기존에 Library Cache에 있는 SQL 실행계획 사용
SQL 및 실행계획이 Library Cache에 존재하므로 해당 Object 에 대한 사용자 접근 권한정도만 확인한 뒤에 Library Cache 에 있는 SQL 실행 계획을 그대로 사용하는 Parsing
• 존재하는 코드를 재사용할 수 없을 때 – 새로운 실행 가능한 코드가 필요할 때의 명령
• Hard Parse 혹은 Library cache miss라고도 함
• DDL에 대해서는 언제나 Hard Parse가 됨
• library cache와 data dictionary cache에 여러번 접근 하게 되며, latch를 요청하게 됨
• Hard Parse가 아닌 모든 parse
• 기존의 코드를 재사용하는 것
• Library cache hit이라고도 함
• soft parse가 optimization과 row source generation 단계를 거치지 않고 바로 실행으로 직행하기 때문에, 일반적으로 hard parse보다 선호됨
데이터베이스 (2017-2) 4
Overview of SQL Processing
SQL 구문
Syntax Check
Semantic Check Shared
Pool Check
Optimization
Row Source Generation
Execution
데이터베이스 (2017-2)
SQL 구문
Syntax Check
Semantic Check Shared
Pool Check
Optimization
Row Source Generation
Execution
5
Overview of SQL Processing
• 구문의 철자가 맞는지(SELECT, FROM 등) 검사
• SQL의 의미가 적합한지(작성된 테이블의 실존 여부 등) 검사
데이터베이스 (2017-2)
SQL 구문
Syntax Check
Semantic Check Shared
Pool Check
Optimization
Row Source Generation
Execution
6
Overview of SQL Processing
• 보통DB는 SQL에 대한 Hash값을 갖고 있는데, 이것을 통 해 Hard/Soft Parsing 여부가 결정됨
• 입력된 SQL을 Hash 하여 Library cache에 해당되는 값 이 있는지를 검사
• Library cache hit시, 바로 실행
• Library cache miss시, 최적화 단계로 넘어감
데이터베이스 (2017-2)
Overview of SQL Processing
7
• 언급된 내용으로, 최소의 비용으로 해당 SQL을 실행할 수 있는 방법에 대한 결정을 진행함
• optimizer로 부터 최적의 실행 계획을 받아서 query plan이라고 불리는 순차적인 계획을 생성하는 소프트웨어로 이진 프로그램 임
• 보통 query plan은 몇 단계의 row set을 반환하는 단계의 조합 으로 이루어 짐 – 이것은 SQL이 실행되는 동안 여러 단계에서 사용될 수 있음
• row source는 각 단계에서 반환된 row set으로, 여러가지 형태 의 schema object로 나타날 수 있음
• 최종적으로 row source tree를 생성하는데, 이것은 row source 의 조함이고, 다음 정보를 담고 있음
• 구문이 참조하는 테이블의 순서
• 구문이 언급하는 테이블에 대한 접근 방법
• 구문이 언급한 join문의 테이블에 대한 접근 방법 • 데이터에 대한 필터링이나 정렬 등의 명령
SQL 구문
Syntax Check
Semantic Check Shared
Pool Check
Optimization
Row Source Generation
Execution
데이터베이스 (2017-2) 8
Overview of SQL Processing
• 실행시, SQL 엔진은 row source generator가 생성한 execution tree(parse tree)를 이용하여 실행을 진행함 – 이 과정은 DML의 경우에만 필수적으로 요청됨
• tree구조를 갖고, reverse하게 진행됨
• 각 박스는 어디에 어떻게 접근할지에 대한 정보를 갖고 있음
• 어느 단계는 어떤 단계들이 실행 된 것을 바탕으로 실행될 수 있는지에 대한
정보도 트리구조로 인하여 알 수 있음
메모리에 없는 데이터 호출시, 디스크에서 값을 읽어오게 되고, 이 때 데이터 정합성을 보장하기 위해 락과 래치 등을 필요로 함
SQL 구문
Syntax Check
Semantic Check Shared
Pool Check
Optimization
Row Source Generation
Execution
데이터베이스 (2017-2) 9
Optimizer SQL 실행계획 수행 절차
SQL Parser Query
Transformer Estimator Plan Generator
Row Source Generator
Parsed
Query Transformed
Query Query +
Estimates Query
Plan
Schema Objects Statistics
• 데이터 딕셔너리의 통계정보(데이터의 분포도, 테이블 저장구조, 인덱스 구조, 파티션 형태, 비교연산자 등을 감안하여 각 실행계획의 비용을 계산
• 사용자가 실행한 SQL은 데이터 딕셔너리를 참조하여 파싱을 수행.
• 옵티마이져는 파싱 결과를 이용해 논리적으로 적용 가능한 실행계획 형태를 선택하고,힌트를 감안하여 일차적으로 잠정적인 실행계획들을 생성
• 실행계획들의 산출된 비용을 비교하여 가장 최소의 비용을 가진 실행계획을 선택(최저가 입찰 방식이므 로 항상 최적의 결정이라고만 할 수는 없음)
Syntax Check &
Semantics Analyze
1
2
3
데이터베이스 (2017-2)
LAB: SQL 실행계획 확인
10
PROF> set autotrace on explain PROF> select * from emp;
Execution Plan
--- Plan hash value: 3956160932
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---
| 0 | SELECT STATEMENT | | 14 | 532 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 532 | 2 (0)| 00:00:01 |
---
데이터베이스 (2017-2)
LAB: SQL 실행계획 확인
11 PROF> select ee.empno, ee.ename, ee.job, d.dname, em.ename from emp ee, emp em, dept d where ee.deptno =
d.deptno and ee.mgr = em.empno;
Execution Plan
--- Plan hash value: 453895177
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---
| 0 | SELECT STATEMENT | | 13 | 624 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 13 | 624 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN | | 13 | 494 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 13 | 325 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 140 | 2 (0)| 00:00:01 | --- Predicate Information (identified by operation id):
--- 1 - access("EE"."MGR"="EM"."EMPNO") 2 - access("EE"."DEPTNO"="D"."DEPTNO") 4 - filter("EE"."MGR" IS NOT NULL)
데이터베이스 (2017-2)
