32.2 Query Response Time index (QRTi)

QRTi stands for "Query Response Time index". It is a "quality of service" measurement for each query, and uses the Apdex formula for that calculation: Apdex on Wikipedia.

How QRTi is Defined

The three measurement conditions are "optimum", "acceptable", and "unacceptable", which are defined as:

Table 32.1 QRTi Value Definitions

Type	Default time values	Assigned value	Description	Color
Optimum	100ms	1.00 (100%)	The optimal time frame	Green
Acceptable	4 * Optimum -- 100ms to 400ms	0.50 (50%)	An acceptable time frame	Yellow
Unacceptable	Exceeds Acceptable -- greater than 400ms	0.00 (0%)	An unacceptable time frame	Red

An example calculation

From there, we calculate an average to determine the final QRTi value. For Example, if there are 100 executions of the digested/canonical query, where 60 finished below 100ms (the optimal time frame), 30 between 100ms and 400ms (the acceptable time frame), and the remaining 10 took longer than 400ms (unacceptable time), then the QRTi score is:

( (60 + (30 / 2) + (10*0) ) / 100) = 0.75.

Reading QRTi Values

The queries listed on the Query Analyzer page also have a color-coded pie chart representing a breakdown of the values used in the QRTi calculation; green representing the optimal percentage, yellow the acceptable percentage, and red the unacceptable percentage. You can mouse over the pie chart itself to see the total number of query executions that fell within each category, as well as the percentage of query executions that fell within that group.

So when doing query optimization, you want to start with the ones that have a QRTi visual pie chart that is 100% red, which means that they also have an actual QRTi value of 0. This means that *all* executions of that query took longer than the acceptable time frame (400ms by default). You can then click on the query to get more information, such as the maximum and average query times, the average number of rows examined, the average lock wait time, examine a sample query, look at an example EXPLAIN plan, see if full table scans were done, examine index usage, etc.

You can then work your way up from the queries with a QRTi value of 0, towards those that have a value of 1 (1 meaning that all instances of the query executed within the optimal time frame). Once you get to the point that you no longer have any queries with a QRTi value of less than 1, then you can go into the Query Analysis Reporting Advisor configuration, and adjust the QRTi Threshold (the target time) down, say to 50ms, and start the process all over again.