Following a question (very similar to one I had been asking myself) that appeared on twitter in response to my original posting on the new qbregistry format option in the dbms_xplan package, I’ve drafted a note of how I interpreted the execution plan so that I could more clearly see how my visualisation of the transformation maps (or fails to map) to the Query Block Registry.
I can’t guarantee the correctness of the description I’ve given here, but it’s probably fairly accurate.
Original Query (hiding the unnest and no_semijoin hints)
select
/* sel$1 */
*
from t1
where t1.owner = 'OUTLN'
and object_name in (
select /* sel$2 */
distinct t2.object_name
from t2
where t2.object_type = 'TABLE'
)
;
Transformation 1: unnest the subquery
This is visible in the Outline Information of the execution plan as the hint UNNEST(@”SEL$2″ UNNEST_INNERJ_DISTINCT_VIEW) and produces two new query blocks, the query block that “is” the unnested subquery and the query block that joins t1 to the unnested subquery vw_nso_1.
select
/* SEL$5DA710D3 */
t1.*
from
t1,
(
select /* SEL$683B0107 */
distinct
t2.object_name
from t2
where t2.object_type = 'TABLE'
) vw_nso_1
where
t1.owner = 'OUTLN'
and vw_nso_1.object_name = t1.object_name
Transformation 2: view merge (join then aggregate)
This is visible in the Outline Information of the execution plan in the hint MERGE(@”SEL$683B0107″ >”SEL$5DA710D3″). I think this produces three new query blocks; the block that “is” the merged view, a block that selects (projects) from the merged view, and the query block that the main query now becomes.
We will pretend that t1 has only 4 columns, owner, object_name, object_type, object_id.
select
/* SEL$B186933D */
vm_nwvw_2.owner,
vm_nwvw_2.object_name,
vm_nwvw_2.object_type
vm_nwvw_2.object_id
from (
select /* SEL$2F1334C4 */
-- no distinct, and t2.object_name and t1.rowid eliminated
t1.owner,
t1.object_name,
t1.object_type
t1.object_id
from (
select /* SEL$88A77D12 */
distinct
t1.rowid, -- ensures we don't duplicate t1 rows
t1.owner,
t1.object_name,
t2.object_name, -- seems redundant, but is in the trace file
t1.object_type
t1.object_id
from
t1,
t2
where
t2.object_type = 'TABLE'
and t1.owner = 'OUTLN'
and t1.object_name = t2.object_name
)
) vm_nwvw_2
;
Transformation 3: aggregate into partial join
I realised only as I was writing this note that I had completely forgotten that the plan reported a semi join even though the subquery had been hinted with a no_semijoin hint, and that the reported semi join was actually a partial join.
However, the query block registry is identical with or without a partial join (controlled by the hint [no]partial_join) so there doesn’t seem to be a transformation stage corresponding to the choice of strategy. Maybe the apparently redundant query block layer allows the variation to appear if required.
It’s Difficult
A problem I have with the query block registry is deciding what it’s telling us – and maybe the trace file and the execution plan are not trying to tell us exactly the same thing. I think it’s quite difficult, anyway, to find a good way of presenting the information that is completely informative but clear and uncluttered.
Something that may help, when you can check the trace file and the final execution plan, is the order in which query blocks are registered. Some of them may be discarded, of course, as the optimizer works through options, some of them may be marked as COPY, but if you ignore those you may be able to see from what’s left the evolution of the plan. Here, for example, is the extract of the lines where each query block is registered, taken from the CBO trace for this query, with numbering:
32:Registered qb: SEL$1 0xc26e28e8 (PARSER)
36: signature (): qb_name=SEL$1 nbfros=1 flg=0
37: fro(0): flg=4 objn=76167 hint_alias="T1"@"SEL$1"
39:Registered qb: SEL$2 0xc26e0d58 (PARSER)
43: signature (): qb_name=SEL$2 nbfros=1 flg=0
44: fro(0): flg=4 objn=76168 hint_alias="T2"@"SEL$2"
966:Registered qb: SEL$683B0107 0xbcc187c8 (SUBQ INTO VIEW FOR COMPLEX UNNEST SEL$2)
970: signature (): qb_name=SEL$683B0107 nbfros=1 flg=0
971: fro(0): flg=0 objn=76168 hint_alias="T2"@"SEL$2"
973:Registered qb: SEL$5DA710D3 0xbb1fcb10 (SUBQUERY UNNEST SEL$1; SEL$2;)
977: signature (): qb_name=SEL$5DA710D3 nbfros=2 flg=0
978: fro(0): flg=0 objn=76167 hint_alias="T1"@"SEL$1"
979: fro(1): flg=5 objn=0 hint_alias="VW_NSO_1"@"SEL$5DA710D3"
1471:Registered qb: SEL$2F1334C4 0xbcf4b210 (SPLIT/MERGE QUERY BLOCKS SEL$5DA710D3)
1475: signature (): qb_name=SEL$2F1334C4 nbfros=1 flg=0
1476: fro(0): flg=5 objn=0 hint_alias="VM_NWVW_2"@"SEL$2F1334C4"
1478:Registered qb: SEL$88A77D12 0xbcda5540 (PROJECTION VIEW FOR CVM SEL$683B0107)
1482: signature (): qb_name=SEL$88A77D12 nbfros=2 flg=0
1483: fro(0): flg=0 objn=76167 hint_alias="T1"@"SEL$1"
1484: fro(1): flg=1 objn=0 hint_alias="VW_NSO_1"@"SEL$5DA710D3"
1489:Registered qb: SEL$B186933D 0xbcda5540 (VIEW MERGE SEL$88A77D12; SEL$683B0107; SEL$5DA710D3)
1493: signature (): qb_name=SEL$B186933D nbfros=2 flg=0
1494: fro(0): flg=0 objn=76167 hint_alias="T1"@"SEL$1"
1495: fro(1): flg=0 objn=76168 hint_alias="T2"@"SEL$2"
Because it’s a very simple query you can almost see the “thinking” in the clumping of the line numbers.
- The first two registrations are the original query blocks.
- After a break the next pair is the t2 subquery being unested and the query which is the join between t1 and the unnested t2.
- After another break we see, in rapid succession, the view using the merged join view, the projection view using that merged join view, then the query block selecting from that projection.
The thing I find difficult to keep clear in my mind (when trying to describe what the trace/registry is saying, that is, not when just reading the plan) is the “doubling” effect where transformation steps often seem to produce two query blocks, one for the inline view containing the transformed object and one for the query that is now using the transformed object; and a further source of confusion appears when a query block seems to be able to peek into an inner query block to reference the objects in it. I just keep losing track of the layers!
It’s probablyh as safe as it’s going to be to read this note (unless someone points out an error). I don’t think there’s any more that I could find to say about the example.
[…] Part 2 of a pair of articles examining the qb_registry option of dbms_xplan.display_xxx() (Aug 2021) […]
Pingback by Execution Plans Catalogue | Oracle Scratchpad — January 29, 2022 @ 5:58 pm GMT Jan 29,2022 |