HI All !
I have a case where I export a table (1.5 million rows with 10% migrated rows) having one lob columns (DISABLE STORAGE IN ROW RETENTION AUTO KEEP_DUPLICATES NOCOMPRESS)
that are frequently updated.
Exporting this table takes 10 hours, I then import it to another db (same server,disk and $OH)
If I export from this database the export takes 1 hour
I have traced the slow and fast execution and have the following figures:
The slow execution
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 40 0.00 0.07
direct path read 4230298 4.08 23974.16
db file sequential read 818488 0.63 6095.52
resmgr:internal state change 1 0.10 0.10
latch: object queue header operation 2 0.00 0.00
latch free 1 0.00 0.00
********************************************************************************
The good execution
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.01 0 0 0 0
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
reliable message 1 0.00 0.00
enq: KO - fast object checkpoint 1 0.00 0.00
Disk file operations I/O 1 0.00 0.00
direct path read 1864314 0.16 1796.70
latch: row cache objects 2 0.00 0.00
********************************************************************************
Max wait in the slow case i 4 seconds while in the fast case is 0.16 seconds.
I don't have the histogram so I don't know how many that takes a long time.
We have many more direct path read calls in the bad execution, it is 2.26 times more calls in the bad case.
COUNT(1) COUNT(1)
---------- ----------
4230294 1864313
This is how the direct path read are spread
OBJNO COUNT(OBJNO) OBJNO COUNT(OBJNO)
---------- ------------ ---------- ------------
156424 603 175034 25
156425 4229691 175035 1864287
The objno points to the lob segment for the heavily updated lob column.
It is also interesting to see that so many direct path reads just reads 1,2 or 3 blocks
while in the good execution it is much more spread.
CNT COUNT(CNT) CNT COUNT(CNT)
---------- ---------- ---------- ----------
1 2926040 1 93047
2 978134 2 119728
3 267652 3 159944
4 40154 4 120821
5 11818 5 250262
6 3957 6 84474
7 1199 7 210958
8 466 8 56487
9 156 9 75359
10 79 10 269712
11 17 11 142413
12 3 12 23959
I have a case with Oracle support and they claim it is all related to the migrated rows
I wonder if that is really true, 10% migrated rows causing so much more direct path reads
against the lob segment. I expect some overhead of the migrated rows but a factor of 10 doing export
does not sound correct to me.
Also, if I understand how lob segments are handled, traditional row migration will not occur.
I'm aware of several bugs in this area, but we run 11.2.0.4 were most of these bugs should be fixed.
My question is, is it likely that 10% migrated rows of the base table can generate so much over head
that the export takes 10 hours instead of 1 ?
If so can anyone explain what is going on in the background ?
If not, what can cause this behavior ?
Appreciate your feedback
Thanks
Magnus