Discussions
Categories
- 385K All Categories
- 2.6K Data
- 595 Big Data Appliance
- 2K Data Science
- 452.1K Databases
- 222.4K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 32 Multilingual Engine
- 576 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 575 SQLcl
- 4K SQL Developer Data Modeler
- 187.7K SQL & PL/SQL
- 21.5K SQL Developer
- 12 Data Integration
- 12 GoldenGate
- 297.7K Development
- 3 Application Development
- 18 Developer Projects
- 140 Programming Languages
- 294.3K Development Tools
- 118 DevOps
- 3.1K QA/Testing
- 646.5K Java
- 30 Java Learning Subscription
- 37K Database Connectivity
- 185 Java Community Process
- 107 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 174 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 206 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 584 LiveLabs
- 41 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 191 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 240 Portuguese
Bloom filters and view using UNION ALL
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
Best 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
-
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
-
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)
-
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
-
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]$5Outline 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 -
When you say "rewrite your query" you mean "use an union of join"?
-
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]$5Outline 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 -
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 : 302Global 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 : 3Global 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 | | -
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
-
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 : 141Global 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 hintDepsite 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
601933If 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(*)
----------
2622select /*+ 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(*)
----------
2868select /*+ 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(*)
----------
2644But 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%).
-
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