Jerry Held

1
(No Transcript)
2
The Self-managing DatabaseAutomatic Performance
Diagnosis
Session id 40092

• Graham WoodKyle Hailey
• Oracle Corporation

3
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

4
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

5
Oracle Database 10g Self-Managing Database
Application SQL Management
Storage Management
System Resource Management
Space Management
Backup Recovery Management
Database Management
Database Control
Intelligent Infrastructure
6
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

Application SQL Management
Storage Management
System Resource Management
Space Management
Backup Recovery Management
Database Management
Intelligent Infrastructure
7
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 components

Application SQL Management
Storage Management
System Resource Management
Space Management
Backup Recovery Management
Database Management
Intelligent Infrastructure
Proactive and effective tuning
8
Performance Monitoring Solutions
In memorystatistics
SGA
Snapshots
Alerts
ADDM
Proactive Monitoring
ADDM Results
Workload Repository
Reactive Monitoring
9
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

10
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

11
New Base Statistics Extensive code
instrumentation
Parse
Conn Mgmt
Java Exec

• Time Model (vsys_time_model)
• Db time
• Connection Management (logon, logoff)
• Parse (hard, soft, failed,..)
• SQL, PLSQL and Java execution times
• Wait Model (vsystem_event)
• 700 different wait events
• 12 wait classes
• OS Stats (vosstat)
• CPU Memory

PLSQL Exec
SQL Exec
12
New SQL Statistics

• SQL_id more unique hash value
• SQL statement statistics
• Wait class time
• PLSQL time
• Java time
• Sampled bind values (vsql_bind_capture)
• Efficient top SQL identification using ?s in the
  kernel, by 6 dimensions
• CPU
• Elapsed
• Parse
• ...

13
Active Session History (ASH)

• Samples active sessions every second into memory
  (vactive_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)

Sampled history of vsession_wait
14
Performance Monitoring Solutions
In memorystatistics
SGA
Snapshots
Alerts
ADDM
Proactive Monitoring
ADDM Results
Workload Repository
Reactive Monitoring
15
Automatic Database Diagnostic Monitor (ADDM)

• Performance expert in a box
• Integrate all components together
• Automatically provides database-wide performance
  diagnostic, including RAC
• Real-time results using the Time Model
• Provides impact and benefit analysis
• Provides Information vs. raw data
• Runs proactively

16
ADDMs Architecture

• 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

Snapshots in Automatic Workload Repository
Automatic Diagnostic Engine
Automatic Diagnostic Engine
High-load SQL
IO / CPU issues
RAC issues
SQLAdvisor
System Sizing Advice
Network DB config Advice
17
ADDM Methodology

• Top 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

18
ADDM Methodology
Problem classification system Decision tree
based on the Wait Model and Time Model Stats


Buffer Busy
RAC Waits
System Wait

Parse Latches
Concurrency
Buf Cache latches
IO Waits
Root Causes
Symptoms
19
ADDM Methodology
Problem classification system Decision tree
based on the Wait Model and Time Model Stats


Buffer Busy
RAC Waits
System Wait

Parse Latches
Concurrency
Buf Cache latches
IO Waits
Non - Problems areas.
20
Top Performance Issues
Not rocket science anymore
Top SQL
IO Issues Bandwidth, Hot Files
Parsing hard, soft, failed
Configuration issues Log file sizing Log buffer sizing Archiving MTTR settings.
Application usage
21
Top Performance Issues
Not diagnosable using Statspack data
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


22
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

23
Database Home Page
24
ADDM Findings
25
ADDM Recommendations
26
Performance Diagnostic Before and Now
Scenario Hard parse problems

• Before
• Examine system utilization
• Look at wait events
• Observe latch contention
• See wait on shared pool and library cache latch
• Review vsysstat
• See parse time elapsed gt parse time cpu and
  hard parses greater than normal
• Identify SQL by..
• Identifying sessions with many hard parses and
  trace them, or
• Reviewing vsql 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

27
Manually running ADDM

• Manual snapshot automatically invokes ADDM
• ADDM Analysis across any 2 snapshots

28
Performance Monitoring Solutions
In memorystatistics
SGA
Snapshots
Alerts
ADDM
Proactive Monitoring
ADDM Results
Workload Repository
Reactive Monitoring
29
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

30
EM Product Layout for Performance
31
EM Pages Layout
32
Buffer Busy Waits Case Study
33
Two Paths
34
ADDM Path
35
Database Home Page
36
ADDM Home
37
ADDM Home
38
ADDM Details
Home Page
Perf Page
ADDM
Top Session
Wait Detail
Top SQL
ADDM Details
SQL Detail
Session Detail
39
ADDM Details
40
Manual Path
41
Database Home Page
42
Database Home Page
43
Database Home Page
44
Performance Page
45
Performance Page
46
Performance Page highlight
47
Wait Drill Down
48
Wait Drill Down
49
Wait Drill Down highlight
50
Wait Drill Down
51
Wait Drill Down
52
Wait Drill Down highlight
53
Wait Drill Down Top SQL
54
SQL Details
55
SQL Details
56
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

57
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

58
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 DatabaseInvisible 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/datab
  ase

59
Next Steps.

• Recommended sessions
• The Self-Managing Database Guided Application
  SQL Tuning (Tuesday, 330 PM)
• The Self-Managing Database Automatic SGA Memory
  Management (Tuesday, 500 PM)
• The Invisible Oracle Deploying Oracle Database
  in Embedded Environment (Wednesday, 430 PM)
• The Self-Managing Database Proactive Space and
  Schema Object Management (Thursday, 830 AM)
• The Self-Managing Database Automatic Health
  Monitoring (Thursday, 11 AM)

60
A
61
Reminder please complete the OracleWorld
online session surveySession id 40092Thank
you.
62
(No Transcript)