Oracle Notes by Mikhail: июня 2020

суббота, 13 июня 2020 г.

CURSOR Expressions Return Wrong Results in 18c/19c

That is a wrong results issue I stumbled upon this morning. A developer told me that one of their queries started returning wrong results after we upgraded one of our databases from 12.2.0.1 to 19.7. I constructed a simplified test case to demonstrate this issue which I ran on 19.4. I have been able to reproduce it on 18.3, 19.7, and Live SQL as well.

SQL> create table t1(id int);
 
Table created.
 
SQL> create table t2(id int);
 
Table created.
 
SQL>
SQL> insert into t1 values (1);
 
1 row created.
 
SQL> insert into t2 values (1);
 
1 row created.

In a nutshell, I created two tables that have one row with a numeric 1 value. Let's now run the following query:

SQL> select query_type
  2    from (select 'A' query_type
  3            from t1 t11
  4           where exists (
  5                   select null
  6                     from t2 t21
  7                    where t21.id = t11.id)
  8           union all
  9          select 'B' query_type
 10            from t1 t12
 11           where not exists (
 12                   select null
 13                     from t2 t22
 14                    where t22.id = t12.id)
 15         );
 
Q
-
A

Then, I am going to put that query into a CURSOR expression:

SQL> select cursor(
  2           select query_type
  3             from (select 'A' query_type
  4                     from t1 t11
  5                    where exists (
  6                            select null
  7                              from t2 t21
  8                             where t21.id = t11.id)
  9                    union all
 10                   select 'B' query_type
 11                     from t1 t12
 12                    where not exists (
 13                            select null
 14                              from t2 t22
 15                             where t22.id = t12.id)
 16                  )
 17         ) data
 18    from dual;
 
DATA
--------------------
CURSOR STATEMENT : 1
 
CURSOR STATEMENT : 1
 
Q
-
B

That is when it gets interesting. Somehow the query which returned 'A' being run separately starts returning 'B' after it comes as an input subquery into a CURSOR expression.

I initially started looking at the execution plan in an attempt to figure out what changed in the execution plan between 12.2 and 19c. There are some changes but nothing that drew my eye.

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2197603499
 
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |       |    14   (0)| 00:00:01 |
|   1 |  VIEW                |      |     2 |     6 |    12   (0)| 00:00:01 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|*  3 |    FILTER            |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| T1   |     1 |    13 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)| 00:00:01 |
|*  6 |    FILTER            |      |       |       |            |          |
|   7 |     TABLE ACCESS FULL| T1   |     1 |    13 |     3   (0)| 00:00:01 |
|*  8 |     TABLE ACCESS FULL| T2   |     1 |    13 |     3   (0)| 00:00:01 |
|   9 |  FAST DUAL           |      |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SET$1 / from$_subquery$_001@SEL$2
   2 - SET$1
   3 - SEL$3
   4 - SEL$3 / T11@SEL$3
   5 - SEL$4 / T21@SEL$4
   6 - SEL$5
   7 - SEL$5 / T12@SEL$5
   8 - SEL$6 / T22@SEL$6
   9 - SEL$1 / DUAL@SEL$1
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$6" "T22"@"SEL$6")
      FULL(@"SEL$4" "T21"@"SEL$4")
      PQ_FILTER(@"SEL$3" SERIAL)
      FULL(@"SEL$3" "T11"@"SEL$3")
      PQ_FILTER(@"SEL$5" SERIAL)
      FULL(@"SEL$5" "T12"@"SEL$5")
      NO_ACCESS(@"SEL$2" "from$_subquery$_001"@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$6")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$4")
      ALL_ROWS
      DB_VERSION('19.1.0')
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter( EXISTS (SELECT 0 FROM "T2" "T21" WHERE "T21"."ID"=:B1))
   5 - filter("T21"."ID"=:B1)
   6 - filter( NOT EXISTS (SELECT 0 FROM "T2" "T22" WHERE
              "T22"."ID"=:B1))
   8 - filter("T22"."ID"=:B1)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
 
68 rows selected.

The gather_plan_statistics hint was not really helpful as it is a nested cursor, so that it does not show the rowsource statistics from that level - the nested cursor runs separately and its rowsource statistics are not included in the top-level cursor ones:

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |      1 |00:00:00.01 |
|   1 |  VIEW                |      |      0 |      2 |      0 |00:00:00.01 |
|   2 |   UNION-ALL          |      |      0 |        |      0 |00:00:00.01 |
|*  3 |    FILTER            |      |      0 |        |      0 |00:00:00.01 |
|   4 |     TABLE ACCESS FULL| T1   |      0 |      1 |      0 |00:00:00.01 |
|*  5 |     TABLE ACCESS FULL| T2   |      0 |      1 |      0 |00:00:00.01 |
|*  6 |    FILTER            |      |      0 |        |      0 |00:00:00.01 |
|   7 |     TABLE ACCESS FULL| T1   |      0 |      1 |      0 |00:00:00.01 |
|*  8 |     TABLE ACCESS FULL| T2   |      0 |      1 |      0 |00:00:00.01 |
|   9 |  FAST DUAL           |      |      1 |      1 |      1 |00:00:00.01 |
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter( IS NOT NULL)
   5 - filter("T21"."ID"=:B1)
   6 - filter( IS NULL)
   8 - filter("T22"."ID"=:B1)

I found the explanation of this issue in the SQL trace file:

=====================
PARSING IN CURSOR #139973723001152 len=579 dep=0 uid=78 oct=3 lid=78 tim=4622189819 hv=2185287422 ad='68d3c950' sql
id='0mkkpha141pry'
select cursor(
         select query_type
           from (select 'A' query_type
                   from t1 t11
                  where exists (
                          select null
                            from t2 t21
                           where t21.id = t11.id)
                  union all
                 select 'B' query_type
                   from t1 t12
                  where not exists (
                          select null
                            from t2 t22
                           where t22.id = t12.id)
                )
       ) data
  from dual
END OF STMT
PARSE #139973723001152:c=11048,e=12511,p=0,cr=45,cu=12,mis=1,r=0,dep=0,og=1,plh=2197603499,tim=4622189819
EXEC #139973723001152:c=13,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2197603499,tim=4622189873
=====================
PARSING IN CURSOR #139973720520408 len=272 dep=2 uid=78 oct=3 lid=78 tim=4622190302 hv=3362864010 ad='68d141b0' sqlid='faj0udr472fwa'
SELECT "A2"."QUERY_TYPE" "QUERY_TYPE"
  FROM  ( 
          (SELECT 'A' "QUERY_TYPE"
             FROM "T1" "A5"
            WHERE  EXISTS (
                     SELECT 0 
                       FROM "T2" "A6"
                      WHERE "A6"."ID"=:CV1$))
           UNION ALL
          (SELECT 'B' "QUERY_TYPE"
             FROM "T1" "A4"
            WHERE  NOT EXISTS (
                     SELECT 0 
                       FROM "T2" "A7"
                      WHERE "A7"."ID"=:CV2$))) "A2"
END OF STMT
PARSE #139973720520408:c=375,e=375,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,plh=0,tim=4622190302
..skip..
BINDS #139973720520408:
 
 Bind#0
  oacdty=02 mxl=22(00) mxlc=00 mal=00 scl=00 pre=00
  oacflg=11 fl2=0001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=7f4e2be3b278  bln=22  avl=00  flg=05
 Bind#1
  oacdty=02 mxl=22(00) mxlc=00 mal=00 scl=00 pre=00
  oacflg=11 fl2=0001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=7f4e2be3b290  bln=22  avl=00  flg=01
EXEC #139973720520408:c=9732,e=9360,p=0,cr=37,cu=0,mis=1,r=0,dep=2,og=1,plh=489056501,tim=4622199711
FETCH #139973723001152:c=10218,e=9845,p=0,cr=37,cu=0,mis=0,r=1,dep=0,og=1,plh=2197603499,tim=4622199753
STAT #139973723001152 id=1 cnt=0 pid=0 pos=1 obj=0 op='VIEW  (cr=0 pr=0 pw=0 str=0 time=0 us cost=12 size=6 card=2)'
STAT #139973723001152 id=2 cnt=0 pid=1 pos=1 obj=0 op='UNION-ALL  (cr=0 pr=0 pw=0 str=0 time=0 us)'
STAT #139973723001152 id=3 cnt=0 pid=2 pos=1 obj=0 op='FILTER  (cr=0 pr=0 pw=0 str=0 time=0 us)'
STAT #139973723001152 id=4 cnt=0 pid=3 pos=1 obj=25013 op='TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 str=0 time=0 us cost=3 size=13 card=1)'
STAT #139973723001152 id=5 cnt=0 pid=3 pos=2 obj=25014 op='TABLE ACCESS FULL T2 (cr=0 pr=0 pw=0 str=0 time=0 us cost=3 size=13 card=1)'
STAT #139973723001152 id=6 cnt=0 pid=2 pos=2 obj=0 op='FILTER  (cr=0 pr=0 pw=0 str=0 time=0 us)'
STAT #139973723001152 id=7 cnt=0 pid=6 pos=1 obj=25013 op='TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 str=0 time=0 us cost=3 size=13 card=1)'
STAT #139973723001152 id=8 cnt=0 pid=6 pos=2 obj=25014 op='TABLE ACCESS FULL T2 (cr=0 pr=0 pw=0 str=0 time=0 us cost=3 size=13 card=1)'
STAT #139973723001152 id=9 cnt=1 pid=0 pos=2 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 str=1 time=0 us cost=2 size=0 card=1)'
FETCH #139973720520408:c=178,e=177,p=0,cr=8,cu=0,mis=0,r=1,dep=0,og=1,plh=489056501,tim=4622200599
STAT #139973720520408 id=1 cnt=1 pid=0 pos=1 obj=0 op='VIEW  (cr=9 pr=0 pw=0 str=1 time=179 us cost=12 size=6 card=2)'
STAT #139973720520408 id=2 cnt=1 pid=1 pos=1 obj=0 op='UNION-ALL  (cr=9 pr=0 pw=0 str=1 time=177 us)'
STAT #139973720520408 id=3 cnt=0 pid=2 pos=1 obj=0 op='FILTER  (cr=1 pr=0 pw=0 str=1 time=8 us)'
STAT #139973720520408 id=4 cnt=0 pid=3 pos=1 obj=25013 op='TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 str=0 time=0 us cost=3 size=0 card=1)'
STAT #139973720520408 id=5 cnt=0 pid=3 pos=2 obj=25014 op='TABLE ACCESS FULL T2 (cr=1 pr=0 pw=0 str=1 time=6 us cost=3 size=13 card=1)'
STAT #139973720520408 id=6 cnt=1 pid=2 pos=2 obj=0 op='FILTER  (cr=8 pr=0 pw=0 str=1 time=164 us)'
STAT #139973720520408 id=7 cnt=1 pid=6 pos=1 obj=25013 op='TABLE ACCESS FULL T1 (cr=7 pr=0 pw=0 str=1 time=144 us cost=3 size=0 card=1)'
STAT #139973720520408 id=8 cnt=0 pid=6 pos=2 obj=25014 op='TABLE ACCESS FULL T2 (cr=1 pr=0 pw=0 str=1 time=9 us cost=3 size=13 card=1)'
FETCH #139973723001152:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2197603499,tim=4622200901
CLOSE #139973720520408:c=3,e=2,dep=1,type=0,tim=4622201312
CLOSE #139973723001152:c=6,e=193,dep=0,type=0,tim=4622201500

That is where the most remarkable part happens. The subquery is modified in an awfully wrong way: the columns of the outer queries disappear from the inner queries, and they are replaced with some bind variables: CV1$, CV2$.
Those variables, which are of the NUMBER data type, are set to NULL.
Then, the 'A' branch of the UNION ALL, which uses EXISTS, returns nothing. The 'B' branch, which uses NOT EXISTS, returns the 'B' row since the NOT EXISTS subquery brings back an empty result set.

At this stage the root cause of the wrong results issue is known. Obviously, I have no clue why those variables were introduced but I know why that query returns that result.

I tried to tinker around with that query a little bit to see what can be done to make it work, and here is a short summary of my findings:

Correlated subqueries are essential. The query starts returning the correct results (one 'A' row) once I decorrelate the subqueries:

SQL> select cursor(
  2           select query_type
  3             from (select 'A' query_type
  4                     from t1 t11
  5                    where id in (
  6                            select id
  7                              from t2 t21)
  8                    union all
  9                   select 'B' query_type
 10                     from t1 t12
 11                    where id not in (
 12                            select id
 13                              from t2 t22)
 14                  )
 15         ) data
 16    from dual;
 
DATA
--------------------
CURSOR STATEMENT : 1
 
CURSOR STATEMENT : 1
 
Q
-
A

Nesting of the subqueries does matter. For instance, the query returns the correct results after I reduce the nesting level:

SQL> select cursor(
  2           select 'A' query_type
  3             from t1 t11
  4            where exists (
  5                    select null
  6                      from t2 t21
  7                     where t21.id = t11.id)
  8            union all
  9           select 'B' query_type
 10             from t1 t12
 11            where not exists (
 12                    select null
 13                      from t2 t22
 14                     where t22.id = t12.id)
 15         ) data
 16    from dual;
 
DATA
--------------------
CURSOR STATEMENT : 1
 
CURSOR STATEMENT : 1
 
Q
-
A

Based on how Oracle modifies the subquery under the hood, certain CURSOR expressions can return:

Less rows:

SQL> doc
DOC>##############################
DOC>   Less rows
DOC>##############################
DOC>#
SQL>
SQL> select cursor(
  2           select query_type
  3             from (select 'A' query_type
  4                     from t1 t11
  5                    where exists (
  6                            select null
  7                              from t2 t21
  8                             where t21.id = t11.id)
  9                    union all
 10                   select 'B' query_type
 11                     from t1 t12
 12                    where exists (
 13                            select null
 14                              from t2 t22
 15                             where t22.id = t12.id)
 16                  )
 17         ) data
 18    from dual;
 
DATA
--------------------
CURSOR STATEMENT : 1
 
CURSOR STATEMENT : 1
 
no rows selected
 
 
SQL>
SQL> doc
DOC>   The cursor subquery
DOC>#
SQL> select query_type
  2    from (select 'A' query_type
  3            from t1 t11
  4           where exists (
  5                   select null
  6                     from t2 t21
  7                    where t21.id = t11.id)
  8           union all
  9          select 'B' query_type
 10            from t1 t12
 11           where exists (
 12                   select null
 13                     from t2 t22
 14                    where t22.id = t12.id)
 15         )
 16  ;
 
Q
-
A
B

More rows:

SQL> doc
DOC>##############################
DOC>   More rows
DOC>##############################
DOC>#
SQL>
SQL> select cursor(
  2           select query_type
  3             from (select 'A' query_type
  4                     from t1 t11
  5                    union all
  6                   select 'B' query_type
  7                     from t1 t12
  8                    where not exists (
  9                            select null
 10                              from t2 t22
 11                             where t22.id = t12.id)
 12                  )
 13         ) data
 14    from dual;
 
DATA
--------------------
CURSOR STATEMENT : 1
 
CURSOR STATEMENT : 1
 
Q
-
A
B
 
 
SQL>
SQL> doc
DOC>   The cursor subquery
DOC>#
SQL> select query_type
  2    from (select 'A' query_type
  3            from t1 t11
  4           union all
  5          select 'B' query_type
  6            from t1 t12
  7           where not exists (
  8                   select null
  9                     from t2 t22
 10                    where t22.id = t12.id)
 11         );
 
Q
-
A

Different values as it was demostrated in the beginning of this post

The good news is that it is a known documented issue: Bug 30528947 - Nested Query Using CURSOR Expression Returns Wrong Result (Doc ID 30528947.8). There are patches available for some release updates.
The bad news is that the issue is not mentioned on the document I would expect it to be: Things to Consider to Avoid Prominent Wrong Result Problems on 19C Proactively (Doc ID 2606585.1). How is one supposed to avoid such issues? I always read those 'things to consider' before I devise any upgrade plans.
The scripts, a sample trace file, and the spool file for this post are available on: GitHub.

вторник, 9 июня 2020 г.

OR Expansion and Virtual Columns: Changes in 19c

This post is a continuation of my previous one: OR Expansion and Virtual Columns. The last post demonstrated that OR Expansion can somehow be affected by virtual columns in such a way that queries that did not perform OR Expansion can start using it as soon as a virtual column is created. Both an FBI and extended statistics lead to the same result provided that a certain column used in the query is included in them.
This time around, I am going to take a look at how 19c has changed that.

Firstly, I am going to recreate my initial table: or_expand_setup.sql:

SQL> @or_expand_setup
SQL> drop table t1;
 
Table dropped.
 
SQL>
SQL> create table t1 (
  2    part_key varchar2(8),
  3    status   varchar2(12),
  4    pad1     char(500)
  5  )
  6  partition by list(part_key) (
  7    partition values ('P1'),
  8    partition values (default)
  9  )
 10  ;
 
Table created.
 
SQL>
SQL>
SQL> insert into t1
  2  select 'P1', status, 'X'
  3    from (select 90 pct, 'PROCESSED' status from dual union all
  4          select 1, 'UNPROCESSED' from dual union all
  5          select 9, 'PENDING' from dual) params,
  6         lateral(
  7           select level
  8             from dual
  9             connect by level <= params.pct * 1000
 10         ) duplicator;
 
100000 rows created.
 
SQL>
SQL> commit;
 
Commit complete.
 
SQL>
SQL> create index t1_status_i on t1(status) local;
 
Index created.
 
SQL>
SQL> select status,
  2         count(*),
  3         round(ratio_to_report(count(*)) over () * 100, 2) pct
  4    from t1
  5   group by status
  6   order by 1;
 
STATUS         COUNT(*)        PCT
------------ ---------- ----------
PENDING            9000          9
PROCESSED         90000         90
UNPROCESSED        1000          1

Secondly, I am running the or_expand_test_vcol.sql script which was previously performed OR Expansion in both 12.2 and 18c:

100

101

102

103

104

105

106

107

108

109

110

111

112

113

SQL> @or_expand_test_vcol
SQL> set def on lin 124 pages 100
SQL>
SQL> alter table t1 add lower_status varchar2(128) generated always as (lower(status)) virtual;
 
Table altered.
 
SQL>
SQL> exec dbms_stats.gather_table_stats( '', 't1', method_opt => 'for all columns size 254')
 
 
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> col tfi old_v tfi nopri
SQL>
SQL> select to_char(sysdate, 'yyyymmdd_hh24miss') tfi from dual;
 
 
 
 
SQL>
SQL> alter session set tracefile_identifier='&tfi.';
old   1: alter session set tracefile_identifier='&tfi.'
new   1: alter session set tracefile_identifier='20200608_180223'
 
Session altered.
 
SQL>
SQL> alter session set events 'trace[sql_optimizer.*]';
 
Session altered.
 
SQL>
SQL>
SQL> var part_key varchar2(10)='P1'
SQL> var param    varchar2(12)='WAITING'
SQL>
SQL> select --+ gather_plan_statistics or_expand(@sel$1 (1) (2))
  2         count(pad1)
  3    from t1
  4   where part_key = :part_key
  5     and (:param = 'WAITING' and status = 'UNPROCESSED'
  6          or
  7          :param = 'ALL' and status <> 'PENDING');
 
COUNT(PAD1)
-----------
       1000
 
SQL>
SQL> select *
  2    from dbms_xplan.display_cursor(format=> 'allstats last outline');
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  fq8b3jumk131t, child number 0
-------------------------------------
select --+ gather_plan_statistics or_expand(@sel$1 (1) (2))
count(pad1)   from t1  where part_key = :part_key    and (:param =
'WAITING' and status = 'UNPROCESSED'         or         :param = 'ALL'
and status <> 'PENDING')
 
Plan hash value: 1293629841
 
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |      1 |00:00:00.03 |    7826 |
|   1 |  SORT AGGREGATE        |      |      1 |      1 |      1 |00:00:00.03 |    7826 |
|   2 |   PARTITION LIST SINGLE|      |      1 |    920 |   1000 |00:00:00.03 |    7826 |
|*  3 |    TABLE ACCESS FULL   | T1   |      1 |    920 |   1000 |00:00:00.03 |    7826 |
-----------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter((((:PARAM='ALL' AND "STATUS"<>'PENDING') OR
              ("STATUS"='UNPROCESSED' AND :PARAM='WAITING')) AND "PART_KEY"=:PART_KEY))
 
 
38 rows selected.
 
SQL>
SQL> alter session set events 'trace[sql_optimizer.*] off';
 
Session altered.
 
SQL>
SQL> col value for a80
SQL>
SQL> select value
  2    from v$diag_info
  3   where name='Default Trace File';
 
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7253_20200608_180223.trc

As the output above demonstrates, there is no OR Expansion anymore. The trace file orcl_ora_7253_20200608_180223.trc shows the following:

***********************************
Cost-Based OR Expansion
***********************************
ORE: Trying CBQT OR expansion before unnesting
ORE: Checking validity of OR Expansion for query block SEL$1 (#1)
ORE: Predicate chain before QB validity check - SEL$1
"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')
 
ORE: Predicate chain after QB validity check - SEL$1
"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')
OR Expansion on query block SEL$1 (#1)
ORE: Checking validity of OR Expansion for query block SEL$1 (#1)
ORE: Predicate chain before QB validity check - SEL$1
"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')
 
ORE: Predicate chain after QB validity check - SEL$1
"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')
ORE: Using search type: linear
ORE: Checking validity of OR Expansion for query block SEL$1 (#1)
ORE: Predicate chain before QB validity check - SEL$1
"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')
 
ORE: Predicate chain after QB validity check - SEL$1
"T1"."PART_KEY"=:B1 AND (:B2='WAITING' AND "T1"."STATUS"='UNPROCESSED' OR :B3='ALL' AND "T1"."STATUS"<>'PENDING')
ORE: Checking validity of disjunct chain 
ORE: Bypassed for disjunct chain: No Index or Partition driver found.
ORE: # conjunction chain - 1
ORE: No state generated.

It is worth noting that even a full outline does not lead to the desired plan. That is how I discovered that issue initially. The query in question did have an SQL Plan Baseline which stopped being reproducible following a 19c upgrade.

A straightforward way to get the desired execution plan is to rewrite the initial query as follows:

100

SQL> @or_expand_test_rewrite
 
..skip..
 
SQL> var part_key varchar2(10)='P1'
SQL> var param    varchar2(12)='WAITING'
SQL>
SQL> select --+ gather_plan_statistics or_expand(@sel$1 (1) (2))
  2         count(pad1)
  3    from t1
  4   where 1 = 1
  5     and (part_key = :part_key and :param = 'WAITING' and status = 'UNPROCESSED'
  6          or
  7          part_key = :part_key||'' and :param = 'ALL' and status <> 'PENDING');
 
COUNT(PAD1)
-----------
       1000
 
SQL>
SQL> select *
  2    from dbms_xplan.display_cursor(format=> 'allstats last outline');
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  99zvump911bc7, child number 0
-------------------------------------
select --+ gather_plan_statistics or_expand(@sel$1 (1) (2))
count(pad1)   from t1  where 1 = 1    and (part_key = :part_key and
:param = 'WAITING' and status = 'UNPROCESSED'         or
part_key = :part_key||'' and :param = 'ALL' and status <> 'PENDING')
 
Plan hash value: 3973059565
 
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                 |      1 |        |      1 |00:00:00.01 |      83 |
|   1 |  SORT AGGREGATE                                |                 |      1 |      1 |      1 |00:00:00.01 |      83 |
|   2 |   VIEW                                         | VW_ORE_BA8ECEFB |      1 |  91982 |   1000 |00:00:00.01 |      83 |
|   3 |    UNION-ALL                                   |                 |      1 |        |   1000 |00:00:00.01 |      83 |
|*  4 |     FILTER                                     |                 |      1 |        |   1000 |00:00:00.01 |      83 |
|   5 |      PARTITION LIST SINGLE                     |                 |      1 |    999 |   1000 |00:00:00.01 |      83 |
|*  6 |       TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1              |      1 |    999 |   1000 |00:00:00.01 |      83 |
|*  7 |        INDEX RANGE SCAN                        | T1_STATUS_I     |      1 |   1000 |   1000 |00:00:00.01 |       6 |
|*  8 |     FILTER                                     |                 |      1 |        |      0 |00:00:00.01 |       0 |
|   9 |      PARTITION LIST SINGLE                     |                 |      0 |  90983 |      0 |00:00:00.01 |       0 |
|* 10 |       TABLE ACCESS FULL                        | T1              |      0 |  90983 |      0 |00:00:00.01 |       0 |
----------------------------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SET$2A13AF86_2")
      OUTLINE_LEAF(@"SET$2A13AF86_1")
      OUTLINE_LEAF(@"SET$2A13AF86")
      OUTLINE_LEAF(@"SEL$9162BF3C")
      OR_EXPAND(@"SEL$1" (1) (2))
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$9162BF3C" "VW_ORE_BA8ECEFB"@"SEL$BA8ECEFB")
      INDEX_RS_ASC(@"SET$2A13AF86_1" "T1"@"SET$2A13AF86_1" ("T1"."STATUS"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SET$2A13AF86_1" "T1"@"SET$2A13AF86_1")
      FULL(@"SET$2A13AF86_2" "T1"@"SET$2A13AF86_2")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - filter(:PARAM='WAITING')
   6 - filter("PART_KEY"=:PART_KEY)
   7 - access("STATUS"='UNPROCESSED')
   8 - filter(:PARAM='ALL')
  10 - filter(("STATUS"<>'PENDING' AND "PART_KEY"=:PART_KEY||'' AND (LNNVL("PART_KEY"=:PART_KEY) OR
              LNNVL(:PARAM='WAITING') OR LNNVL("STATUS"='UNPROCESSED'))))
 
 
57 rows selected.
 
SQL>
SQL> alter session set events 'trace[sql_optimizer.*] off';
 
Session altered.
 
SQL>
SQL> col value for a80
SQL>
SQL> select value
  2    from v$diag_info
  3   where name='Default Trace File';
 
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7253_20200608_180825.trc

That is what is done in or_expand_test_rewrite.sql; the corresponding trace file is: orcl_ora_7253_20200608_180825.trc.
Evidently, this rewrite can be applied in a limited number of cases.
Thankfully, the old OR Expansion transformation, aka CONCATENATION, works just fine for this example which is demonstrated in the listing below: or_expand_test_use_concat.sql

SQL> @or_expand_test_use_concat
 
..skip..
 
SQL> var part_key varchar2(10)='P1'
SQL> var param    varchar2(12)='WAITING'
SQL>
SQL> select --+ gather_plan_statistics use_concat(or_predicates(2))
  2         count(pad1)
  3    from t1
  4   where part_key = :part_key
  5     and (:param = 'WAITING' and status = 'UNPROCESSED'
  6          or
  7          :param = 'ALL' and status <> 'PENDING');
 
COUNT(PAD1)
-----------
       1000
 
SQL>
SQL> select *
  2    from dbms_xplan.display_cursor(format=> 'allstats last outline');
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  cygpq5hyy2uu2, child number 0
-------------------------------------
select --+ gather_plan_statistics use_concat(or_predicates(2))
count(pad1)   from t1  where part_key = :part_key    and (:param =
'WAITING' and status = 'UNPROCESSED'         or         :param = 'ALL'
and status <> 'PENDING')
 
Plan hash value: 496678280
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |             |      1 |        |      1 |00:00:00.01 |      83 |
|   1 |  SORT AGGREGATE                               |             |      1 |      1 |      1 |00:00:00.01 |      83 |
|   2 |   CONCATENATION                               |             |      1 |        |   1000 |00:00:00.01 |      83 |
|*  3 |    FILTER                                     |             |      1 |        |      0 |00:00:00.01 |       0 |
|   4 |     PARTITION LIST SINGLE                     |             |      0 |  90992 |      0 |00:00:00.01 |       0 |
|*  5 |      TABLE ACCESS FULL                        | T1          |      0 |  90992 |      0 |00:00:00.01 |       0 |
|*  6 |    FILTER                                     |             |      1 |        |   1000 |00:00:00.01 |      83 |
|   7 |     PARTITION LIST SINGLE                     |             |      1 |    990 |   1000 |00:00:00.01 |      83 |
|*  8 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1          |      1 |    990 |   1000 |00:00:00.01 |      83 |
|*  9 |       INDEX RANGE SCAN                        | T1_STATUS_I |      1 |    990 |   1000 |00:00:00.01 |       6 |
-----------------------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$1_1")
      USE_CONCAT(@"SEL$1" OR_PREDICATES(2))
      OUTLINE_LEAF(@"SEL$1_2")
      FULL(@"SEL$1_1" "T1"@"SEL$1")
      INDEX_RS_ASC(@"SEL$1_2" "T1"@"SEL$1_2" ("T1"."STATUS"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_2" "T1"@"SEL$1_2")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(:PARAM='ALL')
   5 - filter(("STATUS"<>'PENDING' AND "PART_KEY"=:PART_KEY))
   6 - filter(:PARAM='WAITING')
   8 - filter("PART_KEY"=:PART_KEY)
   9 - access("STATUS"='UNPROCESSED')
       filter((LNNVL(:PARAM='ALL') OR LNNVL("STATUS"<>'PENDING')))
 
 
53 rows selected.
 
SQL>
SQL> alter session set events 'trace[sql_optimizer.*] off';
 
Session altered.
 
SQL>
SQL> col value for a80
SQL>
SQL> select value
  2    from v$diag_info
  3   where name='Default Trace File';
 
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7253_20200608_181301.trc

The corresponding trace file is: orcl_ora_7253_20200608_181301.trc.
This blog post demonstrates a specific example in which OR Expansion fails to produce the desired execution plan, leading to a suboptimal execution.
By way of contrast, CONCATENATION results in the execution plan I was seeking, performing as effectively as OR Expansion did before 19c.
It is my hope that Oracle will fix this issue eventually, which I am trying to get done in SR 3-22449907431 : OR-Expansion does not work following 19.5 upgrade.

Oracle Notes by Mikhail

Страницы

суббота, 13 июня 2020 г.

CURSOR Expressions Return Wrong Results in 18c/19c

вторник, 9 июня 2020 г.

OR Expansion and Virtual Columns: Changes in 19c

Blog Archive

Страницы

суббота, 13 июня 2020 г.

CURSOR Expressions Return Wrong Results in 18c/19c

вторник, 9 июня 2020 г.

OR Expansion and Virtual Columns: Changes in 19c

суббота, 13 июня 2020 г.

вторник, 9 июня 2020 г.