Home » RDBMS Server » Performance Tuning » optimizer mode in oracle 10g
optimizer mode in oracle 10g [message #238336] Thu, 17 May 2007 07:48 Go to next message
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
Re: optimizer mode in oracle 10g [message #238349 is a reply to message #238336] Thu, 17 May 2007 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: optimizer mode in oracle 10g [message #238588 is a reply to message #238349] Fri, 18 May 2007 11:47 Go to previous messageGo to next message
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.
Re: optimizer mode in oracle 10g [message #238590 is a reply to message #238588] Fri, 18 May 2007 11:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: optimizer mode in oracle 10g [message #238615 is a reply to message #238590] Fri, 18 May 2007 13:15 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
oopss!!
Thanks for corection;)
Previous Topic: faq
Next Topic: NEED GUIDENCE
Goto Forum:
  


Current Time: Fri Dec 09 19:42:51 CST 2016

Total time taken to generate the page: 0.09422 seconds