Author Archives: 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 →

I have this test script I use to remind myself of the fastest way I’ve found to copy data into an Oracle table.  It shows three things: Fast way to create a new table populating with data Fast way to … Continue reading →

Click here for a simple but useful script that shows how the execution of a given query has changed over time. (current version). This script just queries DBA_HIST_SQLSTAT for a given sql_id. A sql_id corresponds to a single SQL query.  … Continue reading →

I use this script all the time to get the plans for a given SQL statement I’ve identified from an AWR report: getplans.sql Recently I’ve found out that you can do something like this with the delivered awr script $ORACLE_HOME/rdbms/admin/awrsqrpt.sql … Continue reading →