Re: Direct load mystery
From: ddf <oratune_at_msn.com>
Date: Wed, 1 Sep 2010 00:56:45 -0700 (PDT)
Message-ID: <43c5cffa-facf-4c9c-b06f-088903a4170c_at_v35g2000prn.googlegroups.com>
On Aug 31, 6:31 pm, Mladen Gogala <n..._at_email.here.invalid> 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?
>
> --http://mgogala.byethost5.com
Date: Wed, 1 Sep 2010 00:56:45 -0700 (PDT)
Message-ID: <43c5cffa-facf-4c9c-b06f-088903a4170c_at_v35g2000prn.googlegroups.com>
On Aug 31, 6:31 pm, Mladen Gogala <n..._at_email.here.invalid> 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?
>
> --http://mgogala.byethost5.com
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/B19306_01/server.102/b14215/ldr_modes.htm#i1008815
David Fitzjarrell Received on Wed Sep 01 2010 - 02:56:45 CDT