Re: Inefficient query plan using large in-list

Hi Sayan,

I already tried options 1, 2 and 5 with no luck. Let me try 3 & 4. If these options don't work, I'll then go with option 1.

Thanks for your help!

On Thu, May 16, 2019 at 12:53 PM S M <xt.and.r_at_gmail.com> wrote:

> Senthil,
>
> As i said previously there are few standard ways and their common idea is
> to hide kokbf$ functions. I'm not sure that I remember all of them:
> 1. Avoid table() functions at all (gtt, pl/sql, multiple executions,
> etc...);
> 2. Undocumented hint "materialize": you can hide your table() function in
> "with" clause with this hint (or you can to force materization other
> standard ways). Example:
> with v as (select /*+ materialize */ * from table(...))
> select *
> from v, SECMASTER_HISTORY
> where v.xxx=SECMASTER_HISTORY.xxx
> 3. Undocumented hint "precompute_subquery":
> select *
> from SECMASTER_HISTORY
> where xxx in (select/*+ precompute_subquery */ * from table(....))
> 4. laterals/cross apply: unfortunately this approach is not stable and it
> doesn't always work. It's version-dependent thing, so I wouldn't recommend
> it
> select *
> from table(...) v
> ,lateral(select * from SECMASTER_HISTORY s where s.xxx=v.xxx)
> 5. use xmltable instead of table() - it doesn't always work too, but
> sometimes it can help.
>
> But in your case I don't see the reason to use collections. You showed
> this code:
> 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) ;
>
> and looks like there should be regexp_substr(...) instead of level:
> select sh.*
> from table(cast(multiset(select *regexp_substr(:in_list,
> '[^,]+',1,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) ;
>
> In this case you don't need table(cast multiset(...)), just use inline
> view:
> select sh.*
> from (select regexp_substr(:in_list, '[^,]+',1,level) as val
> from dual
> connect by level <= length (regexp_replace(:in_list, '[^,]+')) +
> 1) xt,
> secmaster_history sh
> where sh.security_alias = xt.val
> and to_date('15-MAY-2010') between effective_date and
> nvl(expiration_date,sysdate) ;
>
>
> On Thu, May 16, 2019 at 7:22 PM Jonathan Lewis <
> jonathan_at_jlcomp.demon.co.uk> wrote:
>
>>
>> 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
>>
>>
>>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
>

--
http://www.freelists.org/webpage/oracle-l