[오라클 아키텍처] SQL문 분석/공유 풀/오라클 기동/정지

책의 목차

4. SQL문 분석과 공유 풀

4.1 SQL문의 분석과 공유 풀을 왜 배워야 하는가?
4.2 SQL문과 일반적인 프로그래밍 언어의 차이
4.3 서버 프로세스와 분석
4.4 실행 계획이 최적이라는 것을 판단하기 위해서는?
4.5 공유 풀의 동작과 구조
4.6 수치로 알아보는 분석과 공유 풀의 정보
4.7 요약

5 .오라클의 기동과 정지

5.1 기동과 정지를 왜 배워야 하는가?
5.2 오라클의 기동/정지의 개요
5.3 업무의 시작에 해당하는 오라클의 기동
5.4 인스턴스, 데이터베이스, 그리고 주요 파일의 구성
5.5 기동 처리의 흐름과 내부 동작
5.6 업무 종료에 해당하는 오라클의 정지
5.7 데이터베이스를 수동으로 생성하기
5.8 요약

---

SQL문 분석과 공유 풀

 

• SQL문 분석
  • 규칙 기반
  • 비용 기반 (10g부터는 비용기반만 지)
    • 통계정보 기반 (Analyze)
      • 수집 정보
        • SQL문 정보
        • 초기화 파라미터
          • 단일 I/O로 읽어올 수 있는 블록 수
          • 세션에서 이용할 수 있는 메모리 크기
        • 옵티마이저 통계
          • 테이블 (로우 및 블록)
          • 칼럼 (칼럼 값 및 데이터 분포)
          • 인덱스 (깊이, 블록 수, 인덱스-데이터 블록 관계 정보)
          • 시스템 통계 (I/O, CPU 성능 및 사용)
      • 수집 방식
        • 사전에 수집됨
        • DBA가 수집 시점, 빈도, 수준을 설정해야 함
          • 정기적 수집을 방지하거나
          • 통계에 Lock을 걸 수 있음
          • 데이터의 양에 증감이 있는 시스템(배치처리 등)의 경우 시점에 유의하여 통계정보를 수집해야 함
          • 통계정보 수집 시점, 통계정보 고정, 힌트 등 요소를 조합하여 최적의 실행계획 운영 필
    • 실행계획 수립 (옵티마이저)
      • 막대한 경우의 수로 인해, 모든 처리 방법의 비용을 계산하지는 않음
        • 액세스 방식 - Full scan vs. Index scan
        • 액세스 순서 - 어떤 테이블을?
        • 조인 방법
      • CPU 자원이 크게 소비되는 작업임
      • 공유 풀에 공유
      • 파싱
        • 하드 파싱
        • 소프트 파싱
          • 공유 풀 라이브러리 캐시의 실행계획 재사용
• 공유 풀
  • 딕셔너리 캐시 - SQL 실행에 관련된 메타 정보
    • 통계 정보
  • 라이브러리 캐시
    • 실행계획
    • SQL 정보
      • Hash 알고리즘 사용
      • SQL문 별 ID 생성
      • 바인드 변수 사용 권고
      • CURSOR_SHARING 초기화 파라미터 설정
        • SQL 조건절에 있는 상수 값들을 변수로 전환시켜 parsing 함으로써 cursor를 공유할 수 있도록 해줌
        • Default value=exact
        • 적용 방법 : alter session(system) set cursor_sharing = value;
        • 종류
          • EXACT
          • SIMILAR
          • FORCE
          • SIMILAR & FORCE
  • 기타
• Statspack 보고서 활용
  • 오라클 성능진단 도구
  • 10g 이상부터는 AWR (Automatic Workload Repository)
    • Diagnostics Pack 라이선스 필요
  • CPU 사용량

오라클의 기동과 정지

• 기동
  • SHUTDOWN ➡️ NOMOUNT ➡️ MOUNT ➡️ OPEN
  • 단계별
    • SHUTDOWN - 정지 상태
    • NOMOUNT
      • ORACLE_HOME, ORACLE_SID 환경 변수를 토대로 초기화 파라미터 파일을 찾아 읽어옴
        • SPFILE (서버 파라미터 파일)
          • 인스턴스가 기본적으로 활용하는 파라미터 파일
          • 9i 이후에 추가된 바이너리 형식 파라미터 파일
          • 텍스트 에디터로 수정 불가
          • SQL문(ALTER SYSTEM문)을 사용해야 함 (적용 시점, 범위 설정 가능)
          • PFILE을 사용해 수동 생성 또는, 데이터베이스 생성 시 DBCA에 의해 자동 생성
        • PFILE (파라미터 파일)
          • 텍스트 에디터로 수동 변경 가능 (적용을 위해서는 인스턴스 재가동 필요)
          • 데이터베이스 생성, 장애시 사용
      • 백그라운드 프로세스 생성
      • 공유 메모리 확보
        • 버퍼 캐시
        • 공유 풀
    • MOUNT
      • 컨트롤 파일을 읽어 데이터 파일, REDO 로그 파일 위치를 파악
        • 데이터베이스 구성정보 파일
        • 데이터베이스의 파일 경로 등
      • 위치를 알아내는 것 뿐이므로, 해당 위치에 실제 파일이 없어도 에러가 발생하지 않음
      • 컨트롤 파일 손상만으로도 MOUNT가 불가해지므로, 주기적으로 백업을 해야 함
    • OPEN
      • SQL을 처리할 수 있는 상태
      • 데이터 파일, REDO 로그 파일을 열어 오라클 내부 정보와 비교
      • 백그라운드 프로세스를 기
  • 인스턴스
    • 백그라운드 프로세스 + 공유 메모리
    • 오라클 관리를 위한 단위
    • 데이터베이스와 1:1로 대응 (일반적으로)
      • BUT RAC을 사용하면 1스토리지:多인스턴스
  • 스토리지
    • 컨트롤 파일
    • 데이터 파일
    • REDO 로그 파일
    • 그 외 파일
      • 파라미터
      • 아카이브 로그
      • 에러 로그
  • 기동 명령어
    • SQL*Plus에서
    • 관리자 계정으로 전환
    • startup 명령
• 정지
  • 정지 명령어
    • shutdown
      • default : 커넥션 종료 기다림. 변경된 데이터 데이터파일에 기록 ⭕
      • transactional : 트랜잭션 끝나기를 기다렸다가 커넥션을 끊음. 데이터파일 기록 ⭕
      • immediate : 커넥션 종료시켜 커밋하지 않은 데이터는 없어짐. 데이터파일 기록 ⭕
      •  
        abort : 커넥션 종료시켜 커밋하지 않은 데이터는 없어짐. 데이터파일 기록 🙅‍♀️
        • 다음 기동 시 데이터를 복구(instance recovery)함
        • REDO 로그 파일을 사용
• QUIZ
  • 컨트롤 파일이 손상되었을 때 컨트롤 파일 생성을 위한 명령이 가능한 상태는?
    • ➡️ NOMOUNT
  • 데이터베이스 파일 손상으로 데이터베이스 OPEN이 되지 않을 때 복구 명령어는 어느 상태에서?
    • ➡️ MOUNT

---

참고 도서

1. 개정판｜그림으로 공부하는 오라클 구조, 스기타 아츠시, 야마모토 유미코 외 3명 저, 이민재 역, 제이펍 출판
https://ridibooks.com/books/852001113

개정판｜그림으로 공부하는 오라클 구조