Re: Export of IOT very slow
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 26 May 2004 15:59:00 -0600
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
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
