8._Statspack_AWR

조회 수 9323 추천 수 0 2013.08.21 12:33:51
시와처 *.124.106.147
  • Statspack와 AWR 이란

- Dynamic Performance view를 주기적으로 수집해 성능관리를 표준화된 방식으로 지원하려고 제공되는 오라클 기본 패키지

- Ration 기반 성능 진단과 wait event 기반 성능 진단 방법론을 모두 포함

- 오라클 데이터베이스 전반의 건강상태를 체크하고 병목원인과 튜닝 대상을 식별해 내는데 사용

- Dynamic Performance view를 주기적으로 특정 Repository에 저장


 Dynamic Performance view

 내용

비고

 v$segstat

 Segment Level Statistics

 DBA_HIST_SEG_STAT

 v$undostat

 작업을 얼마나 잘 수행 하고 있는지에 대한 statistical histogram

(Undo Space consumption,transaction concurrency,length of queries executed)

 

 v$latch

 parent, child latch에 대한 통계정보의 총계

 

 v$latch_children

 child latch에 대한 통계 정보

 

 v$sgastat

 SGA 에 대한 상세한 정보

 

 v$pgastat

 PGA 사용에 대한 통계 정보 제공, automatic PGA memory manager에게 통계정보 제공

 

 v$sysstat

 system statistics

 

 v$system_event

 인스턴수 구동 이후 누적된 wait event의 정보

 

 v$waitstat

 block 통계정보

 

 v$sql

 shared SQL area 통계 (with the GROUP BY clause)

 

 v$sql_plan

 라이브러리 캐쉬에 로드된 커서별 실행 계획에 대한 정보

 

 v$sqlstats

 

 10g 이후

 v$active_session_historary

 

 10g 이후

 v$ossstat

 

 10g 이후


- Statspack : SQL을 이용한 딕셔너리 조회 방식, 수집에 따른 부하로 수동으로 statspack.snap 명령 처리

- AWR : DMA(Direct Memory Access) 방식 - SGA를 직접 Access , 부하가 적고, 빠름, 자동으로 수집


  • Statspack / AWR 기본 사용법

- PERFSTAT.stats$ * : Statspack 수집 된 view

- SYS.dba_hist_* : AWR 수집된 view

- 보고서 출력 방법

  Statspack : @?/rdbms/admin/spreport

  AWR : @?/rdbms/admin/awrrpt


- statspack 실습

sql> exec statspack.snap
sql> exec statspack.snap
SQL>@?/rdbms/admin/spreport
Instance     DB Name        Snap Id   Snap Started    Level Comment
------------ ------------ --------- ----------------- ----- --------------------
TESTDB       TESTDB            1201 06 Feb 2009 09:21     5
                               1202 06 Feb 2009 09:36     5
                               1203 06 Feb 2009 09:51     5
                               1211 06 Feb 2009 10:06     5



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1201
Begin Snapshot Id specified: 1201

Enter value for end_snap: 1202
End   Snapshot Id specified: 1202



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1201_1202.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: aaa   

Using the report name aaa
.
.
.
          -------------------------------------------------------------

End of Report ( aaa.lst )


- AWR 실습

SQL> @?/rdbms/admin/awrrpt

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 2682586924 TESTDB              1 TESTDB1


Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
  2682586924        2 TESTDB       TESTDB2      abasdb02
* 2682586924        1 TESTDB       TESTDB1      abasdb01

Using 2682586924 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
TESTDB1      TESTDB           41148 01 Apr 2012 00:00      1
                              41149 01 Apr 2012 01:00      1
                              41150 01 Apr 2012 02:00      1
                              41151 01 Apr 2012 03:00      1
                              41152 01 Apr 2012 04:00      1
                              41153 01 Apr 2012 05:00      1
                              41154 01 Apr 2012 06:00      1
                              41155 01 Apr 2012 07:00      1
                              41156 01 Apr 2012 08:00      1
                              41157 01 Apr 2012 09:00      1
                              41158 01 Apr 2012 10:00      1
                              41159 01 Apr 2012 11:00      1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 41158
Begin Snapshot Id specified: 41158

Enter value for end_snap: 41159
End   Snapshot Id specified: 41159



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_41158_41159.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:   

Report written to awrrpt_1_41158_41159.html
SQL>



- 구간벌 sql 수행횟수를 추출하는 query

select to_char(min(s.begin_interval_time), 'hh24:mi') begin
      ,to_char(min(s.end_interval_time), 'hh24:mi') end
      ,sum(b.value-a.value) "execute count"
  from dba_hist_sysstat a
      ,dba_hist_sysstat b
      ,dba_hist_snapshot s
 where s.instance_number = &instance_number
   and s.snap_id between &begin_snap and &end_snap
   and a.stat_name = 'execute count'
   and b.stat_id = a.stat_id
   and b.snap_id = s.snap_id
   and a.snap_id = b.snap_id - 1
   and a.instance_number = s.instance_number
   and b.instance_number = s.instance_number
 group by s.snap_id
 order by s.snap_id
;


&instance_number: 1
&begin_snap: 13057
&end_snap: 13058
 
BEGIN END   execute count
----- ----- -------------
20:00 21:00       5708285
21:00 22:00       5691375
 
Executed in 3.557 seconds


  • Statspack / AWR 리포트 분석

- Load Profile : DB 상태에 대한 종합 의견서

  Per Second => 초당 부하량, Per Transaction => 트랜잭션 당 부하량 (Commit, rollback 수행 횟수)

  v$sysstat 에서 조회 가능

'redo size', 'session logical reads', 'db block changs', 'physical reads', 'physical writes', 'user calls'

, 'parse count (total)', 'parse count (hard)', 'sorts (memory)' + 'sorts (disk)', 'logons cumulative', 'execute count'

,'user calls' + 'user rollbacks'

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:              6,103.03              3,152.53
              Logical reads:             58,515.43             30,226.23
              Block changes:                 29.62                 15.30
             Physical reads:              1,184.74                611.98
            Physical writes:                  2.74                  1.42
                 User calls:                100.92                 52.13
                     Parses:                 31.08                 16.05
                Hard parses:                  1.01                  0.52
                      Sorts:                 18.32                  9.46
                     Logons:                  0.42                  0.22
                   Executes:                 38.35                 19.81
               Transactions:                  1.94
 



  % Blocks changed per Read:    0.05    Recursive Call %:    36.61
 Rollback per transaction %:    4.86       Rows per Sort:   605.54

. % Block changed per Read : 읽은 블록 중 갱신이 발생하는 비중
select round(100*chng/gets,2) "% Block changed per Read"
  from (select value chng from v$sysstat where name = 'db block changes')
      ,(select value gets from v$sysstat where name = 'session logical reads')
;
 % Block changed per Read
------------------------
                    0.42
 Executed in 0.032 seconds

. Rollback per transaction % : 최종적으로 커밋되지 못하고 롤백된 트랜잭션 비중
select round(100*urol/(ucom+urol),2) "% Rollback per transaction"
  from (select value ucom from v$sysstat where name = 'user calls')
      ,(select value urol from v$sysstat where name = 'user rollbacks')
;
 % Rollback per transaction
--------------------------
                      0.07
 Executed in 0.047 seconds

. Recursive Call % : 전체 call 중 Recursive Calll이 차지하는 비중
 사용자정의 함수/프로시저를 많이 사용하면 늘어남, 하드 파싱에 의해서도 영향있음
select round(100*recr/(recr+ucal),2) "% Recursive Call"
  from (select value recr from v$sysstat where name = 'recursive calls')
      ,(select value ucal from v$sysstat where name = 'user calls')
;
 % Recursive Call
----------------
           53.46
 Executed in 0.047 seconds

. Rows per Sort : 소트 수행 시 평균 몇 건씩 처리 했는지
select decode(srtm+srtd,0,to_number(null),round(srtr/(srtm+srtd),2)) "% Rows per Sort "
  from (select value srtm from v$sysstat where name = 'sorts (memory)')
      ,(select value srtd from v$sysstat where name = 'sorts (disk)')
      ,(select value srtr from v$sysstat where name = 'sorts (rows)')
;
 % Rows per Sort
----------------
          342.31
 Executed in 0.062 seconds


- Insstance Efficiency Percentages : 인스턴스 효율성 


 Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.98       Redo NoWait %:  100.00
            Buffer  Hit   %:   97.98    In-memory Sort %:  100.00
            Library Hit   %:   95.33        Soft Parse %:   96.76
         Execute to Parse %:   18.97         Latch Hit %:   99.99
Parse CPU to Parse Elapsd %:    9.14     % Non-Parse CPU:   98.43


. Ratio 기반 분석 항목들

. Execute to Parse 를 제외 하면 모두 100%에 가까운 수치를 보여야 한다.


- Shared Pool Statistics : Shared Pool 사용 통계

  Shared Pool Statistics        Begin    End
                              ------  ------
             Memory Usage %:   43.45   48.85
    % SQL with executions>1:   81.91   83.80
  % Memory for SQL w/exec>1:   65.84   74.27


. 시작 시점과 종료시점의 메모리 상황



- Top 5 Timed Events : AWR 구간동안 누적 대기시간이 가정 컷던 대기 이벤트 5개

Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
gc cr multi block request         2,919,562       5,036      2   49.4    Cluster
cursor: pin S wait on X             359,777       3,512     10   34.5 Concurrenc
CPU time                                          1,646          16.2
gc current block 2-way               77,336       1,516     20   14.9    Cluster
gc buffer busy                       32,104       1,015     32   10.0    Cluster
 

. Total Call Time = Service Time + Queue Time = CPU Time + Wait Time
. CPU Time이 Top 1에 위치한다면 DB 건강상태는 양호하다는 청신호
. CPU Time이 아래쪽에 있다면 적신호를 의미한다.
. 항상 CPU Time을 제외한 4개의 대기 이벤트가 발생한다.
. latch, lock 관련 wait event가 상위인경우 적신호 이지만 latch 는 cpu 경합이 주 원인이므로 cpu 경합이 높지 않다면 단지 발생율이 높다는 의미
. transaction 처리 위주의 시스템인경우 log file sync 가 Top 5에 들 확율이 높다
. I/O 관련 wait event 가 높은 경우
  OLTP, DW, OLAP 에 따라  db file sequential read, db file scattered read 의 순서가 바뀜
  CPU Time보다 높은 점유율을 갖는 경우 OS CPU 사용율이 높다면 I/O 튜닝이 필요한 상태