Oracle Openworld Directory Index

Dave Ensor
Senior Technical Advisor
BMC Software

Paper 560
Agenda
Background
Oracle’s Cost Based Optimizer
Supplied package DBMS_STATS
Plan Stability
The Prototype
Case Studies
Conclusions
The Background
BMC internal IS application
licensed software package
statement causing major performance problem
Traditional query tuning techniques
Redesign of database
Redesign of application
Modify problem SQL
Recode
‘Tweak’ predicate clauses
Add or change optimizer hints
Changes to Index strategy
Keep ‘good’ indexes
Lose ‘bad’ indexes
Remove redundant indexes
Reorder index keys if needed
Add new indexes if needed

tkprof reports on statement
Mission Statement
Devise a tuning technique that
Improves the performance of problem SQL
Does not affect performance of any other SQL
Requires no changes to application code
Requires no changes to application schema
Oracle’s Cost Based Optimizer
CBO Basics
Object Statistics
Optimizer Modes
Optimizer Settings
Cursor Sharing
CBO Basics
Prior to Oracle9i (7.x and 8.x)
Used only (notional) I/O cost
Equated rather better to block visits
Calculated from analysis of statement and object statistics stored in Oracle's data dictionary
Took multi-block I/O into account
Took no account of CPU execution cost
Tended to over-use full table scans
Oracle9i
Claims to use execution cost
Why is this needed?
(Selected) Object Statistics
Tables
Total rows & blocks (to HWM)
Average row length
Indexes
Total keys and total distinct keys
Total leaf blocks
Average leaf blocks / data blocks per key
Columns
Value distribution histogram
Optimizer Modes
Can be set at instance, session or statement level
rule
choose
all_rows
first_rows
in Oracle9i expanded to first_rows_N
where N is 1, 10, 100 or 1000
Key CBO settings
optimizer_index_caching (0 to 100)
Higher values make indexes more attractive
optimizer_index_cost_adj (1 to 10,000)
Lower values make indexes more attractive
bitmap_merge_area_size
sort_area_size
More space may make operation more attractive
query_rewrite_enabled
query_rewrite_integrity
Allow optimizer to transform certain queries
Cursor Sharing
init.ora parameter cursor_sharing
if set to force translates literals to bind variables
select ORD# from ORDS where OTYPE = 'STD';
select ORD# from ORDS where OTYPE = 'GOV';
select ORD# from ORDS where OTYPE = 'STAFF';
are all executed as
select ORD# from ORDS where OTYPE = :"SYS_B_0";
Exploits the shared pool
Saves parse time
Prevents optimizer from using column value statistics
Each statement will use the same execution plan
Bind values used first time through in Oracle9i
Supplied package DBMS_STATS
Allows manipulation of optimizer statistics

dbms_stats.set_table_stats ('SCOTT', 'EMP'
, numrows => 14
, numblks => 1
, avrglen => 48);

dbms_stats.delete_index_stats ('SCOTT', 'EMPPK');

dbms_stats.gather_schema_stats ('SCOTT');

~ 35 procedures in total (Oracle9i)
Plan Stability
Problem
Changing optimizer settings and object stats can (will) destabilize other SQL statements
Solution 1
CREATE OUTLINE to store current plan
Use supplied package DBMS_OUTLN_EDIT
to modify stored hints
New in Oracle9i
Currently all you have is CHANGE_JOIN_POS
“Less than adequate” for most problems
Plan Stability
Problem
Changing optimizer settings and object stats can (will) destabilize other SQL statements
Solution 2
Change settings and stats to get desired plan
CREATE OUTLINE to “store the new plan”
Actually stores optimizer hints
Restore settings and stats to original values
Plan Stability
Problem
Changing optimizer settings and object stats can (will) destabilize other SQL statements
Solution 3
Tune copy of query using hints, note plan
Copy empty schema & stats to private instance
Change settings and stats to get desired plan
CREATE OUTLINE to “store the new plan”
Export outline to production schema
Notes on Plan Stability
Incurs a parse time penalty
not significant for most applications

Not normally effective unless SQL uses bind variables rather than literals, either
coded into application, or
through cursor_sharing = force
The Prototype
Perl based GUI
Based on oraexplain.pl © Alan Burlison
My version contains 169 -font arguments!
Allows
Statement to be entered, read from file or captured from shared pool
Changes to
session settings
object statistics
Optimizer plans to be viewed
Case Studies
Effects of optimizer settings

Effects of object statistics

The query that started it all


Fill Out a Survey...
… and Get a Chance to Win a Compaq iPAQ!

We (they) want to know what you think!

Fill out the survey that was handed out at the beginning of the session for a chance to win a Compaq iPAQ.

Remember to include your name and email in the available section and we will enter your name into two daily drawings to win an iPAQ
Conclusions
Using DBMS_STATS may not fix every
“statement from hell” but it
has low implementation cost
can be totally noninvasive
No changes to application code
No changes to application schema (indexes)

may need outlines enabled
may need changes to optimizer settings