• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

oracle optimizer

Oracle Optimizer choose the execution plan based on the statistics information .
we can gather the statistics dbms_stats.
What can a dba do to make the oracle optimizer to pick a different execution plan?
0
taaz
Asked:
taaz
3 Solutions
 
slightwv (䄆 Netminder) Commented:
Why do you want to pick a different plan?

That asked:
You can use hints.
You can use stored outlines.

Possibly a couple others.
0
 
sdstuberCommented:
create indexes  -> new options for optimizer

reload data - > changes index clustering factor

change optimizer init parameters -> change optimizer mode, index "weight" and dynamic sampling

change init version and compatibility parameters -> make the optimizer perform as a different version (usually lower unless increasing from a previously lowered value)

rewrite the query -> new query = new plan

use materialized views and query rewrite -> let the optimizer rewrite the query for you using precalculated values

use advanced rewrite -> replace a bad query with a functionally equivalent better query.  Similar to rewriting except you don't need to modify the original source code.

use sql profiles -> let the optimizer learn data distributions from previous executions

collect extended statistics -> uses dbms_stats, but these are a manual effort to define extensions that are valuable
0
 
Franck PachotCommented:
>> What can a dba do to make the oracle optimizer to pick a different execution plan?

alter system set "_optimizer_random_plan"=42;

Open in new window



This is a joke, don't try this on production... it works...
0
 
taazAuthor Commented:
Thanks
0
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now