OraFAQ Forum: Performance Tuning » Rule based but has cost in explain plan.. why?

Home » RDBMS Server » Performance Tuning » Rule based but has cost in explain plan.. why?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Rule based but has cost in explain plan.. why? [message #236976]

Fri, 11 May 2007 08:34

beetel
Messages: 96
Registered: April 2007

Member

One of our schema is using Rule-based optimizer. When I tried get the explain plan for a query, the Cost column in the Explain plan has values. The table has no statistics as I saw in the LAST_ANALYZED column of the following views:

- USER_TABLES
- USER_INDEXES
- USER_TAB_PARTITIONS

The LAST_ANALYZED column is null. And it has no entry in the USER_TAB_COL_STATISTICS table, too.

My question is how can there be a cost in the explain plan if the table has no statistics at all because it uses rule-based optimizer...

Report message to a moderator

Re: Rule based but has cost in explain plan.. why? [message #236978 is a reply to message #236976]

Fri, 11 May 2007 08:37

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Are you sure it uses RBO?
Post the proof.

Regards
Michel

Report message to a moderator

Re: Rule based but has cost in explain plan.. why? [message #237032 is a reply to message #236976]

Fri, 11 May 2007 12:15

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Do you have hints in your query?
Any hint (except RULE) invokes CBO.

HTH.
Michael

Report message to a moderator

Re: Rule based but has cost in explain plan.. why? [message #237514 is a reply to message #237032]

Mon, 14 May 2007 13:45

jrich
Messages: 35
Registered: February 2006

Member

If you are using an object the RBO does not support, such as partitioned tables, bitmap indexes, IOTs, or function-based indexes, then the CBO will be used.

JR

Report message to a moderator

Previous Topic:	my production database is taking 50% of CPU consumption in main db server
Next Topic:	Setting sql trace parameter

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 23 07:39:35 CST 2025