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

Hi can anyone tell me how we can change existing Database which is running on Rule based optimizer converting it to cost based optimizer.
iabhinay_dbaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sdstuberConnect With a Mentor Commented:
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
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
The optimizer_mode init parameter.  On mobile so I can't provide an exact link but it's in the online docs.
0
 
AnandConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.