Antwort: Re: Index build order
- From: Martin Klier <Martin.Klier@xxxxxxxxxx>
- To: yong321@xxxxxxxxx
- Date: Wed, 28 Jan 2009 18:18:28 +0100
Hi Yong Huan,
thanks a lot for the explanation, I will have to do tests on that, the
parse-while-rebuild stuff sounds very feasible. I think I will create a
small procedure commenting all tables in the DB to force a general re-parse
after gather_schema_stats. That's a great hint!
Last but not least, thanks a lot to all the other posters as well, all of
your opinions will help me in furture!
--
Mit freundlichem Gruß
Martin Klier
------------------------------------------------------------------------------
Klug GmbH integrierte Systeme
Lindenweg 13, D-92552 Teunz
Tel.: +49 9671/9216-245
Fax.: +49 9671/9216-112
mailto: martin.klier@xxxxxxxxxx
www.klug-is.de
------------------------------------------------------------------------------
Geschäftsführer: Johann Klug, Roman Sorgenfrei
Sitz der Gesellschaft: Teunz, USt-ID-Nr. DE175481608,
HRB Nr. 2037, Amtsgericht Amberg
> Now, let's forget about RBO and back to modern versions. You may consider
the
> timing issue with dbms_stats.gather_(schema|table)_stats
> (...,cascade=>true). If a big
> table has multiple big indexes, in a moderately busy OLTP environment,
think
> about the CBO's choice of which index is used when new SQLs are parsed
before
> the gather stats job is completely finished. It may have great impact
because
> the first parse fixes the plan for later executions due to bind peeking.
I
> can't conclude on what the order is in cascaded gathering of index stats
(if
> not random, has to be one of name, object_id, data_object_id, or
> creation time,
> but I can't reproduce). With this in mind, you may as well set
> no_invalidate to
> true and after the gather stats job is done, force reparsing with a
harmless
> DDL (grant, comment, etc.) on the base table.
>
> Similarly, when you build or rebuild indexes, choose the order sensibly
while
> new SQLs are parsed. Again, this has nothing to do with RBO's Rule
> #3 in Mark's
> summary; it's just what CBO sees at the moment of parsing. If
> needed, turn off
> _optimizer_compute_index_stats so you can separate the two tasks,
> (re)building
> indexes and providing stats for CBO.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Antwort: Re: Index build order - Martin Klier
