oracle database performance secrets finally revealed
DESCRIPTION
. Oracle Database Performance Secrets Finally Revealed. Greg Rahn & Michael Hallas Oracle Real-World Performance Group Server Technologies. About The Real-World Performance Group. The secret to a chef’s masterpiece is all in the recipe. Agenda. Troubleshooting approach - PowerPoint PPT PresentationTRANSCRIPT
<Insert Picture Here>
Oracle Database Performance Secrets Finally Revealed
Greg Rahn & Michael HallasOracle Real-World Performance GroupServer Technologies
3
About The Real-World Performance Group
5
The secret to a chef’s masterpiece is all in the recipe
6
Agenda
• Troubleshooting approach• Intro to the optimizer• Demonstration• Summary of secrets and lessons learned
7
What is your performance troubleshooting approach?
8
Do you...
• Use Google as your first resource?• Use the Oracle Documentation as your second
resource?• Believe in tuning databases by changing block size?• Frequently use database parameters to tune queries?• Believe in “Silver Bullet Tuning”?• Blindly apply previously successful solutions?• Practice the “Change and Test” troubleshooting
approach?
9
The Change and Test Troubleshooting Approach
10
What really matters for troubleshooting performance?
<Insert Picture Here>
11
Quite simply...
it's all in the approach.
<Insert Picture Here>
12
Stack Visualization
13
The Systematic Troubleshooting Approach
1. Define the problem, the scope and identify symptoms
2. Collect and analyze data/metrics
3. Ask “Why?” five times to identify root cause
4. Understand and devise change
5. Make a single change
6. Observe and measure results
7. If necessary, back out change; repeat process
14
Systematic Troubleshooting Guidelines
• Don’t just look inside the database, look outside as well• Make exactly one change at a time• Scope of solution matches scope of problem• Choose the right tool for the job• Carefully document change and impact• Suppressing the problem is not the same as root
cause• Realize that you may not be able to get to the root
cause in just one step
15
Fix on Failure vs. Fix it ForeverThe benefits of root cause analysis
• Fix on Failure–Finger pointing and the blame game–Stressful for everyone–Never time to fix it right the first time, but always plenty of time
to keep fixing it time and time again
• Fix it Forever– Identify root causes of problems, so permanent solutions can be
implemented–Develop a logical, systematic and data driven approach to
problem solving
16
Example of Applying the “5 Whys”
<Insert Picture Here>
17
Applying the “5 Whys” to My batch job ran long last night
1. Why? - A specific query took 5x as long
2. Why? - Execution plan changed from HJ to NLJ
3. Why? - Query optimizer costed the NLJ to be cheaper
4. Why? - Variables involved in the costing have changed
5. Why? - Statistics were gathered with wrong options
18
Choosing Different Levels of Scope
• System level–database parameters–alter system–object statistics
• Session level–alter session
• Statement level–hints–SQL profiles & outlines & baselines
19
Performance Troubleshooting Toolbox
• ADDM, AWR, ASH reports and raw data• SQL Monitoring Active Report (11g)• DBMS_XPLAN• SQL Trace• V$SESSTAT• V$SESSION• Stack dumps (pstack)• OS metrics tools (collectl, iostat, vmstat, mpstat, etc.)
20
Quick Introduction To The Optimizer
<Insert Picture Here>
21
<Insert Picture Here>
Good cardinality estimates generally result in a good plan, however, bad cardinality estimates do not always
result in a bad plan
An Important Note About Cardinality Estimates
22
Introducing the Cost-Based OptimizerCost and Cardinality
• Cardinality–Estimated number of rows returned from a join, a table or an
index–Factors influencing cardinality• Query predicates and query variables• Object statistics
23
Introducing the OptimizerCost and Cardinality
• Cost–Representation of resource consumption• CPU• Disk I/O• Memory• Network I/O
–Factors influencing cost• Cardinality and selectivity• Cost model• Parameters• System statistics
24
Good SQL and Bad SQL
• Good SQL–SQL that makes it possible for the optimizer to produce a good
cardinality estimate–select * from emp where ename != ‘KING’
• Bad SQL–SQL that makes it difficult for the optimizer to produce a good
cardinality estimate–select * from emp where replace(ename, ‘KING’) is not null
25
Good Plans and Bad Plans
• Good Plan–Efficient retrieval or modification of the desired rows–Highly selective index to retrieve few rows from a large table–Scan to retrieve many rows from a large table
• Bad Plan– Inefficient retrieval or modification of the desired rows–Scan to retrieve few rows from a large table–Non-selective index to retrieve many rows from a large table
26
What is a Query Plan?
• Access Path–Table scan– Index { fast full | full | range | skip | unique } scan
• Join Method–Hash–Merge–Nested loops
• Join Order• Distribution Method–Broadcast–Hash
27
Challenges in Cardinality Estimation
• Complex predicates• Correlation• Non-representative bind values• Out of range predicates• Skew• Statistics Quality–Frequency–Histograms–Sample Size
28
What Is Dynamic Sampling?
• Improves quality of cardinality estimates• Objects with no statistics–Avoids the use of heuristics–Less complete than statistics stored in the dictionary
• Objects with existing statistics–Predicates with complex expressions
29
Demonstration
<Insert Picture Here>
46
What Have We Seen?Cardinality Drives Plan Selection
▲ Broadcast
▼ Hash
▼ Broadcast
▲ Hash
47
What Have We Seen?
• SQL Monitor Report– Ideal tool to use for statement troubleshooting–Can be used on active statements
• Dynamic Sampling–Good way of getting better cardinality estimates–Be cautious when using DS without table stats–Parallel execution chooses the level automatically (11.2)–RWP used level 4 or 5 for data warehouses (11.1)
51
What Have We Seen?
• SQL Tuning Advisor–Helps identify better plans
• SQL Profile – “Patch” a plan• Generated by SQL Tuning Advisor• Identified by the user
–Force matching can match literals
• SQLT/SQLTXPLAIN –coe_xfr_sql_profile.sql–See MOS note 215187.1
52
What People Think Are Performance Secrets
• Undocumented parameters• Documented parameters• Undocumented events• Index rebuilds and table reorgs• Fiddling with block size• Silver Bullets
53
What Are The Real-World Performance Secrets
• Use a systematic approach, always• Let data (metrics, etc.) guide your troubleshooting• Match the scope of the problem and solution • Realize that you may not be able to get to the root
cause in just one step
55
The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions.The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
56