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

Marcel-Jan is de PR-functionaris van de Werkgroep Maan en Planeten. Hij verzorgt ook het nieuws op de Facebook pagina en deze blog.
This entry was posted in Oracle performance tuning, Oracle scripts and tagged , , , , . Bookmark the permalink.

1 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