Operating System - HP-UX
1748209 Members
2841 Online
108759 Solutions
New Discussion юеВ

Re: Execution Plan on CBO vs Rule

 
SOLVED
Go to solution
ericfjchen
Regular Advisor

Execution Plan on CBO vs Rule

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
13 REPLIES 13
Eric Antunes
Honored Contributor

Re: Execution Plan on CBO vs Rule

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...
Each and every day is a good day to learn.
ericfjchen
Regular Advisor

Re: Execution Plan on CBO vs Rule

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
Honored Contributor

Re: Execution Plan on CBO vs Rule

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
fiat lux
Jean-Luc Oudart
Honored Contributor

Re: Execution Plan on CBO vs Rule

Sorry

forget the attachment


Regards,
Jean-Luc
fiat lux
Jean-Luc Oudart
Honored Contributor

Re: Execution Plan on CBO vs Rule

this document too

Regards
Jean-Luc

fiat lux
Eric Antunes
Honored Contributor

Re: Execution Plan on CBO vs Rule

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
Each and every day is a good day to learn.
Fred Ruffet
Honored Contributor

Re: Execution Plan on CBO vs Rule

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

"Reality is just a point of view." (P. K. D.)
Eric Antunes
Honored Contributor

Re: Execution Plan on CBO vs Rule

Hi Eric,

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

We need points to survive. :-)
Each and every day is a good day to learn.
Brian Crabtree
Honored Contributor

Re: Execution Plan on CBO vs Rule

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