“Everybody lies” was the common phrase used by Dr Gregory House in the TV show House MD. It seems Oracle is no different…
I was recently asked to help a developer tune a change they had made to a query. The query was relatively lengthy given that it was for an OLTP system, hence the need to ensure it ran as efficiently as possible.
Running the original query through Autotrace generated approximately 1,700 logical IO operations, which is one of the critical metrics I use for quickly judging efficiency. Autotrace on the modified query initially reported twice the logical IOs that was quickly identified to be a problem with the way Oracle was ordering the tables. A simple application of the LEADING hint brought the logical IO down… but only to around 1,800, which was 100 higher than the original query.
For the next hour I struggled to work out why the modified query was reporting a higher logical IO figure than the original query. Yes, the query had been modified but the change was the replacement of a subquery that returned a single value with a bind variable holding that value. My expectation was that the modified query should be 3 IO operations less than the original query (just to note, the change to the query really wasn’t worth it from a query tuning perspective but was primarily being done for other reasons).
After applying hints that forced the modified query to behave as closely as possible to the original query the logical IO was still 100 greater. Verification of the result-set showed that both queries did return the same results too, which is another thing to verify when performing any sort of query tuning.
My next step was to examine the execution of both queries using SQL monitor and this was when I discovered that Oracle had been lying to me all along. At the core of the query were about half a dozen tables. The execution plan reported by Autotrace joined them in a particular order and the hints I had applied to the modified query mimicked this order. However, the SQL plan details as revealed by SQL monitor showed that Oracle joined the tables in a different order, choosing to lead with a different table.
I had fallen into the one trap with Autotrace execution plan that I had noted in my earlier blog post:
The main point to note is that the execution plan is simply the proposed plan and in certain situations the database may choose to execute the query differently. For example, the execution plan may be altered as a result of bind variable peeking.
Another way of uncovering the difference in the predicted and actual plans would have been to extract the real plan from the SGA, using dbms_xplan.display_cursor.
It’s not common for the execution plan reported by Explain Plan or Autotrace to be different to the one that Oracle will actually use when executing the query but it is possible. Perhaps I should have been open to the possibility of this earlier and saved myself a bit of angst. Simply hinting the modified query with the different table order resolved the problem of the IO difference. I went home that day happy but mentally cursing Oracle for lying to me and leading me astray in the first place.
Mark Hoxey 