sql_sql_id_html.sql version 1.2 is out

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

Advertisements

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.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s