A few weeks back on the weekend just before I went on call we got a complaint about slowness on an important set of reports. I worried that the slowness of these reports would continue during my support week so I tried to figure out why they were slow. I reviewed an AWR report for the 24 hours when the reports were running and found a simple query against a tiny table at the top of the “SQL ordered by Elapsed Time” report:
SQL Id Elapsed (s) Execs
------------- ------------- ------------
77hcmt4kkr4b6 307,516.33 3.416388E+09
I edited the AWR report to show just elapsed seconds and number of executions. 3.4 billion executions totaling 307,000 seconds of elapsed time. This was about 90 microseconds per execution.
The previous weekend the same query looked like this:
SQL Id Elapsed (s) Execs
------------- ------------- ------------
77hcmt4kkr4b6 133,143.65 3.496291E+09
So, about the same number of executions but less than half of the elapsed time. This was about 38 microseconds per execution. I never fully explained the change from week to week, but I found a way to improve the query performance by adding a new index.
The plan was the same both weekends so the increase in average execution time was not due to a plan change. Here is the plan:
SQL_ID 77hcmt4kkr4b6
--------------------
SELECT DIV_NBR FROM DIV_RPT_GEN_CTL WHERE RPT_NM = :B1 AND
GEN_STAT = 1
Plan hash value: 1430621991
------------------------------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| DIV_RPT_GEN_CTL | 1 |
| 2 | INDEX RANGE SCAN | DIV_RPT_GEN_CTL_U1 | 1 |
------------------------------------------------------------------
I found that the table only had 369 rows and 65 blocks so it was tiny.
The table’s only index was on columns RPT_NM and RPT_ID but only RPT_NM was in the query. For the given value of RPT_NM the index would look up all rows in the table with that value until it found those with GEN_STAT=1. I suspect that on the weekend of the slowdown that the number of rows being scanned for a given RPT_NM value had increased, but I can not prove it.
I did a count grouping by the column GEN_STAT and found that only 1 of the 300 or so rows had GEN_STAT=1.
SELECT GEN_STAT,count(*)
FROM DIV_RPT_GEN_CTL
group by GEN_STAT;
GEN_STAT COUNT(*)
---------- ----------
1 1
2 339
0 29
So, even though this table is tiny it made sense to add an index which included the selective column GEN_STAT. Also, since the reports execute the query billions of times per day it made sense to include the one column in the select clause as well, DIV_NBR. By including DIV_NBR in the index the query could get DIV_NBR from the index and not touch the table. The new index was on the columns RPT_NM, GEN_STAT, and DIV_NBR in that order.
Here is the new plan:
SQL_ID 77hcmt4kkr4b6
--------------------
SELECT DIV_NBR FROM DIV_RPT_GEN_CTL WHERE RPT_NM = :B1 AND
GEN_STAT = 1
Plan hash value: 2395994055
-------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | INDEX RANGE SCAN| DIV_RPT_GEN_CTL_U2 | 1 |
-------------------------------------------------------
Note that it uses the new index and does not access the table. Here is the part of the AWR report for the problem query for last weekend:
SQL Id Elapsed (s) Execs
------------- ------------- ------------
77hcmt4kkr4b6 84,303.02 4.837909E+09
4.8 billion executions and only 84,000 seconds elapsed. That is about 17.4 microseconds per execution. That is less than half of what the average execution time was the weekend before the problem started.
The first Monday after we put the index in we found that one of the slow reports had its run time reduced from 70 minutes to 50 minutes. It was great that we could improve the run time so much with such a simple fix.
It was a simple query to tune. Add an index using the columns in the where clause and the one column in the select clause. It was a tiny table that normally would not even need an index. But, any query that an application executes billions of times in a day needs to execute in the most efficient way possible so it made sense to add the best possible index.
– Bobby
