Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2010-11-23
5
Medium Priority
?
304 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
3 Comments
 
LVL 79

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 6

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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 how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

588 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