DB 성능 아키텍처

아키텍처

아키텍처의 정의

하드웨어와 소프트웨어를 포함한 시스템 구조

아키텍처를 알아야하는 이유

1. 새로운 버전의 DBMS를 경험이 아닌 원리 구조를 가지고 빠른 파익이 가능함.
2. 다른 DBMS 또한 데이터베이스 이론을 기반으로 설계 구현된 것이라 다른 DBMS를 다루는데 필요함.
3. 새로운 문제가 발생했을 때 배우지 않아도 구조적으로 이해하고 파악하여 처리가 가능함.

성능관점 아키텍처

1. 튜닝을 위해 아키텍처의 구조를 아는 것은 중요함
2. 개발자가 충분히 처리 가능한 소프트웨어 튜닝 영역으로 한정함.
3. DBA가 할 수 있는 영역과 다름.

Oracle DBMS 구조

1. Instance

   자바의 인스턴스와 같은 의미, 즉 메모리상에 올라와 실행 가능한 상태

2. 메모리(SGA : SytstemGlobalArea)

   1. Data Buffer Cache
      1. 사용자가 요청한 데이터를 저장하는 메모리 영역
      2. Select 실행 후 결과 집합을 Data Buffer Cache에 저장함.
   2. Shared Pool
      1. SQL, PL/SQL을 공유하기 위한 영역
   3. Redo Log Buffer
      1. 작업내용을 Log로 남김
      2. 데이터 복구

3. Background Process

   1. User Process
      1. SQL 명령어를 Oracle DBMS에게 보내는 프로세스
   2. Server Process
      1. SQL을 받아 서비스하는 프로세스

데이터 저장구조

1. Table Space
   • TableSpace는 여러 Block으로 구성된다.
   • TableSpace에 데이터가 가득 차게 되면 TableSpace를 확장한다.
   • TableScpae에 Block이 연속적으로 할당되는 이유는 검색의 효율을 높이기 위함이다.
2. Extent
   • Extent는 연속된 Block영역
3. Block
   • Block은 하나의 Cell을 의미하며 데이터가 저장되는 공간을 의미한다.

데이터 저장구조, 계층적 저장구조

• Database는 1개 이상의 TableSpace가 반드시 있어야 함.
• TableSpace는 반드시 1개의 Database에 포함되어야 함.
• 실제 운영되는 Database는 여러 TableSpace로 구성됨.

TableSpace와 Segment의 관계

• TableSpace는 논리적인 저장공간
• Segment는 TableSpace 내에 저장되어 자신만의 저장영역을 확보하는 물리적인 DatabaseObject이며 대표적으로 테이블과 인덱스가 있음
• TableSpace는 1개 이상의 Segment가 저장될 수 있음.

File/Extent/Block

1. File

   TableSpace는 여러 개의 물리적인 파일들로 구성되어 있음

2. Extent

   Segment는 1개 이상의 Extent로 구성됨.

3. Block

   • Extent는 1개 이상의 Block으로 구성됨.
   • Block은 I/O의 최소단위.
   • Block의 크기는 2K, 4K ~ 32K의 크기를 가질 수 있음.
   • Block은 I/O의 최소 단위이기 때문에 Block안에 해당하는 레코드가 수정되면 Block단위로 수정됨.

ROWID

1. 객체의 Row가 저장된 논리적 주소값
2. ROWID를 가지고 데이터를 찾는 것이 가장 빠른 길임.

SQL 처리 과정

목표

SQL 처리과정을 이해하자.

SQL 이란

1. 관계형 DBMS에 접근하는 유일한 언어
2. ANSI-SQL
   • 표준 SQL
   • ANSI-SQL을 잘 익혀 두면 다른 RDBMS로 이식이 쉬움
3. SQL의 주요 특징
   1. English-Like
      1. 영어 문맥과 유사함
      2. 대소문자를 구분하지 않음
   2. 비절차적 언어

Server Process

1. Connection과 Session
   • Server Process는 User Process(웹 서버, RDBMS)로부터 Connection을 생성하고 Session을 통해 Connection에 연결된 User의 정보를 관리함.
   • User Process는 Server Process에 SQL (문자열)을 전송한다. 즉 이를 SQL 실행이라고 함.

SQL처리

1. Parsing > Execution > Fetch

2. Fetch는 서버에서 클라이언트로 데이터를 옮겨 표현하는 과정으로 DML(Insert, Delete, Update)에서는 Fetch과정이 존재하지 않음

3. Parsing 단계

   1. Syntax Check

      1. SQL 명령어의 문법적 오류를 Check하는 단계

   2. Semantic Check

      1. SQL 명령어의 의미적인 오류를 Check하는 단계
      2. 데이터베이스 객체(Table, Index, Sequence, 등..)의 유효성 검사, 접근 권한검사 등을 수행함

   3. Execution Plan 수립

      1. 질의 최적화기(Query Optimizer)가 최적화된 실행계획을 수립하는 단계

   4. Execution 단계

      1. Parsed-Tree를 가지고 Server Process가 명령어를 실행하는 단계임
         

         

   5. Fetch 단계

      1. Select의 경우에만 실행되는 단계
      2. Execution 단계에서 준비된 결과 데이터를 사용자에게 전달하는 과정임

   6. Parsing 후 SQL 문장, 실행계획, Parsed-Tree를 DBMS가 관리하는 Shared SQL, PL/SQL Area라는 메모리 영역에 저장됨

      1. Semantic Check, Execution Plan 수립은 CPU를 많이 사용하고, DBMS의 내부 공유자원에 대한 제어를 많이 사용함
      2. 그렇기 때문에 Parsing 작업을 줄이기 위해 Shared Pool 내 Library Cache에 Parsing 결과물을 저장해두고 SQL을 재사용함.
   • SoftParsing vs HardParsing
     1. SoftParsing은 Cache hit로 기존 Parsing된 결과물을 재사용함
     2. HardParsing은 Cache miss로 Parsing 단계를 진행함

4. Select 처리 구조

   1. SGA의 Data Buffer Cache를 탐색한 후 데이터가 존재하지 않으면 검색 시작.
   2. LRU(Least Recent Used : 최근 자주 사용된 것을 유지) 관리정책으로 Shared Pool과 Data Buffer Cache를 관리함.

5. DML 처리 구조

   1. Update, Delete
      1. Redo log Buffer 변경전/변경후/변경행위 기록 [작업내용 선 입력]
      2. Undo Block 변경 전 데이터 기록 [Rollback을 위함]
      3. 물리 데이터에 변경 정보 저장 [DBWR이 작업을 진행함]
   2. Insert
      1. Redo log Buffer 변경전/변경후/변경행위 기록 [작업내용 선 입력]
      2. Insert할 Block Cache할당
      3. 물리 데이터에 변경 정보 저장

• Delete vs Truncate

  1. Delete 데이터를 삭제하기 위해 Redo Data, Undo Data를 기록해야함.
  2. Truncate는 Redo Data, Undo Data를 만들지 않음. DataDictionary에 삭제처리한 후 끝남.
  3. 그렇기 때문에 Truncate의 속도가 훨씬 빠름

• DBWR(Database Writer)

  Data Buffer Cache의 변경된 정보를 한꺼번에 효율적으로 모아서 Data File에 기록함.

• LGWR

  Redo Log Buffer의 로그 정보를 Log File에 기록함.

모니터링

1. 성능 모니터링 원리 및 구조

   1. 모니터링 -> 진단 및 분석 -> 문제해결
   2. DBMS 운영 중 발생 상황 -> SGA 가상 테이블 메모리에 구조체 형식으로 저장
   3. 가상테이블 -> 동적성능테이블(X$), 가상뷰 -> 동적성능뷰(V$)
   4. 동적의 2가지 의미
      1. DBMS 운영 중에 수치 값이 계속 변함
      2. DBMS 종료 시 DataFile에 저장하지 않고 지움

2. DBA 권한

   1. 주로 V$ 테이블의 경우 DBA의 권한으로 되어 있음
   2. DBA들이 자주 사용하는 동적 성능 뷰

      1. 

3. Server Process 방식

   1. DBA 설정에 따라 달라짐
   2. Dedicated Server Process
      1. User Process Server Process 1:1
      2. PGA(Program Global Area)
         1. 해당 서버가 단독으로 사용하는 메모리 영역
      3. User Process 요청이 올 때마다 H/W서버에 Server Process 생성
      4. 기본적으로 Dedicated Server Process를 사용함
   3. Shared Server Process
      1. UserProcess Server Process N:1
      2. 동일한 자원으로 더 많은 유저 처리를 할 수 있음
      3. 유저 처리가 많아질수록 Dedicated Server 방식보다 대응 처리가 늦어질 수 있음
   4. Parallel Processing
      1. 대용량은 병행처리, 소용량은 싱글처리하는 것이 효율적이다.
      2. 대용량 병행처리를 위해서는 업무분석 -> 업무분담 -> 업무병합 작업이 진행되므로 소용량은 싱글처리가 빠른 것이다.
      3. 대용량 병행처리도 많은 프로세서가 참여하면 자원 경쟁으로 인해 속도 저하가 발생할 수 있다.

4. 

   1. /+/ 로 병행처리 힌트 전달
   2. 실행계획 수립에서 Optimizer가 병행처리 여부 판단
   3. Query Coordinator가 조정자, Slave Process Pool에서 Slave Process를 가지고 병행처리를 진행함.

5. Execution Plan

   1. 실행계획을 보면서 SQL DBMS 내부에서 어떻게 처리되는지 내부 처리 절차를 들여다 보는 것이다
   2. SQL 튜닝 시 확인
   3. 실행계획 분석
      1. Explain Plan 명령어
      2. Autotrace OPTION

성능튜닝 개요

1. 목표

   1. 성능튜닝의 주체
   2. 성능튜닝의 시기
   3. 성능튜닝의 적용

2. 누가 튜닝하는가?

   1. 개념적/논리적 데이터 모델링 튜닝
      1. 모델러
   2. 물리적 모델링 측면의 Object 튜닝
      1. DBA, 개발 PL
   3. SQL 튜닝, 데이터 처리 Logic 튜닝
      1. 개발자, 튜너
   4. DBMS Configuration 튜닝 (SGA 크기, I/O 분산, INSTANCE 튜닝)
      1. DBA
   5. OS Configuration 튜닝
      1. 시스템 관리자

3. 무엇을 튜닝하는가?

   1. 

   2. 

      1. 튜닝 Step

         1. 개발중인 시스템에 대해 튜닝을 수행할 때 순서대로 진행하는 것이 비용대비 효과면에서 유리

      2. Tune Design

         1. DB 모델링 튜닝
         2. 개념모델링 -> 논리모델링 -> 물리모델링

      3. Tune Application

         1. Server Tuning
            1. DBMS, OS, H/W
         2. Application Tuning
            1. Application Source

      4. Tune Memory

         1. 적절한 메모리 사이징
         2. Data Buffer Cache

      5. Tune I/O

         1. I/O 최소화 (어플리케이션 개발자)

         2. SQL 튜닝

            1. 

         3. Tune Contention, Tune O/S

            1. Lock과 관련된 부분

4. 성능 튜닝의 시기

   1. 

   2. 

      1. 개발자 단계
         1. SQL 기능 구현
         2. Application 적용 후 개발
      2. 튜닝이 적용된 개발자 단계
         1. SQL 기능 구현
         2. SQL 성능 Test 및 튜닝
         3. Application 적용 후 개발

성능진단 성능튜닝

1. 목표
   1. 성능튜닝의 분석항목
   2. 성능튜닝 Tool을 통한 튜닝 진행
2. 성능튜닝 분석항목
   1. 자원사용량 분석
   2. 실행계획, 접근경로
   3. 응답시간
3. 자원사용량 분석

1. Query는 Select, Current는 DML을 말함
2. 8K block이라고 할 때, 7568 * 8K = 59MB를 읽어드림
3. 1개의 로우를 읽기 위해 59MB Physical Read, Logical Read가 발생함.

4. 실행계획, 접근경로
   1. 실행계획 = 접근경로 + 연산
   2. 큰 성능 차이는 접근경로를 통해 나타나기 때문에, Optimizer가 선택한 접근경로를 잘 파악하는 것이 중요하다.
   3. 접근경로
      1. Direct Access
         1. ROWID를 통한 접근
      2. Index Scan

• Full Table Scan

1. 성능튜닝 툴
   1. PLAN_TABLE
      1. utlxplan.sql을 실행하여 PLAN_TABLE을 개발자가 생성할 수 있다.
   2. Oracle Tool
      1. SQL Trace
         1. 필수적인 Tool
      2. Autotrace
         1. 편리한 Tool
         2. DBA 도움이 있어야함.
         3. DBA 계정으로만 가능 (sys 로그인)
         4. sql로 설치 (@경로/파일명)
         5. set autotrace on 입력 후 쿼리

• explain plan

  1. 개발자가 쓰기 편함

  2. DBMS_XPLAN 으로 강력한 지원

  3. explain for 쿼리

     1. 

     2. Explain for 쿼리 후

     3. Select * from table(DBMS_XPLAN.DISPLAY); 로 분석결과를 볼 수 있음

  4. SQL Trace

     1. DBA 주요 툴
     2. 강력한 툴이지만 전문성이 요구
     3. *.trc 파일 생성
     4. Tkprof로 파일 열기(읽기 쉬운 형태로 보여줌)

  5. statspack

     1. sql로 설치(@ORACLE_HOME\RDBMS\ADMIN*, DBA용 설치 툴)
     2. 구간성능 모니터링
     3. EXECUTE STATSPACK.SNAP 으로 시작
     4. EXECUTE STATSPACK.SNAP 으로 종료

성능 시뮬레이션 1

1. 목표

   1. 빈번한 DB Connection
   2. 빈번한 Hard Parsing
   3. 빈번한 Fetch
   4. 위 경우에 대한 사례 및 아케텍쳐와 해결방안 학습

2. 빈번한 DB Connection

   1. 유저의 요청이 들어오면, 유저의 요청을 받는 리스너는 서버 프로세스를 만들고 서버 프로세스는 SGA에 Session 정보를 만듬.
   2. 프로세스를 만드는 작업은 OS가 진행하며 많은 자원이 소모됨.
   3. 1. 서버 프로세스보다 전체 CPU 사용량이 높은 것으로 보아 서버프로세스 보다 OS자원 사용량이 더 많은 것을 알 수 있음

   

   1. 해결방안

      1. Conneciton Pool을 만들어 빈번한 Connection 생성 및 제거를 줄일 수 있다.

   2. 빈번한 Hard Parsing

      1. 

         1. SQL 처리 단계
            1. Parsing
               1. SQL문
               2. 실행계획 (Execution Plan)
               3. Parsed Tree
            2. Execution
            3. Fetch
         2. 파싱된 SQL문은 SGA에서 캐시에 저장되어 효율성을 높인다.

• 캐시의 경우 LRU(Least Recently Used) 정책을 사용하여 캐시를 관리한다.

1. 해결방법

   1. SQL 공유

      1. 컴파일된 SP를 사용하여 Hard Parsing을 줄인다.

      2. 조건을 바인드 변수 처리

         1. 문자열로 조건을 넘기는 것이 아닌 바인드로 처리하면 SQL을 재사용할 수 있음

         2. ‘SELECT * FROM EMP WHERE EMP_ID’ || TO_CHAR(EMP_ID) (잘못사용X)

         3. ‘SELECT * FROM EMP WHRE EMP_ID = :EMP_ID ‘; (바인딩)

         4. 바인드 변수의 문제점

            1. 바인드 변수의 경우 기존의 SQL을 사용함으로 같은 실행계획을 동일하게 사용함 soft parsing

            2. 균등한 데이터 분포도를 가진 컬럼에서 사용해야함

            3. 인덱싱된 컬럼에 대해 조회를 처리할 시 인덱싱하는 데이터의 양이 불균등하게 많은 데이터 검색 시 Full Table Scan이 효율적이고 적당하게 분표한 데이터의 경우 Index Scan의 효율적이기 때문임

            4. 

2. DBA가 인스턴스 파라미터 사용

3. 1:10:100의 원리

   1. 초기에 해결하면 1, 중기에 해결하면 10, 말기에 해결하면 100

4. HardParsing 문제를 개발전에 계획수립시 개발정책으로 결정하여 사전에 미리 문제를 해결해야함.

5. 빈번한 Fetch

   1. 1번 Fetch시 여러 개의 데이터를 묶어서 보냄
   2. Array Size를 설정하여, 빈번하게 발생하는 Fetch(DBMS Call)을 줄인다.
   3. 전체범위처리, 부분범위처리
      1. 30만건의 데이터를 예시로, 30만건을 전체 다보내면 전체범위처리, 30만건을 30개씩 요청에 따라 나눠보내면 부분범위 처리이다
      2. 특성에 맞게 사용하는 것이 중요하다.

   

   1. Consistent gets은 데이터를 위해 block에 접근한 횟수를 의미함.
   2. 10block * 1 = 10 (consistent gets)
   3. 1block * 10 = 10 (consistent gets)

성능 시뮬레이션2

1. 목표
   1. 시뮬레이션을 통해 효과적인 데이터 처리방식을 알아보자.
2. 툴마다 지원하는 명령어가 있다
   1. Sqlplus
      1. Rem
      2. @

• Set 변수 값

1. PL/SQL

   1. Procedural Language Extension to SQL(절차언어 SQL)
   2. PL/SQL의 명령어는 절차적으로 진행됨
   3. DBMS SQL을 만나면 데이터베이스에 데이터 요청을 하게 됨(DBMS CALL)
   4. 잦은 DBMS CALL은 결국 성능저하를 야기함.
   5. 네트워크 데이터 전송에 따른 시간 감축

2. PL/SQL Array Processing

   1. 빈번한 DBMS CALL을 줄이기 위한 처리방법

   2. ArraySize 크기 만큼 데이터를 가져와 처리 (ArraySize 변동에 따른 시간 체크)

   3. FETCH TEST_CURSOR BULK COLLECT INTO ..

   4. FORALL I IN 1..

      1. 횟수만큼 바인딩 후 한번에 INSERT

   5. Stored Procedural

      1. 

      2. Stored Block을 통해서 SQL 파싱 시간을 감소

      3. 유저간 공유 가능

   6. 절차적 처리와 비절차적 처리

      1. 

      2. 

      3. SQL문을 전송하여 한번에 집합적으로 처리

      4. FETCH, INSERT를 하는 것이 아니기 때문에 빈번한 DBMS Call이 발생하지 않고, 빈번한 Context Switch가 발생하지 않음(연산작용과 DBMS Call간의 데이터 스위치를 의미). 또한 단위를 20개로 나누워 절차적으로 처리하지 않음

   7. LG

      1. 비절차적 방식으로 주로 데이터를 처리함
      2. 필요한 경우 FETCH하여 데이터를 가져온 후 비절차로 처리
      3. 그러기 때문에 Array Processing은 필요없겠구나.
      4. Stord Procedural
      5. 비절차적 처리

   8. 

옵티마이저

1. 목표

   1. Optimizer 개념
   2. Optimizer 종류
   3. Optimizer Mode에 따른 실행계획 차이

2. Optimizer의 개념

   1. 

      1. 비절차적 언어인 SQL은 RDBMS에 쿼리를 보내면 RDBMS의 Optimizer는 해당 요청에 대한 실행계획을 실행함.
      2. 절차적 언어인 Java는 개발자가 효율적인 처리 절차를 기술해야함

   2. 

3. Optimizer의 한계

   1. Optimizer의 한계성, 주변상황, 판단조건 수립된 실행계획이 항상 최적화된 정답은 아니다.

   2. Optimizer를 완벽히 이해하기는 힘들지만, 어느정도 이해를 하고 있어야 오판 원인 진단을 할 수 있다.

   3. 오판을 바로 잡는 것이 바로 튜닝이다.

4. Optimizer의 종류

   1. CFO형
      1. 주변사항에 따라 효율이 좋은 판단을 내림
   2. 행동대장형
      1. 규칙에 따라서 판단을 내림
   3. CFO가 가끔 오판을 하지만 행동대장형보다 주변 상황에 따른 최적의 판단을 하기 때문에 대세임
   4. RBO (Rule Base Optimizer, 행동대장형)
      1. 규칙기반 Optimizer
      2. 미리 정해진 고정된 15개의 규칙을 기반으로 판단
   5. CBO(Cost Base Optimizeer, CFO형)
      1. 비용기반 Optimizer
      2. Data Dictionary 들에 저장된 통계정보 기반 판단
   6. Full Table Scan, Index Scan 판단하기
      1. Full Table Scan은 전체 검색, 검색하려는 데이터가 많을수록 Full Table Scan이 유리
      2. Index Scan은 색인 참조 후 Direct Access 이므로, 검색하려는 데이터가 적을 수록 Index Scan이 유리
   7. CBO가 최적화된 실행계획을 수립할 수 있도록 하려면?
      1. Analyze 명령어 , dbms_stat 패키지는 통계정보 수집 기능을 가지고 있음.
      2. 주기적인 통계정보 갱신을 통해 정확성 있는 정보를 유지해야함

• 통계정보 갱신은 DBA의 업무

1. Oracle의 CBO 비용공식은 아마도(비공개) Row Cadinality, I/O Cost, Network Cost, Cpu Cost등을 종합적으로 산정

2. CBO와 RBO이 장단점

   1. 

   2. 한마디로 RBO은 사장 기술 이고만

3. Optimizer 모드

   1. 

   2. ALL_ROWS, FULL TABLE SCAN
   3. FIRST_ROWS, INDEX SCAN

• RULE, RBO

1. CHOOSE, CBO, RBO 중 택일
2. /+/힌트로 Optimizer에게 힌트를 줌.
   1. Optimzer 판단으로 힌트대로 실행하거나 무시함
3. ANALYZE 명령어로 통계를 수집, 삭제
   1. 데이터딕셔너리는 여러 테이블로 구성되어있군.
   2. 통계정보는 데이터딕셔너리에 저장됨 (USER_TABLES)

• ANALYZE TABLE 테이블명 ESTIMATE STATISTICS FOR COLUMNS 병역종류;
  1. 분포도가 일정하지 않은 경우 CBO는 잘못된 판단을 할 경우 가 큼, 분포도에 따라 접근경로가 달라지기 떄문임
     1. 분포가 많은 데이터는 Full Table Scan으로
     2. 분포가 적은 데이터는 Index Scan이 효율적임.
  2. DBA_HISTOGRAMS에 분포도 저장
  3. USER_TAB_COLUMNS에 데이터 밀도 저장
  4. 검색할 컬럼에 대한 분포도를 알아야 FULL TABLE SCAN, INDEX SCAN이 맞는지 알 수 있음 CBO 계산 정보