Index and mixed order by

The orderly arrangement of the index keys in the leaf blocks makes it possible for Oracle to traverse the index both backward and forward. It renders, as such, the CBO in the plausible capacity of eliminating a costly order by operation. This order by elimination is, however, possible provided the index is used and the query order is wanted in the exact direction of the index columns backward or forward.

Let’s clarify this with a simple example

The model

create table t1 
as select 
    rownum n1
   ,trunc((rownum-1)/3) n2
   ,mod(rownum,5) n3
   ,lpad('x',5) v1
from dual 
connect by level <=1e3;

create index t1_idx on t1(n1, n2, n3);

explain plan for 
select * from t1 
where n1 <=5 
order by 
      n1,n2, n3;

select * from table(dbms_xplan.display);

------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | 
------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     5 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     5 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |     5 |
------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N1"<=5)

You should note in the above expected execution plan the absence of a SORT ORDER BY operation. This has been possible because the order in which the query result set is wanted matches perfectly the order in which the columns have been arranged in the index. Oracle is also able to avoid a SORT operation if the order of the query result set is wanted in the exact reverse direction of the index columns as shown below:

explain plan for 
select * from t1 
where n1 <=5 
order by 
      n1 desc,n2 desc, n3 desc;

select * from table(dbms_xplan.display);

-------------------------------------------------------
| Id  | Operation                    | Name   | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     5 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T1     |     5 |
|*  2 |   INDEX RANGE SCAN DESCENDING| T1_IDX |     5 |
-------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N1"<=5)

Again the SORT ORDER BY operation has been avoided thanks to the ability of Oracle to traverse the index in a descending order (backward). However, if the result set is wanted in an order that is neither in the index direction nor in its opposite one, then it will be impossible for Oracle to avoid a SORT operation even when the index is used as the following demonstrates:

explain plan for 
select * from t1 
where n1 <=5 
order by 
      n1 ,n2 desc, n3 ;

select * from table(dbms_xplan.display);

---------------------------------------------------------------
| Id  | Operation                            | Name   | Rows  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT                     |        |     5 |
|   1 |  SORT ORDER BY                       |        |     5 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1     |     5 |
|*  3 |    INDEX RANGE SCAN                  | T1_IDX |     5 |
---------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("N1"<=5)

As you can see, this time, in contrast to the first two cases, a SORT ORDER BY operation kicks in. This operation becomes mandatory since the result set has been desired in an order that doesn’t match the order of the index columns. Spot in passing that when Oracle avoids a SORT ORDER BY it doesn’t visit the T1 table in a BATCHED mode and vice versa. I have already wrote a note about this here.

If you are in a situation where you want absolutely to avoid a costly SORT ORDER BY operation then you need to create a new matching index like the following one:

alter index t1_idx invisible;

create index t1_idx2 on t1(n1,n2 desc, n3);

explain plan for 
select * from t1 
where n1 <=5 
order by 
      n1 ,n2 desc, n3 ;

select * from table(dbms_xplan.display);

---------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | 
---------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     5 |    85 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     5 |    85 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX2 |     5 |       |
---------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N1"<=5)

The same desired order could also be achieved without a SORT ORDER BY operation by creating the following index:

drop index t1_idx2;

create index t1_idx2 on t1(n1 desc, n2, n3 desc);

explain plan for 
select * from t1 
where n1 <=5 
order by 
      n1 ,n2 desc, n3 ;

select * from table(dbms_xplan.display);

--------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | 
--------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     5 | 
|   1 |  TABLE ACCESS BY INDEX ROWID | T1      |     5 |  
|*  2 |   INDEX RANGE SCAN DESCENDING| T1_IDX2 |     9 | 
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(SYS_OP_DESCEND("N1")>=HEXTORAW('3EF9FF') AND SYS_OP_DESCEND("N1") 
              IS NOT NULL)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("N1"))<=5)

Looking at the above exotic predicate I am not sure that this last case will always outperform the preceding one.

So What?

Since you know roughly how an index should be designed to make a sort operation avoidable, are you going to change the ORDER BY clause to suit an existing index or create a new index to match the ORDER BY clause? The answer to this question has been magnificently given by Peter Gulutzan and Trudy Pelzer in their SQL Performance Tuning book “In both cases, we would have to say No. If you did either one, you’d be crossing the boundary between “taking advantage of a side effect” and “depending on a side effect.”

Because indexes exist mainly to support WHERE clause rather than ORDER BY I would very probably suggest to not change the existing index. Particularly when real life experience confirms that it is easier to add an index than to drop an existing one. But when we know how a well designed index can precisely cover a query and eventually avoid a sort operation, we can either challenge the client for the order in which he wants his result set or, all things being equal, design an index to match the order by clause.

Summary

Watch out your critical queries necessitating a certain order. If you are going to design an index covering one of their predicates, and all things being equal, think about designing this index so that its columns will match the query order by clause either backward or forward. But you shouldn’t being always forcing the CBO to use an index merely because it will avoid a SORT operation.

PS: this post has been inspired by an excellent blog on PostgreSQL by Bruce Momjian

12cR2: unreproducible SPM and cursor bind awareness

In the previous article we knew that, as of Oracle 12cR2, a cursor can’t be kept bind aware when it is protected by a single enabled and accepted SPM plan baseline. In other words, the preceding article explained that if you want a cursor to be both bind aware and protected against any unexpected execution plan change, you need then to have at least two enabled and accepted SPM baselines for this cursor.

That’s said multiple enabled and accepted SPM plan doesn’t necessarily means multiple enabled, accepted and reproducible SPM plans. And, as this article will demonstrate it, this reproducible path seems to have been ignored by the Oracle optimizer group when disabling the bind awareness property explained in the previous article. The new 12cR2 internal code disabling and enabling ACS seems to be based only on the accepted and enabled property of the bind aware cursor SPM plan. Let’s demonstrate this with a simple example

Setting the Scenes

-- create table, index and gather statistics
create table t1
    (col1  number
    ,col2  varchar2(50)
    ,flag  varchar2(2));
 
 insert into t1
    select rownum
          ,lpad('X',50,'X')
          ,case when rownum = 1
            then 'Y1'
               when rownum = 2
            then 'Y2'
               when mod(rownum,2) = 0
            then 'N1'
            else 'N2'
           end
    from   dual
connect by rownum <= 100000;
 
create index i1 on t1(flag);

BEGIN 
  dbms_stats.gather_table_stats
      (user
     ,'t1'
     ,method_opt => 'for all columns size skewonly' 
     ,estimate_percent => dbms_stats.auto_sample_size );
END; 
/

-- generate a bind aware cursor
alter session set cursor_sharing=force;

select count(*), max(col2) from t1 where flag = 'Y1';
select count(*), max(col2) from t1 where flag = 'N1';
select count(*), max(col2) from t1 where flag = 'N1';
select count(*), max(col2) from t1 where flag = 'Y1';

Observation

The above script will generate a bind sensitive and bind aware cursor as shown below:

select
    sql_id
  ,child_number
  ,is_bind_sensitive 
  ,is_bind_aware
  ,executions
  ,invalidations
  ,object_status
from
   gv$sql 
where
   sql_id = '6fbvysnhkvugw'
and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS INVALIDATIONS OBJECT_STATUS      
------------- ------------ - - ---------- ------------- -------------
6fbvysnhkvugw            1 Y Y          1             0 VALID              
6fbvysnhkvugw            2 Y Y          1             0 VALID 

Let’s load these two execution plans (child number 1 and 2) into a SPM baseline and make few verifications:

declare
   rs pls_integer;
begin
   rs := dbms_spm.load_plans_from_cursor_cache('6fbvysnhkvugw');
end;
/

col plan_name format a30
select
  plan_name ,origin, enabled
from dba_sql_plan_baselines;

PLAN_NAME                      ORIGIN                        ENA
------------------------------ ----------------------------- ---
SQL_PLAN_d9tch6banyzg9616acf47 MANUAL-LOAD-FROM-CURSOR-CACHE YES
SQL_PLAN_d9tch6banyzg98576eb1f MANUAL-LOAD-FROM-CURSOR-CACHE YES

select
    sql_id
  ,child_number
  ,is_bind_sensitive 
  ,is_bind_aware
  ,executions
  ,invalidations
  ,object_status
from
   gv$sql 
where
   sql_id = '6fbvysnhkvugw'
and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS INVALIDATIONS OBJECT_STATUS      
------------- ------------ - - ---------- ------------- -------------------
6fbvysnhkvugw            1 Y Y          1             1 INVALID_UNAUTH     
6fbvysnhkvugw            2 Y Y          1             1 INVALID_UNAUTH

As you can see the creation of the two SPM plans has already invalidated the two underlying child cursors making them both not anymore shareable for the next execution(INVALID_UNAUTHORIZED).

Now that we have a bind aware cursor protected by two enabled and accepted SPM plans let’s launch it a couple of time in order see what will happen:

SQL> select count(*), max(col2) from t1 where flag = 'Y1';

  COUNT(*) MAX(COL2)                                         
---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> select * from table(dbms_xplan.display_cursor);

SQL_ID  6fbvysnhkvugw, child number 0
-------------------------------------
 
Plan hash value: 3625400295
-----------------------------------------------------
| Id  | Operation                    | Name | Rows  | 
-----------------------------------------------------
|   0 | SELECT STATEMENT             |      |       | 
|   1 |  SORT AGGREGATE              |      |     1 | 
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |     1 | 
|*  3 |    INDEX RANGE SCAN          | I1   |     1 | 
-----------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("FLAG"=:SYS_B_0)
 
Note
-----
   - SQL plan baseline SQL_PLAN_d9tch6banyzg98576eb1f used for this statement

As expected the apparition of the child number 0 above means that Oracle has effectively flushed out the previous couple of bind aware child cursor (1,2). We need now to go again through a warming up period for ACS to kick in as shown below:

select
    sql_id
  ,child_number
  ,is_bind_sensitive 
  ,is_bind_aware
  ,executions
  ,invalidations
  ,object_status
from
   gv$sql 
where
   sql_id = '6fbvysnhkvugw'
and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS INVALIDATIONS OBJECT_STATUS      
------------- ------------ - - ---------- ------------- -------------
6fbvysnhkvugw            1 Y N          1             1 VALID     

select count(*), max(col2) from t1 where flag = 'N1';
select count(*), max(col2) from t1 where flag = 'N1';
select count(*), max(col2) from t1 where flag = 'Y1';

select
    sql_id
  ,child_number
  ,is_bind_sensitive 
  ,is_bind_aware
  ,executions
  ,invalidations
  ,object_status
from
   gv$sql 
where
   sql_id = '6fbvysnhkvugw'
and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS INVALIDATIONS OBJECT_STATUS      
------------- ------------ - - ---------- ------------- -------------
6fbvysnhkvugw            2 Y Y          1             1 VALID              
6fbvysnhkvugw            3 Y Y          1             0 VALID    

We’ve, finally, succeeded building a situation with which we can proof that the Oracle optimizer group has ignored disabling ACS even when it is protected by a single reproducible SPM plan as the following will demonstrate:

-- disabling the i1 index so that the SPM plan SQL_PLAN_d9tch6banyzg98576eb1f 
-- will be irreproducible

alter index i1 invisible;

Setting the i1 index invisible, the index range scan SPM plan will inevitably be ignored by the CBO because it is not anymore reproducible. The Full table scan SPM plan will become the unique enforced execution plan whatever the bind variable selectivity will be as shown below:

SQL> select count(*), max(col2) from t1 where flag = 'Y1';

  COUNT(*) MAX(COL2)                                         
---------- --------------------------------------------------
         1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

SQL> select * from table(dbms_xplan.display_cursor);

SQL_ID  6fbvysnhkvugw, child number 2
-------------------------------------
select count(*), max(col2) from t1 where flag = :"SYS_B_0"
 
Plan hash value: 3724264953
---------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    54 |
---------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("FLAG"=:SYS_B_0)
 
Note
-----
   - SQL plan baseline SQL_PLAN_d9tch6banyzg9616acf47 used for this statement

When Oracle has detected that the index range scan SPM plan is not anymore reproducible it did two things:

a. invalidated and flushed out both index scan (child number 3) and full table scan(child number 2) cursor
b. created a new bind sensitive and not yet bind aware cursor n°2 as proofed below:

select
   sql_id
  ,child_number
  ,is_bind_sensitive 
  ,is_bind_aware
  ,executions
  ,invalidations
  ,object_status
from
   gv$sql 
where
   sql_id = '6fbvysnhkvugw'
and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS INVALIDATIONS OBJECT_STATUS      
------------- ------------ - - ---------- ------------- -------------
6fbvysnhkvugw            2 Y N          1             2 VALID  

While officially there are still two accepted and enabled SPM plan baselines, in fact, only one SPM plan can be effectively be used to guarantee unpredictable performance degradation. So why Oracle has not disabled the bind sensitiveness property of this cursor? Is Oracle not claiming that it is useless to keep ACS working in presence of a single SPM plan?

select
   child_number
  ,bucket_id
  ,count
from
   gv$sql_cs_histogram
where
   sql_id = '6fbvysnhkvugw'
and child_number = '2';

CHILD_NUMBER  BUCKET_ID      COUNT
------------ ---------- ----------
           2          0          1
           2          1          0
           2          2          0

Given the above situation we need only two extra executions of the same query at bucket_id n°1 or 2 and the cursor will become bind aware as shown below:

select count(*), max(col2) from t1 where flag = 'N1';
select count(*), max(col2) from t1 where flag = 'N1';

SQL> select
        sql_id
      ,child_number
      ,is_bind_sensitive 
      ,is_bind_aware
      ,executions
      ,invalidations
      ,object_status
    from
      gv$sql 
   where
     sql_id = '6fbvysnhkvugw'
   and is_shareable = 'Y';

SQL_ID        CHILD_NUMBER I I EXECUTIONS INVALIDATIONS OBJECT_STATUS      
------------- ------------ - - ---------- ------------- -------------
6fbvysnhkvugw            3 Y Y          1             1 VALID  

Summary

The developer at Oracle corporation has not considered stopping a cursor from being bind aware when one of its two SPM plans is indirectly rendered irreproducible. The internal code implemented by Oracle when deciding to stop ACS seems to be based only the Enabled and Accepted property of the corresponding SPM plan. Unfortunately an accepted and enabled SPM plan is not guaranteed to be reproducible. This is why we can still find, in 12cR2, a bind aware cursor protected by a single SPM plan.