00_intro_live_short
Download
Report
Transcript 00_intro_live_short
Oracle 10g
Advanced Performance Tuning
Kyle Hailey
[email protected]
Delphix
http://oraclemonitor.com - wait events docs
http://ashmasters.com – tools S-ASH and ASHMON
http://www.perfvision.com/ftp/class/2_hour_lvc - power points
Who is Kyle Hailey
1990 Oracle
90 support
92 Ported v6
93 France
95 Benchmarking
98 ST Real World Performance
2000 Dot.Com
2001 Quest
2002 Oracle OEM 10g
2006 Independent
2008 Embarcadero
DB Optimizer
2010 Delphix
Success!
First successful OEM design
My Goal
Simplify the information
and empower the DBA
Copyright 2006
8 Kyle Hailey
Launch: Pressure
Midnight before
January 28, 1986
Lives are on the line
Thanks to Edward Tufte
13 Pages Faxed
Copyright 2006 Kyle Hailey
Original Engineering data
“damages at the hottest
and coldest temperature”
- management
only showed damage
Copyright 2006 Kyle Hailey
Congressional Hearings Evidence
Copyright 2006 Kyle Hailey
Clearer
12
12
8
8
4
X
4
30
35
40
45
50
55
60
65
70
75
80
1. Include successes
2. Mark Differences
3. Normalize same temp
4. Scale known vs unknown
Copyright 2006 Kyle Hailey
Difficult
NASA Engineers Fail
Congressional Investigators Fail
Data Visualization is Difficult
But …
Lack of Clarity can be devastating
Copyright 2006 Kyle Hailey
Solutions
Clear Identification
Know how to identify problems and issues
Access to details
Provide solutions and/or information to address the issues
Graphics
Easy understanding, effective communication and discussion
First Step: Graphics
“The humans … are exceptionally
good at parsing visual information,
especially when that information is
coded by color and/or
motion
_____ .”
Knowledge representation in cognitive science. Westbury, C. & Wilensky, U. (1998)
Why Use Graphics
You can't imagine how many times I was told that nobody wanted or
would use graphics …
-- Jef Raskin, the creator of the Macintosh
Infocus – (overhead projectors) sited a
study that humans can parse graphical
information 400,000 times faster than
textual data
Counties in US
3101 Counties in US
50 pages
18
“If I can't picture it, I can't understand it”
- Albert Einstein
I
y
x
Average
Standard
Deviation
Linear Regression
x
10
8
13
9
11
14
6
4
12
7
5
9
8.04
6.95
7.58
8.81
8.33
9.96
7.24
4.26
10.84
4.82
5.68
7.5
3.31
1.33
2.03
Anscombe's Quartet
II
III
y
x
y
x
10
9.14
10
7.46
8
8.14
8
6.77
13
8.74
13 12.74
9
8.77
9
7.11
11
9.26
11
7.81
14
8.1
14
8.84
6
6.13
6
6.08
4
3.1
4
5.39
12
9.13
12
8.15
7
7.26
7
6.42
5
4.74
5
5.73
9
7.5
9
7.5
3.31
1.33
2.03
3.31
1.33
2.03
IV
y
8
8
8
8
8
8
8
19
8
8
8
9
3.31
1.33
6.58
5.76
7.71
8.84
8.47
7.04
5.25
12.5
5.56
7.91
6.89
7.5
2.03
Graphics for Anscombe’s Quartet
Tuning the Database
Complex
What is a day
in the life look like
Averages
for a DBA who has performance
issues?
I
y
x
Average
Standard Deviation
Linear Regression
9
3.31
1.33
x
7.5
2.03
Anscombe's Quartet
II
III
y
x
y
x
9
7.5
9
7.5
3.31
2.03
3.31
2.03
1.33
1.33
IV
y
9
3.31
1.33
7.5
2.03
How Can We Open the Black Box?
LOAD
Max CPU
(yard stick)
Top Activity
Click here
SQL
Events
Sessions
Get Details
How Can We Open the Black Box?
OEM
ASHMON/SASH DB Optimizer
•Powerful - Identifies issues quickly and powerfully
•Interactive - Allows exploring the data
•Easy - Understandable by everyone, DBA, Dev and Managers !
Ideas for Today
AAS
ASH
Sampling
Waits
Copyright 2006 Kyle Hailey
Sections
http://oraclemonitor.com – wait documentation
Day 1
New Ideas
Day 2
Waits
Statspack
Buffer
ASH
IO
AAS
Redo
OEM
10g
Day 3
SQL Tuning
Cache
Enqueues
Shared
Pool
SQL*Net
Copyright 2006 Kyle Hailey
Do You Want?
Engineering Data?
Copyright 2006 Kyle Hailey
Do You Want?
Pretty Pictures
Copyright 2006 Kyle Hailey
Do You Want?
Clean and Clear
?
?
?
?
?
?
Copyright 2006 Kyle Hailey
Imagine Trying to Drive your Car
Would you want your dashboard to look like :
And is updated once and hour
Or would you like it to
look …
Copyright 2006 Kyle Hailey
Or This
Copyright 2006 Kyle Hailey
Summary
1.Database - AAS
Profile database
Use wait interface and graphics
Identify machine, application, database or SQL
2.SQL - VST
Indexes, stats, execution path
Visual SQL Tuning
Bibliography
Refactoring SQL Applications – Stephane Faroult
Troubleshooting Oracle Performance – Christian Antognini
SQL Tuning – Dan Tow
Cost-Based Oracle Fundamentals – Jonathan Lewis
http://www.simple-talk.com/sql/performance/designing-efficient-sql-a-visual-approach/
END
Copyright 2006 Kyle Hailey
When to Tune
1. Machine
CPU
a)
Response times skewed
100% CPU might be fine
Users wait in queue (run queue) => machine
underpowered
Memory
b)
Paging
Wait times skewed (ex : latch free)
Erratic response times ( ex : ls )
2. Oracle
Host
Waits > CPU ?
1)
Oracle Load
(AAS)
tune waits
AAS >
#CPU
CPU > 100% ?
2)
tune top CPU SQL
AAS > 1
Else
3)
CPU
Memory
Waits >
CPU
Top Session
Top Wait
SQL Detail
Wait Detail
CPU >
Waits
Top SQL
It’s the application
Object Detail
Session Detail
File Detail
Machine
Make sure the machine is healthy before tuning Oracle
CPU => use run queue, < 2 * #CPU
Memory => page out
VMSTAT
Summary
1.Machine - vmstat
Memory, CPU (we can see IO response in Oracle)
2.Database - AAS
Use wait interface and graphics
Identify machine, application, database or SQL
3.SQL - VST
Indexes, stats, execution path
Visual SQL Tuning
How Can We Open the Black Box?
OEM
ASHMON/SASH DB Optimizer
•Powerful - Identifies issues quickly and powerfully
•Interactive - Allows exploring the data
•Easy - Understandable by everyone, DBA, Dev and
Managers !