![Page 1: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/1.jpg)
![Page 2: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/2.jpg)
The Self-managing Database:Automatic Performance Diagnosis
Graham WoodKyle Hailey
Oracle Corporation
Session id: 40092
![Page 3: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/3.jpg)
Problem Definition
Performance Diagnosis & Tuning is complex
Diagnosis often requires additional data capture
Database wide view of operations is lacking
Data overload rather than information
Misguided tuning efforts waste time & money
![Page 4: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/4.jpg)
Problem Solution: Oracle10g Performance Diagnosis & Tuning are complex
automated problem diagnosis Diagnosis often requires additional data capture
complete, lightweight capture of workload data Database wide view of operations is lacking
holistic time based analysis Data overload rather than information
reports top problems and solutions Misguided tuning efforts
reports non-problem areas
![Page 5: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/5.jpg)
Intelligent Infrastructure
Application & SQLManagement
System ResourceManagement
SpaceManagement
Backup & RecoveryManagement
StorageManagement
Database Control
Database Management
Oracle Database 10g – Self-Managing Database
![Page 6: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/6.jpg)
Intelligent Infrastructure Automatic Workload
Repository – “Data Warehouse” of the
Database– Code instrumentation
Automatic Maintenance Tasks
– Pre-packaged, resource controlled
Server-generated Alerts– Push vs. Pull, Just-in-time,
Out-of-the-box Advisory Infrastructure
– Integrated, uniform
Intelligent Infrastructure
Application & SQLManagement
System ResourceManagement
SpaceManagement
Backup & RecoveryManagement
StorageManagement
Database Management
Automatic Workload Repository
Automatic Maintenance Task Infrastructure
Server-generated Alert Infrastructure
Advisory Infrastructure
![Page 7: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/7.jpg)
Automatic Database Diagnostic Monitor (ADDM)
Performance Diagnostic engine in the database
Automatically diagnoses performance problems
Provides Root Cause Analysis with recommended solutions
Identifies non-problems areas
Integrates all componentsIntelligent Infrastructure
Application & SQLManagement
System ResourceManagement
SpaceManagement
Backup & RecoveryManagement
StorageManagement
Database Management
Proactive and effective tuning
![Page 8: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/8.jpg)
Performance Monitoring Solutions
Snapshots
ADDM
ADDM Results
Alerts
In memorystatistics
Workload Repository
SGA
Reactive Monitoring
Proactive Monitoring
![Page 9: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/9.jpg)
Automatic Workload Repository (AWR)
a.k.a. Statspack++
Server captures workload data• Every 30 minutes, or manually• Efficient capture• Self manages space requirements• Saves data for 7 days by default
![Page 10: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/10.jpg)
Automatic Workload Repository (AWR)
Classes of Data
BASE STATISTICS e.g. physical reads SQL STATISTICS e.g. disk reads (per sql stmt) METRICS e.g. physical reads / sec ACTIVE SESSION HISTORY
e.g. sid : 10
event : db file sequential read
file# : 33, block# : 209, obj# : 19
time : 20000 μs
![Page 11: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/11.jpg)
New Base Statistics Extensive code instrumentation
Time Model (v$sys_time_model)– Db time– Connection Management (logon, logoff)– Parse (hard, soft, failed,..)– SQL, PLSQL and Java execution times
Wait Model (v$system_event) – 700 different wait events– 12 wait classes
OS Stats (v$osstat)– CPU + Memory
SQL Exec
PLSQL Exec
Conn MgmtParse
Java Exec
![Page 12: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/12.jpg)
New SQL Statistics
SQL_id – more unique hash value SQL statement statistics
– Wait class time– PLSQL time– Java time
Sampled bind values (v$sql_bind_capture) Efficient top SQL identification using Δs in the kernel,
by 6 dimensions:– CPU– Elapsed– Parse– ...
![Page 13: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/13.jpg)
Active Session History (ASH)
Sampled history of v$session_wait
• Samples active sessions every second into memory (v$active_session_history)
• Direct access to kernel structures• Selected samples flushed to AWR• Data captured includes:
– SID – SQL ID– Program, Module, Action – Wait event# – Object, File, Block– actual wait time (if captured while waiting)
![Page 14: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/14.jpg)
Performance Monitoring Solutions
Snapshots
ADDM
ADDM Results
Alerts
In memorystatistics
Workload Repository
SGA
Reactive Monitoring
Proactive Monitoring
![Page 15: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/15.jpg)
ADDM’s Architecture
SQLAdvisor
High-load SQL
IO / CPU issues
RAC issues
Automatic Diagnostic Engine
Snapshots in
Automatic Workload
Repository
Automatic Diagnostic Engine
System Sizing Advice
Network + DB config
Advice
Uses Time & Wait Model data from Workload Repository
Classification Tree is based on decades of Oracle performance tuning expertise
Time based analysis Recommends solutions
or next steps Runs proactively &
manually
![Page 16: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/16.jpg)
ADDM MethodologyTop down analysis of where time is spent
Period Analysis using AWR snapshots
Throughput centric
Focus on reducing time ‘DB time’
Time based quantification
Problems with impact
Recommendations with benefit
![Page 17: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/17.jpg)
ADDM MethodologyProblem classification system
Decision tree based on the Wait Model and Time Model Stats……
System Wait
RAC Waits
IO Waits
Concurrency
……
Buffer Busy
Parse Latches
Buf Cache latches
……
Root CausesSymptoms
![Page 18: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/18.jpg)
ADDM MethodologyProblem classification system
Decision tree based on the Wait Model and Time Model Stats……
System Wait
RAC Waits
IO Waits
Concurrency
……
Buffer Busy
Parse Latches
Buf Cache latches
……
Non - Problems areas.
![Page 19: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/19.jpg)
Top Performance Issues
- Top SQL
- IO Issues-Bandwidth, Hot Files
- Parsing- hard, soft, failed
- Configuration issues- Log file sizing- Log buffer sizing- Archiving- MTTR settings.
- Application usage
Not rocket science anymore
![Page 20: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/20.jpg)
Top Performance Issues
- Excessive Logon/Logoff
- Undersized memory-SGA, PGA
- Hot Blocks & Objects with SQL-buffer busy waits-cache buffer chain latches
- RAC service issues- network, LMS, remote instance
- Locks & ITL contention with object & SQL- Checkpoint causes- PL/SQL, Java time
Not diagnosable using Statspack data
![Page 21: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/21.jpg)
ADDM Output
Set of Findings with impact– Root cause– Symptoms – Non-problem areas
Recommendations with benefit and rationale Inference Path of the analysis Output in Advisor Framework Externalized through EM screens or ADDM report
![Page 22: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/22.jpg)
Database Home Page
![Page 23: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/23.jpg)
ADDM Findings
![Page 24: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/24.jpg)
ADDM Recommendations
![Page 25: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/25.jpg)
Performance Diagnostic: Before and Now
Before Examine system utilization Look at wait events Observe latch contention See wait on shared pool and library cache latch Review v$sysstat See “parse time elapsed” > “parse time cpu” and #hard
parses greater than normal Identify SQL by..
Identifying sessions with many hard parses and trace them, or
Reviewing v$sql for many statements with same hash plan
Examine objects accessed and review SQL Identify “hard parse” issue by observing the SQL contains
literals Enable cursor sharing
Oracle10G Review ADDM
recommendations ADDM recommends
use of cursor_sharing
Scenario: Hard parse problems
![Page 26: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/26.jpg)
Performance Monitoring Solutions
Snapshots
ADDM
ADDM Results
Alerts
In memorystatistics
Workload Repository
SGA
Reactive Monitoring
Proactive Monitoring
![Page 27: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/27.jpg)
Reactive Monitoring Overview
Reactive monitoring may still be necessary– User calls up– Real time problem diagnosis– Validate ADDM diagnosis– When an alert is raised
Uses new AWR data sources Integrates graphical displays with ADDM Oracle provides an integrated performance
management console using all relevant data sources
![Page 28: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/28.jpg)
EM Product Layout for Performance
Database Home Page
Database Performance Page
Drilldowns
SQL Session
![Page 29: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/29.jpg)
EM Pages LayoutHome Page
Perf Page
Top Session Wait Detail Top SQL
SQL Detail Session Detail
ADDM
ADDM Details
![Page 30: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/30.jpg)
Buffer Busy Waits Case Study
![Page 31: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/31.jpg)
Home Page
Perf Page
Top Session Wait Detail Top SQL
SQL Detail Session Detail
ADDM
ADDM Details
Two Paths
![Page 32: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/32.jpg)
ADDM PathHome Page
Perf Page
Top Session Wait Detail Top SQL
SQL Detail Session Detail
ADDM
ADDM Details
![Page 33: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/33.jpg)
Database Home Page
![Page 34: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/34.jpg)
ADDM HomeHome Page
Perf Page
Top Session Wait Detail Top SQL
SQL Detail Session Detail
ADDM
ADDM Details
![Page 35: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/35.jpg)
ADDM Home
![Page 36: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/36.jpg)
ADDM DetailsHome Page
Perf Page
Top Session Wait Detail Top SQL
SQL Detail Session Detail
ADDM
ADDM Details
![Page 37: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/37.jpg)
ADDM Details
![Page 38: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/38.jpg)
Home Page
Perf Page
Top Session Wait Detail Top SQL
SQL Detail Session Detail
ADDM
ADDM Details
Manual Path
![Page 39: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/39.jpg)
Database Home Page
![Page 40: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/40.jpg)
Database Home Page
![Page 41: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/41.jpg)
Database Home Page
![Page 42: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/42.jpg)
Performance PageHome Page
Perf Page
Top Session Top SQL Wait Detail
SQL Detail Session Detail
ADDM
ADDM Details
![Page 43: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/43.jpg)
Performance Page
![Page 44: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/44.jpg)
Performance Page highlight
![Page 45: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/45.jpg)
Wait Drill DownHome Page
Perf Page
Top Session Wait Detail Top SQL
SQL Detail Session Detail
ADDM
ADDM Details
![Page 46: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/46.jpg)
Wait Drill Down
![Page 47: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/47.jpg)
Wait Drill Down highlight
![Page 48: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/48.jpg)
Wait Drill Down
![Page 49: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/49.jpg)
Wait Drill Down
![Page 50: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/50.jpg)
Wait Drill Down highlight
![Page 51: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/51.jpg)
Wait Drill Down – Top SQL
![Page 52: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/52.jpg)
SQL DetailsHome Page
Perf Page
Top Session Wait Detail Top SQL
SQL Detail Session Detail
ADDM
ADDM Details
![Page 53: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/53.jpg)
SQL Details
![Page 54: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/54.jpg)
Problem Solution: Oracle10g Performance Diagnosis & Tuning are complex
ADDM performs automated problem diagnosis Diagnosis often requires additional data capture
AWR performs capture of workload data Database wide view of operations is lacking
ADDM performs holistic time based analysis Data overload rather than information
EM reports ADDM findings and solutions Misguided tuning efforts
ADDM reports non-problem areas
![Page 55: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/55.jpg)
Conclusion
Oracle 10g revolutionizes performance management
– Built in automatic diagnostic engine– Extensive code instrumentation– Automatic collection of workload information– Proactive performance diagnostics and
recommendations The new Enterprise Manager provides an
integrated performance management console using all relevant data sources
![Page 56: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/56.jpg)
Next Steps….
Recommended hands-on labs– Oracle Database 10g : Manage the Oracle Environment Hands-On
Lab
Campground Demos– Self-Managing Database : Easy Upgrade– Self-Managing Database:Invisible Installation & Deployment– Self-Managing Database: Proactive Performance Management– Self-Managing Database: Automatic Memory Management– Self-Managing Database: Proactive Space Management
Relevant web sites to visit for more information– http://otn.oracle.com/products/manageability/database
![Page 57: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/57.jpg)
Next Steps….
Recommended sessions– The Self-Managing Database: Guided Application & SQL
Tuning (Tuesday, 3:30 PM)– The Self-Managing Database: Automatic SGA Memory
Management (Tuesday, 5:00 PM)– The Invisible Oracle: Deploying Oracle Database in
Embedded Environment (Wednesday, 4:30 PM)– The Self-Managing Database: Proactive Space and Schema
Object Management (Thursday, 8:30 AM)– The Self-Managing Database: Automatic Health Monitoring
(Thursday, 11 AM)
![Page 58: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/58.jpg)
AQ&Q U E S T I O N SQ U E S T I O N S
A N S W E R SA N S W E R S
![Page 59: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/59.jpg)
Reminder – please complete the OracleWorld online session survey
Session id: 40092
Thank you.
![Page 60: The Self-managing Database: Automatic Performance Diagnosis](https://reader036.vdocuments.us/reader036/viewer/2022062305/5681501b550346895dbe033a/html5/thumbnails/60.jpg)