Re: Inefficient query plan using large in-list
From: Senthil Subramanian <skumar.sen_at_gmail.com>
Date: Thu, 16 May 2019 13:14:26 -0400
Message-ID: <CAFUEfmFJ8o+rhpg+Fc-gU4jig2RUBdZNT0OrxWAdY608nPEGwA_at_mail.gmail.com>
select sh.* from secmaster_history sh where sh.security_alias in (927763,927762,927779) and to_date('15-MAY-2010') between effective_date and nvl(expiration_date,sysdate)
| 0 | SELECT STATEMENT |
Date: Thu, 16 May 2019 13:14:26 -0400
Message-ID: <CAFUEfmFJ8o+rhpg+Fc-gU4jig2RUBdZNT0OrxWAdY608nPEGwA_at_mail.gmail.com>
Hello Jonathan,
Okay, I thought it would be simple to force the optimizer to push the predicate to get the intended plan. It works fine with a GTT!
Oracle version is 12.1.0.2
Yes, the IN list with literals works the way I want it to be as you can see below
SQL_ID 6ayrrb28h57pt, child number 0
select sh.* from secmaster_history sh where sh.security_alias in (927763,927762,927779) and to_date('15-MAY-2010') between effective_date and nvl(expiration_date,sysdate)
Plan hash value: 1878147942
| Id | Operation | Name
| Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart|
Pstop | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | O/1/M |
| 0 | SELECT STATEMENT |
| 3 | | | 148 (100)| | |
| 9 |00:00:00.08 | 1179 | 346 | | | |
|* 1 | VIEW |
SECMASTER_HISTORY | 3 | 142 | 619K| 148 (1)|
00:00:01 | | | 9 |00:00:00.08 | 1179 | 346 |
| | |
| 2 | WINDOW SORT |
| 3 | 142 | 221K| 148 (1)| 00:00:01 | |
| 1140 |00:00:00.08 | 1179 | 346 | 832K| 511K| 3/0/0|
| 3 | INLIST ITERATOR |
| 3 | | | | | | | 1140
|00:00:00.05 | 1179 | 346 | | | |
| 4 | MAT_VIEW ACCESS BY GLOBAL INDEX ROWID BATCHED|
SECMASTER_HISTORY_MV | 9 | 142 | 221K| 147 (0)|
00:00:01 | ROWID | ROWID | 1140 |00:00:00.05 | 1179 | 346 |
| | |
|* 5 | INDEX RANGE SCAN |
SYS_C_SNAP$_228PK_SECDBO_SECM | 9 | 142 | | 6 (0)|
00:00:01 | | | 1140 |00:00:00.01 | 39 | 5 |
| | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter(("EFFECTIVE_DATE"<=TO_DATE('15-MAY-2010') AND NVL("EXPIRATION_DATE",SYSDATE_at_!)>=TO_DATE('15-MAY-2010')))
5 - access((("SECURITY_ALIAS"=927762 OR "SECURITY_ALIAS"=927763 OR "SECURITY_ALIAS"=927779)) AND "SRC_INTFC_INST"=4) On Thu, May 16, 2019 at 12:23 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
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu May 16 2019 - 19:14:26 CEST