[Update]: there is now a non-Diagnostics Pack version here.
[Update]: version 1.6 is now out. More on this blogpost.
Two weeks ago I was asked to help solve urgent performance problems on a large database. What SQL statement caused the problems, was completely clear from Active Session History. But what had happened wasn’t so clear. My collegues told me the query had performed pretty well, but not anymore. I ran my SQL performance script, sql_sql_id_html.sql and analyzed the report. I looked at the execution plan and at the history of the response time from AWR data. Luckily I put a column with the plan hash in that table. I immediately saw that until October 7th a different plan hash had been used. And the response time was, say, about 1000x faster then.
Also, my script showed the execution plan from the old plan hash. Now the old execution plan didn’t look all that great, but the cost of 40 was much lower than the cost of the new plan: 1702K. Even though the new plan used an index where the old one did not. For what it’s worth. While analyzing the problem, speculation between the project and collegue DBAs went back and forth. Eventually I found out (from statistics history) that new statistics had been gathered on October 7th and that the number of distinct values in one of the searched for columns changed dramatically that day. So I thought: “This is stupid. I always end up looking at the statistics of the tables and indexes anyway. If I can put something in my script to show statistics, I can end these speculations and discussions before they begin”. But how?
As far as I know, Oracle doesn’t have a view that shows you a neat list of tables and indexes that are accessed by a certain plan. So how to get them? Well, invention is the mother of necessity apparently, because the next day I hatched a scheme. I used the results of a basic output of dbms_xplan and for every line of that output, I created a string between the third and fourth pipe sign. I trimmed the spaces before and after the name of the object and presto, I had a table/index name that I could join on with dba_tables, dba_indexes and dba_tab_columns. And of course, I could also get statistics history data just as easily.
The same day, the possible new queries I wanted to add to the output grew and grew as I got more and more ideas. And that meant two disadvantages: the html output was really getting unwieldly and scrolling would cause even more repetitive strain injury in the “scroll-finger” than before. So I made sure that the new version (1.3) of sql_sql_id_html.sql would create a menu with hyperlinks to the tables in the output.
The second was that some queries would make the response time of the whole script much too large. I wanted it to create an HTML report in a matter of seconds. So I decided to create a second script for the extra statistics queries that usually take longer. stats_sql_id_html.sql was born. Usually you don’t have to run this script every time and certainly not for every child cursor, so resources will be spared for the database users. The input is the same as sql_sql_id_html.sql, so running it, initially, looks the same.