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

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