Home -> Mailing Lists -> Oracle-L -> Re: Inefficient query plan using large in-list

Re: Inefficient query plan using large in-list

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 16 May 2019 16:21:56 +0000
Message-ID: <LO2P265MB0415C450123F47C520DA8AF6A50A0_at_LO2P265MB0415.GBRP265.PROD.OUTLOOK.COM> 







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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Senthil Subramanian <skumar.sen_at_gmail.com>
Sent: 16 May 2019 16:43


To: oracle-l_at_freelists.org


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_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 - 18:21:56 CEST












Original text of this message