How to change OPTIMIZER_MODE setting in oracle?

Question

How to change OPTIMIZER_MODE setting in oracle?

Answer 1

You can change OPTIMIZER_MODE, with alter command as shown below,

alter session set OPTIMIZER_MODE = RULE;
alter session set OPTIMIZER_MODE = choose;

Answer 2

ALTER SESSION SET OPTIMIZER_MODE  = RULE;

or if you need to make changes for all system:

ALTER SYSTEN SET OPTIMIZER_MODE = RULE SCOPE=SPFILE;

Use

 SELECT VALUE FROM v$parameter WHERE NAME = 'optimizer_mode'

to check the result.