Re: Inefficient query plan using large in-list

From: Senthil Subramanian <skumar.sen_at_gmail.com>
Date: Thu, 16 May 2019 13:58:57 -0400
Message-ID: <CAFUEfmE9Djao+x5FyR=YC_N4RW9N1i3sgPKcqFTCYdMaTXURDA_at_mail.gmail.com>

Sayan,

I tried the materialize hint but it doesn't work due to the analytic function present in the view as mentioned by Jonathan.

I rewrote the SQL to go directly against the MV by including the analytic function computation in the SQL itself. This way I can avoid using GTT or other mechanisms to covert the list values into rows.

Here's the updated SQL which works as I expected:

select *
from (select security_alias,

       src_intfc_inst,
       effective_date,
       lag(effective_date) over (partition by security_alias,src_intfc_inst
order by effective_date desc) - interval '1' second expiration_date,
       investment_type,
       issue_amount,
       issue_description,
       issue_id,
       issue_name
       from   (select /*+ no_merge no_unnest dynamic_sampling(xt) */ sh.*
               from   (select regexp_substr(:in_list, '[^,]+',1,level) as
in_list_val
                       from   dual
                       connect by level <= length (regexp_replace(:in_list,
'[^,]+')) + 1) xt
                       JOIN secmaster_history_mv sh ON (sh.security_alias =

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

Thanks to everyone for your time and effort!

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

> 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:58:57 CEST

This message: [ Message body ]
Next message: Mark J. Bobak: "Help with moving from Glassfish 4.1.1 to Oracle WebLogic Server 12.2.1.3.0"
Previous message: S M: "Re: Inefficient query plan using large in-list"
In reply to S M: "Re: Inefficient query plan using large in-list"
Next in thread: S M: "Re: Inefficient query plan using large in-list"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message