Execution Plan on CBO vs Rule

[ericfjchen]

This is Oracle 9.0.1.4.
The DB optimizer_mode is choose. We analyze all database every week. However, some SQL statement execution time is too long. If we add a hint '/*+ rule */', the execution plan is also changed. Then its execution time became faster than CBO. What's going on?

Thanks

Eric

[Eric Antunes]

I think you should only use Choose for some queries but not for the entire database. Cost or Rule are better for the DB optimizer_mode. To use Cost you must compute or estimate statistics...

[ericfjchen]

Our DB optimizer_mode is choose. Oracle said CBO is better than rule base. The whole databse is analyzed every week. We also check the table's statistic as below,
-------------
select owner,table_name,to_char(LAST_ANALYZED,'YYMMDD')
from dba_table
--------------
The result is expected. We reiterate many queries/DML are fast based on CBO. However, few queries are slow against CBO. This is a big database with 180 GB and it works over 2 years. Can someone share your experience on this?

Thanks

Eric

[Jean-Luc Oudart]

Eric,

Sometimes the CBO method is not the best !
Saying that ususally it's better than the old RBO (desupported after 9i ?)

I some case we have to change oracle behaviour either by changeing init.ora parameters or adding SQL hints in the code or changing the way the data is loaded into a table (Physical parameters). It's all about tuning !
CBO makes your life easier but it 's not the ultimate performance master !
This means that DBA have still some work to do.

see attachment

Could you tell us how you update the statistics ?

Regards,
Jean-Luc

[Jean-Luc Oudart]

Sorry

forget the attachment


Regards,
Jean-Luc

[Jean-Luc Oudart]

this document too

Regards
Jean-Luc

[Eric Antunes]

In my DB I use Rule and only Rule and I have no query taking more than 5 minutes! Ok, it's just a 17 GB database but it started with 12 GB and there is no difference on the performance.

On init.ora have you setted the following parameters?

timed_statistics = true
optimizer_mode = cost

[Fred Ruffet]

CBO is better in most case. It aims at finding the execution plan for a query. But sometimes it has no success. It's rare and becoming more rare every version.

You have many solutions to correct such a problem :
Modify the query
Add hints
Open a TAR

As long as the two first may be unsufficent (or can not be done), you may have to contact Oracle support. Note that this third solution may be long.

Apparently you can modify queries so you can do it before using this hint.

Note that RBO will effectivly be desupported after 9i, and may really disappear in future releases.

Regards,

Fred

[Eric Antunes]

Hi Eric,

You have just assigned points to 8 of 46 responses... :-(

We need points to survive. :-)

[Brian Crabtree]

Eric,

You might also want to look at Histograms. I have heard that they work well, however I have not had the available time to look into them more.

Thanks,

Brian

[Volker Borowski]

Hmmm,

having statistics on the table alone rarely has an effect on which index is chosen.
Do you run statistics on the indexes as well ?

Let's see the script you use to analyze.
Do you log the result of the analyze ?
In case of a big DB, an analyze of a big table may fail (for rollback/undo or temp space requirements) resulting in no statistices being stored, although you started a job to collect them.
Check you analysis for errorcodes.

Hope this helps
Volker

[ericfjchen]

Hi All,

The analyze method we did is "exec dbms_stats.gather_schema_stats ( ownname => 'SCOTT', estimate_percent => 99, options => 'GATHER', granularity => 'ALL', cascade => TRUE); ".
We do that for all schemas every week. The result is successful. Therefore, we believe the statistics for all objects are correct against CBO. However, some queries need to addon hint. Have any idea?

Thanks

Eric

[Indira Aramandla]

Hi Eric,

The CBO determines the best plan by calculating an estimated COST for various execution plans and uses the plan with the lowest cost. To give CBO the most information (and the best chance of choosing a good execution plan) you should ANALYZE all tables to be queried. CBO works well for ad-hoc queries. For hard coded, repeated SQL statements, these should be tuned to obtain a repeatable optimal plan.

Any hint, except RULE, causes CBO to be used. It is very important to note that a HINT cannot be 'turned off' by any parameter settings.

As the optimzer evaulates the cost of each plan it compares it with the best cost plan seen so far. It keeps doing that until it gets to the best from the range of plans it considers. If the best plan selected is not the "ideal" plan based on the users knowledge of the data, the user can force a different plan by using "Hints".

In summary to make effective use of the CBO you should:

- Analyze all tables regularly.

- Set the required OPTIMIZER_GOAL (FIRST_ROWS or ALL_ROWS).

- Use hints to help direct the CBO where required.

- Use hints in PL/SQL to ensure the expected optimizer is used.

- Be careful with the use of bind variables.



Indira A

[Aniruddha Mitra · Accepted solution]

We had a similar problem with 9i release 1. The cbo was working well with 90% of the queries, but it was screwing up with the rest 10% queries. Oracle could not explain this, ans instead recommended me to move to 9i release 2. To be honest it works. We only have 0.2% runaway queries. Also as it was a data warehouse, setting optimizer goal to ALL_ROWS helped. Try computing stats for the tables instead of estimating, and in 3 occations I saw huge difference in execution plans. We are on 9.2.0.5, and it runs great.
Mitra