| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Export of IOT very slow
Rick,
A "sequential" read is limited to single block i/o. This can be i/o against either an index or a table. A scattered read is multiblock i/o on either a table or index. I really wish Oracle would rename these events to more accurately indicate what action they are performing, not what they were limited to in Oracle7.
Anyway...just to add lines to avoid the dreaded Quoting Rejection email (makes me feel so ashamed...)
If you look at the waits in the IOT, you will see that they are reading blocks that are not next to each other, which I presume is due to it reading the data in logically sorted order. The process reads file 15 block 506603, then file 17 block 1063439, then file 16 block 39872. From this, I am deducing that this IOT has undergone some major growth which results in a mix of branch/leaf blocks in extents and the extents are even spread over different data files. So 3 blocks that hold logically sequential records are actually in extents that are physically separated.
If you examine the heap reads, you see that the 3 reads are for contigious blocks. Read 1 is file 27 blocks 32645 - 32660 (16 blocks), read 2 is file 27 blocks 32661 - 32676 (again 16 blocks) and read 3 is file 27 blocks 32677 - 32692. Since we don't really care about the logical order of the data, we can just scan all the blocks in physical order. I would venture to guess you might see a sequential read or two for the table, if there would be a single block remaining at the end of an extent (block 17 or block 33...)
Daniel
Rick Stephenson wrote:
> Why does an IOT only allow for 1 block read at a time to extract data during
> an export. I have an IOT with 250 Million rows that takes 24 hours to
> export, whereas, I have a heap table with 12 million rows that takes 6
> minutes to export. If I extrapolate that out, the heap table would take
> roughly 2 hours to finish with 250 million rows. I decided to trace the
> processes and noticed that the IOT export will only read 1 block at a time,
> whereas the heap reads in 16 blocks at a time. Is there a way to force the
> "sequential" read to grab 16 blocks at a time?
>
>
> I am running EE 9.2.0.3 on Solaris 2.8.
>
>
>
> Trace file for IOT:
>
> WAIT #0: nam='db file sequential read' ela= 5828 p1=15 p2=509603 p3=1
>
> WAIT #0: nam='db file sequential read' ela= 16070 p1=17 p2=1063439 p3=1
>
> WAIT #0: nam='db file sequential read' ela= 13220 p1=16 p2=39872 p3=1
>
>
>
> Trace file for Heap:
>
> WAIT #3: nam='db file scattered read' ela= 1810 p1=27 p2=32645 p3=16
>
> WAIT #3: nam='db file scattered read' ela= 1648 p1=27 p2=32661 p3=16
>
> WAIT #3: nam='db file scattered read' ela= 1866 p1=27 p2=32677 p3=16
>
>
>
> Thanks for your help,
>
>
>
> Rick Stephenson
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed May 26 2004 - 16:57:34 CDT
 |
 |