SQL Processing

출처 : https://docs.oracle.com/database/121/TGSQL/tgsql_sqlproc.htm#TGSQL183

출처 : https://o2sunn.tistory.com/4

 

SQL Processing

 

About SQL Processing

SQL 처리는 Parsing, Optimization, Row Source Generation, Execution 단계로 되어있다. 명령문에 따 라 데이터베이스는 이 단계들 중 몇몇 단계를 생략할 수 있다. 다음 그림은 SQL처리하는 일반적인 단계 를 보여준다.

SQL 문장이 수행되는 과정

 

1. User Process는 자신이 가져온 SQL 문장을 Server Process에 전달해주고 결과가 나올 때까지 기 다린다.

2. SQL문장을 받은 Server Process는 Syntax Check, Semantic Check를 한 후 권한검사를 한다.

3. Shared Pool의 Library Cache를 검사하여 실행 계획이 있는지 확인한다.

4. 실행 계획이 있을 경우 Execution 단계로 진행하고(Soft Parse), 실행 계획이 없을 경우 옵티마이져 를 찾아가 실행계획을 만들어 달라고 요청한다.(Hard Parse)

 

SQL 구문분석(SQL Parsing)

 

SQL 처리의 첫 번째 단계는 구문분석(Parsing) 이다.

데이터베이스는 애플리케이션의 지시에 따라 명령문을 구문분석(parse)한다. 즉, 데이터베이스 자체가 아닌 애플리케이션만 구문분석 수를 줄일 수 있다.

애플리케이션이 SQL문을 실행하면 애플리케이션은 데이터베이스에 대한 parse call 을 수행하여 명령 문을 실행할 준비를 한다.

parse call 은 구문분석된(parsed) SQL문과 다른 처리 정보를 보유하는 세션별 private SQL area의 핸 들인 커서(cursor)를 열거나 생성한다.

커서(cursor) 및 private SQL area은 PGA(Program Global Area)에 있다.

 

parse call 중에 데이터베이스는 다음 검사를 수행한다.

- Syntax Check

- Semantic Check

- Shared Pool Check

 

Syntax Check

 

오라클 데이터베이스는 구문적 타당성(syntactic validity)을 위해 각 SQL 문을 확인해야 한다.

올바른 형식의 SQL구문에 대한 규칙을 위반하는 명령문은 이 검사에서 실패한다.

예를 들어, FROM 키워드의 철자가 FORM으로 잘못된 다음 명령문은 Syntax Check에서 실패하고 에러 가 발생한다.

SQL> SELECT * FORM employees;
SELECT * FORM employees
          *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

 

Semantic Check

 

Semantic Check는 명령문의 개체(object)와 열(Column)이 존재하는지와 같이 명령문이 의미가 있는지 여부를 확인합니다.

구문적으로 올바른 명령문이어도 다음 쿼리 예시에서와 같이 Semantic Check를 통과하지 못할 수 있다.

SQL> SELECT * FROM nonexistent_table;
SELECT * FROM nonexistent_table
                *
ERROR at line 1:
ORA-00942: table or view does not exist

 

Shared Pool Check

 

구문 분석 중에 데이터베이스는 Shared Pool Check를 수행하여 리소스 집약적인 명령문 처리 단계를 건 너뛸 수 있는지 여부를 확인한다.

이를 위해 데이터베이스는 해시 알고리즘을 사용하여 모든 SQL문에 대한 해시 값을 생성한다. 명령문 해시 값은 V$SQL.SQL_ID에 표시된 SQL ID이다. 이 해시 값은 Oracle Database Version 내에서 결정적 이므로 단일 인스턴스 또는 서로 다른 인스턴스의 동일한 명령문이 동일한 SQL ID를 가진다.

사용자가 SQL문을 제출하면 데이터베이스는 shared SQL area을 검색하여 기존의 구문분석문(parsed statement)이 동일한 해시 값을 가지고있는지 확인한다. SQL문의 해시 값은 다음 값으로 구별된다.

 

- 명령문의 메모리주소(Memory address for the statement)

Oracle Database는 SQL ID를 사용하여 조회 테이블에서 키 읽기를 수행한다. 이런 방식으로, 데이 터베이스는 명령문의 가능한 메모리 주소를 획득한다.

 

- 명령문에 대한 실행계획의 해시 값(Hash value of an execution plan for the statement)

SQL문은 Shared Pool에 여러 개의 계획을 가질 수 있다. 일반적으로 각 계획은 해시 값이 다르다. 동일한 SQL ID에 여러 계획 해시 값이 있는 경우, 데이터베이스는 이 SQL ID에 대해 여러 계획이 존 재함을 알고 있다.

 

구문 분석 작업은 제출된 명령문의 유형과 해시 검사 결과에 따라 다음 범주에 속한다.

 

- Hard parse

 

Oracle Database가 기존 코드를 재사용할 수 없는 경우 새로 실행할 수 있는 버전의 애플리케이션 코드를 구성해야 한다. 이 작업을 hard parse 또는 library cache miss 라고 한다.

 하드 구문 분석(hard parse) 중에 데이터베이스는 Data Dictionary를 확인하기 위해 Library Cache와 Data Dictionary Cache에 수없이 접근한다. 데이터베이스가 이러한 영역에 접근 할 때, 해당 정의가 변 경되지 않도록 필요한 개체에 대한 래치(latch)라는 직렬화 장치를 사용한다. 래치 경합은 명령문 실행 시 간을 증가시키고 동시성을 감소시킨다.

 

- Soft parse

 

soft parse는 hard parse가 아닌 모든 구문 분석이다. 제출된 명령문이 Shared pool의 재사용 가능 한 SQL문과 동일하면 Oracle Database는 기존 코드를 재사용한다. 이러한 코드의 재사용은 Library hit 라고도 한다.

Soft parse는 작업량에 따라 달라질 수 있는데, 세션 shared SQL area을 구성하면 소프트 구문 분 석(soft parse)의 래칭 양이 줄어 더 "softer"해진다.

일반적으로 데이터베이스가 optimization 및 row source generation 단계를 건너뛰고 바로 실행 으로 진행되기 때문에 hard parse 보다 soft parse 가 더 바람직하다.

 

다음 사진은 dedicated server architecture에서 UPDATE 문의 Shared Pool Check를 단순화 한 것이다.

만약 검사가 Shared pool의 명령문이 동일한 해시 값을 갖는다면, 데이터베이스는 명령문들의 의미가 같은지 여부를 결정하기 위해 의미론적 및 환경적 검사를 수행한다.

 

동일한 구문이 충분하지 않다면 다음과 같이 예시를 들어 확인해보자. 예를 들어, 서로 다른 두 명의 사 용자가 데이터베이스에 로그인하고 다음 SQL 문을 발행한다고 가정한다.

CREATE TABLE my_table ( some_col INTEGER );
SELECT * FROM my_table;

두 사용자에 대한 SELECT문은 구문적으로 동일하지만 두 개의 개별 스키마 개체는 my_table로 명명됩 니다. 이 의미론적 차이는 두 번째 명령문이 첫 번째 명령문에 코드를 재사용할 수 없다는 것을 의미한다.

 

두 개의 명령문이 의미적으로 동일하더라도 환경적 차이는 하드 파싱을 강제할 수 있다. 이러한 맥락에 서 옵티마이져 환경은 작업영역크기 또는 옵티마이져 설정 (예 : 옵티마이져 모드)과 같이 전체적으로 실 행 계획 생성에 영향을 미칠 수 있는 세션 설정이다.

 

단일 사용자가 실행한 다음 일련의 SQL 문을 보자.

 

ALTER SESSION SET OPTIMIZER_MODE=ALL_ROWS;
ALTER SYSTEM FLUSH SHARED_POOL;                -- optimizer environment 1
SELECT * FROM sh.sales;

ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS;   -- optimizer environment 2
SELECT * FROM sh.sales;

ALTER SESSION SET SQL_TRACE=true;              -- optimizer environment 3
SELECT * FROM sh.sales;

 앞의 예시에서는 동일한 SELECT문이 세 가지 다른 옵티마이져 환경에서 실행된다. 따라서 데이터베이 스는 이러한 명령문에 대해 세 개의 개별 shared SQL area을 생성하고 각 명령문을 하드 구문 분석(hard parse)한다.

 

SQL Optimization

 

 optimization 단계동안, Oracle Database는 모든 고유 DML문에 대해 적어도 한 번 하드 구문분석(hard parse)을 수행하고 이 구문분석 중에 optimization을 수행해야한다.

 데이터베이스는 최적화해야 하는 subquery와 같은 DML구성요소를 포함하지 않는한 DDL을 최적화하지 않습니다.

 

SQL Row Source Generation

 

 row source generator는 옵티마이져로부터 최적의 실행 계획을 받고, 나머지 데이터베이스에서 사용 할 수 있는 반복 실행 계획을 생성하는 소프트웨어이다.

 반복 계획은 SQL 엔진에 의해 실행될 때 결과 집합을 생성하는 이진(binary) 프로그램이다. 그 계획은 여 러 단계의 조합의 형태를 취한다. 각 단계는 행 집합(row set)을 반환한다. 다음 단계에서 이 집합의 행을 사용하거나, 마지막 단계에서 SQL문을 실행하는 애플리케이션에 행을 반환한다

 row source는 행을 반복적으로 처리 할 수있는 제어 구조와 함께 실행 계획 단계에서 반환된 행 집합이 다. row source는 테이블, 뷰 또는 조인 또는 그룹화작업의 결과일 수 있다.

 row source generator 는 row source의 집합인 row source tree를 생성한다. row source generator에 다음 정보가 표시된다.

- 명령문이 참조하는 테이블의 순서

- 명령문에 언급된 각 테이블에 대한 액세스 방법

- 명령문의 조인 작업에 영향을 받는 테이블에 대한 조인 방법

- 필터, 정렬 또는 집계와 같은 데이터 작업

 

Ex ) Execution Plan

 

 이 예시에서는 AUTOTRACE 가 활성화된 경우 SELECT문의 실행 계획을 보여준다. 그 명령문은 last name이 문자 'A'로 시작하는 모든 employees의 last name, job title, department name을 조회한다. 이 명령문의 실행 계획은 row source generator 의 출력이다.

 

SELECT e.last_name, j.job_title, d.department_name
FROM hr.employees e, hr.departments d, hr.jobs j
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
AND e.last_name LIKE 'A%';

Execution Plan
----------------------------------------------------------
Plan hash value: 975837011

--------------------------------------------------------------------------------
| Id| Operation | Name |Rows|Bytes|Cost(%CPU)|Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 189 | 7(15)| 00:00:01 |
|*1 | HASH JOIN | | 3 | 189 | 7(15)| 00:00:01 |
|*2 | HASH JOIN | | 3 | 141 | 5(20)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 60 | 2 (0)| 00:00:01 |
|*4 | INDEX RANGE SCAN | EMP_NAME_IX | 3 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | JOBS | 19 | 513 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
2 - access("E"."JOB_ID"="J"."JOB_ID")
4 - access("E"."LAST_NAME" LIKE 'A%')
    filter("E"."LAST_NAME" LIKE 'A%')

 

SQL Execution

 

 실행(Execution) 중에 SQL 엔진은 row source generator에 의해 생성된 트리에서 각 행 소스(row source)를 실행한다.

 

 다음 그림은 구문 분석 트리(parse tree)라고도 하는 실행 트리(execution tree)로, 이전 실행계획에서 행 소스의 흐름을 한 단계에서 다른 단계로 보여준다. 일반적으로 실행 단계의 순서는 계획 순서의 반대 이므로 아래에서 계획을 읽는다.

 실행 계획의 각 단계에는 ID 번호가 있다. 다음 그림의 숫자는 이전 실행계획에 표시된 계획의 ID 열에 해당한다. 실행계획의 Operation Column에 있는 앞의 공백은 계층적 관계를 나타낸다. 예를 들어, Operation의 이름 앞에 두 개의 공백이 선행되면 이 작업은 하나의 공백이 앞에 있는 Operation의 자식 이다. 하나의 공백이 선행하는 작업은 SELECT 문의 하위 항목이다.

 

- Row source tree

위 그림에서 트리의 각 노드는 행 소스 역할을 한다. 즉, 앞 실행 계획의 각 단계는 데이터베이스에서 행 을 검색하거나 하나 이상의 행 소스에서 행을 입력으로 받아들인다. SQL 엔진은 다음과 같이 각 행 소스 를 실행한다.

 

- 검은 상자로 표시된 단계는 데이터베이스의 개체(object)에서 데이터를 물리적으로 검색한다. 이러 한 단계는 데이터베이스에서 데이터를 검색하기 위한 접근경로 또는 기술이다. 

  - 6 단계에서는 full table scan을 사용하여 departments 테이블에서 모든 행을 검색한다.

  - 5 단계에서는 full table scan을 사용하여 jobs 테이블에서 모든 행을 검색한다.

  - 4 단계에서는 emp_name_ix 인덱스를 순서대로 스캔하여 문자 A로 시작하는 각 키를 찾고, 해당 rowid를 검색한다.        ex ) Atkinson에 해당하는 rowid는 AAAPzRAAFAAAABSAAe이다.

  - 3 단계는 employees 테이블에서 4 단계에서 rowid가 반환된 행을 검색한다.

    ex ) 데이터베이스는 rowid AAAPzRAAFAAAABSAAe를 사용하여 Atkinson에 대한 행을 검색한다.

 

- 흰 상자로 표시된 단계는 행 소스에서 작동한다.

  - 2 단계는 HASH JOIN을 수행하여 3 단계와 5 단계의 행 소스를 받고, 5 단계 행 소스의 각 행을 3 단계의 해당 행에 결합하고 결과 행을 1 단계로 반환한다.

    ex ) employee Atkinson의 행은 job name Stock Clerk와 결합된다.

  - 1 단계는 다른 HASH JOIN을 수행하여 2 단계와 6 단계의 행 소스를 받고, 6 단계 소스의 각 행 을 2 단계의 해당 행에 결합하여 결과를 클라이언트에 반환한다.

    ex ) Atkinson 직원의 행은 Shipping이라는 부서와 연결되어 있다.

 

 일부 실행 계획에서는 단계가 반복적이고, 다른 단계에서는 순차적이다. 앞 실행계획에 표시된 HASH JOIN은 순차적이다. 데이터베이스는 조인 순서에 따라 전체 단계를 완료한다. 데이터베이스는 emp_name_ix의 index range scan으로 시작된다. 데이터베이스는 인덱스에서 검색한 rowid를 사용하 여 employees 테이블에서 일치하는 행을 읽은 다음 jobs 테이블을 스캔한다. jobs 테이블에서 행을 검색 한 후 데이터베이스는 HASH JOIN을 수행한다.

 

실행(Execution) 중에 데이터베이스는 데이터가 메모리에 없으면 디스크에서 메모리로 가져와 데이터를 읽는다. 데이터베이스는 또한 데이터 무결성을 보장하는 데 필요한 lock 및 latch를 가져오고, SQL 실 행 중 변경된 사항을 기록한다. SQL문 처리의 마지막 단계는 커서(cursor)를 닫는 것이다.

 

SELECT 문장의 실행 원리

 

DML 문장의 실행 원리

- 모든 DML(Insert, Update, Delete) 문장의 수행 원리는 동일하다.

- SELECT문의 수행단계에서 Fetch 과정만 없고 나머지는 동일하다.

  (Parse, Bind 과정은 SELECT와 동일하며, Execute 과정은 SELECT문보다 복잡하다.)

 

Execute(실행)

 

 1. Database Buffer Cache를 살펴보고 없다면 데이터파일로 가서 해당 블록을 Database Buffer Cache로 복사한다.

 2. Server Process는 변경되는 데이터의 변경내역을 Redo Log Buffer에 기록한다.

     - Redo Log Buffer : 데이터가 변경될 때 만약의 장애를 복구하기 위해 변경내역을 기록해두는 공간

 3. Undo Segment에 이전(원본) 이미지를 기록한 후 Database Buffer Cache의 내용을 변경한다.

   => 데이터가 변경되는 것을 통틀어서 오라클에서는 Transaction(트랜잭션) 이라고 부른다.