[sql] Oracle DBMS의 구조와 SQL 처리 과정

1. Oracle DBMS (데이터베이스 관리 시스템)의 구조

---

Oracle DBMS (데이터베이스 관리 시스템)의 구조는 여러 주요 구성 요소로 이루어져 있으며, 데이터의 저장, 관리, 처리, 보안 등을 담당합니다. 

Oracle DBMS Architecture

다음은 Oracle DBMS의 기본적인 구조에 대한 요약입니다.

 

1. 인스턴스 (Instance)

Oracle DBMS의 인스턴스는 데이터베이스와 상호작용하는 메모리 구조와 백그라운드 프로세스를 포함합니다.

• 메모리 구조:
  • SGA (System Global Area): SGA는 데이터베이스 서버의 공유 메모리 영역으로, 데이터베이스 버퍼 캐시, 공유 풀, 리두 로그 버퍼 등 중요한 정보를 포함합니다. 데이터베이스 버퍼 캐시는 데이터베이스 블록을 메모리에 저장하여 디스크 I/O를 최소화하며, 공유 풀은 SQL 명령어와 PL/SQL 코드 등을 저장합니다. 리두 로그 버퍼는 트랜잭션의 변경 사항을 기록합니다.
  • PGA (Program Global Area): PGA는 각 사용자 프로세스에 대해 할당되는 메모리 영역으로, 해당 프로세스의 실행과 관련된 데이터와 정보를 저장합니다.
• 백그라운드 프로세스:
  • DBWR (Database Writer): DBWR은 데이터베이스 버퍼 캐시에서 디스크로 변경된 데이터를 주기적으로 기록하여 데이터의 안정성을 보장합니다.
  • LGWR (Log Writer): LGWR은 리두 로그 파일에 트랜잭션 변경 사항을 기록하여 데이터베이스의 복구를 지원합니다.
  • CKPT (Checkpoint): CKPT는 체크포인트를 관리하여 데이터베이스의 일관성을 유지하고, 복구 시점을 명확히 합니다.
  • SMON (System Monitor): SMON은 인스턴스의 복구 작업을 수행하고, 로그와 파일 시스템의 정리 작업을 담당합니다.
  • PMON (Process Monitor): PMON은 유휴 프로세스를 감지하고, 실패한 프로세스를 복구하여 데이터베이스의 안정성을 유지합니다.

2. 데이터베이스 (Database)

데이터베이스는 실제 데이터와 관련된 파일로 구성되어 있습니다.

• 데이터 파일: 데이터 파일은 테이블, 인덱스, 클러스터 등 실제 데이터가 저장되는 파일입니다. 하나의 데이터베이스는 여러 데이터 파일로 구성될 수 있습니다.
• 리두 로그 파일: 리두 로그 파일은 데이터베이스의 트랜잭션을 기록하여 데이터베이스의 복구를 지원합니다. 트랜잭션의 변경 사항이 로그 파일에 기록되어 시스템 장애 발생 시 데이터를 복구할 수 있습니다.
• 컨트롤 파일: 컨트롤 파일은 데이터베이스의 구조와 상태를 관리하는 중요한 파일로, 데이터베이스의 물리적 위치, 로그 파일의 위치 등을 기록합니다.

3. 논리적 구조

Oracle 데이터베이스는 논리적 구조를 통해 데이터를 조직적으로 관리합니다.

• 스키마: 스키마는 데이터베이스 내에서 사용자와 객체(테이블, 뷰, 인덱스 등)를 정의하는 논리적 구조입니다. 스키마는 데이터베이스 내의 개체들을 그룹화하여 관리합니다.
• 테이블: 테이블은 데이터베이스의 기본 저장 단위로, 행과 열로 구성되어 데이터를 저장합니다. 각 테이블은 특정 데이터를 구조화된 형태로 보관합니다.
• 뷰: 뷰는 하나 이상의 테이블에서 선택된 데이터를 기반으로 생성된 가상의 테이블입니다. 뷰는 데이터의 특정 부분을 추출하거나 가공하여 제공하는 역할을 합니다.
• 인덱스: 인덱스는 테이블의 데이터를 빠르게 검색할 수 있도록 돕는 구조입니다. 인덱스는 검색 성능을 향상시키지만, 데이터의 삽입과 삭제 시 추가적인 작업이 필요합니다.

4. 보안과 권한 관리

Oracle DBMS는 데이터베이스에 대한 접근과 작업 권한을 관리하여 보안을 유지합니다.

• 사용자 계정: 데이터베이스 접근을 제어하는 사용자 계정을 생성하여, 각 사용자가 수행할 수 있는 작업을 제한합니다.
• 권한: 사용자에게 특정 작업을 수행할 수 있는 권한을 부여하여 데이터베이스의 보안을 강화합니다. 권한 관리는 데이터베이스의 무결성과 보안을 유지하는 데 중요한 역할을 합니다.

5. 네트워크 구성

Oracle DBMS는 클라이언트와 데이터베이스 서버 간의 통신을 관리합니다.

• 리스너: 리스너는 클라이언트와 데이터베이스 서버 간의 연결을 관리하고, 클라이언트의 요청을 데이터베이스 서버로 전달합니다.
• 네트워크 프로토콜: 데이터베이스와 클라이언트 간의 통신을 위한 프로토콜로, 데이터 전송과 명령어 교환을 지원합니다.

---

2. SQL 처리 과정

---

DBMS 엔진은 요청받은 쿼리문을 실행하기 위해 파싱(Parsing)이라는 과정을 진행합니다.

하드 파싱 VS 소프트 파싱

• Library cache에 이전에 실행했던 쿼리가 없는 경우 : '하드 파싱(Hard Parsing)' 진행
• Library cache에 이전에 실행했던 쿼리가 있는 경우 : '소프트 파싱(Soft Parsing)' 진행

---

1-1. 소프트 파싱

소프트 파싱은 Oracle이 이미 구문 분석된 SQL 문에 대해 기존 실행 계획을 재사용할 수 있을 때 발생합니다. 쿼리가 데이터베이스에 제출되면 Oracle은 먼저 Shared Pool 메모리 영역을 검사하여 SQL 문의 구문 분석된 표현(즉, 실행 계획)이 이미 있는지 확인합니다.
 
Oracle이 Shared Pool에서 일치하는 실행 계획을 찾으면 해당 계획을 재사용하여 SQL 문을 다시 분석하는 오버헤드를 피할 수 있습니다. 소프트 파싱은 새로운 실행 계획을 생성하는 단계를 건너뛰기 때문에 하드 파싱에 비해 더 빠르고 리소스를 절약할 수 있습니다.

소프트 파싱은 실행 계획을 반복적으로 구문 분석, 최적화 및 생성하는 데 따른 오버헤드를 줄여주기 때문에 자주 실행되는 SQL 문은 꼭 실행계획을 공유하게 해야 합니다. 
 

1-2. 하드 파싱
하드 파싱은 Oracle이 기존 실행 계획을 재사용할 수 없고 SQL 문에 대한 새 계획을 생성해야 할 때 발생합니다. 이는 일반적으로 Shared Pool 일치하는 구문 분석된 SQL 문의 표현이 없거나 스키마 변경, 최적화 설정 또는 바인드 변수와 같은 다양한 이유로 인해 기존 계획을 재사용할 수 없는 경우에 해당됩니다.

SQL 문이 하드 파싱을 거치면 오라클은 구문 및 의미 분석, 최적화, 새로운 실행 계획 생성을 포함한 전체 구문 분석 프로세스를 수행합니다. 하드 구문 분석은 새로운 실행 계획을 생성하기 위한 추가 처리가 필요하기 때문에 소프트 구문 분석에 비해 리소스를 많이 사용하여 시간이 더 많이 소요됩니다.
 
하드 파싱은 오버헤드는 특히 SQL 문이 자주 실행되는 시스템에서 데이터베이스 성능에 악영향을 미칠 수 있습니다. 따라서 SQL 문을 최적화하고, 바인드 변수를 사용하고, 적절한 크기의 Shared Pool을 보장하여 하드 파싱을 최소화하면 전체 데이터베이스 성능을 향상할 수 있습니다.

---

출처 : https://bommbom.tistory.com/entry/SQL-%ED%8C%8C%EC%8B%B1-%EA%B3%BC%EC%A0%95-%ED%95%98%EB%93%9C%ED%8C%8C%EC%8B%B1hard-parsing-vs-%EC%86%8C%ED%94%84%ED%8A%B8%ED%8C%8C%EC%8B%B1soft-parsing

출처 : https://velog.io/@jincrates/1%EC%9E%A5-SQL-%EC%B2%98%EB%A6%AC-%EA%B3%BC%EC%A0%95%EA%B3%BC-IO

출처 : https://myjamong.tistory.com/207