Hi, trying to get rid of HASH JOIN RIGHT SEMI BUFFERED as a PM_CLOB_IBS_NC is small result set and AB_CLOBS is a huge table 300GB segment and filter factor is 75% . I'm on EE 11.2.0.3 currently hash hash dist proposed by CBO :( .
1 explain plan for
2 INSERT /*+ append parallel(4) PQ_DISTRIBUTE(T, BROADCAST NONE) */ INTO PM_AB_CLOBS t select /*+ parallel(4) */ * from AB_CLOBS c
3 where
4* c.ec_id in ( select p.EV_ID from PM_CLOB_IBS_NC p) and ec_data not like '%TARGET_SYSTEM' || chr(9) || 'PL%'
SQL> /
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2913763871
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 9893K| 16G| 2593K (1)| 01:26:28 | | | |
| 1 | LOAD AS SELECT | PM_AB_CLOBS | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 9893K| 16G| 2593K (1)| 01:26:28 | Q1,02 | P->S | QC (RAND) |
|* 4 | HASH JOIN RIGHT SEMI BUFFERED| | 9893K| 16G| 2593K (1)| 01:26:28 | Q1,02 | PCWP | |
| 5 | PX RECEIVE | | 9893K| 179M| 2460 (2)| 00:00:05 | Q1,02 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 9893K| 179M| 2460 (2)| 00:00:05 | Q1,00 | P->P | HASH |
| 7 | PX BLOCK ITERATOR | | 9893K| 179M| 2460 (2)| 00:00:05 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL | PM_CLOB_IBS_NC | 9893K| 179M| 2460 (2)| 00:00:05 | Q1,00 | PCWP | |
| 9 | PX RECEIVE | | 54M| 90G| 2591K (1)| 01:26:23 | Q1,02 | PCWP | |
| 10 | PX SEND HASH | :TQ10001 | 54M| 90G| 2591K (1)| 01:26:23 | Q1,01 | P->P | HASH |
| 11 | PX BLOCK ITERATOR | | 54M| 90G| 2591K (1)| 01:26:23 | Q1,01 | PCWC | |
|* 12 | TABLE ACCESS FULL | AB_CLOBS | 54M| 90G| 2591K (1)| 01:26:23 | Q1,01 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("C"."EC_ID"="P"."EV_ID")
12 - filter("EC_DATA" NOT LIKE '%TARGET_SYSTEM PL%')
Note
-----
- dynamic sampling used for this statement (level=7)
- Degree of Parallelism is 4 because of hint