트러블슈팅 오라클 퍼포먼스 2판 (2017년)
진단 팩을 사용하지 않는 분석
데이터베이스 서버 부하
SQL> @host_load 15
DCBIS / 2017-11-10
BEGIN_TIME DURATION DB_FG_CPU DB_BG_CPU NON_DB_CPU OS_LOAD NUM_CPU
---------- -------- --------- --------- ---------- ------- -------
16:25:29 60.03 0.04 0.00 0.23 1.08 4
16:26:29 59.04 0.07 0.00 0.11 0.44 4
16:27:28 60.04 0.05 0.00 0.09 0.88 4
16:28:28 60.04 0.05 0.00 0.09 1.28 4
16:29:28 60.03 0.06 0.00 0.10 1.07 4
16:30:28 60.04 0.05 0.00 0.09 0.46 4
16:31:28 60.04 0.07 0.00 0.26 0.58 4
16:32:28 60.04 0.08 0.00 0.09 0.68 4
16:33:28 60.04 0.05 0.00 0.11 0.61 4
16:34:28 60.03 0.06 0.00 0.12 0.57 4
16:35:28 60.03 0.05 0.00 0.09 0.63 4
16:36:28 60.04 0.06 0.00 0.26 0.70 4
16:37:29 60.04 0.07 0.00 0.10 0.80 4
16:38:29 60.04 0.07 0.00 0.11 0.78 4
16:39:29 60.04 0.06 0.00 0.11 0.72 4
SQL> @system_activity 15 20
DCBIS / 2017-11-10
Time AvgActSess Other% Queue% Net% Adm% Conf% Comm% Appl% Conc% Clust% SysIO% UsrIO% Sched% CPU%
-------- ---------- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
16:24:56 0.1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 2.2 2.2 0.0 95.7
16:25:11 0.1 0.0 0.0 0.7 0.0 0.0 8.8 0.0 8.2 0.0 13.6 0.0 0.0 68.7
16:25:26 0.0 0.0 0.0 0.0 0.0 0.0 2.0 0.0 0.0 0.0 8.0 2.0 0.0 88.0
16:25:41 0.0 0.0 0.0 0.0 0.0 0.0 2.8 0.0 16.9 0.0 8.5 0.0 0.0 71.8
16:25:56 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 2.1 2.1 0.0 95.7
16:26:11 0.1 0.0 0.0 0.0 0.0 0.0 8.5 0.7 7.2 0.0 13.7 0.0 0.0 69.9
16:26:26 0.1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 3.9 1.3 0.0 94.8
16:26:41 0.1 0.0 0.0 0.0 0.0 0.0 2.1 0.0 0.0 0.0 5.7 1.4 0.0 90.7
16:26:56 0.1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 1.2 0.0 0.0 98.8
16:27:12 0.1 0.0 0.0 0.0 0.0 0.0 6.0 0.0 6.0 0.0 9.5 0.5 0.0 78.1
16:27:27 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 3.0 0.0 0.0 97.0
16:34:16 1.9 0.0 0.0 0.1 0.0 0.0 3.8 0.1 2.9 0.0 7.2 0.6 0.0 85.2
16:34:31 0.1 0.0 0.0 0.0 0.0 0.0 1.5 0.0 0.0 0.0 5.3 0.8 0.0 92.4
16:34:46 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 10.0 0.0 0.0 90.0
16:35:01 0.1 0.0 0.0 0.0 0.0 0.0 5.1 0.0 0.0 0.0 7.4 0.5 0.0 87.0
16:35:16 0.0 0.0 0.0 0.0 0.0 0.0 1.5 0.0 18.2 0.0 9.1 0.0 0.0 71.2
16:35:31 0.1 0.0 0.0 0.0 0.0 0.0 1.7 0.0 0.0 0.0 5.2 1.7 0.0 91.3
16:35:46 0.0 0.0 0.0 0.0 0.0 0.0 2.2 0.0 24.4 0.0 4.4 0.0 0.0 68.9
16:36:01 0.1 0.0 0.0 0.0 0.0 0.0 6.8 0.0 0.0 0.0 11.7 0.0 0.0 81.5
16:36:16 0.0 0.0 0.0 0.0 0.0 0.0 3.7 0.0 0.0 0.0 11.1 1.9 0.0 83.3
SQL> @time_model 15 2
DCBIS / 2017-11-10
Time Statistic AvgActSess Activity%
-------- -------------------------------------------------- ---------- ---------
16:29:07 DB time 0.1 88.6
.DB CPU 0.1 53.8
.sql execute elapsed time 0.1 77.7
background elapsed time 0.0 11.4
DCBIS / 2017-11-10
Time Statistic AvgActSess Activity%
-------- -------------------------------------------------- ---------- ---------
16:29:23 DB time 0.1 81.6
.DB CPU 0.0 52.1
.sql execute elapsed time 0.1 79.8
background elapsed time 0.0 18.4
SQL> @active_sessions 15 1 10
DCBIS / 2017-11-10
Time #Sessions #Logins SessionId Username Program Activity%
-------- --------- ------- --------------- -------------------- ---------------- ---------
16:31:13 203 6 1502 KSAPP JDBC Thin Client 22.2
1112 KSAPP JDBC Thin Client 21.6
1212 KSDBA sqlplus.exe 16.4
816 CPRBATCH JDBC Thin Client 8.5
1201 LGWR 7.0
103 KSAPP JDBC Thin Client 6.2
Unknown 4.8
301 PSP0 3.1
1109 KSAPP JDBC Thin Client 2.9
907 CPRBATCH JDBC Thin Client 1.8
Top-10 Total 94.4
Snapper Script
SQL> @snapper ash=sql_id 15 1 all
Sampling SID all with interval 15 seconds, taking 1 snapshots...
-- Session Snapper v4.24 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
------------------------------------
ActSes %Thread | SQL_ID
------------------------------------
.05 (5%) | 968q4f33a89pd
.01 (1%) |
-- End of ASH snap 1, end=2017-11-10 16:43:53, seconds=15, samples_taken=85, AAS=.1
SQL> @snapper ash=module+action 15 1 all
Sampling SID all with interval 15 seconds, taking 1 snapshots...
-- Session Snapper v4.24 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
--------------------------------------------------------------------------
ActSes %Thread | MODULE | ACTION
--------------------------------------------------------------------------
.05 (5%) | JDBC Thin Client |
.01 (1%) | |
-- End of ASH snap 1, end=2017-11-10 16:52:52, seconds=15, samples_taken=87, AAS=.1
세션 레벨 분석
SQL>> @snapper ash=event 15 1 1414
Sampling SID 1414 with interval 15 seconds, taking 1 snapshots...
-- Session Snapper v4.24 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
--------------------------------------------------------
ActSes %Thread | EVENT
--------------------------------------------------------
.01 (1%) | db file sequential read
-- End of ASH snap 1, end=2017-11-10 16:52:02, seconds=15, samples_taken=95, AAS=0
SQL>> @snapper ash=sql_id+module+action 15 1 1414
Sampling SID 1414 with interval 15 seconds, taking 1 snapshots...
-- Session Snapper v4.24 - by Tanel Poder ( http://blog.tanelpoder.com/snapper ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)
--------------------------------------------------------------------------------------------
ActSes %Thread | SQL_ID | MODULE | ACTION
--------------------------------------------------------------------------------------------
.80 (80%) | 6gkdkq9wqg1tw | Orange for ORACLE DBA | 6.0.1 (Build:35,T)
-- End of ASH snap 1, end=2017-11-10 16:57:50, seconds=15, samples_taken=98, AAS=.8
SQL 구분 정보
SQL>> @sqlstats 6gkdkq9wqg1tw 15
DCBIS / 2017-11-10
경 과: 00:00:00.02
------------------------------------------------------------------------------------------
Interval (seconds) 15
Period 2017-11-10 16:59:01 - 2017-11-10 16:59:16
------------------------------------------------------------------------------------------
Identification
------------------------------------------------------------------------------------------
SQL Id 6gkdkq9wqg1tw
Execution Plan Hash Value 3701203732
------------------------------------------------------------------------------------------
Shared Cursors Statistics
------------------------------------------------------------------------------------------
Total Parses 1
Loads / Hard Parses 0
Invalidations 0
Cursor Size / Shared (bytes) 0
------------------------------------------------------------------------------------------
Activity by Time
------------------------------------------------------------------------------------------
Elapsed Time (seconds) 11.400
CPU Time (seconds) 1.390
Wait Time (seconds) 10.010
------------------------------------------------------------------------------------------
Activity by Waits
------------------------------------------------------------------------------------------
Application Waits (%) 0.064
Concurrency Waits (%) 0.000
Cluster Waits (%) 0.000
User I/O Waits (%) 60.928
Remaining Waits (%) 26.815
CPU (%) 12.193
------------------------------------------------------------------------------------------
Elapsed Time Breakdown
------------------------------------------------------------------------------------------
SQL Time (seconds) 11.400
PL/SQL Time (seconds) 0.000
Java Time (seconds) 0.000
------------------------------------------------------------------------------------------
Execution Statistics Total Per Execution Per Row
------------------------------------------------------------------------------------------
Elapsed Time (milliseconds) 11,400 11,400
CPU Time (milliseconds) 1,390 1,390
Executions 1 1
Buffer Gets 469,822 469,822
Disk Reads 469,863 469,863
Direct Writes 0 0
Rows 0 0
Fetches 0 0
Average Fetch Size
------------------------------------------------------------------------------------------
Other Statistics
------------------------------------------------------------------------------------------
Executions that Fetched All Rows (%) 0
Serializable Aborts 0
------------------------------------------------------------------------------------------
"데이터베이스 스터디모임" 에서 2017년에 "전문가를 위한 트러블슈팅 오라클 퍼포먼스(Second Edition)
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4344
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.