Oracle Misdirection: Lies, Filthy Dirty Lies!

So I learned today that Oracle with it's EXPLAIN PLAN and AUTOTRACE features sometimes will "lie" about how a particular query is executing.

For a problematic query against a view in one of our databases, I pulled the query into Aqua Data Studio (cross platform database development/admin tool that I use)  and ran an EXPLAIN PLAN on it. What truly puzzled me was that the query appeared to be using the correct index but never seemed to return in a reasonable amount of time.

After speaking with our Senior Oracle DBA, he informed me that the RDBMS will often decide at execution to use a different plan than what EXPLAIN PLAN or AUTOTRACE says it is going to use.  Some of this decision depends on the whether or not you use bind variables or at times the data itself that you are binding in your predicates.

To troubleshoot, I needed to look for the SQL_ID for the query in question.  To make sure I pulled the EXACT query that was causing the problem, I actually looked at a join between the v$sql and v$sql_bind_capture views focusing in on a CustomerID for a query that I knew I had just run:

SELECT /*+ PARALLEL */
       v.executions,
       c.VALUE_STRING as CustomerID,
       v.sql_fulltext,
       v.sql_id,
       v.rows_processed, v.parsing_schema_name,
       v.last_active_time, v.last_load_time, v.first_load_time,
       v.disk_reads, v.buffer_gets, v.cpu_time, ROUND(v.physical_read_bytes/(1024*1024)) as read_meg
FROM  v$sql v, v$sql_bind_capture c
WHERE
v.sql_fulltext LIKE '%CustomerSummary%' AND
v.sql_id = c.SQL_ID
AND c.name like '%CustomerId%'
AND c.VALUE_STRING like '%2005681%'
AND v.PARSING_SCHEMA_NAME = 'MO_COUNTIES'
and v.last_active_time >= sysdate - 1
ORDER BY physical_read_bytes DESC;

This then gave me a SQL_ID that I could plug into the following that provided a table pinpointing the EXACT plan used at execution:

select * from table(dbms_xplan.display_cursor('3qub1pyswv17b','',''));  

Digging through this plan I discovered that an index I thought should have been used was not.  In addition to asking our DBA to gather statistics, I also for the time being modified the view such that it now has a hint driving it to the correct index.