oracle 10g advanced performance tuning kyle hailey [email protected] - wait events docs – tools...
TRANSCRIPT
![Page 1: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/1.jpg)
Oracle 10g Advanced Performance Tuning
Kyle Hailey
http://oraclemonitor.com - wait events docs
http://ashmasters.com – tools S-ASH and ASHMON
http://www.perfvision.com/ftp/class - power points, scripts
![Page 2: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/2.jpg)
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!Success!First successful OEM designFirst successful OEM design
![Page 3: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/3.jpg)
#.3
Software appliance Software appliance installs in under an hour installs in under an hour
on bare-metal x86 on bare-metal x86 servers using SAN servers using SAN
storage or in a VM for storage or in a VM for Oracle 10g or 11g on Oracle 10g or 11g on
Linux, Solaris, AIX, and Linux, Solaris, AIX, and HP-UXHP-UX
1 TB1 TB
Fast, Non-disruptive Deployment
NFSNFS
1 TB1 TB 1 TB1 TB 1 TB1 TB 1 TB1 TB
ProductionProduction DevelopmentDevelopment Q/AQ/A ReportingReporting UATUAT
Sync via Sync via standard standard
APIsAPIs
300MB300MB
1 TB VDB1 TB VDB 1 TB VDB1 TB VDB 1 TB VDB1 TB VDB 1 TB VDB1 TB VDB
Provision and refresh Provision and refresh from any time from any time or SCNor SCN
![Page 4: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/4.jpg)
4Copyright 2006 Kyle HaileyCopyright 2006 Kyle Hailey
My Goal
Simplify the informationSimplify the information
and empower the DBAand empower the DBA
![Page 5: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/5.jpg)
Launch: Pressure
Midnight before Midnight before January 28, 1986 January 28, 1986 Lives are on the lineLives are on the line
Thanks to Edward TufteThanks to Edward Tufte
![Page 6: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/6.jpg)
Copyright 2006 Kyle Hailey
13 Pages Faxed
![Page 7: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/7.jpg)
Copyright 2006 Kyle Hailey
Original Engineering data
only showed damageonly showed damage
““damages at the hottest damages at the hottest and coldest temperature” and coldest temperature”
- - managementmanagement
![Page 8: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/8.jpg)
Copyright 2006 Kyle Hailey
Congressional Hearings Evidence
![Page 9: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/9.jpg)
Copyright 2006 Kyle Hailey
Clearer
1.1. Include successesInclude successes
2.2. Mark DifferencesMark Differences
3.3. Normalize same tempNormalize same temp
4.4. Scale known Scale known vsvs unknown unknown
5555 6565 75756060 7070 8080
44
88
1212
44
88
1212
3030 4040 50503535 4545
XX
![Page 10: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/10.jpg)
Copyright 2006 Kyle Hailey
Difficult
NASA Engineers Fail Congressional Investigators Fail Data Visualization is Difficult
But …
Lack of Clarity can be devastating
![Page 11: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/11.jpg)
Solutions
IdentifyDetailsGraphics
![Page 12: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/12.jpg)
First Step: Graphics
“The humans … are exceptionally good at parsing visual information, especially when that information is coded by color and/or _____ .”
Knowledge representation in cognitive science. Westbury, C. & Wilensky, U. (1998)
motionmotion
![Page 13: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/13.jpg)
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
![Page 14: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/14.jpg)
14
Counties in US
3101 Counties in US 50 pages
![Page 15: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/15.jpg)
“If I can't picture it, I can't understand it”
Anscombe's QuartetI II III IV
x y x y x y x y10 8.04 10 9.14 10 7.46 8 6.58
8 6.95 8 8.14 8 6.77 8 5.7613 7.58 13 8.74 13 12.74 8 7.71
9 8.81 9 8.77 9 7.11 8 8.8411 8.33 11 9.26 11 7.81 8 8.4714 9.96 14 8.1 14 8.84 8 7.04
6 7.24 6 6.13 6 6.08 8 5.254 4.26 4 3.1 4 5.39 19 12.5
12 10.84 12 9.13 12 8.15 8 5.567 4.82 7 7.26 7 6.42 8 7.915 5.68 5 4.74 5 5.73 8 6.89
Average 9 7.5 9 7.5 9 7.5 9 7.5Standard Deviation 3.31 2.03 3.31 2.03 3.31 2.03 3.31 2.03Linear Regression 1.33 1.33 1.33 1.33
- Albert Einstein- Albert Einstein
![Page 16: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/16.jpg)
Graphics for Anscombe’s Quartet
![Page 17: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/17.jpg)
What is a day in the life look like What is a day in the life look like for a DBA who has performance for a DBA who has performance issues?issues?
Tuning the Database
ComplexComplex
AveragesAverages
Anscombe's QuartetI II III IV
x y x y x y x yAverage 9 7.5 9 7.5 9 7.5 9 7.5Standard Deviation 3.31 2.03 3.31 2.03 3.31 2.03 3.31 2.03Linear Regression 1.33 1.33 1.33 1.33
![Page 18: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/18.jpg)
LOADLOAD
Top Top Activity Activity
SQLSQL EventsEvents SessionsSessions
Get DetailsGet Details
Click hereClick here
Max CPUMax CPU
(yard stick)(yard stick)
How Can We Open the Black Box?
![Page 19: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/19.jpg)
How Can We Open the Black Box?
OEM OEM ASHMON/SASHASHMON/SASH DB OptimizerDB Optimizer
•PowerfulPowerful - Identifies issues quickly and powerfully - Identifies issues quickly and powerfully
•InteractiveInteractive - Allows exploring the data - Allows exploring the data
•EasyEasy - Understandable by everyone, DBA, Dev and Managers - Understandable by everyone, DBA, Dev and Managers !!
Other tools: Lab128, ashviewer, oemliteOther tools: Lab128, ashviewer, oemlite
![Page 20: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/20.jpg)
Copyright 2006 Kyle Hailey
Ideas for Today
ASHASH
SamplingSampling
WaitsWaits
AASAAS
![Page 21: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/21.jpg)
Copyright 2006 Kyle Hailey
Sections
Day 1 New Ideas
StatspackASHAASOEM 10g
Day 2 Waits
Buffer Cache IORedoEnqueuesShared PoolSQL*Net
Day 3 SQL Tuning
http://oraclemonitor.com – wait documentationhttp://oraclemonitor.com – wait documentation
![Page 22: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/22.jpg)
Copyright 2006 Kyle Hailey
Do You Want?
Engineering Data?Engineering Data?
![Page 23: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/23.jpg)
Copyright 2006 Kyle Hailey
Do You Want?Pretty PicturesPretty Pictures
![Page 24: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/24.jpg)
Copyright 2006 Kyle Hailey
Do You Want?Clean and Clear Clean and Clear
? ? ? ? ? ? ? ? ? ?? ?
![Page 25: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/25.jpg)
Copyright 2006 Kyle Hailey
Imagine Trying to Drive your Car
And is updated once and hourAnd is updated once and hour
Or would you like it to Or would you like it to look …look …
Would you want your dashboard to look like :Would you want your dashboard to look like :
![Page 26: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/26.jpg)
Copyright 2006 Kyle Hailey
Or This
![Page 27: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/27.jpg)
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
![Page 28: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/28.jpg)
Bibliography
Refactoring SQL Refactoring SQL ApplicationsApplications – Stephane Faroult– Stephane Faroult
Troubleshooting Troubleshooting Oracle Performance Oracle Performance – – Christian AntogniniChristian Antognini
![Page 29: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/29.jpg)
END
Copyright 2006 Kyle Hailey
![Page 30: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/30.jpg)
Bibliography
http://www.simple-talk.com/sql/performance/designing-efficient-sql-a-visual-approach/
Refactoring SQL Applications – Stephane FaroultRefactoring SQL Applications – Stephane Faroult
Troubleshooting Oracle Performance – Christian AntogniniTroubleshooting Oracle Performance – Christian Antognini
SQL Tuning – Dan TowSQL Tuning – Dan Tow
Cost-Based Oracle Fundamentals – Jonathan LewisCost-Based Oracle Fundamentals – Jonathan Lewis
![Page 31: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/31.jpg)
When to Tune1. Machine
a) CPU Response times skewed 100% CPU might be fine Users wait in queue (run queue) => machine
underpowered
b) Memory Paging Wait times skewed (ex : latch free) Erratic response times ( ex : ls )
2. Oracle1) Waits > CPU ?
tune waits
2) CPU > 100% ? tune top CPU SQL
3) Else It’s the application
Oracle Load Oracle Load (AAS)(AAS)
Top SessionTop Session Top WaitTop Wait Top SQLTop SQL
SQL DetailSQL Detail Session DetailSession Detail File DetailFile DetailObject DetailObject Detail Wait DetailWait Detail
HostHost
AAS > AAS > #CPU #CPU
AAS > 1AAS > 1Waits > Waits > CPUCPU
CPU > CPU > WaitsWaits
CPUCPU MemoryMemory
![Page 32: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/32.jpg)
Machine
Make sure the machine is healthy before tuning Oracle CPU => use run queue, < 2 * #CPU Memory => page out
VMSTAT
![Page 33: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/33.jpg)
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
![Page 34: Oracle 10g Advanced Performance Tuning Kyle Hailey KyleLF@gmail.com - wait events docs – tools S-ASH and](https://reader031.vdocuments.us/reader031/viewer/2022020921/5697bf9d1a28abf838c939e9/html5/thumbnails/34.jpg)
How Can We Open the Black Box?
OEM OEM ASHMON/SASHASHMON/SASH DB OptimizerDB Optimizer
•PowerfulPowerful - Identifies issues quickly and powerfully - Identifies issues quickly and powerfully
•InteractiveInteractive - Allows exploring the data - Allows exploring the data
•EasyEasy - Understandable by everyone, DBA, Dev and - Understandable by everyone, DBA, Dev and Managers !Managers !