Last week I had the pleasure of delivering a five-part SQL Tuning Workshop for my local Oracle User Group – Northern California Oracle User Group. The workshop explains the fundamentals of the cost-based optimizer, the statistics that feed it, the hints that influence it and key tools you need to exam executions plans.
The workshop also provides a methodology for diagnosing and resolving the most common SQL execution performance problems. Given the volume of interest in this content, I want to share all of the material from the workshop here and give you links to additional material on each of the 5 topics.
Part 1 Understanding the Oracle Optimizer
The first part of the workshop covers the history of the Oracle Optimizer and explains the first thing the Optimizer does when it begins to optimize a query – query transformation.
Query transformations or the rewriting of the SQL statement into a semantically equivalent statement allows the Optimizer to consider alternative methods of processing or executing that query, which are often more efficient than the original SQL statement would allow. the majority of Oracle’s query transactions are now cost-based, which means the Optimizer will cost the plan with and with the query transformation and pick the plan with the lowest cost. With the help of the Optimizer development team, I’ve already blogged about a number of these transformations including:
You can also download the slides here.
Part 2 Best Practices for Managing Optimizer Statistics
Part 2 of the workshop focuses on Optimizer Statistics and the best practices for managing them, including when and how to gather statistics, including fixed object statistics.
Continue reading “SQL Tuning Workshop”
In my previous life as the Optimizer Lady, I wrote a blog on the importance of gathering fixed object statistics since they were not collected initially as part of the automatic statistics gathering task.
Starting with Oracle Database 12c Release 1, Oracle will automatically gather fixed object statistics as part of the automated statistics gathering task if they have not been previously collected. Does that mean we are off the hook then?
The answer (as always) is it depends!
Let me begin by explaining what we mean by I the term “fixed objects”.
Continue reading “Automatic Collection of Fixed Objects Statistics in 12c”
While at the HotSOS Symposium, last month, I caused quite a stir when I recommended that folks should never gather system statistics.
Why such a stir?
It turns out this goes against what we recommend in the Oracle SQL Tuning Guide, which says “Oracle recommends that you gather system statistics when a physical change occurs in the environment”.
So, who right?
Well in order to figure that out, I spoke with Mohamed Zait, the head of the optimizer development team and Nigel Bayliss, the product manager for the optimizer, upon my return to the office.
After our discussions, Nigel very kindly agreed to write a detailed blog post that explains exactly what system statistics are, how they influence the Optimizer, and provides clear guidance on when, if ever, you should gather system statistics!
What did I learn from all this?
Don’t gather system statistics unless you are in a pure data warehouse environment, with a good IO subsystem (e.g. Exadata) and you want to encourage the Optimizer to pick more full table scans and never says never!
Over the years, Oracle has provided a number of techniques to help you control the execution plan for a SQL statement, such as Store Outlines and SQL Profiles but for me, the only feature to truly give you plan stability is SQL Plan Management (SPM). It’s this true plan stability that has made me a big fan of SPM ever since it was introduced in Oracle Database 11g.
With SPM only known or accepted execution plans are used. That doesn’t mean Oracle won’t parse your SQL statements, it will. But before the execution plan generated at parse is used, we will confirm it is an accepted plan by comparing the PLAN_HASH_VALUE to that of the accepted plan. If they match, we go ahead and use that plan.
Continue reading “SQL Plan Management – Selective Automatic Plan Capture Now Available!”
When it comes to SQL tuning we often need to look at the execution plan for a SQL statement to determine where the majority of the time is spent. But how we generate that execution plan can have a big impact on whether or not the plan we are looking at is really the plan that is used.
The two most common methods used to generate the execution plan for a SQL statement are:
EXPLAIN PLAN command – This displays an execution plan for a SQL statement without actually executing the statement.
V$SQL_PLAN – A dynamic performance view introduced in Oracle 9i that shows the execution plan for a SQL statement that has been compiled into a cursor and stored in the cursor cache.
My preferred method is always to use V$SQL_PLAN (even though it requires the statement to at least begin executing) because under certain conditions the plan shown by the EXPLAIN PLAN command can be different from the plan that will actually be used when the query is executed.
So, what can cause the plans to differ?
Continue reading “Does the Explain Plan command really show the execution plan that will be used?”
Last week I was lucky enough to have participated in the Trivadis Performance Days 2017 conference and several people have asked if I would share the slides from one of my sessions.
The session in question was called “Harnessing the power of optimizer hints”. Although I am not a strong supporter of adding hints to SQL statements for a whole host of reasons, from time to time, it may become necessary to influence the plan the Optimizer chooses.
The most powerful way to alter the plan chosen is via Optimizer hints. But knowing when and how to use Optimizer hints correctly is somewhat of a dark art.
In this session I explained how Optimizer hints are interpreted, when and where they should be used, and why they sometimes appear to be ignored.
Be warned this session won’t make your a hinting master over night and I’m not advocating you should try and hint every problematic SQL statement you encounter!
In last week’s post I described how to use SQL Monitor to determine what is happening during the execution of long-running SQL statements. Shortly after the post went up, I got some requests on both social media and via the blog comments asking, “If it is possible to get the same information from a traditional text-based execution plan, as not everyone has access to SQL Monitor?”.
The answer is yes, it is possible to see a lot of the information shown in SQL Monitor by viewing the execution plan via the DBMS_XPLAN.DISPLAY_CURSOR function. In order to call this function, you will need SELECT or READ privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL, V$SQL, and V$SQL_PLAN, otherwise, you’ll get an error message.
The DBMS_XPLAN.DISPLAY_CURSOR function takes three parameters:
- SQL ID – default null, means the last SQL statement executed in this session
- CURSOR_CHILD_NO – default 0
- FORMAT – Controls the level of details that will be displayed in the execution plan, default TYPICAL.
The video below demonstrates how you can use the FORMAT parameter within the DBMS_XPLAN.DISPLAY_CURSOR function to show you information about what’s happened during an execution plan including the bind variable values used, the actual number of rows returned by each step, and how much time was spent on each step.
Under the video you will find all of the commands used, so you can cut and paste them easily.
How do I see the actual number of rows and elapse time for each step in the plan?
Continue reading “Using DBMS_XPLAN.DISPLAY_CURSOR to examine Execution Plans”
I’m often asked what is the best tool for viewing execution plans and for me, the answer is always SQL Monitor (included in the Oracle Tuning Pack). It really is the most invaluable tool if you need to determine what is happening during the execution of any long-running SQL statements.
In order to help you get the very most out of using SQL Monitor, I wanted to share with you some of the tips and tricks I‘ve learnt over the years from the original Database Manageability team, especially Cecilia Grant!
So, why do I love it so much?
There are so many reasons to love SQL Monitor; it’s hard for me to know where to begin. So, instead of giving you an exhaustive list I’ve put together a short video to demonstrate how I use SQL Monitor (be sure to set your resolution to 720p).
How do I control what statements are monitored?
Continue reading “Getting the most out of Oracle SQL Monitor”
Since it’s introduction in Oracle Database 8i, the DBMS_STATS package is Oracle’s preferred method for gathering statistics. With each new database release the DBMS_STATS package is extended to accommodate new approaches to gather statistics and new types of statistics.
Over the years, application developers and DBAs have written hundreds of scripts using the DBMS_STATS package to help gather and manage optimizer statistics effectively. However, once written these scripts are rarely modified to take advantage of the improvements in the DBMS_STATS package, which can result in suboptimal statistics.
Oracle Database 12 Release 2 makes it a lot easier to be able to manage this vast collection of scripts by includes a new DBMS_STATS preference called PREFERENCE_OVERRIDES_PARAMETER. When this preference is set to TRUE, it allows preference settings to override the parameter values specifically set in a DBMS_STATS command.
Continue reading “Overriding DBMS_STATS Parameter Settings”
At the recent OUG Ireland conference I had the privilege of participating in a panel discussion on the Oracle Database. During the course of the session the topic of Optimizer histograms came up. As always, a heated discussion ensued among the members of the panel, as we each had very different views on the subject.
Why so many different opinions when it comes to histograms?
The problem arises from the fact that some folks have been burnt by histograms in the past. In Oracle Database 9i and 10g, histograms in combination with bind-peeking lead to some unpredictable performance problems, which is explained in detail in this post on the Optimizer blog. This has resulted in a number of folks becoming histogram shy. In fact, I reckon if you were to put 3 Oracle experts on a panel, you would get at least 5 different opinions on when and how you should gather histograms!
So I thought it would be a good idea to explain some of the common misconceptions that surround histograms and the impact of adopting them.
This is a long post, so you might want to grab a coffee before you get into it!
Continue reading “Optimizer Histograms”