SQL 공유 및 재사용

시스템 전역 영역(SGA, System Global Area)

시스템 전역 영역(SGA, System Global Area)은 주로 데이터 베이스 시스템에서 사용되는 용어이다.

SGA는 데이터베이스 인스턴스가 시작될때 할당되는 공유 메모리 영역으로, 데이터 베이스 인스턴스의 모든 프로세스에서 공유된다.

SGA는 데이터베이스에서 자주 엑세스되는 데이터와 제어구조를 캐싱하는데 사용되는 여러 구성요소가 포함된다.

SGA는 주로 데이터 베이스 시스템의 성능을 향상시키는데 사용되며, 데이터베이스가 실행 중일 때 계속해서 메모리를 차지하고 있다. 이러한 메모리 영역은 데이터베이스 인스턴스에 특화되어 있으며, 다른 응용 프로그램이나 시스템에서 사용되는 메모리와는 구분된다.

따라서, "시스템 전역 영역(SGA)"은 주로 데이터베이스 시스템에서 사용되는 메모리 영역을 가리키는 용어이다.

 

 

해당 자원에는 DB Buffer Cache, Redo Log Buffer, Shared Pool, Library cache, Data dictionary Cache가 있다.

• DB Buffer Cache : 데이터베이스에서 자주 엑세스되는 데이터 블록을 캐시하여 읽기 작업의 성능을 향상시키는 데 사용된다
• Redo Log Buffer: 트랜잭션 변경 내용을 임시로 저장하는 버퍼로, 데이터베이스 일관성을 유지하는데 사용된다,
• Shared Pool: SQL 문장의 파싱 정보, 공유 SQL 영역, 데이터베이스 오브젝트의 메타데이터 등을 저장하는 메모리 영역이다.
• Library Cache: SQL 문장의 파싱된 정보, 최적화된 SQL 등이 저장되는 공유 메모리 캐시이다. 이는 SQL 문장을 실행할 때 매번 파싱하는 비용을 줄여준다(소프트 파싱)
• Data Dictionary Cache: 테이블, 뷰, 인덱스 등의 데이터베이스 객체에 관한 메타데이터가 저장되는 캐시입니다. 이를 통해 데이터베이스 객체에 대한 정보를 빠르게 검색할 수 있음. 해당 객체 존재하는지, 객체를 접근할 수 있는 권한이 있는지, 객체의 구조와 속성, SQL 파싱 전 사전 분석 활용(옵티마이저가 활용함)

 

 

---

소프트 파싱 vs 하드 파싱

소프트 파싱:

SQL 문장이 실행될 때 최적화 과정을 거치지 않고, 이미 라이브러리 캐시(library cache)에 있는 SQL 문장을 정보를 사용하여 실행하는 것을 말한다. 이를 통해 사용자가 실행한 SQL이 라이브러리 캐시에 존재할 경우, 최적화 과정을 거치지 않고 해당 SQL의 실행 계획을 바로 가져와 실행한다.

 

하드 파싱:

하드 파싱은 새로운 SQL 문장이나 이전에 실행되지 않는 SQL 문장에 대해서 발생한다. 즉 라이브러리 캐시에 해당 SQL이 존재하지 않기에 옵티마이저가 직접 SQL 문을 파싱하고 최적화하여 실행 계획을 생성합니다. 이러한 최적화하는 과정은 많은 자원을 사용함으로 CPU의 사용량이 높아질 수 있음. 

 

 

아래 파란색 화살표 방향이 소프트 파싱의 과정이고 빨간색 화살표가 하드 파싱의 과정을 설명한 과정이다.

 

하드 파싱을 하는 경우 옵티마이저 실행 계획을 최적화 하는 과정을 가지는데 이때 사용하는 정보들은 아래와 같습니다.

1) 테이블, 칼럼, 인덱스 구조 관한 기본 정보 : SQL 문장이 어떤 테이블과 칼럼을 사용하고 있는지, 어떤 인덱스가 존재하는지 등의 정보를 고려합니다.

2) 오브젝트 통계: 테이블과 인덱스 크기, 행, 수 칼럼의 분포 등의 통계 정보를 활용하여 실행 계획 결정합니다

3) 시스템 통계: 시스템의 하드웨어 및 I/O 성능에 관한 정보를 고려하여 실행 계획을 조정합니다.

4) 옵티마이저 관련 파라미터: 옵티마이저 동작을 제어하는 파라미터 값을 고려하여 최적의 실행 계획을 수립합니다.

 

이러한 정보들을 고려하여 옵티마이저가 실행 계획을 최적화하는 과정은 많은 자원을 필요로 하며, CPU를 많이 소비하는 작업입니다. 이러한 이유로 SQL 문장을 반복해서 실행할 때마다 매번 하드 파싱을 수행하는 것은 효율적이지 않습니다. 따라서 라이브러리 캐시가 존재하여 이미 파싱된 SQL 문장의 정보를 재사용함으로써 소프트 파싱을 통해 성능을 향상시킵니다.

 

---

바인드 변수의 중요성 

사용자 정의 함수/프로시저, 트리거 패키지 등

사용자가 정의한 함수, 프로시저, 트리거, 패키지 등은 생성될 때 즉시 이름을 가지며, 내용이 변경되어도 이름은 동일합니다. 이러한 객체들은 데이터 사전(data dictionary)에 기록되어 사용자가 삭제하지 않는 한 영구적으로 보관됩니다. 실행할 때는 라이브러리 캐시(library cache)에 적재되어 효율적으로 관리됩니다.

 

 

 

SQL(Statement vs PreparedStatement)

SQL 문장은 이름이 따로 존재하지 않으며, 문장 전체가 그 자체로 식별자입니다. 이로 인해 SQL 문장의 어느 부분이라도 변경되면 새로운 객체가 생성됩니다. 또한, 오라클 10g부터 도입된 SQL ID는 SQL 문장 전체 텍스트와 1:1 관계를 갖기 때문에 문장이 변경되면 해당 SQL ID도 변경됩니다.

Statement를 사용하는 경우, SQL 문장의 형태가 조금이라도 다르면 각각의 쿼리로 인식되어 매번 하드 파싱을 수행합니다. 하지만 PreparedStatement를 사용하는 경우에는 대소문자나 공백 등의 차이를 무시하고, SQL 문장의 내용이 동일하다면 하나의 하드 파싱만 수행하고 이후에는 소프트 파싱을 실행할 수 있습니다. 그렇기 때문에 동일한 쿼리를 여러 번 실행하는 경우에는 PreparedStatement를 사용하는 것이 성능상 이점을 가져다 줍니다.