Skip to Main Content

Query Tuning question : How to avoid FTS on this table ?

C BoutetNov 6 2016 — edited Nov 7 2016

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

Comments
Post Details
Added on Nov 6 2016
13 comments
5,536 views