Author Archives: Bobby

I can never remember where to put the (+) symbols in an Oracle SQL query to make it have an outer join.  I rarely need to write a query with an outer join and when I do I have to … Continue reading →

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 →

They say you learn more from your mistakes than your successes.  I’ve been making a big mistake with a script I was using to tell how many logons had occurred between two AWR snapshots.  I don’t want to lead anyone … Continue reading →

I’m working on a performance toolkit with a coworker of mine. It is great to have someone else to use the tools I’ve been using, streamline them, and document them for the rest of our team. To figure out what … Continue reading →