Re: Inefficient query plan using large in-list

From: S M <xt.and.r_at_gmail.com>
Date: Thu, 16 May 2019 20:48:09 +0300
Message-ID: <CAOVevU5h+9QrRE6H2psbYTUFK+G-zbFqX4GAzQ2sB+6nF4thsA_at_mail.gmail.com>

Andy,Senthil,

I've posted few examples on gist.github.com, please see materialize.sql there:
https://gist.github.com/xtender/24d29af63a398200d4d87ebc4a4a27b8

You can easily find that it works. Of course, it's possible that the solution with materialize is version-dependent.

with v(a) as (select/*+ materialize */ * from table(sys.odcinumberlist(1))) select/*+ leading(v t) use_nl(t) index(t) */ t.* from vtest t where exists(select * from v where t.a=v.a); /* PLAN_TABLE_OUTPUT

---

Plan hash value: 3760184782

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

| 0 | SELECT STATEMENT | | 1000 | 39000 | 58 (4)| 00:00:01 | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | 2 | LOAD AS SELECT | SYS_TEMP_0FD9D7150_4E96F434 | | | | | | 3 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 8168 | 16336 | 29 (0)| 00:00:01 | | 4 | HASH GROUP BY | | 1000 |

39000 | 29 (7)| 00:00:01 | | 5 | NESTED LOOPS | | | | | | | 6 | NESTED
LOOPS | | 1000 | 39000 | 28 (4)| 00:00:01 | | 7 | VIEW | | 8168 | 103K| 3
(0)| 00:00:01 | | 8 | HASH UNIQUE | | 1 | 16336 | | | | 9 | TABLE ACCESS

FULL | SYS_TEMP_0FD9D7150_4E96F434 | 8168 | 16336 | 3 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | ITEST | 1000 | | 2 (0)| 00:00:01 | | 11 | TABLE ACCESS BY INDEX ROWID | XTEST | 1000 | 26000 | 24 (0)| 00:00:01 |

---

On Thu, May 16, 2019 at 8:20 PM Senthil Subramanian <skumar.sen_at_gmail.com> wrote:

> 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
>>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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

Received on Thu May 16 2019 - 19:48:09 CEST