SQLP 기초 - 1. SQL 처리 과정 : 파싱, 최적화, 캐싱

1. SQL 파싱과 최적화

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/sql-processing.html#GUID-8CF633B1-EAC4-47C7-9189-C479ADEF1FFA

- 파싱(parsing) : Syntax 체크(문법 체크) - Semantic 체크(의미 체크) - Shared Pool 체크(공유 메모리에 캐시 되어 있는지 체크) 과정, 최적화 이전 단계.

- 최적화 : DBMS 내부에서 Optimizer가 각종 정보를 가지고 다양한 실행경로 중 효율적인 하나를 선택하는 단계.

- 최적화 이후 로우 소스(실행가능한 코드 혹은 프로시저)를 생성한다.

 

2. Optimizer

- 최적의 데이터 엑세스 경로를 선택해 주는 DBMS의 핵심 엔진(별도의 백그라운드 프로세스가 아닌 서버 프로세스가 가진 기능임)

*컴퓨터 프로그래밍에서 엔진(engine)이란 특정 프로그램 내부에서 또는 다른 프로그램들을 위해 핵심적이고 본질적인 기능을 수행해주는 프로그램

- 옵티마이저의 최적화 단계

1. 실행계획 찾기
2. 데이터 딕셔너리 및 시스템 통계 정보 등을 이용한 예상비용 산정
3. 최저 비용의 실행계획 선택 (이를 cost-based 라고 칭한다)

3. 실행계획과 COST

- 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 아래와 같이 트리 구조로 표현한 것

- INDEX UNIQUE SCAN을 하는 것을 확인할 수 있다.

- COST는 쿼리를 수행하는 동안 발생할 것으로 예상되는 I/O 횟수 또는 예상 소요시간을 표현한 값.

- COST는 예상치이므로 실제 수행 시 발생하는 COST와는 다르다.

 

- 힌트를 이용해 데이터 엑세스 경로를 바꿀 수 있다.

- TABLE FULL SCAN을 유도해서 COST가 많이 늘어난 것을 볼 수 있다.

 

4. SQL 공유 및 재사용

- SGA는 서버 프로세스와 백그라운드 프로세스가 공통으로 엑세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간이다.

- 최적화 과정을 통해 생성한 내부 프로시저를 재사용하도록 담아두는(캐싱하는) 공간을 라이브러리 캐시(Library Cache)라고 한다.

- 라이브러리 캐시는 SGA의 구성요소이다.

SGA 구조

- 사용자가 SQL 실행 시 SQL이 SGA에 존재하면, 즉 라이브러리 캐시에 존재하면 그대로 그것을 사용(소프트 파싱)하고, 없다면 최적화 후 로우 소스를 생성하는 단계를 거친다(하드 파싱).

- 하드 파싱은 CPU를 많이 소비하는 작업이라 SQL의 재사용성이 DBMS의 성능에 매우 중요한 영향을 끼친다.

 

 

5. SQL의 바인드 변수

- DBMS는 SQL문 통째로 캐시에 담아둔다. 즉 SQL에 이름이 없고 SQL문 자체가 이름(식별자)이다. 즉 WHERE 조건절에 파라미터가 자꾸 변하면 하드 파싱을 하게 된다. 따라서 WHERE 조건절의 파라미터를 바인드 변수로 둔다면 같은 SQL로 취급하여 소프트 파싱을 하여 자원 낭비를 줄일 수 있다. 

 

- 1번, 2번 SQL은 서로 다른 SQL로 1번 실행 후 2번 SQL을 실행하면 하드 파싱이 일어난다.

- 하지만 3번과 4번 SQL은 서로 같은 SQL로 보기 때문에 CUST_ID라는 변수에 12345, 12346 다른 변수가 들어와도 하드 파싱을 하지 않고 캐시에서 꺼내와 사용한다(소프트 파싱). 이런 방식을 parameter-driven 방식이라고 한다.