#16_140707_WSHOP_데이터베이스 구조2

# 데이터베이스 구조

1. DB BUFFER CACHE

•  cache 속성으로 만들어야할 테이블은 무엇인가?

- 자주 엑세스하는 크기가 작은 테이블

- 테이블을 그냥 생성하면 디폴트가 noCache 이다

• 테이블의 속성이 Cache 인지 noCache 인지 확인하는 방법

            SELECT table_name, CACHE

            FROM user_tables;

• emp 테이블을 cache 속성으로 변경하는 방법

            alter table emp cache;

• emp 테이블을 엑세스할 때 메모리의 MRU로 무조건 올리고 싶을데 alter table 권한이 없을경우 힌트를 쓴다 

          SELECT /*+ cache */ ename, sal

          FROM EMP

          WHERE ename='SCOTT';

   l 이 SQL로 읽은 데이터는 full table scan 이든 index scan 이든 무조건 MRU로 올라간다       

   l /*+ 힌트 */ --- > 힌트 : 오라클에게 실행하는 방법을 명령하는 것(MRU로 올려라)

- LRU 알고리즘 : 가장 최근에 엑세스한 데이터를 메모리에 오랫동안 두고 오래된 data는 메모리에서 빠져나가도록 운영하는 알고리즘

- DB buffer cache 메모리 영역을 효율적으로 사용하기 위해 사용하는 오라클 알고리즘

l LRU 알고리즘 사용하는 테이블의 속성

1. noCache 속성

- full table scan : LRU 로 올린다

- index scan : MRU 로 올린다

2. cache 속성

- full table scan : MRU로 올린다

- index scan : MRU로 올린다

l DB buffer cache 의 버퍼의 종류

1. free buffer : 비어있는 버퍼

2. pinned buffer : 비어있지는 않은데 변경되지 않는 버퍼

3. dirty buffer : 변경된 버퍼

2. REDO LOG BUFFER

• LOG BUFFER 확인

      SQL> show parameter log_buffer

      NAME                                 TYPE                   VALUE

       ------------------------------------ ---------------------- ----------

     log_buffer                           integer                4419584

l log buffer는 4.2MB(4419584/1024/1024) 한정된 공간으로 계속 업데이트 된다.

• UPDATE 문의 처리과정

    UPDATE EMP

    SET sal = 0

   WHERE ename='SCOTT';

1. parsing : SQL을 기계어로 바꾸는 것

2. execute : 데이터를 검색해서 변경작업 수행

- buffer cache 에서 변경하려는 데이터를 검색

- buffer cache 에 없으면 data file 에서 찾아서 buffer cache로 올린다

- 해당 row에 락(lock)을 건다

- redo log buffer 에 3000 을 0으로 변경하겠다고 적는다. 그리고 아직 commit 안한 상태이다

- 변경전 데이터를 rollback buffer 에 적는다

- 데이터를 변경한다

l 다른 세션은 rollback buffer의 3000을 출력

l 디스크에 저장하지 않은 상태에서 scott의 계정에서 scott의 월급을 출력하면 0을 출력한다

3. LGWR(Log Writer)

• LGWR가 디스크로 LOG BUFFER의 내용을 내려쓰는 시점

1. commit 할 떄

2. redo log buffer가 1/3 이상 찼을 때

3. DBWR가 변경사항을 내려쓰기전에

4. cehckpoint event 가 발생되었을 때

l checkpoint event

- 메모리의 변경사항을 주기적으로 자동으로 발생시키는 이벤트

- 이 이벤트를 발생시키는 백그라운드 프로세서는 CKPT이다.

4. DBWR(Database Writer)

• DBWR가 변경된 버퍼를 내려쓰는 시점

1.     checkpoint event 발생시 내려쓴다

2.     buffer cache의 변경된 버퍼가 1/3 이상이 되면 내려쓴다

3.     서버 프로세서가 data file 에서 data block 을 메모리로 올릴 때 free buffer를 찾아야 올릴 수 있는데 오랫동안 free buffer를 못찼을 때 

• DBWR는 commit 할때 작동하지 않고 LGWR만 작동하는 이유는?

- DBWR까지 작동시키면 commit 시간이 너무 오래걸리고 성능이 떨어진다

- LGWR 가 내려쓰는 변경사항(redo entries)은 크기가 아주 작지만 DBWR가 내려쓰는 변경된 버퍼들은 크기가 아주 크다

5. SMON(system monitor)

- 커밋했지만 data file 에 반영안된 데이터를 리두로그 파일을 보고 반영해주는 데몬(=instance recovery)

- commit 이후 서버가 전원이 꺼져도 somn이 반영해준다

6. ARCH(Archive proces)

- 리두로그 파일을 백업하는 프로세서

• ARCHIVE PRCESS 가 활성화 되어 있는지 확인하는 명령어

- archive log list

- select file_name from dba_data_files; - data file 의 위치확인하는 명령어

7. CKPT(Checkpoint Event)

l checkpoint event

- 메모리의 변경사항을 주기적으로 자동으로 발생시키는 이벤트

- 이 이벤트를 발생시키는 백그라운드 프로세서는 CKPT이다.

8. 명령어

• BLOCK SIZE 확인
• SQL> SHOW parameter db_block_size

NAME                                 TYPE                   VALUE

------------------------------------ ---------------------- 

db_block_size                        integer                8192

l 8192는 byte 이고 1024로 나누면 8이므로 8kb이다

data file block과 cache 의 사이즈는 똑같다

• LOG BUFFER SIZE 확인
• SQL> show parameter log_buffer

NAME                                 TYPE                   VALUE

------------------------------------ ---------------------- ----------

log_buffer                           integer                4419584

9. DB 내부 파일

l DB 내부 3가지 파일

1. data file

2. control file

3. redo log file

•  DB 내부 파일과 DB 외부 파일의 차이점은 내부파일은 운영중에 손상되면 정상작동 하지 않지만 외부파일은 관계없다

10. DB 외부 파일

l DB 외부 파일 4가지

1. parameter file : 인스턴스를 구성하기 위한 정보가 들어있는 파일

show parameter spfile

2. password file : 특별한 권한(DB를 올렸다 내렸다 할 수 있는 권한)을 가진 유저를 인증해 주기 위한 파일

- 패스워드 파일에 등록된 유저를 조회하는 방법

select * from v$pwfile_users;

3. archive log file : redo logfile 의 복사본

select name from v$archived_log;

4. alert log file : DB에 문제가 생겼을 때 진단을 하기 위한 파일

- alter log file 의 위치를확인

show parameter background_dump_dest

l scott 유저를 패스워드 파일에 등록시키는 방법

- grant sysdba to scott;

- select * from v$pwfile_users;st

- scott 으로 접속할 때 : connect scott/tiger as sysdba

11. EXTENT

문제21. emp302 테이블이 몇 개의 extent 로 구성되어 있는지 확인하시오

SELECT extent_id, blocks, bytes/1024

FROM dba_extents

WHERE segment_name='EMP302';

EXTENT_ID  BLOCKS   BYTES/1024

----------   ----------  ----------

  0          8         64

l 반복적으로 데이터를 대용량으로 집어넣으면 아래와 같이 extent 가 늘어난 것을 아래의 그림에서 확인할 수 있다

문제23. system 테이블 스패이스와 sysaux 테이블 스패이스의 역할이 무엇인가?

l system tablespace : data dictionary(사전) 이 들어있음

- user_tables

- all_tables

- dba_tables

l sysaux tablespace : DB의 성능정보가 들어있는 파일

l 오라클 DB가 10g 버전에서부터 크게 달리진 점이 무엇인가

- 스스로 튜닝하는 기능이 생겼다(sysaux tablespace 파일에 저장)

문제190. 아래의 데이터를 입력하고 위에서 만든 패키지를 수정하는데 ORA-01427 에러가 나서 수행이 안되면 똑 같은 사원번호인 사원이 여러명 있어서 수행이 안됩니다. 라는 메시지가 출력되게 예외처리 하시오

INSERT INTO emp(empno,ename,sal) VALUES(7788,'JACK',3400);

CREATE OR REPLACE PACKAGE pack_avgsal IS

 FUNCTION get_avgsal(p_empno emp.empno%type) RETURN NUMBER;

 FUNCTION get_maxsal(p_empno emp.empno%type) RETURN NUMBER;

 v_avgsal NUMBER(10);

 v_maxsal NUMBER(10);

END pack_avgsal;

/

  

CREATE OR REPLACE PACKAGE BODY pack_avgsal IS

 FUNCTION get_avgsal(p_empno emp.empno%TYPE) RETURN NUMBER IS

  expt EXCEPTION;

  PRAGMA exception_init(expt, -01427);

  BEGIN

   SELECT AVG(sal) INTO v_avgsal

   FROM EMP

   WHERE deptno=(SELECT deptno FROM EMP WHERE empno=p_empno);

   RETURN v_avgsal;

  

   EXCEPTION

    WHEN expt THEN

    dbms_output.put_line('중복된 데이터가 있습니다');

   

 END get_avgsal;

 FUNCTION get_maxsal(p_empno emp.empno%type) RETURN NUMBER IS

  expt EXCEPTION;

  PRAGMA exception_init(expt, -01427);

  

  BEGIN

   SELECT MAX(sal) INTO v_maxsal

   FROM EMP

   WHERE deptno=(SELECT deptno FROM EMP WHERE empno=p_empno);

  RETURN v_maxsal;

  EXCEPTION

   WHEN expt THEN

    dbms_output.put_line('중복된 데이터가 있습니다');

  END get_maxsal;

END pack_avgsal;

/

마지막문제. 패키지를 생성하고 exec 명령어를 실행하여 아래와 같은 결과가 출력되도록 하시오

SELECT file_name FROM dba_data_files;

SELECT NAME FROM v$controlfile;

SELECT member FROM v$logfile;

SQL> exec dbfile.datafile2;

C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF

C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF

C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF

C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF

C:\DATA01.DBF

SQL> exec dbfile.controlfile2;

C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF

SQL> exec dbfile.redofile2;

C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_2_9PV238OG_.LOG

C:\ORACLEXE\APP\ORACLE\FAST_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_9PV237TV_.LOG

CREATE OR REPLACE PACKAGE dbfile IS

 PROCEDURE datafile2;

 PROCEDURE controlfile2;

 PROCEDURE redofile2;

END dbfile;

/

CREATE OR REPLACE PACKAGE BODY dbfile IS

 PROCEDURE datafile2 IS

  CURSOR data_cursor IS

   SELECT file_name FROM dba_data_files;

  BEGIN

   FOR data_record IN data_cursor LOOP

    dbms_output.put_line(data_record.file_name);

   END LOOP;

 END datafile2;

 PROCEDURE controlfile2 IS

  CURSOR control_cursor IS

   SELECT NAME FROM v$controlfile;

  

  BEGIN

   FOR control_record IN control_cursor LOOP

    dbms_output.put_line(control_record.name);

   END LOOP;

 END controlfile2;

 PROCEDURE redofile2 IS

  CURSOR redo_cursor IS

   SELECT member FROM v$logfile;

  BEGIN

   FOR redo_record IN redo_cursor LOOP

    dbms_output.put_line(redo_record.member);

   END LOOP;

  END redofile2;

END dbfile;

/

EXEC dbfile.datafile2;

EXEC dbfile.controlfile2;

EXEC dbfile.redofile2;