What are Query Block Names and how to find them

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?

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.

So, if I wanted to use a SQL Patch to change the access method for the DEPT table, I would specify it as follows.

DECLARE
   patch_name varchar2(100);
BEGIN
   patch_name := sys.dbms_sqldiag.create_sql_patch(
                 sql_id=>'4c8bfsduxhyht',
                 hint_text=>'INDEX(@"SEL$2" "D" "DEPT_LOC_IDX")',
                 name=>'TEST_QB_PATCH');
END;
/

Let’s check the plan to be sure the SQL Patch worked.

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 (SELECTd.deptno
FROM   dept d	WHEREd.loc='DALLAS')
Plan hash VALUE: 3547841569
-----------------------------------------------------------------------------------------------------
| 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 BY INDEX ROWID BATCHED| DEPT	    |	  1 |	 21 |	  2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN		     | DEPT_LOC_IDX |	  1 |	    |	  1   (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
   4 - SEL$5DA710D3 / D@SEL$2
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   4 - access("D"."LOC"='DALLAS')
 
Note
-----
   - SQL patch "TEST_QB_PATCH" used FOR this statement

Remember, even if it’s a long-running query, once it starts executing the execution plan will be available in the cursor cache and therefore can be viewed by supplying the SQL_ID and CHILD NUMBER to the DBMS_XPLAIN.DISPLAY function.

If you have a more complex SQL statement, you may want to use the QB_NAME hint to explicitly name each query block. This is an excellent way to document your code, and it will make it a lot easier to use a SQL PATCH to add additional hints, to a particular query block.

Let’s add a QB_NAME hint to the subquery in our simple SQL statement above and then use that Query Block name in our SQL Patch.

SELECT e.ename, e.deptno 
FROM   emp e 
WHERE  e.deptno IN (SELECT /*+QB_NAME(MY_DEPT_SQ) */ d.deptno 
                    FROM   dept d 
                    WHERE  d.loc='DALLAS');
 
DECLARE
   patch_name varchar2(100);
BEGIN
   patch_name := sys.dbms_sqldiag.create_sql_patch(
                 sql_id=>'bah0gcbrvm1gk',
                 hint_text=>'INDEX(@"MY_DEPT_SQ" "D" "DEPT_LOC_IDX")',
                 name=>'TEST_MYQB_PATCH2');
END;
/
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id=>'bah0gcbrvm1gk',- 
                                              format=>'+alias'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID bah0gcbrvm1gk, child NUMBER 0
-------------------------------------
SELECT e.ename, e.deptno  FROM   emp e  
WHERE  e.deptno IN (SELECT /*+QB_NAME(MY_DEPT_SQ) */ d.deptno 
FROM   dept d  WHERE  d.loc='DALLAS')
 
Plan hash VALUE: 2006641677
-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |       |       |     4 (100)|          |
|*  1 |  HASH JOIN SEMI                      |              |    10 |   200 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                  | EMP          |    30 |   270 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID BATCHED| DEPT         |     1 |    11 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN                  | DEPT_LOC_IDX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (IDENTIFIED BY operation id):
-------------------------------------------------------------
   1 - SEL$CA735286
   2 - SEL$CA735286 / E@SEL$1
   3 - SEL$CA735286 / D@MY_DEPT_SQ
   4 - SEL$CA735286 / D@MY_DEPT_SQ
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   4 - access("D"."LOC"='DALLAS')
 
Note
-----
   - SQL patch "TEST_MYQB_PATCH2" used FOR this statement

For more complex queries, it might be easier to use the Explain Plan or Auto Trace functionality in SQL Developer to view the Query Block names. Jeff Smith, the product manager for SQL Developer, recently showed me how to do this, as the OBJECT_ALIAS column isn’t displayed there by default.

In SQL Developer, go to the main menu under the Oracle SQL Developer tab in the top left-hand corner of the screen. From there, select preferences. This will open a new window that will allow you to change the default preferences. Under Database, select the Autotrace/Explain Plan option, which will enable you to select which columns you want to have displayed in an execution plan. You want to choose OBJECT_ALIAS from the list and click OK.

Once you have set the preference, simply select the EXPLAIN or AUTO TRACE button for the statement you are interested in and the Query Block Name will appear in the plan.

Unfortunately, SQL Monitor doesn’t provide a mechanism to display the OBJECT_ALIAS column right now. But I have filed an Enhancement Request to get it added.

4 thoughts on “What are Query Block Names and how to find them”

  1. I find it interesting, and a little irritating, that

    index(@sel$2 d@sel$2 (loc))

    and

    index(@sel$5da710d3 d@sel$2 (loc))

    are both valid ways of achieving the indexed access path.

    I can’t help feeling that the former shouldn’t have worked – particularly since the hint that gets into the outline is the latter, and “sel$2” doesn’t exist by the time the optimizer has transformed the query to it’s final form.

    It would be nice to have a formal statement of the correct strategy for hinting rather than having a few very simple examples that just happen to work but won’t work in similar but slightly difference circumstances.

    Regards
    Jonathan Lewis

  2. Thanks for the details, is that the SQL Patch is tied at sql_id level? when the sql_id changes, the sql patch doesn’t work as expected. kindly confirm.

    demo@PDB1> create table emp as select * from scott.emp;

    Table created.

    demo@PDB1> create table dept as select * from scott.dept;

    Table created.

    demo@PDB1> create index dept_loc_idx on dept(loc);

    Index created.

    demo@PDB1> create index dept_dname_idx on dept(loc,dname);

    Index created.

    demo@PDB1>
    demo@PDB1> set serveroutput off
    demo@PDB1> select empno,ename,sal
    2 from emp
    3 where deptno in (select deptno
    4 from dept
    5 where loc =’DALLAS’ );

    EMPNO ENAME SAL
    ———- ———- ———-
    7369 SMITH 800
    7566 JONES 2975
    7788 SCOTT 3000
    7876 ADAMS 1100
    7902 FORD 3000

    demo@PDB1>
    demo@PDB1> set serveroutput off
    demo@PDB1> select empno,ename,sal
    2 from emp
    3 where deptno in (select deptno
    4 from dept
    5 where loc =’DALLAS’ );

    EMPNO ENAME SAL
    ———- ———- ———-
    7369 SMITH 800
    7566 JONES 2975
    7788 SCOTT 3000
    7876 ADAMS 1100
    7902 FORD 3000

    demo@PDB1>
    demo@PDB1> select * from table( dbms_xplan.display_cursor(format=>’+alias’));

    PLAN_TABLE_OUTPUT
    ——————————————————————————————————————————————
    SQL_ID 8u42d326m6413, child number 0
    ————————————-
    select empno,ename,sal from emp where deptno in (select deptno from
    dept where loc =’DALLAS’ )

    Plan hash value: 3597657443

    ——————————————————————————————————-
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ——————————————————————————————————-
    | 0 | SELECT STATEMENT | | | | 5 (100)| |
    |* 1 | HASH JOIN SEMI | | 5 | 140 | 5 (0)| 00:00:01 |
    | 2 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 |
    | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPT | 1 | 11 | 2 (0)| 00:00:01 |
    |* 4 | INDEX RANGE SCAN | DEPT_DNAME_IDX | 1 | | 1 (0)| 00:00:01 |
    ——————————————————————————————————-

    Query Block Name / Object Alias (identified by operation id):
    ————————————————————-

    1 – SEL$5DA710D3
    2 – SEL$5DA710D3 / EMP@SEL$1
    3 – SEL$5DA710D3 / DEPT@SEL$2
    4 – SEL$5DA710D3 / DEPT@SEL$2

    Predicate Information (identified by operation id):
    —————————————————

    1 – access(“DEPTNO”=”DEPTNO”)
    4 – access(“LOC”=’DALLAS’)

    Note
    —–
    – this is an adaptive plan

    35 rows selected.

    demo@PDB1> declare
    2 l_name varchar2(40);
    3 begin
    4 l_name := dbms_sqldiag.create_sql_patch(
    5 sql_id=>’8u42d326m6413′,
    6 hint_text=>’index(@”SEL$2” “DEPT” “DEPT_LOC_IDX”)’,
    7 name=>’MY_DEMO_PATCH’);
    8 end;
    9 /

    PL/SQL procedure successfully completed.

    demo@PDB1> select empno,ename,sal
    2 from emp
    3 where deptno in (select deptno
    4 from dept
    5 where loc =’DALLAS’ );

    EMPNO ENAME SAL
    ———- ———- ———-
    7369 SMITH 800
    7566 JONES 2975
    7788 SCOTT 3000
    7876 ADAMS 1100
    7902 FORD 3000

    demo@PDB1>
    demo@PDB1> select * from table( dbms_xplan.display_cursor(format=>’+alias’));

    PLAN_TABLE_OUTPUT
    ——————————————————————————————————————————————
    SQL_ID 8u42d326m6413, child number 0
    ————————————-
    select empno,ename,sal from emp where deptno in (select deptno from
    dept where loc =’DALLAS’ )

    Plan hash value: 2006641677

    —————————————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    —————————————————————————————————–
    | 0 | SELECT STATEMENT | | | | 5 (100)| |
    |* 1 | HASH JOIN SEMI | | 5 | 140 | 5 (0)| 00:00:01 |
    | 2 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 |
    | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPT | 1 | 11 | 2 (0)| 00:00:01 |
    |* 4 | INDEX RANGE SCAN | DEPT_LOC_IDX | 1 | | 1 (0)| 00:00:01 |
    —————————————————————————————————–

    Query Block Name / Object Alias (identified by operation id):
    ————————————————————-

    1 – SEL$5DA710D3
    2 – SEL$5DA710D3 / EMP@SEL$1
    3 – SEL$5DA710D3 / DEPT@SEL$2
    4 – SEL$5DA710D3 / DEPT@SEL$2

    Predicate Information (identified by operation id):
    —————————————————

    1 – access(“DEPTNO”=”DEPTNO”)
    4 – access(“LOC”=’DALLAS’)

    Note
    —–
    – SQL patch “MY_DEMO_PATCH” used for this statement
    – this is an adaptive plan

    36 rows selected.

    demo@PDB1> select t1.empno,t1.ename,t1.sal
    2 from emp t1
    3 where t1.deptno in (select deptno
    4 from dept
    5 where loc =’DALLAS’ );

    EMPNO ENAME SAL
    ———- ———- ———-
    7369 SMITH 800
    7566 JONES 2975
    7788 SCOTT 3000
    7876 ADAMS 1100
    7902 FORD 3000

    demo@PDB1>
    demo@PDB1> select * from table( dbms_xplan.display_cursor(format=>’+alias’));

    PLAN_TABLE_OUTPUT
    ——————————————————————————————————————————————
    SQL_ID 1nr8vry5gc2c8, child number 0
    ————————————-
    select t1.empno,t1.ename,t1.sal from emp t1 where t1.deptno in (select
    deptno from dept where loc =’DALLAS’ )

    Plan hash value: 3597657443

    ——————————————————————————————————-
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ——————————————————————————————————-
    | 0 | SELECT STATEMENT | | | | 5 (100)| |
    |* 1 | HASH JOIN SEMI | | 5 | 140 | 5 (0)| 00:00:01 |
    | 2 | TABLE ACCESS FULL | EMP | 14 | 238 | 3 (0)| 00:00:01 |
    | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPT | 1 | 11 | 2 (0)| 00:00:01 |
    |* 4 | INDEX RANGE SCAN | DEPT_DNAME_IDX | 1 | | 1 (0)| 00:00:01 |
    ——————————————————————————————————-

    Query Block Name / Object Alias (identified by operation id):
    ————————————————————-

    1 – SEL$5DA710D3
    2 – SEL$5DA710D3 / T1@SEL$1
    3 – SEL$5DA710D3 / DEPT@SEL$2
    4 – SEL$5DA710D3 / DEPT@SEL$2

    Predicate Information (identified by operation id):
    —————————————————

    1 – access(“T1″.”DEPTNO”=”DEPTNO”)
    4 – access(“LOC”=’DALLAS’)

    Note
    —–
    – this is an adaptive plan

    35 rows selected.

    demo@PDB1>

    1. Hi Rajeshwaran,

      A SQL Patch can be applied based on either the SQL_ID or the SQL text. If either change the patch won’t be applied.

      There is no force matching with SQL Patches or SQL Plan Baselines like there is with a SQL Profile.

      Thanks,
      Maria

  3. Thanks for your blog – you are awesome!!!
    Please fix a tiny typo:
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>’+alias’));

    change the > to >

Leave a Reply

Your email address will not be published. Required fields are marked *