Monthly Archives: July 2012

Download this zip of the example scripts and their logs. This post is to describe how I go about testing the performance affects of a change that has the potential to change the execution plans of many queries.  For example, … Continue reading →

I’ll be doing my Introduction to SQL Tuning talk at the East Coast Users Conference in October.  Come check it out! – Bobby

We have hit what is probably a bug on our 11.1 database.  Our optimizer statistics gathering job has been running for hours and consuming a ton of CPU resources.  When we look at an AWR report for this period of … Continue reading →

We use a feature called “Virtual Private Database” or VPD on our 11g database.  This looks a lot like what used to be called “Fine Grained Access Control” or FGAC on our 10g database.  The idea behind both of these … Continue reading →

This is another funny thing I’ve been doing wrong for years.  I’ve been using an older version of sqlplus and missing out on the improvements made in the way autotrace displays a query’s execution plan. Apparently somewhere around the version … Continue reading →

Click here to get a zip of the three different scripts I talk about in this post. There are two different functions in the Oracle supplied package DBMS_XPLAN that I use all the time for tuning SQL queries, DBMS_XPLAN.DISPLAY and DBMS_XPLAN.DISPLAY_AWR. … Continue reading →

Ran into an example this week using my sqlstat.sql(updated) script to see how a query’s performance changed over time.  Notice that the average disk reads per execution is steadily increasing as is the average elapsed time per execution:      select … Continue reading →