Thanks for the question, Houri.
Asked: May 30, 2010 - 7:45 am UTC
Last updated: June 01, 2010 - 9:11 am UTC
Version: 10R2
Viewed 1000+ times
You Asked
Dear Tom,
This year I have been conforonted during 3 different situations where an insert into a partitioned table /select from a remote table was taking a non acceptable time of execution. In each situation I have
1) get 10046 trace for this insert/select
2) found that 74% of the response time is on dbfile sequential read
Then I did this
3) alter the table (from where the dbfile sequential read) parallel
4) alter the index ((from where the dbfile sequential read) parallel
And this reduced drastically the insert/select execution time. This have been done 5 months ago and up to now we are still having correct execution time without any side effect.
My question is :
Would my points 3) and 4) be the points you will advise to solve high dbfile sequential read?
Thanks in advance
Mohamed
and Tom said...
I'd try to figure out why and then correct.
But I think I know why - you are now doing a direct path insert/direct path load. The db file sequential reads probably came from INDEX MAINTENANCE on the table you were loading.
When you direct path load, you will load above the segments high water mark (not reusing any existing free space). Additionally, as you load - we do not maintain the indexes row by row - we build mini indexes for the newly loaded data. At the end of the load - we take these mini-indexes and merge them in bulk with the existing indexes - so instead of doing thousands of millions of row by row index updates - we do a big one at the end - which can be (not IS, but CAN BE) more efficient.
So, you probably introduced a change that made the single block IO's against the index - which consumed most of your time "go away" in large part.
Is this answer out of date? If it is, please let us know via a Comment