[Update]: there is now a non-Diagnostics Pack version here.
[Update]: version 1.6 is now out. More on this blogpost.
Version 1.2 of my SQL performance reporting script sql_sql_id_html.sql is out. In this version I’ve added query statistics from AWR. And I found a neater way to display bind variable mismatch information from v$sql_shared_cursor. It uses the xmltable function to display all the column data into rows (found this in a blogpost by Sayan Malakshinov. A real neat trick.). You need XML DB installed to run that. (Then again you needed XML DB also in 1.0 for showing SQL Profile data.)
Version 1.2 also retrieves the execution plan from AWR history. Today I worked on a case where that was quite handy. I was asked to meet an internal customer to discuss a performance issue. While I was underway, a collegue solved the issue by changing table statistics. By the time I arrived, there was a new execution plan. Usually it is still handy to know what happened: what changed, what was the original execution plan? AWR did have that information for me.
As you might recall from my previous blogpost about this script, I wanted to improve the response time graph at the end of the output. A week after I demonstrated this script to my collegues and said I wanted to improve the graph, but didn’t know how, I received an email from a collegue with as an attachment an output file with an improved graph. All I had to do, was to implement the changes in my code and now the graph has 2 Y-axis, with ranges and everything. Thanks, Oscar!
I’ve talked to collegues of my former employer recently. They said they liked the script, but since very few of their customers have the Diagnostics Pack, my sql_sql_id_html.sql script with AWR queries is almost useless to them. I’ve been thinking about a version that uses Statspack data, but it’s going to take some work to do that. I have attempted to write a query to retrieve query response time history from Statspack tables, but it came out all wrong. It won’t be as easy as querying a dba_hist_% table.
Anyway, for all of you that do have Diagnostics Pack, I hope you’ll have fun with this new version.