topic Re: Execution Plan on CBO vs Rule in Operating System - HP-UX

Execution Plan on CBO vs Rule

ericfjchen — Mon, 06 Sep 2004 22:00:20 GMT

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

Re: Execution Plan on CBO vs Rule

Eric Antunes — Tue, 07 Sep 2004 03:04:02 GMT

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...

Re: Execution Plan on CBO vs Rule

ericfjchen — Tue, 07 Sep 2004 03:18:19 GMT

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

Re: Execution Plan on CBO vs Rule

Jean-Luc Oudart — Tue, 07 Sep 2004 03:29:05 GMT

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

Re: Execution Plan on CBO vs Rule

Jean-Luc Oudart — Tue, 07 Sep 2004 03:30:25 GMT

Sorry

forget the attachment

Regards,
Jean-Luc

Re: Execution Plan on CBO vs Rule

Jean-Luc Oudart — Tue, 07 Sep 2004 03:31:06 GMT

this document too

Regards
Jean-Luc

Re: Execution Plan on CBO vs Rule

Eric Antunes — Tue, 07 Sep 2004 03:38:32 GMT

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

Re: Execution Plan on CBO vs Rule

Fred Ruffet — Tue, 07 Sep 2004 03:39:31 GMT

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

Re: Execution Plan on CBO vs Rule

Eric Antunes — Tue, 07 Sep 2004 06:31:38 GMT

Hi Eric,

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

We need points to survive. :-)

Re: Execution Plan on CBO vs Rule

Brian Crabtree — Mon, 16 Sep 2024 09:08:34 GMT

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

Re: Execution Plan on CBO vs Rule

Volker Borowski — Tue, 07 Sep 2004 12:20:48 GMT

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

Re: Execution Plan on CBO vs Rule

ericfjchen — Thu, 09 Sep 2004 22:53:31 GMT

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

Re: Execution Plan on CBO vs Rule

Indira Aramandla — Thu, 09 Sep 2004 23:43:26 GMT

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

Re: Execution Plan on CBO vs Rule

Aniruddha Mitra — Sat, 11 Sep 2004 18:26:24 GMT

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