07. Response Time Analysis 방법론과 OWI
- Response Time = Service Time + Wait Time
= CPU Time + Queue Time
- Service Time : 프로세스가 정상적으로 동작하며 일을 수행한 시간
- Wait Time : 대기 이벤트가 발생해 수행을 잠시 멈추고 대기한 시간
-- 20 개의 프로세스의 읽는 범위를 다르게해서 Parallel 방식으로 수행
insert into t1
select /*+ ordered use_nl(t3) */ seq.nextval, t2.*, t3.*
from t2, t3
where t2.key = t3.key
and t2.col between :range1 and :range2;
※ 20개를 동시에 수행시켰는데도 생각처럼 속도가 나질 않아 이벤트 분석을 해 보니 db file scattered read 대기 이벤트가
Wait Time 의 대부분을 차지하고 있었다. 이 이벤트의 발생 원인은 Full Table Scan 이다.
t2 테이블 기준으로 NL 조인을 수행하면서 반복 액세스가 일어나는 t3 테이블 조인 컬럼에 인덱스가 없어 매번 Full Table
Scan으로 처리되고 있었다.
인덱스를 추가해 정산적인 Index Scan 으로 처리되도록 튜닝을 실시하였다.
※ 이번에는 buffer busy wait 과 latch: cache buffers chains 이벤트가 발생하기 시작했다.
이전에는 디스크 I/O 때문에 서버 프로세스의 처리속도가 매우 느렸었는데, 이제 많은 처리를 버퍼 캐시 내에서 할 수 있게
됨으로 인해 서버 프로세스의 처리 속도가 크게 향상되었고 그 때문에 버퍼 블록에 대한 동시 액세스가 증가하면서 메모리
경합이 발생하게 되었다.
캐싱된 버퍼 블록에 대한 읽기 요청이 많아 생기는 문제이므로 블록 요청 횟수를 줄여야 한다.
NL 조인을 해시 조인 방식으로 바꿔 보았다.
※ select 경합이 해소되니깐 insert 처리부분에서 경합이 발생하기 시작한 것이다.
insert 에 의한 Redo 레코드 생성 속도가 증가하니까 Redo 로그 버퍼 공간이 부족할 때 발생하는 log buffer space 대기
이벤트가 새롭게 발생하기 시작했다.
또한 Sequence 테이블에 대한 경합으로 enq: SQ - contention 까지 발생했다.
Redo 로그 버퍼 크기를 약간 늘려주고, Sequence 캐시 사이즈를 10 에서 20으로 늘려 주었다.
08. Statspack / AWR
- Statspack : SQL 을 이용한 딕셔너리 조회 방식
- AWR : DMA (Direct Memory Access) 방식으로 SGA 를 직접 액세스하기 때문에 좀 더 빠르고 부하가 적다.
▣ 정보 수집 뷰
- v$segstat
- v$undostat
- v$latch
- v$latch_children
- v$sgastat
- v$pgastat
- v$sysstat
- v$system_event
- v$waitstat
- v$sql
- v$sql_plan
- v$sqlstats
- v$active_session_history
- v$osstat
- 기타 등등
(1) Statspack / AWR 기본 사용법
-- Statspack 사용법
SQL > @$ORACLE_HOME/rdbms/admin/spreport
-- AWR 사용법
-- @$ORACLE_HOME/rdbms/admin/awrrpt.sql
SQL> @?/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1358207727 ORCL 1 ORA11G
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
------------ -------- ------------ ------------ ------------
1358207727 1 ORCL orcl localhost.lo
caldomain
* 1358207727 1 ORCL ORA11G localhost.lo
caldomain
Using 1358207727 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: 7
Listing the last 7 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ORA11G ORCL 55 20 Apr 2014 00:36 1
56 20 Apr 2014 02:00 1
57 20 Apr 2014 03:00 1
58 20 Apr 2014 04:00 1
59 21 Apr 2014 09:23 1
60 21 Apr 2014 10:00 1
61 21 Apr 2014 11:00 1
62 24 Apr 2014 09:33 1
63 24 Apr 2014 11:00 1
64 24 Apr 2014 12:00 1
65 24 Apr 2014 13:00 1
66 26 Apr 2014 01:09 1
67 26 Apr 2014 02:00 1
68 26 Apr 2014 03:00 1
69 26 Apr 2014 04:00 1
70 26 Apr 2014 10:36 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 66
Begin Snapshot Id specified: 66
Enter value for end_snap: 69
End Snapshot Id specified: 69
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_66_69.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awrrpt0427.html
.
.
.
Report written to awrrpt0427.html
(2) Statspack / AWR 리포트 분석
- Per Second : 각 측정 지표 값들을 측정 시간으로 나눈 것. 따라서 초당 부하 발생량을 의미.
- Per Transaction : 각 측정 지표 값들을 트랜잭션 개수로 나눈 것. 한 트랜잭션 내에서 평균적으로 얼마만큼의 부하가 발생하는지
나타냄
SELECT VALUE RSIZ FROM V$SYSSTAT WHERE NAME = 'redo size';
RSIZ
----------
21254156
SELECT VALUE GETS FROM V$SYSSTAT WHERE NAME = 'session logical reads';
GETS
----------
1608542
SELECT VALUE CHNG FROM V$SYSSTAT WHERE NAME = 'db block changes';
CHNG
----------
344578
SELECT VALUE PHYR FROM V$SYSSTAT WHERE NAME = 'physical reads';
PHYR
----------
17752
SELECT VALUE PHYW FROM V$SYSSTAT WHERE NAME = 'physical writes';
PHYW
----------
3457
SELECT VALUE UCAL FROM V$SYSSTAT WHERE NAME = 'user calls';
UCAL
----------
1345
SELECT VALUE PRSE FROM V$SYSSTAT WHERE NAME = 'parse count (total)';
PRSE
----------
29394
SELECT VALUE HPRSE FROM V$SYSSTAT WHERE NAME = 'parse count (hard)';
HPRSE
----------
3100
SELECT SRTM+SRTD FROM (SELECT VALUE SRTM FROM V$SYSSTAT WHERE NAME = 'sorts (memory)'),
(SELECT VALUE SRTD FROM V$SYSSTAT WHERE NAME = 'sorts (disk)');
SRTM+SRTD
----------
25226
SELECT VALUE LOGC FROM V$SYSSTAT WHERE NAME = 'logons cumulative';
LOGC
----------
336
SELECT VALUE EXE FROM V$SYSSTAT WHERE NAME = 'execute count';
EXE
----------
104689
SELECT UCOM+UROL FROM (SELECT VALUE UCOM FROM V$SYSSTAT WHERE NAME = 'user commits'),
(SELECT VALUE UROL FROM V$SYSSTAT WHERE NAME = 'user rollbacks');
UCOM+UROL
----------
797
-- % Blocks changed per Read : 읽은 블록 중 갱신이 발생하는 비중을 나타낸다.
SELECT ROUND(100*CHNG/GETS, 2) "% Blocks 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')
;
% Blocks changed per Read
-------------------------
2.18
09. ASH (Active Session History)
- 별도의 Third Party 모니터링 도구 없이 오라클 내에서 세션 레벨 실시간 모니터링을 가능케 하는 기능
SQL> select * from v$sgastat where name = 'ASH buffers';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool ASH buffers 4194304
1. AWR 뷰를 이용해 하루 동안의 이벤트 발생현황을 조회해 본다.
위의 그래프는 dba_hist_system_event 를 이용해 그린 것인데, 08:15 ~ 09:15 구간에서 end: TM - contention 이벤트가
다량 발생한 것이 확인되었다.
2. dba_hist_active_sess_history 를 조회해서 해당 이벤트를 많이 대기한 세션을 확인한다.
3. 블로킹 세션 정보를 통해 dba_hist_active_sess_history 를 다시 조회한다. 블로킹 세션이 찾아지면 해당 세션이 그 시점에
어떤 작업을 수행 중이었는지 확인한다.
sql_id 를 이용해 그 당시 SQL 과 실행계획까지 확인할 수 있다. v$sql 과 v$sql_plan 까지 AWR 에 저장되기 때문이다.
위 사례에서는 블로킹 세션이 Append Mode Insert 를 수행하면서 Exclusive 모드 TM Lock 에 의한 경합이 발생했다.
4. program, module, action, client_id 등 애플리케이션 정보를 이용해 관련 프로그램을 찾아 Append 힌트를 제거한다.
그러고 나서, 다른 트랜잭션과 동시 DML 이 발생할 수 있는 상황에서는 insert 문에 Append 힌트를 사용해서는 안 된다는
사실을 개발팀 전체에 공지한다.
10. V$SQL
- v$sql : 라이브러리 캐시에 캐싱돼 있는 각 Child 커서에 대한 수행통계를 보여준다.
쿼리가 수행을 마칠 때마다 갱신되며, 오랫동안 수행되는 쿼리는 5초마다 갱신이 이루어진다.
- v$sqlarea : Parent 커서에 대한 수행통계를 나타낸다.
-- V$SQL 정보
SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT, SQL_FULLTEXT, PARSING_SCHEMA_NAME, -- 1
SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, -- 2
LOADS, INVALIDATIONS, PARSE_CALLS, EXECUTIONS, FETCHES, ROWS_PROCESSED, -- 3
CPU_TIME, ELAPSED_TIME, -- 4
BUFFER_GETS, DISK_READS, SORTS, -- 5
APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, -- 6
CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, -- 7
FIRST_LOAD_TIME, LAST_ACTIVE_TIME -- 8
FROM V$SQL;
-----------------------------------------------------------------------------------
-- 1. 라이브러리 캐시에 적재된 SQL 커서 자체에 대한 정보
-- 2. SQL 커서에 의해 사용되는 메모리 사용량
-- 3. 하드파싱 및 무효화 발생횟수, Parse, Execute, Fetch Call 발생 횟수, Execute 또는 Fetch Call 시점에 처리한 로우 건수 등
-- 4. SQL 을 수행하면서 사용된 CPU time 과 소요시간
-- 5. SQL 을 수행하면서 발생한 논리적 블록 읽기와 디스크 읽기, 그리고 소트 발생 횟수
-- 6. SQL 수행 도중 대기 이벤트 때문에 지연이 발생한 시간
-- 7. 커서가 라이브러리 캐시에 처음 적재된 시점, 가장 마지막에 수행된 시점
----------------------------------------------------------------------------------
-- PARSING_SCHEMA_NAME 별 건당 V$SQL 정보
SELECT PARSING_SCHEMA_NAME,
COUNT(*) SQL_CNT,
COUNT(DISTINCT SUBSTR(SQL_TEXT, 1, 100)) SQL_CNT2,
SUM(EXECUTIONS) EXECUTIONS,
ROUND(AVG(BUFFER_GETS/EXECUTIONS)) BUFFER_GETS,
ROUND(AVG(DISK_READS/EXECUTIONS)) DISK_READS,
ROUND(AVG(ROWS_PROCESSED/EXECUTIONS)) ROWS_PROCESSED,
ROUND(AVG(ELAPSED_TIME/EXECUTIONS/1000000), 2) "ELAPSED_TIME(AVG)",
COUNT(CASE WHEN ELAPSED_TIME/EXECUTIONS/1000000 >= 10 THEN 1 END) "BAD SQL",
ROUND(MAX(ELAPSED_TIME/EXECUTIONS/1000000), 2) "ELAPSED_TIME(MAX)"
FROM V$SQL
WHERE PARSING_SCHEMA_NAME NOT IN ('SYSMAN', 'MDSYS', 'DBSNMP', 'EXFSYS', 'APEX_030200')
AND LAST_ACTIVE_TIME >= TO_DATE('20140401' ,'yyyymmdd')
AND EXECUTIONS > 0
GROUP BY PARSING_SCHEMA_NAME;
PARSING_SCHEMA_NAME SQL_CNT SQL_CNT2 EXECUTIONS BUFFER_GETS DISK_READS ROWS_PROCESSED ELAPSED_TIME(AVG) BAD SQL ELAPSED_TIME(MAX)
-------------------- ---------- ---------- ---------- ----------- ---------- -------------- ----------------- ---------- -----------------
SYS 550 497 22217 264 20 20 .14 0 8.8
11. End-To-End 성능관리
- End-To-End 방식의 애플리케이션 성능 관리 툴을 많이 사용하는 데, 이들 툴을 사용하면 Web, AP, DB Zone 으로 나눠 어느
구간에서 병목이 발생하는지를 실시간으로 모니터링 할 수 있다.
성능 저하 요인이 DB 로 판명되면, 시간이 오래 걸린 트랜잭션을 확인하고 해당 트랜잭션에서 수행된 서비스들을 상세 분석한다.
서비스별 수행시간과 서비스 내에서 수행된 SQL 의 수행시간, 그리고 각각이 전체에서 차지하는 점유율을 함께 보여 주기 때문에
성능 저하를 유발한 SQL을 쉽게 찾아낼 수가 있다.
12. 데이터베이스 성능 고도화 정석 해법
※ 데이터베이스 성능 튜닝 3대 핵심 요소
- 라이브러리 캐시 최적화
- 데이터베이스 Call 최소화
- I/O 효율화 및 버퍼캐시 최적화
- 데이터 모델 및 DB 설계 : 데이터 모델과 DB 설계의 개념은 보는 시각에 따라 다를 수 있는데, 여기서는 업무적 특성을
반영한 정규화된 데이터 모델을 바탕으로, DBMS 의 물리적 특성과 성능을 고려한 설계 변경, 그리고 DBMS 요소에 대한
설계를 실시하는 것
- 논리 데이터 모델 : DBMS 의 물리적 특성은 배제하고 데이터의 업무적 특성만을 반영한 논리 설계 (엔티티, 속성, 릴레이션
십 정의)
- 물리 데이터 모델 : DBMS 의 물리적 특성과 성능을 고려한 설계 변경 (엔티티 통합/분할, 인조식별자 정의, 이력관리, 반정
규화, 집계 테이블 설계 등), 테이블 정의, 컬럼 정의, PK/FK 정의 등
- DB 설계 : 파티션 설계, 클러스터 및 IOT 구성, 기초 인덱스 설계, 뷰 설계, 시퀀스 설계 등
- 애플리케이션 튜닝 : Lock 경합, 파싱 부하, 데이터베이스 Call 발생량을 최소화하고, 인덱스 설계 조정 및 SQL 변경을 통해
I/O 효율을 높이는 등의 튜닝 요소를 말한다.
- 데이터베이스 튜닝 : 초기화 파라미터 설정, SGA/PGA 메모리 구성, Redo 파일 구성, 테이블스페이스 및 데이터 파일 구성
등에 관한 전략을 조정하는 것을 말한다.
- 시스템 튜닝 : OS 레벨에서의 CPU 와 메모리 구성, I/O 서브시스템 구성, 네트워크 설정 같은 하드웨어적인 튜닝을 말한다.