I’m a huge fan of the QB_NAME hint. It allows you to name your query blocks and
can help with debugging. Your queries
wouldn’t run faster or slower with this hint but it certainly helps when you
have many subqueries and/or large UNION queries. Also I’m an old dog so I still write my joins
in the WHERE clause not the way new hip way of writing them in the FROM
clause. Because of this I didn’t know of
a problem with QB_NAME until just this past week.
When using the QB_NAME hint and the new FROM clause join
(often called ANSI joins), the QB_NAME is not used. Below I have an example that illustrates this
issue. The query is an outer join
between two tables. Oracle does a
rewrite and make an in line view of the count on the big_tab table and then
joins it to the allusers_tab table.
Of interest is that in the “classic syntax” even
though the QB_NAME is
mauled by the rewrite; it does survive to be used as the table alias,
look below the plans in the Query
Block Name / Object Alias section. However when the join is in the FROM (the
newer ANSI syntax) the query is also rewritten but this time the QB_NAME is
completely gone.
EDIT: The database used for this test: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
EDIT: The database used for this test: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
I did a 10053 trace (a trace of a hard parse) on the ANSI
join query. At the top of the trace the
query block signature gets changed. The
QB_NAME starts as:
signature (): qb_name=MAIN nbfros=1 flg=0
Then right after is this line:
signature (): qb_name=SEL$1 nbfros=2 flg=0
I’m not sure what nbfros is but I’m guessing that
this is some sort of “level” and that at a 2 this is a rewrite of the query or
some such, and it takes precedence over the 1.
Also from the 10053
trace I found it interestingly that the join was actually converted back into
the classic syntax. I saw this in the
trace:
Stmt:
******* UNPARSED QUERY IS *******
SELECT /*+
QB_NAME ("MAIN") */ "A"."USERNAME"
"USERNAME",COUNT("B"."OWNER") "CNT"
FROM "OP"."ALLUSERS_TAB"
"A","OP"."BIG_TAB" "B" WHERE
"A"."USERNAME"="B"."OWNER"(+) GROUP BY
"A"."USERNAME" ORDER BY "A"."USERNAME"
However this is not the query that is parsed, the one below is
the one that was parsed. Notice it still
has the classic outer join syntax in it and it has the QB_NAME hint, but it's
effectively ignored.
Final query
after transformations:******* UNPARSED QUERY IS *******
SELECT /*+
QB_NAME ("MAIN") */ "A"."USERNAME"
"USERNAME",TO_NUMBER(TO_CHAR(NVL("VW_GBC_5"."ITEM_2",0)))
"CNT" FROM (SELECT
"B"."OWNER"
"ITEM_1",COUNT("B"."OWNER") "ITEM_2"
FROM "OP"."BIG_TAB" "B" GROUP BY "B"."OWNER")
"VW_GBC_5","OP"."ALLUSERS_TAB" "A"
WHERE
"A"."USERNAME"="VW_GBC_5"."ITEM_1"(+)
ORDER BY "A"."USERNAME"
OK
now here is the test, first the code I used for my test. The file name is ansi_join_test1.sql
set serveroutput off
select /*+ qb_name(MAIN) */ a.username, count(owner) cnt
from allusers_tab a, big_tab b
where a.username = b.owner(+)
group by a.username
order by a.username
/
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'TYPICAL
IOSTATS LAST ALIAS'))
/
select /*+ qb_name(MAIN) */ a.username, count(owner) cnt
from allusers_tab a left outer join big_tab b on a.username
= b.owner
group by a.username
order by a.username
/
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'TYPICAL
IOSTATS LAST ALIAS'))
/
The
sample run -------------------------------------------------------------------------------------------
SQL> @ansi_join_test1
USERNAME
CNT
---------------------------------------- ---------------
ANONYMOUS
0
APEX_040200
96672
There
are 48 total rows returned, delete most to conserve space.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0yy8murg5phnw, child number 0
-------------------------------------
select /*+ qb_name(MAIN) */ a.username, count(owner) cnt from
allusers_tab a, big_tab b where a.username = b.owner(+)
group by
a.username order by a.username
Plan hash value: 2416054041
-------------------------------------------------------------------------------------------------------------------------------
| Id |
Operation
| Name | Starts | E-Rows |E-Bytes|
Cost (%CPU)| E-Time | A-Rows | A-Time |
Buffers |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT
|
| 1 |
| | 11755
(100)|
| 48 |00:00:00.34 | 42681 |
| 1 | MERGE JOIN
OUTER
|
| 1 | 48 | 1344 |
11755 (2)| 00:00:01 | 48 |00:00:00.34
| 42681 |
| 2 | INDEX FULL
SCAN | USERNAME_PK | 1
| 48 | 432 |
1 (0)| 00:00:01 | 48 |00:00:00.01
| 5 |
|* 3 | SORT
JOIN
|
| 48 | 35 | 665 |
11754 (2)| 00:00:01 | 34 |00:00:00.34
| 42676 |
| 4 |
VIEW
| VW_GBC_5 | 1
| 35 | 665 | 11753 (2)|
00:00:01 | 35 |00:00:00.34 | 42676 |
| 5 | HASH GROUP
BY
|
| 1 | 35 |
210 | 11753 (2)| 00:00:01 | 35 |00:00:00.34
| 42676 |
| 6 | TABLE ACCESS
FULL| BIG_TAB | 1
| 2422K| 13M| 11657 (1)| 00:00:01
| 2422K|00:00:00.16 | 42676 |
-------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$85E9EE9D
2 - SEL$85E9EE9D / A@MAIN Notice MAIN is still here
4 - SEL$E0E6E493 / VW_GBC_5@SEL$ED2A7381
5 - SEL$E0E6E493
6 - SEL$E0E6E493 / B@MAIN
Predicate Information (identified by operation id):
---------------------------------------------------
3 -
access("A"."USERNAME"="ITEM_1")
filter("A"."USERNAME"="ITEM_1")
USERNAME
CNT
---------------------------------------- ---------------
ANONYMOUS
0
APEX_040200
96672
There are 48 total rows returned, delete most to conserve
space.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID b9bvggwsyx6uy, child number 0
-------------------------------------
select /*+ qb_name(MAIN) */ a.username, count(owner) cnt from
allusers_tab a left outer join big_tab b on a.username =
b.owner group
by a.username order by a.username
Plan hash value: 2416054041
-------------------------------------------------------------------------------------------------------------------------------
| Id |
Operation
| Name | Starts | E-Rows |E-Bytes|
Cost (%CPU)| E-Time | A-Rows | A-Time |
Buffers |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT
|
| 1 |
| | 11755
(100)|
| 48 |00:00:00.33 | 42681 |
| 1 | MERGE JOIN
OUTER
|
| 1 | 48 | 1344 |
11755 (2)| 00:00:01 | 48 |00:00:00.33
| 42681 |
| 2 | INDEX FULL
SCAN | USERNAME_PK | 1
| 48 | 432 |
1 (0)| 00:00:01 | 48 |00:00:00.01
| 5 |
|* 3 | SORT
JOIN
|
| 48 | 35 | 665 |
11754 (2)| 00:00:01 | 34 |00:00:00.33
| 42676 |
| 4 |
VIEW
| VW_GBC_5 | 1
| 35 | 665 | 11753 (2)|
00:00:01 | 35 |00:00:00.33 | 42676 |
| 5 | HASH GROUP
BY
|
| 1 | 35 |
210 | 11753 (2)| 00:00:01 | 35 |00:00:00.33
| 42676 |
| 6 | TABLE ACCESS
FULL| BIG_TAB | 1
| 2422K| 13M| 11657 (1)| 00:00:01
| 2422K|00:00:00.15 | 42676 |
-------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$6A6A6CC9
2 - SEL$6A6A6CC9 / A@SEL$1 Notice that MAIN is gone.
4 - SEL$8D772734 / VW_GBC_5@SEL$920000A1
5 - SEL$8D772734
6 - SEL$8D772734 / B@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 -
access("A"."USERNAME"="ITEM_1")
filter("A"."USERNAME"="ITEM_1")
SQL>
To get
the 10053 trace I used:
exec
dbms_sqldiag.dump_trace(p_sql_id=>'b9bvggwsyx6uy', p_child_number=>0,
p_component=>'Optimizer');
To create the tables used I used this:
drop table
big_tab ;
drop table
allusers_tab ;
create table
big_tab as select * from all_objects;
insert /*+
append */ into big_tab select * from big_tab;
commit;
insert /*+
append */ into big_tab select * from big_tab;
commit;
insert /*+
append */ into big_tab select * from big_tab;
commit;
insert /*+
append */ into big_tab select * from big_tab;
commit;
insert /*+
append */ into big_tab select * from big_tab;
create index
big_idx on big_tab(object_id);
create index
big_objtype_idx on big_tab(object_type);
create table
allusers_tab as select * from all_users ;
alter table
allusers_tab
add
constraint username_pk
primary key
(username) ;
EXEC
DBMS_STATS.SET_TABLE_PREFS('OP','BIG_TAB','METHOD_OPT','FOR ALL COLUMNS SIZE
REPEAT');
EXEC
DBMS_STATS.SET_TABLE_PREFS('OP','ALLUSERS_TAB','METHOD_OPT','FOR ALL COLUMNS
SIZE REPEAT');
EXEC
DBMS_STATS.GATHER_TABLE_STATS(USER,'BIG_TAB',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR
ALL COLUMNS SIZE 1')
EXEC
DBMS_STATS.GATHER_TABLE_STATS(USER,'ALLUSERS_TAB',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR
ALL COLUMNS SIZE 1')
