4. Database Buffer Cache

[ db 정상 서비스 조건 ]
 1. DB open 상태 확인  

1) 백그라운드 프로세스로 확인

$ ps -ef | grep pmon | grep -v grep

다음과 같은 상태가 나오면 DB 가동중

 

=> 아무것도 안뜨면 DB 내려가있음

 

2) DB 접속 후 status 조회

 

$ sqlplus / as sysdba

 

SQL> select status from v$instance;

정상오픈상태

 

=> 에러가 발생하거나 open이 아니면 정상적으로 DB가 올라온게 아님

 

 2. 리스너 프로세스 기동 확인

 

$  ps -ef | grep lsnr | grep -v grep

현재 기동중인 상태

=> 아무것도 출력되지 않는 경우 리스너 프로세스는 내려가 있음(기동 필요)

 

** 리스너 기동

$ lsnrctl start

 

3. 리스너 정상 서비스 여부 확인

$ lsnrctl status

하단에 이 부분이 출력이 되어야 서비스를 제대로 물었다고 볼 수 있다.

 

=> no services... 가 뜨면 아직 서비스를 못물고 있는 상태임

 

[ 원격 접속 설정 ]

       window orange 6               -------->            oel7 12c(db1) 접속 시도

  (orange 11g server 설치)                       sqlnet.ora 파일에 11g 접속 허용 설정  필요                                            

     

     tnsnames.ora  파일에

     db1 접속 정보를 기술필요

     (ip, 리스너 port, SID 확인)

 

- ip 확인 : oel7 서버에서 ifconfig로 확인 가능

- 리스너  port : lsnrctl status로 확인 / listener.ora 파일 확인

- sid 확인 : DB 접속 후 인스턴스명 확인

     SQL> select instance_name from v$instance;

 

[ SGA(System Global Area) ]

- 서버 프로세스들이 공유하는 메모리 공간

- parameter file에 SGA 구성 정보가 담겨있음

- 동적 변경 가능(spfile 환경일 경우)

- 자동 관리 가능(ASMM)

- shared pool / db buffer cache / redo log buffer 

   large pool / java pool / streams pool 로 구성

 

1. Shared pool

- SQL 정보(구문분석, 실행계획, 객체정보) 기록하는 공간

- hard parsing 방지를 위해 설계된 공간

 

1) library cache

   - 실행 계획 저장

 

2) dictionary cache

   - SQL 수행에 필요한 객체 정보를 저장하는 공간

 

2. Database buffer cache ( 가장 큼 ) 

 - 모든 SQL 실행 결과를 저장하는 메모리 공간(실제 데이터를 저장하는 공간)

- 매번 사용자가 요청하는 데이터를 디스크에서 불러오는 경우 성능 저하 발생

=> 한번 불러온 데이터를 메모리에 저장해두고 재사용

 

 

** Physical Read / Logical Read

  - 최초 실행되는 SQL의 경우 해당 데이터를 디스크에서 직접 불러와 메모리에 저장해 둠(Physical Read)

  - 사용자가 원하는 데이터가 메모리(Database buffer cache)에 남아 있는 경우

    다시 디스크에서 데이터를 불러오지 않고 메모리에 있는 데이터를 재사용함(Logical Read)

 

 

** Database buffer cache 상태

1. pinned buffer : 디스크로부터 데이터를 메모리 공간에 올려두고 사용중인 상태

 

2. dirty buffer :  (쉽게 말하면 반영을 기다리고 있는 상태)

    - 데이터 변경이 발생한 buffer

    - commit 이후 해당 내용을 datafile에 내려써야 되는 상태

    -  shutdown immediate 시 dirty buffer를 모두 datafile에 저장하고 종료(DBWR에 의해 수행됨)

    -  shutdwon abort 시 dirty buffer를 저장하지 않고 즉시 종료

       startup시 dirty buffer를 다시 저(instance recovery)

 

    

3. free buffer :

    - dirty buffer 가 DBWR에 의해 datafile에 내려써지면 free buffer가 되어 재사용 가능한 공간으로 할당

    - 아예 사용되지 않은 공간

pinned buffer 상태

 

하나의 블럭은 크기가 8k 이고 pinned buffer 한 블럭에 수정 추가 등등을 진행한 상태(commit이후)를 dirty buffer라고 한다. 그 후에 DBWR가 다시 블럭으로 내려놓은 상태를 free buffer라고 한다.

 

 

 

 

 

 

 

 

[ 파라미터 파일 ]

 

1. 확인(pfile or spfile)

SQL> show parameter pfile

SQL> show parameter spfile

현재 spfile을 물고있다.

 

2. 생성 및 변경(spfile -> pfile)

1) pfile 생성

SQL> create pfile from spfile;

: spfile과 같은 내용의 text 형식의 init parameter file 생김

 

 

나간 후 

 

확인하면 initdb1.ora 가 생성됨을 알 수 있다.

 

cat을 이용해서 init 파라미터 확인해보자.

 

2) DB shutdown 

SQL> shutdown immediate

3) spfile 삭제

$ cd $ORACLE_HOME/dbs

$ rm spfiledb1.ora

 

이후 

 

셧다운 뒤에 다시 startup

 

현재 상태는 spfile과 init파일이 둘 다 있는 상태에서 startup 한 상태인데 우선순위는 spfile이 우선순위이다.

 

그럼 init 파일 우선으로 열기 위해선

다시

 

이후 spfile 삭제시키자

 

4) DB startup

SQL> startup

 

5) 확인

SQL> show parameter pfile

Value에 아무것도 안보이면 pfile 환경임

 

spfile로 조회되지만 value값이 없다.

이는 pfile로 물었다는걸 알 수 있다.

 

 

3. 생성 및 변경(pfile -> spfile)

 

1) spfile생성

 

SQL> create spfile from pfile;

 

2) DB shutdown

 

SQL> shutdown immediate

 

3) DB startup

 

SQL> startup

 

4) 확인

SQL> show parameter pfile

 

 

만약 pfile, spfile을 둘 다 삭제했을 경우

 

initdb1.ora 파일을 만들고

 

백업본 또는 내용을 찾아서 넣은 뒤 

 

 

 

 

 

 

[실습]
1. 할당된 db buffer cache 사이즈 조회

1) parameter file 확인 

   - initdb1.ora : 정적 파라미터 파일

                         db1.__db_cache_size=180355072

 

   - spfiledb1.ora : 동적 파라미터 파일(binary 파일이므로 열면 깨질수 있음)

 

2) SQL prompt

SQL> show parameter db_cache_size

VALUE 가 0 인 것은 자동관리 되고 있다는 것을 알 수 있다. ASMM(automatic shared memory management)

 

3) v$parameter

 select name, value, display_value
   from v$parameter
  where name = 'db_cache_size';

0으로 조회됨(ASMM 환경)

 

2. 실제 사용량 조회

  select BYTES/1024/1024 as "SIZE(MB)"
    from v$sgastat
   where name = 'buffer_cache';

 

 

 

3. db buffer cache hit ration 조회

select

   from v$sysstat

where name in ('physical reads');

 

select sum(decode(name, 'physical reads', VALUE, 0)) as "physical reads size", 
           sum(decode(name, 'db block gets', VALUE, 0)) as "db block gets", 
           sum(decode(name, 'consistent gets', VALUE, 0)) as "consistent gets" 
 from v$sysstat
where name in ('physical reads', 'db block gets', 'consistent gets');

 

 

select round(1 - sum(decode(name, 'physical reads', VALUE, 0)) / 
           (sum(decode(name, 'db block gets', VALUE, 0)) +
           sum(decode(name, 'consistent gets', VALUE, 0)))*100,2) as "db buffer cache hit ratio"
 from v$sysstat
where name in ('physical reads', 'db block gets', 'consistent gets');

 

 

최소 80% 이상인게 좋음

 

hit ratio 가 낮으면 메모리를 거치지 않고 바로 디비에서 가져옴

hit ratio 가 높다는건 직접 메모리에서 가져온다는 것을 의미함

수동으로 관리하는 상황이면 데이터 버퍼 캐시를 조절

자동으로 관리하는 상황이면 SGA 사이즈를 조절