트러블슈팅 오라클 퍼포먼스 2판 (2017년)
진단 팩과 튜닝 팩을 이용한 분석
0
0
46,908
by 구루비스터디 V$METRIC_HISTORY Diagnostics Pack Tuning Pack V$ACTIVE_SESSION_HISTORY [2023.09.08]
진단 팩과 튜닝 팩을 이용한 분석
개요
| 1 | 엔터프라이즈 관리자(EM)의 performance 페이지 |
| 2 | SQL*Plus에서 직접 동적 성능 뷰를 조회 |
데이터베이스 서버 부하
| EM | performance Home 페이지 |
| SQL*Plus | V$METRIC_HISTORY |
SQL> @host_load_hist
DCBIS / 2017-11-10
BEGIN_TIME DURATION DB_FG_CPU DB_BG_CPU NON_DB_CPU OS_LOAD NUM_CPU
---------- -------- --------- --------- ---------- ------- -------
14:24:29 60.03 0.05 0.00 0.10 0.46 4
14:25:29 60.04 0.04 0.00 0.22 0.50 4
14:26:29 60.03 0.04 0.00 0.10 0.58 4
14:27:29 60.04 0.04 0.00 0.06 0.67 4
14:28:29 60.04 0.06 0.00 0.09 0.57 4
14:29:29 60.03 0.12 0.00 0.15 0.68 4
14:30:29 60.04 0.04 0.00 0.17 0.63 4
14:31:29 60.03 0.04 0.00 0.15 0.67 4
14:32:29 60.04 0.05 0.00 0.10 0.64 4
14:33:29 60.04 0.04 0.00 0.11 0.95 4
14:34:29 60.03 0.06 0.00 0.10 0.95 4
14:35:29 60.04 0.05 0.00 0.07 0.71 4
14:36:29 59.04 0.05 0.00 0.26 1.12 4
14:37:28 60.03 0.04 0.00 0.08 0.71 4
14:38:28 60.04 0.04 0.00 0.07 0.82 4
14:39:28 60.04 0.05 0.00 0.11 0.98 4
14:40:28 60.03 0.06 0.00 0.10 0.70 4
14:41:28 60.04 0.05 0.00 0.26 0.90 4
14:42:28 60.04 0.04 0.00 0.07 0.81 4
14:43:28 60.04 0.04 0.00 0.07 0.63 4
14:44:28 60.03 0.05 0.00 0.10 0.50 4
14:45:28 60.04 0.04 0.00 0.07 0.65 4
14:46:28 60.04 0.06 0.00 0.11 0.56 4
14:47:28 60.03 0.04 0.00 0.25 0.77 4
시스템 레벨 분석
| EM | Top Activity 페이지 |
| SQL*Plus | V$ACTIVE_SESSION_HISTORY |
SQL> @ash_activity all all
DCBIS / 2017-11-10
TIME AvgActSes CPU% Sched% UsrIO% SysIO% Conc% Appl% Commit% Config% Admin% Net% Queue% Cluster% Other%
----- --------- ------ ------ ------ ------ ------ ------ ------- ------- ------ ------ ------ -------- ------
14:30 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:31 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:32 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:33 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:34 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:35 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:36 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:37 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:38 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:39 0.1 80.0 0.0 0.0 0.0 0.0 20.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:40 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:41 0.2 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:42 0.2 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:43 0.2 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:44 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:45 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:46 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:47 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:48 1.5 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:49 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:50 1.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:51 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:52 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:53 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:54 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
SQL> @ash_top_sqls 2017-11-10_14:55:00.000 2017-11-10_15:00:00.000 all all
Period Begin Period End Total Sample Count
------------------------------ ------------------------------ ------------------
2017-11-10_14:55:00.000000000 2017-11-10_15:00:00.000000000 17
Activity% DB Time CPU% UsrIO% Wait% SQL Id SQL Type
--------- ---------- ------ ------ ------ ------------- ----------------------------
58.8 10 100.0 0.0 0.0 968q4f33a89pd SELECT
11.8 2 100.0 0.0 0.0 2sb1y6m7w1s5t INSERT
11.8 2 100.0 0.0 0.0 71ux6xzn619ay SELECT
5.9 1 100.0 0.0 0.0 0pgs023t72bmb ALTER SESSION
5.9 1 100.0 0.0 0.0 0ws7ahf1d78qa SELECT
5.9 1 100.0 0.0 0.0 a0xbfqvcx3xfm INSERT
SQL> @ash_top_sessions 2017-11-10_14:55:00.000 2017-11-10_15:00:00.000
경 과: 00:00:00.00
Period Begin Period End Total Sample Count
------------------------------ ------------------------------ ------------------
2017-11-10_14:55:00.000000000 2017-11-10_15:00:00.000000000 21
Activity% DB Time CPU% UsrIO% Wait% Session Id Ses Serial User Name Program
--------- ---------- ------ ------ ------ ---------- ---------- -------------------- ----------------------------------
47.6 10 100.0 0.0 0.0 415 57699 DBMON JDBC Thin Client
9.5 2 100.0 0.0 0.0 1502 20179 KSAPP JDBC Thin Client
4.8 1 100.0 0.0 0.0 103 12933 KSAPP JDBC Thin Client
4.8 1 100.0 0.0 0.0 301 1 SYS oracle@kdcbdb1a (PSP0)
4.8 1 100.0 0.0 0.0 516 8201 ACSAPP JDBC Thin Client
4.8 1 100.0 0.0 0.0 516 8205 ACSAPP JDBC Thin Client
4.8 1 100.0 0.0 0.0 801 1 SYS oracle@kdcbdb1a (DIA0)
4.8 1 100.0 0.0 0.0 905 14595 ACSAPP JDBC Thin Client
4.8 1 0.0 0.0 100.0 1201 1 SYS oracle@kdcbdb1a (LGWR)
4.8 1 0.0 0.0 100.0 1202 1 SYS oracle@kdcbdb1a (CJQ0)
SQL> @ash_top_actions 2017-11-10_14:55:00.000 2017-11-10_15:00:00.000
Period Begin Period End Total Sample Count
------------------------------ ------------------------------ ------------------
2017-11-10_14:55:00.000000000 2017-11-10_15:00:00.000000000 21
Activity% DB Time CPU% UsrIO% Wait% Service Module Action
--------- ---------- ------ ------ ------ ---------------------- ---------------------- ---------------------
66.7 14 100.0 0.0 0.0 SYS$USERS JDBC Thin Client
19.0 4 50.0 0.0 50.0 SYS$BACKGROUND
9.5 2 100.0 0.0 0.0 DCBIS JDBC Thin Client
4.8 1 100.0 0.0 0.0 SYS$USERS
세션 레벨 분석
| EM | Search Session 메뉴 또는 Top Activity 페이지(Top session 테이블) |
| SQL*Plus | V$ACTIVE_SESSION_HISTORY |
SQL> @ash_activity 415 all
DCBIS / 2017-11-10
TIME AvgActSes CPU% Sched% UsrIO% SysIO% Conc% Appl% Commit% Config% Admin% Net% Queue% Cluster% Other%
----- --------- ------ ------ ------ ------ ------ ------ ------- ------- ------ ------ ------ -------- ------
14:48 0.5 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:51 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:52 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:53 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:54 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:55 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:56 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:57 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:58 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:59 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:00 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:01 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:02 0.0 50.0 0.0 50.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:03 0.1 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:04 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:05 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:06 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:07 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:08 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:18 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:19 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:20 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:21 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:22 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:23 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
SQL 구문 정보
| EM | Statistics 탭의 Summary 차트 또는 Search SQL |
| SQL*Plus | v$sqlarea, v$sql, v$sqlstats |
15:43:47 KSDBA@DCBIS[kdcbdb1a]> @ash_activity all 968q4f33a89pd
DCBIS / 2017-11-10
TIME AvgActSes CPU% Sched% UsrIO% SysIO% Conc% Appl% Commit% Config% Admin% Net% Queue% Cluster% Other%
----- --------- ------ ------ ------ ------ ------ ------ ------- ------- ------ ------ ------ -------- ------
14:51 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:52 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:53 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:54 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:55 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:56 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:57 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:58 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
14:59 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:00 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:01 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:02 0.0 50.0 0.0 50.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:03 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:04 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:05 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:06 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:07 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:08 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:18 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:19 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:20 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:21 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:22 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:23 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
15:24 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
"데이터베이스 스터디모임" 에서 2017년에 "전문가를 위한 트러블슈팅 오라클 퍼포먼스(Second Edition)
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4343
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.