Saturday, April 21, 2018

ANSI joins and QB_NAME in the cloud or not

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 

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')