Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

oracle optimizer

Posted on 2013-06-18
4
Medium Priority
?
353 Views
Last Modified: 2013-06-20
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
Comment
Question by:taaz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 400 total points
ID: 39256930
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 800 total points
ID: 39256958
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
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 800 total points
ID: 39257236
>> 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
 

Author Closing Comment

by:taaz
ID: 39263498
Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
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…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

688 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