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

Friday, March 30, 2018

ENABLE_PARALLEL_DML hint in the cloud or not


So you want to update that table in parallel eh?  Up until 12.1.01 you had to first alter your session to get Parallel DML, and then issue the statement.  Then turn it off if you didn’t want other DML statements to be done in Parallel.   Not a huge deal of course, but now there is an easier way.

(BTW – Here DML, Data Manipulation Langue, refers only to insert, update, and delete.  Technically a select statement is DML, but not in this particular case.)  

So a little example here, first I check to see if my session has parallel DML enabled.  Then I run an update with the ENABLE_PARALLEL_DML  hint and check the plan.   The database version used in this example is 12.2.0.1.0.

SQL> select PDML_STATUS from v$session WHERE audsid = userenv('sessionid')
  2  /

PDML_STA
--------
DISABLED
SQL>
SQL> update /*+ ENABLE_PARALLEL_DML parallel */
  2  big_tab set EDITION_NAME = 'BOB'
  3  where owner != 'SYS';
SQL>
SQL> SELECT PLAN_TABLE_OUTPUT
  2  FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR
  3  ('&psqlid','&PCHILDNO',FORMAT=>'typical allstats last alias'))
  4  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------

SQL_ID  1qrndwafn5vrc, child number 5
-------------------------------------
update /*+ ENABLE_PARALLEL_DML parallel */ big_tab set EDITION_NAME =
'BOB' where owner != 'SYS'

Plan hash value: 2335173333

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT         |          |      1 |        |       |  1622 (100)|          |        |      |            |
|   1 |  PX COORDINATOR          |          |      1 |        |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |      0 |   2353K|   161M|  1622   (1)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    INDEX MAINTENANCE     | BIG_TAB  |      0 |        |       |            |          |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          |      0 |   2353K|   161M|  1622   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE       | :TQ10000 |      0 |   2353K|   161M|  1622   (1)| 00:00:01 |  Q1,00 | P->P | RANGE      |
|   6 |       UPDATE             | BIG_TAB  |      0 |        |       |            |          |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          |      0 |   2353K|   161M|  1622   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| BIG_TAB  |      0 |   2353K|   161M|  1622   (1)| 00:00:01 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------

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

   1 - UPD$1
   8 - UPD$1 / BIG_TAB@UPD$1

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

   8 - access(:Z>=:Z AND :Z<=:Z)
       filter("OWNER"<>'SYS')

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 8 because of degree limit

And boom!  It’s a parallel update!   Rock-n-Roll.  




If I didn’t use the ENABLE_PARALLEL_DML hint but still had the parallel hint, the statement's plan looks like what is below.  Notice that the statement is running “in parallel” but only to find the rows, then the update is applied serially.  Seriously.  Also there is a note now telling me that parallel DML is not enabled.

SQL_ID  8xyhtpc76rwfq, child number 1
-------------------------------------
update /*+ parallel */ big_tab set EDITION_NAME = 'BOB' where owner !=
'SYS'

Plan hash value: 3425284328

-------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows |E-Bytes| E-Time   |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |          |      3 |        |       |          |        |      |            |
|   1 |  UPDATE               | BIG_TAB  |      3 |        |       |          |        |      |            |
|   2 |   PX COORDINATOR      |          |      3 |        |       |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 |      0 |   2353K|   161M| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |          |      0 |   2353K|   161M| 00:00:01 |  Q1,00 | PCWC |            |
|*  5 |      TABLE ACCESS FULL| BIG_TAB  |      0 |   2353K|   161M| 00:00:01 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------

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

   5 - access(:Z>=:Z AND :Z<=:Z)
       filter("OWNER"<>'SYS')

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - PDML is disabled in current session