sqlperf.sql version 1.6 is out

The main inspiration for this new version of sqlperf.sql is the datawarehouse project I’m currently in. They have performance problems and parallellism seems to be one of the issues. So I needed to know over how many sessions parallellism had spread.

The execution plan tells you that parallellism has occurred, but not over how many PX servers. Luckily I found dba_hist_sqlstats gives you that information.

But dba_hist_sqlstats has a lot more information to offer. For example, dba_hist_sqlstats has a column called bind_data. When you do a select bind_data from dba_hist_sqlstats you get unreadable information, but with dbms_sqltune.extract_binds(bind_data) you can get the bind data like you read it in v$sql_bind_capture. And as I’ve learned while making version 1.4: you need the function anydata.accesstimestamp to get readable timestamps. So I’ve implemented this for the AWR bind data also.

bind_data only has this data if the parameter cursor_bind_capture_destination is set to the default of memory+disk. If you don’t use AWR and/or don’t have the Diagnostics Pack license, you might just as well set this parameter to memory. That saves you disk space.

Another change, is the command to drop SQL plan baselines. The old one based on just the plan_name never seemed to work. I believe I have a better generated drop SQL plan baselines statement. There are still two different statements to drop SQL plan baselines until I have had more chances to test the new one.

I hope you like it and I hope it helps you too in your SQL performance investigations.


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.

6 Responses to sqlperf.sql version 1.6 is out

  1. Pingback: Version 1.3 of sql_sql_id_html.sql and a new script: stats_sql_id_html.sql | Marcel-Jan's Oracle Blog

  2. Pingback: sql_sql_id_html.sql version 1.4 | Marcel-Jan's Oracle Blog

  3. Pingback: sql_sql_id_html.sql is now called sqlperf.sql (and new versions too) | Marcel-Jan's Oracle Blog

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

  5. Pingback: sql_sql_id_html.sql: a SQL performance script with HTML output | Marcel-Jan's Oracle Blog

  6. 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:

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