DB Version: 9.2
OS : AIX 5.3
Below query currently takes 10 minutes to execute. It returns rougly 450 rows.Business want this query to complete much faster.
From the below execution plan, do you know which step is taking more time ? I want to know what is causing Full Table Scan for PDTCOST_CHARGE_MAP table in step2 shown in red below
PDTCOST_CHARGE_MAP is a non-partitioned table which is only 2 GB in size and it has 30 Million records .
SQL> select count(*) from PDTCOST_CHARGE_MAP;
COUNT(*)
----------
30155624
Step2 of predicate section in the execution plan shows FTS
access("P"."TRACKING_ID"="PCM"."TRACKING_ID" AND "P"."TRACKING_ID_SERV"="PCM"."TRACKING_ID_SERV")
filter("P"."BILLING_INACTIVE_DT" IS NULL AND "PCM"."INACTIVE_DT" IS NULL OR "PCM"."ACTIVE_DT"= (SELECT /*+ */MAX("PCM1"."ACTIVE_DT") FROM "PDTCOST_CHARGE_MAP" "PCM1"))
But, PDTCOST_CHARGE_MAP_PK index has both TRACKING_ID and TRACKING_ID_SERV in it in the correct order.
And an index named PROD_CHG_MAP_TRACKING has TRACKING_ID, TRACKING_ID_SERV, INACTIVE_DT and BILLED_THRU_DT in the right order. Column position shown at bottom of this post (user_ind_columns output). Despite having these 2 indexes, why is the optimizer not choosing neither of these indexes ? Stats are up-to-date in PDTCOST_CHARGE_MAP table.
Although this is just a 2GB table, I would like optimizer to use index for step2. Any idea how I can do this? Any other recommendations welcome too.
--- Info on other table involved
JDL_WORK_LIST is a small table with a size of 120 MB with 300,000 records
BILL_INVOICE is a partitoed table 26 GB in size .
BILL_INVOICE_DETAIL is a partitioned table which is 27 GB in size
---- Below is the query and its execution plan
SELECT JWL.ACCOUNT_NO,
JWL.BILL_REF_NO,
JWL.BILL_REF_RESETS,
JWL.ACCOUNT_CATEGORY,
JWL.MKT_CODE,
JWL.CURRENCY_CODE,
JWL.TO_DATE,
JWL.STATEMENT_DATE,
JWL.OWNING_COST_CTR,
JWL.JNL_STATUS,
JWL.JNL_EARNED_THRU_DT,
BID.BILLING_LEVEL,
BID.TRACKING_DATE,
BID.TRACKING_ID,
BID.TRACKING_ID_SERV,
BID.SUBSCR_NO,
BID.SUBSCR_NO_RESETS,
BID.SUBTYPE_CODE,
BID.FROM_DATE,
BID.TO_DATE,
BID.GEOCODE,
BID.DISCOUNT,
BID.AMOUNT,
BID.PROVIDER_ID,
BID.REV_RCV_COST_CTR,
BID.B_REV_RCV_COST_CTR,
S.EMF_CONFIG_ID,
BID.TAX_TYPE_CODE,
BID.OPEN_ITEM_ID,
P.BILLING_INACTIVE_DT,
PCM.BILLED_THRU_DT,
BI.BACKOUT_STATUS,
BID.AMOUNT_REDUCTION
FROM SERVICE S,
PDTCOST P,
PDTCOST_CHARGE_MAP PCM,
BILL_INVOICE_DETAIL BID,
BILL_INVOICE BI,
JDL_WORK_LIST JWL
WHERE JWL.BILL_REF_NO = BID.BILL_REF_NO
AND JWL.BILL_REF_RESETS = BID.BILL_REF_RESETS
AND JWL.BILL_REF_NO = BI.BILL_REF_NO
AND JWL.BILL_REF_RESETS = BI.BILL_REF_RESETS
AND BID.TYPE_CODE = 4
AND BID.AMOUNT != 0
AND BID.BILLING_LEVEL = 1
AND BID.TRACKING_ID = P.TRACKING_ID
AND BID.TRACKING_ID_SERV = P.TRACKING_ID_SERV
AND BID.SUBSCR_NO = S.SUBSCR_NO
AND BID.SUBSCR_NO_RESETS = S.SUBSCR_NO_RESETS
AND P.TRACKING_ID = PCM.TRACKING_ID
AND P.TRACKING_ID_SERV = PCM.TRACKING_ID_SERV
AND ( (P.BILLING_INACTIVE_DT IS NULL AND PCM.INACTIVE_DT IS NULL)
OR (PCM.ACTIVE_DT =
(SELECT MAX (ACTIVE_DT) FROM PDTCOST_CHARGE_MAP PCM1 )));
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 137K| 27M| | 134K| | |
|* 1 | HASH JOIN | | 137K| 27M| 27M| 134K| | |
|* 2 | HASH JOIN | | 140K| 26M| 1293M| 133K| | |
| 3 | TABLE ACCESS FULL | PDTCOST_CHARGE_MAP | 30M| 948M| | 24044 | | |
|* 4 | HASH JOIN | | 11M| 1837M| 810M| 57206 | | |
| 5 | INDEX FAST FULL SCAN | PDTCOST_BILL_INV_TRACK | 29M| 475M| | 16107 | | |
|* 6 | TABLE ACCESS BY LOCAL INDEX ROWID | BILL_INVOICE_DETAIL | 5840K| 478M| | 2 | | |
| 7 | NESTED LOOPS | | 11M| 1634M| | 6 | | |
| 8 | NESTED LOOPS | | 2 | 120 | | 3 | | |
| 9 | TABLE ACCESS FULL | JDL_WORK_LIST | 2 | 96 | | 2 | | |
| 10 | PARTITION RANGE ITERATOR | | | | | | KEY | KEY |
| 11 | TABLE ACCESS BY LOCAL INDEX ROWID| BILL_INVOICE | 1 | 12 | | 1 | KEY | KEY |
|* 12 | INDEX UNIQUE SCAN | BILL_INVOICE_XSUM_BILL_REF_NO | 1 | | | | KEY | KEY |
| 13 | PARTITION RANGE ITERATOR | | | | | | KEY | KEY |
|* 14 | INDEX RANGE SCAN | BILL_INVOICE_DETAIL_PK | 32 | | | 1 | KEY | KEY |
| 15 | SORT AGGREGATE | | 1 | 8 | | | | |
| 16 | INDEX FAST FULL SCAN | PDTCOST_CHARGE_MAP_PK | 30M| 229M| | 17498 | | |
| 17 | INDEX FAST FULL SCAN | SERVICE_EMF_CONF_SUBSCR | 1660K| 19M| | 575 | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("BID"."SUBSCR_NO"="S"."SUBSCR_NO" AND "BID"."SUBSCR_NO_RESETS"="S"."SUBSCR_NO_RESETS")
2 - access("P"."TRACKING_ID"="PCM"."TRACKING_ID" AND "P"."TRACKING_ID_SERV"="PCM"."TRACKING_ID_SERV")
filter("P"."BILLING_INACTIVE_DT" IS NULL AND "PCM"."INACTIVE_DT" IS NULL OR "PCM"."ACTIVE_DT"= (SELECT /*+ */
MAX("PCM1"."ACTIVE_DT") FROM "PDTCOST_CHARGE_MAP" "PCM1"))
4 - access("BID"."TRACKING_ID"="P"."TRACKING_ID" AND "BID"."TRACKING_ID_SERV"="P"."TRACKING_ID_SERV")
6 - filter("BID"."TYPE_CODE"=4 AND "BID"."AMOUNT"<>0 AND "BID"."BILLING_LEVEL"=1)
12 - access("JWL"."BILL_REF_NO"="BI"."BILL_REF_NO" AND "JWL"."BILL_REF_RESETS"="BI"."BILL_REF_RESETS")
14 - access("JWL"."BILL_REF_NO"="BID"."BILL_REF_NO" AND "JWL"."BILL_REF_RESETS"="BID"."BILL_REF_RESETS")
Note: cpu costing is off
37 rows selected.
Since I thought MAX (ACTIVE_DT) towards the end of the query is causing the FTS , I generated execution plan of just.But, It is using Index.
SQL> explain plan for select MAX (ACTIVE_DT) FROM PDTCOST_CHARGE_MAP;
Explained.
Elapsed: 00:00:00.01
SQL> set linesize 10000 pagesize 300
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 17498 |
| 1 | SORT AGGREGATE | | 1 | 8 | |
| 2 | INDEX FAST FULL SCAN| PDTCOST_CHARGE_MAP_PK | 30M| 229M| 17498 |
--------------------------------------------------------------------------------
-- MAX query finishes in 59 seconds
SQL> select MAX (ACTIVE_DT) FROM PDTCOST_CHARGE_MAP;
MAX(ACTIVE_DT)
-----------------
06-NOV-2016 00:00
Elapsed: 00:00:59.36
--- Additional info primary keys, Index columns
Primary Key of PDTCOST_CHARGE_MAP table:
SQL> select COLUMN_NAME,COLUMN_POSITION from user_ind_columns where index_name='PDTCOST_CHARGE_MAP_PK' order by 2 asc;
COLUMN_NAME COLUMN_POSITION
------------------------- ---------------
TRACKING_ID 1
TRACKING_ID_SERV 2
ACTIVE_DT 3
Indexes on PDTCOST_CHARGE_MAP table:
SQL> select index_name,COLUMN_NAME,COLUMN_POSITION from user_ind_columns where table_name='PDTCOST_CHARGE_MAP' ORDER BY 1,3 ASC;
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------- ---------------
PRDCT_CHRG_MP_XPCM_BLL_CC_N BILLING_ACCOUNT_NO 1
PRDCT_CHRG_MP_XPCM_PR_CC_N PARENT_ACCOUNT_NO 1
PDTCOST_CHARGE_MAP_PK TRACKING_ID 1
PDTCOST_CHARGE_MAP_PK TRACKING_ID_SERV 2
PDTCOST_CHARGE_MAP_PK ACTIVE_DT 3
PROD_CHG_MAP_TRACKING TRACKING_ID 1
PROD_CHG_MAP_TRACKING TRACKING_ID_SERV 2
PROD_CHG_MAP_TRACKING INACTIVE_DT 3
PROD_CHG_MAP_TRACKING BILLED_THRU_DT 4
9 rows selected.
-- All indexes are valid in PDTCOST_CHARGE_MAP Table
SQL> select INDEX_NAME, status from user_indexes where table_name = 'PDTCOST_CHARGE_MAP';
INDEX_NAME STATUS
------------------------------ --------
PRDCT_CHRG_MP_XPCM_BLL_CC_N VALID
PRDCT_CHRG_MP_XPCM_PR_CC_N VALID
PDTCOST_CHARGE_MAP_PK VALID
PROD_CHG_MAP_TRACKING VALID
Primary Key of PDTCOST table:
SQL> select COLUMN_NAME,COLUMN_POSITION from user_ind_columns where index_name='PDTCOST_PK' order by 2 asc;
COLUMN_NAME COLUMN_POSITION
------------------------- ---------------
TRACKING_ID 1
TRACKING_ID_SERV 2
--Indexes on PDTCOST table
SQL> select index_name,COLUMN_NAME,COLUMN_POSITION from user_ind_columns where table_name='PDTCOST' ORDER BY 1,3 ASC;
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ ------------------------- ---------------
PDTCOST_BILL_INV_TRACK BILLING_INACTIVE_DT 1
PDTCOST_BILL_INV_TRACK TRACKING_ID 2
PDTCOST_BILL_INV_TRACK TRACKING_ID_SERV 3
PDTCOST_PK TRACKING_ID 1
PDTCOST_PK TRACKING_ID_SERV 2
PDTCOST_XP_BILLING_ACC_NO BILLING_ACCOUNT_NO 1
PDTCOST_XP_COMPONENT_ID COMPONENT_ID 1
PDTCOST_XP_CONT_TRACK_ID CONTRACT_TRACKING_ID 1
PDTCOST_XP_CONT_TRACK_ID CONTRACT_TRACKING_ID_SERV 2
PDTCOST_XP_ELEMENT_ID ELEMENT_ID 1
PDTCOST_XP_PAR_ACC_NO PARENT_ACCOUNT_NO 1
PDTCOST_XP_SUBSCR_NO PARENT_SUBSCR_NO 1
PDTCOST_XP_SUBSCR_NO PARENT_SUBSCR_NO_RESETS 2
PDTCOST_XP_VIEW_ID_FK VIEW_ID 1
14 rows selected.
Message was edited by: C. Boutet Added info on PROD_CHG_MAP_TRACKING index