Insights into Statistics, Query Optimization and the Oracle Optimizer
How do I
How to Generate a Useful SQL Execution Plan
Introduction There is an old joke the unreconstructed comic Dave Allen used to tell, where a traveler asks a passer-by for directions to a particular town and the passer-by simply says, “Well I wouldn’t start from here if I were you.” When it comes to SQL execution plans, if you start from the wrong place, then you probably won't make it to your destination. The purpose of this blog post is to take stock for a moment and present what I consider to be the best 'default' methods...
Wednesday, April 25, 2018 | How do I | Read More
How do I create statistics to make ‘small’ objects appear ‘large’ to the Optmizer?
I recently spoke with a customer who has a development environment that is a tiny fraction of the size of their production environment. His team has been tasked with identifying problem SQL statements in this development environment before new code is released into production. The problem is the objects in the development environment are so small, the execution plans selected in the development environment rarely reflects what actually happens in production. To ensure the...
Wednesday, November 21, 2012 | How do I | Read More
How do I restrict concurrent statistics gathering to a small set of tables from a single schema?
I got an interesting question from one of my colleagues in the performance team last week about how to restrict a concurrent statistics gather to a small subset of tables from one schema, rather than the entire schema. I thought I would share the solution we came up with because it was rather elegant, and took advantage of concurrent statistics gathering, incremental statistics, and the not so well known “obj_filter_list” parameter in DBMS_STATS.GATHER_SCHEMA_STATS procedure...
Wednesday, August 29, 2012 | How do I | Read More
How do I cut and paste commands from your blog?
At the recent ODTUG Kscope 12 conference several people told me that they really enjoyed our blog on the Optimizer but were frustrated because they couldn’t cut and paste the commands used in the blog posts straight into their environment. Typically I use screen shots in the blog posts to make the commands clear but it does mean that it is impossible to cut and paste the commands into your environment. In order to get around this I have created a downloadable .sql script for...
Thursday, July 5, 2012 | How do I | Read More
How Do I Compare Optimizer Statistics?
This question came up recently when I was helping a customer upgrade a large data warehouse. Prior to the upgrade, they were using an ESTIMATE_PERCENT of 0.000001, the smallest possible sample size allowed, when they gathering statistics on their larger tables. When I asked why they picked such a tiny sample size they said it was because they needed statistics to be gathered extremely quickly after their daily load both at the partition and at the global level. Since these...
Friday, May 18, 2012 | How do I | Read More
How do I force a query to use bind-aware cursor sharing?
Back in our original post on adaptive cursor sharing, a commenter asked if there was a way to skip the monitoring for a query and go straight to bind-aware cursor sharing. We mentioned then that we would add a hint for this purpose, but we never addressed the new hint here on the blog. From Oracle Database 11g, the BIND_AWARE hint can be used to force bind-aware cursor sharing from the first execution. The hint only works if all of the other criteria required for...