프린트 하기

내맘대로긍정이 알려주는

Oracle 23ai 신기능
무료 세미나 발표자료

다운로드 trending_flat

OS환경 : Oracle Linux 7.5 (64bit)


DB 환경 : Oracle Database 19.3.0.0


방법 : oracle 19c 버퍼캐쉬 플러쉬 테스트(buffer cache flush)


테이블 생성

1
2
3
SQL> CREATE TABLE MAXTEST (COLA NUMBER, COLB NUMBER, COLC NUMBER);
 
Table created.



벌크 INSERT 실행

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF MAXTEST%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..1000000 LOOP 
   w_ins(i).COLA :=i;
   w_ins(i).COLB :=10;
   w_ins(i).COLC :=99;
END LOOP;
   FORALL i in 1..1000000 INSERT INTO MAXTEST VALUES w_ins(i);
   COMMIT;
END;
/

/ 를 10번 반복 실행



테이블 카운트 확인

1
2
3
4
5
SQL> select count(*) from maxtest;
 
  COUNT(*)
----------
  10000000

  


버퍼캐쉬 확인

1
2
3
4
5
6
7
8
9
10
11
SQL> SELECT O.OBJECT_TYPE, SUBSTR(O.OBJECT_NAME, 1, 10) OBJECT_NAME, 
B.OBJD, B.STATUS, COUNT(B.OBJD)
FROM V$BH B, DBA_OBJECTS O
WHERE B.OBJD = O.DATA_OBJECT_ID 
AND O.OBJECT_NAME = 'MAXTEST'
GROUP BY O.OBJECT_TYPE, O.OBJECT_NAME, B.OBJD, B.STATUS
/
 
OBJECT_TYPE        OBJECT_NAME           OBJD STATUS       COUNT(B.OBJD)
----------------------- -------------------- ---------- ---------- -------------
TABLE            MAXTEST           72057 xcur           22014

버퍼캐쉬에 STATUS 가 xcur 상태로 올라가 있음



V$BH 뷰의 STATUS 컬럼 값 설명

1
2
3
4
5
6
7
8
9
10
11
12
V$BH 뷰의 Status 
free - Not currently in use
xcur - Exclusive
scur - Shared current
cr - Consistent read
read - Being read from disk
mrec - In media recovery mode
irec - In instance recovery mode
pi - A past image in RAC mode
securefile - A secured file buffer
flashfree - A free flash cache buffer
flashcur - A current flash cache buffer



버퍼캐쉬 플러쉬

1
2
3
SQL> alter system flush buffer_cache;
 
System altered.



다시 버퍼캐쉬 확인

1
2
3
4
5
6
7
8
9
10
11
SQL> SELECT O.OBJECT_TYPE, SUBSTR(O.OBJECT_NAME, 1, 10) OBJECT_NAME, 
B.OBJD, B.STATUS, COUNT(B.OBJD)
FROM V$BH B, DBA_OBJECTS O
WHERE B.OBJD = O.DATA_OBJECT_ID 
AND O.OBJECT_NAME = 'MAXTEST'
GROUP BY O.OBJECT_TYPE, O.OBJECT_NAME, B.OBJD, B.STATUS
/
 
OBJECT_TYPE        OBJECT_NAME           OBJD STATUS       COUNT(B.OBJD)
----------------------- -------------------- ---------- ---------- -------------
TABLE            MAXTEST           72057 free           22014

버퍼캐쉬에 STATUS 가 FREE 상태로 표시됨



쿼리 1개 실행

1
2
3
4
5
SQL> 
select * from maxtest 
where COLA like '350%' 
order by 1
/



다시 버퍼캐쉬 확인

1
2
3
4
5
6
7
8
9
10
11
12
SQL> SELECT O.OBJECT_TYPE, SUBSTR(O.OBJECT_NAME, 1, 10) OBJECT_NAME, 
B.OBJD, B.STATUS, COUNT(B.OBJD)
FROM V$BH B, DBA_OBJECTS O
WHERE B.OBJD = O.DATA_OBJECT_ID 
AND O.OBJECT_NAME = 'MAXTEST'
GROUP BY O.OBJECT_TYPE, O.OBJECT_NAME, B.OBJD, B.STATUS
/
 
OBJECT_TYPE        OBJECT_NAME           OBJD STATUS       COUNT(B.OBJD)
----------------------- -------------------- ---------- ---------- -------------
TABLE            MAXTEST           72057 xcur               1
TABLE            MAXTEST           72057 free               1

버퍼캐쉬에 STATUS 가 XCUR 이 하나 더 생김



업데이트 구문 실행

1
2
3
SQL> update maxtest set cola=0 where cola=10000;
 
10 rows updated.

10개 row 업데이트



다시 버퍼캐쉬 확인

1
2
3
4
5
6
7
8
9
10
11
12
SQL> SELECT O.OBJECT_TYPE, SUBSTR(O.OBJECT_NAME, 1, 10) OBJECT_NAME, 
B.OBJD, B.STATUS, COUNT(B.OBJD)
FROM V$BH B, DBA_OBJECTS O
WHERE B.OBJD = O.DATA_OBJECT_ID 
AND O.OBJECT_NAME = 'MAXTEST'
GROUP BY O.OBJECT_TYPE, O.OBJECT_NAME, B.OBJD, B.STATUS
/
 
OBJECT_TYPE        OBJECT_NAME           OBJD STATUS       COUNT(B.OBJD)
----------------------- -------------------- ---------- ---------- -------------
TABLE            MAXTEST           72057 xcur           22014
TABLE            MAXTEST           72057 cr              10

버퍼캐쉬에 STATUS 가 CR이 10개 생김



다른 세션에서 똑같은 업데이트 구문 한번더 실행

1
2
SQL> update maxtest set cola=0 where cola=10000;
...lock



다시 버퍼캐쉬 확인

1
2
3
4
5
6
7
8
9
10
11
12
SQL> SELECT O.OBJECT_TYPE, SUBSTR(O.OBJECT_NAME, 1, 10) OBJECT_NAME, 
B.OBJD, B.STATUS, COUNT(B.OBJD)
FROM V$BH B, DBA_OBJECTS O
WHERE B.OBJD = O.DATA_OBJECT_ID 
AND O.OBJECT_NAME = 'MAXTEST'
GROUP BY O.OBJECT_TYPE, O.OBJECT_NAME, B.OBJD, B.STATUS
/
 
OBJECT_TYPE        OBJECT_NAME           OBJD STATUS       COUNT(B.OBJD)
----------------------- -------------------- ---------- ---------- -------------
TABLE            MAXTEST           72057 xcur           22014
TABLE            MAXTEST           72057 cr              11

cr이 10개에서 11개로 변경됨



업데이트 구문 실행한 세션 2개 모두 rollback 실행

1
2
3
4
5
6
7
8
9
첫번 째 세션
SQL> rollback;
 
Rollback complete.
 
두번 째 세션
SQL> rollback;
 
Rollback complete.



다시 버퍼캐쉬 확인

1
2
3
4
5
6
7
8
9
10
11
12
SQL> SELECT O.OBJECT_TYPE, SUBSTR(O.OBJECT_NAME, 1, 10) OBJECT_NAME, 
B.OBJD, B.STATUS, COUNT(B.OBJD)
FROM V$BH B, DBA_OBJECTS O
WHERE B.OBJD = O.DATA_OBJECT_ID 
AND O.OBJECT_NAME = 'MAXTEST'
GROUP BY O.OBJECT_TYPE, O.OBJECT_NAME, B.OBJD, B.STATUS
/
 
OBJECT_TYPE        OBJECT_NAME           OBJD STATUS       COUNT(B.OBJD)
----------------------- -------------------- ---------- ---------- -------------
TABLE            MAXTEST           72057 xcur           22014
TABLE            MAXTEST           72057 cr              20

첫번 째 세션 rollback 으로 인해 두번 째 세션의 업데이트 구문이 실행되어서 20으로 변경됨

rollback 해도 cr 상태 유지



버퍼캐쉬 플러쉬

1
2
3
SQL> alter system flush buffer_cache;
 
System altered.



다시 버퍼캐쉬 확인

1
2
3
4
5
6
7
8
9
10
11
SQL> SELECT O.OBJECT_TYPE, SUBSTR(O.OBJECT_NAME, 1, 10) OBJECT_NAME, 
B.OBJD, B.STATUS, COUNT(B.OBJD)
FROM V$BH B, DBA_OBJECTS O
WHERE B.OBJD = O.DATA_OBJECT_ID 
AND O.OBJECT_NAME = 'MAXTEST'
GROUP BY O.OBJECT_TYPE, O.OBJECT_NAME, B.OBJD, B.STATUS
/
 
OBJECT_TYPE        OBJECT_NAME           OBJD STATUS       COUNT(B.OBJD)
----------------------- -------------------- ---------- ---------- -------------
TABLE            MAXTEST           72057 free           22031

OBJD의 COUNT 가 조금 증가했음



재기동 후 확인

1
2
3
4
5
6
7
8
9
10
SQL> startup force
ORACLE instance started.
 
Total System Global Area 1677718256 bytes
Fixed Size            8897264 bytes
Variable Size          536870912 bytes
Database Buffers     1124073472 bytes
Redo Buffers            7876608 bytes
Database mounted.
Database opened.



버퍼캐쉬 확인

1
2
3
4
5
6
7
8
9
SQL> SELECT O.OBJECT_TYPE, SUBSTR(O.OBJECT_NAME, 1, 10) OBJECT_NAME, 
B.OBJD, B.STATUS, COUNT(B.OBJD)
FROM V$BH B, DBA_OBJECTS O
WHERE B.OBJD = O.DATA_OBJECT_ID 
AND O.OBJECT_NAME = 'MAXTEST'
GROUP BY O.OBJECT_TYPE, O.OBJECT_NAME, B.OBJD, B.STATUS
/
 
no rows selected



DB 기동중에 버퍼캐쉬에 블록을 올린다음 

버퍼캐쉬 플러쉬를 하면 v$bh에 status free로 남아있게되고

DB를 재기동하면 v$bh에서도 사라짐



참조 : 

https://positivemh.tistory.com/364

https://positivemh.tistory.com/183

https://smarttechways.com/2017/09/19/flush-shared-pool-buffer-cache-in-oracle/

http://develop.sunshiny.co.kr/469

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-BH.html#GUID-A8230335-47C4-4707-A866-678DD8D322A8


저작자표시

'ORACLE > Admin' 카테고리의 다른 글

오라클 11g R2 tfa(ahf) 설치 및 로그수집 가이드(SRDC)  (0) 2020.06.27
오라클 19c $ORACLE_HOME/dbs 디렉토리의 hc_{SID}.dat 파일과 lk{SID} 파일  (3) 2020.06.25
오라클 11g R2 싱글 DB 환경에서 grid 기동 및 중지  (0) 2020.06.24
오라클 19c Hugepages 설정 방법  (3) 2020.06.24
오라클 11g crsctl stop crs 시 db의 shutdown 명령  (0) 2020.06.19

티스토리툴바