13 Replies Latest reply on Sep 9, 2020 2:23 PM by Jonathan Lewis

    Bloom filters and view using UNION ALL

    Ahmed AANGOUR

      Hi,

       

      I have a query with the following plan. The database version is 12.1.0.2 and it's running in an Exadata X6

       

      ----------------------------------------------------------------------------------------------------------------------------------------------------------

      | Id  | Operation                          | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

      ----------------------------------------------------------------------------------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT                   |                        |     1 | 17625 | 87749  (61)| 00:00:14 |       |       |        |      |            |

      |*  1 |  PX COORDINATOR                    |                        |       |       |            |          |       |       |        |      |            |

      |   2 |   PX SEND QC (RANDOM)              | :TQ10001               |     1 | 17625 | 87749  (61)| 00:00:14 |       |       |  Q1,01 | P->S | QC (RAND)  |

      |*  3 |    FILTER                          |                        |       |       |            |          |       |       |  Q1,01 | PCWC |            |

      |*  4 |     HASH JOIN                      |                        |     1 | 17625 | 87749  (61)| 00:00:14 |       |       |  Q1,01 | PCWP |            |

      |   5 |      PX RECEIVE                    |                        | 15468 |  5135K|    59   (6)| 00:00:01 |       |       |  Q1,01 | PCWP |            |

      |   6 |       PX SEND BROADCAST            | :TQ10000               | 15468 |  5135K|    59   (6)| 00:00:01 |       |       |  Q1,00 | P->P | BROADCAST  |

      |*  7 |        HASH JOIN                   |                        | 15468 |  5135K|    59   (6)| 00:00:01 |       |       |  Q1,00 | PCWP |            |

      |   8 |         PART JOIN FILTER CREATE    | :BF0000                | 14614 |  1141K|     2   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |

      |   9 |          PARTITION RANGE ITERATOR  |                        | 14614 |  1141K|     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWC |            |

      |* 10 |           TABLE ACCESS STORAGE FULL| T_INSTANCES            | 14614 |  1141K|     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWP |            |

      |  11 |         PX PARTITION RANGE AND     |                        | 40352 |    10M|    57   (6)| 00:00:01 |KEY(AP)|KEY(AP)|  Q1,00 | PCWC |            |

      |* 12 |          TABLE ACCESS STORAGE FULL | T_PRODUCTS_PART        | 40352 |    10M|    57   (6)| 00:00:01 |KEY(AP)|KEY(AP)|  Q1,00 | PCWP |            |

      |  13 |      VIEW                          |                        |    16M|   258G| 87681  (62)| 00:00:14 |       |       |  Q1,01 | PCWP |            |

      |  14 |       UNION-ALL                    |                        |       |       |            |          |       |       |  Q1,01 | PCWP |            |

      |* 15 |        FILTER                      |                        |       |       |            |          |       |       |  Q1,01 | PCWC |            |

      |  16 |         PX PARTITION RANGE ITERATOR|                        |     1 |   341 | 26894  (43)| 00:00:05 |   KEY |   KEY |  Q1,01 | PCWC |            |

      |* 17 |          TABLE ACCESS STORAGE FULL | T_EXECS_PART           |     1 |   341 | 26894  (43)| 00:00:05 |   KEY |   KEY |  Q1,01 | PCWP |            |

      |* 18 |        FILTER                      |                        |       |       |            |          |       |       |  Q1,01 | PCWC |            |

      |  19 |         PX PARTITION RANGE ITERATOR|                        |  5540K|   877M| 19929  (71)| 00:00:04 |   KEY |   KEY |  Q1,01 | PCWC |            |

      |* 20 |          TABLE ACCESS STORAGE FULL | T_MARKET_MESSAGES_PART |  5540K|   877M| 19929  (71)| 00:00:04 |   KEY |   KEY |  Q1,01 | PCWP |            |

      |* 21 |        FILTER                      |                        |       |       |            |          |       |       |  Q1,01 | PCWC |            |

      |  22 |         PX PARTITION RANGE ITERATOR|                        |  5327K|  1498M| 22054  (75)| 00:00:04 |   KEY |   KEY |  Q1,01 | PCWC |            |

      |* 23 |          TABLE ACCESS STORAGE FULL | T_ORDER_DETAILS_PART   |  5327K|  1498M| 22054  (75)| 00:00:04 |   KEY |   KEY |  Q1,01 | PCWP |            |

      |* 24 |        FILTER                      |                        |       |       |            |          |       |       |  Q1,01 | PCWC |            |

      |  25 |         PX PARTITION RANGE ITERATOR|                        |  5209K|  1336M| 18804  (62)| 00:00:03 |   KEY |   KEY |  Q1,01 | PCWC |            |

      |* 26 |          TABLE ACCESS STORAGE FULL | T_SEND_MESSAGES_PART   |  5209K|  1336M| 18804  (62)| 00:00:03 |   KEY |   KEY |  Q1,01 | PCWP |            |

      ----------------------------------------------------------------------------------------------------------------------------------------------------------

       

      For some reasons, I would to use Bloom Filters (BF) for joining the 4 tables of the view (Operation 13). The join is performed on 3 columns and these 3 columns exist in the 4 tables involved in the view.

      I tried to force the use of BF by using PX_JOIN_FILTER hint but it didn't work. I had the feeling that it was due to the UNION ALL operation so I tried the same query but with only one table in the view and the BF are used (the view is merged):

       

      ------------------------------------------------------------------------------------------------------------------------------------------------------------

      | Id  | Operation                              | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

      ------------------------------------------------------------------------------------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT                       |                      |  1381 |   864K|  4467  (75)| 00:00:01 |       |       |        |      |            |

      |   1 |  PX COORDINATOR                        |                      |       |       |            |          |       |       |        |      |            |

      |   2 |   PX SEND QC (RANDOM)                  | :TQ10003             |  1381 |   864K|  4467  (75)| 00:00:01 |       |       |  Q1,03 | P->S | QC (RAND)  |

      |*  3 |    HASH JOIN                           |                      |  1381 |   864K|  4467  (75)| 00:00:01 |       |       |  Q1,03 | PCWP |            |

      |   4 |     JOIN FILTER CREATE                 | :BF0001              |  9278 |  3116K|    14   (8)| 00:00:01 |       |       |  Q1,03 | PCWP |            |

      |   5 |      PART JOIN FILTER CREATE           | :BF0000              |  9278 |  3116K|    14   (8)| 00:00:01 |       |       |  Q1,03 | PCWP |            |

      |   6 |       PX RECEIVE                       |                      |  9278 |  3116K|    14   (8)| 00:00:01 |       |       |  Q1,03 | PCWP |            |

      |   7 |        PX SEND BROADCAST               | :TQ10002             |  9278 |  3116K|    14   (8)| 00:00:01 |       |       |  Q1,02 | P->P | BROADCAST  |

      |*  8 |         HASH JOIN BUFFERED             |                      |  9278 |  3116K|    14   (8)| 00:00:01 |       |       |  Q1,02 | PCWP |            |

      |   9 |          JOIN FILTER CREATE            | :BF0003              |  2896 |   243K|     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |

      |  10 |           PART JOIN FILTER CREATE      | :BF0002              |  2896 |   243K|     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |

      |  11 |            PX RECEIVE                  |                      |  2896 |   243K|     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |

      |  12 |             PX SEND HASH               | :TQ10000             |  2896 |   243K|     2   (0)| 00:00:01 |       |       |  Q1,00 | P->P | HASH       |

      |  13 |              PX BLOCK ITERATOR         |                      |  2896 |   243K|     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWC |            |

      |* 14 |               TABLE ACCESS STORAGE FULL| T_INSTANCES          |  2896 |   243K|     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWP |            |

      |  15 |          PX RECEIVE                    |                      |  8176 |  2059K|    12   (9)| 00:00:01 |       |       |  Q1,02 | PCWP |            |

      |  16 |           PX SEND HASH                 | :TQ10001             |  8176 |  2059K|    12   (9)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |

      |  17 |            JOIN FILTER USE             | :BF0003              |  8176 |  2059K|    12   (9)| 00:00:01 |       |       |  Q1,01 | PCWP |            |

      |  18 |             PX BLOCK ITERATOR          |                      |  8176 |  2059K|    12   (9)| 00:00:01 |KEY(AP)|KEY(AP)|  Q1,01 | PCWC |            |

      |* 19 |              TABLE ACCESS STORAGE FULL | T_PRODUCTS_PART      |  8176 |  2059K|    12   (9)| 00:00:01 |KEY(AP)|KEY(AP)|  Q1,01 | PCWP |            |

      |  20 |     JOIN FILTER USE                    | :BF0001              |  1080K|   306M|  4453  (75)| 00:00:01 |       |       |  Q1,03 | PCWP |            |

      |  21 |      PX BLOCK ITERATOR                 |                      |  1080K|   306M|  4453  (75)| 00:00:01 |KEY(AP)|KEY(AP)|  Q1,03 | PCWC |            |

      |* 22 |       TABLE ACCESS STORAGE FULL        | T_ORDER_DETAILS_PART |  1080K|   306M|  4453  (75)| 00:00:01 |KEY(AP)|KEY(AP)|  Q1,03 | PCWP |            |

      ------------------------------------------------------------------------------------------------------------------------------------------------------------

       

      So I would like to know if my assumption is correct: does UNION ALL prevents from using Bloom Filters? Is there any way to force them in my query?

       

      The query looks like this:

      SELECT

      ...........

      FROM (SELECT /*+ parallel(8) */

                 ..............

            FROM force.t_execs_part e

            UNION ALL

            SELECT /*+ parallel(8) */

                   ..............

         FROM force.t_market_messages_part

            UNION ALL

            SELECT /*+ parallel(8) */

      ..........

            FROM force.t_order_details_part

            UNION ALL

            SELECT /*+ parallel(8) */

      ...........

            FROM force.t_send_messages_part

           ) t

          ,force.t_instances i

          ,force.t_products_part p

      WHERE i.t_dt = t.t_dt

      AND i.i_id = t.i_id

      AND i.t_dt = p.t_dt

      AND i.i_id = p.i_id

      AND t.productid = p.productid

      and i.t_dt = trunc(sysdate-5)

      and p.isin like  '%XXXXXX%'

      and TO_CHAR(t.pm_timestamp,'HH24:MI:SS')  between '09:00:00' and '18:00:00'

      ;

       

      For the PX_JOIN_FILTER hint I tried both:

      - using the alias of the view: PX_JOIN_FILTER(@"SEL$1" "T"@"SEL$1")

      - using the alias of the tables: PX_JOIN_FILTER(@"SEL$4" "T_ORDER_DETAILS_PART"@"SEL$4")

       

      Thanks for helpling

       

      Cheers,

      Ahmed

        • 1. Re: Bloom filters and view using UNION ALL
          Jonathan Lewis

          What does the Predicate Information section look like?

          You may not have the join predicates you think you have, and that may be affecting the arithmetic and plans.

          In particular:

          i.t_dt = t.t_dt AND i.t_dt = p.t_dt and i.t_dt = trunc(sysdate-5)

          Transitive closure may turn this into 3 simple filter predicates rather than one filter and two join predicates.

           

          The outline information might be helpful too.

           

          There's no inherent reason why the UNION ALL should stop Bloom filters appearing, but there may be something about the arithmetic that makes Oracle pick a path where a bloom filter can't exist, or shouldn't exist.  For example - the BROADCAST distribution may be the thing that's stopping the Bloom filter - you might investigate the possibility of testing pq_distribute() hints using the hash/hash distribution.  Broadcast is often most effective on Exadata because it avoids build and probe table data spilling to disc, but the potential benefit of offloading Bloom filters is so great that a hash/hash eliminates most of the spill.

           

          Regards

          Jonathan Lewis

          • 2. Re: Bloom filters and view using UNION ALL
            Jonathan Lewis

            Following up the previous post.

             

            I've got an example in 12.1.0.2 where there's a Bloom filter created and used - but it's used on the VIEW, it's not pushed down inside the view to the separate tables.

             

            So the sys_op_bloom() is applied AFTER the table data has been through the UNION. I don't have an Exadata machine to prove it on, but I doubt if the bloom filter will be offloaded. The same thing happens in 19.3 with my example.

             

            It looks like we may need to force the "join of a union" to a "union of a join" to get the desired effect.

             

            In passing, with a Broadcast plan there is a point where a Bloom filter is "accepted" and then the optimizer tries to find an inflection point - starting from an insanely (300 digit) cost and cardinality estimate. Possibly there's an arithmetic bug somewhere in the code path that blocks what we'd like to do. This anomaly doesn't appear when the view is replaced with a table.

             

            Regards

            Jonathan Lewis

             

             

            UPDATE - I've just checked MOS: there's an enhancement request to allow Bloom filters to be pushed inside UNION ALL views - fixed in "some future version". The note says "versions below 18.1" and I've just checked the parameter lists for 18.3 and 19.3 and both have

             

            _bloom_filter_setops_enabled = true

            _bloom_pruning_setops_enabled=true

             

            both with highly suggestive descriptions - but my test doesn't push through on 19.3

             

             

             

             

            Bug 18849313 - ENH : bloom filters/pruning are pushed through union-all view (Doc ID 18849313.8)

            • 3. Re: Bloom filters and view using UNION ALL
              Jonathan Lewis

              I took out the hints I'd been using to try and force 12.1 to do the push down, and when there were no hints in place to confuse the issue 19.3 did the pushdown by default.  (12.2 didn't, and the 19.3 hints didn't help it)

               

              Bottom line - if you need this to happen you have to rewrite the query or upgrade.

               

              Regards

              Jonathan Lewis

              • 4. Re: Bloom filters and view using UNION ALL
                Ahmed AANGOUR

                Hi Jonathan,

                 

                Thanks for replying.

                 

                Here is the plan with the filter/predicates + outline sections:

                 

                ------------------------------------------------------------------------------------------------------------------------------------------------------------

                | Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

                ------------------------------------------------------------------------------------------------------------------------------------------------------------

                |   0 | SELECT STATEMENT                     |                        |   121 |  2083K| 15052  (70)| 00:00:03 |       |       |        |      |            |

                |   1 |  PX COORDINATOR                      |                        |       |       |            |          |       |       |        |      |            |

                |   2 |   PX SEND QC (RANDOM)                | :TQ10003               |   121 |  2083K| 15052  (70)| 00:00:03 |       |       |  Q1,03 | P->S | QC (RAND)  |

                |*  3 |    HASH JOIN                         |                        |   121 |  2083K| 15052  (70)| 00:00:03 |       |       |  Q1,03 | PCWP |            |

                |   4 |     PX RECEIVE                       |                        |  9705 |  3288K|    14   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |            |

                |   5 |      PX SEND BROADCAST               | :TQ10002               |  9705 |  3288K|    14   (0)| 00:00:01 |       |       |  Q1,02 | P->P | BROADCAST  |

                |*  6 |       HASH JOIN BUFFERED             |                        |  9705 |  3288K|    14   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |

                |   7 |        JOIN FILTER CREATE            | :BF0001                |  2907 |   244K|     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |

                |   8 |         PART JOIN FILTER CREATE      | :BF0000                |  2907 |   244K|     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |

                |   9 |          PX RECEIVE                  |                        |  2907 |   244K|     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |

                |  10 |           PX SEND HASH               | :TQ10000               |  2907 |   244K|     2   (0)| 00:00:01 |       |       |  Q1,00 | P->P | HASH       |

                |  11 |            PX BLOCK ITERATOR         |                        |  2907 |   244K|     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWC |            |

                |* 12 |             TABLE ACCESS STORAGE FULL| T_INSTANCES            |  2907 |   244K|     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWP |            |

                |  13 |        PX RECEIVE                    |                        |  8279 |  2110K|    12   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |

                |  14 |         PX SEND HASH                 | :TQ10001               |  8279 |  2110K|    12   (0)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |

                |  15 |          JOIN FILTER USE             | :BF0001                |  8279 |  2110K|    12   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |

                |  16 |           PX BLOCK ITERATOR          |                        |  8279 |  2110K|    12   (0)| 00:00:01 |KEY(AP)|KEY(AP)|  Q1,01 | PCWC |            |

                |* 17 |            TABLE ACCESS STORAGE FULL | T_PRODUCTS_PART        |  8279 |  2110K|    12   (0)| 00:00:01 |KEY(AP)|KEY(AP)|  Q1,01 | PCWP |            |

                |  18 |     VIEW                             |                        |  3998K|    64G| 15035  (70)| 00:00:03 |       |       |  Q1,03 | PCWP |            |

                |  19 |      UNION-ALL                       |                        |       |       |            |          |       |       |  Q1,03 | PCWP |            |

                |  20 |       PX BLOCK ITERATOR              |                        |   274 |   111K|    42  (36)| 00:00:01 |   KEY |   KEY |  Q1,03 | PCWC |            |

                |* 21 |        TABLE ACCESS STORAGE FULL     | T_EXECS_PART           |   274 |   111K|    42  (36)| 00:00:01 |   KEY |   KEY |  Q1,03 | PCWP |            |

                |  22 |       PX BLOCK ITERATOR              |                        |  1372K|   215M|  4909  (72)| 00:00:01 |   KEY |   KEY |  Q1,03 | PCWC |            |

                |* 23 |        TABLE ACCESS STORAGE FULL     | T_MARKET_MESSAGES_PART |  1372K|   215M|  4909  (72)| 00:00:01 |   KEY |   KEY |  Q1,03 | PCWP |            |

                |  24 |       PX BLOCK ITERATOR              |                        |  1330K|   371M|  5475  (76)| 00:00:01 |   KEY |   KEY |  Q1,03 | PCWC |            |

                |* 25 |        TABLE ACCESS STORAGE FULL     | T_ORDER_DETAILS_PART   |  1330K|   371M|  5475  (76)| 00:00:01 |   KEY |   KEY |  Q1,03 | PCWP |            |

                |  26 |       PX BLOCK ITERATOR              |                        |  1295K|   332M|  4609  (63)| 00:00:01 |   KEY |   KEY |  Q1,03 | PCWC |            |

                |* 27 |        TABLE ACCESS STORAGE FULL     | T_SEND_MESSAGES_PART   |  1295K|   332M|  4609  (63)| 00:00:01 |   KEY |   KEY |  Q1,03 | PCWP |            |

                ------------------------------------------------------------------------------------------------------------------------------------------------------------

                 

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

                  1 - SEL$1
                  12 - SEL$1 / I@SEL$1
                  17 - SEL$1 / P@SEL$1
                  18 - SET$1 / T@SEL$1
                  19 - SET$1
                  20 - SEL$2
                  21 - SEL$2 / E@SEL$2
                  22 - SEL$3
                  23 - SEL$3 / T_MARKET_MESSAGES_PART@SEL$3
                  24 - SEL$4
                  25 - SEL$4 / T_ORDER_DETAILS_PART@SEL$4
                  26 - SEL$5
                  27 - SEL$5 / T_SEND_MESSAGES_PART@SEL$5

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

                  /*+
                      BEGIN_OUTLINE_DATA
                      FULL(@"SEL$2" "E"@"SEL$2")
                      FULL(@"SEL$3" "T_MARKET_MESSAGES_PART"@"SEL$3")
                      FULL(@"SEL$4" "T_ORDER_DETAILS_PART"@"SEL$4")
                      FULL(@"SEL$5" "T_SEND_MESSAGES_PART"@"SEL$5")
                      PQ_CONCURRENT_UNION(@"SET$1")
                      PQ_DISTRIBUTE(@"SEL$1" "T"@"SEL$1" BROADCAST NONE)
                      PX_JOIN_FILTER(@"SEL$1" "P"@"SEL$1")
                      PQ_DISTRIBUTE(@"SEL$1" "P"@"SEL$1" HASH HASH)
                      USE_HASH(@"SEL$1" "T"@"SEL$1")
                      USE_HASH(@"SEL$1" "P"@"SEL$1")
                      LEADING(@"SEL$1" "I"@"SEL$1" "P"@"SEL$1" "T"@"SEL$1")
                      NO_ACCESS(@"SEL$1" "T"@"SEL$1")
                      FULL(@"SEL$1" "P"@"SEL$1")
                      FULL(@"SEL$1" "I"@"SEL$1")
                      OUTLINE_LEAF(@"SEL$1")
                      OUTLINE_LEAF(@"SET$1")
                      OUTLINE_LEAF(@"SEL$5")
                      OUTLINE_LEAF(@"SEL$4")
                      OUTLINE_LEAF(@"SEL$3")
                      OUTLINE_LEAF(@"SEL$2")
                      ALL_ROWS
                      OPT_PARAM('_fix_control' '7452863:0')
                      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
                      OPT_PARAM('_optimizer_gather_feedback' 'false')
                      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
                      OPT_PARAM('_px_adaptive_dist_method' 'off')
                      OPT_PARAM('_optimizer_use_feedback' 'false')
                      DB_VERSION('12.1.0.2')
                      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
                      IGNORE_OPTIM_EMBEDDED_HINTS
                      END_OUTLINE_DATA
                  */

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

                  3 - access("I"."T_DT"="T"."T_DT" AND "I"."I_ID"="T"."I_ID" AND "T"."PRODUCTID"="P"."PRODUCTID")
                  6 - access("I"."T_DT"="P"."T_DT" AND "I"."I_ID"="P"."I_ID")
                  12 - storage("I"."T_DT"=TRUNC(SYSDATE@!-5))
                      filter("I"."T_DT"=TRUNC(SYSDATE@!-5))
                  17 - storage(UPPER("P"."ISIN") LIKE '%CNE100000XX9%' AND "P"."T_DT"=TRUNC(SYSDATE@!-5) AND SYS_OP_BLOOM_FILTER(:BF0001,"P"."T_DT","P"."I_ID"))
                      filter(UPPER("P"."ISIN") LIKE '%CNE100000XX9%' AND "P"."T_DT"=TRUNC(SYSDATE@!-5) AND SYS_OP_BLOOM_FILTER(:BF0001,"P"."T_DT","P"."I_ID"))
                  21 - storage(TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                              TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND NVL(TO_CHAR(INTERNAL_FUNCTION("EXECUTION_TIME"),'HH24:MI:SS'),'%') LIKE '%'
                              AND "T_DT"=TRUNC(SYSDATE@!-5))
                      filter(TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                              TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND NVL(TO_CHAR(INTERNAL_FUNCTION("EXECUTION_TIME"),'HH24:MI:SS'),'%') LIKE '%'
                              AND "T_DT"=TRUNC(SYSDATE@!-5))
                  23 - storage(TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                              TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T_DT"=TRUNC(SYSDATE@!-5))
                      filter(TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                              TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T_DT"=TRUNC(SYSDATE@!-5))
                  25 - storage(TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                              TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T_DT"=TRUNC(SYSDATE@!-5))
                      filter(TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                              TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T_DT"=TRUNC(SYSDATE@!-5))
                  27 - storage(TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                              TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T_DT"=TRUNC(SYSDATE@!-5))
                      filter(TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                              TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T_DT"=TRUNC(SYSDATE@!-5))

                Note
                -----
                  - Degree of Parallelism is 8 because of hint

                • 5. Re: Bloom filters and view using UNION ALL
                  Ahmed AANGOUR

                  When you say "rewrite your query" you mean "use an union of join"?

                  • 6. Re: Bloom filters and view using UNION ALL
                    Ahmed AANGOUR

                    Here's the plan after forcing the use of HASH pq distribution and putting the join predicates in the following way:

                    i.t_dt = p.t_dt

                    AND i.i_id = p.i_id

                    AND p.t_dt = t.t_dt

                    AND p.i_id = t.i_id

                    AND p.productid = t.productid

                    and i.t_dt = trunc(sysdate-5)

                    and upper(p.isin) like  '%CNE100000XX9%'

                    and TO_CHAR(t.pm_timestamp,'HH24:MI:SS')  between '09:00:00' and '18:00:00'

                    and nvl(TO_CHAR(t.execution_time,'HH24:MI:SS'),'%')  like  nvl(NULL, '%')

                     

                    ------------------------------------------------------------------------------------------------------------------------------------------------------------
                    | Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
                    ------------------------------------------------------------------------------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT                     |                        |  9705 |   163M| 15052  (70)| 00:00:03 |       |       |        |      |            |
                    |   1 |  PX COORDINATOR                      |                        |       |       |            |          |       |       |        |      |            |
                    |   2 |   PX SEND QC (RANDOM)                | :TQ10004               |  9705 |   163M| 15052  (70)| 00:00:03 |       |       |  Q1,04 | P->S | QC (RAND)  |
                    |*  3 |    HASH JOIN BUFFERED                |                        |  9705 |   163M| 15052  (70)| 00:00:03 |       |       |  Q1,04 | PCWP |            |
                    |   4 |     PX RECEIVE                       |                        |  9705 |  3288K|    14   (0)| 00:00:01 |       |       |  Q1,04 | PCWP |            |
                    |   5 |      PX SEND HASH                    | :TQ10002               |  9705 |  3288K|    14   (0)| 00:00:01 |       |       |  Q1,02 | P->P | HASH       |
                    |*  6 |       HASH JOIN BUFFERED             |                        |  9705 |  3288K|    14   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
                    |   7 |        JOIN FILTER CREATE            | :BF0001                |  2907 |   244K|     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
                    |   8 |         PART JOIN FILTER CREATE      | :BF0000                |  2907 |   244K|     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
                    |   9 |          PX RECEIVE                  |                        |  2907 |   244K|     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
                    |  10 |           PX SEND HASH               | :TQ10000               |  2907 |   244K|     2   (0)| 00:00:01 |       |       |  Q1,00 | P->P | HASH       |
                    |  11 |            PX BLOCK ITERATOR         |                        |  2907 |   244K|     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWC |            |
                    |* 12 |             TABLE ACCESS STORAGE FULL| T_INSTANCES            |  2907 |   244K|     2   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWP |            |
                    |  13 |        PX RECEIVE                    |                        |  8279 |  2110K|    12   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
                    |  14 |         PX SEND HASH                 | :TQ10001               |  8279 |  2110K|    12   (0)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |
                    |  15 |          JOIN FILTER USE             | :BF0001                |  8279 |  2110K|    12   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
                    |  16 |           PX BLOCK ITERATOR          |                        |  8279 |  2110K|    12   (0)| 00:00:01 |KEY(AP)|KEY(AP)|  Q1,01 | PCWC |            |
                    |* 17 |            TABLE ACCESS STORAGE FULL | T_PRODUCTS_PART        |  8279 |  2110K|    12   (0)| 00:00:01 |KEY(AP)|KEY(AP)|  Q1,01 | PCWP |            |
                    |  18 |     PX RECEIVE                       |                        |  3998K|    64G| 15035  (70)| 00:00:03 |       |       |  Q1,04 | PCWP |            |
                    |  19 |      PX SEND HASH                    | :TQ10003               |  3998K|    64G| 15035  (70)| 00:00:03 |       |       |  Q1,03 | P->P | HASH       |
                    |  20 |       VIEW                           |                        |  3998K|    64G| 15035  (70)| 00:00:03 |       |       |  Q1,03 | PCWP |            |
                    |  21 |        UNION-ALL                     |                        |       |       |            |          |       |       |  Q1,03 | PCWP |            |
                    |  22 |         PX BLOCK ITERATOR            |                        |   274 |   111K|    42  (36)| 00:00:01 |   KEY |   KEY |  Q1,03 | PCWC |            |
                    |* 23 |          TABLE ACCESS STORAGE FULL   | T_EXECS_PART           |   274 |   111K|    42  (36)| 00:00:01 |   KEY |   KEY |  Q1,03 | PCWP |            |
                    |  24 |         PX BLOCK ITERATOR            |                        |  1372K|   215M|  4909  (72)| 00:00:01 |   KEY |   KEY |  Q1,03 | PCWC |            |
                    |* 25 |          TABLE ACCESS STORAGE FULL   | T_MARKET_MESSAGES_PART |  1372K|   215M|  4909  (72)| 00:00:01 |   KEY |   KEY |  Q1,03 | PCWP |            |
                    |  26 |         PX BLOCK ITERATOR            |                        |  1330K|   371M|  5475  (76)| 00:00:01 |   KEY |   KEY |  Q1,03 | PCWC |            |
                    |* 27 |          TABLE ACCESS STORAGE FULL   | T_ORDER_DETAILS_PART   |  1330K|   371M|  5475  (76)| 00:00:01 |   KEY |   KEY |  Q1,03 | PCWP |            |
                    |  28 |         PX BLOCK ITERATOR            |                        |  1295K|   332M|  4609  (63)| 00:00:01 |   KEY |   KEY |  Q1,03 | PCWC |            |
                    |* 29 |          TABLE ACCESS STORAGE FULL   | T_SEND_MESSAGES_PART   |  1295K|   332M|  4609  (63)| 00:00:01 |   KEY |   KEY |  Q1,03 | PCWP |            |
                    ------------------------------------------------------------------------------------------------------------------------------------------------------------

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

                       1 - SEL$1
                      12 - SEL$1 / I@SEL$1
                      17 - SEL$1 / P@SEL$1
                      20 - SET$1 / T@SEL$1
                      21 - SET$1
                      22 - SEL$2
                      23 - SEL$2 / E@SEL$2
                      24 - SEL$3
                      25 - SEL$3 / T_MARKET_MESSAGES_PART@SEL$3
                      26 - SEL$4
                      27 - SEL$4 / T_ORDER_DETAILS_PART@SEL$4
                      28 - SEL$5
                      29 - SEL$5 / T_SEND_MESSAGES_PART@SEL$5

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

                      /*+
                          BEGIN_OUTLINE_DATA
                          FULL(@"SEL$2" "E"@"SEL$2")
                          FULL(@"SEL$3" "T_MARKET_MESSAGES_PART"@"SEL$3")
                          FULL(@"SEL$4" "T_ORDER_DETAILS_PART"@"SEL$4")
                          FULL(@"SEL$5" "T_SEND_MESSAGES_PART"@"SEL$5")
                          PQ_CONCURRENT_UNION(@"SET$1")
                          PQ_DISTRIBUTE(@"SEL$1" "T"@"SEL$1" HASH HASH)
                          PX_JOIN_FILTER(@"SEL$1" "P"@"SEL$1")
                          PQ_DISTRIBUTE(@"SEL$1" "P"@"SEL$1" HASH HASH)
                          USE_HASH(@"SEL$1" "T"@"SEL$1")
                          USE_HASH(@"SEL$1" "P"@"SEL$1")
                          LEADING(@"SEL$1" "I"@"SEL$1" "P"@"SEL$1" "T"@"SEL$1")
                          NO_ACCESS(@"SEL$1" "T"@"SEL$1")
                          FULL(@"SEL$1" "P"@"SEL$1")
                          FULL(@"SEL$1" "I"@"SEL$1")
                          OUTLINE_LEAF(@"SEL$1")
                          OUTLINE_LEAF(@"SET$1")
                          OUTLINE_LEAF(@"SEL$5")
                          OUTLINE_LEAF(@"SEL$4")
                          OUTLINE_LEAF(@"SEL$3")
                          OUTLINE_LEAF(@"SEL$2")
                          ALL_ROWS
                          OPT_PARAM('_fix_control' '7452863:0')
                          OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
                          OPT_PARAM('_optimizer_gather_feedback' 'false')
                          OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
                          OPT_PARAM('_px_adaptive_dist_method' 'off')
                          OPT_PARAM('_optimizer_use_feedback' 'false')
                          DB_VERSION('12.1.0.2')
                          OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
                          IGNORE_OPTIM_EMBEDDED_HINTS
                          END_OUTLINE_DATA
                      */

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

                       3 - access("P"."T_DT"="T"."T_DT" AND "P"."I_ID"="T"."I_ID" AND "P"."PRODUCTID"="T"."PRODUCTID")
                       6 - access("I"."T_DT"="P"."T_DT" AND "I"."I_ID"="P"."I_ID")
                      12 - storage("I"."T_DT"=TRUNC(SYSDATE@!-5))
                           filter("I"."T_DT"=TRUNC(SYSDATE@!-5))
                      17 - storage(UPPER("P"."ISIN") LIKE '%CNE100000XX9%' AND "P"."T_DT"=TRUNC(SYSDATE@!-5) AND SYS_OP_BLOOM_FILTER(:BF0001,"P"."T_DT","P"."I_ID"))
                           filter(UPPER("P"."ISIN") LIKE '%CNE100000XX9%' AND "P"."T_DT"=TRUNC(SYSDATE@!-5) AND SYS_OP_BLOOM_FILTER(:BF0001,"P"."T_DT","P"."I_ID"))
                      23 - storage(TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                  TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND NVL(TO_CHAR(INTERNAL_FUNCTION("EXECUTION_TIME"),'HH24:MI:SS'),'%') LIKE '%'
                                  AND "T_DT"=TRUNC(SYSDATE@!-5))
                           filter(TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                  TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND NVL(TO_CHAR(INTERNAL_FUNCTION("EXECUTION_TIME"),'HH24:MI:SS'),'%') LIKE '%'
                                  AND "T_DT"=TRUNC(SYSDATE@!-5))
                      25 - storage(TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                  TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T_DT"=TRUNC(SYSDATE@!-5))
                           filter(TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                  TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T_DT"=TRUNC(SYSDATE@!-5))
                      27 - storage(TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                  TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T_DT"=TRUNC(SYSDATE@!-5))
                           filter(TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                  TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T_DT"=TRUNC(SYSDATE@!-5))
                      29 - storage(TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                  TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T_DT"=TRUNC(SYSDATE@!-5))
                           filter(TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                  TO_CHAR(INTERNAL_FUNCTION("PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T_DT"=TRUNC(SYSDATE@!-5))

                    Note
                    -----
                       - Degree of Parallelism is 8 because of hint

                    • 7. Re: Bloom filters and view using UNION ALL
                      Ahmed AANGOUR

                      Here is the explanation why I did need bloom filters to be used in that query. I think it'd be interested to share it with you.

                       

                      Actually my query is an extract from a view which is used in an APEX application report.

                      All the filters especially T_DT column (which is the partition column) and the ISIN column( which allows to filter on a particular product) are chosen by the application user.

                      The APEX developer decided to retrieve only 30k rows from this query, that's why in the final query run by APEX we have something like this: where rownum<=30001

                       

                      So the final query actually looks like:

                      select * from

                      (

                      SELECT

                      ...........

                      FROM (SELECT /*+ parallel(8) */

                                ..............

                            FROM force.t_execs_part e

                            UNION ALL

                            SELECT /*+ parallel(8) */

                                  ..............

                        FROM force.t_market_messages_part

                            UNION ALL

                            SELECT /*+ parallel(8) */

                      ..........

                            FROM force.t_order_details_part

                            UNION ALL

                            SELECT /*+ parallel(8) */

                      ...........

                            FROM force.t_send_messages_part

                          ) t

                          ,force.t_instances i

                          ,force.t_products_part p

                      WHERE i.t_dt = t.t_dt

                      AND i.i_id = t.i_id

                      AND i.t_dt = p.t_dt

                      AND i.i_id = p.i_id

                      AND t.productid = p.productid

                      and i.t_dt = trunc(sysdate-5)

                      and p.isin like  '%XXXXXX%'

                      and TO_CHAR(t.pm_timestamp,'HH24:MI:SS')  between '09:00:00' and '18:00:00'

                      )

                      where rownum <=30000

                      ;

                       

                      When the user does not filter on any product (no filter on ISIN column) the query is very fast. The plan is the following one:

                       

                      Global Information
                      ------------------------------
                      Status              :  DONE (ALL ROWS)
                      Instance ID        :  1
                      Session            :  XXXX (1667:40208)
                      SQL ID              :  atc6hgvzjgd6y
                      SQL Execution ID    :  16777216
                      Execution Started  :  09/08/2020 10:29:36
                      First Refresh Time  :  09/08/2020 10:29:36
                      Last Refresh Time  :  09/08/2020 10:29:39
                      Duration            :  3s
                      Module/Action      :  SQL*Plus/-
                      Service            :  XXXXX
                      Program            :  sqlplus.exe
                      Fetch Calls        :  302

                      Global Stats
                      ==================================================================================================================
                      | Elapsed |  Cpu  |    IO    | Application | Concurrency | Cluster  |  Other  | Fetch | Buffer | Read | Read  |
                      | Time(s) | Time(s) | Waits(s) |  Waits(s)  |  Waits(s)  | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
                      ==================================================================================================================
                      |      10 |    9.05 |    0.64 |        0.00 |        0.00 |    0.27 |    0.04 |  302 |  13598 |  102 |  54MB |
                      ==================================================================================================================

                      Parallel Execution Details (DOP=8 , Servers Allocated=8)
                      ===========================================================================================================================================================
                      |      Name      | Type  | Server# | Elapsed |  Cpu  |    IO    | Application | Concurrency | Cluster  |  Other  | Buffer | Read | Read  | Wait Events |
                      |                |      |        | Time(s) | Time(s) | Waits(s) |  Waits(s)  |  Waits(s)  | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes | (sample #)  |
                      ===========================================================================================================================================================
                      | PX Coordinator | QC    |        |    0.21 |    0.16 |    0.00 |        0.00 |            |    0.00 |    0.04 |    32 |    7 | 224KB |            |
                      | p00g          | Set 1 |      1 |    1.19 |    1.08 |    0.07 |            |        0.00 |    0.04 |          |  1690 |    8 |  5MB |            |
                      | p00h          | Set 1 |      2 |    1.21 |    1.11 |    0.07 |            |        0.00 |    0.03 |          |  1691 |  11 |  5MB |            |
                      | p00i          | Set 1 |      3 |    1.23 |    1.11 |    0.09 |            |        0.00 |    0.03 |          |  1692 |    3 |  2MB |            |
                      | p00j          | Set 1 |      4 |    1.23 |    1.12 |    0.08 |            |        0.00 |    0.03 |          |  1711 |  21 |  12MB |            |
                      | p00k          | Set 1 |      5 |    1.23 |    1.12 |    0.08 |            |        0.00 |    0.03 |          |  1691 |  16 |  10MB |            |
                      | p00l          | Set 1 |      6 |    1.22 |    1.11 |    0.07 |            |        0.00 |    0.04 |          |  1690 |    5 |  3MB |            |
                      | p00m          | Set 1 |      7 |    1.22 |    1.11 |    0.08 |            |        0.00 |    0.04 |          |  1702 |  16 |  9MB |            |
                      | p00n          | Set 1 |      8 |    1.26 |    1.14 |    0.09 |            |        0.00 |    0.03 |          |  1699 |  15 |  9MB |            |
                      ===========================================================================================================================================================

                      SQL Plan Monitoring Details (Plan Hash Value=2739421464)
                      ====================================================================================================================================================================================
                      | Id |              Operation              |          Name          |  Rows  | Cost  |  Time    | Start  | Execs |  Rows  | Read | Read  |  Mem  | Activity | Activity Detail |
                      |    |                                      |                        | (Estim) |      | Active(s) | Active |      | (Actual) | Reqs | Bytes | (Max) |  (%)    |  (# samples)  |
                      ====================================================================================================================================================================================
                      |  0 | SELECT STATEMENT                    |                        |        |      |        3 |    +1 |    1 |    30001 |      |      |      |          |                |
                      |  1 |  COUNT STOPKEY                      |                        |        |      |        3 |    +1 |    1 |    30001 |      |      |      |          |                |
                      |  2 |    PX COORDINATOR                    |                        |        |      |        3 |    +1 |    9 |    30001 |    7 | 224KB |      |          |                |
                      |  3 |    PX SEND QC (RANDOM)              | :TQ10000              |    194K | 15053 |        2 |    +2 |    8 |    30400 |      |      |      |          |                |
                      |  4 |      COUNT STOPKEY                  |                        |        |      |        2 |    +2 |    8 |    30400 |      |      |      |          |                |
                      |  5 |      HASH JOIN                      |                        |    194K | 15053 |        2 |    +2 |    8 |    30400 |      |      |  38M |          |                |
                      |  6 |        PART JOIN FILTER CREATE      | :BF0000                |    2907 |    2 |        1 |    +2 |    8 |    23256 |      |      |      |          |                |
                      |  7 |        PARTITION RANGE SINGLE      |                        |    2907 |    2 |        1 |    +2 |    8 |    23256 |      |      |      |          |                |
                      |  8 |          TABLE ACCESS STORAGE FULL  | T_INSTANCES            |    2907 |    2 |        1 |    +2 |    8 |    23256 |    2 | 960KB |      |          |                |
                      |  9 |        HASH JOIN                    |                        |    166K | 15051 |        2 |    +2 |    8 |    30400 |      |      |  393M |          |                |
                      | 10 |        PARTITION RANGE SINGLE      |                        |    166K |    14 |        1 |    +2 |    8 |      1M |      |      |      |          |                |
                      | 11 |          TABLE ACCESS STORAGE FULL  | T_PRODUCTS_PART        |    166K |    14 |        1 |    +2 |    8 |      1M |  83 |  46MB |      |    88.89 | Cpu (8)        |
                      | 12 |        VIEW                        |                        |      4M | 15035 |        2 |    +2 |    8 |    30400 |      |      |      |          |                |
                      | 13 |          UNION-ALL                  |                        |        |      |        2 |    +2 |    8 |    30400 |      |      |      |          |                |
                      | 14 |          PX BLOCK ITERATOR          |                        |    274 |    42 |        2 |    +2 |    8 |    30400 |      |      |      |          |                |
                      | 15 |            TABLE ACCESS STORAGE FULL | T_EXECS_PART          |    274 |    42 |        2 |    +2 |    8 |    30400 |  10 |  7MB |      |          |                |
                      | 16 |          PX BLOCK ITERATOR          |                        |      1M |  4909 |          |        |      |          |      |      |      |          |                |
                      | 17 |            TABLE ACCESS STORAGE FULL | T_MARKET_MESSAGES_PART |      1M |  4909 |          |        |      |          |      |      |      |          |                |
                      | 18 |          PX BLOCK ITERATOR          |                        |      1M |  5475 |          |        |      |          |      |      |      |          |                |
                      | 19 |            TABLE ACCESS STORAGE FULL | T_ORDER_DETAILS_PART  |      1M |  5475 |          |        |      |          |      |      |      |          |                |
                      | 20 |          PX BLOCK ITERATOR          |                        |      1M |  4609 |          |        |      |          |      |      |      |          |                |
                      | 21 |            TABLE ACCESS STORAGE FULL | T_SEND_MESSAGES_PART  |      1M |  4609 |          |        |      |          |      |      |      |          |                |
                      ====================================================================================================================================================================================

                       

                      It's very fast because without any filter on ISIN column the whole partition of T_PRODUCTS_PART is returned (166K rows in the partition but since we have 8 PX slaves scanning the partition we have 1M of rows in the A-ROWS column) so the join with the view returns some rows immediately, the threshold  of 30000 rows is quickly  reached and the scan of the tables involved in the view is stopped.

                      However, if we limit the number of rows returned by T_PRODUCTS_PART the query becomes slower:

                       

                      Global Information
                      ------------------------------
                      Status              :  DONE (ALL ROWS)
                      Instance ID         :  1
                      Session             :  XXXX (1667:40208)
                      SQL ID              :  4yumxjxvsvsw2
                      SQL Execution ID    :  16777216
                      Execution Started   :  09/08/2020 10:09:05
                      First Refresh Time  :  09/08/2020 10:09:05
                      Last Refresh Time   :  09/08/2020 10:24:18
                      Duration            :  913s
                      Module/Action       :  SQL*Plus/-
                      Service             :  xxxx
                      Program             :  sqlplus.exe
                      Fetch Calls         :  3

                      Global Stats
                      =============================================================================================================================
                      | Elapsed |   Cpu   |    IO    | Application | Concurrency | Cluster  |  Other   | Fetch | Buffer | Read | Read  |   Cell   |
                      | Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes | Offload  |
                      =============================================================================================================================
                      |    5590 |    5543 |       43 |        0.00 |        0.00 |     0.41 |     3.80 |     3 |   908K | 100K |  21GB | -244.83% |
                      =============================================================================================================================

                      Parallel Execution Details (DOP=8 , Servers Allocated=8)
                      ===============================================================================================================================================================================================
                      |      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    | Application | Concurrency | Cluster  |  Other   | Buffer | Read  | Read  |   Cell   |             Wait Events             |
                      |                |       |         | Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Waits(s) |  Gets  | Reqs  | Bytes | Offload  |             (sample #)              |
                      ===============================================================================================================================================================================================
                      | PX Coordinator | QC    |         |    0.08 |    0.08 |          |        0.00 |             |          |          |     32 |       |     . |     NaN% |                                     |
                      | p00g           | Set 1 |       1 |     860 |     853 |     7.03 |             |             |     0.05 |     0.26 |   119K | 15485 |   3GB | -244.83% | cell smart table scan (7)           |
                      | p00h           | Set 1 |       2 |      43 |      43 |     0.40 |             |        0.00 |     0.00 |     0.02 |  22842 |   967 | 209MB | -212.50% |                                     |
                      | p00i           | Set 1 |       3 |     869 |     860 |     6.54 |             |        0.00 |     0.06 |     2.36 |   132K | 16228 |   3GB | -244.83% | cell smart table scan (9)           |
                      | p00j           | Set 1 |       4 |     369 |     366 |     2.82 |             |             |     0.01 |     0.02 |  93991 |  5556 |   1GB | -212.50% | cell smart table scan (4)           |
                      | p00k           | Set 1 |       5 |     858 |     851 |     6.19 |             |             |     0.03 |     1.13 |   137K | 15507 |   3GB | -244.83% | cell smart table scan (3)           |
                      | p00l           | Set 1 |       6 |     872 |     865 |     6.18 |             |             |     0.08 |          |   124K | 15090 |   3GB | -244.83% | cell smart table scan (4)           |
                      | p00m           | Set 1 |       7 |     860 |     853 |     7.33 |             |        0.00 |     0.15 |     0.02 |   162K | 15770 |   3GB | -233.33% | cell single block physical read (1) |
                      |                |       |         |         |         |          |             |             |          |          |        |       |       |          | cell smart table scan (9)           |
                      | p00n           | Set 1 |       8 |     858 |     852 |     6.25 |             |        0.00 |     0.02 |          |   117K | 15538 |   3GB | -233.33% | cell smart table scan (6)           |
                      ===============================================================================================================================================================================================

                      SQL Plan Monitoring Details (Plan Hash Value=2739421464)
                      ====================================================================================================================================================================================================================
                      | Id |              Operation               |          Name          |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read  | Read  |   Cell   |  Mem  | Activity |           Activity Detail           |
                      |    |                                      |                        | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs  | Bytes | Offload  | (Max) |   (%)    |             (# samples)             |
                      ====================================================================================================================================================================================================================
                      |  0 | SELECT STATEMENT                     |                        |         |       |        43 |   +871 |     1 |      144 |       |       |          |       |          |                                     |
                      |  1 |   COUNT STOPKEY                      |                        |         |       |        43 |   +871 |     1 |      144 |       |       |          |       |          |                                     |
                      |  2 |    PX COORDINATOR                    |                        |         |       |        43 |   +871 |     9 |      144 |       |       |          |       |          |                                     |
                      |  3 |     PX SEND QC (RANDOM)              | :TQ10000               |    9705 | 15052 |         4 |   +871 |     8 |      144 |       |       |          |       |          |                                     |
                      |  4 |      COUNT STOPKEY                   |                        |         |       |         4 |   +871 |     8 |      144 |       |       |          |       |          |                                     |
                      |  5 |       HASH JOIN                      |                        |    9705 | 15052 |       873 |     +2 |     8 |      144 |       |       |          |   38M |          |                                     |
                      |  6 |        PART JOIN FILTER CREATE       | :BF0000                |    2907 |     2 |       870 |     +2 |     8 |    23256 |       |       |          |       |          |                                     |
                      |  7 |         PARTITION RANGE SINGLE       |                        |    2907 |     2 |       870 |     +2 |     8 |    23256 |       |       |          |       |          |                                     |
                      |  8 |          TABLE ACCESS STORAGE FULL   | T_INSTANCES            |    2907 |     2 |       870 |     +2 |     8 |    23256 |       |       |          |       |          |                                     |
                      |  9 |        HASH JOIN                     |                        |    8279 | 15049 |       912 |     +2 |     8 |      144 |       |       |          |   10M |     2.44 | Cpu (136)                           |
                      | 10 |         PARTITION RANGE SINGLE       |                        |    8279 |    12 |       870 |     +2 |     8 |        8 |       |       |          |       |          |                                     |
                      | 11 |          TABLE ACCESS STORAGE FULL   | T_PRODUCTS_PART        |    8279 |    12 |       870 |     +2 |     8 |        8 |       |       |          |       |          |                                     |
                      | 12 |         VIEW                         |                        |      4M | 15035 |       912 |     +2 |     8 |       1G |       |       |          |       |    12.03 | Cpu (671)                           |
                      | 13 |          UNION-ALL                   |                        |         |       |       913 |     +1 |     8 |       1G |       |       |          |       |    38.03 | Cpu (2122)                          |
                      | 14 |           PX BLOCK ITERATOR          |                        |     274 |    42 |       871 |     +2 |     8 |       2M |       |       |          |       |          |                                     |
                      | 15 |            TABLE ACCESS STORAGE FULL | T_EXECS_PART           |     274 |    42 |       872 |     +1 |   133 |       2M |   587 | 112MB |          |       |     0.32 | Cpu (18)                            |
                      | 16 |           PX BLOCK ITERATOR          |                        |      1M |  4909 |       885 |     +6 |     8 |     494M |       |       |          |       |          |                                     |
                      | 17 |            TABLE ACCESS STORAGE FULL | T_MARKET_MESSAGES_PART |      1M |  4909 |       886 |     +5 |   130 |     494M | 28607 |   6GB | -203.03% |   25M |    18.57 | Cpu (1023)                          |
                      |    |                                      |                        |         |       |           |        |       |          |       |       |          |       |          | cell smart table scan (13)          |
                      | 18 |           PX BLOCK ITERATOR          |                        |      1M |  5475 |       601 |   +302 |     8 |     477M |       |       |          |       |          |                                     |
                      | 19 |            TABLE ACCESS STORAGE FULL | T_ORDER_DETAILS_PART   |      1M |  5475 |       601 |   +302 |   129 |     477M | 35851 |   6GB | -316.67% |   25M |    14.77 | Cpu (808)                           |
                      |    |                                      |                        |         |       |           |        |       |          |       |       |          |       |          | cell single block physical read (1) |
                      |    |                                      |                        |         |       |           |        |       |          |       |       |          |       |          | cell smart table scan (15)          |
                      | 20 |           PX BLOCK ITERATOR          |                        |      1M |  4609 |       342 |   +572 |     8 |     463M |       |       |          |       |          |                                     |
                      | 21 |            TABLE ACCESS STORAGE FULL | T_SEND_MESSAGES_PART   |      1M |  4609 |       342 |   +572 |   123 |     463M | 35086 |   8GB | -212.50% |   25M |    13.85 | Cpu (759)                           |
                      |    |                                      |                        |         |       |           |        |       |          |       |       |          |       |          | cell smart table scan (14)          |
                      ====================================================================================================================================================================================================================

                       

                      Instead of 3sec, now the query takes 15min to return only 144 rows. That's because there's only one row retrieved from the T_PRODUCTS_PART table and because of that the limit of 30k rows is never reached, we have to wait for all the tables involved in the view to be scanned. If the CBO was able to use Bloom Filters, the PRODUCTID join column could be pushed down to the probe rows source (the tables of the view) and thanks to bloom filters offloading feature the scan of tables would be very fast. That's why I wanted to use bloom filters.

                      As pointed out by Jonathan, there is a bug preventing the CBO from using bloom filters in a UNION-ALL view.

                      So I rewrote the view using a materialized CTE to retrieve the product informations and I added a join with this CTE in each table of the view:

                      WITH LST_PRODUCT AS

                      (

                      select /*+ materialize */ * from force.t_products_part p

                      where p.T_DT = TRUNC(SYSDATE-5)

                      and upper(p.isin) like  '%CNE100000XX9%')

                      select * from

                      (

                      SELECT

                      ....

                      FROM (SELECT /*+ parallel(8) */

                                  .....

                            FROM force.t_execs_part t1

                         JOIN LST_PRODUCT p

                        ON  p.t_dt   = t1.t_dt

                        AND p.i_id  = t1.i_id

                        AND p.productid = t1.productid

                            UNION ALL

                            SELECT /*+ parallel(8) */

                                   ....

                            UNION ALL

                            SELECT /*+ parallel(8) */

                                  ....

                            FROM force.t_order_details_part t3

                         JOIN LST_PRODUCT p

                          ON  p.t_dt    = t3.t_dt

                          AND p.i_id   = t3.i_id

                          AND p.productid  = t3.productid

                            UNION ALL

                            SELECT /*+ parallel(8) */

                                   ....

                            FROM force.t_send_messages_part t4

                          JOIN LST_PRODUCT p

                          ON  p.t_dt    = t4.t_dt

                          AND p.i_id   = t4.i_id

                          AND p.productid  = t4.productid

                           ) t

                          ,force.t_instances i

                          ,force.LST_PRODUCT p

                      WHERE

                      i.t_dt = p.t_dt

                      AND i.i_id = p.i_id

                      AND p.t_dt = t.t_dt

                      AND p.i_id = t.i_id

                      AND p.productid = t.productid

                      and i.t_dt = trunc(sysdate-5)

                      and upper(p.isin) like  '%CNE100000XX9%'

                      and TO_CHAR(t.pm_timestamp,'HH24:MI:SS')  between '09:00:00' and '18:00:00'

                      and nvl(TO_CHAR(t.execution_time,'HH24:MI:SS'),'%')  like  nvl(NULL, '%')

                      )

                      where rownum <= 30001

                      ;

                       

                      Now the query takes about 22 seconds to return 144 rows and you can notice that now BFs are used and offloaded:

                      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                      | Id  | Operation                                     | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
                      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT                              |                             |     1 | 19117 | 15062  (70)| 00:00:03 |       |       |        |      |            |
                      |   1 |  TEMP TABLE TRANSFORMATION                    |                             |       |       |            |          |       |       |        |      |            |
                      |   2 |   PX COORDINATOR                              |                             |       |       |            |          |       |       |        |      |            |
                      |   3 |    PX SEND QC (RANDOM)                        | :TQ10000                    |  8279 |  2118K|    12   (0)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
                      |   4 |     LOAD AS SELECT (TEMP SEGMENT MERGE)       | SYS_TEMP_0FD9D7ED6_A113BAA7 |       |       |            |          |       |       |  Q1,00 | PCWP |            |
                      |   5 |      PX BLOCK ITERATOR                        |                             |  8279 |  2118K|    12   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWC |            |
                      |*  6 |       TABLE ACCESS STORAGE FULL               | T_PRODUCTS_PART             |  8279 |  2118K|    12   (0)| 00:00:01 |   KEY |   KEY |  Q1,00 | PCWP |            |
                      |*  7 |   COUNT STOPKEY                               |                             |       |       |            |          |       |       |        |      |            |
                      |   8 |    PX COORDINATOR                             |                             |       |       |            |          |       |       |        |      |            |
                      |   9 |     PX SEND QC (RANDOM)                       | :TQ20005                    |     1 | 19117 | 15050  (70)| 00:00:03 |       |       |  Q2,05 | P->S | QC (RAND)  |
                      |* 10 |      COUNT STOPKEY                            |                             |       |       |            |          |       |       |  Q2,05 | PCWC |            |
                      |* 11 |       HASH JOIN                               |                             |     1 | 19117 | 15050  (70)| 00:00:03 |       |       |  Q2,05 | PCWP |            |
                      |  12 |        JOIN FILTER CREATE                     | :BF0001                     |     1 | 19031 | 15048  (70)| 00:00:03 |       |       |  Q2,05 | PCWP |            |
                      |  13 |         PART JOIN FILTER CREATE               | :BF0000                     |     1 | 19031 | 15048  (70)| 00:00:03 |       |       |  Q2,05 | PCWP |            |
                      |  14 |          PX RECEIVE                           |                             |     1 | 19031 | 15048  (70)| 00:00:03 |       |       |  Q2,05 | PCWP |            |
                      |  15 |           PX SEND BROADCAST                   | :TQ20004                    |     1 | 19031 | 15048  (70)| 00:00:03 |       |       |  Q2,04 | P->P | BROADCAST  |
                      |* 16 |            HASH JOIN BUFFERED                 |                             |     1 | 19031 | 15048  (70)| 00:00:03 |       |       |  Q2,04 | PCWP |            |
                      |  17 |             PX RECEIVE                        |                             |  8279 |    13M|     2   (0)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |  18 |              PX SEND BROADCAST                | :TQ20000                    |  8279 |    13M|     2   (0)| 00:00:01 |       |       |  Q2,00 | P->P | BROADCAST  |
                      |* 19 |               VIEW                            |                             |  8279 |    13M|     2   (0)| 00:00:01 |       |       |  Q2,00 | PCWP |            |
                      |  20 |                PX BLOCK ITERATOR              |                             |  8279 |  2118K|     2   (0)| 00:00:01 |       |       |  Q2,00 | PCWC |            |
                      |  21 |                 TABLE ACCESS STORAGE FULL     | SYS_TEMP_0FD9D7ED6_A113BAA7 |  8279 |  2118K|     2   (0)| 00:00:01 |       |       |  Q2,00 | PCWP |            |
                      |  22 |             VIEW                              |                             | 25192 |   415M| 15046  (70)| 00:00:03 |       |       |  Q2,04 | PCWP |            |
                      |  23 |              UNION-ALL                        |                             |       |       |            |          |       |       |  Q2,04 | PCWP |            |
                      |  24 |               PX SELECTOR                     |                             |       |       |            |          |       |       |  Q2,04 | PCWP |            |
                      |* 25 |                HASH JOIN                      |                             |   274 |   171K|    44  (35)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |  26 |                 PART JOIN FILTER CREATE       | :BF0002                     |  8279 |  1811K|     2   (0)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |* 27 |                  VIEW                         |                             |  8279 |  1811K|     2   (0)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |  28 |                   TABLE ACCESS STORAGE FULL   | SYS_TEMP_0FD9D7ED6_A113BAA7 |  8279 |  2118K|     2   (0)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |  29 |                 PARTITION RANGE SINGLE        |                             |   274 |   111K|    42  (36)| 00:00:01 |KEY(AP)|KEY(AP)|  Q2,04 | PCWP |            |
                      |* 30 |                  TABLE ACCESS STORAGE FULL    | T_EXECS_PART                |   274 |   111K|    42  (36)| 00:00:01 |KEY(AP)|KEY(AP)|  Q2,04 | PCWP |            |
                      |* 31 |               HASH JOIN                       |                             |  8307 |  3155K|  4912  (72)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |  32 |                JOIN FILTER CREATE             | :BF0004                     |  8279 |  1811K|     2   (0)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |  33 |                 PART JOIN FILTER CREATE       | :BF0003                     |  8279 |  1811K|     2   (0)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |  34 |                  PX RECEIVE                   |                             |  8279 |  1811K|     2   (0)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |  35 |                   PX SEND BROADCAST           | :TQ20001                    |  8279 |  1811K|     2   (0)| 00:00:01 |       |       |  Q2,01 | P->P | BROADCAST  |
                      |* 36 |                    VIEW                       |                             |  8279 |  1811K|     2   (0)| 00:00:01 |       |       |  Q2,01 | PCWP |            |
                      |  37 |                     PX BLOCK ITERATOR         |                             |  8279 |  2118K|     2   (0)| 00:00:01 |       |       |  Q2,01 | PCWC |            |
                      |  38 |                      TABLE ACCESS STORAGE FULL| SYS_TEMP_0FD9D7ED6_A113BAA7 |  8279 |  2118K|     2   (0)| 00:00:01 |       |       |  Q2,01 | PCWP |            |
                      |  39 |                JOIN FILTER USE                | :BF0004                     |  1372K|   215M|  4909  (72)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |  40 |                 PX BLOCK ITERATOR             |                             |  1372K|   215M|  4909  (72)| 00:00:01 |KEY(AP)|KEY(AP)|  Q2,04 | PCWC |            |
                      |* 41 |                  TABLE ACCESS STORAGE FULL    | T_MARKET_MESSAGES_PART      |  1372K|   215M|  4909  (72)| 00:00:01 |KEY(AP)|KEY(AP)|  Q2,04 | PCWP |            |
                      |* 42 |               HASH JOIN                       |                             |  8306 |  4193K|  5478  (75)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |  43 |                JOIN FILTER CREATE             | :BF0006                     |  8279 |  1811K|     2   (0)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |  44 |                 PART JOIN FILTER CREATE       | :BF0005                     |  8279 |  1811K|     2   (0)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |  45 |                  PX RECEIVE                   |                             |  8279 |  1811K|     2   (0)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |  46 |                   PX SEND BROADCAST           | :TQ20002                    |  8279 |  1811K|     2   (0)| 00:00:01 |       |       |  Q2,02 | P->P | BROADCAST  |
                      |* 47 |                    VIEW                       |                             |  8279 |  1811K|     2   (0)| 00:00:01 |       |       |  Q2,02 | PCWP |            |
                      |  48 |                     PX BLOCK ITERATOR         |                             |  8279 |  2118K|     2   (0)| 00:00:01 |       |       |  Q2,02 | PCWC |            |
                      |  49 |                      TABLE ACCESS STORAGE FULL| SYS_TEMP_0FD9D7ED6_A113BAA7 |  8279 |  2118K|     2   (0)| 00:00:01 |       |       |  Q2,02 | PCWP |            |
                      |  50 |                JOIN FILTER USE                | :BF0006                     |  1330K|   371M|  5475  (76)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |  51 |                 PX BLOCK ITERATOR             |                             |  1330K|   371M|  5475  (76)| 00:00:01 |KEY(AP)|KEY(AP)|  Q2,04 | PCWC |            |
                      |* 52 |                  TABLE ACCESS STORAGE FULL    | T_ORDER_DETAILS_PART        |  1330K|   371M|  5475  (76)| 00:00:01 |KEY(AP)|KEY(AP)|  Q2,04 | PCWP |            |
                      |* 53 |               HASH JOIN                       |                             |  8305 |  3998K|  4611  (63)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |  54 |                JOIN FILTER CREATE             | :BF0008                     |  8279 |  1811K|     2   (0)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |  55 |                 PART JOIN FILTER CREATE       | :BF0007                     |  8279 |  1811K|     2   (0)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |  56 |                  PX RECEIVE                   |                             |  8279 |  1811K|     2   (0)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |  57 |                   PX SEND BROADCAST           | :TQ20003                    |  8279 |  1811K|     2   (0)| 00:00:01 |       |       |  Q2,03 | P->P | BROADCAST  |
                      |* 58 |                    VIEW                       |                             |  8279 |  1811K|     2   (0)| 00:00:01 |       |       |  Q2,03 | PCWP |            |
                      |  59 |                     PX BLOCK ITERATOR         |                             |  8279 |  2118K|     2   (0)| 00:00:01 |       |       |  Q2,03 | PCWC |            |
                      |  60 |                      TABLE ACCESS STORAGE FULL| SYS_TEMP_0FD9D7ED6_A113BAA7 |  8279 |  2118K|     2   (0)| 00:00:01 |       |       |  Q2,03 | PCWP |            |
                      |  61 |                JOIN FILTER USE                | :BF0008                     |  1295K|   332M|  4609  (63)| 00:00:01 |       |       |  Q2,04 | PCWP |            |
                      |  62 |                 PX BLOCK ITERATOR             |                             |  1295K|   332M|  4609  (63)| 00:00:01 |KEY(AP)|KEY(AP)|  Q2,04 | PCWC |            |
                      |* 63 |                  TABLE ACCESS STORAGE FULL    | T_SEND_MESSAGES_PART        |  1295K|   332M|  4609  (63)| 00:00:01 |KEY(AP)|KEY(AP)|  Q2,04 | PCWP |            |
                      |  64 |        JOIN FILTER USE                        | :BF0001                     |  2907 |   244K|     2   (0)| 00:00:01 |       |       |  Q2,05 | PCWP |            |
                      |  65 |         PX BLOCK ITERATOR                     |                             |  2907 |   244K|     2   (0)| 00:00:01 |KEY(AP)|KEY(AP)|  Q2,05 | PCWC |            |
                      |* 66 |          TABLE ACCESS STORAGE FULL            | T_INSTANCES                 |  2907 |   244K|     2   (0)| 00:00:01 |KEY(AP)|KEY(AP)|  Q2,05 | PCWP |            |
                      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

                         1 - SEL$335DD26A
                         2 - SEL$1
                         6 - SEL$1        / P@SEL$1
                        19 - SEL$D67CB2D6 / P@SEL$3
                        21 - SEL$D67CB2D6 / T1@SEL$D67CB2D6
                        22 - SET$1        / T@SEL$3
                        23 - SET$1
                        25 - SEL$83632299
                        27 - SEL$D67CB2D5 / P@SEL$4
                        28 - SEL$D67CB2D5 / T1@SEL$D67CB2D5
                        30 - SEL$83632299 / T1@SEL$4
                        31 - SEL$8C13D988
                        36 - SEL$D67CB2D4 / P@SEL$5
                        38 - SEL$D67CB2D4 / T1@SEL$D67CB2D4
                        41 - SEL$8C13D988 / T2@SEL$5
                        42 - SEL$529045D0
                        47 - SEL$D67CB2D3 / P@SEL$6
                        49 - SEL$D67CB2D3 / T1@SEL$D67CB2D3
                        52 - SEL$529045D0 / T3@SEL$6
                        53 - SEL$CFDACA1E
                        58 - SEL$D67CB2D2 / P@SEL$7
                        60 - SEL$D67CB2D2 / T1@SEL$D67CB2D2
                        63 - SEL$CFDACA1E / T4@SEL$7
                        66 - SEL$335DD26A / I@SEL$3

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

                        /*+
                            BEGIN_OUTLINE_DATA
                            FULL(@"SEL$1" "P"@"SEL$1")
                            FULL(@"SEL$D67CB2D2" "T1"@"SEL$D67CB2D2")
                            FULL(@"SEL$D67CB2D3" "T1"@"SEL$D67CB2D3")
                            FULL(@"SEL$D67CB2D4" "T1"@"SEL$D67CB2D4")
                            FULL(@"SEL$D67CB2D5" "T1"@"SEL$D67CB2D5")
                            USE_HASH(@"SEL$83632299" "T1"@"SEL$4")
                            LEADING(@"SEL$83632299" "P"@"SEL$4" "T1"@"SEL$4")
                            FULL(@"SEL$83632299" "T1"@"SEL$4")
                            NO_ACCESS(@"SEL$83632299" "P"@"SEL$4")
                            PX_JOIN_FILTER(@"SEL$8C13D988" "T2"@"SEL$5")
                            PQ_DISTRIBUTE(@"SEL$8C13D988" "T2"@"SEL$5" BROADCAST NONE)
                            USE_HASH(@"SEL$8C13D988" "T2"@"SEL$5")
                            LEADING(@"SEL$8C13D988" "P"@"SEL$5" "T2"@"SEL$5")
                            FULL(@"SEL$8C13D988" "T2"@"SEL$5")
                            NO_ACCESS(@"SEL$8C13D988" "P"@"SEL$5")
                            PX_JOIN_FILTER(@"SEL$529045D0" "T3"@"SEL$6")
                            PQ_DISTRIBUTE(@"SEL$529045D0" "T3"@"SEL$6" BROADCAST NONE)
                            USE_HASH(@"SEL$529045D0" "T3"@"SEL$6")
                            LEADING(@"SEL$529045D0" "P"@"SEL$6" "T3"@"SEL$6")
                            FULL(@"SEL$529045D0" "T3"@"SEL$6")
                            NO_ACCESS(@"SEL$529045D0" "P"@"SEL$6")
                            PX_JOIN_FILTER(@"SEL$CFDACA1E" "T4"@"SEL$7")
                            PQ_DISTRIBUTE(@"SEL$CFDACA1E" "T4"@"SEL$7" BROADCAST NONE)
                            USE_HASH(@"SEL$CFDACA1E" "T4"@"SEL$7")
                            LEADING(@"SEL$CFDACA1E" "P"@"SEL$7" "T4"@"SEL$7")
                            FULL(@"SEL$CFDACA1E" "T4"@"SEL$7")
                            NO_ACCESS(@"SEL$CFDACA1E" "P"@"SEL$7")
                            PQ_CONCURRENT_UNION(@"SET$1")
                            FULL(@"SEL$D67CB2D6" "T1"@"SEL$D67CB2D6")
                            PX_JOIN_FILTER(@"SEL$335DD26A" "I"@"SEL$3")
                            PQ_DISTRIBUTE(@"SEL$335DD26A" "I"@"SEL$3" BROADCAST NONE)
                            PQ_DISTRIBUTE(@"SEL$335DD26A" "T"@"SEL$3" BROADCAST NONE)
                            USE_HASH(@"SEL$335DD26A" "I"@"SEL$3")
                            USE_HASH(@"SEL$335DD26A" "T"@"SEL$3")
                            LEADING(@"SEL$335DD26A" "P"@"SEL$3" "T"@"SEL$3" "I"@"SEL$3")
                            FULL(@"SEL$335DD26A" "I"@"SEL$3")
                            NO_ACCESS(@"SEL$335DD26A" "T"@"SEL$3")
                            NO_ACCESS(@"SEL$335DD26A" "P"@"SEL$3")
                            OUTLINE(@"SEL$3")
                            OUTLINE(@"SEL$2")
                            OUTLINE(@"SEL$7")
                            OUTLINE(@"SEL$11")
                            OUTLINE(@"SEL$6")
                            OUTLINE(@"SEL$10")
                            OUTLINE(@"SEL$5")
                            OUTLINE(@"SEL$9")
                            OUTLINE(@"SEL$4")
                            OUTLINE(@"SEL$8")
                            OUTLINE(@"SEL$1")
                            MERGE(@"SEL$3")
                            OUTLINE_LEAF(@"SEL$335DD26A")
                            OUTLINE_LEAF(@"SEL$D67CB2D6")
                            OUTLINE_LEAF(@"SET$1")
                            MERGE(@"SEL$7")
                            OUTLINE_LEAF(@"SEL$CFDACA1E")
                            OUTLINE_LEAF(@"SEL$D67CB2D2")
                            MERGE(@"SEL$6")
                            OUTLINE_LEAF(@"SEL$529045D0")
                            OUTLINE_LEAF(@"SEL$D67CB2D3")
                            MERGE(@"SEL$5")
                            OUTLINE_LEAF(@"SEL$8C13D988")
                            OUTLINE_LEAF(@"SEL$D67CB2D4")
                            MERGE(@"SEL$4")
                            OUTLINE_LEAF(@"SEL$83632299")
                            OUTLINE_LEAF(@"SEL$D67CB2D5")
                            OUTLINE_LEAF(@"SEL$1")
                            ALL_ROWS
                            OPT_PARAM('_fix_control' '7452863:0')
                            OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
                            OPT_PARAM('_optimizer_gather_feedback' 'false')
                            OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
                            OPT_PARAM('_px_adaptive_dist_method' 'off')
                            OPT_PARAM('_optimizer_use_feedback' 'false')
                            DB_VERSION('12.1.0.2')
                            OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
                            IGNORE_OPTIM_EMBEDDED_HINTS
                            END_OUTLINE_DATA
                        */

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

                         6 - storage(UPPER("P"."ISIN") LIKE '%CNE100000XX9%' AND "P"."T_DT"=TRUNC(SYSDATE@!-5))
                             filter(UPPER("P"."ISIN") LIKE '%CNE100000XX9%' AND "P"."T_DT"=TRUNC(SYSDATE@!-5))
                         7 - filter(ROWNUM<=30001)
                        10 - filter(ROWNUM<=30001)
                        11 - access("I"."T_DT"="P"."T_DT" AND "I"."I_ID"="P"."I_ID")
                        16 - access("P"."T_DT"="T"."T_DT" AND "P"."I_ID"="T"."I_ID" AND "P"."PRODUCTID"="T"."PRODUCTID")
                        19 - filter(UPPER("P"."ISIN") LIKE '%CNE100000XX9%' AND "P"."T_DT"=TRUNC(SYSDATE@!-5))
                        25 - access("P"."T_DT"="T1"."T_DT" AND "P"."I_ID"="T1"."I_ID" AND "P"."PRODUCTID"="T1"."PRODUCTID")
                        27 - filter("P"."T_DT"=TRUNC(SYSDATE@!-5))
                        30 - storage(TO_CHAR(INTERNAL_FUNCTION("T1"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                    TO_CHAR(INTERNAL_FUNCTION("T1"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND NVL(TO_CHAR(INTERNAL_FUNCTION("T1"."EXECUTION_TIME"),'HH24:MI:SS'),'%') LIKE '%' AND
                                    "T1"."T_DT"=TRUNC(SYSDATE@!-5))
                             filter(TO_CHAR(INTERNAL_FUNCTION("T1"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                    TO_CHAR(INTERNAL_FUNCTION("T1"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND NVL(TO_CHAR(INTERNAL_FUNCTION("T1"."EXECUTION_TIME"),'HH24:MI:SS'),'%') LIKE '%' AND
                                    "T1"."T_DT"=TRUNC(SYSDATE@!-5))
                        31 - access("P"."T_DT"="T2"."T_DT" AND "P"."I_ID"="T2"."I_ID" AND "P"."PRODUCTID"="T2"."PRODUCTID")
                        36 - filter("P"."T_DT"=TRUNC(SYSDATE@!-5))
                        41 - storage(TO_CHAR(INTERNAL_FUNCTION("T2"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                    TO_CHAR(INTERNAL_FUNCTION("T2"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T2"."T_DT"=TRUNC(SYSDATE@!-5) AND
                                    SYS_OP_BLOOM_FILTER(:BF0004,"T2"."T_DT","T2"."I_ID","T2"."PRODUCTID"))
                             filter(TO_CHAR(INTERNAL_FUNCTION("T2"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                    TO_CHAR(INTERNAL_FUNCTION("T2"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T2"."T_DT"=TRUNC(SYSDATE@!-5) AND
                                    SYS_OP_BLOOM_FILTER(:BF0004,"T2"."T_DT","T2"."I_ID","T2"."PRODUCTID"))
                        42 - access("P"."T_DT"="T3"."T_DT" AND "P"."I_ID"="T3"."I_ID" AND "P"."PRODUCTID"="T3"."PRODUCTID")
                        47 - filter("P"."T_DT"=TRUNC(SYSDATE@!-5))
                        52 - storage(TO_CHAR(INTERNAL_FUNCTION("T3"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                    TO_CHAR(INTERNAL_FUNCTION("T3"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T3"."T_DT"=TRUNC(SYSDATE@!-5) AND
                                    SYS_OP_BLOOM_FILTER(:BF0006,"T3"."T_DT","T3"."I_ID","T3"."PRODUCTID"))
                             filter(TO_CHAR(INTERNAL_FUNCTION("T3"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                    TO_CHAR(INTERNAL_FUNCTION("T3"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T3"."T_DT"=TRUNC(SYSDATE@!-5) AND
                                    SYS_OP_BLOOM_FILTER(:BF0006,"T3"."T_DT","T3"."I_ID","T3"."PRODUCTID"))
                        53 - access("P"."T_DT"="T4"."T_DT" AND "P"."I_ID"="T4"."I_ID" AND "P"."PRODUCTID"="T4"."PRODUCTID")
                        58 - filter("P"."T_DT"=TRUNC(SYSDATE@!-5))
                        63 - storage(TO_CHAR(INTERNAL_FUNCTION("T4"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                    TO_CHAR(INTERNAL_FUNCTION("T4"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T4"."T_DT"=TRUNC(SYSDATE@!-5) AND
                                    SYS_OP_BLOOM_FILTER(:BF0008,"T4"."T_DT","T4"."I_ID","T4"."PRODUCTID"))
                             filter(TO_CHAR(INTERNAL_FUNCTION("T4"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                    TO_CHAR(INTERNAL_FUNCTION("T4"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T4"."T_DT"=TRUNC(SYSDATE@!-5) AND
                                    SYS_OP_BLOOM_FILTER(:BF0008,"T4"."T_DT","T4"."I_ID","T4"."PRODUCTID"))
                        66 - storage("I"."T_DT"=TRUNC(SYSDATE@!-5) AND SYS_OP_BLOOM_FILTER(:BF0001,"I"."T_DT","I"."I_ID"))
                             filter("I"."T_DT"=TRUNC(SYSDATE@!-5) AND SYS_OP_BLOOM_FILTER(:BF0001,"I"."T_DT","I"."I_ID"))

                      Note
                      -----
                         - Degree of Parallelism is 8 because of hint
                        

                      • 8. Re: Bloom filters and view using UNION ALL
                        Jonathan Lewis

                        Ahmed,

                         

                        Thanks for taking the trouble to supply all the information I had asked for.

                         

                        Thanks to time differences I'd already gone ahead with a few best-guess experiments before the details arrived, but I still appreciatet the fact that you produced all those results.

                         

                        Yes (to your question) - rewrite as a union of joins, which is exactly what you did, and I really like the way you took advantage of subquery factoring there.  Might it be worth testing a version where the factored subquery is a join between instances and product_parts - or might that just produce a large and slowed SYS_TEMP table ?

                         

                        Nice explanation of the underlying business problem. The technical problem is just a more sophisticated version of this note that I published 10 years ago.

                         

                        Regards

                        Jonathan Lewis

                        • 9. Re: Bloom filters and view using UNION ALL
                          Ahmed AANGOUR

                          Hi Jonathan,

                           

                          I tried to run the same query but instead of filtering on a single date I filtered on a week (range of 5 dates). The performance I got is not good at all depsite the fact that Bloom filters are still used.

                          Here is the plan:

                          Global Information
                          ------------------------------
                          Status              :  DONE (ALL ROWS)
                          Instance ID        :  1
                          Session            :  XXX (912:50397)
                          SQL ID              :  3w8v7qtqxh7bz
                          SQL Execution ID    :  16777216
                          Execution Started  :  09/09/2020 10:27:15
                          First Refresh Time  :  09/09/2020 10:27:15
                          Last Refresh Time  :  09/09/2020 10:40:47
                          Duration            :  812s
                          Module/Action      :  SQL*Plus/-
                          Service            :  xxxx
                          Program            :  sqlplus.exe
                          DOP Downgrade      :  4%
                          Fetch Calls        :  141

                          Global Stats
                          =============================================================================================================================================
                          | Elapsed |  Cpu  |    IO    | Application | Concurrency | Cluster  |  Other  | Fetch | Buffer | Read  | Read  | Write | Write |  Cell  |
                          | Time(s) | Time(s) | Waits(s) |  Waits(s)  |  Waits(s)  | Waits(s) | Waits(s) | Calls |  Gets  | Reqs  | Bytes | Reqs  | Bytes | Offload |
                          =============================================================================================================================================
                          |    1633 |    1138 |      494 |        0.02 |        0.00 |    0.08 |    1.70 |  141 |    2M | 94671 |  68GB |    46 |  42MB |  65.16% |
                          =============================================================================================================================================

                          Parallel Execution Details (DOP=8 , Servers Requested=24 , Servers Allocated=23)
                          ================================================================================================================================================================================================
                          |      Name      | Type  | Group# | Server# | Elapsed |  Cpu  |    IO    | Application | Concurrency | Cluster  |  Other  | Buffer | Read  | Read  | Write | Write |  Cell  | Wait Events |
                          |                |      |        |        | Time(s) | Time(s) | Waits(s) |  Waits(s)  |  Waits(s)  | Waits(s) | Waits(s) |  Gets  | Reqs  | Bytes | Reqs  | Bytes | Offload  | (sample #)  |
                          ================================================================================================================================================================================================
                          | PX Coordinator | QC    |        |        |    0.23 |    0.17 |    0.01 |        0.02 |            |    0.00 |    0.03 |    486 |    17 | 544KB |      |    . |    NaN% |            |
                          | p00w          | Set 1 |      1 |      1 |    0.16 |    0.16 |    0.01 |            |            |          |          |  1917 |    1 | 32768 |    9 |  8MB | -100.00% |            |
                          | p00x          | Set 1 |      1 |      2 |    0.13 |    0.12 |    0.01 |            |            |          |          |  1854 |    1 | 32768 |    9 |  8MB | -100.00% |            |
                          | p00y          | Set 1 |      1 |      3 |    0.13 |    0.13 |          |            |            |          |          |  1566 |      |    . |      |    . |    NaN% |            |
                          | p00z          | Set 1 |      1 |      4 |    0.13 |    0.12 |    0.01 |            |            |    0.00 |          |  1747 |    1 | 32768 |    9 |  8MB | -100.00% |            |
                          | p010          | Set 1 |      1 |      5 |    0.12 |    0.12 |    0.01 |            |            |    0.00 |          |  1631 |    2 | 65536 |    9 |  8MB | -100.00% |            |
                          | p011          | Set 1 |      1 |      6 |    0.00 |    0.00 |          |            |            |          |          |    43 |      |    . |      |    . |    NaN% |            |
                          | p012          | Set 1 |      1 |      7 |    0.00 |    0.00 |          |            |            |          |          |      1 |      |    . |      |    . |    NaN% |            |
                          | p00w          | Set 1 |      2 |      1 |    0.25 |    0.21 |    0.04 |            |            |    0.00 |          |  1143 |    37 |  15MB |      |    . |    NaN% |            |
                          | p00x          | Set 1 |      2 |      2 |    0.19 |    0.13 |    0.06 |            |        0.00 |    0.00 |          |    644 |    36 |  13MB |      |    . |    NaN% |            |
                          | p00y          | Set 1 |      2 |      3 |    0.18 |    0.14 |    0.04 |            |            |    0.01 |          |  1128 |    37 |  15MB |      |    . |    NaN% |            |
                          | p00z          | Set 1 |      2 |      4 |    0.14 |    0.12 |    0.02 |            |            |    0.00 |          |    360 |    16 |  6MB |      |    . |    NaN% |            |
                          | p010          | Set 1 |      2 |      5 |    0.14 |    0.12 |    0.02 |            |            |    0.00 |          |    525 |    19 |  6MB |      |    . |    NaN% |            |
                          | p011          | Set 1 |      2 |      6 |    0.14 |    0.12 |    0.01 |            |            |    0.00 |          |    795 |    16 |  6MB |      |    . |    NaN% |            |
                          | p012          | Set 1 |      2 |      7 |    0.16 |    0.13 |    0.03 |            |            |    0.00 |          |    442 |    22 |  9MB |      |    . |    NaN% |            |
                          | p013          | Set 1 |      2 |      8 |    0.17 |    0.13 |    0.03 |            |            |    0.00 |          |  1057 |    37 |  14MB |      |    . |    NaN% |            |
                          | p014          | Set 2 |      2 |      1 |    667 |    556 |      110 |            |        0.00 |    0.00 |    1.67 |  679K | 34623 |  21GB |      |    . |  40.12% |            |
                          | p015          | Set 2 |      2 |      2 |      85 |      84 |    0.90 |            |            |    0.05 |          |  33549 |  659 | 531MB |    4 |  4MB |  -1.01% |            |
                          | p016          | Set 2 |      2 |      3 |    204 |      11 |      193 |            |        0.00 |    0.00 |          |  558K | 17720 |  17GB |    6 |  6MB |  99.17% |            |
                          | p017          | Set 2 |      2 |      4 |      64 |    3.64 |      60 |            |        0.00 |    0.00 |          |  530K | 16800 |  16GB |      |    . |  99.73% |            |
                          | p018          | Set 2 |      2 |      5 |    611 |    482 |      130 |            |        0.00 |    0.00 |          |  441K | 24627 |  13GB |      |    . |  21.26% |            |
                          | p019          | Set 2 |      2 |      6 |    0.05 |    0.05 |          |            |            |          |          |        |      |    . |      |    . |    NaN% |            |
                          | p01a          | Set 2 |      2 |      7 |    0.04 |    0.04 |          |            |            |          |          |        |      |    . |      |    . |    NaN% |            |
                          | p01b          | Set 2 |      2 |      8 |    0.04 |    0.04 |          |            |            |          |          |        |      |    . |      |    . |    NaN% |            |
                          ================================================================================================================================================================================================

                          SQL Plan Monitoring Details (Plan Hash Value=2240676893)
                          =========================================================================================================================================================================================================================================
                          | Id |                    Operation                    |            Name            |  Rows  | Cost  |  Time    | Start  | Execs |  Rows  | Read  | Read  | Write | Write |  Cell  |  Mem  | Temp  | Activity | Activity Detail |
                          |    |                                                  |                            | (Estim) |      | Active(s) | Active |      | (Actual) | Reqs  | Bytes | Reqs  | Bytes | Offload | (Max) | (Max) |  (%)    |  (# samples)  |
                          =========================================================================================================================================================================================================================================
                          |  0 | SELECT STATEMENT                                  |                            |        |      |        2 |  +811 |    1 |    13975 |      |      |      |      |        |      |      |          |                |
                          |  1 |  TEMP TABLE TRANSFORMATION                      |                            |        |      |        2 |  +811 |    1 |    13975 |      |      |      |      |        |      |      |          |                |
                          |  2 |    PX COORDINATOR                                |                            |        |      |        1 |    +0 |    8 |        8 |      |      |      |      |        |      |      |          |                |
                          |  3 |    PX SEND QC (RANDOM)                          | :TQ10000                    |  40352 |    57 |        1 |    +0 |    7 |        8 |      |      |      |      |        |      |      |          |                |
                          |  4 |      LOAD AS SELECT (TEMP SEGMENT MERGE)          |                            |        |      |        1 |    +0 |    7 |        8 |    1 | 32768 |    4 | 128KB |        |      |      |          |                |
                          |  5 |      FILTER                                      |                            |        |      |        1 |    +0 |    7 |        5 |      |      |      |      |        |      |      |          |                |
                          |  6 |        PX PARTITION RANGE ITERATOR                |                            |  40352 |    57 |        1 |    +0 |    7 |        5 |      |      |      |      |        |      |      |          |                |
                          |  7 |        TABLE ACCESS STORAGE FULL                | T_PRODUCTS_PART            |  40352 |    57 |        1 |    +0 |    7 |        5 |      |      |      |      |        |      |      |          |                |
                          |  8 |    COUNT STOPKEY                                  |                            |        |      |        2 |  +811 |    1 |    13975 |      |      |      |      |        |      |      |          |                |
                          |  9 |    PX COORDINATOR                                |                            |        |      |        2 |  +811 |    17 |    13975 |    17 | 544KB |      |      |        |      |      |          |                |
                          | 10 |      PX SEND QC (RANDOM)                          | :TQ20005                    |  14614 | 87974 |        2 |  +811 |    8 |    13975 |      |      |      |      |        |      |      |          |                |
                          | 11 |      COUNT STOPKEY                              |                            |        |      |        2 |  +811 |    8 |    13975 |      |      |      |      |        |      |      |          |                |
                          | 12 |        FILTER                                    |                            |        |      |        2 |  +811 |    8 |    13975 |      |      |      |      |        |      |      |          |                |
                          | 13 |        HASH JOIN                                |                            |  14614 | 87974 |        2 |  +811 |    8 |    13975 |      |      |      |      |        |  147M |      |          |                |
                          | 14 |          JOIN FILTER CREATE                      | :BF0001                    |  40352 | 87971 |        2 |  +811 |    8 |    112K |      |      |      |      |        |      |      |          |                |
                          | 15 |          PART JOIN FILTER CREATE                | :BF0000                    |  40352 | 87971 |        2 |  +811 |    8 |    112K |      |      |      |      |        |      |      |          |                |
                          | 16 |            PX RECEIVE                            |                            |  40352 | 87971 |        2 |  +811 |    8 |    112K |      |      |      |      |        |      |      |          |                |
                          | 17 |            PX SEND BROADCAST                    | :TQ20004                    |  40352 | 87971 |        1 |  +811 |    8 |    112K |      |      |      |      |        |      |      |          |                |
                          | 18 |              HASH JOIN BUFFERED                  |                            |  40352 | 87971 |      810 |    +2 |    8 |    13975 |    10 |  10MB |    10 |  10MB |        |  42M |  17M |          |                |
                          | 19 |              PX RECEIVE                          |                            |  40352 |    3 |        84 |    +2 |    8 |      40 |      |      |      |      |        |      |      |          |                |
                          | 20 |                PX SEND BROADCAST                  | :TQ20000                    |  40352 |    3 |        1 |    +85 |    8 |      40 |      |      |      |      |        |      |      |          |                |
                          | 21 |                VIEW                              |                            |  40352 |    3 |        1 |    +85 |    8 |        5 |      |      |      |      |        |      |      |          |                |
                          | 22 |                  PX BLOCK ITERATOR                |                            |  40352 |    3 |        1 |    +85 |    8 |        5 |      |      |      |      |        |      |      |          |                |
                          | 23 |                  TABLE ACCESS STORAGE FULL      | SYS_TEMP_0FD9D86E6_A113BAA7 |  40352 |    3 |        1 |    +85 |    99 |        5 |  103 |  40MB |      |      |        |      |      |          |                |
                          | 24 |              VIEW                                |                            |    121K | 87968 |      703 |    +2 |    8 |    13975 |      |      |      |      |        |      |      |          |                |
                          | 25 |                UNION-ALL                          |                            |        |      |      703 |    +2 |    8 |    13975 |      |      |      |      |        |      |      |          |                |
                          | 26 |                PX SELECTOR                      |                            |        |      |        67 |    +2 |    8 |    5841 |      |      |      |      |        |      |      |          |                |
                          | 27 |                  FILTER                          |                            |        |      |        67 |    +2 |    8 |    5841 |      |      |      |      |        |      |      |          |                |
                          | 28 |                  HASH JOIN                      |                            |      1 | 27163 |      638 |    +2 |    8 |    5841 |      |      |      |      |        |    3M |      |          |                |
                          | 29 |                    PART JOIN FILTER CREATE        | :BF0002                    |  40352 |    3 |        1 |    +2 |    8 |        5 |      |      |      |      |        |      |      |          |                |
                          | 30 |                    VIEW                          |                            |  40352 |    3 |        1 |    +2 |    8 |        5 |      |      |      |      |        |      |      |          |                |
                          | 31 |                      TABLE ACCESS STORAGE FULL    | SYS_TEMP_0FD9D86E6_A113BAA7 |  40352 |    3 |        1 |    +2 |    8 |        5 |      |      |      |      |        |      |      |          |                |
                          | 32 |                    PARTITION RANGE AND            |                            |      1 | 27160 |      638 |    +2 |    1 |      10M |      |      |      |      |        |      |      |          |                |
                          | 33 |                    TABLE ACCESS STORAGE FULL    | T_EXECS_PART                |      1 | 27160 |      640 |    +0 |    4 |      10M |  655 | 527MB |      |      |        |      |      |          |                |
                          | 34 |                FILTER                            |                            |        |      |        14 |    +87 |    8 |    2622 |      |      |      |      |        |      |      |          |                |
                          | 35 |                  HASH JOIN                        |                            |  40352 | 19936 |      555 |    +87 |    8 |    2622 |      |      |      |      |        |    9M |      |          |                |
                          | 36 |                  JOIN FILTER CREATE              | :BF0004                    |  40352 |    3 |      553 |    +87 |    8 |      40 |      |      |      |      |        |      |      |          |                |
                          | 37 |                    PART JOIN FILTER CREATE        | :BF0003                    |  40352 |    3 |      553 |    +87 |    8 |      40 |      |      |      |      |        |      |      |          |                |
                          | 38 |                    PX RECEIVE                    |                            |  40352 |    3 |      553 |    +87 |    8 |      40 |      |      |      |      |        |      |      |          |                |
                          | 39 |                      PX SEND BROADCAST            | :TQ20001                    |  40352 |    3 |      555 |    +85 |    8 |      40 |      |      |      |      |        |      |      |          |                |
                          | 40 |                      VIEW                        |                            |  40352 |    3 |      555 |    +85 |    8 |        5 |      |      |      |      |        |      |      |          |                |
                          | 41 |                        PX BLOCK ITERATOR          |                            |  40352 |    3 |      555 |    +85 |    8 |        5 |      |      |      |      |        |      |      |          |                |
                          | 42 |                        TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D86E6_A113BAA7 |  40352 |    3 |      555 |    +85 |    99 |        5 |      |      |      |      |        |      |      |          |                |
                          | 43 |                  JOIN FILTER USE                | :BF0004                    |      6M | 19929 |      555 |    +87 |    8 |    483M |      |      |      |      |        |      |      |          |                |
                          | 44 |                    PX PARTITION RANGE AND        |                            |      6M | 19929 |      555 |    +87 |    8 |    483M |      |      |      |      |        |      |      |          |                |
                          | 45 |                    TABLE ACCESS STORAGE FULL    | T_MARKET_MESSAGES_PART      |      6M | 19929 |      557 |    +85 |    7 |    483M | 45716 |  21GB |      |      |  -7.53% |  13M |      |          |                |
                          | 46 |                FILTER                            |                            |        |      |        7 |  +641 |    8 |    2868 |      |      |      |      |        |      |      |          |                |
                          | 47 |                  HASH JOIN                        |                            |  40352 | 22060 |        60 |  +641 |    8 |    2868 |      |      |      |      |        |    9M |      |          |                |
                          | 48 |                  JOIN FILTER CREATE              | :BF0006                    |  40352 |    3 |        60 |  +641 |    8 |      40 |      |      |      |      |        |      |      |          |                |
                          | 49 |                    PART JOIN FILTER CREATE        | :BF0005                    |  40352 |    3 |        60 |  +641 |    8 |      40 |      |      |      |      |        |      |      |          |                |
                          | 50 |                    PX RECEIVE                    |                            |  40352 |    3 |        60 |  +641 |    8 |      40 |      |      |      |      |        |      |      |          |                |
                          | 51 |                      PX SEND BROADCAST            | :TQ20002                    |  40352 |    3 |        62 |  +639 |    8 |      40 |      |      |      |      |        |      |      |          |                |
                          | 52 |                      VIEW                        |                            |  40352 |    3 |        62 |  +639 |    8 |        5 |      |      |      |      |        |      |      |          |                |
                          | 53 |                        PX BLOCK ITERATOR          |                            |  40352 |    3 |        62 |  +639 |    8 |        5 |      |      |      |      |        |      |      |          |                |
                          | 54 |                        TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D86E6_A113BAA7 |  40352 |    3 |        62 |  +639 |    99 |        5 |    5 |  1MB |      |      |        |      |      |          |                |
                          | 55 |                  JOIN FILTER USE                | :BF0006                    |      5M | 22054 |        50 |  +641 |    8 |    2871 |      |      |      |      |        |      |      |          |                |
                          | 56 |                    PX PARTITION RANGE AND        |                            |      5M | 22054 |        50 |  +641 |    8 |    2871 |      |      |      |      |        |      |      |          |                |
                          | 57 |                    TABLE ACCESS STORAGE FULL    | T_ORDER_DETAILS_PART        |      5M | 22054 |        62 |  +639 |    7 |    2871 | 20903 |  20GB |      |      |  99.34% |  13M |      |          |                |
                          | 58 |                FILTER                            |                            |        |      |        3 |  +702 |    8 |    2644 |      |      |      |      |        |      |      |          |                |
                          | 59 |                  HASH JOIN                        |                            |  40352 | 18810 |      110 |  +702 |    8 |    2644 |      |      |      |      |        |    9M |      |          |                |
                          | 60 |                  JOIN FILTER CREATE              | :BF0008                    |  40352 |    3 |      110 |  +702 |    8 |      40 |      |      |      |      |        |      |      |          |                |
                          | 61 |                    PART JOIN FILTER CREATE        | :BF0007                    |  40352 |    3 |      110 |  +702 |    8 |      40 |      |      |      |      |        |      |      |          |                |
                          | 62 |                    PX RECEIVE                    |                            |  40352 |    3 |      110 |  +702 |    8 |      40 |      |      |      |      |        |      |      |          |                |
                          | 63 |                      PX SEND BROADCAST            | :TQ20003                    |  40352 |    3 |      112 |  +700 |    8 |      40 |      |      |      |      |        |      |      |          |                |
                          | 64 |                      VIEW                        |                            |  40352 |    3 |      112 |  +700 |    8 |        5 |      |      |      |      |        |      |      |          |                |
                          | 65 |                        PX BLOCK ITERATOR          |                            |  40352 |    3 |      112 |  +700 |    8 |        5 |      |      |      |      |        |      |      |          |                |
                          | 66 |                        TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D86E6_A113BAA7 |  40352 |    3 |      112 |  +700 |    99 |        5 |  102 |  39MB |      |      |        |      |      |          |                |
                          | 67 |                  JOIN FILTER USE                | :BF0008                    |      5M | 18804 |        52 |  +702 |    8 |    2646 |      |      |      |      |        |      |      |          |                |
                          | 68 |                    PX PARTITION RANGE AND        |                            |      5M | 18804 |        52 |  +702 |    8 |    2646 |      |      |      |      |        |      |      |          |                |
                          | 69 |                    TABLE ACCESS STORAGE FULL    | T_SEND_MESSAGES_PART        |      5M | 18804 |      111 |  +701 |    7 |    2646 | 27142 |  26GB |      |      |  98.96% |  13M |      |          |                |
                          | 70 |          JOIN FILTER USE                          | :BF0001                    |  14614 |    2 |        2 |  +811 |    8 |        5 |      |      |      |      |        |      |      |          |                |
                          | 71 |          PX PARTITION RANGE AND                  |                            |  14614 |    2 |        2 |  +811 |    8 |        5 |      |      |      |      |        |      |      |          |                |
                          | 72 |            TABLE ACCESS STORAGE FULL              | T_INSTANCES                |  14614 |    2 |        2 |  +811 |    7 |        5 |    10 |  6MB |      |      |        |      |      |          |                |
                          =========================================================================================================================================================================================================================================

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

                            2 - filter(TO_DATE('03-SEP-20','DD-MON-YY')>=TO_DATE('28-AUG-20','DD-MON-YY'))
                            5 - filter(TO_DATE('03-SEP-20','DD-MON-YY')>=TO_DATE('28-AUG-20','DD-MON-YY'))
                            7 - storage((UPPER("P"."ISIN") LIKE '%CNE100000XX9%' AND "P"."T_DT"<=TO_DATE('03-SEP-20','DD-MON-YY') AND "P"."T_DT">=TO_DATE('28-AUG-20','DD-MON-YY')))
                                filter((UPPER("P"."ISIN") LIKE '%CNE100000XX9%' AND "P"."T_DT"<=TO_DATE('03-SEP-20','DD-MON-YY') AND "P"."T_DT">=TO_DATE('28-AUG-20','DD-MON-YY')))
                            8 - filter(ROWNUM<=30001)
                            9 - filter(TO_DATE('03-SEP-20','DD-MON-YY')>=TO_DATE('28-AUG-20','DD-MON-YY'))
                            11 - filter(ROWNUM<=30001)
                            12 - filter(TO_DATE('03-SEP-20','DD-MON-YY')>=TO_DATE('28-AUG-20','DD-MON-YY'))
                            13 - access("I"."T_DT"="P"."T_DT" AND "I"."I_ID"="P"."I_ID")
                            18 - access("P"."T_DT"="T"."T_DT" AND "P"."I_ID"="T"."I_ID" AND "P"."PRODUCTID"="T"."PRODUCTID")
                            21 - filter((UPPER("P"."ISIN") LIKE '%CNE100000XX9%' AND "P"."T_DT">=TO_DATE('28-AUG-20','DD-MON-YY') AND "P"."T_DT"<=TO_DATE('03-SEP-20','DD-MON-YY')))
                            23 - storage(:Z>=:Z AND :Z<=:Z)
                            27 - filter(TO_DATE('03-SEP-20','DD-MON-YY')>=TO_DATE('28-AUG-20','DD-MON-YY'))
                            28 - access("P"."T_DT"="T1"."T_DT" AND "P"."I_ID"="T1"."I_ID" AND "P"."PRODUCTID"="T1"."PRODUCTID")
                            30 - filter(("P"."T_DT">=TO_DATE('28-AUG-20','DD-MON-YY') AND "P"."T_DT"<=TO_DATE('03-SEP-20','DD-MON-YY')))
                            33 - storage(("T1"."T_DT">=TO_DATE('28-AUG-20','DD-MON-YY') AND TO_CHAR(INTERNAL_FUNCTION("T1"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                        TO_CHAR(INTERNAL_FUNCTION("T1"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND NVL(TO_CHAR(INTERNAL_FUNCTION("T1"."EXECUTION_TIME"),'HH24:MI:SS'),'%') LIKE '%' AND
                                        "T1"."T_DT"<=TO_DATE('03-SEP-20','DD-MON-YY')))
                                filter(("T1"."T_DT">=TO_DATE('28-AUG-20','DD-MON-YY') AND TO_CHAR(INTERNAL_FUNCTION("T1"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                        TO_CHAR(INTERNAL_FUNCTION("T1"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND NVL(TO_CHAR(INTERNAL_FUNCTION("T1"."EXECUTION_TIME"),'HH24:MI:SS'),'%') LIKE '%' AND
                                        "T1"."T_DT"<=TO_DATE('03-SEP-20','DD-MON-YY')))
                            34 - filter(TO_DATE('03-SEP-20','DD-MON-YY')>=TO_DATE('28-AUG-20','DD-MON-YY'))
                            35 - access("P"."T_DT"="T2"."T_DT" AND "P"."I_ID"="T2"."I_ID" AND "P"."PRODUCTID"="T2"."PRODUCTID")
                            40 - filter(("P"."T_DT">=TO_DATE('28-AUG-20','DD-MON-YY') AND "P"."T_DT"<=TO_DATE('03-SEP-20','DD-MON-YY')))
                            42 - storage(:Z>=:Z AND :Z<=:Z)
                            45 - storage((TO_CHAR(INTERNAL_FUNCTION("T2"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                        TO_CHAR(INTERNAL_FUNCTION("T2"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T2"."T_DT"<=TO_DATE('03-SEP-20','DD-MON-YY') AND
                                        "T2"."T_DT">=TO_DATE('28-AUG-20','DD-MON-YY') AND SYS_OP_BLOOM_FILTER(:BF0000,"T2"."T_DT","T2"."I_ID","T2"."PRODUCTID")))
                                filter((TO_CHAR(INTERNAL_FUNCTION("T2"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                        TO_CHAR(INTERNAL_FUNCTION("T2"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T2"."T_DT"<=TO_DATE('03-SEP-20','DD-MON-YY') AND
                                        "T2"."T_DT">=TO_DATE('28-AUG-20','DD-MON-YY') AND SYS_OP_BLOOM_FILTER(:BF0000,"T2"."T_DT","T2"."I_ID","T2"."PRODUCTID")))
                            46 - filter(TO_DATE('03-SEP-20','DD-MON-YY')>=TO_DATE('28-AUG-20','DD-MON-YY'))
                            47 - access("P"."T_DT"="T3"."T_DT" AND "P"."I_ID"="T3"."I_ID" AND "P"."PRODUCTID"="T3"."PRODUCTID")
                            52 - filter(("P"."T_DT">=TO_DATE('28-AUG-20','DD-MON-YY') AND "P"."T_DT"<=TO_DATE('03-SEP-20','DD-MON-YY')))
                            54 - storage(:Z>=:Z AND :Z<=:Z)
                            57 - storage((TO_CHAR(INTERNAL_FUNCTION("T3"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                        TO_CHAR(INTERNAL_FUNCTION("T3"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T3"."T_DT"<=TO_DATE('03-SEP-20','DD-MON-YY') AND
                                        "T3"."T_DT">=TO_DATE('28-AUG-20','DD-MON-YY') AND SYS_OP_BLOOM_FILTER(:BF0000,"T3"."T_DT","T3"."I_ID","T3"."PRODUCTID")))
                                filter((TO_CHAR(INTERNAL_FUNCTION("T3"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                        TO_CHAR(INTERNAL_FUNCTION("T3"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T3"."T_DT"<=TO_DATE('03-SEP-20','DD-MON-YY') AND
                                        "T3"."T_DT">=TO_DATE('28-AUG-20','DD-MON-YY') AND SYS_OP_BLOOM_FILTER(:BF0000,"T3"."T_DT","T3"."I_ID","T3"."PRODUCTID")))
                            58 - filter(TO_DATE('03-SEP-20','DD-MON-YY')>=TO_DATE('28-AUG-20','DD-MON-YY'))
                            59 - access("P"."T_DT"="T4"."T_DT" AND "P"."I_ID"="T4"."I_ID" AND "P"."PRODUCTID"="T4"."PRODUCTID")
                            64 - filter(("P"."T_DT">=TO_DATE('28-AUG-20','DD-MON-YY') AND "P"."T_DT"<=TO_DATE('03-SEP-20','DD-MON-YY')))
                            66 - storage(:Z>=:Z AND :Z<=:Z)
                            69 - storage((TO_CHAR(INTERNAL_FUNCTION("T4"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                        TO_CHAR(INTERNAL_FUNCTION("T4"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T4"."T_DT"<=TO_DATE('03-SEP-20','DD-MON-YY') AND
                                        "T4"."T_DT">=TO_DATE('28-AUG-20','DD-MON-YY') AND SYS_OP_BLOOM_FILTER(:BF0000,"T4"."T_DT","T4"."I_ID","T4"."PRODUCTID")))
                                filter((TO_CHAR(INTERNAL_FUNCTION("T4"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                        TO_CHAR(INTERNAL_FUNCTION("T4"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T4"."T_DT"<=TO_DATE('03-SEP-20','DD-MON-YY') AND
                                        "T4"."T_DT">=TO_DATE('28-AUG-20','DD-MON-YY') AND SYS_OP_BLOOM_FILTER(:BF0000,"T4"."T_DT","T4"."I_ID","T4"."PRODUCTID")))
                            72 - storage(("I"."T_DT"<=TO_DATE('03-SEP-20','DD-MON-YY') AND "I"."T_DT">=TO_DATE('28-AUG-20','DD-MON-YY') AND
                                        SYS_OP_BLOOM_FILTER(:BF0000,"I"."T_DT","I"."I_ID")))
                                filter(("I"."T_DT"<=TO_DATE('03-SEP-20','DD-MON-YY') AND "I"."T_DT">=TO_DATE('28-AUG-20','DD-MON-YY') AND
                                        SYS_OP_BLOOM_FILTER(:BF0000,"I"."T_DT","I"."I_ID")))

                          Note
                          -----
                            - Degree of Parallelism is 8 because of hint

                           

                          Depsite the range of dates I have only 5 productid in my materialized CTE and 2 distinct productid:

                          SQL> select productid from force.t_products_part p
                            2  where p.T_DT between to_date('28-AUG-20', 'DD-MON-YY') and to_date('03-SEP-20', 'DD-MON-YY')
                            3  and upper(p.isin) like  '%CNE100000XX9%';

                          PRODUCTID
                          ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                          601933
                          601933.SH
                          601933
                          601933
                          601933

                           

                          If the filters on product id is applied I would retrieve only a few thousand of rows from the tables of my view:

                          select /*+ parallel(8) */ count(*)  from force.T_EXECS_PART where T_DT between to_date('28-AUG-20', 'DD-MON-YY') and to_date('03-SEP-20', 'DD-MON-YY') and productid in ('601933','601933.SH');

                            COUNT(*)
                          ----------
                                5841
                           
                          select /*+ parallel(8) */ count(*)  from force.T_MARKET_MESSAGES_PART where T_DT between to_date('28-AUG-20', 'DD-MON-YY') and to_date('03-SEP-20', 'DD-MON-YY') and productid in ('601933','601933.SH');
                           
                            COUNT(*)
                          ----------
                                2622

                          select /*+ parallel(8) */ count(*)  from force.T_ORDER_DETAILS_PART where T_DT between to_date('28-AUG-20', 'DD-MON-YY') and to_date('03-SEP-20', 'DD-MON-YY') and productid in ('601933','601933.SH');

                            COUNT(*)
                          ----------
                                2868

                          select /*+ parallel(8) */ count(*)  from force.T_SEND_MESSAGES_PART where T_DT between to_date('28-AUG-20', 'DD-MON-YY') and to_date('03-SEP-20', 'DD-MON-YY') and productid in ('601933','601933.SH');

                            COUNT(*)
                          ----------
                                2644

                           

                           

                          But in the plan I can see that 483M rows are retrived from T_MARKET_MESSAGES_PART instead of 2622 and 10M from T_EXECS_PART instead of 5841, and all this while in the plan we can see that bloom filters are used and offloadables:

                            33 - storage(("T1"."T_DT">=TO_DATE('28-AUG-20','DD-MON-YY') AND TO_CHAR(INTERNAL_FUNCTION("T1"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND

                                        TO_CHAR(INTERNAL_FUNCTION("T1"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND NVL(TO_CHAR(INTERNAL_FUNCTION("T1"."EXECUTION_TIME"),'HH24:MI:SS'),'%') LIKE '%' AND

                                        "T1"."T_DT"<=TO_DATE('03-SEP-20','DD-MON-YY')))

                           

                          45 - storage((TO_CHAR(INTERNAL_FUNCTION("T2"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND

                                        TO_CHAR(INTERNAL_FUNCTION("T2"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T2"."T_DT"<=TO_DATE('03-SEP-20','DD-MON-YY') AND

                                        "T2"."T_DT">=TO_DATE('28-AUG-20','DD-MON-YY') AND SYS_OP_BLOOM_FILTER(:BF0000,"T2"."T_DT","T2"."I_ID","T2"."PRODUCTID")))

                           

                          Because of that, the query is very slow.What do you think it means?

                          Are bloom filters some times not applied by the engine during the execution?

                           

                          PS: I have noticed a small downgrade of the DOP (4%).

                          • 10. Re: Bloom filters and view using UNION ALL
                            Ahmed AANGOUR

                            I made a mistake : BFs are not used for joinng T_EXECS_PART so it's normal to get 10M rows but they are applied for joining T_MARKET_MESSAGES_PART so my question is I think still relevant

                            • 11. Re: Bloom filters and view using UNION ALL
                              Jonathan Lewis

                              Before getting stuck into the detail of the plan the first thing to do is repeat the exercise but use 4-digit years in the predicates. This may have a significant impact on the cardinality estimates which may then affect the shape of the execution plan.

                               

                              Regards

                              Jonathan Lewis

                              • 12. Re: Bloom filters and view using UNION ALL
                                Ahmed AANGOUR

                                Indeed, using the 4-digit years makes the T_INSTANCES table to be joined first, which is much better. The execution time is faster but I still have questions about the number of rows returned by the table.

                                Several million of rows are retrieved from T_MARKET_MESSAGES_PART, T_ORDER_DETAILS_PART and T_SEND_MESSAGES_PART despite the use of BFs.

                                Note also that I had to use the following hint because the CBO decided to use T_EXECS_PART as the hash table for joining with the SYS_TEMP table: LEADING(@"SEL$83632299" "P"@"SEL$4" "T1"@"SEL$4" )

                                That's because the CBO reckoned that T_EXECS_PART would return only a single row.

                                 

                                Global Information
                                ------------------------------
                                Status              :  DONE (ALL ROWS)
                                Instance ID        :  1
                                Session            :  XXX (1887:34541)
                                SQL ID              :  43vd2bxzfkmg2
                                SQL Execution ID    :  16777216
                                Execution Started  :  09/09/2020 14:36:00
                                First Refresh Time  :  09/09/2020 14:36:00
                                Last Refresh Time  :  09/09/2020 14:39:01
                                Duration            :  181s
                                Module/Action      :  SQL*Plus/-
                                Service            :  xxxx
                                Program            :  sqlplus.exe
                                Fetch Calls        :  141

                                Global Stats
                                ============================================================================================================================================
                                | Elapsed |  Cpu  |    IO    | Application | Concurrency | Cluster  |  Other  | Fetch | Buffer | Read | Read  | Write | Write |  Cell  |
                                | Time(s) | Time(s) | Waits(s) |  Waits(s)  |  Waits(s)  | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes | Reqs  | Bytes | Offload |
                                ============================================================================================================================================
                                |    1542 |    598 |      943 |        0.01 |        0.00 |    0.13 |    0.07 |  141 |    3M | 111K |  96GB |    45 |  40MB |  89.06% |
                                ============================================================================================================================================

                                Parallel Execution Details (DOP=8 , Servers Allocated=24)
                                ==================================================================================================================================================================================================================
                                |      Name      | Type  | Group# | Server# | Elapsed |  Cpu  |    IO    | Application | Concurrency | Cluster  |  Other  | Buffer | Read  | Read  | Write | Write |  Cell  |          Wait Events          |
                                |                |      |        |        | Time(s) | Time(s) | Waits(s) |  Waits(s)  |  Waits(s)  | Waits(s) | Waits(s) |  Gets  | Reqs  | Bytes | Reqs  | Bytes | Offload  |          (sample #)          |
                                ==================================================================================================================================================================================================================
                                | PX Coordinator | QC    |        |        |    0.29 |    0.21 |    0.00 |        0.01 |            |    0.00 |    0.07 |    871 |    2 | 65536 |      |    . |    NaN% |                              |
                                | p000          | Set 1 |      1 |      1 |    0.22 |    0.20 |    0.01 |            |            |    0.02 |          |  2439 |    11 | 352KB |      |    . |    NaN% | cell smart table scan (232)  |
                                | p001          | Set 1 |      1 |      2 |    0.21 |    0.19 |    0.01 |            |            |    0.01 |          |  2618 |    11 | 416KB |    9 |  8MB |  -96.08% | cell smart table scan (210)  |
                                | p002          | Set 1 |      1 |      3 |    0.20 |    0.18 |    0.01 |            |            |    0.01 |          |  2372 |    18 | 608KB |      |    . |    NaN% | cell smart table scan (218)  |
                                | p003          | Set 1 |      1 |      4 |    0.20 |    0.18 |    0.01 |            |            |    0.02 |          |  2523 |    10 | 320KB |    9 |  8MB |  -96.08% | cell smart table scan (262)  |
                                | p004          | Set 1 |      1 |      5 |    0.20 |    0.19 |    0.00 |            |            |    0.01 |          |  2265 |    2 | 65536 |      |    . |    NaN% | cell smart table scan (198)  |
                                | p005          | Set 1 |      1 |      6 |    0.21 |    0.19 |    0.01 |            |            |    0.01 |          |  2725 |    9 | 288KB |    9 |  8MB |  -96.08% | gc cr multi block request (2) |
                                |                |      |        |        |        |        |          |            |            |          |          |        |      |      |      |      |          | cell smart table scan (212)  |
                                | p006          | Set 1 |      1 |      7 |    0.22 |    0.20 |    0.01 |            |            |    0.01 |          |  2827 |    10 | 352KB |    9 |  8MB |  -96.08% | cell smart table scan (256)  |
                                | p007          | Set 1 |      1 |      8 |    0.21 |    0.19 |    0.01 |            |            |    0.01 |          |  2795 |    11 | 352KB |    9 |  8MB |  -96.08% | cell smart table scan (204)  |
                                | p000          | Set 1 |      2 |      1 |    163 |      48 |      116 |            |            |          |          |  428K | 14124 |  13GB |      |    . |  94.61% | cell smart table scan (232)  |
                                | p001          | Set 1 |      2 |      2 |    164 |      58 |      106 |            |            |          |          |  358K | 12212 |  11GB |      |    . |  91.15% | cell smart table scan (210)  |
                                | p002          | Set 1 |      2 |      3 |    165 |      52 |      113 |            |            |          |          |  393K | 13122 |  12GB |      |    . |  93.39% | cell smart table scan (218)  |
                                | p003          | Set 1 |      2 |      4 |    163 |      34 |      129 |            |            |          |          |  432K | 13862 |  13GB |      |    . |  97.44% | cell smart table scan (262)  |
                                | p004          | Set 1 |      2 |      5 |    159 |      60 |      99 |            |            |          |          |  345K | 12098 |  10GB |      |    . |  88.02% | cell smart table scan (198)  |
                                | p005          | Set 1 |      2 |      6 |    163 |      58 |      105 |            |        0.00 |          |          |  355K | 12152 |  11GB |      |    . |  90.71% | gc cr multi block request (2) |
                                |                |      |        |        |        |        |          |            |            |          |          |        |      |      |      |      |          | cell smart table scan (212)  |
                                | p006          | Set 1 |      2 |      7 |    161 |      34 |      127 |            |            |          |          |  467K | 15084 |  14GB |      |    . |  96.71% | cell smart table scan (256)  |
                                | p007          | Set 1 |      2 |      8 |    161 |      59 |      102 |            |            |          |          |  346K | 12073 |  10GB |      |    . |  88.58% | cell smart table scan (204)  |
                                | p008          | Set 2 |      2 |      1 |      30 |      24 |    6.21 |            |        0.00 |    0.01 |          |  6762 |  724 | 169MB |      |    . | -177.78% | cell smart table scan (2)    |
                                | p009          | Set 2 |      2 |      2 |      30 |      25 |    5.22 |            |        0.00 |    0.00 |          |  7693 |  759 | 180MB |      |    . | -177.78% | cell smart table scan (5)    |
                                | p00a          | Set 2 |      2 |      3 |      30 |      24 |    5.44 |            |        0.00 |    0.00 |          |  6573 |  688 | 160MB |      |    . | -185.71% | cell smart table scan (2)    |
                                | p00b          | Set 2 |      2 |      4 |      30 |      24 |    5.84 |            |        0.00 |    0.00 |          |  7218 |  720 | 171MB |      |    . | -177.78% | cell smart table scan (7)    |
                                | p00c          | Set 2 |      2 |      5 |      30 |      24 |    5.77 |            |        0.00 |    0.00 |          |  7420 |  740 | 176MB |      |    . | -177.78% |                              |
                                | p00d          | Set 2 |      2 |      6 |      30 |      24 |    5.47 |            |        0.00 |    0.00 |          |  7006 |  723 | 170MB |      |    . | -177.78% | cell smart table scan (6)    |
                                | p00e          | Set 2 |      2 |      7 |      30 |      24 |    6.31 |            |        0.00 |    0.00 |          |  7559 |  736 | 178MB |      |    . | -177.78% | cell smart table scan (8)    |
                                | p00f          | Set 2 |      2 |      8 |      30 |      23 |    6.62 |            |            |    0.00 |          |  6847 |  678 | 160MB |      |    . | -185.71% | cell smart table scan (4)    |
                                ==================================================================================================================================================================================================================

                                SQL Plan Monitoring Details (Plan Hash Value=905442776)
                                ==========================================================================================================================================================================================================================================
                                | Id |                  Operation                  |            Name            |  Rows  | Cost  |  Time    | Start  | Execs |  Rows  | Read  | Read  | Write | Write |  Cell  |  Mem  | Activity |        Activity Detail        |
                                |    |                                            |                            | (Estim) |      | Active(s) | Active |      | (Actual) | Reqs  | Bytes | Reqs  | Bytes | Offload  | (Max) |  (%)    |          (# samples)          |
                                ==========================================================================================================================================================================================================================================
                                |  0 | SELECT STATEMENT                            |                            |        |      |        1 |  +181 |    1 |    13984 |      |      |      |      |          |      |          |                              |
                                |  1 |  TEMP TABLE TRANSFORMATION                |                            |        |      |        1 |  +181 |    1 |    13984 |      |      |      |      |          |      |          |                              |
                                |  2 |    PX COORDINATOR                          |                            |        |      |        1 |    +1 |    9 |      10 |    1 | 32768 |      |      |          |      |          |                              |
                                |  3 |    PX SEND QC (RANDOM)                    | :TQ10000                    |  86680 |  119 |        1 |    +1 |    8 |      10 |      |      |      |      |          |      |          |                              |
                                |  4 |      LOAD AS SELECT (TEMP SEGMENT MERGE)    |                            |        |      |        1 |    +1 |    8 |      10 |      |      |    5 | 160KB |          |      |          |                              |
                                |  5 |      PX BLOCK ITERATOR                    |                            |  86680 |  119 |        1 |    +1 |    8 |        8 |      |      |      |      |          |      |          |                              |
                                |  6 |        TABLE ACCESS STORAGE FULL            | T_PRODUCTS_PART            |  86680 |  119 |        1 |    +1 |  358 |        8 |    77 |  3MB |      |      |          |      |    0.57 | gc cr multi block request (2) |
                                |    |                                            |                            |        |      |          |        |      |          |      |      |      |      |          |      |          | Cpu (14)                      |
                                |  7 |    COUNT STOPKEY                            |                            |        |      |        1 |  +181 |    1 |    13984 |      |      |      |      |          |      |          |                              |
                                |  8 |    PX COORDINATOR                          |                            |        |      |        1 |  +181 |    17 |    13984 |    1 | 32768 |      |      |          |      |          |                              |
                                |  9 |      PX SEND QC (RANDOM)                    | :TQ20005                    |  32066 |  120K |        1 |  +181 |    8 |    13984 |      |      |      |      |          |      |          |                              |
                                | 10 |      BUFFER SORT                          |                            |  30001 |      |      179 |    +3 |    8 |    13984 |      |      |      |      |          |  17M |          |                              |
                                | 11 |        COUNT STOPKEY                        |                            |        |      |      176 |    +3 |    8 |    13984 |      |      |      |      |          |      |          |                              |
                                | 12 |        HASH JOIN                          |                            |  32066 |  120K |      176 |    +3 |    8 |    13984 |      |      |      |      |          |  39M |          |                              |
                                | 13 |          PX RECEIVE                        |                            |  32066 |    12 |        1 |    +3 |    8 |      64 |      |      |      |      |          |      |          |                              |
                                | 14 |          PX SEND BROADCAST                | :TQ20000                    |  32066 |    12 |        1 |    +3 |    8 |      64 |      |      |      |      |          |      |          |                              |
                                | 15 |            HASH JOIN                        |                            |  32066 |    12 |        1 |    +3 |    8 |        8 |      |      |      |      |          |  45M |          |                              |
                                | 16 |            PARTITION RANGE ITERATOR        |                            |  32066 |    6 |        1 |    +3 |    8 |    257K |      |      |      |      |          |      |          |                              |
                                | 17 |              TABLE ACCESS STORAGE FULL      | T_INSTANCES                |  32066 |    6 |        1 |    +3 |  120 |    257K |      |      |      |      |          |      |          |                              |
                                | 18 |            VIEW                            |                            |  86680 |    6 |        1 |    +3 |    8 |        8 |      |      |      |      |          |      |          |                              |
                                | 19 |              PX BLOCK ITERATOR              |                            |  86680 |    6 |        1 |    +3 |    8 |        8 |      |      |      |      |          |      |          |                              |
                                | 20 |              TABLE ACCESS STORAGE FULL    | SYS_TEMP_0FD9D8788_A113BAA7 |  86680 |    6 |        1 |    +3 |  103 |        8 |  109 |  48MB |      |      |          |      |          |                              |
                                | 21 |          VIEW                              |                            |    260K |  120K |      176 |    +3 |    8 |    13984 |      |      |      |      |          |      |          |                              |
                                | 22 |          UNION-ALL                        |                            |        |      |      176 |    +3 |    8 |    13984 |      |      |      |      |          |      |          |                              |
                                | 23 |            HASH JOIN                        |                            |      1 |  433 |        32 |    +3 |    8 |    5847 |      |      |      |      |          |    9M |    1.44 | Cpu (40)                      |
                                | 24 |            VIEW                            |                            |  86680 |    6 |        1 |    +3 |    8 |        8 |      |      |      |      |          |      |          |                              |
                                | 25 |              PX BLOCK ITERATOR              |                            |  86680 |    6 |        1 |    +3 |    8 |        8 |      |      |      |      |          |      |          |                              |
                                | 26 |              TABLE ACCESS STORAGE FULL    | SYS_TEMP_0FD9D8788_A113BAA7 |  86680 |    6 |        1 |    +3 |  103 |        8 |      |      |      |      |          |      |          |                              |
                                | 27 |            PX RECEIVE                      |                            |      1 |  427 |        33 |    +2 |    8 |    124M |      |      |      |      |          |      |    6.60 | Cpu (184)                    |
                                | 28 |              PX SEND BROADCAST              | :TQ20001                    |      1 |  427 |        80 |    +2 |    8 |    198M |      |      |      |      |          |      |    4.09 | Cpu (114)                    |
                                | 29 |              PX BLOCK ITERATOR            |                            |      1 |  427 |        79 |    +3 |    8 |      25M |      |      |      |      |          |      |          |                              |
                                | 30 |                TABLE ACCESS STORAGE FULL    | T_EXECS_PART                |      1 |  427 |        80 |    +2 |  438 |      25M |  5659 |  1GB |      |      | -185.71% |  25M |    4.34 | Cpu (87)                      |
                                |    |                                            |                            |        |      |          |        |      |          |      |      |      |      |          |      |          | cell smart table scan (34)    |
                                | 31 |            HASH JOIN                        |                            |  86680 | 39094 |        49 |    +33 |    8 |    2623 |      |      |      |      |          |  17M |    0.43 | Cpu (12)                      |
                                | 32 |            JOIN FILTER CREATE              | :BF0001                    |  86680 |    6 |        2 |    +33 |    8 |      64 |      |      |      |      |          |      |          |                              |
                                | 33 |              PART JOIN FILTER CREATE        | :BF0000                    |  86680 |    6 |        2 |    +33 |    8 |      64 |      |      |      |      |          |      |          |                              |
                                | 34 |              PX RECEIVE                    |                            |  86680 |    6 |        2 |    +33 |    8 |      64 |      |      |      |      |          |      |          |                              |
                                | 35 |                PX SEND BROADCAST            | :TQ20002                    |  86680 |    6 |        1 |    +81 |    8 |      64 |      |      |      |      |          |      |          |                              |
                                | 36 |                VIEW                        |                            |  86680 |    6 |        1 |    +81 |    8 |        8 |      |      |      |      |          |      |          |                              |
                                | 37 |                  PX BLOCK ITERATOR          |                            |  86680 |    6 |        1 |    +81 |    8 |        8 |      |      |      |      |          |      |          |                              |
                                | 38 |                  TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D8788_A113BAA7 |  86680 |    6 |        1 |    +81 |  103 |        8 |      |      |      |      |          |      |          |                              |
                                | 39 |            JOIN FILTER USE                | :BF0001                    |    11M | 39081 |        49 |    +33 |    8 |      37M |      |      |      |      |          |      |          |                              |
                                | 40 |              PX BLOCK ITERATOR              |                            |    11M | 39081 |        49 |    +33 |    8 |      37M |      |      |      |      |          |      |          |                              |
                                | 41 |              TABLE ACCESS STORAGE FULL    | T_MARKET_MESSAGES_PART      |    11M | 39081 |        50 |    +33 |  446 |      37M | 32469 |  29GB |      |      |  93.67% |  25M |    27.05 | Cpu (164)                    |
                                |    |                                            |                            |        |      |          |        |      |          |      |      |      |      |          |      |          | cell smart table scan (590)  |
                                | 42 |            HASH JOIN                        |                            |  86680 | 43342 |        50 |    +82 |    8 |    2869 |      |      |      |      |          |  17M |    0.07 | Cpu (2)                      |
                                | 43 |            JOIN FILTER CREATE              | :BF0003                    |  86680 |    6 |        3 |    +82 |    8 |      64 |      |      |      |      |          |      |          |                              |
                                | 44 |              PART JOIN FILTER CREATE        | :BF0002                    |  86680 |    6 |        3 |    +82 |    8 |      64 |      |      |      |      |          |      |          |                              |
                                | 45 |              PX RECEIVE                    |                            |  86680 |    6 |        3 |    +82 |    8 |      64 |      |      |      |      |          |      |          |                              |
                                | 46 |                PX SEND BROADCAST            | :TQ20003                    |  86680 |    6 |        55 |    +81 |    8 |      64 |      |      |      |      |          |      |          |                              |
                                | 47 |                VIEW                        |                            |  86680 |    6 |        55 |    +81 |    8 |        8 |      |      |      |      |          |      |          |                              |
                                | 48 |                  PX BLOCK ITERATOR          |                            |  86680 |    6 |        55 |    +81 |    8 |        8 |      |      |      |      |          |      |          |                              |
                                | 49 |                  TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D8788_A113BAA7 |  86680 |    6 |        55 |    +81 |  103 |        8 |      |      |      |      |          |      |          |                              |
                                | 50 |            JOIN FILTER USE                | :BF0003                    |    10M | 43330 |        50 |    +82 |    8 |      44M |      |      |      |      |          |      |          |                              |
                                | 51 |              PX BLOCK ITERATOR              |                            |    10M | 43330 |        50 |    +82 |    8 |      44M |      |      |      |      |          |      |          |                              |
                                | 52 |              TABLE ACCESS STORAGE FULL    | T_ORDER_DETAILS_PART        |    10M | 43330 |        55 |    +82 |  446 |      44M | 32102 |  28GB |      |      |  90.43% |  25M |    29.99 | Cpu (190)                    |
                                |    |                                            |                            |        |      |          |        |      |          |      |      |      |      |          |      |          | cell smart table scan (646)  |
                                | 53 |            HASH JOIN                        |                            |  86680 | 36695 |        44 |  +136 |    8 |    2645 |      |      |      |      |          |  17M |    0.07 | Cpu (2)                      |
                                | 54 |            JOIN FILTER CREATE              | :BF0005                    |  86680 |    6 |        2 |  +136 |    8 |      64 |      |      |      |      |          |      |          |                              |
                                | 55 |              PART JOIN FILTER CREATE        | :BF0004                    |  86680 |    6 |        2 |  +136 |    8 |      64 |      |      |      |      |          |      |          |                              |
                                | 56 |              PX RECEIVE                    |                            |  86680 |    6 |        2 |  +136 |    8 |      64 |      |      |      |      |          |      |          |                              |
                                | 57 |                PX SEND BROADCAST            | :TQ20004                    |  86680 |    6 |        1 |  +135 |    8 |      64 |      |      |      |      |          |      |          |                              |
                                | 58 |                VIEW                        |                            |  86680 |    6 |        1 |  +135 |    8 |        8 |      |      |      |      |          |      |          |                              |
                                | 59 |                  PX BLOCK ITERATOR          |                            |  86680 |    6 |        1 |  +135 |    8 |        8 |      |      |      |      |          |      |          |                              |
                                | 60 |                  TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D8788_A113BAA7 |  86680 |    6 |        1 |  +135 |  103 |        8 |      |      |      |      |          |      |          |                              |
                                | 61 |            JOIN FILTER USE                | :BF0005                    |    10M | 36683 |        44 |  +136 |    8 |      29M |      |      |      |      |          |      |          |                              |
                                | 62 |              PX BLOCK ITERATOR              |                            |    10M | 36683 |        44 |  +136 |    8 |      29M |      |      |      |      |          |      |          |                              |
                                | 63 |              TABLE ACCESS STORAGE FULL    | T_SEND_MESSAGES_PART        |    10M | 36683 |        46 |  +136 |  446 |      29M | 40156 |  37GB |      |      |  94.26% |  25M |    25.33 | Cpu (150)                    |
                                |    |                                            |                            |        |      |          |        |      |          |      |      |      |      |          |      |          | cell smart table scan (556)  |
                                ==========================================================================================================================================================================================================================================
                                 
                                Query Block Name / Object Alias (identified by operation id):
                                -------------------------------------------------------------

                                  1 - SEL$335DD26A
                                  2 - SEL$1
                                  6 - SEL$1        / P@SEL$1
                                  17 - SEL$335DD26A / I@SEL$3
                                  18 - SEL$D67CB2D6 / P@SEL$3
                                  20 - SEL$D67CB2D6 / T1@SEL$D67CB2D6
                                  21 - SET$1        / T@SEL$3
                                  22 - SET$1
                                  23 - SEL$83632299
                                  24 - SEL$D67CB2D5 / P@SEL$4
                                  26 - SEL$D67CB2D5 / T1@SEL$D67CB2D5
                                  30 - SEL$83632299 / T1@SEL$4
                                  31 - SEL$8C13D988
                                  36 - SEL$D67CB2D4 / P@SEL$5
                                  38 - SEL$D67CB2D4 / T1@SEL$D67CB2D4
                                  41 - SEL$8C13D988 / T2@SEL$5
                                  42 - SEL$529045D0
                                  47 - SEL$D67CB2D3 / P@SEL$6
                                  49 - SEL$D67CB2D3 / T1@SEL$D67CB2D3
                                  52 - SEL$529045D0 / T3@SEL$6
                                  53 - SEL$CFDACA1E
                                  58 - SEL$D67CB2D2 / P@SEL$7
                                  60 - SEL$D67CB2D2 / T1@SEL$D67CB2D2
                                  63 - SEL$CFDACA1E / T4@SEL$7

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

                                  /*+
                                      BEGIN_OUTLINE_DATA
                                      IGNORE_OPTIM_EMBEDDED_HINTS
                                      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
                                      DB_VERSION('12.1.0.2')
                                      OPT_PARAM('_optimizer_use_feedback' 'false')
                                      OPT_PARAM('_px_adaptive_dist_method' 'off')
                                      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
                                      OPT_PARAM('_optimizer_gather_feedback' 'false')
                                      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
                                      OPT_PARAM('_fix_control' '7452863:0')
                                      ALL_ROWS
                                      OUTLINE_LEAF(@"SEL$1")
                                      OUTLINE_LEAF(@"SEL$D67CB2D5")
                                      OUTLINE_LEAF(@"SEL$83632299")
                                      MERGE(@"SEL$4")
                                      OUTLINE_LEAF(@"SEL$D67CB2D4")
                                      OUTLINE_LEAF(@"SEL$8C13D988")
                                      MERGE(@"SEL$5")
                                      OUTLINE_LEAF(@"SEL$D67CB2D3")
                                      OUTLINE_LEAF(@"SEL$529045D0")
                                      MERGE(@"SEL$6")
                                      OUTLINE_LEAF(@"SEL$D67CB2D2")
                                      OUTLINE_LEAF(@"SEL$CFDACA1E")
                                      MERGE(@"SEL$7")
                                      OUTLINE_LEAF(@"SET$1")
                                      OUTLINE_LEAF(@"SEL$D67CB2D6")
                                      OUTLINE_LEAF(@"SEL$335DD26A")
                                      MERGE(@"SEL$3")
                                      OUTLINE(@"SEL$1")
                                      OUTLINE(@"SEL$8")
                                      OUTLINE(@"SEL$4")
                                      OUTLINE(@"SEL$9")
                                      OUTLINE(@"SEL$5")
                                      OUTLINE(@"SEL$10")
                                      OUTLINE(@"SEL$6")
                                      OUTLINE(@"SEL$11")
                                      OUTLINE(@"SEL$7")
                                      OUTLINE(@"SEL$2")
                                      OUTLINE(@"SEL$3")
                                      FULL(@"SEL$335DD26A" "I"@"SEL$3")
                                      NO_ACCESS(@"SEL$335DD26A" "P"@"SEL$3")
                                      NO_ACCESS(@"SEL$335DD26A" "T"@"SEL$3")
                                      LEADING(@"SEL$335DD26A" "I"@"SEL$3" "P"@"SEL$3" "T"@"SEL$3")
                                      USE_HASH(@"SEL$335DD26A" "P"@"SEL$3")
                                      USE_HASH(@"SEL$335DD26A" "T"@"SEL$3")
                                      PQ_DISTRIBUTE(@"SEL$335DD26A" "P"@"SEL$3" BROADCAST NONE)
                                      PQ_REPLICATE(@"SEL$335DD26A" "P"@"SEL$3")
                                      PQ_DISTRIBUTE(@"SEL$335DD26A" "T"@"SEL$3" BROADCAST NONE)
                                      FULL(@"SEL$D67CB2D6" "T1"@"SEL$D67CB2D6")
                                      PQ_CONCURRENT_UNION(@"SET$1")
                                      NO_ACCESS(@"SEL$CFDACA1E" "P"@"SEL$7")
                                      FULL(@"SEL$CFDACA1E" "T4"@"SEL$7")
                                      LEADING(@"SEL$CFDACA1E" "P"@"SEL$7" "T4"@"SEL$7")
                                      USE_HASH(@"SEL$CFDACA1E" "T4"@"SEL$7")
                                      PQ_DISTRIBUTE(@"SEL$CFDACA1E" "T4"@"SEL$7" BROADCAST NONE)
                                      PX_JOIN_FILTER(@"SEL$CFDACA1E" "T4"@"SEL$7")
                                      NO_ACCESS(@"SEL$529045D0" "P"@"SEL$6")
                                      FULL(@"SEL$529045D0" "T3"@"SEL$6")
                                      LEADING(@"SEL$529045D0" "P"@"SEL$6" "T3"@"SEL$6")
                                      USE_HASH(@"SEL$529045D0" "T3"@"SEL$6")
                                      PQ_DISTRIBUTE(@"SEL$529045D0" "T3"@"SEL$6" BROADCAST NONE)
                                      PX_JOIN_FILTER(@"SEL$529045D0" "T3"@"SEL$6")
                                      NO_ACCESS(@"SEL$8C13D988" "P"@"SEL$5")
                                      FULL(@"SEL$8C13D988" "T2"@"SEL$5")
                                      LEADING(@"SEL$8C13D988" "P"@"SEL$5" "T2"@"SEL$5")
                                      USE_HASH(@"SEL$8C13D988" "T2"@"SEL$5")
                                      PQ_DISTRIBUTE(@"SEL$8C13D988" "T2"@"SEL$5" BROADCAST NONE)
                                      PX_JOIN_FILTER(@"SEL$8C13D988" "T2"@"SEL$5")
                                      NO_ACCESS(@"SEL$83632299" "P"@"SEL$4")
                                      FULL(@"SEL$83632299" "T1"@"SEL$4")
                                      LEADING(@"SEL$83632299" "P"@"SEL$4" "T1"@"SEL$4")
                                      USE_HASH(@"SEL$83632299" "T1"@"SEL$4")
                                      PQ_DISTRIBUTE(@"SEL$83632299" "T1"@"SEL$4" NONE BROADCAST)
                                      FULL(@"SEL$D67CB2D5" "T1"@"SEL$D67CB2D5")
                                      FULL(@"SEL$D67CB2D4" "T1"@"SEL$D67CB2D4")
                                      FULL(@"SEL$D67CB2D3" "T1"@"SEL$D67CB2D3")
                                      FULL(@"SEL$D67CB2D2" "T1"@"SEL$D67CB2D2")
                                      FULL(@"SEL$1" "P"@"SEL$1")
                                      END_OUTLINE_DATA
                                  */

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

                                  6 - storage(:Z>=:Z AND :Z<=:Z AND (UPPER("P"."ISIN") LIKE '%CNE100000XX9%' AND "P"."T_DT"<=TO_DATE(' 2020-09-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
                                      filter((UPPER("P"."ISIN") LIKE '%CNE100000XX9%' AND "P"."T_DT"<=TO_DATE(' 2020-09-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
                                  7 - filter(ROWNUM<=30001)
                                  11 - filter(ROWNUM<=30001)
                                  12 - access("P"."T_DT"="T"."T_DT" AND "P"."I_ID"="T"."I_ID" AND "P"."PRODUCTID"="T"."PRODUCTID")
                                  15 - access("I"."T_DT"="P"."T_DT" AND "I"."I_ID"="P"."I_ID")
                                  17 - storage("I"."T_DT"<=TO_DATE(' 2020-09-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
                                      filter("I"."T_DT"<=TO_DATE(' 2020-09-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
                                  18 - filter(("P"."T_DT">=TO_DATE(' 2020-08-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "P"."T_DT"<=TO_DATE(' 2020-09-03 00:00:00', 'syyyy-mm-dd
                                              hh24:mi:ss') AND UPPER("P"."ISIN") LIKE '%CNE100000XX9%'))
                                  20 - storage(:Z>=:Z AND :Z<=:Z)
                                  23 - access("P"."T_DT"="T1"."T_DT" AND "P"."I_ID"="T1"."I_ID" AND "P"."PRODUCTID"="T1"."PRODUCTID")
                                  24 - filter(("P"."T_DT">=TO_DATE(' 2020-08-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "P"."T_DT"<=TO_DATE(' 2020-09-03 00:00:00', 'syyyy-mm-dd
                                              hh24:mi:ss')))
                                  26 - storage(:Z>=:Z AND :Z<=:Z)
                                  30 - storage(:Z>=:Z AND :Z<=:Z AND (TO_CHAR(INTERNAL_FUNCTION("T1"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                              TO_CHAR(INTERNAL_FUNCTION("T1"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND NVL(TO_CHAR(INTERNAL_FUNCTION("T1"."EXECUTION_TIME"),'HH24:MI:SS'),'%') LIKE '%'
                                              AND "T1"."T_DT"<=TO_DATE(' 2020-09-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
                                      filter((TO_CHAR(INTERNAL_FUNCTION("T1"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                              TO_CHAR(INTERNAL_FUNCTION("T1"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND NVL(TO_CHAR(INTERNAL_FUNCTION("T1"."EXECUTION_TIME"),'HH24:MI:SS'),'%') LIKE '%'
                                              AND "T1"."T_DT"<=TO_DATE(' 2020-09-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
                                  31 - access("P"."T_DT"="T2"."T_DT" AND "P"."I_ID"="T2"."I_ID" AND "P"."PRODUCTID"="T2"."PRODUCTID")
                                  36 - filter(("P"."T_DT">=TO_DATE(' 2020-08-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "P"."T_DT"<=TO_DATE(' 2020-09-03 00:00:00', 'syyyy-mm-dd
                                              hh24:mi:ss')))
                                  38 - storage(:Z>=:Z AND :Z<=:Z)
                                  41 - storage(:Z>=:Z AND :Z<=:Z AND (TO_CHAR(INTERNAL_FUNCTION("T2"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                              TO_CHAR(INTERNAL_FUNCTION("T2"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T2"."T_DT"<=TO_DATE(' 2020-09-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
                                              SYS_OP_BLOOM_FILTER(:BF0000,"T2"."T_DT","T2"."I_ID","T2"."PRODUCTID")))
                                      filter((TO_CHAR(INTERNAL_FUNCTION("T2"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                              TO_CHAR(INTERNAL_FUNCTION("T2"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T2"."T_DT"<=TO_DATE(' 2020-09-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
                                              SYS_OP_BLOOM_FILTER(:BF0000,"T2"."T_DT","T2"."I_ID","T2"."PRODUCTID")))
                                  42 - access("P"."T_DT"="T3"."T_DT" AND "P"."I_ID"="T3"."I_ID" AND "P"."PRODUCTID"="T3"."PRODUCTID")
                                  47 - filter(("P"."T_DT">=TO_DATE(' 2020-08-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "P"."T_DT"<=TO_DATE(' 2020-09-03 00:00:00', 'syyyy-mm-dd
                                              hh24:mi:ss')))
                                  49 - storage(:Z>=:Z AND :Z<=:Z)
                                  52 - storage(:Z>=:Z AND :Z<=:Z AND (TO_CHAR(INTERNAL_FUNCTION("T3"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                              TO_CHAR(INTERNAL_FUNCTION("T3"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T3"."T_DT"<=TO_DATE(' 2020-09-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
                                              SYS_OP_BLOOM_FILTER(:BF0000,"T3"."T_DT","T3"."I_ID","T3"."PRODUCTID")))
                                      filter((TO_CHAR(INTERNAL_FUNCTION("T3"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                              TO_CHAR(INTERNAL_FUNCTION("T3"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T3"."T_DT"<=TO_DATE(' 2020-09-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
                                              SYS_OP_BLOOM_FILTER(:BF0000,"T3"."T_DT","T3"."I_ID","T3"."PRODUCTID")))
                                  53 - access("P"."T_DT"="T4"."T_DT" AND "P"."I_ID"="T4"."I_ID" AND "P"."PRODUCTID"="T4"."PRODUCTID")
                                  58 - filter(("P"."T_DT">=TO_DATE(' 2020-08-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "P"."T_DT"<=TO_DATE(' 2020-09-03 00:00:00', 'syyyy-mm-dd
                                              hh24:mi:ss')))
                                  60 - storage(:Z>=:Z AND :Z<=:Z)
                                  63 - storage(:Z>=:Z AND :Z<=:Z AND (TO_CHAR(INTERNAL_FUNCTION("T4"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                              TO_CHAR(INTERNAL_FUNCTION("T4"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T4"."T_DT"<=TO_DATE(' 2020-09-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
                                              SYS_OP_BLOOM_FILTER(:BF0000,"T4"."T_DT","T4"."I_ID","T4"."PRODUCTID")))
                                      filter((TO_CHAR(INTERNAL_FUNCTION("T4"."PM_TIMESTAMP"),'HH24:MI:SS')>='09:00:00' AND
                                              TO_CHAR(INTERNAL_FUNCTION("T4"."PM_TIMESTAMP"),'HH24:MI:SS')<='18:00:00' AND "T4"."T_DT"<=TO_DATE(' 2020-09-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
                                              SYS_OP_BLOOM_FILTER(:BF0000,"T4"."T_DT","T4"."I_ID","T4"."PRODUCTID")))

                                Note
                                -----
                                  - Degree of Parallelism is 8 because of hint

                                • 13. Re: Bloom filters and view using UNION ALL
                                  Jonathan Lewis

                                  Bloom filters may give false positives, and that might be why you see 20 - 40M rows being returned by the offload when the final hash join reports only a couple of thousand rows.  However I believe (a) it's possible that predicates reported as STORAGE predicates aren't necessarily passed to the storage layer and (b) Oracle may choose not to use a Bloom filter that's been reported in the plan - I don't know if either (a) or (b) is true, and don't have an Exadata machine in my garage to test on.

                                   

                                  You could do a little investigation about those 20-40M rows.  Generate the data set for the CTE and work out which partitions in one of those big tables are relevant to the query. Then you can start counting

                                   

                                  select count(*) from your derived list of partitions

                                  select count(*) from your derived list of partitions applying "time-only predicate is true"

                                  select count(*) from your derived list of partition applying "time-only predicate is true" AND (t_dt, t_product_id) predicate as a literal list of values.

                                   

                                  It the first one is in the 20-40M range then the storage predicates are not being sent

                                  If the second one is in the 20-40M range then the Bloom filter is not being sent

                                  The last one should be in the "couple of thousand" range,

                                   

                                  One thought that may be important - the filter predicate for the big tablescan includes only ONE of the date range predicates: "T_DT"<=TO_DATE(' 2020-09-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss') rather than both - which is a little odd. Maybe you're visiting far too many partitions because of this - this may explain a large number of false positives - but I would expect the "part join" Bloom filter to eliminate them.

                                   

                                  Your hinting for T_EXECS_PART is incomplete - Oracle is still using a broadcast of what it thinks is the small table (broadcasting 25M rows out to become 200M rows), so you need to add a pq_distibute() hint (and a use_hash() hint to complete things)

                                  /*+

                                  LEADING(@"SEL$83632299" "P"@"SEL$4" "T1"@"SEL$4" ) 

                                  use_hash(@SEL$83632299 t1@sel$4)

                                  pq_distribute(@SEL$83632299 broadcast none)

                                  */

                                   

                                  looks about right.

                                   

                                  (It won't make the query complete significantly more quickly, but it will reduce memory and CPU wastage)


                                  Regards

                                  Jonathan Lewis