재현 불가능한 문제의 분석
- 개요
- 재현 불가능하며 문제 발생 당시에는 관찰하지 못했던 성능 문제를 분석하는 방법
- SQL 트레이스나 동적 성능 뷰를 통해 제공되는 정보를 활용할 수 없는 문제를 분석
- 성능 문제가 발생한 기간의 성능 통계가 저장된 리포지터리를 이용하여 분석
- 리포지터리
| 1 | AWR(Automatic Workload Repository |
|---|
| 2 | Statspack |
|---|
- 기본 개념
- 일정 간격으로 다수의 동적 성뉴 뷰의 내용을 여러 테이블에 기록, 이를 스냅샷이라고 하며 스냅샷ID로 식별
- 오라클에서 제공하는 스크립트나 도구(EM, SQL Developer)를 이용하여 두 스냅샷 기간 동안 리포지터리에 저장된 통계 변화를 확인
- 스냅샷은 보존기간이 지나면 삭제되나, 특정 기간 동의 스냅샷을 베이스라인으로 설정 시 삭제 대상에서 제외
- 스냅샷은 한 시간 이내로 설정(20분 또는 30분)한 것을 권장
- 너무 오래 기간에 걸쳐 계산된 비율이나 평균은 잘못된 평가로 이어질 수 있다.
- 일부 동적 성능 뷰에서 제공되는 정보는 휘발성이 매우 높으므로 스냅샷을 찍는 시점에 유용한 정보가 없을 수도 있다.
- AWR과 Statspack 차이점
| AWR | Statspack |
|---|
| 데이터베이스 엔진과 긴밀하게 통합되어 있으므로 자동으로 설치되고 관리된다. | DBA가 직접 설치해서 수동으로 관리해야 한다. |
| 시스템 레벨 및 SQL 레벨의 정보뿐만 아니라 ASH에 기반을 둔 세션 레벨의 정보도 저장한다. | 시스템 레벨 및 SQL 레벨의 정보만 저장한다. |
| 엔터프라이즈 관리자를 통해 저장된 정보를 활용할 수 있다. | 엔터프라이즈 관리자와 통합되어 있지 않다. |
| 성능 문제를 자동으로 진단하기 위해 어드바이저가 저장된 정보를 이용할 수 있다. | 어드바이저가 저장된 정보를 이용하지 않는다. |
| 오라클 진단 팩 옵션을 필요로 하므로 엔터프라이즈 에디션이어야한다. | 모든 에디션에서 무료로 사용 가능하다. |
| 읽기 전동 모드로 오픈된 스탠바이 데이터베이스에서는 사용할 수 없다. | 11.1부터 읽기 전용 모드로 오픈된 스탠바이 데이터베이스에서 사용할 수 있다. |
- AWR: Automatic Workload Repository
- 구성하기
- 자동으로 설치 및 구성
- (주의)statistics_level이 basic일 경우 자동으로 스냅샷을 찍지 않는다.
- AWR 설정
| 구분 | 옵션명 | 내용 | 최소값 | 최대값 | 기본값 |
| 1 | 스냅샷 간격(snap interval) | 두 스냅샷 사이의 간격을 분 단위로 설정 | 10분 | 100년 | 1시간 |
| 2 | 보존기간(retention) | 스냅샷을 보존하는 기간, 분 단위 설정 | 1일 | 100년 | 8일(11.1) |
| 3 | Top SQL(Top n SQL) | 자원을 가장 많이 사용한 SQL 저장 개수 | 30 | 50,000 또는 MAXIMUM | DEFAULT(30 또는 100) |
- SQL ID를 coloerd로 마킹 시 스냅샷을 찍을 때마다 해당 SQL을 반드시 수집(dbms_workload_repository 패키지의 add_colored_sql)
- AWR 설정 확인 및 변경
SQL> SELECT SNAP_INTERVAL, RETENTION, TOPNSQL
FROM dba_hist_wr_control;
SNAP_INTERVAL RETENTION TOPNSQL
------------- --------- ----------
+00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
EXEC dbms_workload_repository.modify_snapshot_settings(
interval => 20
,retention => 35*60*24
,topnsql => 'DEFAULT'
);
--AWR 용량 확인
SQL> SELECT OCCUPANT_DESC, SPACE_USAGE_KBYTES
FROM v$sysaux_occupants
WHERE occupant_name = 'SM/AWR';
OCCUPANT_DESC SPACE_USAGE_KBYTES
---------------------------------------------------------------- ------------------
Server Manageability - Automatic Workload Repository 1009216
- 스냅샷 찍기
- 자동 스냅샷뿐만 아니라 수동으로 스냅샷 가능
- dbms_workload_repository 패키지의 create_snapshot(프로시저, 함수 둘 다 제공)
SQL> SELECT dbms_workload_repository.create_snapshot(flush_level => 'ALL') AS snap_id
FROM dual;
SNAP_ID
---------
57592
--flush_level: 1)TYPICAL: 30개 2)ALL: 100개 SQL 저장
SQL> SELECT begin_interval_time
,end_interval_time
,decode(snap_level, 1 , 'typical', 2, 'all', snap_level) as snap_level
FROM dba_hist_snapshot
WHERE SNAP_ID = 57592;
BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL
-------------------- -------------------- ----------------------------------------
2017/11/13 13:00:01 2017/11/13 13:45:01 all
| 고정 베이스라인 | 고정값인 시작 스냅샷 ID와 종료 스냅샷 ID로 구분되는 연속된 스냅샷 집합
필요한 만큼 생성 가능 |
| 이동 윈도우 베이스라인 | 가장 최근의 스냅샷으로 끝나며 일정 기간을 다루는 연속된 스냅샷의 집합
모든 데이터베이스는 하나의 이동 윈도우를 가지며, 어댑티브 임계치(adaptive thresholds) 기능을 위해 데이터베이스 엔진에 의해 사용 |
SQL> EXEC dbms_workload_repository.create_baseline(
start_snap_id => 57563
,end_snap_id => 57572
,baseline_name => 'OFFICE_HOURS'
,expiration => 30
);
SQL> SELECT start_snap_id, start_snap_time, end_snap_id, end_snap_time, expiration
FROM dba_hist_baseline
WHERE baseline_name = 'OFFICE_HOURS'
AND baseline_type = 'STATIC';
START_SNAP_ID START_SNAP_TIME END_SNAP_ID END_SNAP_TIME EXPIRATION
------------- -------------------- ----------- -------------------- ----------
57563 2017/11/12 09:00:22 57572 2017/11/12 18:00:34 30
SQL> SELECT METRIC_NAME, METRIC_UNIT, MINIMUM, AVERAGE, MAXIMUM
FROM table(dbms_workload_repository.select_baseline_metric('OFFICE_HOURS'))
ORDER BY metric_name;
METRIC_NAME METRIC_UNIT MINIMUM AVERAGE MAXIMUM
---------------------------------------------------------------- ---------------------------------------------------------------- --------- --------- ---------
Active Parallel Sessions Sessions 0 0 0
Active Serial Sessions Sessions 0 1.0e+000 4
Average Active Sessions Active Sessions 0 4.9e-002 1.5e+000
Average Synchronous Single-Block Read Latency Milliseconds 0 5.0e-004 1.3e-001
Background CPU Usage Per Sec CentiSeconds Per Second 0 3.3e-001 5.7e+001
Background Checkpoints Per Sec Check Points Per Second 0 2.8e-005 1.7e-002
Background Time Per Sec Active Sessions 0 1.4e-002 1.2e+000
Branch Node Splits Per Sec Splits Per Second 0 8.3e-005 3.3e-002
Branch Node Splits Per Txn Splits Per Txn 0 3.0e-005 1.1e-002
Buffer Cache Hit Ratio % (LogRead - PhyRead)/LogRead 0 9.6e+001 9.9e+001
CPU Usage Per Sec CentiSeconds Per Second 0 3.1e+000 4.9e+001
CPU Usage Per Txn CentiSeconds Per Txn 0 1.9e+001 1.1e+002
CR Blocks Created Per Sec Blocks Per Second 0 8.5e-003 1.5e-001
CR Blocks Created Per Txn Blocks Per Txn 0 3.2e-002 8.6e-001
CR Undo Records Applied Per Sec Undo Records Per Second 0 1.7e-002 1.7e-001
CR Undo Records Applied Per Txn Records Per Txn 0 6.3e-002 8.6e-001
Captured user calls calls 0 0 0
Cell Physical IO Interconnect Bytes bytes 0 1.1e+008 2.1e+009
Consistent Read Changes Per Sec Blocks Per Second 0 5.3e-001 1.1e+002
Consistent Read Changes Per Txn Blocks Per Txn 0 2.7e+000 9.4e+002
(중간생략)
User Rollback UndoRec Applied Per Sec Records Per Second 0 1.7e-002 8.3e-002
User Rollbacks Per Sec Rollbacks Per Second 0 0 0
User Rollbacks Percentage % (UserRollback/TotalUserTxn) 0 0 0
User Transaction Per Sec Transactions Per Second 0 2.0e-001 4.8e+000
Workload Capture and Replay status status 0 0 0
--Baseline 이름 변경
SQL> EXEC dbms_workload_repository.rename_baseline(
old_baseline_name => 'OFFICE_HOURS'
,new_baseline_name => 'OFFICE_HOURS_DELETED'
);
SQL> SELECT baseline_name, start_snap_id, start_snap_time, end_snap_id, end_snap_time, expiration
FROM dba_hist_baseline
WHERE baseline_name LIKE 'OFFICE_HOURS%'
AND baseline_type = 'STATIC';
BASELINE_NAME START_SNAP_ID START_SNAP_TIME END_SNAP_ID END_SNAP_TIME EXPIRATION
----------------------- ------------- -------------------- ----------- -------------------- ----------
OFFICE_HOURS_DELETED 57563 2017/11/12 09:00:22 57572 2017/11/12 18:00:34 30
--Baseline 이름 삭제
SQL> EXEC dbms_workload_repository.drop_baseline(
baseline_name => 'OFFICE_HOURS_DELETED'
,cascade => TRUE
);
SQL> SELECT baseline_name, start_snap_id, start_snap_time, end_snap_id, end_snap_time, expiration
FROM dba_hist_baseline
WHERE baseline_name LIKE 'OFFICE_HOURS%'
AND baseline_type = 'STATIC';
BASELINE_NAME START_SNAP_ID START_SNAP_TIME END_SNAP_ID END_SNAP_TIME EXPIRATION
---------------------------------------------------------------- ------------- -------------------- ----------- -------------------- ----------
0 rows selected.
- 이동 윈도우 베이스라인
- 윈도우 크기만 변경 가능: dbms_workload_repository 패키지의 modify_baseline_window_size
SQL> SELECT BASELINE_NAME, BASELINE_TYPE, MOVING_WINDOW_SIZE
FROM dba_hist_baseline
WHERE baseline_type = 'MOVING_WINDOW';
BASELINE_NAME BASELINE_TYPE MOVING_WINDOW_SIZE
---------------------------------------------------------------- ------------- ------------------
SYSTEM_MOVING_WINDOW MOVING_WINDOW 8
EXEC dbms_workload_repository.modify_baseline_window_size(window_size => 30);
- Statspack
- 설치하기: $ORACEL_HOME/rdbms/admin/spcreate.sql로 설치
SQL> @spcreate
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: ********
Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data. Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.
Choose the PERFSTAT users's default tablespace. This is the tablespace
in which the STATSPACK tables and indexes will be created.
TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
CONTEXT PERMANENT
EDUTBS PERMANENT
GISCVI01 PERMANENT
ICMLFQ32 PERMANENT
ICMLNF32 PERMANENT
ICMLSNDX PERMANENT
ICMSFQ04 PERMANENT
ICMVFQ04 PERMANENT
INDX PERMANENT
INGRIAN PERMANENT
LOB_IDX PERMANENT
LOB_TB PERMANENT
LOGMIR_T PERMANENT
MAIL2006_DAT PERMANENT
MAIL2006_IDX PERMANENT
PERFSTAT PERMANENT
SELF2006_DAT PERMANENT
SELF2006_IDX PERMANENT
SIGNGATE PERMANENT
SYSAUX PERMANENT *
SYSMASTER_DATA PERMANENT
TEST PERMANENT
Pressing <return> will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: perfstat
Using tablespace PERFSTAT as PERFSTAT default tablespace.
Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas). Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.
Choose the PERFSTAT user's Temporary tablespace.
TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP TEMPORARY *
TEMP01 TEMPORARY
TEMP02 TEMPORARY
Pressing <return> will result in the database's default Temporary
tablespace (identified by *) being used.
Enter value for temporary_tablespace: temp
Using tablespace temp as PERFSTAT temporary tablespace.
... Creating PERFSTAT user
... Installing required packages
... Creating views
... Granting privileges
NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.
(중간 생략)
Creating Package STATSPACK...
Package created.
No errors.
Creating Package Body STATSPACK...
Package body created.
No errors.
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
- 리포지터리 구성하기
- 구정정보는 perfstat 스키마의 stats$statspack_parameter 테이블에 저장
- 파라미터
- 스냅샷 레벨: 스냅샷을 찍을 때 저장될 데이터를 정의
| 레벨 | 설명 |
|---|
| 0 | 일반 성능 통계 수집 |
| 5 | 레벨0과 마찬가지로 일반 성능 통계를 수집할 뿐만 아니라, 임계치를 초과하는 SQL 구문의 통계도 수집(기본값) |
| 6 | 레벨5 이하에서 수집되는 모든 통계에 추가로 사용량 통계를 포함한 실행 계획까지 수집 |
| 7 | 레벨6 이하에서 수집되는 모든 통계에 추가로 임계치를 초과하는 세그먼트 레벨의 통계를 수집 |
| 10 | 레벨7 이하에서 수집되는 모든 통계에 추가로 래치 통계까지 수집 |
- SQL 임계치: SQL을 수집할지 판단할 때 사용되는 6개의 임계치, 이 중 하나 이상을 초과한 SQL 수집
| 1 | 실행 횟수 |
| 2 | 파싱 콜 횟수 |
| 3 | 물리적 읽기의 횟수 |
| 4 | 놀리적 읽기의 횟수 |
| 5 | 공유 가능한 메모리의 양 |
| 6 | 자식 커서의 수 |
- 세그먼트 통계 임계치: 세그먼트 통계 수집 여부를 판단할 때 사용되는 7개 임계치,이 중 하나 이상을 초과한 세그먼트 통계 수집
| 1 | 논리적 읽기의 수 |
| 2 | 물리적 읽기의 수 |
| 3 | buffer busy waits의 수 |
| 4 | row lock waits의 수 |
| 5 | ITL waits의 수 |
| 6 | global cache-consistent read blocks의 수 |
| 7 | global cache current blocks의 수 |
SQL> SELECT parameter, value
FROM stats$statspack_parameter
UNPIVOT( value FOR parameter IN ( SNAP_LEVEL,
EXECUTIONS_TH,
PARSE_CALLS_TH,
DISK_READS_TH,
BUFFER_GETS_TH,
SHARABLE_MEM_TH,
VERSION_COUNT_TH,
SEG_PHY_READS_TH,
SEG_LOG_READS_TH,
SEG_BUFF_BUSY_TH,
SEG_ROWLOCK_W_TH,
SEG_ITL_WAITS_TH,
SEG_CR_BKS_SD_TH,
SEG_CU_BKS_SD_TH)
);
PARAMETER VALUE
---------------- ---------
SNAP_LEVEL 5
EXECUTIONS_TH 100
PARSE_CALLS_TH 1000
DISK_READS_TH 1000
BUFFER_GETS_TH 10000
SHARABLE_MEM_TH 1048576
VERSION_COUNT_TH 20
SEG_PHY_READS_TH 1000
SEG_LOG_READS_TH 10000
SEG_BUFF_BUSY_TH 100
SEG_ROWLOCK_W_TH 100
SEG_ITL_WAITS_TH 100
SEG_CR_BKS_SD_TH 1000
SEG_CU_BKS_SD_TH 1000
--파라미터 변경
EXEC perfstat.statspack.modify_statspack_parameter(i_snap_level => 6);
- 스냅샷 찍기와 삭제하기
- 스냅샷 찍기:statspack 패키지의 snap(함수, 프로시저)
--파라미터 없이 스냅샷 찍기
EXEC perfstat.statspack.snap();
- 스냅샷 삭제
- 시작 스냅샷ID와 종료 스냅샷ID사이의 모든 스냅샷:i_begin_snap, i_end_snap
- 시작일과 종료일 사이에 생성된 모든 스냅샷:i_begin_date, i_end_date
- 지정한 날짜 이전에 생성된 모든 스냅샷:i_purge_before_date
- 지정한 일수보다 오래된 모든 스냅샷 삭제: i_num_days
--i_extended_purge: SQL과 실행 계획까지 삭제
EXEC perfstat.statspack.purge(
i_purge_before_date => to_date('2017-01-01', 'YYYY-MM-DD'),
i_extended_purge => TRUE
);
EXEC perfstat.statspack.make_baseline(
i_begin_date => to_date('2017-11-12 09:00:00', 'YYYY-MM-DD HH24:MI:SS'),
i_end_date => to_date('2017-11-12 18:00:00', 'YYYY-MM-DD HH24:MI:SS')
);
EXEC perfstat.statspack.clearx c_baseline(
i_begin_date => to_date('2017-11-12 09:00:00', 'YYYY-MM-DD HH24:MI:SS'),
i_end_date => to_date('2017-11-12 18:00:00', 'YYYY-MM-DD HH24:MI:SS')
);
- 진단 팩을 이용한 분석: EM(Performance 페이지), AWR
WORKLOAD REPOSITORY report for
>>> 1. 인스턴스 및 서버 정보
DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
DCBIS 1368679494 DCBIS 1 23-Sep-17 03:54 11.2.0.2.0 NO
Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
kdcbdb1a AIX-Based Systems (64-bit) 16 4 31.00
>>> 2. 스냅샷 정보
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 57618 14-Nov-17 16:00:38 219 3.4
End Snap: 57619 14-Nov-17 17:00:39 208 3.5
Elapsed: 60.02 (mins)
DB Time: 5.35 (mins)
>>> 3. SGA 구성정보
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 304M 304M Std Block Size: 8K
Shared Pool Size: 576M 576M Log Buffer: 7,548K
>>> 3. 메트릭 정보
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
DB Time(s): 0.1 0.0 0.00 0.00
DB CPU(s): 0.1 0.0 0.00 0.00
Redo size: 7,755.8 2,989.2
Logical reads: 2,021.7 779.2
Block changes: 53.4 20.6
Physical reads: 198.3 76.4
Physical writes: 3.9 1.5
User calls: 51.4 19.8
Parses: 34.0 13.1
Hard parses: 22.2 8.6
W/A MB processed: 3.3 1.3
Logons: 0.1 0.1
Executes: 653.0 251.7
Rollbacks: 0.0 0.0
Transactions: 2.6
>>> 4. 일련의 비율 정보
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 94.35 In-memory Sort %: 100.00
Library Hit %: 99.88 Soft Parse %: 34.78
Execute to Parse %: 94.79 Latch Hit %: 99.96
Parse CPU to Parse Elapsd %: 8.53 % Non-Parse CPU: 99.37
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 88.87 90.30
% SQL with executions>1: 77.57 87.26
% Memory for SQL w/exec>1: 45.71 85.51
>>> 5. Top 5 이벤트의 리소스 사용율 프로파일
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 193 60.2
log file sync 9,230 11 1 3.6 Commit
direct path read 4,755 6 1 1.8 User I/O
db file sequential read 374,373 3 0 .9 User I/O
control file sequential read 172,899 1 0 .2 System I/O
>>> 6. CPU 사용율
Host CPU (CPUs: 16 Cores: 4 Sockets: )
~~~~~~~~ Load Average
Begin End %User %System %WIO %Idle
--------- --------- --------- --------- --------- ---------
0.68 0.88 0.8 0.4 0.1 98.8
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 0.3
% of busy CPU for Instance: 29.4
%DB time waiting for CPU - Resource Mgr: 0.0
>>> 7. 메모리 사용율
Memory Statistics
~~~~~~~~~~~~~~~~~ Begin End
Host Mem (MB): 31,744.0 31,744.0
SGA use (MB): 960.0 960.0
PGA use (MB): 589.6 591.7
% Host Mem used for SGA+PGA: 4.88 4.89
>>> 8. 시간 모델 통계
Time Model Statistics DB/Inst: DCBIS/DCBIS Snaps: 57618-57619
-> Total time in database user-calls (DB Time): 320.8s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 274.0 85.4
DB CPU 193.2 60.2
parse time elapsed 20.1 6.3
hard parse elapsed time 12.0 3.7
PL/SQL execution elapsed time 2.9 .9
connection management call elapsed time 2.7 .9
repeated bind elapsed time 2.5 .8
hard parse (sharing criteria) elapsed time 0.9 .3
hard parse (bind mismatch) elapsed time 0.9 .3
failed parse elapsed time 0.2 .0
PL/SQL compilation elapsed time 0.1 .0
sequence load elapsed time 0.1 .0
DB time 320.8
background elapsed time 51.0
background cpu time 5.7
-------------------------------------------------------------
(중간 생략)
Wait Event Histogram DB/Inst: DCBIS/DCBIS Snaps: 57618-57619
-> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
-> % of Waits: value of .0 indicates value was <.05%; value of null is truly 0
-> % of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms
-> Ordered by Event (idle events last)
% of Waits
-----------------------------------------------
Total
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
ADR block file read 16 100.0
ADR block file write 5 60.0 40.0
ADR file lock 6 100.0
Disk file operations I/O 1224 100.0
LGWR wait for redo copy 115 100.0
SQL*Net break/reset to cli 46 73.9 19.6 2.2 4.3
SQL*Net message to client 134.3 100.0
SQL*Net more data from cli 406 100.0
SQL*Net more data to clien 659 97.3 .2 .5 .3 1.4 .3 .2
buffer busy waits 52 100.0
control file parallel writ 1280 15.9 72.0 7.7 3.3 1.1
control file sequential re 177.1 100.0
cursor: pin S 1 100.0
db file parallel read 3 100.0
db file parallel write 1236 42.6 40.0 11.6 4.5 .6 .2 .6
db file scattered read 1293 100.0
db file sequential read 376.4 100.0 .0 .0 .0
direct path read 4755 75.0 12.4 7.0 4.5 .8 .3 .1
direct path sync 11 63.6 18.2 18.2
direct path write 72 100.0
direct path write temp 1652 99.3 .2 .4 .1
enq: CR - block range reus 5 20.0 80.0
enq: TX - row lock content 118 32.2 33.9 25.4 6.8 1.7
latch free 5 80.0 20.0
latch: In memory undo latc 18 100.0
(중간 생략)
SQL ordered by Elapsed Time DB/Inst: DCBIS/DCBIS Snaps: 57618-57619
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
-> %Total - Elapsed Time as a percentage of Total DB time
-> %CPU - CPU Time as a percentage of Elapsed Time
-> %IO - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for 89.8% of Total DB Time (s): 321
-> Captured PL/SQL account for 23.5% of Total DB Time (s): 321
Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
104.3 120 0.87 32.5 60.6 1.7 968q4f33a89pd
Module: JDBC Thin Client
SELECT * FROM (SELECT A.TABLESPACE_NAME, ROUND(A.TOTAL_BYTES/1024/1024,1) AS TOT
AL_MB, ROUND(NVL(B.FREE_BYTES, 0)/1024/1024,1) AS FREE_MB, ROUND(NVL(B.FREE_BYTE
S, 0) * 100 / A.TOTAL_BYTES, 1) AS FREE_PCT FROM (SELECT TABLESPACE_NAME, SUM(BY
TES) AS TOTAL_BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A, (SELECT TAB
52.3 359 0.15 16.3 60.0 .1 5tftvawtbsjjx
Module: JDBC Thin Client
BEGIN SP_KCBSCR25_PROFG3(:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ) ; END;
41.9 2,163,921 0.00 13.1 60.8 .0 9tgj4g8y4rwy8
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis
ts,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0),NVL
(bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
(중간 생략)
SQL ordered by CPU Time DB/Inst: DCBIS/DCBIS Snaps: 57618-57619
-> Resources reported for PL/SQL code includes the resources used by all SQL
statements called by the code.
-> %Total - CPU Time as a percentage of Total DB CPU
-> %CPU - CPU Time as a percentage of Elapsed Time
-> %IO - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for 85.9% of Total CPU Time (s): 193
-> Captured PL/SQL account for 21.1% of Total CPU Time (s): 193
CPU CPU per Elapsed
Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id
---------- ------------ ---------- ------ ---------- ------ ------ -------------
63.2 120 0.53 32.7 104.3 60.6 1.7 968q4f33a89pd
Module: JDBC Thin Client
SELECT * FROM (SELECT A.TABLESPACE_NAME, ROUND(A.TOTAL_BYTES/1024/1024,1) AS TOT
AL_MB, ROUND(NVL(B.FREE_BYTES, 0)/1024/1024,1) AS FREE_MB, ROUND(NVL(B.FREE_BYTE
S, 0) * 100 / A.TOTAL_BYTES, 1) AS FREE_PCT FROM (SELECT TABLESPACE_NAME, SUM(BY
TES) AS TOTAL_BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A, (SELECT TAB
31.4 359 0.09 16.2 52.3 60.0 .1 5tftvawtbsjjx
Module: JDBC Thin Client
BEGIN SP_KCBSCR25_PROFG3(:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ) ; END;
25.5 2,163,921 0.00 13.2 41.9 60.8 .0 9tgj4g8y4rwy8
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis
ts,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0),NVL
(bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3
(이하 생략)
- 진단 팩을 이용하지 않는 분석: Statspack
STATSPACK report for
>>> 1. 인스턴스 및 서버 정보
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
1368679494 DCBIS 1 23-Sep-17 03:54 11.2.0.2.0 NO
Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
kdcbdb1a AIX-Based Systems (64- 16 4 0 31.0
>>> 2. 스냅샷 정보
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 21 14-Nov-17 16:52:13 203 3.6
End Snap: 22 14-Nov-17 17:13:08 208 3.5
Elapsed: 20.92 (mins) Av Act Sess: 0.1
DB time: 1.88 (mins) DB CPU: 1.15 (mins)
>>> 3.SGA 구성정보
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 304M Std Block Size: 8K
Shared Pool: 576M Log Buffer: 7,216K
>>> 3. 메트릭 정보
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 0.1 0.0 0.00 0.00
DB CPU(s): 0.1 0.0 0.00 0.00
Redo size: 10,188.7 3,837.6
Logical reads: 2,042.3 769.2
Block changes: 60.1 22.6
Physical reads: 243.6 91.8
Physical writes: 4.6 1.7
User calls: 47.3 17.8
Parses: 32.4 12.2
Hard parses: 19.0 7.2
W/A MB processed: 3.4 1.3
Logons: 0.1 0.1
Executes: 632.0 238.0
Rollbacks: 0.0 0.0
Transactions: 2.7
>>> 4. 일련의 비율 정보
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 94.22 Optimal W/A Exec %: 100.00
Library Hit %: 99.81 Soft Parse %: 41.38
Execute to Parse %: 94.87 Latch Hit %: 99.96
Parse CPU to Parse Elapsd %: 9.46 % Non-Parse CPU: 99.27
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 89.64 90.43
% SQL with executions>1: 83.36 85.64
% Memory for SQL w/exec>1: 71.32 73.61
>>> 5. Top 5 이벤트의 리소스 사용율 프로파일
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 57 77.1
log file parallel write 4,554 6 1 7.9
log file sync 3,267 4 1 5.8
os thread startup 51 3 58 4.0
db file parallel write 474 1 3 2.0
-------------------------------------------------------------
>>> 6. CPU 사용율
Host CPU (CPUs: 16 Cores: 4 Sockets: 0)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
0.78 1.14 0.75 0.42 98.83 0.07 4.04
Instance CPU
~~~~~~~~~~~~ % Time (seconds)
-------- --------------
Host: Total time (s): 20,104.2
Host: Busy CPU time (s): 235.0
% of time Host is Busy: 1.2
Instance: Total CPU time (s): 71.5
% of Busy CPU used for Instance: 30.4
Instance: Total Database time (s): 132.6
%DB time waiting for CPU (Resource Mgr): 0.0
Virtual Memory Paging
~~~~~~~~~~~~~~~~~~~~~
KB paged out per sec: 117.7
KB paged in per sec: 9.0
>>> 7. 메모리 사용율
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 31,744.0 31,744.0
SGA use (MB): 1,449.5 1,449.5
PGA use (MB): 586.4 591.5
% Host Mem used for SGA+PGA: 6.4 6.4
-------------------------------------------------------------
>>> 8. 시간 모델 통계
Time Model System Stats DB/Inst: DCBIS/DCBIS Snaps: 21-22
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 97.6 86.5
DB CPU 69.0 61.2
parse time elapsed 5.9 5.3
hard parse elapsed time 3.4 3.0
PL/SQL execution elapsed time 1.0 .9
connection management call elapsed 0.9 .8
repeated bind elapsed time 0.9 .8
hard parse (sharing criteria) elaps 0.3 .3
hard parse (bind mismatch) elapsed 0.3 .3
PL/SQL compilation elapsed time 0.1 .1
sequence load elapsed time 0.0 .0
DB time 112.8
background elapsed time 19.9
background cpu time 2.5
-------------------------------------------------------------
Foreground Wait Events DB/Inst: DCBIS/DCBIS Snaps: 21-22
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
(중간 생략)
Wait Event Histogram DB/Inst: DCBIS/DCBIS Snaps: 21-22
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)
Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
Disk file operations I/O 671 100.0
LGWR wait for redo copy 51 100.0
SQL*Net more data to clien 56 100.0
buffer busy waits 17 94.1 5.9
control file parallel writ 446 26.0 62.8 7.6 2.7 .9
control file sequential re 109K 100.0 .0
db file parallel read 1 100.0
db file parallel write 474 40.9 40.5 10.3 4.9 1.5 .6 1.3
db file scattered read 689 98.4 1.3 .3
db file sequential read 129K 100.0 .0 .0 .0
direct path read 168 99.4 .6
direct path sync 3 100.0
direct path write 34 100.0
direct path write temp 572 99.5 .2 .3
enq: CR - block range reus 1 100.0
enq: TX - row lock content 40 45.0 35.0 20.0
latch free 2 100.0
latch: In memory undo latc 9 100.0
library cache: mutex X 9 100.0
log file parallel write 4554 53.2 36.6 7.3 2.3 .5 .1
log file sync 3267 52.9 36.1 7.9 2.6 .5 .1
os thread startup 51 100.0
reliable message 1 100.0
DIAG idle wait 2508 100.0
SQL*Net message from clien 43K 70.5 10.8 .9 .5 .2 .1 14.9 2.2
SQL*Net message to client 43K 100.0
SQL*Net more data from cli 135 100.0
Space Manager: slave idle 911 .3 .7 1.9 97.1
Streams AQ: qmn coordinato 96 52.1 1.0 46.9
Streams AQ: qmn slave idle 45 100.0
class slave wait 9 100.0
dispatcher timer 21 100.0
jobq slave wait 2647 .1 .0 .0 99.8
pmon timer 418 100.0
rdbms ipc message 10K 7.5 2.4 13.6 7.3 2.9 .9 27.7 37.7
shared server idle wait 42 100.0
smon timer 11 9.1 9.1 27.3 54.5
-------------------------------------------------------------
SQL ordered by CPU DB/Inst: DCBIS/DCBIS Snaps: 21-22
-> Total DB CPU (s): 69
-> Captured SQL accounts for 120.4% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
22.17 42 0.53 32.1 36.52 1,492,948 648130771
Module: JDBC Thin Client
SELECT * FROM (SELECT A.TABLESPACE_NAME, ROUND(A.TOTAL_BYTES/102
4/1024,1) AS TOTAL_MB, ROUND(NVL(B.FREE_BYTES, 0)/1024/1024,1) A
S FREE_MB, ROUND(NVL(B.FREE_BYTES, 0) * 100 / A.TOTAL_BYTES, 1)
AS FREE_PCT FROM (SELECT TABLESPACE_NAME, SUM(BYTES) AS TOTAL_BY
11.05 126 0.09 16.0 18.28 43,653 1563971278
Module: JDBC Thin Client
BEGIN SP_KCBSCR25_PROFG3(:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 )
; END;
8.74 730,540 0.00 12.7 14.16 1,577,293 2926525797
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0),NVL(scanhint,0),NVL(bitmapranges,0) from seg$ where
ts#=:1 and file#=:2 and block#=:3
(중간 생략)
-------------------------------------------------------------
SQL ordered by Elapsed time for DB: DCBIS Instance: DCBIS Snaps: 21 -22
-> Total DB Time (s): 113
-> Captured SQL accounts for 129.1% of Total DB Time
-> SQL reported below exceeded 1.0% of Total DB Time
Elapsed Elap per CPU Old
Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
36.52 42 0.87 32.4 22.17 93,472 648130771
Module: JDBC Thin Client
SELECT * FROM (SELECT A.TABLESPACE_NAME, ROUND(A.TOTAL_BYTES/102
4/1024,1) AS TOTAL_MB, ROUND(NVL(B.FREE_BYTES, 0)/1024/1024,1) A
S FREE_MB, ROUND(NVL(B.FREE_BYTES, 0) * 100 / A.TOTAL_BYTES, 1)
AS FREE_PCT FROM (SELECT TABLESPACE_NAME, SUM(BYTES) AS TOTAL_BY
18.28 126 0.15 16.2 11.05 1,260 1563971278
Module: JDBC Thin Client
BEGIN SP_KCBSCR25_PROFG3(:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 )
; END;
14.16 730,540 0.00 12.6 8.74 7 2926525797
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#
,iniexts,NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, N
VL(spare1,0),NVL(scanhint,0),NVL(bitmapranges,0) from seg$ where
ts#=:1 and file#=:2 and block#=:3
(이하 생략)