Case Study
  Introduction to Multidimensional Analysis of AWR & ASH data
What is multidimensional analysis
Techniques to probe the correlational relationships among dimensions
An example of using multidimensional analysis to solve performance bottleneck puzzle
Crosscheck multiple dimensions to separate symptoms and root cause
  Performance Troubleshooting Scenarios
Use DB Time & Average Active Session to detect performance bottleneck
Who generated the largest amount of redo?
Troubleshoot wait event 'log file sync'
Module-based SQL tuning strategy
Solve locking and blocking puzzle
  Capacity Planning Scenarios
Display DB Time in graph
Display IOPS/MBPS in graph
Display database/tablespace size growth in graph
  Understanding SQL Stats
Two Analytical Models for SQL Stats
SQL with SQL Plan change
SQL with high Cluster Wait Time
SQL with high Application Wait Time
SQL with high Concurrency Wait
SQL with high Buffer Gets
SQL with high Disk Reads
SQL with high Executions
SQL with high Parse Calls
SQL with high CPU time - Merge & Sort Operations
SQL with high CPU time - Merge Join Cartesian
SQL with high CPU time - PL/SQL Function
SQL with no Row Processed
SQL Stats pattern for JDBC batch processing
  SQL Scripts Samples

On the graphic user interface of DBspeed, you can click ‘Display SQL’ button to view queries it uses to data mining AWR & ASH data. All queries run by DBspeed are ‘SELECT’ statements and read-only permission is sufficient. You can take a look at some sample queries here.

Two Analytical Models for SQL Stats

This article was recently published at IOUG SELECT Journal Q4 2012.

"SQL Stats is important performance data that should be checked during the performance analysis of a SQL statement. This article describes the method of using time analysis and resource consumption analysis together to study SQL Stats, verifying whether a SQL statement has a performance issue, what if any is the possible root cause of the SQL performance issue, and what are the corresponding SQL tuning strategies. It also illuminates a series of SQL Stats patterns in Oracle 11g."

CPU Spikes and Top CPU SQL

This article was published at IOUG SELECT Journal Q2 2011. It describes two case studies of how to use data mining queries against AWR & ASH to solve CPU spike issues on database servers.

DBspeed includes all the sql scripts used by this article, and much more.

DBspeed I
for Oracle Database


DBspeed II
for Oracle RAC Database


  Windows 2000/XP/Vista/7/8
  Mac OS X 10.6 and newer
Request trial version contact

Copyright © 2011 Actrace. All Rights Reserved.
Home | Product | Case Study | Support | Order | Contact Us