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.
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.
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 * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=\>'+alias')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------- SQL_ID 4c8bfsduxhyht, child NUMBER 0 ------------------------------------- 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 (IDENTIFIED BY operation id): ------------------------------------------------------------- 1 - SEL$5DA710D3 2 - SEL$5DA710D3 / E@SEL$1 3 - SEL$5DA710D3 / D@SEL$2 Predicate Information (IDENTIFIED BY 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.
Continue reading “What are Query Block Names and how to find them”
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 the key tools you need to examine execution 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 five topics.
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. Most 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 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”