Tuning SQL with STATSPACK & AWR Oracle Tips by Burleson Consulting

For complete details on tuning SQL with STATSPACK and AWR reports are in my book "Oracle Tuning: The Definitive Reference".  Also see this case study in tuning SQL with STATSPACK.

---

Using a STATSPACK AWR report to tune SQL

Most of a STATSPACK/AWR report gives system-level statistics for the elapsed time period, but the SQL section displays specific SQL statements along with important execution details.  The report is displayed with repeating groups of SQL statements displayed in descending order by metric, with the number of SQL statements in the STATSPACK report being governed by thresholds. 

SQL> desc statspack

 

PROCEDURE MODIFY_STATSPACK_PARAMETER

 

 Argument Name         Type          In/Out Default?

 --------------------- ------------- ------ --------

 I_DBID                NUMBER        IN     DEFAULT

 I_INSTANCE_NUMBER     NUMBER        IN     DEFAULT

 I_SNAP_LEVEL          NUMBER        IN     DEFAULT

 I_SESSION_ID          NUMBER        IN     DEFAULT

 I_UCOMMENT            VARCHAR2      IN     DEFAULT

 I_NUM_SQL             NUMBER        IN     DEFAULT

 I_EXECUTIONS_TH       NUMBER        IN     DEFAULT

 I_PARSE_CALLS_TH      NUMBER        IN     DEFAULT

 I_DISK_READS_TH       NUMBER        IN     DEFAULT

 I_BUFFER_GETS_TH      NUMBER        IN     DEFAULT

 I_SHARABLE_MEM_TH     NUMBER        IN     DEFAULT

 I_VERSION_COUNT_TH    NUMBER        IN     DEFAULT

 I_ALL_INIT            VARCHAR2      IN     DEFAULT

 I_PIN_STATSPACK       VARCHAR2      IN     DEFAULT

 I_MODIFY_PARAMETER    VARCHAR2      IN     DEFAULT

The default threshold values for SQL display in a STATSPACK report are:

End Buffer Gets Threshold:     10000

End Disk Reads Threshold:      1000

End Executions Threshold:       100

End Parse Calls Threshold:      1000

End Sharable Memory Threshold:  1048576

Structure of the SQL Section of a STATSPACK AWR report

The SQL section of a STATSPACK report is like this, with the number of repeating SQL statements governed by the STATSPACK collection thresholds:

 

 

 

 

 

 

 

 

 

Understanding the SQL execution metrics in a STATSPACK report

The metrics for each SQL statement include many of the same metrics that we see in the v$sql and dba_hist_sqlstat tables:

• Buffer_gets – This is the count of “logical I/O”, data block requests.  Excessive buffer gets can be due to a sub-optimal execution plan (unnecessary full-table scan), or a tiny db_block_size.  Ideally, comparing the numbers of rows returned to the buffer gets is a good metric, but it’s not available in a STATSPACK report.
   
• Executions – This is the number of times that this SQL statement executed during the elapsed time period.
   
• Gets_per_execution – This is the number of logical reads per SQL execution, a measure of the number of block touches required to service the SQL and return the desired rows.
   
• Pct_total (%total) – This is the percentage of the total metric (gets, reads, executions) for that particular SQL statement.  Note that resources reported for PL/SQL includes the resources used by all SQL statements called within the PL/SQL code.  As individual SQL statements are also reported, it is possible and valid for the summed total % to exceed 100.
   
• cpu_time – This is the CPU time consumed for this SQL statement.
   
• elapsed_time – This is the total number of wall-clock seconds.
   
• hash_value – This is the hash value to the actual SQL statement, not useful.

Correlating SQL statements to STATSPACK report metrics

By itself, the “Top SQL” section alone cannot reveal which particular SQL statements have sub-optimal execution. This determination is dependent on other factors, and we can only infer sub-optimal SQL execution from associated values on other parts of the STATSPACK/AWR report.

Also, the STATSPACK report truncates all SQL to 320 characters. Hence, we may not have the al-important WHERE clause, and in some cases if many long column names being selected, we cannot even see the FROM clause. For example, this statement does not tell us anything:

select * into :b1,:b2,:b3,:b4,:b5,:b6:i6,:b7:i7,:b8:i8,:b9:i9,:
b10:i10,:b11:i11,:b12:i12,:b13:i13,:b14:i14,:b15:i15,:b16:i16,:b
17:i17,:b18:i18,:b19:i19,:b20:i20,:b21:i21,:b22:i22,:b23:i23,:b2
4:i24,:b25:i25,:b26:i26,:b27:i27,:b28:i28,:b29:i29,:b30:i30,:b31
:i31,:b32:i32,:b33:i33,:b34:i34,:b35:i35,:b36:i36,:b37,:b38,:b39

The trick to correlating the behavior of individual SQL statements to the system-level statistics in a STATSPACK report is tricky.

For example, if the STATSPACK report shows a high-level of system-wide parses, the Top SQL should be checked by parse number section to find the particular statements with a large number of parses.
 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.