Performance
CLUSTERING_FACTOR
I had to analyze a performance problem this morning, and it occurred to me it is a good example of troubleshooting queries impacted by the clustering_factor of an index. As such, below is the email exchange. I took a look, and I think I understand why the query optimizer is choosing a full scan and... »
Troubleshooting “library cache lock”
If you have several users waiting on a library cache lock, they are normally blocked by one user that is waiting on a library cache pin. You have to find out who is blocking the user waiting on the library cache pin and find out what they are doing. Our test case performs the following... »
SQL Bind Capture - Part 3
I needed to find out which variables were producing a poor execution plan. Short of enabling 10046 trace with binds enabled, I found the following is a good approximation. Basically: 1. Find the PLAN_HASH_VALUE for the SQL_ID with the poor plan 2. Pass this value to V$SQL_BIND_CAPTURE and print the variables associated with this... »
Shell scripting stats from a 10046 trace
This morning, I enabled a 10046 trace at level 12 for a slow session. I did not scope the trace interval (ala Cary Milsap), so I didn’t even have the query text in the trace file (although I knew what it was). The resulting trace file was huge at 200MB. I... »
Manually subscribing to Oracle FAN events
JDBC and dot Net clients are the recommended way to automatically subscribe to high availability events in an Oracle clustered environment, AKA, a RAC. I was curious as to how this could be implemented using your own code behind the scenes. To show this, we will use a combination of PL/SQL and python. First, we... »
At least one case where rebuilding indexes helps…
We found that after deleting 250 million “lower” rows from a table with a monotonically increasing sequence, our MIN range scans were incredibly slow. Richard Foote describes this much more clearly than I can this morning, so all this post will be is a link to his blog about it. Enjoy… http://richardfoote.wordpress.com/2008/07/01/deleted-index-entries-part-v-trouble/ »
Poor man’s parallel DML
We were recently required to get rid of 250 million rows in a partitioned audit table. While the table is partitioned, we have global indexes that would be rendered unusable after dropping or truncating a partition. The UPDATE GLOBAL INDEXES clauses I have never found to be immensely useful, as all it... »
Are you actually *using* Oracle partitioning?
Partitioning is sometimes sold as the panacea to all performance problems. Anyone who has added local indexes on a column other than the one on which the table was partitioned quickly finds out this isn’t always the case. Keep in mind that partitioning is not only about improving performance, but also increasing availability and... »
More fun with SQL_BIND_CAPTURE
We had a query that was running slow during a performance test, but extremely fast with my laptop based threaded java test. The SQL was exactly the same, so I was driven bonkers by why it wasn’t using an index. It finally occurred to me that not only could the bind values... »
dbms_sqltune using AWR data
Today I wanted to use dbms_sqltune on a SQL statement that was run on Friday, but had not been executed since then. dbms_sqltune by default will assume the statement is still in the shared pool. If it is not, you must use one of the overloaded procedures for create_tuning_task. See below… variable l varchar2(4000) begin ... »