Examines the different aspects of an execution plan, from cardinality estimates to parallel execution and explains what information you should be gleaming from the plan.
Part 3 of the workshop examines the different aspects of an execution plan, from cardinality estimates to parallel execution and explains what information you should be gleaming from the plan and how it affects the execution. It offers insight into what caused the Optimizer to make the decision it did as well as a set of corrective measures that can be used to improve each aspect of the plan.
This session focuses on Optimizer statistics and the best practices for managing them!
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.
Part one covers the history of the Oracle Optimizer and explains the first thing the Optimizer does when it begins to optimize a query.
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.
I got a lot of follow-up questions on what Query Block names are and how to find them, after my recent post about using SQL Patches to influence execution plans. Rather than burying my responses in the comment section under that post, I thought it would be more useful to do a quick post on it.
What are query blocks?
A query block is a basic unit of SQL. For example, any inline view or subquery of a SQL statement are considered separate query blocks to the outer query.
The simple query below has just one sub-query, but it has two Query Blocks—one for the outer SELECT and one for the subquery SELECT.
Oracle automatically names each query block in a SQL statement based on the keyword using the following sort of name; sel$1, ins$2, upd$3, del$4, cri$5, mrg$6, set$7, misc$8, etc.
Given there are two SELECT statements in our query, the query block names will begin with SEL. The outer query will be SEL$1 and the inner query SEL$2.
How do I find the name of a query block?
To find the Query Block name, you can set the FORMAT parameter to ‘+alias’ in the DBMS_XPLAN.DISPLAY_CURSOR command. This will display the contents of the OBJECT_ALIAS column in the PLAN_TABLE, as a new section under the execution plan.
The new section will list the Query Block name for each of the lines in the plan.
SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=\>'+alias'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID 4c8bfsduxhyht, child NUMBER0-------------------------------------SELECT e.ename, e.deptno FROM emp e WHERE e.deptno IN(SELECT d.deptno
FROM dept d WHERE d.loc='DALLAS')
Plan hash VALUE: 2484013818---------------------------------------------------------------------------| Id | Operation | Name |ROWS| Bytes | Cost (%CPU)|TIME|---------------------------------------------------------------------------|0|SELECT STATEMENT ||||5(100)|||*1| HASH JOIN SEMI ||5|205|5(20)| 00:00:01 ||2|TABLE ACCESS FULL| EMP |14|280|2(0)| 00:00:01 ||*3|TABLE ACCESS FULL| DEPT |1|21|2(0)| 00:00:01 |---------------------------------------------------------------------------
Query Block Name / Object Alias (IDENTIFIEDBY operation id):
-------------------------------------------------------------1- SEL$5DA710D3
2- SEL$5DA710D3 / E@SEL$1
3- SEL$5DA710D3 / D@SEL$2
Predicate Information (IDENTIFIEDBY operation id):
---------------------------------------------------1- access("E"."DEPTNO"="D"."DEPTNO")3-FILTER("D"."LOC"='DALLAS')
As you can see, @SEL1 is the Query Block name for the outer query, where the EMP table is used, and @SEL2 is the Query Block name for the sub-query, where the DEPT tables is used.
In a recent chat with Connor McDonald, we discussed if it is realistic to have applications that don’t contain Optimizer hints. Ideally, the answer to this question is “yes”, you don’t need hints if you have a well-written application and you have supplied a representative set of statistics and all the possible constraint information (NOT NULL, Primary keys, Foreign Keys, etc.) to the Optimizer.
But in reality even with all of this in place, there can be cases where something goes wrong with the execution plan for a critical SQL statement and you get called in to fix.
During our chat, Connor used a very apt analogy to describe this situation. He said it was like having a patient arrive in the emergency room, who is bleeding profusely. Your first priority is to stop the patient from bleeding by slapping a band-aid on the wound.
The same is true for our poorly performing SQL statement. Our initial response is to add an optimizer hint to get the SQL statement’s execution plan back to a reasonable response time or acceptable performance.
But once a patient has been stabilized in the emergency room, medical professionals normally take that patient into surgery to make a permanent fix or at the very least stitch up the wound properly.
We need to make sure we do the same thing for our SQL statements.
Rather than leaving a band-aid in the application code in the form of an optimizer hint, we should either fix the root cause or at the very least, make a permanent fix that can be easily traced and ideally can evolve over time.
That’s why you often hear me say, “if you can hint it, you can baseline or patch it”.
What do I mean by that?
I mean we should capture the hinted plan as a SQL plan baseline or at the very least insert the hints via a SQL Patch so that we know that this statement is patched (the use of a SQL patch is visible in the note section of the plan).
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:
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”.
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.
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.
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?
