Re: Direct load mystery
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Wed, 01 Sep 2010 08:28:44 +0200
Message-ID: <8e6a5aFq08U1_at_mid.individual.net>
On 01.09.2010 00:31, Mladen Gogala wrote:
> 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?
Date: Wed, 01 Sep 2010 08:28:44 +0200
Message-ID: <8e6a5aFq08U1_at_mid.individual.net>
On 01.09.2010 00:31, Mladen Gogala wrote:
> 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?
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/Received on Wed Sep 01 2010 - 01:28:44 CDT