Be careful with autotrace in SQLDeveloper

I was tuning a query today that involved a couple of nested loops. I wanted to see the effect of different values for my bind variable(s) on the actual work being performed, so naturally autotrace was going to be the weapon of choice, especially since it is so convenient to use from SQLDeveloper. So I was stuffing my query with different variables, checked that the execution plan stayed the same (of course it did) and observed autotrace’s value for ‘consistent gets’. But something was odd. When I used a value that yielded no (or just very few) results for the driving part of the nested loop, the consistent gets were higher than with a value that yielded many results. After all, the work being done or blocks needed to be read should be dependant on how often I execute that loop. For an easy (and similar) example, look at this query with bind values of either ‘%x%’ or ‘%@%’:

FROM oe.orders o,
oe.customers c
WHERE c.customer_id = o.customer_id
AND c.cust_email LIKE :1

If I look for order by customers with an ‘x’ in their email address, I get 2 results and it takes 27 consistent gets. When looking for customers with an ‘@’ (all of them) I get 105 results but only 19 consistent gets are needed. I spent the next 60 minutes trying find an explanation for this behavior and really started to doubt my knowledge about Oracle. So I double-checked with autotrace from sqlplus which yielded the expected results: more rows – more CGs. So the problem had to be with SQLDeveloper and it was a rather easy find from there on. SQLDeveloper has a pagination with the default page size being set to 50. This basically means that it will only fetch the first 50 rows and wait for the user to scroll down before going on with the query. Which is a very good idea for most general purpose database work because you might now want to wait for all results to be fetched and transferred over the net.
But this poses a problem in combination with the built-in autotrace function because SQLDeveloper will stop (or rather pause) executing the query after the first 50 results. And autotrace can only output the work being done until that point. In the example above, this meant that with the ‘%x%’ predicate the outer or driving loop had to go through the whole full scan of the customers table. When given the other predicate, it had to execute the inner loop 50 times (a simple index lookup) and stopped after that, not even halfway ‘done’. The workaround for me was to increase the pagination size. You will find this at Tools->Preferences->Database->Advanced under ‘SQL Array Fetch Size’ but there seems to be an upper limit and I would also wish that they would either not display the wrong autotrace results or introduce an option that disables pagination when using autotrace.

One thought on “Be careful with autotrace in SQLDeveloper

  1. In more recent versions of SQL Developer, you can make Autotrace retireve all rows:

    Tools > Preferences > Database > Autotrace > Fetch all rows

Leave a Reply

Your email address will not be published. Required fields are marked *