#41_140808_OWI_DB BUFFER CACHE

# DB BUFFER CACHE

1.     DB buffer cache의 용도

- database의 data file에서 읽은 데이터를 메모리에 올려놓으면 다음번에 빠르게 데이터를 검색할 수 있게 하기 위한 용도

2.     DB buffer cache의 성능 저하로 발생하는 대기 이벤트

1)   buffer busy wait

- 하나의 buffer 안의 특정 data를 읽거나 수정하려면 buffer lock을 획득해야 하는데 이 락을 누가 쓰고있다면 waiting 하는데 이떄 발생하는 대기 이벤트이다

- 특정 버퍼안의 데이터에 대해 update가 집중되는 경우 buffer busy wait이 발생한다

- busy buffer wait를 해결하는 방법

: 테이블의 pctfree를 늘림(pct free와 HWM과의 차이점은 pct free는 block에서의 경계값이고 HWM은 테이블 내에서 경계값이다)

: 파티션 테이블을 생성

2)   Latch : cache buffer chains

n Buffer busy wait 테스트(Awr report 확인)

SQL> connect owi/owi SQL> exec dbms_workload_repository.create_snapshot; @exec - buffer busy wait 대기 이벤트 생성(60초) SQL> exec dbms_workload_repository.create_snapshot; SQL> @?/rdbms/admin/awrrpt.sql - buffer busy waits 가 가장 많은 이벤트를 발생한 것을 확인할 수 있다 - 특정 버퍼의 테이블에 update가 집중됨을 확인 - 위의 상황이 발생하면 하나의 버퍼에는 하나의 버퍼락을 사용하기 때문에 다른 세션들은 대기할 수 밖에 없다 - 그래서 아래의 그림과 같이 data를 하나의 buffer에 하나의 row가 들어가도록 하면busy buffer waits를 줄일 수 있다 - 실제로는 1개씩은 아니고 buffer에 row를 분할해서 저장한다 >> ADDM Reprt를 떠서 recommend 확인 SQL> @?/rdbms/admin/addmrpt.sql Action       Consider rebuilding the TABLE "OWI.T_BUFFER_BUSY_WAITS" with object ID       88884 using a higher value for PCTFREE.       Related Object          Database object with ID 88884. - pctfree를 더 높이라고 추천하고 있다 >> buffer busy wait 대기이벤트의 pct free 확인하고 90으로 변경 # OWI SQL> connect owi/owi SQL> select table_name, pct_free     from user_tables     where table_name = 'T_BUFFER_BUSY_WAITS'; SQL> create table t_backup as       select * from T_BUFFER_BUSY_WAITS; SQL> alter table T_BUFFER_BUSY_WAITS pctfree 90; SQL> truncate table T_BUFFER_BUSY_WAITS; SQL> insert into T_BUFFER_BUSY_WAITS       select * from t_backup; - pct free를 90으로 변경하고 다시 insert 시킨다 SQL> commit; SQL> select dbms_rowid.rowid_block_number(rowid) as block_no, count(*)      from T_BUFFER_BUSY_WAITS      group by dbms_rowid.rowid_block_number(rowid); - block당 row가 몇 개 들어있는지 확인 - 결과를 확인해보면 pct free가 90%이기 때문에 여유공간을 크게 확장해서 아래와같이 각각의 block에 row가 한 개씩 들어가 있는 것을 확인할 수 있다 - 이럴경우 공간의 낭비가 심해지는 대신에 buffer busy wait 이벤트가 줄어든다 BLOCK_NO   COUNT(*) ---------- ----------      38551          1      38556          1      38565          1      38575          1 ………………………………………….. >> pct free를 변경하기 전과 후를 비교하기 위해 AWR compare report를 생성한다 SQL> @?/rdbms/admin/awrddrpt.sql SQL> exec dbms_workload_repository.create_snapshot; SQL> @exec SQL> exec dbms_workload_repository.create_snapshot; - buffer busy waits 이벤트가 완전히 없어지지는 않았지만 202초에서 65초로 줄어든 것을 확인할 수 있다

l DB buffer cache 튜닝방법

1.    악성SQL 튜닝

2.    pct free를 늘리거나 partition table 생성

3.    multiple buffer pool 구성 : buffer cache를 효율적으로 사용

- keep buffer pool

- recycle buffer pool

- default buffer pool

4.    buffer cache size를 늘린다

- SQL> show parameter db_cache_size

- db_cache_size는 해당하는 value 값 이하로 안내려 가도록 한다

l latch : cache buffer chain 대기 이벤트 확인과 해결방법

- 내가 찾고자 하는 데이터가 있는 버퍼를 검색하려면 latch를 확보해야 한다

- 악성SQL은 데이터를 찾기 위한 버퍼의 검색시간이 길어진다

- 결국 악성 SQL을 튜닝함으로써 해결할 수 있다

n cache_buffers_chains_latch(ADDM report)

SQL> exec dbms_workload_repository.create_snapshot; SQL> @exec - cache_buffer_chains_latch 대기 이벤트(세션30개, 60초) SQL> exec dbms_workload_repository.create_snapshot; SQL> @?/rdbms/admin/addmrpt.sql Action       Run SQL Tuning Advisor on the SELECT statement with SQL_ID       "0yfg8g1tfas99".       Related Object          SQL statement with SQL_ID 0yfg8g1tfas99.          SELECT /*+ INDEX(t idx_db_file_sequential_read) */ name          FROM t_db_file_sequential_read t          WHERE t.id > :"SYS_B_ - 부하를 주는 악성 SQL을 위처럼 인덱스를 이용하여 수정하라는 레포트 결과를 볼 수 있다