Oracle Autotrace vs Explain

My team uses Oracle 11 and SQL Developer. I've been relying heavily on explain plans lately to try and determine the most efficient way to solve various problems. Recently, a coworker pointed out that explain plan is not always accurate to what actually happens in the database, and that an autotrace is a better indication since the query is actually run against the data.

Testing a query, I've gotten the following results

_________________________________
|      Method        |   Cost   | 
|--------------------|----------|
| Query A Explain    |  306,188 |
| Query A Autotrace  |  399,131 |
| Query B Explain    |   99,226 |
| Query B Autotrace  |  137,661 |
|____________________|__________|

When using the autotrace, query A had a cost increase of 30% and Query B of nearly 40%. Obviously, I should be using query b in both cases, but I don't understand what causes them to differ.

Autotrace in SQL Developer gets the plan from v$sql_plan, and also gets the stats from your session, does a delta of session stats before and after running the query.

Explain Plan asks the database what it THINKS the plan will be for your query.

Your co-worker is right, they can differ wildly, and you're better served to use AutoTrace or our new feature in 4 and higher where we show the cached plan (a drop down control on the explain plan button makes these available.)

StackExchange Database Administrators Q#107096, answer score: 6