Oracle Optimizer Master Class w/ Tom Kyte: Part 2

Use the Right Tools:

• Don't use AUTOTRACE with bind vars
• Function abuse
• may cause cardinality issues
• may reduce access paths (ie not use indexes)
• in WHERE clause, compare like datatypes 
• can increase CPU
• Could ignore partitions on table (ie partition elimination)
• use AUTOTRACE to identify implicit conversions

In PL/SQL, use of WHEN OTHERS without a RAISE some sort of error IS A BUG. You've just coded a bug in your code, because errors will never be seen. 

[Updated 1:51pm PST]

Optimizer Hints

• Adding hints won't magically improve every query you encounter
• Optimizer hints should be used only with great care
• hints allow you influence the Optimizer
• Hints are directives 

Two Types of Hints

• Non-Optimizer (aka 'good hints')
• Parallel
• APPEND
• MONITOR
• Dynamic_sampling -- provide more information to optimizer
• cardinality -- provide more information to optimizer
• Optimizer (aka 'bad hints')
• Bad, because hints are applied to query, post-transformation
• INDEX
• ORDERED
• FULL

Optimizer defaults to return ALL_ROWS by default. If you want something else, use a hint

• FIRST_ROWS()

Changing initialization parameter for a query: OPT_PARAM

• Allows value for init.ora Optimizer parameter to be changed for a specific query
• Useful way to prevent setting non-default parameter value system wide
• This are 'GOOD hints
• OPTIMIZER_DYNAMIC_SAMPLING
• OPTIMIZER_USE_PENDING_STATISTICS
• and so on

[Updated: 2:20pm PST]

Determining which hints you need

• Run SQL Tune Advisor
• SQL Profile is additional statistics 
• SQL Profile will provide additional information 
• without allowing telling the optimizer to do anything different, 
• without changing the changing the query
• Profiles are specific to query
• extended stats are for all operations
• Profiles are better in OLTP systems, for queries which do not change, and are repeated
• SQL Profile fixes 'scaling factors' not cardinality factors. 

Dynamic Sampling Levels defined on slide #79

Why are Hints not obeyed

• Typos
• aliases to database objects
• bind peeking
• scope (hint on subquery, not in sub query)
• invalid hint combination
• or maybe it is
• or maybe it's just such a bad idea Oracle threw it away

[Updated: 2:45pm PST]

If you can hint it, you can baseline it.

• Use SQL Plan Management
• Influence the execution plan w/o adding hints

here's how to do it: 

1. Get SQL id w/ bad plan
2. create sql baseline with non-hinted, bad plan
3. captured plan in baseline is not desired, so disable it 
4. modified SQL statement with hints to get plan you want, anyway you can, doesn't matter how
5. get SQL for optimal plan and planed hash value
6. load plan into baseline and enable

and we're done.

[Updated 2:55pm PST]