[ORACLE] 튜닝포인트 : I/O, archive 파일, 블럭구조 확인하기

* file IO
1. System ,Nonsystem 테이블 스페이스 분리
2. users system user data(x) -> 시스템테이블스페이스에 들어가지 않게
3. table, index 분리
ex)create table xxx (a number primary key using index 절 table~
4. temporary tablespace 반드시 지정
5. RBS 필요

* redo log file IO
LGWR : DBWR_IO_SLAVES = 10 -> 여기서 프로세서를 빌려와서 쓴다.
size 대 : log switch 자주(x)
소 : log switch 자주(O)
Server
복구시간 고려

* check point
log_checkpoint_interval : 간격
log_checkpoint_timeout : 시간
Fast_start_IO_target : 다이나믹하게 interval,timeout 셋팅
DB_Block_MAX_Dirty_target : Dirty 블럭의 갯수

*Archive

<--아카이브 화일의 저장하는 곳이 많으면 IO가 많이 발생한다.
SQL> show parameter archive

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
log_archive_dest string
log_archive_dest_1 string location=C:\Oracle\oradata\ORC
L\archive : 저장위치
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_state_1 string enable : 아카이브가 저장될때까지 기다림
defer : 로그파일에 그냥 덥어씀

(파일저장공간의 틈이생길수 있음

log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
log_archive_dest_state_5 string enable
log_archive_duplex_dest string
log_archive_format string %%ORACLE_SID%%T%TS%S.ARC
log_archive_max_processes integer 1 : 최대 아카이브 프로세서
log_archive_min_succeed_dest integer 1
log_archive_start boolean TRUE
log_archive_trace integer 0
standby_archive_dest string %ORACLE_HOME%\RDBMS

<-- 아카이브 파일의 경로
SQL> select DEST_ID,
2 STATUS,
3 BINDING,
4 NAME_SPACE,
5 TARGET,
6 REOPEN_SECS,
7 DESTINATION,
8 FAIL_DATE,
9 FAIL_SEQUENCE,
10 FAIL_BLOCK
11 from v$archive_dest;

DEST_ID STATUS BINDING NAME_SP TARGET REOPEN_SECS
---------- --------- --------- ------- ------- -----------
DESTINATION
-----------------------------------------------------------------
FAIL_DATE FAIL_SEQUENCE FAIL_BLOCK
--------- ------------- ----------
1 VALID OPTIONAL SYSTEM PRIMARY 0
C:\Oracle\oradata\ORCL\archive: 예정경로~
0 0

2 INACTIVE OPTIONAL SYSTEM PRIMARY 0

0 0

DEST_ID STATUS BINDING NAME_SP TARGET REOPEN_SECS
---------- --------- --------- ------- ------- -----------
DESTINATION
-----------------------------------------------------------------
FAIL_DATE FAIL_SEQUENCE FAIL_BLOCK
--------- ------------- ----------

3 INACTIVE OPTIONAL SYSTEM PRIMARY 0

0 0

4 INACTIVE OPTIONAL SYSTEM PRIMARY 0

DEST_ID STATUS BINDING NAME_SP TARGET REOPEN_SECS
---------- --------- --------- ------- ------- -----------
DESTINATION
-----------------------------------------------------------------
FAIL_DATE FAIL_SEQUENCE FAIL_BLOCK
--------- ------------- ----------
0 0

5 INACTIVE OPTIONAL SYSTEM PRIMARY 0

0 0

<-- 아카이브된 파일에 대한 정보
SQL> select recid, stamp, name, sequence#, archived, blocks
2 from v$archived_log;

RECID STAMP NAME SEQUENCE# ARC BLOCKS
---------- ---------- -------------------------------------------------- ---------- --- ----------
14 544183646 C:\ORACLE\ORADATA\ORCL\ARCHIVE\ORCLT001S00002.ARC 2 YES 9
15 544191432 C:\ORACLE\ORADATA\ORCL\ARCHIVE\ORCLT001S00003.ARC 3 YES 74
.
.
.
88 544795798 C:\ORACLE\ORADATA\ORCL\ARCHIVE\ORCLT001S00068.ARC 68 YES 2048
89 544813796 C:\ORACLE\ORADATA\ORCL\ARCHIVE\ORCLT001S00069.ARC 69 YES 613
90 544958422 C:\ORACLE\ORADATA\ORCL\ARCHIVE\ORCLT001S00070.ARC 70 YES 57

SQL> select * from v$archive_processes;

PROCESS STATUS LOG_SEQUENCE STAT
---------- ---------- ------------ ----
0 ACTIVE 0 IDLE
1 STOPPED 0 IDLE
2 STOPPED 0 IDLE
3 STOPPED 0 IDLE
4 STOPPED 0 IDLE
5 STOPPED 0 IDLE
6 STOPPED 0 IDLE
7 STOPPED 0 IDLE
8 STOPPED 0 IDLE
9 STOPPED 0 IDLE

* 블럭(BLOCK)
1. 데이타베이스 블록 구조

① 블록헤더(Header): 일반적인 Block의 정보를 가지고 있습니다.
(Block의 위치, Segment의 형태)
85~100bytes정도

② Table Directory : 클러스터에 있는 테이블에 관한 정보를 가지고 있습니다.

③ Row Directory : Block내의 Row관련 정보를 가지고 있습니다.
각 Row마다 2byte

④ Free Space : New Row Insert나 Update시 사용 합니다.
PCTFREE값과 PCTUSED에 의해 결정 됩니다.

⑤ Row Data : 테이블 데이터와 인덱스 데이터를 포함 합니다.

2. 블록 공간 활용 파라메타

- 블록 공간 활용 파라메타는 데이타와 인덱스 세그먼트의 공간 사용을 제어하는데 사용할 수 있습니다.
- INITRANS, MAXTRANS, PCTFREE, PCTUSED등이 있습니다.

◈ INITRANS
- 최소 수준의 동시성을 보장 합니다.
예를 들어 INITRANS가 3으로 설정되면 최소 세 개의 트랜잭션이 동시에 블록을 변경할 수 있습니다
- Default : 1 (TABLE), 2 (INDEX and CLUSTER)

◈ MAXTRANS
- 데이터나 인덱스 블록에 동시에 변경을 가할 수 있는 트랜잭션 수를 제한 합니다.
- MAXTRANS의 수가 너무 작을 경우, 지정된 수 이상의 transaction이 생성되면
다른 transaction이 종료되어 사용가능한 transaction entry 공간이 생길때 까지 기다립니다.
- Default : 255

◈ PCTFREE ?(인덱스블럭은 없다.)
- 블럭내에 이미 존재하고 있는 Row에 Update가 가능하도록 예약시켜 놓는 블럭의 퍼센트 값을
지정 합니다

◈ PCTUSED ? (인덱스블럭은 0값을 가진다.)
- 오라클 서버가 테이블의 각 데이터 블록에 대해 유지하려는 사용 공간의 최소 백분율로써
데이터 세그먼트에 대해 지정합니다

block 주업무 Rollback
* OLTP 적은거 MTS(멀티스래드서버) DML index필수 크기작게 갯수많게
* DW 큰거 dedicated server 긴 select full table 크기크게 갯수적게

backup 주기
* OLTP 짧다
* DW길다

[출처] < 튜닝포인트 : I/O, archive 파일, 블럭구조 확인하기|작성자 쩡동