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 * 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”