꿈꾸는 개발자, DBA 커뮤니티 구루비

오라클 성능 분석 방법론 정보

RATIO ANALYSIS - 성능 분석 방법론
Ratio 라는 것이 0 에서 1사이의 범위를 가진 수치로 표현하여 상대적인 수치를 절대적인 수치로 변경하였기 때문에 언제, 어느 데이터베이스에 적용하더라도 같은 단위로 비교가 가능하다는 의미이다.
ORACLE 성능분석 방법론 - 김한도 지음

공유 풀 튜닝

오라클의 공유 풀 영역은 라이브러리 캐시와 딕셔너리 캐시로 구분되며, 라이브러리 캐시에는 SQL 문장과 PL/SQL 문장이 저장되고, 딕셔너리 캐시에는 데이터베이스에 관련된 각종 정보들이 딕셔너리로 저장된다.

HIT 율 ( 90% 보다 커야 한다. )


sys@ARTDOM>select gets, gethitratio, pins, pinhitratio, reloads, invalidations
  2  from v$librarycache
  3  where namespace ='SQL AREA';

      GETS GETHITRATIO       PINS PINHITRATIO    RELOADS INVALIDATIONS
---------- ----------- ---------- ----------- ---------- -------------
    340751  .990635391    1383994  .995631484        756          1197

재파싱 율 ( 1% 보다 작게 나와야 한다. )


sys@ARTDOM>select (sum(reloads)/sum(pins))*100 "MISS RATE"
  2  from v$librarycache ;

 MISS RATE
----------
.083522836

라이브러리 캐시의 HIT 율을 향상 시키는 방법

1. init<SID>.ora 파일에서 shared_pool_size 를 크게 한다.
2. 명명표준을 준수하여 동일한 문장에 대해 재파싱 작업이 일어나지 않게 한다.
3. 상수를 사용하기보다는 바인딩 변수를 사용한다.

딕셔너리 캐시튜닝


sys@ARTDOM>  select to_char(trunc(sum(getmisses)/sum(gets)*100,5),0999.99)||'%(LESS THAN 15%)' "MISS RATE"
              from v$rowcache ;
MISS RATE
-----------------------
   4.05%(LESS THAN 15%)

리두로그 버퍼 튜닝

WAIT_RATIO 가 1% 이상이 나오면 리두로그가 발생할 때 경합이 발생한 경우다.
리두로그 버퍼는 모든 SQL 문장이 실행될 때 이용하는 메모리 영역이므로 작업하는 동안은 이 메모리 공간을 이용해야 한다.
데이터에 대해 수정이 발생하면 잠금을 설정하듯이 메모리 영역에서 다른 프로세스가 이용하지 못하도록 독점하는 방법으로 래치(LATCH)를 할당받아 작업한다.
래치를 할당받지 못한 SQL 문장들은 래치를 할당받을 때까지 기다려야 한다.
그러므로 대기율을 분석하여 오랜 시간동안 대기하고 있거나 잦은 대기 상태가 되는 경우에는 리두 로그 버퍼의 크기를 조정해야 한다.


sys@ARTDOM>select a.name, (b.misses/b.gets)*100 WAIT_RATIO
    from v$latchname a, v$latch b
    where a.name in ('redo allocation') and
    a.latch# = b.latch# ;

NAME                                               WAIT_RATIO
-------------------------------------------------- ----------
redo allocation                                    .034880924

sys@ARTDOM>select name, value from v$sysstat where name ='redo log space requests';

NAME                                                    VALUE
-------------------------------------------------- ----------
redo log space requests                                    57

디스크 I/O 튜닝

특정 디스크에 있는 데이터 파일에 I/O가 집중되면 이는 성능 저하의 요인이 되므로 I/O 현황을 분석하여 집중된 I/O를 분산시켜야 한다.


sys@ARTDOM> select name, phyrds, phywrts, phyblkrd, phyblkwrt
            from v$filestat, v$datafile
             where v$filestat.file# = v$datafile.file# ; 

NAME                                                   PHYRDS    PHYWRTS   PHYBLKRD  PHYBLKWRT
-------------------------------------------------- ---------- ---------- ---------- ----------
/u02b/ORACLE/ARTDOM/ARTDOM/system01.dbf                  5381      18424       7194      19874
/u02b/ORACLE/ARTDOM/ARTDOM/undotbs01.dbf                   47      11516         47      26253
/u02b/ORACLE/ARTDOM/ARTDOM/sysaux01.dbf                  3212      19088       9979      26131
/u02b/ORACLE/ARTDOM/ARTDOM/DATA/users01.dbf                50         36        179        180
/u02b/ORACLE/ARTDOM/ARTDOM/example01.dbf                   35         26         43         26
/u02b/ORACLE/ARTDOM/ARTDOM/DATA/users02.dbf                59         43        183        187
/u02b/ORACLE/ARTDOM/ARTDOM/DATA/users03.dbf                42         32        158        156
/u02b/ORACLE/ARTDOM/ARTDOM/DATA/users04.dbf                42         32        158        156
/u02b/ORACLE/ARTDOM/ARTDOM/DATA/users05.dbf                38         38        153        162

9 rows selected.

[v$filestat 정보]

PHYRDS NUMBER Number of physical reads done
PHYWRTS NUMBER Number of times DBWR is required to write
PHYBLKRD NUMBER Number of physical blocks read
PHYBLKWRT NUMBER Number of blocks written to disk, which may be the
same as PHYWRTS if all writes are single blocks

일반적으로 데이터베이스의 디스크 I/O 를 줄이기 위해서는 다음과 같은 설계 방법을 권한다.

시스템 테이블 스페이스를 별도로 생성한다.
임시 테이블 스페이스는 테이블과 인덱스의 테이블 스페이스와 분리하여 생성한다.
롤백 세그먼트와 온라인 리두로그 파일은 분리하여 생성한다.
온라인 리두로그 파일과 아카이브 리두로그 파일은 분리하여 생성한다.
롤백 세그먼트와 테이블과 인덱스 테이블 스페이스와 분리하여 생성한다.
테이블의 테이블 스페이스와 인덱스의 테이블 스페이스는 분리하여 생성한다.
리두로그 파일은 지속적으로 I/O 가 발생하므로 I/O가 가장 적은 디스크에 배치한다.
테이블과 인덱스 테이블 스페이스 중에 I/O가 많은 테이블 스페이스는 별도의 디스크에 배치한다.

로우 마이그레이션과 로우 체이닝

로우 마이그레이션(Row Migration)

데이터 블록에서 수정이 발생하면 수정된 데이터를 해당 데이터 블록에서 저장하지 못하고, 다른 블록의 빈 공간을 찾아 데이터를 저장하는 방식
보통 PCTFREE 가 낮게 설정된 경우 발생

로우 체이닝(Row Chaining)

로우 길이가 너무 길어서 테이블 블록 하나에 데이터가 모두 저장되지 않고, 두 개 이상의 블록에 걸쳐 하나의 로우가 저정되어 있는 형태
보통 DB_BLOCK_SIZE 가 너무 작게 지정 혹은 LOB 타입 사용시

로우 마이그레이션 확인


sys@ARTDOM>analyze table scott.emp compute statistics ;

Table analyzed.

sys@ARTDOM>select num_rows, chain_cnt from dba_tables where table_name ='EMP';

  NUM_ROWS  CHAIN_CNT
---------- ----------
        14          0

로우 체이닝 확인

사전 실행[VLDB:아래 SQL 을 실행 하여 CHAINED_ROWS 테이블을 사전 생성 해야한다. ]


sys@ARTDOM>!ls /app/oracle/product/10.1.0/rdbms/admin/utlchain.sql
/app/oracle/product/10.1.0/rdbms/admin/utlchain.sql

sys@ARTDOM>analyze table scott.emp list chained rows into CHAINED_ROWS ;

Table analyzed.

sys@ARTDOM>select count(*) from CHAINED_ROWS where table_name =upper('EMP');

  COUNT(*)
----------
         0