OraFAQ Forum: Performance Tuning » optimizer mode in oracle 10g

Home » RDBMS Server » Performance Tuning » optimizer mode in oracle 10g

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

optimizer mode in oracle 10g [message #238336]

Thu, 17 May 2007 07:48

hanueng
Messages: 1
Registered: May 2007

Junior Member

Hi Experts,

My question is regarding optimizer mode RULE in 10g.

I think this topic is already stated in form,

but what i came to know is even if you use RULE as optimizer mode 10g will ignore this and uses default optimizer mode parameter ALL_ROWS...

actuallu in oracle 9i(RULE) i had run application which took 170 sec where as in 10g it took 50 seconds

Plz clarify my doubt

Report message to a moderator

Re: optimizer mode in oracle 10g [message #238349 is a reply to message #238336]

Thu, 17 May 2007 09:01

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

Senior Member
Account Moderator

It will use RULE if it can otherwise it'll use ALL_ROWS.
Just explain plan your statement and have a look of what Oracle tells you.

Regards
Michel

Report message to a moderator

Re: optimizer mode in oracle 10g [message #238588 is a reply to message #238349]

Fri, 18 May 2007 11:47

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

HEY
This Mode is obsolete in Oracle 10g.The only Mode is Cost Based Optimizer(Statistics driven).However yourquery will run by default optimizer mode of Oracle 10g.

Report message to a moderator

Re: optimizer mode in oracle 10g [message #238590 is a reply to message #238588]

Fri, 18 May 2007 11:53

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

Senior Member
Account Moderator

Wrong.
RBO still exists and can be used in 10g.

SQL> set autotrace traceonly explain
SQL> select /*+ rule */ dummy from dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| DUAL |
----------------------------------

Note
-----
   - rule based optimizer used (consider using cbo)

SQL> @v

Version Oracle : 10.2.0.3.0

Notice the note.

Regards
Michel

Report message to a moderator

Re: optimizer mode in oracle 10g [message #238615 is a reply to message #238590]

Fri, 18 May 2007 13:15

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

oopss!!
Thanks for corection;)

Report message to a moderator

Previous Topic:	faq
Next Topic:	NEED GUIDENCE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 08:45:27 CST 2024