Direct load mystery
From: Mladen Gogala <no_at_email.here.invalid>
Date: Tue, 31 Aug 2010 22:31:05 +0000 (UTC)
Message-ID: <pan.2010.08.31.22.31.05_at_email.here.invalid>
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: Tue, 31 Aug 2010 22:31:05 +0000 (UTC)
Message-ID: <pan.2010.08.31.22.31.05_at_email.here.invalid>
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.comReceived on Tue Aug 31 2010 - 17:31:05 CDT