该系统特征为 执行解析比极低 0.32 ,问题出现时 表现为大量解析等待
vent Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 4,218 30.8
latch: shared pool 17,135 1,925 112 14.0 Concurrency
latch: library cache 17,984 1,614 90 11.8 Concurrency
cursor: pin S wait on X 71,119 721 10 5.3 Concurrency
latch free 1,288 168 130 1.2 Other
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
latch free 198,917 57,588 290 79.4 Other
latch: library cache 150,216 41,739 278 57.5 Concurrency
latch: session allocation 119,104 34,834 292 48.0 Other
latch: shared pool 103,678 28,946 279 39.9 Concurrency
CPU time 3,476 4.8
硬解析不高大约为每秒4次
SGA breakdown difference
ordered by Pool, Name
N/A value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot
Pool Name Begin MB End MB % Diff
java free memory 26.30 26.30 0.00
java joxlod exec hp 5.52 5.52 0.00
large PX msg pool 1.03 1.03 0.00
large free memory 14.97 14.97 0.00
shared CCursor 1,351.69 1,328.34 -1.73
shared Cursor Stats 130.80 130.80 0.00
shared PCursor 911.14 895.63 -1.70
shared free memory 749.07 807.65 7.82
shared kglsim heap 124.88 124.88 0.00
shared kglsim object batch 169.13 169.13 0.00
shared library cache 682.37 668.86 -1.98
shared sql area 5,473.85 5,465.33 -0.16
buffer_cache 6,512.00 6,512.00 0.00
fixed_sga 2.02 2.02 0.00
log_buffer 13.98 13.98 0.00
sql area达到了5g