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

C. Boutet wrote:DB Version: 9.2OS : AIX 5.3Below 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 belowStep2 of predicate section in the execution plan shows FTSBut, PDTCOST_CHARGE_MAP_PK index has both TRACKING_ID and TRACKING_ID_SERV in it in the correct order. Then why is the optimizer not choosing PDTCOST_CHARGE_MAP_PK index ? Stats are up-to-date in PDTCOST_CHARGE_MAP table.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.

Step 2 is a HASH JOIN in your execution plan, it is step 3 that is the Full Table Scan.

Assuming your indexes are okay (I don't have time to look at them all fully), the thing that strikes me is that the previous step in the execution plan - 4 HASH JOIN - is estimated to produce 11 million rows.  This is then joined to your 30 million row table.  Assuming at least a 1 to 1 row match on the join, then that is at least one third of the rows in the PDTCOST_CHARGE_MAP, and for that high volume of rows in the table Oracle is preferring the Full Table Scan i.e. it believes that most of the rows in the table will be used, so it is "cheaper" to read all of the data blocks of the table using multi-block reads then using an index and doing single row / block look up reads.

Maybe you should be looking at why step 7 NESTED LOOPS is estiimated to produce 11 million rows.  If this can be reduced somehow then the Optimizer may end up choosing the index for the final join.

EDITED: Corrected the reference to be to step 7 from the original mistype of 11.

First, Yes, I meant operation 7 not 11 in my previous reply, as Jonathan pointed out.  My fingers must have been ahead of my brain when I was typing that in, as I did it very quickly.

Your query joins 6 tables together:

FROM SERVICE S,        PDTCOST P,       PDTCOST_CHARGE_MAP PCM,       BILL_INVOICE_DETAIL BID,       BILL_INVOICE BI,       JDL_WORK_LIST JWL 

You have only told us details about the PCM table, at 30 million rows and 2 GB in size.  What about the other 5 tables?  They all form part of the query and impact the execution plan produced by the Optimizer.  How many rows do each of them have, and what indexes?  This is the kind of information the Optimizer is using when it produces the execution plan.  If we don't know as much as the Optimizer does then it is unlikely that we can produce a better execution plan for you.

You only have 2 real filter conditions on tables, BID and PCM, the other conditions are all joins:

AND BID.TYPE_CODE = 4  AND BID.AMOUNT != 0  AND BID.BILLING_LEVEL = 1  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 )));  

As Jonathan has pointed out there are complications involved with the last filter because it has an OR in it, and mixes different columns from different tables in each part.  And as Jonathan says you might be better off doing it as 2 separate queries and a UNION ALL of all of the matching rows, and getting each query to run efficiently.

We don't know how selective the filters on BID are - how many rows out of the total in the table will match those conditions?  From the names of the columns and the values used I would guess that these are not very selective, because those columns contain very few distinct values each, and are probably Indicator or Type data columns.  If this is true then an index on these columns may not help, as too many rows are expected to meet the condition.  The Optimizer will only use an index when the total cost of access is below that of a Full Table Scan, and the main factor affecting this is the number of matching rows, though there are other factors too such as the clustering factor of the index.

I'd also say that your condition on PCM.ACTIVE_DT uses a sub-query on the same table PCM again which has no WHERE clause in it i.e. the sub-query is not correlated and will produce one and only one value for the whole table.  To make the MAX and the join to it run quicker I would be looking at creating an index on PCM (ACTIVE_DT) i.e. the one column only, or at least an index with that as the leading, first column.  This would mean that the sub-query for the MAX can run quicker, and the join back on ACTIVE_DT can run quickly too.

The logical way to execute this query would be to start with BID, applying the filters to it, then joining out to the other tables.  Unfortunately although the execution plan does seem to start on BID (operation 14 INDEX RANGE SCAN on its PK) it does not apply the filter conditions until step 6, which is much later.  That is probably why there are 11 million rows estimated for step 7, before the filters are applied in step 6.  Note that these filters only reduce the row count estimate from 11M to 5.8M, which is only really halving the row count i.e. Oracle is estimating that half of the rows in the BID table will match the filter conditions on it in the query.

Depending on how many rows are in the other tables, which we don't know yet, and how selective the filters on BID are, which we also don't know, I would consider the following indexes if they don't already exist.

BID (TYPE_CODE, BILLING_LEVEL, AMOUNT, BILL_REF_NO, BILL_REF_RESETS, TRACKING_ID, TRACKING_ID_SERV, SUBSCR_NO, SUBSCR_NO_RESETS)

JWL (BILL_REF_NO, BILL_REF_RESETS)

BI (BILL_REF_NO, BILL_REF_RESETS)

P (TRACKING_ID, TRACKING_SERV_NO, BILLING_INACTIVE_DT)

PCM (TRACKING_ID, TRACKING_SERV_NO, INACTIVE_DT)

S (SUBSCR_NO, SUBSCR_NO_RESETS)

This would potentially let the Optimizer start on BID and then join out to the other tables using indexes.  But it would only do this if the filters on BID were selective enough - if too many rows are estimated to match in BID then it would do a Full Table Scan on BID and join out to other tables instead.  Which means you would end up with a similar execution plan to the one you currently have, and the row count estimates during the execution itself would still be high because the filters do not reduce the row count that much.

An alternative index to try on PCM as well would be on (ACTIVE_DT, TRACKING_ID, TRACKING_SERV_NO).  Potentially the execution plan could start on PCM instead and join out to the other tables.  But as Jonathan as pointed out, the filter on PCM.ACTIVE_DT is buried within an OR that references other columns from PCM and P.  And you are using a very old version of Oracle, and the Optimizer is probably not up to being able to handle both conditions in the OR simultaneously with a single, simple access to those tables.  So you could try such an index, but its benefit would be limited.

And another index to try would be on PCM (BILLING_INACTIVE_DT, TRACKING_ID, TRACKING_SERV_NO), assuming that TRACKING_ID and TRACKING_SERV_NO do not allow NULL values to be stored in them.  This would mean that such an index would actually contain entries for when BILLING_ACTIVE_DT is NULL, as the other 2 columns would always have a data value for each row.

If you could somehow simplify that last WHERE condition with the OR in the middle, then the Optimizer might be able to produce a better execution plan.  But you are also limited by how selective your filters are (how many or few rows in BID or PCM match those conditions), and how big the other tables are.

In principle one step you will have to take is to write the query as a UNION ALL of two very similar queries, splitting the last two bits of your predicate into different pieces.

In the first half of the query you should have:

AND (P.BILLING_INACTIVE_DT IS NULL AND PCM.INACTIVE_DT IS NULL) 

In the second half you should have

AND (PCM.ACTIVE_DT =  (SELECT  MAX (ACTIVE_DT)   FROM PDTCOST_CHARGE_MAP PCM1 ))

AND (P.BILLING_INACTIVE_DT IS NOT NULL OR PCM.INACTIVE_DT IS NOT NULL)                 -- note that the AND in the middle here has changed to an OR

This will make it possible for the optimizer to do something efficient, but if the statistics still make the optimizer think its handling a large number of rows then this change on its own may not be enough.

Regards

Jonathan Lewis

Some good points to take on board by the others. No one seems to have mentioned the statistics on JDL_WORK_LIST though. You say:

"JDL_WORK_LIST is a small table with a size of 120 MB with 300,000 records"

But Oracle thinks that it has a cost of 2 to full table scan and it will return 2 rows. Oracle has chosen to begin the execution from this table on the grounds that it will only find 2 rows, if it realises it will return 300,000 rows then perhaps a different leading table will be chosen.

As you're on 9.2 you can enable row source execution statistics so you can actually determine where the time is going - currently you are guessing it's the full table scan, that might be correct but it also might be due to reads/writes with temp, it might be due to an aggressive nested loop. https://blogs.oracle.com/optimizer/entry/how_do_i_know_if  Explains how to get this information.

Looking at your query, there doesn't strike me as being a leading predicate to use, but I don't know the data. You know the data better than us, where would you expect the execution plan to lead from? How often do you expect to execute the query - it does look like something you might only run once a day, could you just kick off the execution 10 minutes earlier?

Jonathan,

QQ - Why would Optimizer doesn't performed un-nesting in this case ? I mean as per your blog info, it may tried to re-written the query. I was trying to find out why it would have failed.

I hope its not 11.2 (perhaps 9.2)

- Pavan Kumar N