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.