I have cardinality misestimate coming from hash join in plan - Oracle is estimating one row and i am getting 9 Billion rows which is Line number 11. All the tables have fresh statistics today.
Code:
SELECT /*+ PARALLEL(8) use_hash(cus) use_hash(ord) */
DISTINCT
agt.rep_id rep_id,
agt.full_name rep_name,
ord.order_desc order_description,
ord.policy_product_type_desc annuity_plan_type,
ord.distributor_client_acct_num client_account_num,
cus.full_name client_name,
ord.order_id order_id,
ord.solicitation_state solicitation_state,
ord.residence_state_cd resident_state,
carr.carrier_name carrier,
ord.policy_cusip_num cusip,
ord.share_class share_class,
ord.txn_execution_dt submission_date,
f3.message_start_dt approval_date,
f3.message_src_userid approver,
( f3.message_subject_txt
|| ' '
|| ' '
|| f3.message_start_dt
|| ' '
|| f3.message_start_ts
|| ' '
|| f3.message_desc ) approval_notes
FROM
aoe_dwh.system_message_fact f3,
aoe_dwh.order_dimension ord,
aoe_dwh.agent_dimension agt,
aoe_dwh.customer_dimension cus,
aoe_dwh.carrier_dimension carr,
aoe_dwh.calendar_dimension cal,
aoe_dwh.funding_info_fact f2
WHERE
ord.order_key = f3.order_key
AND f3.primary_agent_key = agt.agent_key
AND f3.owner_key = cus.customer_key
AND carr.carrier_key = ord.carrier_key
AND f3.trade_dt = cal.calendar_dt
AND cal.MTH_PERIOD_TXT IN ( '2018 / 04','2018 / 05','2018 / 06')
AND f2.primary_agent_key = agt.agent_key
AND f2.owner_key = cus.customer_key
AND f2.order_key = ord.order_key
AND f2.trade_dt = cal.calendar_dt
AND ord.admin_trans_stage IN ( 'DTCC Acknowledged', 'Order Sent to DTCC(Error Detected)', 'Rejected',
'Cancelled' );
Global Information
------------------------------
Status : EXECUTING
Instance ID : 2
Session : SYS (1424:61067)
SQL ID : gw1xtq0rfy3k2
SQL Execution ID : 33554432
Execution Started : 10/04/2021 19:47:41
First Refresh Time : 10/04/2021 19:47:42
Last Refresh Time : 10/04/2021 19:58:23
Duration : 1303s
Module/Action : SQL Developer/-
Service : ebis2prd
Program : SQL Developer
Global Stats
==================================================================================================
| Elapsed | Cpu | IO | Application | Other | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes |
==================================================================================================
| 4480 | 4181 | 190 | 0.00 | 109 | 1G | 21817 | 21GB | 284 | 69MB |
==================================================================================================
Parallel Execution Details (DOP=8 , Servers Allocated=16)
============================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Application | Other | Buffer | Read | Read | Write | Write | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | (sample #) |
============================================================================================================================================================
| PX Coordinator | QC | | 0.17 | 0.17 | | 0.00 | | 138 | | . | | . | |
| p008 | Set 1 | 1 | 16 | 12 | 3.54 | | | 115K | 923 | 891MB | | . | PX qref latch (1) |
| | | | | | | | | | | | | | direct path read (4) |
| p009 | Set 1 | 2 | 15 | 11 | 3.69 | | | 77124 | 625 | 599MB | | . | PX qref latch (2) |
| | | | | | | | | | | | | | direct path read (5) |
| p00a | Set 1 | 3 | 14 | 10 | 3.11 | | | 81754 | 673 | 627MB | | . | PX qref latch (2) |
| | | | | | | | | | | | | | direct path read (3) |
| p00b | Set 1 | 4 | 16 | 12 | 3.50 | | | 105K | 845 | 813MB | | . | PX qref latch (5) |
| | | | | | | | | | | | | | direct path read (2) |
| p00c | Set 1 | 5 | 14 | 11 | 3.82 | | | 109K | 869 | 848MB | | . | PX qref latch (3) |
| | | | | | | | | | | | | | direct path read (4) |
| p00d | Set 1 | 6 | 13 | 10 | 3.06 | | | 109K | 867 | 846MB | | . | PX qref latch (3) |
| | | | | | | | | | | | | | direct path read (3) |
| p00e | Set 1 | 7 | 13 | 10 | 3.12 | | | 84328 | 681 | 653MB | | . | PX qref latch (3) |
| | | | | | | | | | | | | | direct path read (4) |
| p00f | Set 1 | 8 | 16 | 12 | 3.39 | | | 97055 | 783 | 751MB | | . | PX qref latch (3) |
| | | | | | | | | | | | | | direct path read (4) |
| p00g | Set 2 | 1 | 388 | 357 | 20 | | 11 | 87M | 1862 | 2GB | 41 | 10MB | direct path read (22) |
| p00h | Set 2 | 2 | 537 | 503 | 20 | | 14 | 38M | 2022 | 2GB | 32 | 8MB | direct path read (18) |
| p00i | Set 2 | 3 | 378 | 349 | 20 | | 8.64 | 82M | 1831 | 2GB | 33 | 8MB | direct path read (21) |
| p00j | Set 2 | 4 | 330 | 301 | 20 | | 8.05 | 80M | 1918 | 2GB | 42 | 10MB | direct path read (21) |
| p00k | Set 2 | 5 | 600 | 563 | 20 | | 17 | 116M | 1909 | 2GB | 25 | 6MB | direct path read (19) |
| p00l | Set 2 | 6 | 485 | 454 | 21 | | 10 | 216M | 1956 | 2GB | 37 | 9MB | direct path read (22) |
| p00m | Set 2 | 7 | 1228 | 1175 | 20 | | 32 | 400M | 2002 | 2GB | 38 | 9MB | direct path read (21) |
| p00n | Set 2 | 8 | 419 | 390 | 20 | | 8.89 | 112M | 2051 | 2GB | 36 | 9MB | direct path read (20) |
============================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=565187942)
===========================================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | | | (%) | (# samples) | |
===========================================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 17 | | | | | | . | . | | | |
| 1 | PX COORDINATOR | | | | | | 17 | | | | | | . | . | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10007 | 1 | 289K | | | | | | | | | . | . | | | |
| 3 | SORT GROUP BY | | 1 | 289K | | | | | | | | | . | . | | | |
| 4 | PX RECEIVE | | 1 | 289K | | | | | | | | | . | . | | | |
| 5 | PX SEND HASH | :TQ10006 | 1 | 289K | | | 8 | | | | | | . | . | | | |
| -> 6 | SORT GROUP BY | | 1 | 289K | 1272 | +31 | 8 | 0 | | | | | . | . | 21.49 | Cpu (952) | |
| 7 | NESTED LOOPS | | 1 | 289K | 1271 | +31 | 8 | 474M | | | | | . | . | 0.09 | Cpu (4) | |
| 8 | NESTED LOOPS | | 1 | 289K | 1271 | +31 | 8 | 474M | | | | | . | . | 0.11 | Cpu (5) | |
| 9 | NESTED LOOPS | | 1 | 289K | 1271 | +31 | 8 | 576M | | | | | . | . | 0.27 | Cpu (12) | |
| 10 | NESTED LOOPS SEMI | | 1 | 289K | 1271 | +31 | 8 | 576M | | | | | . | . | 3.18 | Cpu (141) | |
| -> 11 | HASH JOIN | | 1 | 289K | 1274 | +29 | 8 | 9G | | | | | 515MB | . | 48.14 | Cpu (2132) | |
| 12 | JOIN FILTER CREATE | :BF0000 | 2M | 85095 | 3 | +29 | 8 | 2M | | | | | . | . | | | |
| 13 | PX RECEIVE | | 2M | 85095 | 4 | +28 | 8 | 2M | | | | | . | . | 0.05 | Cpu (2) | |
| 14 | PX SEND HYBRID HASH | :TQ10004 | 2M | 85095 | 3 | +29 | 8 | 2M | | | | | . | . | | | |
| 15 | STATISTICS COLLECTOR | | | | 3 | +29 | 8 | 2M | | | | | . | . | | | |
| 16 | HASH JOIN BUFFERED | | 2M | 85095 | 27 | +5 | 8 | 2M | | | | | . | . | 0.02 | Cpu (1) | |
| 17 | JOIN FILTER CREATE | :BF0001 | 2M | 11446 | 3 | +5 | 8 | 2M | | | | | . | . | | | |
| 18 | PX RECEIVE | | 2M | 11446 | 3 | +5 | 8 | 2M | | | | | . | . | | | |
| 19 | PX SEND HYBRID HASH | :TQ10002 | 2M | 11446 | 3 | +5 | 8 | 2M | | | | | . | . | | | |
| 20 | STATISTICS COLLECTOR | | | | 3 | +5 | 8 | 2M | | | | | . | . | | | |
| 21 | HASH JOIN BUFFERED | | 2M | 11446 | 5 | +3 | 8 | 2M | 284 | 69MB | 284 | 69MB | . | . | | | |
| 22 | JOIN FILTER CREATE | :BF0002 | 473K | 6649 | 3 | +3 | 8 | 473K | | | | | . | . | | | |
| 23 | PX RECEIVE | | 473K | 6649 | 3 | +3 | 8 | 473K | | | | | . | . | | | |
| 24 | PX SEND HYBRID HASH | :TQ10000 | 473K | 6649 | 3 | +3 | 8 | 473K | | | | | . | . | | | |
| 25 | STATISTICS COLLECTOR | | | | 3 | +3 | 8 | 473K | | | | | . | . | | | |
| 26 | PX BLOCK ITERATOR | | 473K | 6649 | 3 | +3 | 8 | 473K | | | | | . | . | | | |
| 27 | TABLE ACCESS FULL | ORDER_DIMENSION | 473K | 6649 | 5 | +1 | 198 | 473K | 1383 | 1GB | | | . | . | 0.36 | Cpu (2) | |
| | | | | | | | | | | | | | | | | direct path read (14) | |
| 28 | PX RECEIVE | | 5M | 4795 | 3 | +3 | 8 | 3M | | | | | . | . | 0.02 | Cpu (1) | |
| 29 | PX SEND HYBRID HASH | :TQ10001 | 5M | 4795 | 3 | +3 | 8 | 3M | | | | | . | . | 0.07 | Cpu (3) | |
| 30 | JOIN FILTER USE | :BF0002 | 5M | 4795 | 1 | +5 | 8 | 3M | | | | | . | . | | | |
| 31 | PX BLOCK ITERATOR | | 5M | 4795 | 1 | +5 | 8 | 3M | | | | | . | . | | | |
| 32 | TABLE ACCESS FULL | FUNDING_INFO_FACT | 5M | 4795 | 3 | +3 | 123 | 3M | 1068 | 1GB | | | . | . | 0.29 | Cpu (1) | |
| | | | | | | | | | | | | | | | | direct path read (12) | |
| 33 | PX RECEIVE | | 88M | 73616 | 23 | +7 | 8 | 8M | | | | | . | . | 0.02 | Cpu (1) | |
| 34 | PX SEND HYBRID HASH | :TQ10003 | 88M | 73616 | 23 | +7 | 8 | 8M | | | | | . | . | 0.07 | Cpu (3) | |
| 35 | JOIN FILTER USE | :BF0001 | 88M | 73616 | 23 | +7 | 8 | 8M | | | | | . | . | | | |
| 36 | PX BLOCK ITERATOR | | 88M | 73616 | 23 | +7 | 8 | 8M | | | | | . | . | | | |
| 37 | TABLE ACCESS FULL | CUSTOMER_DIMENSION | 88M | 73616 | 25 | +5 | 208 | 8M | 15267 | 15GB | | | . | . | 3.99 | Cpu (13) | |
| | | | | | | | | | | | | | | | | direct path read (164) | |
| 38 | PX RECEIVE | | 75M | 204K | 1271 | +31 | 8 | 6M | | | | | . | . | 1.45 | Cpu (64) | |
| 39 | PX SEND HYBRID HASH | :TQ10005 | 75M | 204K | 1271 | +31 | 8 | 6M | | | | | . | . | 0.68 | Cpu (8) | |
| | | | | | | | | | | | | | | | | PX qref latch (22) | |
| -> 40 | JOIN FILTER USE | :BF0000 | 75M | 204K | 1271 | +31 | 8 | 6M | | | | | . | . | | | |
| -> 41 | PX BLOCK ITERATOR | | 75M | 204K | 1271 | +31 | 8 | 6M | | | | | . | . | | | |
| 42 | TABLE ACCESS FULL | SYSTEM_MESSAGE_FACT | 75M | 204K | 1271 | +31 | 21 | 6M | 3815 | 4GB | | | . | . | 0.16 | Cpu (4) | 53% |
| | | | | | | | | | | | | | | | | direct path read (3) | |
| 43 | TABLE ACCESS BY INDEX ROWID | CALENDAR_DIMENSION | 1 | | 1265 | +31 | 21533 | 24 | | | | | . | . | | | |
| 44 | INDEX UNIQUE SCAN | XAK1CALENDAR_DIMENSION | 1 | | 1265 | +31 | 21533 | 20587 | | | | | . | . | | | |
| -> 45 | TABLE ACCESS BY INDEX ROWID | CARRIER_DIMENSION | 1 | | 1274 | +29 | 576M | 576M | | | | | . | . | 6.25 | Cpu (277) | |
| 46 | INDEX UNIQUE SCAN | XPKCARRIER_DIMENSION | 1 | | 1271 | +31 | 576M | 576M | | | | | . | . | 2.98 | Cpu (132) | |
| 47 | INDEX UNIQUE SCAN | XPKAGENT_DIMENSION | 1 | | 1273 | +29 | 576M | 474M | | | | | . | . | 4.31 | Cpu (191) | |
| -> 48 | TABLE ACCESS BY INDEX ROWID | AGENT_DIMENSION | 1 | | 1274 | +29 | 474M | 474M | | | | | . | . | 5.98 | Cpu (265) | |
===========================================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
11 - access("ORD"."ORDER_KEY"="F3"."ORDER_KEY" AND "F3"."OWNER_KEY"="CUS"."CUSTOMER_KEY")
16 - access("F2"."OWNER_KEY"="CUS"."CUSTOMER_KEY")
21 - access("F2"."ORDER_KEY"="ORD"."ORDER_KEY")
27 - access(:Z>=:Z AND :Z<=:Z)
filter(("ORD"."ADMIN_TRANS_STAGE"='Cancelled' OR "ORD"."ADMIN_TRANS_STAGE"='DTCC Acknowledged' OR "ORD"."ADMIN_TRANS_STAGE"='Order
Sent to DTCC(Error Detected)' OR "ORD"."ADMIN_TRANS_STAGE"='Rejected'))
32 - access(:Z>=:Z AND :Z<=:Z)
filter(SYS_OP_BLOOM_FILTER(:BF0002,"F2"."ORDER_KEY"))
37 - access(:Z>=:Z AND :Z<=:Z)
filter(SYS_OP_BLOOM_FILTER(:BF0001,"CUS"."CUSTOMER_KEY"))
42 - access(:Z>=:Z AND :Z<=:Z)
filter(SYS_OP_BLOOM_FILTER(:BF0000,"F3"."ORDER_KEY","F3"."OWNER_KEY"))
43 - filter(("CAL"."MTH_PERIOD_TXT"='2018 / 04' OR "CAL"."MTH_PERIOD_TXT"='2018 / 05' OR "CAL"."MTH_PERIOD_TXT"='2018 / 06'))
44 - access("F2"."TRADE_DT"="CAL"."CALENDAR_DT")
filter("F3"."TRADE_DT"="CAL"."CALENDAR_DT")
46 - access("CARR"."CARRIER_KEY"="ORD"."CARRIER_KEY")
47 - access("F2"."PRIMARY_AGENT_KEY"="AGT"."AGENT_KEY")
filter("F3"."PRIMARY_AGENT_KEY"="AGT"."AGENT_KEY")
Note
-----
- Degree of Parallelism is 8 because of hint