I like SQL Developer

One of our customers sent in some SQL that took ages to run. On the face of it was not doing anything difficult, it just inserted the results of an aggregate query into another table. The select part of the query joined two large partitioned tables on the partition key to restrict the result set to just 7 partitions from each table, added a couple of other column filters, and put in a count distinct and that’s it

Dropping the query into SQL Developer SQL window and clicking the Explain button gives a nice graphical representation of the plan. I could see that we hit 7 out of 800-odd partitions on one table and key accessed the partitions on the other table. There were three sorts, two for the group by and one for the count distinct. But the thing that worried me was the Nested Loop join between a 500,000 row and a 3M row chunk of the source tables, I had hoped to see a hash join. One nice feature of SQL developer is the snippet window and by choosing the Optimiser Hints tab you get a nice list of hints that you can just drag and drop into the query. I dropped the USE_HASH hint into the select statement in the SQL window and re-captured the plan, this time it looked like the one I expected. I then executed the query and found it took less than 10 seconds (the nested loop version was killed after 1 hour).

But I do not like hinting queries, to me it means that the optimiser has got it wrong, that is there is something about the data it does not know or it has incorrect information on. From the table window in SQL Developer it is easy to see when statistics where gathered, in this case it was the same day. But there is also a columns statistics tab, and on this I could see the problem: the column statistics were very old compared to table stats – like 6 months older. By refreshing the column statistics, the un-hinted plan for the query changed to a hash join plan. So, result one happy user.

But why were the column statistics so wrong, it looks like the problem happened last year when a change was made to the statistics collection routine to collect histograms on all indexed columns, but this table is not indexed… and hence no new stats are collected. We now need to develop a routine to correctly gather the statistics on this table.

Although SQL Developer helped us find the problem quickly there is one major failing in the product for our data warehouse support use, it does not allow us to see the tables that are materialized view containers. It would be great if we could see when columns were last analyzed on the mviews - next release perhaps

Later update... Another Blog post by Kris Rice explains exactly how to add the missing features - I'll give it a try when I'm in the office next week. Thanks Kris