Asynchronous Global Index Maintenance effects

I think Asynchronous Global Index Maintenance is one of the most exciting features in Oracle Database 12c.
I would like to blog about one particular case when that feature can cause performance problems.
Let's create some test data:

?

1 2 3 4 5 6 7 8 9 10 11

SQL> create table t   2  partition by range(owner)   3  (   4    partition values less than ('SYS'),   5    partition values less than (maxvalue)   6  )   7  as   8  select *   9    from dba_objects; SQL> SQL> create index t_name_i on t(object_name);

Consider the following query:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SQL> explain plan for   2  select max(object_name)   3    from t; SQL> select *   2    from table(dbms_xplan.display);     PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------- Plan hash value: 2886567490   --------------------------------------------------------------------------------------- | Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT           |          |     1 |    25 |     3   (0)| 00:00:01 | |   1 |  SORT AGGREGATE            |          |     1 |    25 |            |          | |   2 |   INDEX FULL SCAN (MIN/MAX)| T_NAME_I |     1 |    25 |     3   (0)| 00:00:01 | ---------------------------------------------------------------------------------------

It's quite simple and the queries like that are common in my environment. For instance, they are used to monitor application activities: "select max(date_column) from some_table" and the stuff alike.
Now I'm going to drop a partition of that table:

?

1	SQL> alter table t drop partition for ('X') update indexes;

The index is marked as having orphaned entries after that:

?

1 2 3 4 5 6 7 8

SQL> select index_name, orphaned_entries   2    from ind   3   where table_name='T';     INDEX_NAME                     ORPHANED_ ------------------------------ --------- T_NAME_I                       YES

Causing some important changes in the plan:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

SQL> explain plan for   2  select max(object_name)   3    from t; SQL> select *   2    from table(dbms_xplan.display);     PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------- Plan hash value: 3249307143   ---------------------------------------------------------------------------------- | Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------- |   0 | SELECT STATEMENT      |          |     1 |    25 |   148   (1)| 00:00:01 | |   1 |  SORT AGGREGATE       |          |     1 |    25 |            |          | |*  2 |   INDEX FAST FULL SCAN| T_NAME_I | 96566 |  2357K|   148   (1)| 00:00:01 | ----------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - filter(TBL$OR$IDX$PART$NUM("T",0,8,0,"T".ROWID)=1)

The filter clearly was occured due to orphaned index entries which is confirmed by looking into the CBO traces (event 10053):

?

1 2	Added Filter for Orphaned Entries of Index T_NAME_I: TBL$OR$IDX$PART$NUM("T",0,8,0,"T".ROWID)=1

The IFFS (INDEX FAST FULL SCAN) would cause a huge amount of I/O being executed against large indexes.
For instance, some queries started scanning multi-gigabyte indexes in my database after I had dropped a single small partition while executing periodic maintenance tasks.
Of course, in such case Global Index Maintenance can't be delayed and an index synchronization procedure must be executed ASAP:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

SQL> alter index t_name_i coalesce cleanup; SQL> select index_name, orphaned_entries   2    from ind   3   where table_name='T';     INDEX_NAME                     ORPHANED_ ------------------------------ --------- T_NAME_I                       NO SQL> explain plan for   2  select max(object_name)   3    from t; SQL> select * from table(dbms_xplan.display);     PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------- Plan hash value: 2886567490   --------------------------------------------------------------------------------------- | Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT           |          |     1 |    25 |     3   (0)| 00:00:01 | |   1 |  SORT AGGREGATE            |          |     1 |    25 |            |          | |   2 |   INDEX FULL SCAN (MIN/MAX)| T_NAME_I |     1 |    25 |     3   (0)| 00:00:01 | ---------------------------------------------------------------------------------------