Adding a where clause makes the query considerably slower

user_3960032 0
Viewed 132

My Oracle SQL query is given below -

Q1-

select 
    hca.account_number,
    hca.attribute3,
    SUM(rcl.extended_amount)
from 
    ra_customer_trx_all rct 
    JOIN ra_customer_trx_lines_all rcl  
    ON rct.customer_trx_id = rcl.customer_trx_id
    JOIN HZ_CUST_ACCOUNTS_ALL  hca
    ON rct.bill_to_customer_id = hca.cust_account_id 
where
    rct.trx_date >= TO_DATE('01-MAR-2020', 'DD-MON-YYYY')
    AND 
    rct.trx_date < TO_DATE('03-MAR-2020', 'DD-MON-YYYY')
    AND
    rcl.line_type = 'LINE' 
    AND
    rct.org_id = 3523
    AND hca.account_number = '1063431'
group by 
    hca.account_number,
    hca.attribute3;

It seems pretty straight-forward but I cannot seem to understand why the above query runs a lot slower as compared to the exact same version of the query except I comment out the last where statement -

Q2 -

select 
    hca.account_number,
    hca.attribute3,
    SUM(rcl.extended_amount)
from 
    ra_customer_trx_all rct 
    JOIN ra_customer_trx_lines_all rcl  
    ON rct.customer_trx_id = rcl.customer_trx_id
    JOIN HZ_CUST_ACCOUNTS_ALL  hca
    ON rct.bill_to_customer_id = hca.cust_account_id 
where
    rct.trx_date >= TO_DATE('01-MAR-2020', 'DD-MON-YYYY')
    AND 
    rct.trx_date < TO_DATE('03-MAR-2020', 'DD-MON-YYYY')
    AND
    rcl.line_type = 'LINE' 
    AND
    rct.org_id = 3523
    -- AND hca.account_number = '1063431' ***THIS IS NOW COMMENTED***
group by 
    hca.account_number,
    hca.attribute3;

Q1 takes about 20 minutes whereas Q2 runs in 1 second.

I'm not really good at query tuning, but I have an index on account_number, made sure there's no implicit datatype conversions. The explain plan tells me the where clause would use a Index Unique Scan which I believe is the correct option. I would have assumed that adding a where clause should make it faster, as you are narrowing down the number of rows to be grouped. But it seems my assumption is incorrect, as I am observing the complete opposite of what I had assumed.

Explain plans given below -

Q1 (With Where Clause)

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                               |       |       |   245 (100)|          |
|   1 |  HASH GROUP BY                  |                               |     1 |    53 |   245   (1)| 00:00:01 |
|   2 |   NESTED LOOPS                  |                               |       |       |            |          |
|   3 |    NESTED LOOPS                 |                               |     1 |    53 |   245   (1)| 00:00:01 |
|   4 |     NESTED LOOPS                |                               |     1 |    38 |   240   (1)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| HZ_CUST_ACCOUNTS              |     1 |    15 |     2   (0)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN         | HZ_CUST_ACCOUNTS_U2           |     1 |       |     1   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| RA_CUSTOMER_TRX_ALL           |     1 |    23 |   238   (1)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN          | XXCCC_RA_CUSTOMER_TRX_ALL_N97 |  1353 |       |     8   (0)| 00:00:01 |
|*  9 |     INDEX RANGE SCAN            | RA_CUSTOMER_TRX_LINES_N2      |     2 |       |     3   (0)| 00:00:01 |
|* 10 |    TABLE ACCESS BY INDEX ROWID  | RA_CUSTOMER_TRX_LINES_ALL     |     1 |    15 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("HCA"."ACCOUNT_NUMBER"='1063431')
   7 - filter(("RCT"."TRX_DATE">=TO_DATE(' 2020-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "RCT"."ORG_ID"=3523 AND "RCT"."TRX_DATE"<TO_DATE(' 2020-03-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   8 - access("RCT"."BILL_TO_CUSTOMER_ID"="HCA"."CUST_ACCOUNT_ID")
   9 - access("RCT"."CUSTOMER_TRX_ID"="RCL"."CUSTOMER_TRX_ID")
  10 - filter("RCL"."LINE_TYPE"='LINE')

Q2 (Without Where Clause)

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                           |       |       |       |   137K(100)|          |
|   1 |  HASH GROUP BY                    |                           |    43 |  1892 |       |   137K  (1)| 00:00:11 |
|   2 |   NESTED LOOPS                    |                           |       |       |       |            |          |
|   3 |    NESTED LOOPS                   |                           | 34875 |  1498K|       |   137K  (1)| 00:00:11 |
|   4 |     VIEW                          | VW_GBF_9                  | 29570 |   837K|       |  4665   (1)| 00:00:01 |
|   5 |      HASH GROUP BY                |                           | 29570 |  1097K|  1400K|  4665   (1)| 00:00:01 |
|*  6 |       HASH JOIN                   |                           | 29570 |  1097K|       |  4369   (1)| 00:00:01 |
|*  7 |        TABLE ACCESS BY INDEX ROWID| RA_CUSTOMER_TRX_ALL       | 29570 |   664K|       |  3904   (1)| 00:00:01 |
|*  8 |         INDEX SKIP SCAN           | RA_CUSTOMER_TRX_N17       | 33634 |       |       |   189   (1)| 00:00:01 |
|   9 |        TABLE ACCESS FULL          | HZ_CUST_ACCOUNTS          | 43897 |   643K|       |   464   (1)| 00:00:01 |
|* 10 |     INDEX RANGE SCAN              | RA_CUSTOMER_TRX_LINES_N2  |     2 |       |       |     3   (0)| 00:00:01 |
|* 11 |    TABLE ACCESS BY INDEX ROWID    | RA_CUSTOMER_TRX_LINES_ALL |     1 |    15 |       |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("RCT"."BILL_TO_CUSTOMER_ID"="HCA"."CUST_ACCOUNT_ID")
   7 - filter("RCT"."ORG_ID"=3523)
   8 - access("RCT"."TRX_DATE">=TO_DATE(' 2020-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "RCT"."TRX_DATE"<TO_DATE(' 2020-03-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter(("RCT"."TRX_DATE">=TO_DATE(' 2020-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "RCT"."TRX_DATE"<TO_DATE(' 2020-03-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
  10 - access("ITEM_1"="RCL"."CUSTOMER_TRX_ID")
  11 - filter("RCL"."LINE_TYPE"='LINE')

EDIT: Sorry, added images of execution plans below.

Q1 Q2

oracle sql performance
1 Answers

First of all, from the explain plan we can see that table statistics are not actual. Please gather table statistics. Secondly, you need to show real execution plans with execution statistics, not just explain plan.

The main problem here is that CBO gets wrong cardinality for line #9 of the first plan:

|*  8 |       INDEX RANGE SCAN          | XXCCC_RA_CUSTOMER_TRX_ALL_N97 |  1353 |       |     8   (0)| 00:00:01 |

   8 - access("RCT"."BILL_TO_CUSTOMER_ID"="HCA"."CUST_ACCOUNT_ID")

As you can see it gets ROWIDs from the index by BILL_TO_CUSTOMER_ID="HCA"."CUST_ACCOUNT_ID" and then gets rows from the table RA_CUSTOMER_TRX_ALL by those ROWIDs and filter them by dates:

|*  7 |      TABLE ACCESS BY INDEX ROWID| RA_CUSTOMER_TRX_ALL           |     1 |    23 |   238   (1)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN          | XXCCC_RA_CUSTOMER_TRX_ALL_N97 |  1353 |       |     8   (0)| 00:00:01 |

Predicate Information (identified by operation id):
---------------------------------------------------
 
   7 - filter(("RCT"."TRX_DATE">=TO_DATE(' 2020-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "RCT"."ORG_ID"=3523 AND "RCT"."TRX_DATE"<TO_DATE(' 2020-03-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
   8 - access("RCT"."BILL_TO_CUSTOMER_ID"="HCA"."CUST_ACCOUNT_ID")

So looks like you get huge amount of rows that satisfy condition by BILL_TO_CUSTOMER_ID and very small amount of rows by "TRX_DATE" between date'2020-03-01' and date'2020-03-03'.

While second plan shows that it gets at first rows using INDEX_SS by dates.

So you have the following options:

  1. You need to gather/fix statistics, so CBO could get correct cardinality to build plans; (optimal)

  2. You can add hint dynamic_sampling so oracle could get correct cardinality during parsing; (1st options is better)

  3. (optimal) You can add an index either on ra_customer_trx_all(BILL_TO_CUSTOMER_ID, TRX_DATE) or in opposite order ra_customer_trx_all(TRX_DATE, BILL_TO_CUSTOMER_ID). You need to analyze which one is the best in your circumstances (ie consider other queries, operations, etc). Usually it's better to reduce number of ISS (Index Skip Scans), since it's pretty hard operation. So in case of the index by (BILL_TO_CUSTOMER_ID, TRX_DATE) CBO could easily get those small number of needed rows using this index.

  4. You can force CBO to use similar plan for the first query using hint or sql profiles or sql baselines, for example using the following hints:

select --+ leading(hca rct rcl) use_hash(rct) index(rct) use_nl(rcl)
    hca.account_number,
    hca.attribute3,
    SUM(rcl.extended_amount)
from 
    ra_customer_trx_all rct 
    JOIN ra_customer_trx_lines_all rcl  
         ON rct.customer_trx_id = rcl.customer_trx_id
    JOIN HZ_CUST_ACCOUNTS_ALL  hca
         ON rct.bill_to_customer_id = hca.cust_account_id 
where
    rct.trx_date >= TO_DATE('01-MAR-2020', 'DD-MON-YYYY')
AND rct.trx_date < TO_DATE('03-MAR-2020', 'DD-MON-YYYY')
AND rct.org_id = 3523

AND rcl.line_type = 'LINE' 

AND hca.account_number = '1063431'
group by 
    hca.account_number,
    hca.attribute3;

4th option is just a workaround if you can't go with the 1st option.

Update

Also very important thing (and probably most important in your case): in case of big data skew (which is pretty often thing in case of number of transactions per account) you can gather histograms, but they can't help in case of join predicates, ie histograms might help you in case of select * from t where skewed_col=literal or select * from t where skewed_col=:bind (with bind variable peeking and/or adaptive cursor sharing), but not in case of

select * 
from x, t 
where x.id = literal
and t.skewed_col = x.col

In such cases CBO can't use histograms since it doesn't know exact value and takes average selectivity.

You can easily check how your data is skewed:

select *
from (
    select 
        v.*,
        dense_rank()over(order by cnt asc) min_n,
        dense_rank()over(order by cnt desc) max_n
    from (
        select 
            bill_to_customer_id,
            count(*) cnt
        from ra_customer_trx_all rct 
        group by bill_to_customer_id
        ) v
    )
where min_n <=10 or max_n >=10;

And you will see top 10 most and least popular bill_to_customer_id. Still option 3 (index on (bill_to_customer_id,trx_date) will help you even in case of skewed data.

There is also another way, but it's not supported by oracle, so just FYI:

select 
    hca.account_number,
    hca.attribute3,
    SUM(rcl.extended_amount)
from 
    ra_customer_trx_all rct 
    JOIN ra_customer_trx_lines_all rcl  
    ON rct.customer_trx_id = rcl.customer_trx_id
    JOIN HZ_CUST_ACCOUNTS_ALL  hca
    ON rct.bill_to_customer_id = hca.cust_account_id 
where
    rct.trx_date >= TO_DATE('01-MAR-2020', 'DD-MON-YYYY')
    AND 
    rct.trx_date < TO_DATE('03-MAR-2020', 'DD-MON-YYYY')
    AND
    rcl.line_type = 'LINE' 
    AND
    rct.org_id = 3523
    AND hca.account_number = '1063431'
    -- added: *** don't use in production!
    AND rct.bill_to_customer_id in 
        (select/*+ precompute_subquery */ 
            h.cust_account_id 
        from HZ_CUST_ACCOUNTS_ALL h 
        where h.account_number='1063431')
group by 
    hca.account_number,
    hca.attribute3;

As you can see I've added bill_to_customer_id in (select...) with undocumented hint precompute_subquery, which forces Oracle to replace that subquery with the "precomputed" values, ie oracle executes that subquery and substitutes subquery with them, so CBO now can use histograms to estimate cardinality correctly. But it's not-supported by Oracle, so you can play with it, but don't use it in production without approval from Oracle support.

user_4291000
Related