1. Skip to Menu
  2. Skip to Content
  3. Skip to Footer>

Expert Sessions!

Preliminary Analysis - Expensive SQL Statement

Tuesday, 31 July 2012 10:48

Written by Prakash Palani

Print E-mail

 

When it comes to identifying Expensive SQL Statements, my approach in  identifying the statements is to use the preliminary analysis as indicated in this article, Steps indicated in the below flowchart help you to take step-by-step approach to identify the expensive SQL statements.  If you are able to identify the expensive SQL statements, then it is worthwhile to proceed further with Detailed Analysis towards database, if not, then the focus should be turned on different areas such as Workprocess, Hardware Bottleneck, etc.,.

SQL Trace (ST05) and SQL Statistics (ST04) are the primary functions which gives clear indication on how an SQL statement is performed in terms of runtime, index, statistics, where clause, etc., below flow chart outlines the steps needed to identify the problematic statement.

 

  1. Complaints from an end user that there is a performance issue
    1. Use SM50 to identify what exactly is happening during the runtime of a program which is expensive and then proceed with ST05
  2. You are able to see a decline in average response time trend
    1. Use ST03 to identify the expensive component of the response time, if the expensive component is database time, then proceed with ST05.
  3. In ST04, you are able to see high number of Buffer Gets / Disk Reads
    1. In this case, detailed analysis can be done in ST04 itself.

 

Quick Tips: An easy way to identify the expensive statement in ST04

 

  • A statement with disk reads that amount more than 5% of the total physical reads
  • A statement with buffer gets that amount more than 5% of the total reads

In the upcoming articles, we will get into the details of ST05 and ST04.

 

{fcomment} {flike}
Share
Preliminary Analysis - Expensive SQL Statement