Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-11-23
5
Medium Priority
?
301 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
5 Comments
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 664 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 672 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
Anand earned 664 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.
Suggested Courses

885 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