A colleague of mine contacted me with the following issue. She's loading
data, large quantities of it, into a 64-bit Oracle 10.2.0.5 on SUN
Slowaris (should I say Oracle Slowaris?). She noticed that there is a
significant increase in performance if the transit tables she's loading
the data into are analyzed after they're truncated, immediately before
the load. She's loading using the direct=true option to sqlldr.
Now, my problem with the situation is the following: direct load should
bypass the SQL layer, which also includes the optimizer. There shouldn't
be anything to optimize here. The client process pre-formats blocks in
PGA and passes them to Oracle, which puts them into the right place.
Statistics should play no part here, yet it apparently does. I have no
access to the machine in question and cannot check the situation. The
first thought was that the "direct" argument is being ignored because of
a trigger or non-unique index used to enforce uniqueness or something
else that usually breaks BULK load in the PL/SQL. But that shouldn't
prevent SQL*Loader from doing a direct load, should it? Any thoughts?



--
http://mgogala.byethost5.com

On 01.09.2010 00:31, Mladen Gogala wrote:
What about segment information? Maybe that is updated as part of the
analyze, as side effect of the analyze or just happens to happen at the
same time.

No idea, whether you could clarify with a trace since - as you said -
direct load should bypass most of SQL layers. But then again, if it
doesn't, i.e. you're not doing direct load, you might likely see in
traces. :-)

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On Aug 31, 6:31*pm, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:
Direct-path loads get segment information from the database and,
apparently, that information comes partly from table statistics.
Analyzing the empty table replaces the old information with new, and
the direct path API puts those statistics to good use. This is shown
in the diagram here:

http://download.oracle.com/docs/cd/B...s.htm#i1008815


David Fitzjarrell

On Aug 31, 3:31*pm, Mladen Gogala <n... (AT) email (DOT) here.invalid> wrote:
Is she doing an analyze as part of the load, or separately later?
Maybe there's something odd like a large number of histogram buckets
that need to be dealt with and makes the time of the analyze longer if
the truncate doesn't pre-wipe them. (pure speculation here) Don't
quite know enough to figure if delayed block cleanout could result.

Even further out speculation: I noticed a note on MOS having to do
with best practices for containers, which mentions that if sga_target
and sga_max_size are the same size, that ISM will be used, but if
sga_max_size is larger, DISM is used. So maybe something is screwing
with sga resizing that upsets DISM or ISM. Been way too long since
I've dealt with Solaris, but I was burned once in the last century by
import performance and ISM issues.

More speculation: Something that analyzing does knocks off something
else that is disallowing exclusive read access to the index, which
something else is dropping it into a conventional load. Maybe a
previous load or analyze isn't finished, or some other process is
still hanging around. Or a bug with parallel loading...

Which truncation qualifiers?

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...ather-website/

Mladen:

At my shop we delete table stats with cascade set after every truncate
table.

The truncate table leaves around the old stats.

I have also seen issues from time to time with tables that were
analyzed empty and at my shop we are using dynamic sampling for
truncated tables until at some future point actual data is out there.