Transcript Jerry Held
Session id: 40092 The Self-managing Database: Automatic Performance Diagnosis Graham Wood Kyle Hailey Oracle Corporation 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 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 Oracle Database 10g – Self-Managing Database Application & SQL Management Storage Management Database Control System Resource Management Database Backup & Recovery Management Management Intelligent Infrastructure Space Management Intelligent Infrastructure Automatic Workload Repository Advisory Application & SQL Management Infrastructure – – Storage System Resource Server-generated Alert Management Management Infrastructure Automatic Maintenance Task Space Backup & Recovery Infrastructure Management Database Management Management Automatic Workload IntelligentRepository Infrastructure “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 Automatic Database Diagnostic Monitor (ADDM) Application & SQL Management Storage Management System Resource Management Backup & Recovery Database Management Space Management Management Intelligent Infrastructure Performance Diagnostic engine in the database Automatically diagnoses performance problems Provides Root Cause Analysis with recommended solutions Identifies non-problems areas Integrates all components Proactive and effective tuning Performance Monitoring Solutions In memory statistics SGA Snapshots Alerts ADDM Proactive Monitoring ADDM Results Workload Repository Reactive Monitoring 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 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 New Base Statistics Extensive code instrumentation Time Model (v$sys_time_model) – – – – – Java Exec Db time Connection Management (logon, logoff) Parse (hard, soft, failed,..) SQL, PLSQL and Java execution times Wait Model (v$system_event) – Parse 700 different wait events 12 wait classes OS Stats (v$osstat) – CPU + Memory Conn Mgmt PLSQL Exec SQL Exec 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 ... Active Session History (ASH) • 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) Sampled history of v$session_wait Performance Monitoring Solutions In memory statistics SGA Snapshots Alerts ADDM Proactive Monitoring ADDM Results Workload Repository Reactive Monitoring ADDM’s Architecture Snapshots in Automatic Workload Repository AutomaticDiagnostic DiagnosticEngine Engine Automatic High-load SQL IO / CPU issues SQL Advisor System Sizing Advice RAC issues 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 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 ADDM Methodology Problem classification system Decision tree based on the Wait Model and Time Model Stats …… …… RAC Waits Buffer Busy System Wait Concurrency IO Waits Symptoms Parse Latches …… Buf Cache latches Root Causes ADDM Methodology Problem classification system Decision tree based on the Wait Model and Time Model Stats …… …… RAC Waits Buffer Busy System Wait Concurrency IO Waits Non - Problems areas. Parse Latches Buf Cache latches …… 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 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 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 Database Home Page ADDM Findings ADDM Recommendations 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 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 Performance Monitoring Solutions In memory statistics SGA Snapshots Alerts ADDM Proactive Monitoring ADDM Results Workload Repository Reactive Monitoring 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 EM Product Layout for Performance Database Home Page Database Performance Page Drilldowns SQL Session EM Pages Layout Home Page Perf Page Top Session ADDM Wait Detail SQL Detail Top SQL Session Detail ADDM Details Buffer Busy Waits Case Study Two Paths Home Page Perf Page Top Session ADDM Wait Detail SQL Detail Top SQL Session Detail ADDM Details ADDM Path Home Page Perf Page Top Session ADDM Wait Detail SQL Detail Top SQL Session Detail ADDM Details Database Home Page ADDM Home Home Page Perf Page Top Session ADDM Wait Detail SQL Detail Top SQL Session Detail ADDM Details ADDM Home ADDM Details Home Page Perf Page Top Session ADDM Wait Detail SQL Detail Top SQL Session Detail ADDM Details ADDM Details Manual Path Home Page Perf Page Top Session ADDM Wait Detail SQL Detail Top SQL Session Detail ADDM Details Database Home Page Database Home Page Database Home Page Performance Page Home Page Perf Page Top Session Top SQL SQL Detail ADDM Wait Detail Session Detail ADDM Details Performance Page Performance Page highlight Wait Drill Down Home Page Perf Page Top Session ADDM Wait Detail SQL Detail Top SQL Session Detail ADDM Details Wait Drill Down Wait Drill Down highlight Wait Drill Down Wait Drill Down Wait Drill Down highlight Wait Drill Down – Top SQL SQL Details Home Page Perf Page Top Session ADDM Wait Detail SQL Detail Top SQL Session Detail ADDM Details SQL Details 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 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 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 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) QUESTIONS ANSWERS Reminder – please complete the OracleWorld online session survey Session id: 40092 Thank you.