1 / 58

Performance Tuning: 10 New Active Session History and Automatic Workload Repository Tips

Performance Tuning: 10 New Active Session History and Automatic Workload Repository Tips. Deba Chatterjee Principal Product Manager. 3 Types of Performance Management. Reactive performance management. Reactive Performance Management. Inconsistent Performance

lars
Download Presentation

Performance Tuning: 10 New Active Session History and Automatic Workload Repository Tips

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Performance Tuning: 10 New Active Session History and Automatic Workload Repository Tips Deba Chatterjee Principal Product Manager

  2. 3 Types of Performance Management

  3. Reactive performance management

  4. Reactive Performance Management

  5. Inconsistent Performance Over utilization of system resources High load ad hoc query consuming resources Change in execution plan of query Parallel execution downgrade Comparing Performance Across Two Periods • Performance was fine yesterday, today my application is really slow?

  6. Tip: Compare Period ADDM SQL Commonality Regressed SQL AWR Snapshot Period 1 I/O Bound Undersized SGA Compare Period ADDM Analysis Report AWR Snapshot Period 2 • Full ADDM analysis across two AWR snapshot periods • Detects causes, measure effects, then correlates them • Causes: workload changes, configuration changes • Effects: regressed SQL, reach resource limits (CPU, I/O, memory, interconnect) • Makes actionable recommendations along with quantified impact

  7. Compare Period ADDM: Method • 30% smaller Buffer cache • 10% new SQL • Top SQL increased 45% • Read I/O up 55% • Buffer cache reduction caused read I/O increase

  8. Reactive Performance Management

  9. Database Hung state Blocking Sessions Memory allocation issues Library cache issues Unresponsive Storage (ASM) Interconnect problems Database Hang Analysis • My database is hung? I do not want to bounce it.

  10. Tip: Real-Time ADDM EM Agent Hangs Diagnostic Connection Unresponsive DB JDBC Connection Real-Time Analysis Database • Uses a pre-established diagnostic connection for unresponsive systems • Initiates a standard JDBC connection for real-time analysis • Diagnostic connection collects data without holding latches or running SQL • First intelligent advisor to diagnose problems in real-time as they occur, no matter how sick the system is Deadlocks Latches ADDM Analysis

  11. Real-Time ADDM • Real-time analysis of hung or slow database systems • Holistically identify global resource contentions and deadlocks • Quantified performance impact • Precise, actionable recommendations • Provide cluster-wide analysis for RAC

  12. Reactive Performance Management

  13. I enabled parallel query, yet this query is taking so long. Can you take a look? Parallel Downgrades Uncontrolled parallel execution Parallel Server availability Object level settings Session level settings SQL Performance Analysis

  14. Tip: Real-Time SQL Monitoring Insert Executed with Parallel Hint

  15. Real-Time SQL Monitoring Parallel Tab • Parallel Coordinator busy for the entire duration!!

  16. Real-Time SQL Monitoring Enabled Parallel DML • Parallel Slaves busy for the entire duration!!!

  17. My simple query takes so long. What’s wrong with the database? SQL Performance Problems Statistics Resources Application issues Parallelism Initialization parameters SQL Performance Analysis

  18. Tip: Real-Time SQL Monitoring SQL with Count and Group By

  19. Real-Time SQL Monitoring SQL with Count and Group By

  20. Real-Time SQL Monitoring PGA Size Increased

  21. Reactive Performance Management

  22. Proactive performance management

  23. Proactive Performance Management

  24. Can you trace my program ? What is wrong with tracing ? A very reactive way of looking at problems Overhead of writing data to trace files Programs we want to trace are usually the ones with issues Impacts the performance of the production system Reactive Tracing of long running programs ?

  25. Monitoring Complex Database Operations What’s Really Happening inside the Database Challenge Solution • SQL & PL/SQL Monitoring only monitors a single execution • How does a DBA monitor a composite operation such as a batch job? • Real-Time Database Operations Monitoring • Benefit: Allows DBAs to analyze and tune complex composite DB operations

  26. Real-Time Database Operations Monitoring Know What’s Happening and Resolve Issues Faster. • Database monitoring of application jobs • Grouping of SQLs, sessions for the application jobs • Key scenarios: ETL operations, Quarter End Close jobs • Real-time monitoring driven by application specified tagging • Oracle Data Pumpjobs automatically monitored • Tagging ability in PL/SQL, OCI, JDBC • Avoids the overhead of SQL*Trace • Visibility of top SQL statements, system and session performance metrics

  27. Naming a Database Operation How to Setup a DBOP ? Naming or Bracketing Tagging IMPLICIT (For Java & OCI) EXPLICIT DBOP (Tag) BEGIN_OPERATION SQL PL/SQL Blocks … SQL SQL SQL PL/SQL Blocks … SQL SQL END_OPERATION

  28. Monitor Composite Database Operations • Oracle Database 11g: Support for simple DB operations • PL/SQL procedures/functions • Oracle Database 12c: NEW support for composite operations • Session(s) activity between 2 points of time defined by application code or DBA • For example; SQL*Plus script, batch job, or ETL processing • At most one DBOP per DB session

  29. Proactive Performance Management

  30. What happened last night the batch job took twice the time to finish ? No way to detect transient issues We look at AWR data Averaged out over the snapshot window On-disk ASH Data Sampled every 10 seconds Very difficult to detect such issues in the “past” Analyzing Transient Performance Problems

  31. Automatic Performance Diagnostics The ADDM Family: A Continuous Evolution in Database Performance Management ComparePeriod ADDM ComparePeriod ADDM Real-Time ADDM Real-Time ADDM Enhanced Real-Time ADDM ADDM ADDM • Diagnose persistent performance issues • Uses AWR snapshots • Automatically runs every hour • In-depth performance comparison across two periods • Uses AWR data • Manually triggered • Hung or extremely slow databases • Uses a normal and diagnostic mode connection • Manually triggered • Proactively detect & diagnose performance spikes • Uses in-memory data • Automatically runs every 3 seconds

  32. Enhanced Real-Time ADDM • Database self-monitors for serious performance issues • Proactive problem detection & analysis • Very light weight (in memory, latchless) check runs every 3 seconds • When detects bad performance trends, triggers further analysis • Analyzes High CPU, I/O spikes, memory, interconnect, hangs, deadlocks • Identifies a problem before it threatens application performance • For current spikes, Real-Time ADDM can be manually triggered • For short duration (5-min) performance spikes, i.e. transient, high impact problems • Actionable advice for critical issues • Richer data set available for analysis • Reports (analysis and data) stored in AWR for historical analysis

  33. Triggers for Further Analysis

  34. Proactive Performance Management

  35. The SQL Response Metric crossed the warning threshold. What is wrong? Several factors can impact SQL Response time Increased or unusual load on system Hardware Issues Runaway queries consuming system resources Changes in execution plans Missing or stale object statistics Need a mechanism to quickly analyze in-memory performance data Understanding Workload Profile

  36. Database Response Time Analysis—AWR • AWR top 5 section shows the Wait Class which contributes most to DB wait time • Objects involved in TX row lock contention can be identified in Segment Statistics section of AWR Foreground Wait Class section in AWR to see distribution of DB waits over Waits classes

  37. From AWR to ASH • ASH report for the period of increase of Application waits will show the same waits as AWR • Can I get the Application Module which suffered from this type of contention ?

  38. Extracting More Data From ASH Identify SQL statements and sessions impacted by waits on “Application” Wait Class

  39. Extracting More Data From ASH Get a list of blocking sessions and DB objects !

  40. Understanding Workload Profile • Graphical ASH report for advanced analysis • Provides visual filtering for recursive drill-downs • Select any time period for analysis • Analyze performance across many dimensions • Different visualizations: Stacked chart or Tree Map • Collaborate with others using Active Reports

  41. Proactive Performance Management

  42. Preventive performance management

  43. Preventive Performance Management

  44. Case Study DATABASE MANAGEMENT 187 Million Members $252 Million in Revenue800 Oracle Databases 1,400 Applications FOCUS ON Preventive Performance Management • Challenge: • LinkedIn’s ERP systems were being upgraded from DB 10g to DB11g • Presence of a large amount of custom code • Limited time frame to complete the upgrade. • Management concern about System performance • Initial testing showed no major problems/concerns • A week before go-live several potential showstopper performance issues were noticed. • Re-writing or tuning several pieces of code was not feasible in a short window of time. • Decision to use either SQL Profiles or Baselines to regress to the DB10g plan in the interim • Using Oracle Enterprise Manager: • Used EM to regress back to the old plan to run a job that calls the slow performing SQL • Used SQL Tuning Advisor to drilling down in to the session and identify the SQL ID • You can compare the explain plan and see the new explain plan in the same window • Click through guided wizard to implement the SQL profile • DONE!!!

  45. SQL Tuning Advisor SQL Profiling Statistics Analysis Access Path Analysis SQL Restructure Analysis Alternative Plan Analysis Parallel Query Analysis Gather Missing or Stale Statistics Create a SQL Profile Add Missing Access Structures Modify SQL Constructs Adopt AlternativeExecution Plan (11.2) Create Parallel SQL Profile (11.2) SQL SQL Tuning Advisor SQL Automatic Tuning Optimizer • Analyzes statistics for accuracy • Recommends SQL Profiles for transparent application tuning • Suggest access structures and alternate SQL to speed up query execution • Identifies alternative execution plans using real-time and historical performance data to recover from plan regression • Recommends appropriate degree of parallelism for best performance Administrator Comprehensive SQL Tuning Recommendations

  46. Preventive Performance Management

  47. We are going through a schema consolidation, how can I ensure one schema / user will not run away with all my system resources? Database resource manager directives prevent a single user session to run away with all resources Create a resource allocation strategy Allocate appropriate CPU and I/O (Exadata) across consumer groups Ensure Optimal Resource Allocation

  48. Allocating Resources in the Database

  49. Tip: Setting up Resource Manager in Oracle Enterprise Manager • Extremely simple to manage resource plans using Enterprise Manager UI

More Related