Inefficient query plan using large in-list

From: Senthil Subramanian <skumar.sen_at_gmail.com>
Date: Thu, 16 May 2019 11:43:23 -0400
Message-ID: <CAFUEfmGTZdR5gKHvOPbB_mqrDxC+4ZYtymE=CbdzOG_xwS1crw_at_mail.gmail.com>



Hello list members,

I would like get some help to solve a performance issue. I have a query with a simple view which needs to return rows when application passes comma separated input values. The view goes against an MVIEW with an analytic (LAG) function to compute the expiration date using an effective date for a given security ID & src. Below is the view definition:

CREATE OR REPLACE FORCE VIEW SECMASTER_HISTORY AS

SELECT security_alias,

       src_intfc_inst,

       effective_date,

       (effective_date) over (partition by security_alias,src_intfc_inst order by effective_date desc) - interval '1' second expiration_date,

       <other cols>

FROM SECMASTER_HISTORY_MV WHERE src_intfc_inst = 4;

There is an unique index on the MV with columns (SECURITY_ALIAS,SRC_INTFC_INST,EFFECTIVE_DATE) When I run the below query

select sh.*

from table(cast(multiset(select level from dual

                           connect by level <= length
(regexp_replace(:in_list, '[^,]+')) + 1) as sys.OdciNumberList)) xt,

       secmaster_history sh

where sh.security_alias = xt.column_value

and to_date('15-MAY-2010') between effective_date and nvl(expiration_date,sysdate) ;

The optimizer is not pushing the predicate into the view and does a full scan on the MV causing performance issue!


| Id | Operation | Name | Rows
| Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |IN-OUT|


| 0 | SELECT STATEMENT | |
 387K| 1653M| | 17M (1)| 00:11:12 | | | |

| 1 | MERGE JOIN | |
 387K| 1653M| | 17M (1)| 00:11:12 | | | |

|*  2 |   VIEW                               | SECMASTER_HISTORY    |
47M|   199G|       |    17M  (1)| 00:11:12 |      |        |      |


| 3 | WINDOW SORT | |
47M| 71G| 91G| 17M (1)| 00:11:12 | | | |
| 4 | PARTITION RANGE ALL | |
47M| 71G| | 1209K (1)| 00:00:48 | 1 |1048575| | |* 5 | MAT_VIEW ACCESS FULL | SECMASTER_HISTORY_MV | 47M| 71G| | 1209K (1)| 00:00:48 | 1 |1048575| | |* 6 | SORT JOIN | | 8168
| 16336 | | 30 (4)| 00:00:01 | | | |

| 7 | COLLECTION ITERATOR SUBQUERY FETCH| | 8168
| 16336 | | 29 (0)| 00:00:01 | | | |
|* 8 | CONNECT BY WITHOUT FILTERING | | | | | | | | | PCWP |
| 9 | FAST DUAL | | 1
| | | 2 (0)| 00:00:01 | | | PCWP |
--------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):


   2 - filter("EFFECTIVE_DATE"<=TO_DATE('15-MAY-2010') AND NVL("EXPIRATION_DATE",SYSDATE_at_!)>=TO_DATE('15-MAY-2010'))

   5 - filter("SRC_INTFC_INST"=4)

   6 - access("SH"."SECURITY_ALIAS"=VALUE(KOKBF$))

       filter("SH"."SECURITY_ALIAS"=VALUE(KOKBF$))

   8 - filter(LEVEL<=LENGTH( REGEXP_REPLACE (:IN_LIST,'[^,]+'))+1)

25 rows selected.

I tried to hint the optimizer but nothing works :-(

Any help is highly appreciated!

Thanks in advance for your help!

Senthil

--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 16 2019 - 17:43:23 CEST

Original text of this message