Добрый день.
При полном сканировании больших таблиц, когда выполняется непосредственно select ..., или при выполнении create table ... as select ... производится чтение, минуя буферный кеш, с событиями direct path read, а при выполнении insert ... select ... - чтение через буферный кеш с событиями db file scattered read. Не могу понять в чём причина, и как заставить insert ... select ... работать через direct path read.
Версия Oracle - 11.2.0.4.0.
Пример запросов:
1.
2.
3.
4.
5.
6.
create table xxvip_gl_je_lines_copy
as select * from gl_je_lines where rownum <= 20000000; --direct path read
alter system flush buffer_cache;
insert
into xxvip_gl_je_lines_copy
select * from gl_je_lines where rownum <= 20000000; -- db file scattered read
Выписка из разбора трассировки:
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.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
create table xxvip_gl_je_
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 0 0 0 0
Execute 1 98.52 251.77 371990 431933 2210392 20000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 98.56 251.80 371990 431933 2210392 20000000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD AS SELECT (cr=431947 pr=371990 pw=491461 time=251760085 us)
20000000 20000000 20000000 COUNT STOPKEY (cr=372121 pr=371990 pw=0 time=43412509 us)
20000000 20000000 20000000 TABLE ACCESS FULL GL_JE_LINES (cr=372121 pr=371990 pw=0 time=37033756 us cost=102428 size=3340000000 card=20000000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 44 0.00 0.00
direct path read 1643 0.74 33.72
direct path write 3478 2.76 113.67
latch: row cache objects 29 0.00 0.00
latch: redo allocation 1 0.00 0.00
buffer busy waits 1 0.00 0.00
log file switch (private strand flush incomplete)
1 0.21 0.21
log file sync 1 0.06 0.06
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.02 0.02
********************************************************************************
...
insert into xxvip_gl_je_lines_copy
select * from gl_je_lines where rownum <= 20000000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 537.49 1248.19 371594 3440160 28087631 20000000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 537.50 1248.20 371594 3440160 28087631 20000000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL (cr=3440180 pr=371594 pw=0 time=1248197759 us)
20000000 20000000 20000000 COUNT STOPKEY (cr=373987 pr=371594 pw=0 time=942086927 us)
20000000 20000000 20000000 TABLE ACCESS FULL GL_JE_LINES (cr=373987 pr=371594 pw=0 time=930713853 us cost=2651991 size=86478925960 card=517837880)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 23336 4.32 690.90
log file switch (private strand flush incomplete)
71 1.36 4.99
log buffer space 40 2.12 14.04
log file switch completion 134 1.08 6.43
db file sequential read 1 0.00 0.00
latch: row cache objects 13 0.00 0.00
latch: cache buffers chains 1 0.00 0.00
latch: redo allocation 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
На всякий случай значения параметров:
_serial_direct_read = auto
_direct_read_decision_statistics_driven = TRUE
_small_table_threshold = 12207 (Количество блоков в таблице - более 5 млн.)
