Oracle Tips and Tricks — David Fitzjarrell

September 6, 2016

A Bloomin’ Mess

Filed under: General — dfitzjarrell @ 09:03

'Why is it,' he said quietly, 'that quite often even the things which are correct just don't seem to be right?'
-- Norton Juster, The Phantom Tollbooth

For some time now Oracle has used bloom filters to speed the processing of select statements; unfortunately in 11.2.0.4 converting a select statement that uses a bloom filter into an ‘insert into … select’ form causes Oracle to abandon the bloom filter altogether. Let’s look at an example provided by Jonathan Lewis that illustrates this; it will be run in 11.2.0.4 and then in 12.1.0.2, and there are differences in how the two versions generate plans for the insert statement.

This is not unusual according to Oracle support as the following bugs are listed to produce this or similar behavior:

Bug 20112932 : BLOOM FILTER IS NOT CHOOSED WHEN EXECUTING INSERT SELECT STATEMENT
11.2.0.4 - Nov 2014: "not a bug".
This cites three more documents:
  Bug 13801198  BLOOM PRUNING/FILTER NOT USED IN DML STATEMENTS 
    11.2.0.3 - March 2012 - Duplicate, base bug: 18949550
    18949550: MERGE QUERY NOT USING BLOOM FILTER
    11.2.0.3 - June 2014: fixed in 12.2 (no patches yet)

  Bug 14325392  BLOOM FILTER NOT SUPPORTED IN PARTITION WISE JOIN 
    11.2.0.3 - July 2012 - fixed in 12.1 
    (but I think this is a PX filter, not a partition filter)

  Bug 17716301  BLOOM FILTER NOT USED FOR OUTER JOIN

    Aug 2014 - hidden bug note. Patches available for 11.2.0.3
    Note 1919508.1 

It appears that bug 14325392 is the cause for this, and it is listed as fixed in 12.1. Although not completely addressed (as proven later on in this post) it is addressed to a degree in 12.1.0.2.

First a partitioned table is created, with four partitions, containing 100,000 rows, a 4-row non-partitioned table is created as a driving table for the hash join and an empty table is created to insert data into using the select statement as an ‘insert into … select’ construct:


SQL> create table pt_hash (
  2  	     id,
  3  	     grp,
  4  	     small_vc,
  5  	     padding
  6  )
  7  nologging
  8  pctfree 90 pctused 10
  9  partition by hash(grp)
 10  (
 11  	     partition p1,
 12  	     partition p2,
 13  	     partition p3,
 14  	     partition p4
 15  )
 16  as
 17  with generator as (
 18  	     select  --+ materialize
 19  		     rownum id
 20  	     from dual
 21  	     connect by
 22  		     level <= 1e4
 23  )
 24  select
 25  	     rownum			     id,
 26  	     trunc(rownum/50)		     grp,
 27  	     to_char(trunc(rownum/20))	     small_vc,
 28  	     rpad('x',100)		     padding
 29  from
 30  	     generator, generator
 31  where
 32  	     rownum <= 1e5
 33  ;

Table created.

SQL> 
SQL> create table t1
  2  as
  3  select
  4  	     rownum  id,
  5  	     rownum  n1,
  6  	     lpad(rownum,10) v1,
  7  	     rpad('x',100) padding
  8  from
  9  	     dual
 10  connect by
 11  	     rownum <= 4
 12  ;

Table created.

SQL> 
SQL> create table target (
  2  	     n1      number,
  3  	     id      number
  4  )
  5  ;

Table created.

SQL> 
SQL> set serveroutput off linesize 168 pagesize 0
SQL> alter session set statistics_level = all;

Session altered.

SQL> 

The stage is set; let’s run the basic select and see what plan 11.2.0.4 generates:


SQL> select
  2  	     /*+
  3  		     leading(t1 h1)
  4  		     use_hash(h1)    no_swap_join_inputs(h1)
  5  	     */
  6  	     t1.n1,
  7  	     h1.id
  8  from
  9  	     t1,
 10  	     pt_hash h1
 11  where
 12  	     t1.id between 2 and 3
 13  and     h1.grp = t1.n1
 14  and     h1.id <= 100
 15  ;
         2        100

SQL> 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'outline allstats last cost partition'));
SQL_ID  3rm369uarustm, child number 0
-------------------------------------
select         /*+                 leading(t1 h1)
use_hash(h1)    no_swap_join_inputs(h1)         */         t1.n1,
  h1.id from         t1,         pt_hash h1 where         t1.id between
2 and 3 and     h1.grp = t1.n1 and     h1.id <= 100

Plan hash value: 3022732788

------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |  4617 (100)|       |       |      1 |00:00:00.24 |    8377 |   8135 |       |       |          |
|*  1 |  HASH JOIN                  |         |      1 |    164 |  4617   (1)|       |       |      1 |00:00:00.24 |    8377 |   8135 |  2440K|  2440K|  771K (0)|
|   2 |   PART JOIN FILTER CREATE   | :BF0000 |      1 |      2 |     3   (0)|       |       |      2 |00:00:00.01 |       3 |      0 |       |       |          |
|*  3 |    TABLE ACCESS FULL        | T1      |      1 |      2 |     3   (0)|       |       |      2 |00:00:00.01 |       3 |      0 |       |       |          |
|   4 |   PARTITION HASH JOIN-FILTER|         |      1 |   4929 |  4614   (1)|:BF0000|:BF0000|     51 |00:00:00.24 |    8374 |   8135 |       |       |          |
|*  5 |    TABLE ACCESS FULL        | PT_HASH |      2 |   4929 |  4614   (1)|:BF0000|:BF0000|     51 |00:00:00.24 |    8374 |   8135 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      FULL(@"SEL$1" "H1"@"SEL$1")
      LEADING(@"SEL$1" "T1"@"SEL$1" "H1"@"SEL$1")
      USE_HASH(@"SEL$1" "H1"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   1 - access("H1"."GRP"="T1"."N1")
   3 - filter(("T1"."ID">=2 AND "T1"."ID"<=3))
   5 - filter("H1"."ID"<=100)

Note
-----
   - dynamic sampling used for this statement (level=2)


48 rows selected.

SQL> 

The hash join produces a bloom filter thus reducing the partition access to only the two partitions we need to generate the results. Converting this same select statement into an insert changes the plan:


SQL> insert into target(n1, id)
  2  select
  3  	     /*+
  4  		     ordered
  5  		     use_hash(h1)    no_swap_join_inputs(h1)
  6  	     */
  7  	     t1.id,
  8  	     h1.id
  9  from
 10  	     t1,
 11  	     pt_hash h1
 12  where
 13  	     t1.id between 2 and 3
 14  and     h1.grp = t1.n1
 15  and     h1.id <= 100
 16  ;

1 row created.

SQL> 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'outline allstats last cost partition'));
SQL_ID  g693aju7gwm9n, child number 0
-------------------------------------
insert into target(n1, id) select         /*+                 ordered
              use_hash(h1)    no_swap_join_inputs(h1)         */
 t1.id,         h1.id from         t1,         pt_hash h1 where
t1.id between 2 and 3 and     h1.grp = t1.n1 and     h1.id <= 100

Plan hash value: 3736574952

---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |         |      1 |        |  4617 (100)|       |       |      0 |00:00:00.22 |   16713 |   8084 |       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL |         |      1 |        |            |       |       |      0 |00:00:00.22 |   16713 |   8084 |       |       |          |
|*  2 |   HASH JOIN              |         |      1 |    164 |  4617   (1)|       |       |      1 |00:00:00.22 |   16682 |   8084 |  1969K|  1969K|  494K (0)|
|*  3 |    TABLE ACCESS FULL     | T1      |      1 |      2 |     3   (0)|       |       |      2 |00:00:00.01 |       3 |      0 |       |       |          |
|   4 |    PARTITION HASH ALL    |         |      1 |   4929 |  4614   (1)|     1 |     4 |    100 |00:00:00.22 |   16679 |   8084 |       |       |          |
|*  5 |     TABLE ACCESS FULL    | PT_HASH |      4 |   4929 |  4614   (1)|     1 |     4 |    100 |00:00:00.22 |   16679 |   8084 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"INS$1")
      FULL(@"INS$1" "TARGET"@"INS$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      FULL(@"SEL$1" "H1"@"SEL$1")
      LEADING(@"SEL$1" "T1"@"SEL$1" "H1"@"SEL$1")
      USE_HASH(@"SEL$1" "H1"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   2 - access("H1"."GRP"="T1"."N1")
   3 - filter(("T1"."ID">=2 AND "T1"."ID"<=3))
   5 - filter("H1"."ID"<=100)

Note
-----
   - dynamic sampling used for this statement (level=2)


50 rows selected.

SQL> 

The bloom filter mysteriously vanishes, and all four partitions are accessed to produce the same results as the stand-alone query produced earlier. Proving this isn’t ‘one-off’ behaviour witn 11.2.0.4 the transaction is rolled back and the same insert statement is run a second time:


SQL> rollback;

Rollback complete.

SQL> 
SQL> insert /*+ append */ into target(n1, id)
  2  select
  3  	     /*+
  4  		     ordered
  5  		     use_hash(h1)    no_swap_join_inputs(h1)
  6  	     */
  7  	     t1.id,
  8  	     h1.id
  9  from
 10  	     t1,
 11  	     pt_hash h1
 12  where
 13  	     t1.id between 2 and 3
 14  and     h1.grp = t1.n1
 15  and     h1.id <= 100
 16  ;

1 row created.

SQL> 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'outline allstats last cost partition'));
SQL_ID  10jb8cf3tx39p, child number 0
-------------------------------------
insert /*+ append */ into target(n1, id) select         /*+
    ordered                 use_hash(h1)    no_swap_join_inputs(h1)
    */         t1.id,         h1.id from         t1,         pt_hash h1
where         t1.id between 2 and 3 and     h1.grp = t1.n1 and
h1.id <= 100

Plan hash value: 3504255781

-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers | Writes |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT     |         |      1 |        |  4617 (100)|       |       |      0 |00:00:00.02 |   16716 |      1 |       |       |          |
|   1 |  LOAD AS SELECT      |         |      1 |        |            |       |       |      0 |00:00:00.02 |   16716 |      1 |   266K|   266K|  266K (0)|
|*  2 |   HASH JOIN          |         |      1 |    164 |  4617   (1)|       |       |      1 |00:00:00.02 |   16682 |      0 |  1969K|  1969K|  501K (0)|
|*  3 |    TABLE ACCESS FULL | T1      |      1 |      2 |     3   (0)|       |       |      2 |00:00:00.01 |       3 |      0 |       |       |          |
|   4 |    PARTITION HASH ALL|         |      1 |   4929 |  4614   (1)|     1 |     4 |    100 |00:00:00.02 |   16679 |      0 |       |       |          |
|*  5 |     TABLE ACCESS FULL| PT_HASH |      4 |   4929 |  4614   (1)|     1 |     4 |    100 |00:00:00.02 |   16679 |      0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"INS$1")
      FULL(@"INS$1" "TARGET"@"INS$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      FULL(@"SEL$1" "H1"@"SEL$1")
      LEADING(@"SEL$1" "T1"@"SEL$1" "H1"@"SEL$1")
      USE_HASH(@"SEL$1" "H1"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   2 - access("H1"."GRP"="T1"."N1")
   3 - filter(("T1"."ID">=2 AND "T1"."ID"<=3))
   5 - filter("H1"."ID"<=100)

Note
-----
   - dynamic sampling used for this statement (level=2)


51 rows selected.

SQL> 

Again the bloom filter vanishes; this is exactly the same plan generated by the previous run of this insert statement.

Moving to Oracle 12.1.0.2 some differing results are obtained. We set up the same tables and data as in the 11.2.0.4 run:


SQL> create table pt_hash (
  2  	     id,
  3  	     grp,
  4  	     small_vc,
  5  	     padding
  6  )
  7  nologging
  8  pctfree 90 pctused 10
  9  partition by hash(grp)
 10  (
 11  	     partition p1,
 12  	     partition p2,
 13  	     partition p3,
 14  	     partition p4
 15  )
 16  as
 17  with generator as (
 18  	     select  --+ materialize
 19  		     rownum id
 20  	     from dual
 21  	     connect by
 22  		     level <= 1e4
 23  )
 24  select
 25  	     rownum			     id,
 26  	     trunc(rownum/50)		     grp,
 27  	     to_char(trunc(rownum/20))	     small_vc,
 28  	     rpad('x',100)		     padding
 29  from
 30  	     generator, generator
 31  where
 32  	     rownum <= 1e5
 33  ;

Table created.

SQL> 
SQL> create table t1
  2  as
  3  select
  4  	     rownum  id,
  5  	     rownum  n1,
  6  	     lpad(rownum,10) v1,
  7  	     rpad('x',100) padding
  8  from
  9  	     dual
 10  connect by
 11  	     rownum <= 4
 12  ;

Table created.

SQL> 
SQL> create table target (
  2  	     n1      number,
  3  	     id      number
  4  )
  5  ;

Table created.

SQL> 
SQL> set serveroutput off linesize 168 pagesize 0
SQL> alter session set statistics_level = all;

Session altered.

SQL> alter session set tracefile_identifier='dml_bloom';

Session altered.

SQL> alter session set events '10053 trace name context forever, level 1';

Session altered.

SQL> 
SQL> select
  2  	     /*+
  3  		     leading(t1 h1)
  4  		     use_hash(h1)    no_swap_join_inputs(h1)
  5  	     */
  6  	     t1.n1,
  7  	     h1.id
  8  from
  9  	     t1,
 10  	     pt_hash h1
 11  where
 12  	     t1.id between 2 and 3
 13  and     h1.grp = t1.n1
 14  and     h1.id <= 100
 15  ;
         2        100

SQL> 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'outline allstats last cost partition'));
SQL_ID  3rm369uarustm, child number 0
-------------------------------------
select         /*+                 leading(t1 h1)
use_hash(h1)    no_swap_join_inputs(h1)         */         t1.n1,
  h1.id from         t1,         pt_hash h1 where         t1.id between
2 and 3 and     h1.grp = t1.n1 and     h1.id <= 100

Plan hash value: 3022732788

------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |  4531 (100)|       |       |      1 |00:00:00.20 |    8385 |   8279 |       |       |          |
|*  1 |  HASH JOIN                  |         |      1 |    155 |  4531   (1)|       |       |      1 |00:00:00.20 |    8385 |   8279 |  2440K|  2440K|  730K (0)|
|   2 |   PART JOIN FILTER CREATE   | :BF0000 |      1 |      2 |     2   (0)|       |       |      2 |00:00:00.01 |       3 |      0 |       |       |          |
|*  3 |    TABLE ACCESS FULL        | T1      |      1 |      2 |     2   (0)|       |       |      2 |00:00:00.01 |       3 |      0 |       |       |          |
|   4 |   PARTITION HASH JOIN-FILTER|         |      1 |   5417 |  4529   (1)|:BF0000|:BF0000|     51 |00:00:00.20 |    8382 |   8279 |       |       |          |
|*  5 |    TABLE ACCESS FULL        | PT_HASH |      2 |   5417 |  4529   (1)|:BF0000|:BF0000|     51 |00:00:00.20 |    8382 |   8279 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      FULL(@"SEL$1" "H1"@"SEL$1")
      LEADING(@"SEL$1" "T1"@"SEL$1" "H1"@"SEL$1")
      USE_HASH(@"SEL$1" "H1"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   1 - access("H1"."GRP"="T1"."N1")
   3 - filter(("T1"."ID">=2 AND "T1"."ID"<=3))
   5 - filter("H1"."ID"<=100)

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


48 rows selected.

SQL> 

As in 11.2.0.4 the select statement produces a bloom filter; a 10053 trace on the session shows this for the select and the two insert statement runs:


Consider using bloom filter between T1[T1] and H1[PT_HASH] with ??
kkoBloomFilter: join (lcdn:2 rcdn:5417 jcdn:155 limit:5417)
kkopqSingleJoinBloomNdv:Compute bloom ndv for lfro:T1[T1] and rfro:H1[PT_HASH] swap:no
kkopqSingleJoinBloomNdv: predCnt:#1 col1:(bndv:70 ndv:70) and col2:(bndv:4 ndv:2) creatorNDV:4.0 userNDV:70.0
kkopqComputeBloomNdv: predCnt:1 creatorNdv:4.0 userNdv:70.0 singleTblPred:yes
kkoBloomFilter: join ndv:2 reduction:0.000369 (limit:0.500000)  accepted
Enumerating distribution method (advanced)

It’s time to convert that select to an insert statement as see what 12.1.0.2 does with it. The first run produces the same plan as in 11.2.0.4:


SQL> insert into target(n1, id)
  2  select
  3  	     /*+
  4  		     ordered
  5  		     use_hash(h1)    no_swap_join_inputs(h1)
  6  	     */
  7  	     t1.id,
  8  	     h1.id
  9  from
 10  	     t1,
 11  	     pt_hash h1
 12  where
 13  	     t1.id between 2 and 3
 14  and     h1.grp = t1.n1
 15  and     h1.id <= 100
 16  ;

1 row created.

SQL> 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'outline allstats last cost partition'));
SQL_ID  g693aju7gwm9n, child number 0
-------------------------------------
insert into target(n1, id) select         /*+                 ordered
              use_hash(h1)    no_swap_join_inputs(h1)         */
 t1.id,         h1.id from         t1,         pt_hash h1 where
t1.id between 2 and 3 and     h1.grp = t1.n1 and     h1.id <= 100

Plan hash value: 3736574952

---------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |         |      1 |        |  4531 (100)|       |       |      0 |00:00:00.20 |   16706 |   8218 |       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL | TARGET  |      1 |        |            |       |       |      0 |00:00:00.20 |   16706 |   8218 |       |       |          |
|*  2 |   HASH JOIN              |         |      1 |    155 |  4531   (1)|       |       |      1 |00:00:00.20 |   16698 |   8218 |  1969K|  1969K|  509K (0)|
|*  3 |    TABLE ACCESS FULL     | T1      |      1 |      2 |     2   (0)|       |       |      2 |00:00:00.01 |       3 |      0 |       |       |          |
|   4 |    PARTITION HASH ALL    |         |      1 |   5417 |  4529   (1)|     1 |     4 |    100 |00:00:00.20 |   16695 |   8218 |       |       |          |
|*  5 |     TABLE ACCESS FULL    | PT_HASH |      4 |   5417 |  4529   (1)|     1 |     4 |    100 |00:00:00.20 |   16695 |   8218 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"INS$1")
      FULL(@"INS$1" "TARGET"@"INS$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      FULL(@"SEL$1" "H1"@"SEL$1")
      LEADING(@"SEL$1" "T1"@"SEL$1" "H1"@"SEL$1")
      USE_HASH(@"SEL$1" "H1"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   2 - access("H1"."GRP"="T1"."N1")
   3 - filter(("T1"."ID">=2 AND "T1"."ID"<=3))
   5 - filter("H1"."ID"<=100)

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


50 rows selected.

SQL> 

Remember that the bloom section appears in every statement executed by this session; the interesting part is the bloom filter is not used in the first run of the insert. Rolling back the initial insert and running the exact same statement again produces different results:


SQL> rollback;

Rollback complete.

SQL> 
SQL> insert /*+ append */ into target(n1, id)
  2  select
  3  	     /*+
  4  		     ordered
  5  		     use_hash(h1)    no_swap_join_inputs(h1)
  6  	     */
  7  	     t1.id,
  8  	     h1.id
  9  from
 10  	     t1,
 11  	     pt_hash h1
 12  where
 13  	     t1.id between 2 and 3
 14  and     h1.grp = t1.n1
 15  and     h1.id <= 100
 16  ;

1 row created.

SQL> 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'outline allstats last cost partition'));
SQL_ID  10jb8cf3tx39p, child number 0
-------------------------------------
insert /*+ append */ into target(n1, id) select         /*+
    ordered                 use_hash(h1)    no_swap_join_inputs(h1)
    */         t1.id,         h1.id from         t1,         pt_hash h1
where         t1.id between 2 and 3 and     h1.grp = t1.n1 and
h1.id <= 100

Plan hash value: 3662684207

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers | Writes |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT             |         |      1 |        |  4531 (100)|       |       |      0 |00:00:00.01 |    8387 |      1 |       |       |          |
|   1 |  LOAD AS SELECT              |         |      1 |        |            |       |       |      0 |00:00:00.01 |    8387 |      1 |  1036K|  1036K| 1036K (0)|
|*  2 |   HASH JOIN                  |         |      1 |    155 |  4531   (1)|       |       |      1 |00:00:00.01 |    8384 |      0 |  1969K|  1969K|  507K (0)|
|   3 |    PART JOIN FILTER CREATE   | :BF0000 |      1 |      2 |     2   (0)|       |       |      2 |00:00:00.01 |       3 |      0 |       |       |          |
|*  4 |     TABLE ACCESS FULL        | T1      |      1 |      2 |     2   (0)|       |       |      2 |00:00:00.01 |       3 |      0 |       |       |          |
|   5 |    PARTITION HASH JOIN-FILTER|         |      1 |   5417 |  4529   (1)|:BF0000|:BF0000|     51 |00:00:00.01 |    8381 |      0 |       |       |          |
|*  6 |     TABLE ACCESS FULL        | PT_HASH |      2 |   5417 |  4529   (1)|:BF0000|:BF0000|     51 |00:00:00.01 |    8381 |      0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"INS$1")
      FULL(@"INS$1" "TARGET"@"INS$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      FULL(@"SEL$1" "H1"@"SEL$1")
      LEADING(@"SEL$1" "T1"@"SEL$1" "H1"@"SEL$1")
      USE_HASH(@"SEL$1" "H1"@"SEL$1")
      END_OUTLINE_DATA
  */

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

   2 - access("H1"."GRP"="T1"."N1")
   4 - filter(("T1"."ID">=2 AND "T1"."ID"<=3))
   6 - filter("H1"."ID"<=100)

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


52 rows selected.

SQL> 

The second insert returns to the original plan and cardinalities, and restores the use of the bloom filter. As mentioned earlier Bug 14325392 appears to have been addressed, providing at least one instance when the bloom filter returns to use. That both inserts didn’t use the bloom filter may indicate that the optimizer is still having issues deciding to use it if the conditions aren’t ‘right’. But the fact remains that Oracle 12.1.0.2 will use bloom filters in when a select statement that uses them is converted to an ‘insert into … as select …’ construct. We may have to wait for 12.2 to finally be available outside of the cloud to know if the problem has been completely resolved.

Knowledge is our armor as DBAs, and the more we have the better prepared we are to do ‘battle’ with optimizer bugs and occasional errant behavior. Knowing what to expect is half the battle; the more we know, the more likely we are to be able to address issues that may arise.

Just because it’s ‘correct’ doesn’t make it ‘right’.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: