Transcript Oracle Database Performance Secrets Finally Revealed

<Insert Picture
Here>
Oracle Database Performance Secrets Finally Revealed
Greg Rahn & Michael Hallas
Oracle Real-World Performance Group
Server Technologies
About The Real-World Performance Group
3
The secret to a chef’s
masterpiece is all in the
recipe
5
Agenda
•
•
•
•
Troubleshooting approach
Intro to the optimizer
Demonstration
Summary of secrets and lessons learned
6
What is your performance
troubleshooting
approach?
7
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?
8
The Change and Test Troubleshooting Approach
9
<Insert Picture
Here>
What really matters for
troubleshooting
performance?
10
<Insert Picture
Here>
Quite simply...
it's all in the approach.
11
Stack Visualization
12
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
13
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
14
Fix on Failure vs. Fix it Forever
The 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
15
<Insert Picture
Here>
Example of Applying the
“5 Whys”
16
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
17
Choosing Different Levels of Scope
• System level
– database parameters
– alter system
– object statistics
• Session level
– alter session
• Statement level
– hints
– SQL profiles & outlines & baselines
18
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.)
19
<Insert Picture
Here>
Quick Introduction To The
Optimizer
20
<Insert Picture Here>
An Important Note About
Cardinality Estimates
Good cardinality estimates generally
result in a good plan, however, bad
cardinality estimates do not always
result in a bad plan
21
Introducing the Cost-Based Optimizer
Cost 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
22
Introducing the Optimizer
Cost 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
23
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
24
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
25
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
26
Challenges in Cardinality Estimation
•
•
•
•
•
•
Complex predicates
Correlation
Non-representative bind values
Out of range predicates
Skew
Statistics Quality
– Frequency
– Histograms
– Sample Size
27
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
28
<Insert Picture
Here>
Demonstration
29
What Have We Seen?
Cardinality Drives Plan Selection
▲ Broadcast
▼ Hash
▼ Broadcast
▲ Hash
46
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)
47
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
51
What People Think Are Performance Secrets
•
•
•
•
•
•
Undocumented parameters
Documented parameters
Undocumented events
Index rebuilds and table reorgs
Fiddling with block size
Silver Bullets
52
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
53
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.
55
56