oracle rac数据库性能分析
目录
一、 数据库性能分析概述 3
二、 数据库运行分析 3
i. 数据库负载情况 3
ii. 实例效率 3
iii. 共享池情况 4
iv. 各种类型的数据库处理任务所占用的CPU时间 4
v. 系统中最严重的5个等待 4
vi. 等待事件分析 5
三、 SQL分析 10
i. SQL语句执行总时长排序： 10
ii. SQL语句执行CPU时长排序： 11
四、 数据库性能分析概述 15
一、 数据库性能分析概述
本分析
报告
软件系统测试报告下载sgs报告如何下载关于路面塌陷情况报告535n,sgs报告怎么下载竣工报告下载
采用oracle自带AWR分析功能采集信息后分析得出。所有数据、结论均使用oracle提供的报告采集工具采集和分析得出。
DB Name
DB Id
Instance
Inst num
Release
RAC
Host
CXSIDB
1202884396
cxsidb2
2
10.2.0.1.0
YES
cxsidb2
Snap Id
Snap time
Sessions
Cursors/Session
Begin Snap:
20419
19-Jul-10 07:57:00
124
10.9
End Snap:
20451
20-Jul-10 15:57:15
145
10.4
Elapsed:
1,920.24 (mins)
DB Time:
1,411.09 (mins)
数据库名为CXSIDB，DB ID为1202884396 数据库版本rac 10.2.0.1
分析起始时间为2010-7-19 7:57，分析截止时间为2010-7-20 15:57。整个分析时间段位1920分钟，其中数据库DB time—记录的服务器花在数据库运算(非后台进程)和等待(非空闲等待)上的时间（不包括oracle后台进程耗时）为1411分钟，负载较为理想。
二、 数据库运行分析
i. 数据库负载情况
主要检查以下三项内容：
Parses：SQL解析的次数，健康值为不大于每秒300次。
Hard parses：其中硬解析的次数，硬解析太多，
说明
关于失联党员情况说明岗位说明总经理岗位说明书会计岗位说明书行政主管岗位说明书
SQL重用率不高。健康值为不大于每秒100次。
Logons：每秒/每事务登录的次数，健康值为不大于每秒1到2次。
Per Second
Per Transaction
Parses:
40.67
9.55
Hard parses:
1.60
0.38
Logons:
0.02
0.01
数据库负载正常。
ii. 实例效率
本
表
关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf
包含了Oracle关键指标的内存命中率及其它数据库实例操作的效率。大部分检查值正常情况下应该在90%以上，其中Parse CPU to Parse Elapsd：解析实际运行时间/(解析实际运行时间+解析中等待资源时间)，越高越好。
Buffer Nowait %:
100.00
Redo NoWait %:
100.00
Buffer Hit %:
97.75
In-memory Sort %:
100.00
Library Hit %:
99.67
Soft Parse %:
96.06
Execute to Parse %:
95.21
Latch Hit %:
99.82
Parse CPU to Parse Elapsd %:
50.54
% Non-Parse CPU:
97.50
Parse CPU to Parse Elapsd值为50%，结合其他各值属于正常范围。
iii. 共享池情况
该表主要检查Memory Usage %是否稳定在75%至90%之间
Begin
End
Memory Usage %:
95.76
95.97
% SQL with executions>1:
87.67
50.04
% Memory for SQL w/exec>1:
69.87
57.93
共享池情况正常
iv. 各种类型的数据库处理任务所占用的CPU时间
Statistic Name
time (s)
% of DB Time
sql execute elapsed time
81,904.28
96.74
DB CPU
37,597.20
44.41
PL/SQL execution elapsed time
9,445.73
11.16
parse time elapsed
1,893.88
2.24
hard parse elapsed time
987.13
1.17
Java execution elapsed time
409.13
0.48
PL/SQL compilation elapsed time
202.81
0.24
sequence load elapsed time
39.17
0.05
hard parse (sharing criteria) elapsed time
33.89
0.04
repeated bind elapsed time
6.46
0.01
failed parse elapsed time
6.07
0.01
hard parse (bind mismatch) elapsed time
5.93
0.01
connection management call elapsed time
3.60
0.00
DB time
84,665.43
background elapsed time
4,098.35
background cpu time
1,814.17
v. 系统中最严重的5个等待
Event
Waits
Time(s)
Avg Wait(ms)
% Total Call Time
Wait Class
CPU time
37,597
44.4
db file sequential read
3,679,200
21,518
6
25.4
User I/O
db file scattered read
2,335,789
5,959
3
7.0
User I/O
SQL*Net more data to client
43,852,329
3,034
0
3.6
Network
db file parallel write
127,474
1,638
13
1.9
System I/O
vi. 等待事件分析
Event
Waits
%time -outs
Total Wait Time (s)
Avg wait (ms)
Waits /txn
db file sequential read
3,679,200
0.00
21,518
6
7.50
db file scattered read
2,335,789
0.00
5,959
3
4.76
SQL*Net more data to client
43,852,329
0.00
3,034
0
89.41
db file parallel write
127,474
0.00
1,638
13
0.26
lms flush message acks
33,689
0.01
983
29
0.07
gc cr multi block request
1,784,201
0.00
593
0
3.64
SQL*Net more data FROM client
88,312
0.00
488
6
0.18
log file sync
518,727
0.00
348
1
1.06
gc current grant busy
79,209
0.00
346
4
0.16
log file parallel write
627,545
0.00
324
1
1.28
db file parallel read
39,277
0.00
305
8
0.08
gc current block 2-way
824,635
0.00
290
0
1.68
gc cr grant 2-way
1,040,647
0.00
265
0
2.12
gc current block busy
1,140
0.00
91
80
0.00
gc cr block 2-way
220,374
0.00
79
0
0.45
read by other session
33,743
0.00
70
2
0.07
gc current multi block request
12,472
0.00
64
5
0.03
wait for scn ack
51,541
0.10
60
1
0.11
enq: WF - contention
871
11.71
57
65
0.00
gcs drm freeze in enter server mode
109
0.00
50
454
0.00
control file sequential read
204,532
0.00
48
0
0.42
control file parallel write
48,231
0.00
44
1
0.10
Log archive I/O
3,642
0.00
43
12
0.01
kst: async disk IO
1,992
0.00
38
19
0.00
direct path write temp
8,324
0.00
35
4
0.02
row cache lock
136,305
0.00
33
0
0.28
gc current grant 2-way
108,976
0.00
27
0
0.22
IPC send completion sync
4,925,698
99.97
25
0
10.04
gc remaster
125
0.00
25
197
0.00
gc cr block busy
16,631
0.00
23
1
0.03
name-service call wait
340
0.29
22
66
0.00
gcs log flush sync
31,964
20.69
19
1
0.07
gc buffer busy
4,755
0.02
17
4
0.01
os thread startup
345
0.00
17
49
0.00
log file sequential read
3,217
0.00
16
5
0.01
ges2 LMON to wake up LMD - mrcvr
655
90.23
12
18
0.00
library cache lock
71,530
0.00
11
0
0.15
CGS wait for IPC msg
1,143,113
99.96
11
0
2.33
direct path read
17,097
0.00
7
0
0.03
library cache pin
16,698
0.01
7
0
0.03
DFS lock handle
25,152
0.00
6
0
0.05
kjbdrmcvtq lmon drm quiesce: ping completion
93
30.11
6
64
0.00
SQL*Net message to client
10,734,591
0.00
6
0
21.89
direct path read temp
6,964
0.00
6
1
0.01
enq: US - contention
43,739
0.00
6
0
0.09
rdbms ipc reply
20,027
0.00
5
0
0.04
SQL*Net message to dblink
8,262,703
0.00
5
0
16.85
latch: shared pool
1,529
0.00
4
2
0.00
ksxr poll remote instances
396,845
57.47
4
0
0.81
gc cr grant congested
16
0.00
3
182
0.00
enq: TX - row lock contention
24
20.83
3
119
0.00
direct path write
8,459
0.00
2
0
0.02
enq: TM - contention
11,608
0.00
2
0
0.02
latch free
109
0.00
2
15
0.00
enq: CF - contention
5,567
0.02
2
0
0.01
reliable message
2,067
0.00
1
1
0.00
latch: session allocation
39
0.00
1
32
0.00
gc current split
4
0.00
1
305
0.00
SQL*Net more data from dblink
37,226
0.00
1
0
0.08
enq: PS - contention
3,571
0.00
1
0
0.01
latch: library cache
1,132
0.00
1
1
0.00
ges global resource directory to be frozen
93
100.00
1
10
0.00
gc cr block congested
2
0.00
1
449
0.00
log file switch completion
23
0.00
1
38
0.00
PX Deq: reap credit
80,055
99.34
1
0
0.16
latch: object queue header operation
426
0.00
1
2
0.00
kksfbc child completion
16
93.75
1
46
0.00
PX Deq Credit: send blkd
2,952
22.12
1
0
0.01
latch: cache buffers chains
1,787
0.00
1
0
0.00
SQL*Net break/reset to client
2,049
0.00
0
0
0.00
PX Deq: Signal ACK
2,066
30.30
0
0
0.00
enq: HW - contention
2,220
0.00
0
0
0.00
SQL*Net more data to dblink
21,767
0.00
0
0
0.04
enq: TO - contention
2,046
0.00
0
0
0.00
enq: FB - contention
1,832
0.00
0
0
0.00
enq: TX - index contention
481
0.00
0
1
0.00
buffer exterminate
1
0.00
0
276
0.00
ges LMON for send queues
7
85.71
0
39
0.00
enq: TT - contention
1,409
0.00
0
0
0.00
enq: JQ - contention
1,246
0.00
0
0
0.00
enq: SK - contention
1,069
0.00
0
0
0.00
enq: TX - contention
5
0.00
0
26
0.00
enq: SQ - contention
3
0.00
0
34
0.00
library cache load lock
7
0.00
0
12
0.00
enq: TD - KTF dump entries
533
0.00
0
0
0.00
LGWR wait for redo copy
1,583
0.00
0
0
0.00
gc cr disk read
144
0.00
0
0
0.00
log file single write
96
0.00
0
0
0.00
latch: row cache objects
6
0.00
0
6
0.00
gc current block congested
10
0.00
0
3
0.00
enq: PI - contention
216
0.00
0
0
0.00
undo segment extension
19,748
99.99
0
0
0.04
SGA: allocation forcing component growth
3
66.67
0
10
0.00
enq: JS - job run lock - synchronize
58
0.00
0
0
0.00
enq: RO - fast object reuse
182
0.00
0
0
0.00
enq: DR - contention
128
0.00
0
0
0.00
enq: MW - contention
128
0.00
0
0
0.00
enq: CU - contention
5
0.00
0
4
0.00
buffer busy waits
312
0.00
0
0
0.00
gc current block unknown
35
0.00
0
0
0.00
enq: AF - task serialization
40
0.00
0
0
0.00
latch: enqueue hash chains
1
0.00
0
14
0.00
ges inquiry response
94
0.00
0
0
0.00
latch: cache buffers lru chain
22
0.00
0
1
0.00
enq: PE - contention
69
0.00
0
0
0.00
PX qref latch
1,621
100.00
0
0
0.00
enq: MD - contention
57
0.00
0
0
0.00
enq: PG - contention
66
0.00
0
0
0.00
enq: WL - contention
48
0.00
0
0
0.00
ges LMON to get to FTDONE
1
100.00
0
6
0.00
latch: KCL gc element parent latch
39
0.00
0
0
0.00
enq: TX - allocate ITL entry
23
0.00
0
0
0.00
latch: gcs resource hash
19
0.00
0
0
0.00
SQL*Net break/reset to dblink
83
0.00
0
0
0.00
enq: TS - contention
20
0.00
0
0
0.00
enq: IR - contention
7
0.00
0
0
0.00
gc current grant congested
1
0.00
0
1
0.00
enq: TA - contention
10
0.00
0
0
0.00
latch: ges resource hash list
26
0.00
0
0
0.00
gc current retry
2
0.00
0
0
0.00
enq: UL - contention
2
0.00
0
0
0.00
enq: KM - contention
2
0.00
0
0
0.00
KJC: Wait for msg sends to complete
8
0.00
0
0
0.00
cursor: mutex X
11
0.00
0
0
0.00
lock escalate retry
3
66.67
0
0
0.00
latch: redo allocation
1
0.00
0
0
0.00
cursor: mutex S
4
0.00
0
0
0.00
buffer deadlock
2
100.00
0
0
0.00
cr request retry
1
100.00
0
0
0.00
SQL*Net message from client
10,734,570
0.00
5,323,756
496
21.89
gcs remote message
26,554,279
96.95
448,958
17
54.14
PX Idle Wait
47,209
96.28
112,477
2383
0.10
ges remote message
2,058,154
94.87
112,467
55
4.20
DIAG idle wait
575,655
0.00
112,399
195
1.17
Streams AQ: qmn slave idle wait
4,463
0.00
112,117
25121
0.01
virtual circuit status
3,840
100.00
112,100
29193
0.01
Streams AQ: qmn coordinator idle wait
8,246
50.79
112,091
13593
0.02
Streams AQ: waiting for time management or cleanup tasks
27
100.00
42,418
1571047
0.00
SQL*Net message from dblink
8,262,702
0.00
4,126
0
16.85
jobq slave wait
1,252
98.16
3,630
2899
0.00
PX Deq: Execution Msg
7,468
23.43
428
57
0.02
pipe get
4,078
1.94
386
95
0.01
class slave wait
62
100.00
303
4883
0.00
SGA: MMAN sleep for component shrink
548
86.86
5
9
0.00
PX Deq: Parse Reply
2,231
36.62
2
1
0.00
PX Deq: Join ACK
2,257
36.24
1
1
0.00
PX Deq: Execute Reply
2,527
31.66
1
0
0.01
PX Deq: Msg Fragment
3,152
24.27
0
0
0.01
single-task message
8
0.00
0
55
0.00
Streams AQ: RAC qmn coordinator idle wait
8,246
100.00
0
0
0.02
结合最系统最严重的5个等待和本等待事件表，需要分析以下几个事件：
CPU time，db file sequential read，db file scattered read，SQL*Net more data to client，db file parallel write。
CPU time：在正常的数据库中CPU time总是排在第一位。
db file sequential read：DB file sequential read等待意味着发生顺序I/O读等待（通常是单块读取到连续的内存区域中），
db file scattered read：db file scattered read等待事件是当SESSION等待multi-block I/O时发生的，通常是由于full table scans或 index fast full scans。在本系统中该等待事件属于正常。
SQL*Net more data to client：说明数据库在向客户端发送数据。在本系统中该等待事件平均等待事件不足1ms，属于正常。
db file parallel write：该等待事件属于DBWR进程，DBWR进程负责向数据文件写入脏数据块的唯一进程，即DBWR进程执行对使用SGA的所有数据库写入。如果db file parallel write平均等待时间大于10cs（或者100ms），则通常表明缓慢的I/O吞吐量。在本系统中该等待事件属于正常。
三、 SQL分析
i. SQL语句执行总时长排序：
本表不是分析单个SQL跑的时间，而是监控范围内SQL执行次数的总和时间。单位时间为秒。
总时长 = CPU Time + Wait Time
Elapsed Time (s)
CPU Time (s)
Executions
Elap per Exec (s)
% Total DB Time
SQL Id
SQL Module
SQL Text
19,102
14,577
17,572
1.09
22.56
cg1qyks5fr5vj
JDBC Thin Client
BEGIN orp_pub.p_exepro(:1, :2,...
8,923
6,351
1
8923.23
10.54
0nhzx776ahf4d
JDBC Thin Client
SELECT Ac01.Aac001, ...
6,481
138
20,518
0.32
7.66
aq99udw42j28m
SELECT AC01.AAC001, AC01.AAC0...
6,001
5,955
4,578
1.31
7.09
09m6wxqgzsu8a
JDBC Thin Client
SELECT NVL(MAX(TRANSNO), 0) F...
5,116
1,236
1
5116.45
6.04
6mcpb06rctk0x
DBMS_SCHEDULER
call dbms_space.auto_space_adv...
4,140
258
9,749
0.42
4.89
8szmwam7fysa3
DBMS_SCHEDULER
insert into wri$_adv_objspace_...
3,863
3,857
10,205
0.38
4.56
41vvtkt88p1ug
JDBC Thin Client
SELECT NVL ( MAX ( TRANSNO ), ...
3,767
3,354
384
9.81
4.45
fhxvd8mhu6ubt
JDBC Thin Client
begin PKG_A_ZJGL.P_DZDEPT_XML(...
3,583
3,583
78,391,424
0.00
4.23
dhxujg235vvnk
JDBC Thin Client
SELECT AAE001 FROM AA89 WHERE ...
3,533
3,460
871
4.06
4.17
06v7956fw4c8t
JDBC Thin Client
begin PKG_A_GRGL.P_RYCB_PAUSE_...
2,862
2,846
3,913
0.73
3.38
8gw50vw3q3dtp
JDBC Thin Client
UPDATE AC32 SET BAC061 = :B6 ,...
2,802
2,796
3,695
0.76
3.31
1xuxmr1kagbzc
JDBC Thin Client
SELECT NVL(MAX(TRANSNO), 0) F...
2,640
2,639
86
30.70
3.12
2855md41a1jka
JDBC Thin Client
INSERT INTO Ac13 SELECT BAB221...
2,543
1,656
13
195.62
3.00
gd52v544fu0my
PL/SQL Developer
begin -- Call the procedure ...
2,535
2,527
532
4.76
2.99
86ap567urphna
JDBC Thin Client
UPDATE AC13 SET AAE114 = :B8 ,...
2,059
338
67
30.73
2.43
144kcsb19q9cn
JDBC Thin Client
begin PKG_A_ZJGL.P_HZ_XML(:1);...
2,011
401
1
2010.70
2.37
b6usrg82hwsa3
DBMS_SCHEDULER
call dbms_stats.gather_databas...
1,996
1,503
289,496
0.01
2.36
bnmjghywbjw7f
MCSVR@cxsi-tuxedo-2 (TNS V1-V3)
begin pkg_k_expcalc_comm . prc...
1,786
1,760
66
27.07
2.11
54uv91tm1zup0
JDBC Thin Client
begin PKG_A_ZJGL.P_BSCL_XML_NE...
1,779
1,237
6
296.45
2.10
7j4h51j761bwy
PL/SQL Developer
INSERT INTO YLGH_IC13IC16_MX (...
1,596
1,445
285
5.60
1.89
gxqy9yz6jdav2
JDBC Thin Client
begin PKG_A_GRGL.P_RYCB_CONTIN...
1,326
293
67
19.79
1.57
6qsx3f686s9yy
JDBC Thin Client
UPDATE AB13 A SET A.BAB206 = '...
938
929
12
78.21
1.11
4ah11jtdyc5yc
JDBC Thin Client
begin PKG_A_ZJGL.P_A_TSCL_2_XM...
927
881
912
1.02
1.10
ftgjvwgyjchzv
JDBC Thin Client
UPDATE AC01 SET BAC084 = :B2 W...
911
902
1,585
0.57
1.08
b8k6u185695d4
JDBC Thin Client
UPDATE AC32 SET AAE100 = :B4 W...
878
854
9,749
0.09
1.04
cfxfxjk00tq0m
DBMS_SCHEDULER
SELECT TASKS.TASK_ID, REC.ID ...
浙公网安备 33021202002483