Adding a where clause makes the query considerably slower

Question

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.

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` — Sayan Malakshinov, Sep 11 '20 at 17:33
Please prepend the column names with the table they belong to. We can't really guess where `trx_date` and `account_number` come from. — The Impaler, Sep 11 '20 at 17:37
Have you tried using the index hint to use index HZ_CUST_ACCOUNTS_U2 in 2nd query. — Atif, Sep 11 '20 at 17:45
You are using Function in where class, which is the main reason for slowness of you query . Kindly Remove the function from where class and store it in a variable then use the variable in where class.You will see your query run faster. here is the link for it https://stackoverflow.com/questions/3564283/how-to-declare-variable-and-use-it-in-the-same-oracle-sql-script — Ninja, Sep 11 '20 at 17:55
@MHanif expressions like `TO_DATE('01-MAR-2020','DD-MON-YYYY')` are literals. They could be written more concisely as `date '2020-03-01'` but they come to the same thing. Replacing them with variables won't necessarily help. — William Robertson, Sep 11 '20 at 18:26
@TheImpaler VW_GBF just means that CBO rewrote original query using "group-by placement" transformation. — Sayan Malakshinov, Sep 11 '20 at 19:44
@SayanMalakshinov - I apologize, I am new performance tuning. I have attached image of the auto-trace result, which I believe gives you the entire execution statistics. — TheEspada, Sep 11 '20 at 22:22
@TheImpaler - I have made the edit to add table-name, sorry about that. — TheEspada, Sep 11 '20 at 22:23
@TheEspada just compare "cardinality" column with "last_output_rows": as you can see my answer was absolutely correct - instead of 1353 estimated rows, you got 23.5mln rows by account. I'd recommend to implement 1st and 3rd options. — Sayan Malakshinov, Sep 11 '20 at 23:57
@WilliamRobertsonThanks for the correction, what i was saying that we should avoid using functions in where class — Ninja, Sep 12 '20 at 19:04
@SayanMalakshinov. The cardinality estimate is actually quite close. The estimate is 1353 *per execution*. It's being executed about 18800 times. 1353 x 18800 is just over 25M — BobC, Sep 13 '20 at 00:17
Gettin an Active SQL Monitor report is going to be the best tool for diagnosing SQL performance issues. — BobC, Sep 13 '20 at 00:24
@BobC you're reading it wrong: there was just 1 start there, since leading table HR_cust_accounts returned just 1 row. 18800 is a number of rows returned from the table by ROWIDs from that index and filtered by filter predicates. — Sayan Malakshinov, Sep 13 '20 at 01:39
RTSM is a good tool, but this issue is obvious and real execution plan is enough here. — Sayan Malakshinov, Sep 13 '20 at 01:51
@SayanMalakshinov, yes HR_cust_accounts retrieves 1 row, but the row source above that NL is the one responsible for the 18k rows ( the access to RA_CUSTOMER_TRX_LINES_ALL). That is where the number of executions is coming from. — BobC, Sep 13 '20 at 04:04
@BobC, you're wrong here. How NL works: for each row returned from first child row source it executes second child operation. First row source is HR_cust_accounts and it returns 1 row, so nested loop executes 'index range scan XXCCC_RA_CUSTOMER_TRX_ALL_N97' and 'table access by rowid RA_CUSTOMER_TRX_ALL' just once. Read more about this here: "11.3.3.1 Original and New Implementation for Nested Loop Joins in 11g" https://docs.oracle.com/cd/E25178_01/server.1111/e16638/optimops.htm#i49732 — Sayan Malakshinov, Sep 13 '20 at 08:47
@BobC 'index range scan XXCCC_RA_CUSTOMER_TRX_ALL_N97' returns 23.5 mln ROWIDs, so 'table access by rowid RA_CUSTOMER_TRX_ALL' has to check all those 23.5mln rows and after the filter it returns 18800 rows. That's why you see the same 18800 rows in last_output_rows of that nested loop — Sayan Malakshinov, Sep 13 '20 at 08:52

Sayan Malakshinov · Accepted Answer · 2020-09-12T00:40:27.657

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:

You need to gather/fix statistics, so CBO could get correct cardinality to build plans; (optimal)
You can add hint dynamic_sampling so oracle could get correct cardinality during parsing; (1st options is better)
(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.
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.

for my understanding , how do you say the cardinality is wrong ? What is your base here to identify it ? — Sujitmohanty30, Sep 11 '20 at 21:25

Adding a where clause makes the query considerably slower

1 Answers1