jump to navigation

12.2 Index Deferred Invalidation (Atomica) May 30, 2017

Posted by Richard Foote in 12c Rel 2, 12c Release 2 New Features, Deferred Invalidation, Oracle Indexes.
2 comments

One of the useful little features quietly introduced with Oracle Database 12c Release 2 is the ability to now defer the invalidation of dependent SQL cursors when an index is created or modified. This can be useful when you say create a new index which you know will have no impact on any existing SQL statements. Rather than automatically invalidating all dependent SQL cursors, you can chose to defer such invalidations and avoid the expense of unnecessarily re-parsing dependent cursors.

A simple demo. We first create a little table and index on the ID column:

SQL> create table bowie (id number, code number, name varchar2(42));
Table created.

SQL> insert into bowie select rownum, mod(rownum,10000), 'DAVID BOWIE' from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE');

PL/SQL procedure successfully completed.

SQL> create index bowie_id_i on bowie(id);

Index created.

If we execute the following statement a number of times we’ll see there are no recursive calls, which confirms no hard parsing or SQL invalidations. One can also look at the associated SQL within v$SQL to confirm no additional invalidations.

SQL> select * from bowie where id = 42;

Execution Plan
----------------------------------------------------------
Plan hash value: 1405654398

--------------------------------------------------------------------------------------------------
| Id | Operation                           | Name       | Rows | Bytes | Cost(%CPU) | Time      |
--------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |            |    1 |    21 |      4 (0) |  00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |    1 |    21 |      4 (0) |  00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BOWIE_ID_I |    1 |       |       3 (0)|  00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=42)

Statistics
----------------------------------------------------------
  0 recursive calls
  0 db block gets
  5 consistent gets
  0 physical reads
  0 redo size
479 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed

If we say rebuild the index, we can see that we now have recursive calls due to the invalidation of this SQL. The new index structure “might” facilitate a change in the plan of this query and so Oracle will re-parse the statement:

SQL> alter index bowie_id_i rebuild;
Index altered.

SQL> select * from bowie where id=42;

Execution Plan
----------------------------------------------------------
Plan hash value: 1405654398

--------------------------------------------------------------------------------------------------
| Id | Operation                           | Name       | Rows | Bytes | Cost(%CPU) | Time      |
--------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |            |    1 |    21 |      4 (0) |  00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |    1 |    21 |      4 (0) |  00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BOWIE_ID_I |    1 |       |      3 (0) |  00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=42)

Statistics
----------------------------------------------------------
  3 recursive calls
  0 db block gets
 12 consistent gets
  2 physical reads
  0 redo size
479 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed

Oracle Database 12c Release 2 now gives us the option to defer these invalidations (with the DEFERRED INVALIDATION clause), as we might know for example that such a rebuild is not going to impact the plans of any existing SQL:

SQL> alter index bowie_id_i rebuild deferred invalidation;

Index altered.

SQL> select * from bowie where id=42;

Execution Plan
----------------------------------------------------------
Plan hash value: 1405654398

--------------------------------------------------------------------------------------------------
| Id | Operation                           | Name       | Rows | Bytes | Cost(%CPU) | Time      |
--------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |            |    1 |    21 |      4 (0) |  00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |    1 |    21 |      4 (0) |  00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BOWIE_ID_I |    1 |       |      3 (0) |  00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=42)

Statistics
----------------------------------------------------------
  0 recursive calls
  0 db block gets
  5 consistent gets
  2 physical reads
  0 redo size
479 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed

We see the index rebuild this time did not result in any recursive calls due to the invalidation of the SQL statement.

If we run an SQL with a predicate on the non-indexed CODE column:

SQL> select * from bowie where code=42;
100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1845943507

-----------------------------------------------------------------------------------
| Id | Operation                 | Name  | Rows | Bytes | Cost (%CPU) | Time      |
-----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |       |  100 |  2100 |     996 (1) |  00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | BOWIE |  100 |  2100 |     996 (1) |  00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - storage("CODE"=42)
    filter("CODE"=42)

Statistics
----------------------------------------------------------
   1 recursive calls
   0 db block gets
3603 consistent gets
   0 physical reads
   0 redo size
2050 bytes sent via SQL*Net to client
 566 bytes received via SQL*Net from client
   8 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
 100 rows processed

The CBO has no choice but to perform a Full Table Scan. If we now create an index on the CODE column:

SQL> create index bowie_code_i on bowie(code);
Index created.

We notice the SQL that only has a predicate on the ID is impacted by default:

SQL> select * from bowie where id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 1405654398

--------------------------------------------------------------------------------------------------
| Id | Operation                           | Name       | Rows | Bytes | Cost(%CPU) | Time      |
--------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |            |    1 |    21 |      4 (0) |  00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |    1 |    21 |      4 (0) |  00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BOWIE_ID_I |    1 |       |      3 (0) |  00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=42)

Statistics
----------------------------------------------------------
  1 recursive calls
  0 db block gets
  5 consistent gets
 12 physical reads
  0 redo size
479 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed

The new SQL with a predicate on the CODE column now uses the new index as we would hope due to it being invalidated and re-parsed:

SQL> select * from bowie where code=42;
100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 853003755

----------------------------------------------------------------------------------------------------
| Id | Operation                           | Name         | Rows | Bytes | Cost (%CPU) | Time      |
----------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |              |  100 |  2100 |     103 (0) |  00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE        |  100 |  2100 |     103 (0) |  00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BOWIE_CODE_I |  100 |       |       3 (0) |  00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("CODE"=42)

Statistics
----------------------------------------------------------
   1 recursive calls
   0 db block gets
 110 consistent gets
   2 physical reads
   0 redo size
3540 bytes sent via SQL*Net to client
 566 bytes received via SQL*Net from client
   8 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
 100 rows processed

If we now drop this index on the CODE column, but with the new deferred invalidation clause:

SQL> drop index bowie_code_i deferred invalidation;

Index dropped.

Even though deferred invalidation was specified, this clause has no impact on the SQL with the predicate on the CODE column. It previously referenced the dropped index and so has no choice but to become invalidated and be re-parsed. The new execution plan performs a Full Table Scan as it now again has no other viable option:

SQL> select * from bowie where code=42;
100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1845943507

-----------------------------------------------------------------------------------
| Id | Operation                 | Name  | Rows | Bytes | Cost (%CPU) | Time      |
-----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |       |  100 |  2100 |     996 (1) |  00:00:01 |
|* 1 | TABLE ACCESS STORAGE FULL | BOWIE |  100 |  2100 |     996 (1) |  00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - storage("CODE"=42)
    filter("CODE"=42)

Statistics
----------------------------------------------------------
  46 recursive calls
   0 db block gets
3641 consistent gets
   0 physical reads
   0 redo size
2050 bytes sent via SQL*Net to client
 566 bytes received via SQL*Net from client
   8 SQL*Net roundtrips to/from client
   6 sorts (memory)
   0 sorts (disk)
 100 rows processed

If we now re-create the index on the CODE column, again with deferred invalidation:

SQL> create index bowie_code_i on bowie(code) deferred invalidation;

Index created.

We notice the SQL with the predicate on the ID column has not been impacted by either the drop or the subsequent re-creation of the index on the CODE column. It still has no recursive calls. Any invalidation on this SQL would have been unnecessary as the execution plan can’t be impacted by such an index on the CODE column:

SQL> select * from bowie where id=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 1405654398

--------------------------------------------------------------------------------------------------
| Id | Operation                           | Name       | Rows | Bytes | Cost(%CPU) | Time      |
--------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |            |    1 |    21 |      4 (0) |  00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE      |    1 |    21 |      4 (0) |  00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BOWIE_ID_I |    1 |       |      3 (0) |  00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=42)

Statistics
----------------------------------------------------------
  0 recursive calls
  0 db block gets
  5 consistent gets
  0 physical reads
  0 redo size
479 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
  2 SQL*Net roundtrips to/from client
  0 sorts (memory)
  0 sorts (disk)
  1 rows processed

But we look at the SQL that does have a predicate on the CODE column, we notice that it likewise has not been invalidated (as we explicitly stated deferred invalidation when we re-created the index). The SQL still performs a Full Table Scan as evidenced by the number of consistent gets (3603, the same as with the Full Table Scan) and that there are no recursive SQL calls:

SQL> select * from bowie where code=42;
100 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 853003755

---------------------------------------------------------------------------------------------------
| Id | Operation                           | Name         | Rows | Bytes | Cost (%CPU) | Time     |
---------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |              |  100 |  2100 |      03 (0) | 00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE        |  100 |  2100 |      03 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BOWIE_CODE_I |  100 |       |       3 (0) | 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("CODE"=42)

Statistics
----------------------------------------------------------
   0 recursive calls
   0 db block gets
3603 consistent gets
   0 physical reads
   0 redo size
2050 bytes sent via SQL*Net to client
 566 bytes received via SQL*Net from client
   8 SQL*Net roundtrips to/from client
   0 sorts (memory)
   0 sorts (disk)
 100 rows processed

 

SQL*Plus autotrace which displays the current SQL plan (but not necessarily the actual execution plan) shows that the CBO would have chosen to use the new index if it had been allowed to invalidate this cursor.

So like many features, deferred invalidation can be useful if used well but can be problematic if it’s not implemented and used appropriately 🙂