Version 1.3 of sql_sql_id_html.sql and a new script: stats_sql_id_html.sql

[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.

Before you use it, remember that to run both scripts, you need the Diagnostics Pack license. I use tables that will be marked by Oracle as usage of the license. Otherwise, have fun.


About Marcel-Jan Krijgsman

Ever since I started working with Oracle, I had an interest in Oracle database performance tuning. This led, eventually, to a four day training I made and gave for customers of Transfer Solutions. Since 2012 I work for Rabobank Nederland. A few years ago I also became interested in Oracle database security. All technology aside, it is my experience that security usually plays out on a political level. I'm a Oracle certified professional for the 8i, 9i, 10g and 11g databases and Oracle Database 11g Performance Tuning Certified Expert.
This entry was posted in Oracle performance tuning and tagged , , . Bookmark the permalink.

One Response to Version 1.3 of sql_sql_id_html.sql and a new script: stats_sql_id_html.sql

  1. Pingback: Table and index statistics history | Marcel-Jan's Oracle Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s