In PI, SQL statement Response Time is a calculated metric that is derived from internal DB metrics that SQL PI has collected and which have been analyzed by the system. Thus, Average SQL Response Time is a calculated metric in PI, as follows:
Average SQL Response Time = SQL statement duration time / SQL statement number of executions
SQL duration time is the elapsed time since the statement has been opened. In PI, it is represented by (Statement Active Time + Statement Idle Time). Active Time is the value representing the time frame in which the statement was not idle, meaning it is experiencing a Wait Event e.g. the SQL execution is in I/O wait, CPU wait, CPU Usage etc.
SQL statement number of executions is the value representing how many times the statement has been executed during its duration time.
From the above explanation, it can observed clearly, the Average SQL Response Time metric accuracy is really dependent on two factors - detection of SQL duration time and Number of executions of the SQL statement in a given History period. Both of these factors are dependent on sampling rate in a heavily loaded Monitored Database environment. As we know, PI samples Database queries once per second (by default). However, the very nature a "sampling" rate means some of the given SQL executions may start between the actual samples which means that the SQL statement duration time may not be accurate. If one or more executions complete between the samples being collected, it is possible that number of executions detected for a given SQL statement may not be accurate.
Also, PI reported DB metrics should always be considered in the context of History period that is selected in the PI dashboard.
To give a simplified scenario,
History Top Statements:
History tab always displays the top SQL statements in a given time interval. PI samples data per time intervals, which for the simplicity of the explanation, we will consider as 15 minutes each (other time resolutions would be 1 minute, 1 hour, 6 hours etc.).
In one hour, then, we will have the following intervals:
- 0-15 minutes (interval 1)
- 15-30 minutes (interval 2)
- 30-45 minutes (interval 3)
- 45-60 minutes (interval 4)
In each interval, PI will keep the most active sql's ran in terms of sql's active time: time measured for a sql doing either CPU or wait events. Each interval will keep the 20 most active sql's (see definition as above).
Let's think of a scenario where a user runs the same sql every 15 minutes, one execution per each interval. PI will keep the sql in the interval Top 20, if the sql active time was significant compared to all other sql's ran at the same interval. There could be a situation where out of 4 intervals, the sql discussed was kept in only 3 intervals (in 1 interval it was not kept in PI top 20 as there were more active sql's).
In that kind of scenario, PI will report that the sql number of execution is 3 and not 4 (as it appeared in PA Top 20 for just 3 intervals) and accordingly will PI report the sql Response Time metric (which will average 3 samples instead of 4), and the Total Duration Time (which will be shorter because it calculates 3 intervals and not 4 intervals).
In Summary, PI will focus on the most significant SQL statements executed in the Database, and is not an auditing tool. The key point to stress in this explanation is that in specific scenarios where Database being monitored is busy and is executing number of SQL statements concurrently, it is possible that PI may miss some executions when the executions number exceeds sampling rate. If that happens, then Average SQL Response Time may vary according to the number of executions actually detected.
However, the activity associated with the SQL in History (Wait Events detected) is still accurate as it is derived from the session level and not related to the SQL execution number.