[ORACLE] SQL Parsing

SQL은 Oracle Instance Memory (SGA 내 Shared Pool) / 보조 TBS(SYSAUX)에 저장되고 Dictionary View를 조회해 SQL에 대한 통계나 성능을 확인할 수 있다.

 

Parse

: SQL문의 구문(syntax)과 의미(semantics)를 검증함과 동시에, 문장을 질의한 사용자 계정에 대해 오브젝트 접근 권한을 확인함. 이 단계에서 오라클은 실행 SQL문과 계획 정보 등을 저장하기 위해 PGA도 할당함

 

SQL 저장 방식

1. 사용자(Server Process)가 SQL을 이용해 database에 질의 요청
2. Database는 요청된 SQL을 Parsing (hard / soft)
3. Hard Parsing된 SQL의 cursor 정보가 Shared Pool에 Caching되고, 동일한 SQL요청 시 해당 Cursor를 사용해 Soft Parsing 수행
4. 1~3번의 과정이 반복 수행되고 일정한 시간이 흐르면, MMON Process에 의해 SYSAUX TABLESPACE에 저장됨

 

위 과정을 통해  SQL이 Oracle Database에 저장되고 활용할 수 있게 됨

 

SQL 커서 정보는 메모리와 TBS에 저장되는데,

TBS에 저장된 커서 정보는 데이터베이스가 종료되도 유지됨

Memory에 있는 커서는 장시간동안 재호출이 없거나 재기동 되면 사라짐

 

SQL 저장 정보는 온라인 / 오프라인 정보로 나뉨

온라인 정보 : V$SQL 등의 Dictionary View를 통해 정보 추출

오프라인 정보 : DBA_HIST_* VIEW를 활용해 정보 추출

 

SQL이 처음 수행되면, Optimizer는 최적의 경로를 찾기 위해 Parsing을 수행하고, 실행계획을 생성한다. 실행계획에서 sql이 어떻게 수행되어야 하는지 정보가 들어감

실행계획은 Memory에 저장되고 V$SQL_PLAN 에서 확인할 수 있다.

 

Literal SQL

bind 변수 없이 수행된 1회성 SQL로 한번 사용된 후 재사용되지 않아서 메모리 공간을 낭비하게 되고 빈번한 Hard Parsing으로 성능 지연의 원인이 됨

 

FORCE_MATCHING_SIGNATURE

정규화된 (공백제거 및 non-literal string의 대문자화) SQL Text를 Hasing한 값

bind변수와 literal query가 혼합하여 사용될 때 force_matching_signature가 달랐던 것이 19.14부터는 사라짐

 

 

V$SQL

 

Memory에 Access할 수 있는 딕셔너리 뷰로, SQL에 관련된 정보를 추출

Parsing User 중 SYS, SYSTEM을 제외한 SQL들을 수행 속도(ELAPSED_TIME)로 분류하여 결과 표시

ELAPSED_TIME : CPU TIME + WAIT TIME

CPU TIME : Cache에서 SQL을 처리한 시간

WAIT TIME : I/O, Application, Concurrency, Cluster(gc) … wait

ex) table을 읽기 위해 10만 블록을 읽어야 하는데, 총 10초가 걸렸다 -> 여기서 디스크I/O WAIT이 느려서 메모리로 올리는데 8초를 소비했다면 CPU에서 SQL수행된 시간은 2초라는 뜻

PROGRAM_ID = OBJECT_ID

PROGRAM_LINE# = OBJECT 몇번째 LINE에서 call한 SQL인지 확인가능

 

 

시스템에 성능 문제가 발생 시 FULL SCAN 이라는 용어를 많이 사용함 -> Oracle 실행계획에서 segment (Table / Index)를 처음부터 끝까지 전부 읽는 방식

select * from v$sql_plan where options like '%FULL%' ;