Re: Difference between build index and gather stat

xxgeorge_at_gmail.com wrote:
> hi all,
>
>
> I'm not very clear about the concept of build-index and gather-stat.
> Grateful if you can give me some idea.
>
> I have a table [user] where [user_id] is the primary key. Initially
> the table is empty. An application will read from a text file and load
> about 100000 rows of user info into the table. Right after that the
> application will start a process which scan through the 100000 users,
> do calculations and update some status flags row by row.

Of course, most performance problems are in application code.

>
> The update process, which involves only straight-forward update
> statements (UPDATE user SET status = ? WHERE user_id = ?) is extremely
> slow. It is found that the Optimizer opts for full-table-scan instead
> of using the primary key for the updates. If I manually gather
> statistics for the tables after loading the data and before running the
> calculations the performance is much much better (trimmed from several
> hours to several seconds).
>
>
> I'd assume:
>
> 1. with the primary key defined, whenever a row is being inserted, a
> index-tree is built somewhere;

Not necessarily, see the utilities manual for imp on one way to bring in data delaying the index to later,
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6407993912330 for what to do when you are adding 100000 rows per day.

> 2. the index-tree is always there, but the Optimizer may not use it
> (depends on cost)

True, but...

you can give hints, which the Optimizer still may not use, or use plan stability if you know ahead of time exactly what is and will always be the best way.

> 3. gathering stat 'tells' the Optimizer to use the primary key
>
>
> If that's the case, next time I bulk-load a new batch of data, is it
> right to assume the index already there (1), and the Optimizer will use
> the primary key (2)? Does it mean that I only need to gather stat once
> to teach the Optimizer? Or should I gather stat again or re-build the
> index? What's the difference and the mechanism behind?

No. You generally don't have to rebuild indices unless you do something to make them invalid (like nologging loads), or some types of massive deletes. The optimizer can make much better decisions with statistics that accurately reflect the data. If your data isn't normally distributed simply collecting statistics may not be the most accurate reflection. So, it depends, and I would recommend reading the concepts and performance tuning guides, and googling for information like "under the hood of the Oracle optimizer," then going through Tom Kyte's books for how to make your app code good, and Jonathan Lewis' books and posts for how the Optimizer really works.

Somewhere near the beginning of that you should figure out how to run explain plan, too. (With proper options OEM can show you that, but better you figure it out from SQL*Plus). Keep in mind, explain plan doesn't necessarily show what the Optimizer will do (whereas a trace will), but it is a good start.

>
>
> Sorry if I didn't make it clear. Thanks in advance
> george

Please remember to post version and platform, too. 10g, for example, has some interesting features for dynamic tuning. See http://dbaoracle.net/readme-cdos.htm for posting instructions and other resources.

Generally to get specific help here you need to ask specific questions.  Complete runnable code samples and explain plans get some people excited to help.

jg

--
@home.com is bogus.
"survivorship bias"
http://www.signonsandiego.com/uniontrib/20060416/news_1b16lynn.html