실습 - Physical I/O
- 신규 테이블 생성
create table hr.cbc_latch(id number, name char(100));
- 데이터 입력
insert into hr.cbc_latch(id,name)
select level, 'oracle'||level
from dual
connect by level <= 500000
order by dbms_random.value;
- 저장
commit;- 신규테이블 블록, 사이즈 확인
select blocks, bytes/1024/124 size_mb from dba_segments where owner='HR' and segment_name='CBC_LATCH';
- 인덱스 생성
create index hr.cbc_latch_idx on hr.cbc_latch(id);
- 인덱스 블록, 사이즈 확인
select blocks, bytes/1024/124 size_mb from dba_segments where owner='HR' and segment_name='CBC_LATCH_IDX';
- 생성된 인덱스는 nonunique로 설정되어있다.
select index_name, uniqueness from dba_indexes where index_name='CBC_LATCH_IDX';
- shared pool 메모리 공간 초기화
alter system flush shared_pool;- buffer cache 메모리 공간 초기화
alter system flush buffer_cache;<<session 1>>
execute dbms_application_info.set_client_info('sess_1')
begin
for i in (select /*+ index(c cbc_latch_idx) */ * from hr.cbc_latch c where id >= 0) loop
null;
end loop;
end;
/<<session 2>>
execute dbms_application_info.set_client_info('sess_2')
begin
for i in (select /*+ index(c cbc_latch_idx) */ * from hr.cbc_latch c where id >= 0) loop
null;
end loop;
end;
/<<session 3>>
select client_info, sid from v$session where client_info in ('sess_1','sess_2');
- 아직 다른 세션에서 sql 작업을 하지 않았지만 나오는 wait event 들은 버그성일 수 있다.
select sid, event, total_waits, time_waited from v$session_event where sid in (286,294);
- db file 관련 wait event는 physical I/O가 발생할때 생기는 이벤트이다.
db file sequential read: single block I/O 시 발생 이벤트db file scattered read: multi block I/O 시 발생 이벤트
- sess_1, sess_2에서 index range scan을 이용해 single block I/O를 유발했을때 발생되는 wait event 확인
select sid, event, total_waits, time_waited from v$session_event where sid in (286,294);
read by other session: phsical i/o가 발생하여 블록을 메모리에 올리는 도중 다른 세션에서 해당 블록을 메모리에서 읽으려고 하는 순간 발생하는 이벤트
- sql 작업이 끝난 이후에 해당 쿼리문의 sql id 확인하고 싶을때는 prev_sql_id로 확인할 수 있다.
select sql_id, prev_sql_id from v$session where sid in (286,294);
- 해당 sql를 실행한 유저를 찾고자 할때
- 이렇게 phsical i/o가 많이 발생하는 업무는 낮시간 보다는 밤 시간에 작업하는게 cpu 사용에 좋기 때문에 유저를 찾아 이야기를 할 필요가 있다.
select sql_text from v$sql where sql_id = '5chcmytd968m9';
- 실행계획은 library cache 안의 pl/sql area안의 LCO안에 있겠지만 프로그램 안에 있는 실행계획은 확인할 수 없다.
- 그렇기 때문에 따로 sql_text를 뽑아서 별도로 확인해야 한다.
select * from table(dbms_xplan.display_cursor('5chcmytd968m9'));
FLM(FreeList Management), 수동 세그먼트 공간관리
- freelist란 프리 블록을 링크드 리스트 형태로 관리하는 기법
- block의 free 상태를 pctused block parameter로 관리한다.
- pctused, freelists, freelist groups(RAC에서 사용)
- freelists 정보가 있는 블록을 access 못하면 기다려야한다. 그 때 발생하는 이벤트가
buffer busy waits이다.
- flm방식으로 테이블스페이스 생성
create tablespace flm_tbs
datafile '/u01/app/oracle/oradata/ORA19C/flm_tbs01.dbf' size 10m autoextend on
extent management local uniform size 1m
segment space management manual;- flm 방식의 테이블스페이스에 신규 테이블 생성
create table hr.flm_tab(id char(1000)) storage(freelists 1) tablespace flm_tbs;- flm방식에서 설정되어 있는 값 확인
select pct_free,pct_used, ini_trans, max_trans, freelists from dba_tables where owner='HR' and table_name='FLM_TAB';
실습 - FLM방식으로 insert시 발생하는 wait event
<<session 1>>
execute dbms_application_info.set_client_info('sess_1')
begin
for i in 1..100000 loop
insert into hr.flm_tab values('oracle'||to_char(i));
end loop;
commit;
end;
/<<session 2>>
execute dbms_application_info.set_client_info('sess_2')
begin
for i in 1..100000 loop
insert into hr.flm_tab values('oracle'||to_char(i));
end loop;
commit;
end;
/<<session 3>>
select client_info, sid from v$session where client_info in ('sess_1','sess_2');
select sid, event, total_waits, time_waited from v$session_event where sid in (52,286);
- freelist를 잡지 못해 기다리는
buffer busy wait event가 가장 많이 발생한걸 확인할 수 있다. control file sequential read wait event가 발생할 수 있는데 데이터를 insert 할때 extent의 확장정보를 control file(v$datafile)에도 기록해야 하기 때문에 발생한다.enq: HW - contention: os block을 가지고 oracle이 사용가능한 block으로 format을 변경해야하는데 변경하는 동안에는 HWM(High Water Mark)가 움직일 수 없기 때문에 발생하는 이벤트문에 발생하는 이벤트- HWM는 extent 있는 블록 중에 사용된 공간과 아직 사용하지 않은 공간을 구분하는 표시
- 세그먼트 헤더에서 관리하다.
- HWM 이동은 5 블록 만큼 이동한다.
ASSM(Automatic Segment Space Management), 자동 세그먼트 공간관리
- 프리 블록에 대한 모든 정보들은 비트맵 블록에서 관리한다.
- 비트맵 블록은 총 3단계 깊이의 트리로 관리한다.
- root node : 3rd level bmb, L3 BMB
- brach node : 2nd level bmb, L2 BMB
- leaf node : 1st level bmb, L1 BMB
- 블록의 free 공간의 상태를 6가지로 관리(leaf node에서 관리)
- full
- unformated
- 0~25% free
- 25~50% free
- 50~75% free
- 75~100% free
- pctused, freelists, freelist groups(RAC) 사용하지 않는다.
ASSM에서도 아예 buffer busy wait가 발생하지 않는것은 아니다. leaf node로 가기 위해서는 가장 먼저 root node를 찾아가야 하는데, root node에 대한 경합이 발생할때 buffer busy wait가 발생할 수 있다.
- assm 방식의 테이블스페이스 생성
create tablespace assm_tbs
datafile '/u01/app/oracle/oradata/ORA19C/assm_tbs01.dbf' size 10m autoextend on
extent management local uniform size 1m
segment space management auto;- assm 방식의테이블스페이스에 신규 테이블 생성
create table hr.assm_tab(id char(1000)) tablespace assm_tbs;- assm 방식으로 생성하였기 때문에 pct_used와 freelists는 설정되지 않는다.
select pct_free,pct_used, ini_trans, max_trans, freelists from dba_tables where owner='HR' and table_name='ASSM_TAB';
실습 - ASSM방식으로 insert시 발생하는 wait event
<<session 1>>
execute dbms_application_info.set_client_info('sess_1')
begin
for i in 1..100000 loop
insert into hr.assm_tab values('oracle'||to_char(i));
end loop;
commit;
end;
/<<session 2>>
execute dbms_application_info.set_client_info('sess_2')
begin
for i in 1..100000 loop
insert into hr.assm_tab values('oracle'||to_char(i));
end loop;
commit;
end;
/<<session 3>>
select client_info, sid from v$session where client_info in ('sess_1','sess_2');
select sid, event, total_waits, time_waited from v$session_event where sid in (52,278);buffer busy waits와enq: HW - contention가 FLM 방식에 비해 줄어든걸 확인할 수 있다.db file single write: 데이터파일의 헤더블록을 수정할때 발생하는 이벤트
Database buffer cache(keep 영역)
- 사용할수 있는 free 메모리 공간 확인
- Granule Size : 메모리의 확장 단위, 배수 단위로 확장하고 줄인다.
select name, bytes/1024/1024 size_mb from v$sgainfo;
- buffer cache size를 v$buffer_pool로도 확인할 수있다(size는 mb)
select name, current_size from v$buffer_pool;
- EMPLOYEES 테이블의 block은 default database buffer cache에 올라간다.
select blocks, buffer_pool from dba_tables where owner='HR' and table_name='EMPLOYEES';
- dba_segments로는 전체 extent의 크기를 알 수있고 dba_tables는 HWM가 있는 블록까지의 block의 수를 알려준다.
select blocks,extents,bytes, buffer_pool from dba_segments where owner='HR' and segment_name='EMPLOYEES';
- keep 영역에서의 cache hits를 높게 하기 위해서는 hit를 할 테이블의 전체 사이즈 만큼 메모리를 할당하는게 좋다.
db_keep_cache_size는 dynamic parameter이다.
alter system set db_keep_cache_size = 1m scope=both;- 다시 buffer cache 종류 확인
- 1mb로 요청했지만 오라클이 8mb로 만들어버렸다. Granule Size의 배수 단위로만 memory를 확장하기 때문이다.
- 기존 default cache 영역이 shrink 되었다.
select name, current_size from v$buffer_pool;
- hr.employees 테이블의 메모리 저장공간을 keep 영역으로 변경
alter table hr.employees storage(buffer_pool keep);- 변경된 영역 확인
select blocks, buffer_pool from dba_tables where owner='HR' and table_name='EMPLOYEES';
- 인덱스가 사용하는 buffer cache 영역 확인
- 인덱스도 데이터블록이 올라가는 keep영역으로 이동해주는게 좋다.
select index_name, buffer_pool from dba_indexes where owner='HR' and table_name='EMPLOYEES';
- 인덱스가 올라가는 database buffer cache 영역 변경
alter index hr.emp_emp_id_pk storage(buffer_pool keep);- 변경 확인
select index_name, buffer_pool from dba_indexes where owner='HR' and table_name='EMPLOYEES';
- dba_segments 로도 확인할 수 있다.
select blocks,extents,bytes, buffer_pool from dba_segments where owner='HR' and segment_name='EMP_EMP_ID_PK';
- 다시 default로 변경해야할 때
alter index hr.emp_emp_id_pk storage(buffer_pool default);
alter table hr.employees storage(buffer_pool default);