EXADATA Developer - 6

EXADATA Developer - 6
> 기존 DB Block I/O 방식
  - Server = Instance + database
  - Instance
    - 오라클은 인스턴스라는 것이 있는데, 인스턴스는 메모리와 백그라운드 프로세스로 이뤄짐.
    - 이런 메모리 영역을 SGA라고 함(Shared Global Area).
      1. Shared Pool (library cache, data dictionary cache)
      2. DB Buffer cache (buffer 사이즈는 block 사이즈와 동일, 그대로 읽어서 넣을 거니)
    - 굉장히 많은 BGProcess가 있음
      1. SMON
      2. PMON
      3. DBW
      4. LGW
      5. CKPT
    - startup 명령어를 통해서 DB를 구동하면, 메모리를 할당하고 bgprocess를 실행 시킴.
  - Database의 구성
    - control files : 물리적인 DB 구조, 위치 정보 有.
    - data files : 실제 물리적인 데이터 有
    - redo log files
  - sh/sh@접속DB대상
  - SQL의 종류
    1. Query
    2. DML
    3. DDL
    4. DCL
    5. TCL
  - Data Dictionary (internal table)
    : DB Core 정보가 있음.
    1. 유저 계정에 대한 정보, 테이블에 대한 정보, 인덱스에 대한 정보 등이 있음.
    2. DB 시스템에서 알아서, 자동으로 관리. 사용자가 정보는 조회할 수 있음.
    3. View를 통해서 조회할 수 있는데, 이를 Data Dictionary View라고 함.
    4. Data Dictionary View의 예로
      - user_tables
      - all_tables
      - dba_tables (db에 생성된 모든 db를 볼 수 있음)
      - V$ : dynamic performance view로, 성능을 확인하기 위한 View.
  - Query 처리 절차
    1. sh/sh@접속DB대상으로 접속하면, 유저 프로세스가 생성되고, 접속 DB 대상으로 접속 요청 보냄.
    2. 그럼 DB Server의 Listener가 이를 듣고, 서버 프로세스를 spawn함.
    3. 서버 프로세스는 data dictionary cache가 없으면 data files의 SYSTEM 파일을 통해서 유저 계정을 확인하고,
    4. Shared Pool에 SYSTEM File을 Shared Pool에 data dictionary cache로 올림. (추후 계정 확인은 cache 확인)
    5. 인증 되면 유저 프로세스와 서버 프로세스 간 연결이 일어나고, 이 connection을 sesssion이라고 함.
      - DB 생성될 때, 먼저 만들어져야하는 테이블이 있음. SYSTEM. 이 파일들이 data files에 있음.
      - SYSTEM의 datafiles를 Shared Pool 안에 data dictionary cache에 올림. (shared pool에는 library cache도 있음)
    6. Select * from emp where emp_id = 100;이라는 Query를 날리면
      0) soft parse : hard parse를 하기 전에, 이전의 parse했던 정보들이 Shared Pool의 library cache에 있는 지 확인 후 없으면
        - 동일한 SQL이라는 것이란,
        : 대소문자, 공백 개수, comment, owner, literal
        - 그럼 다른 SQL로 인식이 되면 Hard Parsing하네. 왜 comment도 다른 SQL로 할까?
      1) hard parse : Syntax check, semantic check (object, 권한 등), execution plan 작성.
        - execution plan을 optimizer가 작성함.
        - optimizer는 일정의 알고리즘 (CBO Cost Bast Optimizer)
          (1) transformer : Query를 조금 더 나은 작업으로 변형
          (2) estimator : optimizer 통계를 보고 가장 코스트가 낮은 것을 계산한 후에,
          (3) query generator : 쿼리들이 생성되면
          (4) (2), (3)을 반복하여 가장 비용이 낮은 것을 선택
        - pcode 작성
        - 이 정보들을 library cache에 넣어주고, 나중에 비슷한 쿼리 날리면 soft parse로 사용.
      2) (bind 변수 사용하는 경우) bind 변수 (Select * from emp where emp_id : 1;
        - bind 변수로 지정하게 되면, bind 변수 작업을 하게 되고, 실제 값을 변수이 assign함.
      3) execute
        - 오라클의 최소 I/O 단위는 Block인데, 이 Block의 사이즈는 db 생성 시 파라미터 값으로 결정 (db_block_size, 기본적으로 8K)
   - 원하는 정보가 DB Buffer cache에 확인해서, miss하는 경우 (못 찾은 경우) data files (Disk)에서 읽어야 함.
 - data files도 block 단위로 쪼개져 있음. 만약 emp_id가 100이라면, 그 row가 있는 block들을 모두 읽어서 DB Buffer cache에 올림. 이렇게 disk (data files)를 읽는 작업을 physical read라고 함.
        - 그런데, 실제로 원하는 작업은 block 중에서 100번 emp_id. 즉 원하는 데이터를 거르는 filter 작업이 필요함.
        - 서버 프로세스는 PGA라는 메모리가 할당 되는데, stack, session info, cursor state, SQL workarea로 구성됨.
        (PGA는 session memory라고도 함)
        - DBA가 관심있는 영역이 SQL Workarea인데, 여기서 sorting, hash join, bitmap 작업을 함.
          - 예를 들어서 order by는 DB Buffer cache에 원하는 데이터를 가지고 와서, PGA의 SQL Workarea에서 ordering함.
          - 근데 이 메모리 영역이 한계가 있으니, Disk의 temporary space에 가지고 가서 SQL Workarea 작업을 함. 이러면 속도가 느려짐. 그래서 order by를 자주 하지 말라는 것이 이런 이유.
 - PGA가 DB Buffer cache를 읽어서, 원하는 row (100번 사원 데이터)를 Cursur state로 올려서, 그 row를 가리키는 cursor를 유지.
      4) fetch
 - query하는 경우에만 있는 작업으로, 3번에서 가지고 온 row를 유저 프로세스에 return.
  - 튜닝은 절대적인 것이 없다. 상황과 환경에 맞게.
  - bind 변수를 사용할 때 장단점.
    1. parse할 때 실행 계획을 작성하는데, parse 단계에서는 bind 변수가 뭔지 모름. (2번째 bind 단계에서 알게 됨)
      => 따라서, bind 변수 값을 parse 단계에서 모르기 때문에 분포를 모르고, 정확한 실행 계획을 작성할 수 없음.
      : 예를 들어, city가 서울인 사원이나, city가 제주도인 사원을 검색하는 상황에서, 서울인 사원이 99%면 Full table scan.
    2. OLTP : Bind 변수 O, OLAP : Bind 변수 X (데이터의 양이 多)
  - Transaction
    1. 논리적으로 연관된 작업의 집합 (DML로 이뤄짐). data 무결성을 지원하기 위함.
    2. 예를 들어, A 은행에서 B 은행으로 100만원을 송금한다고 하자. 그럼 A 은행은 -100만원, B 은행은 +100만원이 되어야 하는데, 그 사이 네트워크가 끝나서 A는 보냈는데 B는 받지 못하는 상황이 발생하면 Data 무결성이 깨짐
    3. 그래서, 중간에 문제가 발생하면 다 안됨. 즉 all or nothing.
    4. commit과 rollback으로 all or nothing을 작업.
  - lock : 데이터 보호, 동시성 관리.
    1. row-level lock을 사용
    2. 예를 들어, 사원 테이블에서 첫번째 row를 update하러 들어온 상황인데, commit을 하지 않음.
    3. 근데 누가 동일 row를 delete하려고 하면, 문제 발생.
    4. 그래서 첫번째 작업을 시작하기 전에 row를 locking해서, 두번째 작업이 기다리도록 wait 시킴.
    5. row-level이기 때문에, 다른 row이면 상관 없음.
    6. lock의 종류는 shared와 exclusive가 있음. shared는 query, exclusive는 DML
       따라서 변경 진행 중인 row에 대해서 SELECT하면 before image를 보고 변경 작업 전 row를 가지고 옴.
  - Tranaction 처리 절차
    1. Update emp set salary = 3000 where emp_id = 100;
    2. DML이 시작되면 오라클은 내부적으로 Tranaction으로 인식해서, transaction_id를 부여.
    3. DML은 DB가 바뀌는 작업이기 때문에, 변경 작업에 대한 기록을 남김.
      1) redo log : 유저가 했던 SQL문을 그대로 남겨놔서 다시 실행. 즉 Recovery가 목적.
      2) undo log : tx (transaction) rollback. 변경 되기 전 원래 값으로, before image라고 함. 또 read consistency를 위해서 사용. (같은 테이블을 작업하고 있는데, 다른 프로세스가 동일 테이블을 Query 날리면 이 로그를 참조해서 작업 전 테이블을 토대로 결과를 보여줌). flashback 기능.
    4. soft parse
    5. hard parse
    6. bind
    7. execute
      - DB Buffer cache에 없으면 (miss 시) Physical Read (disk block read).
      - redo log 기록
        1. log buffer에 user가 내린 SQL문을 그대로 넣어줌. + SCN 번호를 남겨줌 (Undo시 어디까지 Undo할 것인지 적기 위해서)
          - SCN : DB에 변경이 일어날 때마다, DB 변경이 어디까지 진행 되었는 지 번호를 남김 (System Change Number)
   - SCN_TO_TIMESTAMP라는 SCN 넘버를 넣어주면 타임을 알려주는 함수와, TIMESTAMP_TO_SCN 함수가 있음.
      - DB Buffer Cache에 undo buffer 기록 (기존 데이터) (commit 전에 rollback하고 싶으면 이 정보 참조)
      - DB Buffer Cache의 buffer 변경 (변경된 update된 데이터)
    8. 이때 다른 프로세스가 update emp set name = 'b' where emp_id = 500; commit을 하면,
    9. soft parse, hard parse, (bind), execute함.
    10. commit을 하면 data files에 disk write를 해야 하는데, oracle은 지연 쓰기를 함.
      - 지연 쓰기
        1. Background process 중 LGW가 SGA의 Log buffer를 redo log files에 저장.
        2. 이때 instance shut down 되면, 유저가 진행한 마지막 작업이 반영 되지 않으면 켜지지가 않음. 이를 해결하기 위해서 auto recovery함 (SMON이라는 백그라운드 프로세스가)
        3. redo log를 참조해서, data file에 반영. redo log file이지만, before image도 같이 달려 있음. 그래서 undo tablespace에 redo log를 읽으면서 recovery할 때, undo tablespace에 before image를 저장해줌.
        4. redo log files를 실행하고 있는데, tx 1은 commit을 하지 않았으니 undo tablespace를 참조해서 roll back 해줌.