Forum Stats

  • 3,723,262 Users
  • 2,244,521 Discussions
  • 7,850,383 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Bloom filters and view using UNION ALL

Ahmed AANGOUR
Ahmed AANGOUR Member Posts: 72
edited September 2020 in General Database Discussions

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

Mohamed Houri

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,560 Gold Crown
    edited September 2020 Accepted Answer

    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)

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,560 Gold Crown
    edited September 2020

    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

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,560 Gold Crown
    edited September 2020 Accepted Answer

    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)

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,560 Gold Crown
    edited September 2020

    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

  • Ahmed AANGOUR
    Ahmed AANGOUR Member Posts: 72
    edited September 2020

    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 / [email protected]$1
      17 - SEL$1 / [email protected]$1
      18 - SET$1 / [email protected]$1
      19 - SET$1
      20 - SEL$2
      21 - SEL$2 / [email protected]$2
      22 - SEL$3
      23 - SEL$3 / [email protected]$3
      24 - SEL$4
      25 - SEL$4 / [email protected]$4
      26 - SEL$5
      27 - SEL$5 / [email protected]$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([email protected]!-5))
          filter("I"."T_DT"=TRUNC([email protected]!-5))
      17 - storage(UPPER("P"."ISIN") LIKE '%CNE100000XX9%' AND "P"."T_DT"=TRUNC([email protected]!-5) AND SYS_OP_BLOOM_FILTER(:BF0001,"P"."T_DT","P"."I_ID"))
          filter(UPPER("P"."ISIN") LIKE '%CNE100000XX9%' AND "P"."T_DT"=TRUNC([email protected]!-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([email protected]!-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([email protected]!-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([email protected]!-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([email protected]!-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([email protected]!-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([email protected]!-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([email protected]!-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([email protected]!-5))

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

  • Ahmed AANGOUR
    Ahmed AANGOUR Member Posts: 72
    edited September 2020

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

  • Ahmed AANGOUR
    Ahmed AANGOUR Member Posts: 72
    edited September 2020

    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 / [email protected]$1
      17 - SEL$1 / [email protected]$1
      20 - SET$1 / [email protected]$1
      21 - SET$1
      22 - SEL$2
      23 - SEL$2 / [email protected]$2
      24 - SEL$3
      25 - SEL$3 / [email protected]$3
      26 - SEL$4
      27 - SEL$4 / [email protected]$4
      28 - SEL$5
      29 - SEL$5 / [email protected]$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([email protected]!-5))
           filter("I"."T_DT"=TRUNC([email protected]!-5))
      17 - storage(UPPER("P"."ISIN") LIKE '%CNE100000XX9%' AND "P"."T_DT"=TRUNC([email protected]!-5) AND SYS_OP_BLOOM_FILTER(:BF0001,"P"."T_DT","P"."I_ID"))
           filter(UPPER("P"."ISIN") LIKE '%CNE100000XX9%' AND "P"."T_DT"=TRUNC([email protected]!-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([email protected]!-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([email protected]!-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([email protected]!-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([email protected]!-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([email protected]!-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([email protected]!-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([email protected]!-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([email protected]!-5))

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

  • Ahmed AANGOUR
    Ahmed AANGOUR Member Posts: 72
    edited September 2020

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

    Mohamed Houri
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,560 Gold Crown
    edited September 2020

    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

    Mohamed Houri
  • Ahmed AANGOUR
    Ahmed AANGOUR Member Posts: 72
    edited September 2020

    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%).

  • Ahmed AANGOUR
    Ahmed AANGOUR Member Posts: 72
    edited September 2020

    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

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,560 Gold Crown
    edited September 2020

    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

  • Ahmed AANGOUR
    Ahmed AANGOUR Member Posts: 72
    edited September 2020

    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        / [email protected]$1
      17 - SEL$335DD26A / [email protected]$3
      18 - SEL$D67CB2D6 / [email protected]$3
      20 - SEL$D67CB2D6 / [email protected]$D67CB2D6
      21 - SET$1        / [email protected]$3
      22 - SET$1
      23 - SEL$83632299
      24 - SEL$D67CB2D5 / [email protected]$4
      26 - SEL$D67CB2D5 / [email protected]$D67CB2D5
      30 - SEL$83632299 / [email protected]$4
      31 - SEL$8C13D988
      36 - SEL$D67CB2D4 / [email protected]$5
      38 - SEL$D67CB2D4 / [email protected]$D67CB2D4
      41 - SEL$8C13D988 / [email protected]$5
      42 - SEL$529045D0
      47 - SEL$D67CB2D3 / [email protected]$6
      49 - SEL$D67CB2D3 / [email protected]$D67CB2D3
      52 - SEL$529045D0 / [email protected]$6
      53 - SEL$CFDACA1E
      58 - SEL$D67CB2D2 / [email protected]$7
      60 - SEL$D67CB2D2 / [email protected]$D67CB2D2
      63 - SEL$CFDACA1E / [email protected]$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

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,560 Gold Crown
    edited September 2020

    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 [email protected]$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

Sign In or Register to comment.