sql_sql_id_html.sql: a SQL performance script with HTML output

[Update]: there is now a non-Diagnostics Pack version here.

[Update]: version 1.6 is now out. More on this blogpost.

A while ago I read a blogpost by Tanel Poder about HTMLizing your sqlplus output. I used to make sqlplus scripts with HTML output. But I stopped doing that at my previous site, because we had 70 customers, so almost half of 70 ways of making VPN connections and also nearly as many ways of copying the HTML output to an environment where there was a browser and the HTML would load reasonably fast. You can’t beat viewing a text file of a few kilobytes then. On my current site however access to all database servers is standard and fast. So why not HTMLize my scripts now?

So here it is: sql_sql_id_html.sql. One thing has changed: it uses AWR data without warning. You enter the sql_id and child_number and it will query dba_hist_% tables right away. So internally Oracle will count that as one more usage of the Diagnostics Pack (in dba_feature_usage_statistics). Just so you know.

I’m actually really happy about the way the output looks. I use the output frequently to discuss SQL performance issues with developers and other DBAs (and they ask me “how on Earth did you get that query response time data in your report?”). I’ve even rebranded the output file as “SQL performance reports” when I discuss them in meetings. I always had a thing for marketing 😉

If you run the script and scroll to the end of it, you will notice a graph. That’s a Google Chart API graph. That is why you need internet access to view the graph. Otherwise the cold, hard data is still there in a table above it. I still want to improve the graph. I want response time and executions on different Y-axes. And I would like to see ticks with values on both X- and Y-axes (now you have to move your mouse over the graph).


I’ve tried though. Lord, have I tried. But everytime I change the Javascript code even the slightest, it won’t show and in rare occasions it shows just plain wrong. That’s what you get for copying code from a collegue. So, for now, I have given up. But I’ll be back… Probably when I’ve overhauled the code completely or something.


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, Oracle scripts and tagged , , , , . Bookmark the permalink.

One Response to sql_sql_id_html.sql: a SQL performance script with HTML output

  1. Pingback: sql_sql_id_html.sql version 1.2 is out | Marcel-Jan's Oracle Blog

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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