Oracle Scratchpad

June 19, 2014

Delete Costs

Filed under: Bugs,CBO,Execution plans,Hints,Indexing,Oracle,Performance — Jonathan Lewis @ 6:18 pm GMT Jun 19,2014

One of the quirky little anomalies of the optimizer is that it’s not allowed to select rows from a table after doing an index fast full scan (index_ffs) even if it is obviously the most efficient (or, perhaps, least inefficient) strategy. For example:


create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level <= 1e4
)
select
	rownum			id,
	mod(rownum,100)		n1,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 1e5
;

create index t1_i1 on t1(id, n1);
alter table t1 modify id not null;

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		method_opt	 => 'for all columns size 1'
	);
end;
/

explain plan for
select /*+ index_ffs(t1) */ max(padding) from t1 where n1 = 0;

select * from table(dbms_xplan.display(null,null,'outline -note'));

In this case we can see that there are going to be 1,000 rows where n1 = 0 spread evenly across the whole table so a full tablescan is likely to be the most efficient strategy for the query, but we can tell the optimizer to do an index fast full scan with the hint that I’ve shown, and if the hint is legal (which means there has to be at least one column in it declared as not null) the optimizer should obey it. So here’s the plan my hinted query produced:


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   104 |   207   (4)| 00:00:02 |
|   1 |  SORT AGGREGATE    |      |     1 |   104 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |  1000 |   101K|   207   (4)| 00:00:02 |
---------------------------------------------------------------------------

We’d have to examine the 10053 trace file to be certain, but it seems the optimizer won’t consider doing an index fast full scan followed by a trip to the table for a select statement (in passing, Oracle would have obeyed the skip scan – index_ss() – hint). It’s a little surprising then that the optimizer will obey the hint for a delete:


explain plan for
delete /*+ index_ffs(t1) cluster_by_rowid(t1) */ from t1 where n1 = 0;

select * from table(dbms_xplan.display(null,null,'outline -note'));

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |       |  1000 |  8000 |    38  (11)| 00:00:01 |
|   1 |  DELETE               | T1    |       |       |            |          |
|*  2 |   INDEX FAST FULL SCAN| T1_I1 |  1000 |  8000 |    38  (11)| 00:00:01 |
-------------------------------------------------------------------------------

You might note three things from this plan. First, the optimizer can consider a fast full scan followed by a table visit (so why can’t we do that for a select); secondly that the cost of the delete statement is only 38 whereas the cost of the full tablescan in the earlier query was much larger at 207 – surprisingly Oracle had to be hinted to consider this fast full scan path, despite the fact that the cost was cheaper than the cost of the tablescan path it would have taken if I hadn’t included the hint; finally you might note the cluster_by_rowid() hint in the SQL – there’s no matching “Sort cluster by rowid” operation in the plan, even though this plan came from 11.2.0.4 where the mechanism and hint are available.

The most interesting of the three points is this: there is a bug recorded for the second one (17908541: CBO DOES NOT CONSIDER INDEX_FFS) reported as fixed in 12.2 – I wonder if this means that an index fast full scan followed by table access by rowid will also be considered for select statements in 12.2.

Of course, there is a trap – and something to be tested when the version (or patch) becomes available. Why is the cost of the delete so low (only 38, the cost of the index fast full scan) when the number of rows to be deleted is 1,000 and they’re spread evenly through the table ? It’s because the cost of a delete is actually calculated as the cost of the query: “select the rowids of the rows I want to delete but don’t worry about the cost of going to the rows to delete them (or the cost of updating the indexes that will have to be maintained, but that’s a bit irrelevant to the choice anyway)”.

So when Oracle does do a delete following an index fast full scan in 12.2, will it be doing it because it’s the right thing to do, or because it’s the wrong thing ?

To be continued … (after the next release/patch).

 

7 Comments »

  1. What happens if you specify either the index name or indexed column in the hint?

    Comment by Jeff Jacobs, Oracle Ace — June 19, 2014 @ 6:58 pm GMT Jun 19,2014 | Reply

    • Jeff,

      It makes no difference – using either the index name or index description, the select won’t do an index_ffs() followed by “table access by rowid”.

      Comment by Jonathan Lewis — June 19, 2014 @ 8:19 pm GMT Jun 19,2014 | Reply

  2. Hi Jonathan,

    > surprisingly Oracle had to be hinted to consider this fast full scan path, despite the fact that the cost was cheaper than the cost of the tablescan path it would have taken if I hadn’t included the hint

    It seems like Oracle considers the fact of the different index maintenance in such cases. Alexander Anokhin has written a nice blog post about this ( http://alexanderanokhin.wordpress.com/deferred-index-maintenance , even if it is about INDEX FULL SCAN / INDEX FAST FULL SCAN ) and makes an assumption that this could be a heuristic based CBO feature. He also references to some of your older blog posts in there :-))

    Maybe this additional information is useful for some of frequent blog followers.

    Regards
    Stefan

    Comment by Stefan Koehler — June 20, 2014 @ 9:42 am GMT Jun 20,2014 | Reply

  3. Jonathan,

    I have tested the model in 11.2.0.3.0 and 10.2.0.4.
    In the first mentioned release I got an index skip scan (with or without using the index_ffs(t1) hint ) as shown below:

    ---------------------------------------------------------------------------
    | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)|
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |       |     1 |   104 |   282   (1)|
    |   1 |  SORT AGGREGATE              |       |     1 |   104 |            |
    |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |  1000 |   101K|   282   (1)|
    |*  3 |    INDEX SKIP SCAN           | T1_I1 |  1000 |       |   266   (1)|
    ---------------------------------------------------------------------------
    Outline Data
    -------------
      /*+
          BEGIN_OUTLINE_DATA
          INDEX_SS(@"SEL$1" "T1"@"SEL$1" ("T1"."ID" "T1"."N1"))
          OUTLINE_LEAF(@"SEL$1")
          ALL_ROWS
          DB_VERSION('11.2.0.3')
          OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
          IGNORE_OPTIM_EMBEDDED_HINTS
          END_OUTLINE_DATA
      */
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       3 - access("N1"=0)
           filter("N1"=0)
    

    And what seems strange is that the 10053 trace file is showing that the index_ffs hint has been used (used = 1)

    Dumping Hints
    =============
      atom_hint=(@=0x7f78f7a5aea8 err=0 resol=1 used=1 token=846 org=1 lvl=3 txt=INDEX_FFS ("T1") )
    

    Additionally it seems that the CBO doesn’t consider evaluating the cost of using the index ffs option. It evaluates only the cost of an index skip scan versus the table scan cost

    Access Path: index (skip-scan)
        SS sel: 0.010000  ANDV (#skips): 100000.000000
        SS io: 264.000000 vs. table scan io: 486.000000
        Skip Scan chosen
      Access Path: index (SkipScan)
        Index: T1_I1
        resc_io: 281.00  resc_cpu: 2391125
        ix_sel: 0.010000  ix_sel_with_filters: 0.010000 
        Cost: 282.65  Resp: 282.65  Degree: 1
      Best:: AccessPath: IndexRange
      Index: T1_I1
             Cost: 282.65  Degree: 1  Resp: 282.65  Card: 1000.00  Bytes: 0 

    While in the 10.2.0.4. I got an INDEX FULL SCAN (using the index_ffs otherwise I got a full table scan) instead of the 11.0.2.3 INDEX SKIP SCAN as shown below:

    ---------------------------------------------------------------------------
    | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)|
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |       |     1 |   104 |   290   (4)|
    |   1 |  SORT AGGREGATE              |       |     1 |   104 |            |
    |   2 |   TABLE ACCESS BY INDEX ROWID| T1    |   990 |   100K|   290   (4)|
    |*  3 |    INDEX FULL SCAN           | T1_I1 |   990 |       |   273   (3)|
    ---------------------------------------------------------------------------
     
    Outline Data
    -------------
      /*+
          BEGIN_OUTLINE_DATA
          INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."ID" "T1"."N1"))
          OUTLINE_LEAF(@"SEL$1")
          ALL_ROWS
          OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
          IGNORE_OPTIM_EMBEDDED_HINTS
          END_OUTLINE_DATA
      */
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       3 - access("N1"=0)
           filter("N1"=0)
    

    However, I didn’t noticed any difference for the delete statement when compared to the version you have used (11.0.2.4) and the versions I have tested i.e. 11.0.2.3 and 10.2.0.4

    Best Regards

    Comment by hourim — June 24, 2014 @ 10:41 am GMT Jun 24,2014 | Reply

    • Hi Hourim,

      I have also the tested the above case on 10.2.0.1, 11.2.0.2.0 and 12.1.0.1
      And got the same result as that of yours on 10g and 11g. On 12c also optimizer is choosing skip scan
      Below is the plan from 12c

      PLAN_TABLE_OUTPUT
      ----------------------------------------------------------------------------------------------
      Plan hash value: 2973688329
      
      ----------------------------------------------------------------------------------------------
      | Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                     |       |     1 |   104 |   281   (0)| 00:00:01 |
      |   1 |  SORT AGGREGATE                      |       |     1 |   104 |            |          |
      |   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1    |  1000 |   101K|   281   (0)| 00:00:01 |
      |*  3 |    INDEX SKIP SCAN                   | T1_I1 |  1000 |       |   265   (0)| 00:00:01 |
      ----------------------------------------------------------------------------------------------
      
      Outline Data
      -------------
      
        /*+
            BEGIN_OUTLINE_DATA
            BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")
            INDEX_SS(@"SEL$1" "T1"@"SEL$1" ("T1"."ID" "T1"."N1"))
            OUTLINE_LEAF(@"SEL$1")
            ALL_ROWS
            DB_VERSION('12.1.0.1')
            OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
            IGNORE_OPTIM_EMBEDDED_HINTS
            END_OUTLINE_DATA
        */
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         3 - access("N1"=0)
             filter("N1"=0)
      

      and below is from 10053 event trace file in 12c

        Access Path: index (skip-scan)
          SS scan sel: 0.010000  SS filter sel: 0.010000  ANDV (#skips): 100000.000000
          SS io: 264.000000 vs. table scan io: 440.000000
          Skip Scan chosen
        Access Path: index (SkipScan)
          Index: T1_I1
          resc_io: 281.00  resc_cpu: 2391125
          ix_sel: 0.010000  ix_sel_with_filters: 0.010000 
          Cost: 281.06  Resp: 281.06  Degree: 1
        Best:: AccessPath: IndexRange
        Index: T1_I1
               Cost: 281.06  Degree: 1  Resp: 281.06  Card: 1000.00  Bytes: 0
      

      Warm Regards,

      Comment by jagdeepsangwan — June 24, 2014 @ 12:34 pm GMT Jun 24,2014 | Reply

  4. Hello Jonathan,

    The most interesting of the three points is this: there is a bug recorded for the second one (17908541: CBO DOES NOT CONSIDER INDEX_FFS) reported as fixed in 12.2 – I wonder if this means that an index fast full scan followed by table access by rowid will also be considered for select statements in 12.2.

    The bug fix is included into 12.1.0.2 as well, but this does not change a thing for SELECT.

    Comment by Timur Akhmadeev — July 25, 2014 @ 1:31 pm GMT Jul 25,2014 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,430 other followers