Re: Optimizer "enhancements" in 19.9?
Date: Wed, 17 Feb 2021 17:56:21 +0000
Message-ID: <CALe4HpkSiOuRA3m07UJYE5JHQeH_TOLaZ9eW5GB==eL347G+9g_at_mail.gmail.com>
Hi Charles,
An over-simplified example to demonstrate the same issue which we discussed with Sayan:
create table t(id int, x date, y date);
I am running the queries below in both 19.4 and 19.9:
explain plan for
select *
from t driver
where x||y in (select --+ unnest
max(x||y)
from t
where id = driver.id);
select * from dbms_xplan.display();
*19.4: unnested*
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
| 0 | SELECT STATEMENT | | 1 | 64 | 5 (20)|
00:00:01 |
|* 1 | HASH JOIN | | 1 | 64 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T | 1 | 31 | 2 (0)|
00:00:01 |
| 3 | VIEW | VW_SQ_1 | 1 | 33 | 3 (34)|
00:00:01 |
| 4 | HASH GROUP BY | | 1 | 31 | 3 (34)|
00:00:01 |
| 5 | TABLE ACCESS FULL| T | 1 | 31 | 2 (0)|
00:00:01 |
Predicate Information (identified by operation id):
1 - access("MAX(X||Y)"=INTERNAL_FUNCTION("X")||INTERNAL_FUNCTION("Y")
AND "ITEM_1"="DRIVER"."ID") *19.9: not unnested (the same as in your example)*
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 31 | 4 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T | 1 | 31 | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 31 | | |
|* 4 | TABLE ACCESS FULL| T | 1 | 31 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter(INTERNAL_FUNCTION("X")||INTERNAL_FUNCTION("Y")= (SELECT
/*+ UNNEST */
MAX(INTERNAL_FUNCTION("X")||INTERNAL_FUNCTION("Y")) FROM
"T" "T" WHERE "ID"=:B1))
4 - filter("ID"=:B1)
Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (U - Unused (1))
3 - SEL$2
U - unnest / Failed basic validity checks
*10053: SU: SU bypassed: More than 1 column in connect condition.*
*Next query: unnested in 19.4, not unnested in 19.9:*
explain plan for
select *
from t driver
where *nvl(x,y)* in (select --+ unnest
max(nvl(x,y))
from t
where id = driver.id);
select * from dbms_xplan.display();
*Single column: unnested in both 19.4/19.9*
explain plan for
select *
from t driver
where *(x)* in (select --+ unnest
max(x)
from t
where id = driver.id);
select * from dbms_xplan.display();
*Multi-column IN:*
explain plan for
select *
from t driver
where *(x,y)* in (select --+ unnest
max(x), max(y)
from t
where id = driver.id);
select * from dbms_xplan.display();
Has the same plan in both - there is no SU:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 31 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T | 1 | 31 | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 31 | | |
|* 4 | TABLE ACCESS FULL| T | 1 | 31 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------
Predicate Information (identified by operation id):
1 - filter(("X","Y")= (SELECT /*+ UNNEST */ MAX("X"),MAX("Y") FROM
"T" "T" WHERE "ID"=:B1))
4 - filter("ID"=:B1)
Hint Report (identified by operation id / Query Block Name / Object Alias): Total hints for statement: 1 (U - Unused (1))
3 - SEL$2
U - unnest / Failed basic validity checks
*To conclude,* when more than one column is present in the IN condition, then SU is blocked with the following reason in 19.9: *10053: SU: SU bypassed: More than 1 column in connect condition.* 19.4 was less restricted. Both versions block unnesting with multi-column IN.
Best regards,
Mikhail Velikikh
On Wed, 17 Feb 2021 at 17:45, Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:
> Hi Charles, > > OK, Mikhail Velikikh has found the new limitation blocking subquery > unnesting, he will write in details soon, but I just want to highlight > strange storing time in date column and so complex type conversions... I'd > try to avoid such problems in future > > Best regards, > Sayan Malakshinov > Oracle performance tuning expert > Oracle Database Developer Choice Award winner > Oracle ACE Associate > http://orasql.org > > ср, 17 февр. 2021 г., 20:41 Charles Schultz <sacrophyte_at_gmail.com>: > >> Sayan, to answer your question, both are DATE. Yes, I agree it is better >> to rewrite the query; however, that is not the point of this email thread. >> :) >> >> SQL > desc notes >> Name >> Null? Type >> ----------------------------------------------------------------------- >> -------- ------------------------------------------------ >> NOTEID >> NOT NULL VARCHAR2(14 CHAR) >> DATENOTE >> DATE >> TIMENOTE >> DATE >> NOTETOPIC >> VARCHAR2(80 CHAR) >> NOTETEXT >> CLOB >> USERID >> VARCHAR2(14 CHAR) >> ENTITYTYPEID >> NUMBER(38) >> ENTITYID >> VARCHAR2(14 CHAR) >> ENTITYSUBTYPEID >> NUMBER(38) >> ENTITYSUBID >> VARCHAR2(14 CHAR) >> NOTECATEGORY >> VARCHAR2(80 CHAR) >> CNVID >> VARCHAR2(20 CHAR) >> CNVID2 >> VARCHAR2(20 CHAR) >> LASTMODDATE >> DATE >> LASTMODTIME >> DATE >> LASTMODUSERID >> VARCHAR2(14 CHAR) >> ISPRIORITYITEM >> NUMBER(38) >> EMAILMID >> VARCHAR2(14 CHAR) >> ISSUMMARYITEM >> NUMBER(38) >> SITEID >> VARCHAR2(4 CHAR) >> LFSWDDID >> VARCHAR2(14 CHAR) >> >> On Wed, Feb 17, 2021 at 11:30 AM Sayan Malakshinov <xt.and.r_at_gmail.com> >> wrote: >> >>> Hello, >>> >>> What are datatypes of the columns LASTMODDATE and LASTMODTIME? >>> To be honest it's much better to rewrite this query using analytic >>> function dense_rank() over(...) = 1, ie without the need to scan table >>> twice >>> >>> >>> -- >>> Best regards, >>> Sayan Malakshinov >>> Oracle performance tuning expert >>> Oracle Database Developer Choice Award winner >>> Oracle ACE Associate >>> http://orasql.org >>> >>> ср, 17 февр. 2021 г., 19:30 Jonathan Lewis <jlewisoracle_at_gmail.com>: >>> >>>> >>>> Various possibilties. >>>> a) Oracle Corp. has realised that there's a boundary condition with >>>> this pattern that could produce wrong results and blocks the transformation >>>> (e.g. are either of lastmoddate and lastmodtime declared not null - if not >>>> the unnest ought to be invalid) >>>> >>>> b) Oracle Corp. has modified the optimizer code to produced more >>>> efficient plans in almost all cases, but the change introduces certain >>>> restrictiions that your SQL now meets (e.g. even if both lastmoddate and >>>> lastmodetime the code may now assume that to_char() or to_date() could >>>> produce a null from a non-null. >>>> >>>> c) Some relatively simple code change has introduced a bug >>>> >>>> Since the report suggests the blocking of the UNNEST is for a failed >>>> validity test I'd suspect it may be deliberate. >>>> >>>> One quick and dirty test I'd try is to add to the subquery the predicate >>>> where to_date(to_char(A.LASTMODDATE,'yyyymmdd') || to_char >>>> (A.LASTMODTIME,'hh24miss'),'yyyymmddhh24miss')is not null >>>> >>>> and see if that allows the unnest. >>>> >>>> The other Q&D I would do is produce the 10053 trace file and look for >>>> the final "Unparsed" query to see what transformation (if any) Oracle had >>>> applied in case that gave you a clue. (I'd resist looking at more detail >>>> in the 10053 for as long as possible). >>>> >>>> Regards >>>> Jonathan Lewis >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >> >> -- >> Charles Schultz >> >
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 17 2021 - 18:56:21 CET