есть запрос:
1.
2.
3.
4.
SELECT D_ID
FROM
cc_table CC WHERE CC.CODE = :B3 AND CC.DIC_CODE = :B2 AND :B1 BETWEEN CC.DAT1
AND CC.DAT2 AND NVL(CC.IS_NOT_UNIC, 0) = 0 AND ROWNUM = 1
1.большое количество parse - 1420 для запроса, правда при этом cpu и elapsed на парс чрезвычайно малы.
2. большое значение execute для запроса, 390334 раз...
возможно главное и не увидел (подозреваю, что так и есть)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1420 0.07 0.06 0 0 0 0
Execute 390334 75.55 76.48 0 0 0 0
Fetch 390334 13.32 13.10 54 1555367 0 384365
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 782088 88.95 89.65 54 1555367 0 384365
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87 (recursive depth: 2)
как это можно оптимизировать? снизить количестве executions?
меня смущают: OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
1.
Execute 867198 209.63 258.85 6107 178403 407931 30173
в трейсе запросов +100500, но
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.
58.
59.
60.
61.
62.
63.
64.
65.
66.
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 54 0.81 0.89 0 153 2 0
Execute 41 179.88 197.89 12225 191192 42291 15945
Fetch 97 0.09 0.09 1 4083 0 556
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 192 180.78 198.87 12226 195428 42293 16501
Misses in library cache during parse: 24
Misses in library cache during execute: 5
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 1677 0.11 13.24
SQL*Net message to client 149 0.00 0.00
SQL*Net message from client 149 189.32 204.86
log file sync 6 0.00 0.00
SQL*Net more data to client 39 0.00 0.00
SQL*Net more data from client 2 0.00 0.00
Disk file operations I/O 30 0.02 0.07
latch: row cache objects 16 0.00 0.01
latch: shared pool 5 1.08 1.16
latch: cache buffers chains 2 0.00 0.00
db file scattered read 1343 0.02 2.25
library cache: mutex X 2 0.00 0.00
latch: redo allocation 2 0.00 0.00
SQL*Net break/reset to client 2 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 75903 2.99 3.61 0 20 44 0
Execute 867198 209.63 258.85 6107 178403 407931 30173
Fetch 825081 123.52 136.42 1662 4935026 2448 808175
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1768182 336.15 398.89 7769 5113449 410423 838348
Misses in library cache during parse: 159
Misses in library cache during execute: 141
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 7769 0.18 59.18
Disk file operations I/O 4 0.03 0.03
latch: row cache objects 38 0.00 0.00
cursor: pin S 8 0.01 0.02
latch: shared pool 9 0.25 0.33
latch: cache buffers chains 3 0.00 0.00
library cache: mutex X 23 0.01 0.01
enq: SQ - contention 10 0.00 0.00
buffer busy waits 25 0.00 0.00
row cache lock 6 0.00 0.00
read by other session 2 0.00 0.00
enq: TX - index contention 13 0.02 0.06
latch: cache buffers lru chain 2 0.00 0.00
log file switch (private strand flush incomplete)
1 0.13 0.13
latch: redo allocation 1 0.00 0.00
latch free 1 0.00 0.00
latch: In memory undo latch 1 0.00 0.00
