Re: Direct load mystery
Date: Wed, 1 Sep 2010 10:20:00 -0700 (PDT)
Message-ID: <4df8668f-ed1d-4040-b567-7a7a61926ead_at_z34g2000pro.googlegroups.com>
On Aug 31, 3: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
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
-- _at_home.com is bogus. http://www.signonsandiego.com/news/2010/sep/01/sdge-unveils-fire-weather-website/Received on Wed Sep 01 2010 - 12:20:00 CDT