Hi,
Version 10204
I have a data warehouse statment that is automatically generated by analytic crm tool.
I can not use hints .
The problem is in lines 35-36.
There in an index on the field : UA_CONTACTHISTORY.LEAD_ID but oracle choose to do TABLE ACCESS FULL on 2 million rows partitioned table named :UA_CONTACTHISTORY insted using the index.
When i use INDEX hint on this table using sqlplus the statment is finished after 5 minute.
Since i cant use hint , and the statments is automatically generated its took about 5 hours to complete.
I gather 100% table stat on the table and its indexes , also collect histograms size 254 on the lead_id column but yet the statment still using TABLE ACESS FULL.
more information :
sql > show parameter dynamic
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
sql > select num_rows,last_analyzed from dba_tables where table_name='UA_CONTACTHISTORY';
NUM_ROWS LAST_ANAL
---------- ---------
2365998 09-JUL-09
Any Advice ?
Frow the AWR reoprt the statment is:
SQL> SELECT * FROM table(dbms_xplan.display_awr('7npmxp0mz00f5'));
PLAN_TABLE_OUTPUT
--------------------------
SQL_ID 7npmxp0mz00f5
--------------------
INSERT INTO UAC_14270_wd (InternalRowNumber, LEAD_ID, TreatmentInstID, ExpirationDateTime,
RunDateTime, EffectiveDateTime, HasDetailHistory, CntrlTreatmtFlag, PackageID,
ContactDateTime, DirectResponse) SELECT TInputs.InternalRowNumber, TInputs.LEAD_ID,
TInputs.TreatmentInstID, TInputs.ExpirationDateTime, TInputs.RunDateTime, TInputs.EffectiveDateTime,
TInputs.HasDetailHistory, TInputs.CntrlTreatmtFlag, TInputs.PackageID, CInputs.ContactDateTime,
1 FROM ( SELECT UAC_14270_vd.LEAD_ID, Cand.* FROM UAC_14270_vd INNER JOIN ( SELECT
UAC_14270_vd.InternalRowNumber, cm.UA_Treatment.* FROM UAC_14270_vd, cm.UA_Treatment WHERE
UAC_14270_vd.TreatmentLookup = cm.UA_Treatment.TreatmentInstID AND
(cm.UA_Treatment.ExpirationDateTime > '2009-04-08 00:00:00' OR
cm.UA_Treatment.ExpirationDateTime IS NULL) UNION SELECT UAC_14270_vd.InternalRowNumber,
cm.UA_Treatment.* FROM UAC_14270_vd, cm.UA_Treatment WHERE UAC_14270_vd.OfferLookup =
cm.UA_Treatment.OfferID AND (cm.UA_Treatment.ExpirationDateTime > '2009-04-08 00:00:00' OR
cm.UA_Treatment.ExpirationDateTime IS NULL) AND (UAC_14270_vd.TreatmentLookup IS NOT NULL OR
(cm.UA_Treatment.CntrlTreatmtFlag = 0)) UNION SELECT UAC_14270_vd.InternalRowNumber,
cm.UA_Treatment.* FROM UAC_14270_vd, cm.UA_Treatment WHERE UAC_14270_vd.CellLookup =
cm.UA_Treatment.CellID AND (cm.UA_Treatment.ExpirationDateTime > '2009-04-08 00:00:00' OR
cm.UA_Treatment.ExpirationDateTime IS NULL) AND (UAC_14270_vd.TreatmentLookup IS NOT NULL OR
(cm.UA_Treatment.CntrlTreatmtFlag = 0)) UNION SELECT UAC_14270_vd.InternalRowNumber,
cm.UA_Treatment.* FROM UAC_14270_vd, cm.UA_Treatment WHERE UAC_14270_vd.CampaignLookup =
cm.UA_Treatment.CampaignID AND (cm.UA_Treatment.ExpirationDateTime > '2009-04-08 00:00:00' OR
cm.UA_Treatment.ExpirationDateTime IS NULL) AND (UAC_14270_vd.TreatmentLookup IS NOT NULL OR
(cm.UA_Treatment.CntrlTreatmtFlag = 0)) ) Cand ON UAC_14270_vd.InternalRowNumber = Cand.InternalRowNumber
WHERE (UAC_14270_vd.TreatmentCode IS NULL OR UAC_14270_vd.TreatmentLookup = Cand.TreatmentInstID) AND
(UAC_14270_vd.OfferCode1 IS NULL OR UAC_14270_vd.OfferLookup = Cand.OfferID) AND (UAC_14270_vd.CellCode IS
NULL OR UAC_14270_vd.CellLookup = Cand.CellID) AND (UAC_14270_vd.CampaignCode IS NULL OR
UAC_14270_vd.CampaignLookup = Cand.CampaignID) AND (UAC_14270_vd.TreatmentCode IS NOT NULL OR
UAC_14270_vd.OfferCode1 IS NOT NULL OR UAC_14270_vd.CellCode IS NOT NULL OR UAC_14270_vd.CampaignCode IS
NOT NULL) ) TInputs
LEFT OUTER JOIN ( SELECT UAC_14270_vd.InternalRowNumber,
CM.UA_CONTACTHISTORY.ContactDateTime, NULL TreatmentInstID, CM.UA_CONTACTHISTORY.PackageID,
CM.UA_CONTACTHISTORY.CellID
FROM UAC_14270_vd, CM.UA_CONTACTHISTORY
WHERE UAC_14270_vd.LEAD_ID = *CM.UA_CONTACTHISTORY.LEAD_ID* ) CInputs
ON (TInputs.InternalRowNumber = CInputs.InternalRowNumber) AND
((CInputs.PackageID IS NULL) OR (CInputs.PackageID = TInputs.PackageID)) AND ((CInputs.CellID IS NULL) OR
(CInputs.CellID = TInputs.CellID)) AND ((CInputs.TreatmentInstID IS NULL) OR (CInputs.TreatmentInstID
= TInputs.TreatmentInstID))
Plan hash value: 2746403946
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1285 (100)| | | |
| 1 | NESTED LOOPS OUTER | | 1364 | 372K| 1285 (7)| 00:00:08 | | |
| 2 | HASH JOIN | | 16 | 4304 | 1278 (7)| 00:00:08 | | |
| 3 | TABLE ACCESS FULL | UAC_14270_VD | 8498 | 1643K| 11 (10)| 00:00:01 | | |
| 4 | VIEW | | 1128K| 76M| 1254 (6)| 00:00:08 | | |
| 5 | SORT UNIQUE | | | | | | | |
| 6 | UNION-ALL | | | | | | | |
| 7 | HASH JOIN | | 8498 | 821K| 18 (6)| 00:00:01 | | |
| 8 | TABLE ACCESS FULL | UAC_14270_VD | 8587 | 218K| 10 (0)| 00:00:01 | | |
| 9 | TABLE ACCESS FULL | UA_TREATMENT | 4383 | 312K| 7 (0)| 00:00:01 | | |
| 10 | CONCATENATION | | | | | | | |
| 11 | HASH JOIN | | 14965 | 1636K| 10 (10)| 00:00:01 | | |
| 12 | TABLE ACCESS FULL | UAC_14270_VD | 328 | 12792 | 2 (0)| 00:00:01 | | |
| 13 | TABLE ACCESS FULL | UA_TREATMENT | 2192 | 156K| 7 (0)| 00:00:01 | | |
| 14 | HASH JOIN | | 748 | 83776 | 10 (10)| 00:00:01 | | |
| 15 | TABLE ACCESS FULL | UAC_14270_VD | 16 | 624 | 2 (0)| 00:00:01 | | |
| 16 | TABLE ACCESS FULL | UA_TREATMENT | 2192 | 156K| 7 (0)| 00:00:01 | | |
| 17 | CONCATENATION | | | | | | | |
| 18 | HASH JOIN | | 2258 | 246K| 10 (10)| 00:00:01 | | |
| 19 | TABLE ACCESS FULL | UAC_14270_VD | 328 | 12792 | 2 (0)| 00:00:01 | | |
| 20 | TABLE ACCESS FULL | UA_TREATMENT | 2192 | 156K| 7 (0)| 00:00:01 | | |
| 21 | HASH JOIN | | 113 | 12656 | 10 (10)| 00:00:01 | | |
| 22 | TABLE ACCESS FULL | UAC_14270_VD | 16 | 624 | 2 (0)| 00:00:01 | | |
| 23 | TABLE ACCESS FULL | UA_TREATMENT | 2192 | 156K| 7 (0)| 00:00:01 | | |
| 24 | CONCATENATION | | | | | | | |
| 25 | HASH JOIN | | 12775 | 1397K| 10 (10)| 00:00:01 | | |
| 26 | TABLE ACCESS FULL | UAC_14270_VD | 328 | 12792 | 2 (0)| 00:00:01 | | |
| 27 | TABLE ACCESS FULL | UA_TREATMENT | 2192 | 156K| 7 (0)| 00:00:01 | | |
| 28 | HASH JOIN | | 639 | 71568 | 10 (10)| 00:00:01 | | |
| 29 | TABLE ACCESS FULL | UAC_14270_VD | 16 | 624 | 2 (0)| 00:00:01 | | |
| 30 | TABLE ACCESS FULL | UA_TREATMENT | 2192 | 156K| 7 (0)| 00:00:01 | | |
| 31 | VIEW | | 86 | 946 | 0 (0)| | | |
| 32 | HASH JOIN | | 1 | 51 | 4157 (5)| 00:00:26 | | |
| 33 | TABLE ACCESS BY INDEX ROWID| UAC_14270_VD | 1 | 26 | 2 (0)| 00:00:01 | | |
| 34 | INDEX RANGE SCAN | UAC_14270_VD_I | 1 | | 1 (0)| 00:00:01 | | |
| 35 | PARTITION RANGE ALL | | 67 | 1675 | 4154 (5)| 00:00:26 | 1 | 13 |
| 36 | TABLE ACCESS FULL | UA_CONTACTHISTORY | 67 | 1675 | 4154 (5)| 00:00:26 | 1 | 13 |
--------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement