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@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Senthil Subramanian <skumar.sen@xxxxxxxxx>
Sent: 16 May 2019 16:43
To: oracle-l@xxxxxxxxxxxxx
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@!)>=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
--
//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
