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:

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:

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:

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

The index is marked as having orphaned entries after that:

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:

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):

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:

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 |
---------------------------------------------------------------------------------------