Solved

How to change an existing database from rule based optimizer to cost based optimizer

Posted on 2010-11-23
5
299 Views
Last Modified: 2012-10-06
Hi can anyone tell me how we can change existing Database which is running on Rule based optimizer converting it to cost based optimizer.
0
Comment
Question by:iabhinay_dba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 166 total points
ID: 34202070
The optimizer_mode init parameter.  On mobile so I can't provide an exact link but it's in the online docs.
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 168 total points
ID: 34202361
http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams167.htm#i1131532

It's not documented after 9i but you can also set the value to "CHOOSE"
0
 
LVL 5

Assisted Solution

by:anand_20703
anand_20703 earned 166 total points
ID: 34273639
Above answer is right and CHOOSE parameter is recommended because optimizer will select between RULE and COST , accordingly generates the explain plans. RULE based optimizer is old and was recommended only in Oracle ERP applications older than 11i.
.
To reap the benefits(performance) of  COST/CHOOSE optimizer enabled Databases, gathering statistics periodically is very important.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question