Two Analytical Models for SQL Stats

SQL Stats, also called as ‘SQL execution statistics’, refers to the performance data contained in view V$SQLSTATS and DBA_HIST_SQLSTAT. QL Stats includes two basic categories of information about how a SQL statement runs on database:

  •   How long a SQL statement has been running and where it spends most of its time
  •   How much resources a SQL statement has consumed and how large is the result set it returns

The differentiation between these two categories of information lays out the foundation of our two analytical models for SQL Stats: time analysis and resource consumption analysis. The ultimate goal of our SQL performance tuning is to ensure SQL statements to use the least time and resources to accomplish their tasks. Time analysis and resource consumption analysis provide two different angles to verify whether a SQL statement has performance bottleneck.

 Case Study Summary


 Time analysis

The elapsed time, also called as 'response time', is the total time a SQL statement uses for parse, execute, and fetch. The elapsed time can be broke down into the following components:

  •   CPU time
  •   cluster wait time (for RAC database only)
  •   user IO wait time
  •   concurrency wait time
  •   application wait time
  •   PL/SQL execution time
  •   Java execution time

 Resource consumptions analysis

The resources a SQL statement consumes primarily include the following components:

  •   CPU time
  •   rows processed
  •   buffer gets
  •   disk reads
  •   executions
  •   parse calls

 Relationship between two analytical models

  •   It is recommend to do time analysis first to have a clear idea of the impact of wait events.
  •   Resource consumption analysis can provide more diagnostic information on CPU and I/O usage.

 Guidelines for using two models

  •   SQL Stats is performance data collected at database instance level. One SQL statement’ variables can be reviewed in the context of their database-wide rankings. Oracle’s AWR report has 'Top SQL' section displaying several lists of SQL statements ranking with respect to variables such as 'SQL ordered by Elapsed Time', 'SQL ordered by Buffer Gets', etc. Those lists provide a good entry point of which SQL statements we should start our analyses.

  •   SQL Stats is time-based data. One SQL statement’ variables can be reviewed in the context of continuous time frame. We believe that when a SQL statement is having performance issue, its performance data will be skewed compared to those at normal time. We can understand SQL Stats better if the changes in variables over time can be revealed.

  •   Each analytical model involves multiple variables. One SQL statement’ variables can be reviewed in the context of their correlational relationships. Some variables are driving variables. Others are dependent variables and usually change in a synchronized manner with driving ones.

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