목차

1. Tablespace IO Stats
2. File IO Stats
3. Buffer Pool Statistics

• 3-1) buffer busy wait와 read by other sessions
• 3-2) gc buffer busy
• 3-3) free buffer waits와 write complete waits

4. Instance Recovery Stats
5. Buffer Pool Advisory
6. PGA Aggr Summary
7. PGA Aggr Target Stats
8. PGA Aggr Target Histogram
9. PGA Memory Advisory
10. Shared Pool Advisory
11. SGA Target Advisory
12. Streams Pool Advisory
13. Java Pool Advisory

1. Tablespace IO Stats

AWR DB 보고서 내용

Tablespace IO Stats               DB/Inst: XSOFT/XSOFT2  Snaps: 90505-90564
-> ordered by IOs (Reads + Writes) desc

Tablespace
------------------------------
                 Av      Av     Av                       Av     Buffer Av Buf
         Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
APPS_TS_SSD_DATA
   374,001,758   3,520    0.8     1.7    1,903,439       18 ##########    2.1
APPS_TS_EXT_DATA
   186,799,104   1,758    0.6     4.0    1,710,644       16 ##########    3.8
APPS_TS_EXT_IDX
    66,380,204     625    0.5     1.0    5,618,575       53  8,598,510    0.8
APPS_TS_TX_DATA
    70,836,490     667    1.0    10.8      241,141        2  2,469,910    1.5
APPS_TS_TX_IDX
    66,176,821     623    0.7     1.0      607,256        6  2,619,839    2.2
.......................................
.......................................
.......................................

의미

설명

• Tablespace IO Stats 단위 보고서는 테이블스페이스별 디스크 I/O 발생 수치를 보여줌.
  • DBA_HIST_FILESTATXS, DBA_HIST_TEMPSTATXS 사용
  • V$FILESTAT, V$TEMPSTAT 참조

• 체크포인트
  • 특정 테이블스페이스로 I/O가 집중될 경우, 디스크 경합을 일으키며 성능이 크게 저하되므로 점검 필요.
  • 언두 테이블스페이스에 I/O가 많을 경우, 언두 관련 통계 및 대기 이벤트 수치 점검 필요.

• 특정 테이블스페이스 I/O 성능이 낮게 나올 경우 체크사항

• 세그먼트 발생한 디스크 I/O 체크
  • AWR 'Segments by Physical Reads'에서 체크 가능
  • 아래 스크립트로 체크 가능

SELECT /*+ ORDERED USE_NL(SEG TBS) */
       SEG.OWNER,
       TBS.NAME TS_NAME,
       SEG.OBJECT_NAME,
       SEG.OBJECT_TYPE,
       SEG.PHY_R,
       ROUND(SEG.RATIO * 100, 1) RATIO
FROM   (SELECT /*+ ORDERED USE_NL(A B) */
               C.OWNER,
               B.TS#,
               C.OBJECT_NAME,
               C.OBJECT_TYPE,
               SUM(NVL(B.PHYSICAL_READS_DELTA, 0)) PHY_R,
               RATIO_TO_REPORT(SUM(NVL(B.PHYSICAL_READS_DELTA, 0))) OVER(PARTITION BY 1) RATIO
        FROM   DBA_HIST_SNAPSHOT A,
               DBA_HIST_SEG_STAT B,
               DBA_OBJECTS       C
        WHERE  A.SNAP_ID = B.SNAP_ID
        AND    B.OBJ#    = C.OBJECT_ID
        AND    TO_CHAR(A.BEGIN_INTERVAL_TIME, 'YYYY/MM/DD:HH24:MI') BETWEEN '2012/12/10:00:00' 
                                                                    AND     '2012/12/12:00:00' -- 검색 구간 지정
        -- AND B.TS#=10  특정 테이블스페이스만 보고자 할 때 이용
        GROUP  BY C.OWNER,
                  B.TS#,
                  C.OBJECT_NAME,
                  C.OBJECT_TYPE
        ORDER  BY 6 DESC) SEG,
       SYS.TS$ TBS
WHERE  SEG.TS# = TBS.TS#
AND    ROWNUM <= 20 -- 추출 행 수 지정

2. File IO Stats

AWR DB 보고서 내용

File IO Stats                     DB/Inst: XSOFT/XSOFT2  Snaps: 90505-90564
-> ordered by Tablespace, File

Tablespace               Filename
------------------------ ----------------------------------------------------
                 Av      Av     Av                       Av     Buffer Av Buf
         Reads Reads/s Rd(ms) Blks/Rd       Writes Writes/s      Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
APPS_TS_ARCHIVE          /dev/rd_arch01
         5,295       0    0.4     1.0           87        0          0    0.0
APPS_TS_ARCHIVE          /dev/rd_data2024
       106,278       1    0.4     1.1       12,415        0         14    1.4
APPS_TS_ARCHIVE          /dev/rd_data4_20024
        36,633       0    0.4     1.0        9,960        0         74    0.3
APPS_TS_ARCHIVE          /dev/rd_data4_20943
        43,578       0    0.5     1.0       10,352        0         11    2.7

의미

설명

• File IO Stats 단위 보고서는 '테이블스페이스 > 파일단위' 디스크 I/O 발생 수치를 보여줌.
  • DBA_HIST_FILESTATXS, DBA_HIST_TEMPSTATXS 사용
  • V$FILESTAT, V$TEMPSTAT 참조

• 체크포인트
  • 특정 테이블스페이스의 특정 데이터 파일에 I/O가 집중될 경우, 테이블스페이스 I/O도 낮게 나오므로 튜닝 점검 필요.
  • 하지만, 테이블스페이스의 I/O는 크지 않지만 특정 데이터파일의 I/O가 클 경우, I/O가 높은 세그먼트의 익스텐트가 특정 데이터파일에 집중적으로 할당되어 있기 때문.

• 해결방안
  • 특정 세그먼트의 편중으로 인해 데이터 파일 I/O가 높게 니타날경우, 세그먼트를 재생성하거나 MOVE 명령으로 해당 세그먼트를 재구성함으로써 추가적인 I/O 분산처리함.
    
    

• 특정 세그먼트가 특정 데이터 파일에 얼마나 많은 데이터 블록들을 생성하고 있는지 체크

SELECT A.SEGMENT_NAME,
       SUM(A.BLOCKS)
FROM   DBA_EXTENTS A,
       V$DBFILE    B
WHERE  A.FILE_ID = B.FILE#
AND    B.NAME = '/oradata1/DATA/user_1.dbf'
AND    A.SEGMENT_NAME IN ('EMPLOYEES', 'DEPARTMENT')
GROUP  BY A.SEGMENT_NAME
ORDER  BY 2 DESC
;

3. Buffer Pool Statistics

AWR DB 보고서 내용

Buffer Pool Statistics            DB/Inst: XSOFT/XSOFT2  Snaps: 90505-90564
-> Standard block size Pools  D: default,  K: keep,  R: recycle
-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

                                                            Free Writ     Buffer
     Number of Pool         Buffer     Physical    Physical Buff Comp       Busy
P      Buffers Hit%           Gets        Reads      Writes Wait Wait      Waits
--- ---------- ---- -------------- ------------ ----------- ---- ---- ----------
D      768,000   86 ############## ############  55,462,235    0    1 ##########
          -------------------------------------------------------------

의미

설명

• Buffer Pool Statistics 단위 보고서는 각 버퍼 캐시의 영역별로 별도의 정보들을 제공함
  • DBA_HIST_BUFFER_POOL_STAT 사용
  • V$BUFFER_POOL_STATISTICS 참조

• 체크포인트
  • 적중룰과 디스크 I/O에 대한 읽기와 쓰기의 비율을 확인하여 현재 설정된 각 버퍼 풀의 크기 적절성을 판단함.
  • 버퍼캐시 크기가 부족할 경우 디스크 I/O 발생 빈도가 높아져 성능에 좋지 않고, 반대로 너무 클 경우 메모리 사용 효율이 떨어지므로 적절한 설정값 필요함.

• 버퍼 캐시 적중률 체크

SELECT INSTANCE_NUMBER,
       BEGIN_TIME,
       ROUND(100 *
             (1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS))),
             2) RATIO
FROM   (SELECT INSTANCE_NUMBER,
               BEGIN_TIME,
               SUM(DECODE(STAT_NAME, 'db block gets', DELTA_VALUE)) DB_BLOCK_GETS,
               SUM(DECODE(STAT_NAME, 'consistent gets', DELTA_VALUE)) CONSISTENT_GETS,
               SUM(DECODE(STAT_NAME, 'physical reads', DELTA_VALUE)) PHYSICAL_READS
        FROM   (SELECT B.INSTANCE_NUMBER,
                       B.STAT_NAME,
                       TO_CHAR(A.BEGIN_INTERVAL_TIME, 'YYYY/MM/DD:HH24') BEGIN_TIME,
                       (LEAD(B.VALUE, 1)
                        OVER(PARTITION BY B.INSTANCE_NUMBER,
                             B.STAT_NAME ORDER BY B.SNAP_ID) - VALUE) DELTA_VALUE
                FROM   DBA_HIST_SNAPSHOT A,
                       DBA_HIST_SYSSTAT  B
                WHERE  A.SNAP_ID = B.SNAP_ID
                AND    TO_CHAR(A.BEGIN_INTERVAL_TIME, 'YYYY/MM/DD:HH24:MI') BETWEEN '2009/09/07:00:00' 
                                                                            AND     '2009/09/08:00:00'
                AND    B.STAT_NAME IN ('db block gets', 'consistent gets', 'physical reads')
                AND    VALUE IS NOT NULL)
        WHERE  DELTA_VALUE > 0
        GROUP  BY INSTANCE_NUMBER,
                  BEGIN_TIME)
;

• 버퍼 캐시 영역에 저장되어 있는 각 세그먼트의 메모리 크기 점유 체크

-- X$ 테이블 조회 권한 필요
SELECT DECODE(KPD.BP_ID, 1, 'KEEP',
                         2, 'RECYCLE',
                         3, 'DEFAULT',
                         4, '2K SUBCACHE',
                         5, '4K SUBCACHE',
                         6, '8K SUBCACHE',
                         7, '16K SUBCACHE',
                         8, '32K SUBCACHE',
                         'UNKNOWN') SUBCACHE,
       BH.OBJECT_NAME,
	   SUM(BH.BLOCKS)*8192/1024/1024 MBYTES
FROM   X$KCBWDS KDS,
       X$KCBWBPD KPD,
       (SELECT /*+ USE_HASH(X) */
               SET_DS,
               O.NAME OBJECT_NAME,
               COUNT(*) BLOCKS
        FROM   OBJ$ O,
               X$BH X
        WHERE  O.DATAOBJ# = X.OBJ
        AND    X.STATE    != 0
        AND    O.OWNER#   != 0
        GROUP  BY SET_DS,
                  O.NAME) BH
WHERE  KDS.SET_ID  >= KPD.BP_LO_SID
AND    KDS.SET_ID  <= KPD.BP_HI_SID
AND    KPD.BP_SIZE != 0
AND    KDS.ADDR    = BH.SET_DS
--AND KPD.BP_ID = 1   -- KEEP
GROUP BY DECODE(KPD.BP_ID, 1, 'KEEP',
                           2, 'RECYCLE',
                           3, 'DEFAULT',
                           4, '2K SUBCACHE',
                           5, '4K SUBCACHE',
                           6, '8K SUBCACHE',
                           7, '16K SUBCACHE',
                           8, '32K SUBCACHE',
                           'UNKNOWN'),
         BH.OBJECT_NAME
;

3-1) buffer busy wait와 read by other sessions

buffer busy wait

• buffer busy wait 대기 이벤트는 읽으려는 데이터 블록을 버퍼 캐시를 통해 사용하려 할 때, 버퍼 캐시를 사용하지 못해서 대기함을 의미함.
• 동일 블록에 저장된 데이터를 동시에 조회하고 변경하거나 조회와 변경이 동시에 이루어질 때 발생함.

read by other sessions

• 버퍼 캐시에 존재하지 않는 블록을 동시 세션들이 참조하려고 할 때, 해당 블록이 디스크로부터 버퍼 캐시로 복사되는 동안 복사를 수행하는 세션을 제외한 다른 세션들은 read by other sessions 이벤트 발생시킴.
• 이 때 버퍼 캐시로 복사하는 세션은 db file sequential read나 db file scattered read 이벤트를 대기함.

해결방안

• 핫 블록 경합 해결
• 자동 세그먼트 공간 관리(ASSM) 사용

buffer busy wait와 read by other sessions 대기 이벤트 발생 시 파라미터 값 의미

3-2) gc buffer busy

• RAC 환경에서 로컬 인스턴스의 세션이 참조하고자 하는 블록이 다른 인스턴스의 다른 세션에 의해서 사용 중일 때 대기하는 이벤트.
• RAC 환경에서 인스턴스간 블록 요청 시 발생하는 것을 제외하면 buffer busy wait와 read by other sessions과 동일한 원인과 해결방안도 동일함.

3-3) free buffer waits와 write complete waits

free buffer waits

• 버퍼 캐시에 사용 가능한 프리 버퍼가 없을 때 발생함.
• 프리 버퍼가 없을 때 DBWR 프로세서가 더티 버퍼들을 디스크에 내리고, 프리 버퍼를 확보할 때까지 대기 이벤트 발생함.

write complete waits

• 버퍼 캐시가 DBWR에 의해 디스크로 내려쓰여지고 있을 때 발생함.

해결방안

• 두 개의 이벤트가 발생하는 주 원인은 DBWR의 성능이 낮을 경우에 발생할 확률 높음.
• 해결방안은 아래와 같음.
  • DBWR의 수를 증가.
  • 디스크 성능 개선

두 개의 대기 이벤트 발생 시 파라미터 값 의미

4. Instance Recovery Stats

AWR DB 보고서 내용

Instance Recovery Stats            DB/Inst: XSOFT/XSOFT2  Snaps: 90505-90564
-> B: Begin snapshot,  E: End snapshot

  Targt  Estd                                  Log File Log Ckpt     Log Ckpt
  MTTR   MTTR   Recovery  Actual    Target       Size    Timeout     Interval
   (s)    (s)   Estd IOs Redo Blks Redo Blks  Redo Blks Redo Blks   Redo Blks
- ----- ----- ---------- --------- --------- ---------- --------- ------------
B     0    42       4540     95324    100000   15092100    241217       100000
E     0    42       4754     67364    100000   15092100   2653129       100000
          -------------------------------------------------------------

의미

설명

• Instance Recovery Stats 보고서는 인스턴스 복구 관련 정보를 보여줌.
  • DBA_HIST_INSTANCE_RECOVERY 사용
  • V$INSTANCE_RECOVERY 참조

• Target MTTR(s) 수치는 보통 FAST_START_MTTR_TARGET 설정 값과 일치함.
  • Target MTTR(s) 값이 FAST_START_MTTR_TARGET 값보다 작다면, 체크포인트 발생 주기 설정이 너무 높게 잡혀 있는 것이므로, FAST_START_MTTR_TARGET 값을 Target MTTR(s) 보다 낮춰 주어야 함.
  • Target MTTR(s) 값이 FAST_START_MTTR_TARGET 값보다 크다면, 성능에 영향을 미치지 않는 수준보다 체크포인트가 적게 발생하는 것이므로 빠른 인스턴스 복구를 위해 Target MTTR(s) 수치 이하로 늘려야 함.

• FAST_START_MTTR_TARGET 설정 값 변경에 따른 디스크 I/O 변화 체크

SELECT -- FAST_START_MTTR_TARGET 설정 수치
       MTTR_TARGET_FOR_ESTIMATE TARGET,
       -- FAST_START_MTTR_TARGET 설정 시 증가되거나 감소되는 버퍼 캐시 I/O 양
       ESTD_CACHE_WRITE_FACTOR  CACHE_FACT,
       -- FAST_START_MTTR_TARGET 설정 시 증가되거나 감소되는 전체 I/O 양
       -- APPEND나 PAEALLEL 힌트의 사용 등으로 발생하는 DIRECT I/O 포함
       ESTD_TOTAL_WRITE_FACTOR  TOTAL_FACT
FROM   DBA_HIST_MTTR_TARGET_ADVICE
WHERE  DBID = (SELECT DBID
               FROM   V$DATABASE)
AND    INSTANCE_NUMBER = :INST_ID -- 인스턴스 번호
AND    SNAP_ID         = :SNAP_ID -- 스냅샷 ID
ORDER  BY 1

• MTTR 권고자 사용 위한 설정사항
  • STATISTICS_LEVEL 파라미터를 TYPICAL이나 ALL로 설정
  • FAST_START_MTTR_TARGET 파라미터를 0 이상으로 설정

5. Buffer Pool Advisory

AWR DB 보고서 내용

-- 1. AWR에서는 데이터 없음(책 참조)
                  No data exists for this section of the report.
          -------------------------------------------------------------
;

-- 2. 추출쿼리
SELECT SUBSTR(NAME, 1, 1),
       SIZE_FOR_ESTIMATE,
       SIZE_FACTOR,
       BUFFERS_FOR_ESTIMATE,
       PHYSICAL_READS / BASE_PHYSICAL_READS,
       PHYSICAL_READS * (ACTUAL_PHYSICAL_READS / BASE_PHYSICAL_READS)
FROM   DBA_HIST_DB_CACHE_ADVICE
WHERE  INSTANCE_NUMBER = 8
AND    SNAP_ID         >= 92167
AND    SNAP_ID         <= 92215
;

의미

설명

• Buffer Pool Advisory 보고서는 버퍼 캐시 크기 변경에 따른 영향도를 파악할 수 있으며, 버퍼 캐시 크기 설정이 도움을 줌.
  • DBA_HIST_DB_CACHE_ADVICE 사용
  • V$DB_CACHE_ADVICE 참조

• 체크포인트
  • Size Factor 값이 1.0인 Size for Est(M) 수치 4,960M이 현재 버퍼 캐시와 가장 유사한 크기.
  • 만약, 버퍼 캐시 크기를 현재의 10% 수준인 496MB로 설정하면 디스크 I/O는 27,900,510회로 현재 수준의 12.8배 증가함.
  • 버퍼 캐시를 줄인다면, 예상되는 디스크 I/O가 30% 증가한 992M에서 0% 증가한 가장 큰 크기인 2,976M 까지 줄이는 것이 대안.
  • 버커 캐시를 늘린다면, 예상되는 디스크 I/O가 30% 감소한 9,920M(2배)가 가장 좋으며, 그 이전 크기는 예상 디스크 I/O에 변동이 없으므로 불필요함.

• 동적 뷰에 저장된 버퍼 캐시 권고자 통계치 체크

COL size_for_estimate  FORMAT 999,999,999 heading 'Size for|Est (M)' 
COL buffers_for_estimate  FORMAT 999,999,999 heading 'Buffers for|Estimate'
COL estd_physical_read_factor FORMAT 999.90 heading ' Est|Phys|Read|Factor'
COL estd_physical_reads  FORMAT 999,999,999 heading 'Estimated|Physical Reads'

SELECT SUBSTR(NAME, 1, 1) P,
       SIZE_FOR_ESTIMATE,
       BUFFERS_FOR_ESTIMATE,
       ESTD_PHYSICAL_READ_FACTOR,
       ESTD_PHYSICAL_READS
FROM   V$DB_CACHE_ADVICE
WHERE  ADVICE_STATUS = 'ON'
ORDER  BY 1,
          2
/

6. PGA Aggr Summary

AWR DB 보고서 내용

PGA Cache Hit %   W/A MB Processed  Extra W/A MB Read/Written
--------------- ------------------ --------------------------
           98.8          1,129,369                     14,264
          -------------------------------------------------------------

의미

설명

• PGA Aggr Summary 보고서는 스냅 구간 중 PGA 사용 효율을 보여줌
  • DBA_HIST_PGASTAT 사용
  • V$PGASTAT 참조

• 체크포인트
  • 'PGA Cache Hit %' 값은 디스크 I/O가 아닌 메모리에서 Hit된 블록을 읽은 비율이므로 100%에 근접할수록 좋음.

7. PGA Aggr Target Stats

AWR DB 보고서 내용

    PGA Aggr   Auto PGA   PGA Mem    W/A PGA     W/A    W/A    W/A Global Mem
   Target(M)  Target(M)  Alloc(M)    Used(M)     Mem    Mem    Mem   Bound(K)
- ---------- ---------- ---------- ---------- ------ ------ ------ ----------
B     10,240      8,022    2,112.1       46.4    2.2  100.0     .0  1,048,570
E     10,240      6,978    5,587.7    1,649.7   29.5  100.0     .0  1,048,570
          -------------------------------------------------------------

의미

설명

• PGA Aggr Target Stats 보고서는 스냅 구간 중 PGA 사용 효율을 보여줌
  • DBA_HIST_PGASTAT 사용

• 체크포인트
  • 'Auto PGA Target(M)' 컬럼의 값은 오라클이 판단하는 PGA의 필요한 SQL 작업 공간 크기이므로 PGA_AGGREGATE_TARGET 초기화 파라미터 설정 값인 PGA Aggr Target(M) 컬럼의 값과 다를 수 있음.
  • 이 두 값의 차이는 PGA의 크기를 결정하는데 판단의 기준이 되며, 이 값이 파라미터 설정 값 보다 큰 경우 PGA 크기 확장을 고려해야 하고, 작을 경우 PGA 크기 축소를 고려해야 함.

8. PGA Aggr Target Histogram

AWR DB 보고서 내용

  Low     High
Optimal Optimal    Total Execs  Optimal Execs 1-Pass Execs M-Pass Execs
------- ------- -------------- -------------- ------------ ------------
     2K      4K     41,286,726     41,286,726            0            0
    64K    128K        203,589        203,589            0            0
   128K    256K          7,484          7,484            0            0
   256K    512K         83,353         83,353            0            0
   512K   1024K        308,907        308,907            0            0
     1M      2M        394,502        394,502            0            0
     2M      4M          3,526          3,256          270            0
     4M      8M          3,949          3,881           68            0
     8M     16M          1,355          1,330           25            0
    16M     32M            729            683           46            0
    32M     64M          2,640          2,520          120            0
    64M    128M            344            344            0            0
   128M    256M            166            158            8            0
   256M    512M             13             11            2            0
   512M   1024M              4              0            4            0
          -------------------------------------------------------------

의미

설명

• PGA Aggr Target Histogram 보고서는 스냅 구간 중 SQL 작업 영역의 사용 현황을 보여줌
  • DBA_HIST_SQL_WORKAREA_HSTGRM 사용
  • V$SQL_WORKAREA_HISTOGRAM 참조

• 체크포인트
  • 2K ~ 4K 구간은 전체 수행 횟수 중 100%로 OPTIMAL_EXECUTIONS 영역에서 처리되었으며, 2M ~ 4M 구간은 전체 수행 횟수 중 270회가 ONEPASS_EXECUTIONS 영역에서 처리된 것을 알 수 있음
  • 이처럼, 대부분의 작업 영역이 OPTIMAL_EXECUTIONS에서 처리되며 4K 이하이므로 OLTP 시스템임을 짐작할 수 있음.

9. PGA Memory Advisory

AWR DB 보고서 내용

                                       Estd Extra    Estd PGA   Estd PGA
PGA Target    Size           W/A MB   W/A MB Read/      Cache  Overalloc
  Est (MB)   Factr        Processed Written to Disk     Hit %      Count
---------- ------- ---------------- ---------------- -------- ----------
     1,280     0.1     17,602,339.5        388,001.2     98.0      5,588
     2,560     0.3     17,602,339.5        155,786.3     99.0        668
     5,120     0.5     17,602,339.5         60,040.9    100.0          0
     7,680     0.8     17,602,339.5         60,040.9    100.0          0
    10,240     1.0     17,602,339.5         24,691.3    100.0          0
    12,288     1.2     17,602,339.5         19,821.0    100.0          0
    14,336     1.4     17,602,339.5         19,821.0    100.0          0
    16,384     1.6     17,602,339.5         19,821.0    100.0          0
    18,432     1.8     17,602,339.5         19,821.0    100.0          0
    20,480     2.0     17,602,339.5         19,821.0    100.0          0
    30,720     3.0     17,602,339.5         19,821.0    100.0          0
    40,960     4.0     17,602,339.5         19,821.0    100.0          0
    61,440     6.0     17,602,339.5         19,821.0    100.0          0
    81,920     8.0     17,602,339.5         19,821.0    100.0          0
          -------------------------------------------------------------

의미

설명

• PGA Memory Advisory 보고서는 PGA 크기 변경 시 예상되는 사용 효율을 나타냄
  • DBA_HIST_PGA_TARGET_ADVICE 사용
  • V$PGA_TARGET_ADVICE 참조

• 체크포인트
  • 'Estd PGA Overalloc Count' 컬럼은 예상 PGA 크기 적용 시 발생하는 추가 메모리 할당 횟수이며, 이 값이 0 이상이 되지 않도록 해야함.
  • 'PGA Target Est' 값을 20% 늘릴 경우 'Estd Extra W/A MB Read/Written to Disk' 값이 줄어드므로, DISK I/O 발생량을 줄어들이는 효과는 있으니 효과는 미비함.
  • 하지만 70%을 줄일 경우 급격하게 DISK I/O가 증가하므로 줄일 경우 최대 50%까지만 줄여야 함.

10. Shared Pool Advisory

AWR DB 보고서 내용

-- 1. AWR에서는 데이터 없음(책 참조)
                  No data exists for this section of the report.
          -------------------------------------------------------------
;

-- 2. 추출쿼리
SELECT SHARED_POOL_SIZE_FOR_ESTIMATE,
       SHARED_POOL_SIZE_FACT,
       ESTD_LC_SIZE,
       ESTD_LC_MEMORY_OBJECTS,
       ESTD_LC_TIME_SAVE,
       ESTD_LC_TIME_SAVED_FACTOR,
       ESTD_LC_LOAD_TIME,
       ESTD_LC_LOAD_TIME_FACTOR,
       ESTD_LC_MEMORY_OBJECT_HITS 
FROM   DBA_HIST_SHARED_POOL_ADVICE
WHERE  INSTANCE_NUMBER = 8
AND    SNAP_ID         >= 92167
AND    SNAP_ID         <= 92215
;

의미

설명

• Shared Pool Advisory 보고서는 공유 풀 크기에 따른 영향도를 보여줌
  • DBA_HIST_SHARED_POOL_ADVICE 사용
  • V$SHARED_POOL_ADVICE 참조

• 체크포인트
  • 'Shared Pool Size(M)' 값이 608MB보다 증가할 경우 'Est LC Time Saved Factr' 비율은 0.9%에서 1.0으로 증가하여 효과는 있지만, 그 이상은 효과 없음.
  • 또한 288MB로 줄일 경우 30% 가량 효율이 낮아지므로, 하드파싱 확률이 높아짐.
  • 이를 통해 확인 가능한 내용은, 공유 풀 크기가 크다고 해서 반드시 효율이 높아지는 것은 아니며, 이 항목을 체크하려면 Literal SQL을 최대한 바인드 변수로 변경 후 체크해야 함.

11. SGA Target Advisory

AWR DB 보고서 내용

-- 1. AWR에서는 데이터 없음(책 참조)
                  No data exists for this section of the report.
          -------------------------------------------------------------
;

-- 2. 추출쿼리
SELECT SGA_SIZE,
       SGA_SIZE_FACTOR,
       ESTD_DB_TIME,
       ESTD_PHYSICAL_READS
FROM   DBA_HIST_SGA_TARGET_ADVICE
WHERE  INSTANCE_NUMBER = 8
AND    SNAP_ID         >= 92167
AND    SNAP_ID         <= 92215
;

의미

설명

• SGA Target Advisory 보고서는 SGA 크기에 따른 영향도를 보여줌
  • DBA_HIST_SGA_TARGET_ADVICE 사용
  • V$SGA_TARGET_ADVICE 참조

• 체크포인트
  • SGA_TARGET 설정 값은 160MB이며, 이 크기를 200MB 이상으로 증가시키면 'Est DB Time(s)' 값이 거의 변하지 않으므로 30%까지만 증가시키는 것이 가장 효율적임.

12. Streams Pool Advisory

AWR DB 보고서 내용

  Size for      Size   Est Spill   Est Spill Est Unspill Est Unspill
  Est (MB)    Factor       Count    Time (s)       Count    Time (s)
---------- --------- ----------- ----------- ----------- -----------
        96       0.2           0           0           0           0
       160       0.3           0           0           0           0
       224       0.4           0           0           0           0
       288       0.5           0           0           0           0
       352       0.6           0           0           0           0
       416       0.7           0           0           0           0
       480       0.8           0           0           0           0
       544       0.9           0           0           0           0
       608       1.0           0           0           0           0
       672       1.1           0           0           0           0
       736       1.2           0           0           0           0
       800       1.3           0           0           0           0
       864       1.4           0           0           0           0
       928       1.5           0           0           0           0
       992       1.6           0           0           0           0
     1,056       1.7           0           0           0           0
     1,120       1.8           0           0           0           0
     1,184       1.9           0           0           0           0
     1,248       2.1           0           0           0           0
     1,312       2.2           0           0           0           0
          -------------------------------------------------------------

설명

• Streams Pool Advisory 보고서는 스트림즈 풀의 크기에 따른 영향도를 보여줌.
  • 스트림즈는 오라클에서 제공하는 데이터 복제 기능 중 하나이며, 9i부터 사용 가능.
  • 스트림즈는 별도의 스트림즈 관련 프로세스가 오라클의 로그 마이너 기능을 사용하여 원본 DB의 리두 로그 데이터 중 변경된 데이터만 수집하여 복제 대상 DB로 전송해서 적용하기 때문에 데이터 복제에 따른 부하를 최소화할 수 있음.

13. Java Pool Advisory

AWR DB 보고서 내용

-- 1. AWR에서는 데이터 없음(책 참조)
                  No data exists for this section of the report.
          -------------------------------------------------------------
;

-- 2. 추출쿼리
SELECT JAVA_POOL_SIZE_FOR_ESTIMATE,
       JAVA_POOL_SIZE_FACTOR,
       ESTD_LC_SIZE,
       ESTD_LC_MEMORY_OBJECTS,
       ESTD_LC_TIME_SAVED,
       ESTD_LC_TIME_SAVED_FACTOR,
       ESTD_LC_LOAD_TIME,
       ESTD_LC_LOAD_TIME_FACTOR,
       ESTD_LC_MEMORY_OBJECT_HITS
FROM   DBA_HIST_JAVA_POOL_ADVICE
WHERE  INSTANCE_NUMBER = 8
AND    SNAP_ID         >= 92167
AND    SNAP_ID         <= 92215
;

의미

설명

• Java Pool Advisory 보고서는 자바 풀 사용 통계를 보여줌.
  • DBA_HIST_JAVA_POOL_ADVICE 사용
  • V$JAVA_POOL_ADVICE 참조

• 체크포인트
  • 'Java Pool Size(M)' 값이 일정 값 이상 증가할 경우 'Est LC Time Sved Factr' 값이 더 이상 증가하지 않으며, 효과가 없음을 의미함.
  • 그러므로, 이 보고서에서 사이즈는 48MB가 아닌 24MB로 유지하는 것이 가장 효율적임을 알 수 있음.

문서에 대하여

• 최초작성자 : ~xsoft
• 최초작성일 : 2013년 01월 05일
• 이 문서는 오라클클럽 오라클 성능 트러블슈팅 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 '오라클 AWR을 이용한 고성능 데이터베이스 튜닝'을 참고하였습니다.*