Re: Inefficient query plan using large in-list

  • From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 16 May 2019 16:21:56 +0000


As others have noted, table() functions don't mix well with the optimizer and 
various transformation may fail to appear for reasons that are not obvious to 
mere mortals such as us.

Which version of Oracle, though, and have you demonstrated that a query like 
the following behaves the way you want because sometimes the presence of 
analytic functions blocks the query transformation you expect to see:

select
  {columns}
from secmaster_history
where
where  security_alias in (1,2,3,4,5)
and    to_date('15-MAY-2010') between effective_date and 
nvl(expiration_date,sysdate) 


Regards
Jonathan Lewis

________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf 
of Senthil Subramanian <skumar.sen@xxxxxxxxx>
Sent: 16 May 2019 16:43
To: oracle-l@xxxxxxxxxxxxx
Subject: Inefficient query plan using large in-list

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@!)>=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
--
//www.freelists.org/webpage/oracle-l


Other related posts: