Re: why does CBO not use available indices

"Anke Heinrich" <anke.heinrich_at_marconi.com> schrieb im Newsbeitrag news:9042145d.0310060903.4ed4e354_at_posting.google.com...
> Hi,
>
> I have a problem getting the cost based optimizer to use the available
> indices instead of performing a full table scan. Using hints is not
> possible as I can't change the source code at the moment. I've heard
> about outlines, but I'm not sure if this works for us.

> table statistics:
> have been calculated using
> analyze table TP compute statistics
> for all indexes for all indexed columns;
> analyze table ETP compute statistics
> for all indexes for all indexed columns;
>
> optimizer parameter:
> optimizer_index_cost_adj=10
> optimizer_max_permutations=1000
> performance difference is 0.04 with good old plan, 0.46 with new one
> :-(
>
> Is there a way to force the optimizer to use a specific plan for
> dynamically generated statements without changing source code?
>
> Are there any other options left?

Oracle strongly recommends that you use the statspack to analyze the tables. So, I'd read a bit in http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_stats.htm#ARPLS059 and http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#50788 and then execute as sys something like

• dbms_stats.alter_schema_table_monitoring(ownname=>'Schemaname',monitoring=>true);
• dbms_stats.gather_table_stats(ownname=>...,tabname=>'TP',method_opt=>'for all columns size auto',granularity=>'ALL',cascade=>true);

, ditto for ETP
and see what the optimizer does then.

And, btw, there's also dbms_stats.gather_system_stats which, when called under realistic load, tells the optimizer the true cost of i/o and cpu operations which is much better than manually setting the optimizer parameters. Maybe you should call that one before you test the query.

On my little database (7GB total) I run

• dbms_stats.alter_database_table_monitoring(monitoring=true,sysobjs=>false),
• dbms_stats.gather_database_stats(options=>'GATHER AUTO') in a dbms_job about every ten minutes and
• dbms_stats.gather_system_stats(gathering_mode=>'INTERVAL',interval=>5) in another job also about every ten minutes.

The first time I called dbms_stats.gather_database_stats it took almost 2 hours and created rather heavy load but now it's typically just two or three seconds. It brought a noticable improvement, but our starting point was pretty low as I had not done any optimization before.

Btw, does anybody know when to use this flush_database_monitoring_info procedure?

Lots of Greetings!
Volker Received on Mon Oct 06 2003 - 14:13:34 CDT