Your explain plan is fooling you

Tuning SQL performance is very much about optimizing the execution plans and I just wanted to throw out a thing that seemed obvious to me. Turns out, not necessarily.

How do you check execution plans?

Here’s an ‘Explain plan’ feature in Oracle SQL Developer (which I use for DB related stuff). What it does under the hood is running an EXPLAIN PLAN command and parsing its output. What you get as a result is really cool: a pretty and easy-to-read table with access predicates, filters, costs of every operation, — all that you want really. It even comes with a built-in tool to compare plans.

Whichever SQL tool you use it probably has such a feature if it’s worth its salt.

Now all of that is great when you’re writing new code and want to see how the DB is going to perform your newly written query (beware of non-representative data in your dev DB though, but that goes without saying really).

But this is NOT your tool of choice when you’re playing a performance detective!

Query caching

Look, parsing queries is expensive, and a working database instance is not going to parse the same query every time. Rather it will store the parsed query in the Shared Pool so that next time it can just fetch an execution plan from cache.

That’s why when you’re summoned to take a look at what’s going on, you have to look at cached plans rather than asking the database to come up with a new one.

• The conditions under which the database instance was parsing our query first time had probably changed.
• The bind variables! Explain plan will work without any. While in real life Oracle will probably peek at your bind variable values and try to come up with a plan best suited for those.

So if the database instance knows which plans it uses, let’s just ask it.

Where to look

You’ll find the cached queries in V$SQL. How to find the exact one you need is a good question, but you’ve probably came here after examining a trace file, that’s how you know the query is slow, right?

Every query in your trace file has sql_id and plan_hash_value. Those correspond to exactly the same columns in V$SQL. If you don’t know the sql_id but somehow magically know that the query is slow, first, tell me how, and second, try the SQL_TEXT column — the complete query is there.

You might find that there’re several rows with the same sql_id. That means there are several copies of the query in a shared pool. That usually happens when Oracle peeks into the bind variables and comes up with a special execution plan for an unusual set of values. These lines have different value in a CHILD_NUMBER column.

Now take the child number from a row of V$SQL and pass it to

SELECT *
FROM   dbms_xplan.display_cursor(:sql_id, :child_number)

What you get is a pretty verbose output

Plan hash value: 3873487503, SQL hash value: 2893978328, child number: 0
------------------------------------------------------------------
SELECT ename FROM emp e, dept d WHERE e.deptno = d.deptno
AND e.empno=:1

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |            |          |
|*  1 |  HASH JOIN         |      |     1 |    16 |     6  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    13 |     3  (34)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |    10 |    12 |     3  (34)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   2 - filter("E"."EMPNO"=:1)

21 rows selected.

That’s how you find the exact plan that the database instance is using. Have fun tuning it!