Loading in 2 Seconds...
Loading in 2 Seconds...
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
Performance Tuning: 10 New Active Session History and Automatic Workload Repository Tips Deba Chatterjee Principal Product Manager
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?
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
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
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.
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
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
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
Tip: Real-Time SQL Monitoring Insert Executed with Parallel Hint
Real-Time SQL Monitoring Parallel Tab • Parallel Coordinator busy for the entire duration!!
Real-Time SQL Monitoring Enabled Parallel DML • Parallel Slaves busy for the entire duration!!!
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
Tip: Real-Time SQL Monitoring SQL with Count and Group By
Real-Time SQL Monitoring SQL with Count and Group By
Real-Time SQL Monitoring PGA Size Increased
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 ?
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
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
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
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
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
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
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
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
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
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 ?
Extracting More Data From ASH Identify SQL statements and sessions impacted by waits on “Application” Wait Class
Extracting More Data From ASH Get a list of blocking sessions and DB objects !
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
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!!!
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
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
Tip: Setting up Resource Manager in Oracle Enterprise Manager • Extremely simple to manage resource plans using Enterprise Manager UI