Differences I have noticed in the Query Block Registry section of an execution plan between Oracle 19c and 21c

Posted by FatDBA on October 10, 2022

Hi Guys,

Todays post is a quick one about the difference that I have noticed in one of the extended execution plan section ‘Query block registry‘ between Oracle 19c (19.8) and Oracle 21c (21.3). I am not going to explain about query blocks etc. here as I’ve already made few blog posts on those topics in the past, this one is about the difference that you will observe between two said database versions for QBR section in execution plans.

First I am going to use the option/flag ‘qbregistry‘ (for Query block registry info) in Oracle database version 19.16, and next will repeat same steps in Oracle 21.3. Query block registy information can also be collect from the 10053 optimizer traces, but I always notice that one’s there in CBO traces are more repetitive that what you see as a concise version through execution plans with ‘qbregistry‘ option.

So, I have already set the playground, for testing purpose, created two sample tables and have written two outer join queries. One for each table. Then combining the results of these using union all.

--
-- In Oracle 19.16 Database
--
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 9 03:17:19 2022
Version 19.8.0.0.0

SQL> explain plan for select /*+ GATHER_PLAN_STATISTICS */ *
from   toys, bricks
where  toy_id = brick_id (+)
union all
select *
from   toys, bricks
where  toy_id (+) = brick_id
and    toy_id is null;  

Explained.


SQL> select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'+alias +outline +qbregistry'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 731550672

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |     6 |   354 |     8   (0)| 00:00:01 |
|   1 |  UNION-ALL           |        |       |       |            |          |
|*  2 |   HASH JOIN OUTER    |        |     3 |   177 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL | TOYS   |     3 |    96 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | BRICKS |     3 |    81 |     2   (0)| 00:00:01 |
|*  5 |   FILTER             |        |       |       |            |          |
|*  6 |    HASH JOIN OUTER   |        |     3 |   177 |     4   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| BRICKS |     3 |    81 |     2   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL| TOYS   |     3 |    96 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - SET$1
   2 - SEL$1
   3 - SEL$1 / TOYS@SEL$1
   4 - SEL$1 / BRICKS@SEL$1
   5 - SEL$2
   7 - SEL$2 / BRICKS@SEL$2
   8 - SEL$2 / TOYS@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH(@"SEL$1" "BRICKS"@"SEL$1")
      LEADING(@"SEL$1" "TOYS"@"SEL$1" "BRICKS"@"SEL$1")
      FULL(@"SEL$1" "BRICKS"@"SEL$1")
      FULL(@"SEL$1" "TOYS"@"SEL$1")
      USE_HASH(@"SEL$2" "TOYS"@"SEL$2")
      LEADING(@"SEL$2" "BRICKS"@"SEL$2" "TOYS"@"SEL$2")
      FULL(@"SEL$2" "TOYS"@"SEL$2")
      FULL(@"SEL$2" "BRICKS"@"SEL$2")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   2 - access("TOY_ID"="BRICK_ID"(+))
   5 - filter("TOY_ID" IS NULL)
   6 - access("TOY_ID"(+)="BRICK_ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SET$1]]></n><f><h><t><![CDATA[NULL_HALIAS]]></t><s><![CDATA[SET$1]]></s></h></f></q>
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[BRICKS]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[TOYS]]></t><s><![CDATA[SEL$1]]> </s></h></f></q>
<q o="2" f="y"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[BRICKS]]></t><s><![CDATA[SEL$2]]></s></h><h><t><![CDATA[TOYS]]></t><s><![CDATA[SEL$2]]> </s></h></f></q>

73 rows selected.

SQL>
SQL>

Considering we have a two SELECT statements, one for each table, internally optimizer has created two query blocks SEL$1 and SEL$2, one for each of the select. Here its using a hint alias name ‘NULL_HALIAS‘, and points to both of the two SELECT statements used in the original query.

Next, lets execute the same statement in Oracle 21c (21.3.0) version and see the difference in QBR section.

--
-- In Oracle 21.3 Database
--
-- Skipping few sections to have more clarity about discussed topic
SQL*Plus: Release 21.0.0.0.0 - Production on Sat Oct 8 23:57:12 2022
Version 21.3.0.0.0

SQL>  select * from table(dbms_xplan.display('PLAN_TABLE',NULL,'+alias +outline +qbregistry'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 731550672
...
.....
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SET$1
   2 - SEL$1
   3 - SEL$1 / "TOYS"@"SEL$1"
   4 - SEL$1 / "BRICKS"@"SEL$1"
   5 - SEL$2
   7 - SEL$2 / "BRICKS"@"SEL$2"
   8 - SEL$2 / "TOYS"@"SEL$2"

Outline Data
-------------
......
Predicate Information (identified by operation id):
---------------------------------------------------
.....

Query Block Registry:
---------------------

  SEL$1 (PARSER) [FINAL]
  SEL$2 (PARSER) [FINAL]
  SET$1 (PARSER) [FINAL]

SQL>

Here with 21c (21.3), first thing is its no more coming in the form of an XML, The curious part out of the entire output is the ‘Query Block Registry‘ where the [FINAL] is the transformation that is chosen by the CBO. This assures that time was used on a query block which has been selected for an optimal plan.

That’s it, just a small tidbit this time! 🙂

Hope It Helped!
Prashant Dixit

This entry was posted on October 10, 2022 at 8:29 AM and is filed under Advanced, troubleshooting. Tagged: optimization, oracle, performance, troubleshooting, Tuning. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Likes