|
помогите интерпретировать awr
#39273720
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Собрали тестовую БД, а она тормозит, помогите понять проблему
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.98 Redo NoWait %: 100.00
Buffer Hit %: 99.42 In-memory Sort %: 100.00
Library Hit %: 99.98 Soft Parse %: 99.97
Execute to Parse %: 89.29 Latch Hit %: 99.78
Parse CPU to Parse Elapsd %: 92.23 % Non-Parse CPU: 99.93
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tota Wait % DB
Event Waits Time Avg(ms) time Wait Class
------------------------------ ------------ ---- ------- ------ ----------
latch free 996,294 16.9 17 30.8 Other
DB CPU 8844 16.1
db file sequential read 848,142 5686 7 10.3 User I/O
enq: HW - contention 48,610 907. 19 1.6 Configurat
direct path write 173,736 112 1 .2 User I/O
direct path read temp 250,877 103. 0 .2 User I/O
direct path write temp 122,377 72.6 1 .1 User I/O
db file scattered read 5,369 15.2 3 .0 User I/O
buffer busy waits 32,584 3.9 0 .0 Concurrenc
enq: TX - index contention 285 1.2 4 .0 Concurrenc
Wait Classes by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg Avg
Total Wait Wait % DB Active
Wait Class Waits Time (sec) (ms) time Sessions
---------------- ---------------- ---------------- -------- ------ --------
Other 1,081,586 16,949 16 30.8 2.4
DB CPU 8,845 16.1 1.2
User I/O 1,403,068 5,992 4 10.9 0.8
Configuration 51,886 908 18 1.6 0.1
System I/O 1,527,174 556 0 1.0 0.1
Concurrency 53,105 8 0 .0 0.0
Application 60 1 13 .0 0.0
Commit 356 0 1 .0 0.0
Network 2,790 0 0 .0 0.0
правильно понимаю что начать стоит с latch free? тут?
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
Latch Miss Sources DB/Inst: / Snaps: 36964-36966
-> only latches with sleeps are shown
-> ordered by name, sleeps desc
NoWait Waiter
Latch Name Where Misses Sleeps Sleeps
------------------------ -------------------------- ------- ---------- --------
In memory undo latch kticmt: child 0 27 6
In memory undo latch ktiFlush: child 0 14 19
In memory undo latch ktichg: child 0 9 8
JS Sh mem access jsksGetShMemLatch 0 1 1
Result Cache: RC Latch Result Cache: Serializatio 0 1,112,562 814,156
Result Cache: RC Latch Result Cache: Serializatio 0 81,994 77,258
Result Cache: RC Latch Result Cache: Serializatio 0 79,549 185,174
Result Cache: RC Latch Result Cache: Serializatio 0 13,550 128,530
Result Cache: RC Latch Result Cache: Serializatio 0 1,399 11,513
Result Cache: RC Latch Result Cache: Serializatio 0 1,244 68,624
Result Cache: RC Latch Result Cache: Serializatio 0 242 6,254
Result Cache: RC Latch Result Cache: Serializatio 0 9 0
Result Cache: RC Latch Result Cache: Serializatio 0 4 1
SQL memory manager worka qesmmIRegisterWorkArea:2 0 1 0
cache buffers chains kcbgtcr: fast path (cr pin 0 432 143
cache buffers chains kcbget: fast exchange 0 426 19
cache buffers chains kcbchg1: mod cr pin 0 340 187
cache buffers chains kcbgtcr: fast path 0 215 1
cache buffers chains kcbzwb 0 168 119
cache buffers chains kcbgcur_2 0 82 6
cache buffers chains kcbgtcr_2 0 65 131
cache buffers chains kcbgcur_4 0 32 19
cache buffers chains kcbgcur: fast path (shr) 0 17 10
cache buffers chains kcbrls_2 0 7 209
cache buffers chains kcbesc: escalate 0 3 1
cache buffers chains kcbget: pin buffer 0 3 0
cache buffers chains kcb_is_private 0 1 24
cache buffers chains kcbget: fast path 0 1 13
cache buffers chains kcbgtcr: kslbegin excl 0 1 3
cache buffers lru chain kcbzgws 0 4 0
checkpoint queue latch kcbbwthc: thread checkpoin 0 1 1
enqueue hash chains ksqgtl3 0 9 13
enqueue hash chains ksqrcl 0 6 2
global ctx hash table la kzctxgscv: modify context 0 5 0
global ctx hash table la kzctxggcv: get context val 0 1 3
messages ksaamb: after wakeup 0 1 0
object queue header oper kcbo_switch_cq 0 2 1
object queue header oper kcbo_link_q 0 1 0
object queue header oper kcbo_unlink_q 0 1 1
redo allocation kcrfw_redo_gen: redo alloc 0 38 0
redo allocation kcrfw_post: more space 0 1 48
redo allocation kcrfw_redo_gen: redo alloc 0 1 19
row cache objects kqreqd: reget 0 16 0
row cache objects kqrpre: find obj 0 6 16
row cache objects kqreqd 0 1 7
sequence cache kdnssd 0 5 0
sequence cache kdnnxt: cached seq 0 1 0
sequence cache kdnss 0 1 8
shared pool kghalo 0 2 2
undo global data kturax 0 6 0
проблема с result cache? не найден результат в резулт кеше? частые запросы? подскажите пожалуйста как найти проблемный запрос?
пока идея одна идти по топ sql и смотреть глазами на подозрительные result_cache... |
|
|
