By Franck Pachot

.
Do you think that online operations are available only on Enterprise Edition?
That changed in 12c, there is an operation that is online (without the need for the ONLINE keyword) and available in Standard Edition and a very important feature for performance tuning: invisible indexes.

Example

I’ll show it from the SCOTT schema where I’ve added an index on EMP(HIREDATE):


SQL> info+ EMP
TABLE: EMP
         LAST ANALYZED:
         ROWS         :
         SAMPLE SIZE  :
         INMEMORY     :DISABLED
         COMMENTS     :
 
Columns
NAME         DATA TYPE           NULL  DEFAULT    LOW_VALUE   HIGH_VALUE   NUM_DISTINCT   HISTOGRAM
----------  -------------------  ----  ---------  ----------- -----------  -------------  ---------
*EMPNO       NUMBER(4,0)         No
 ENAME       VARCHAR2(10 BYTE)   Yes
 JOB         VARCHAR2(9 BYTE)    Yes
 MGR         NUMBER(4,0)         Yes
 HIREDATE    DATE                Yes                  .....       .....
 SAL         NUMBER(7,2)         Yes
 COMM        NUMBER(7,2)         Yes
 DEPTNO      NUMBER(2,0)         Yes
 
Indexes
INDEX_NAME          UNIQUENESS  STATUS  FUNCIDX_STATUS  COLUMNS   COLUMN_EXPRESSION
------------------  ----------  ------  --------------  --------  -----------------
SCOTT.PK_EMP        UNIQUE      VALID                   EMPNO
SCOTT.EMP_HIREDATE  NONUNIQUE   VALID                   HIREDATE

If you don’t know where that ‘info+’ is coming from, then you should read about sqlcl.

It’s always good to remove unnecessary indexes

Ok, imagine that you think that this EMP_HIREDATE index is not useful. Do you drop it? Or the fear of unexpected regression wins against the will to clean up. If you drop it and you see a regression, then you will have to create it back. It can take a lot of time on a big table. Do you choose to create it offline, blocking everybody, but trying to have the index back as quickly as possible?
How long will it take? Are you sure that you have enough temp space?

Well, since 11g you have a very nice feature: invisible index. It’s still maintained but invisible to the optimizer. Just make the index invisible and if you see any regression you can bring it back in seconds. But are you sure of that?

In 11g, ALTER INDEX VISIBLE requires an exclusive lock. It can be quick and nobody see it. But imagine that you have a lot of DML activity on the table, and on tables linked to it by referential integrity. That DML activity acquires Row-X locks. Your ALTER INDEX VISIBLE will wait. But it requests an exclusive lock. Then all new sessions that wants to have activity on those tables will be blocked. This is not good. Especially in that situation: if you want to bring back the index as visible, that’s probably because you had some performance problems. It not the right time to add lock issues…

12c

In 12c, new feature, it’s online. which means that we don’t need any Share or Exclusive lock.
Let’s prove it.
I’m tracing sql_trace and enqueues


SQL> alter session set events='10046 trace name context forever, level 1 : 10704 trace name context forever, level 3' tracefile_identifier='InvisibleIndex';
Session altered.

I have some queries using the index:


SQL> select count(*) from EMP where HIREDATE>sysdate-1;
 
COUNT(*)
--------
0
 
SQL> select * from table(dbms_xplan.display_cursor);
 
PLAN_TABLE_OUTPUT
SQL_ID  gy8sag39zkrxr, child number 0
-------------------------------------
select count(*) from EMP where HIREDATE>sysdate-1
 
Plan hash value: 3798098543
 
----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |              |     1 |     9 |            |          |
|*  2 |   INDEX RANGE SCAN| EMP_HIREDATE |     1 |     9 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("HIREDATE">SYSDATE@!-1)

the query is here:


SQL> select sql_id,child_number,invalidations,plan_hash_value from V$SQL where plan_hash_value=3798098543;
 
SQL_ID         CHILD_NUMBER  INVALIDATIONS  PLAN_HASH_VALUE
-------------  ------------  -------------  ---------------
gy8sag39zkrxr  0             0              3,798,098,543

But I didn’t noticed it and think the index is not useful.
I minimize the risks: I make it invisible.


SQL> alter index EMP_HIREDATE invisible;
 
Index EMP_HIREDATE altered.

Now future queries are doing a full table scan:


SQL> select count(*) from EMP where HIREDATE>sysdate-1;
 
COUNT(*)
--------
0
 
SQL> select * from table(dbms_xplan.display_cursor);
 
PLAN_TABLE_OUTPUT
SQL_ID  gy8sag39zkrxr, child number 0
-------------------------------------
select count(*) from EMP where HIREDATE>sysdate-1
 
Plan hash value: 2083865914
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |     9 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("HIREDATE">SYSDATE@!-1)

We see that the cursor has been invalidated:


SQL> select sql_id,child_number,invalidations,plan_hash_value from V$SQL where sql_id='gy8sag39zkrxr';
 
SQL_ID         CHILD_NUMBER  INVALIDATIONS  PLAN_HASH_VALUE
-------------  ------------  -------------  ---------------
gy8sag39zkrxr  0             1              2,083,865,914

If I have any issue with that, I can bring back the index visible:


SQL> alter index EMP_HIREDATE visible;
&nbsp
Index EMP_HIREDATE altered.

and after running the query again, I can check that the cursor has been invalidated again and is now using the range scan:


SQL> select sql_id,child_number,invalidations,plan_hash_value from V$SQL where sql_id='gy8sag39zkrxr';
 
SQL_ID         CHILD_NUMBER  INVALIDATIONS  PLAN_HASH_VALUE
-------------  ------------  -------------  ---------------
gy8sag39zkrxr  0             2              3,798,098,543

It’s online

Nothing new here if you know the 12c new features (and if you don’t we have a training for that). But let’s stop the trace and grep the ‘get lock’ from the dump:


SQL> alter session set events='10046 trace name context off : 10704 trace name context off ';
 
Session altered.

I need the OBJECT_ID of my table in hexadecimal to find it in the dump:


SQL> column object_id new_value object_id
SQL> select object_name,to_char(object_id,'FM0XXXXXXX') object_id from dba_objects where owner='SCOTT';
 
OBJECT_NAME   OBJECT_ID
------------  ---------
PK_DEPT       0001677D
DEPT          0001677C
EMP           0001677E
PK_EMP        0001677F
BONUS         00016780
SALGRADE      00016781
EMP_HIREDATE  00016782

Get the tracefile


SQL> column tracefile new_value tracefile
SQL> select tracefile from v$process where addr=(select paddr from v$session where sid=sys_context('USERENV','SID'));
 
TRACEFILE
------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/se2/SE2/trace/SE2_ora_6710_InvisibleIndex.trc
 
SQL> host mv &tracefile last.trc

and filter what I’m looking for: ‘get lock’ for my tables:


host grep -A 0 -E 'ksqgtl [*]{3} TM-000167|ksqrcl: TM-000167|ksqcnv: TM-000167|^alter' last.trc
ksqgtl *** TM-0001677E-00000000-00000003-00000000 mode=2 flags=0x400 timeout=0 ***
alter index EMP_HIREDATE invisible
ksqrcl: TM-0001677E-00000000-00000003-00000000
ksqgtl *** TM-0001677E-00000000-00000003-00000000 mode=2 flags=0x400 timeout=0 ***
alter index EMP_HIREDATE visible
ksqrcl: TM-0001677E-00000000-00000003-00000000

This is the proof that both statements require only the lowest table lock: mode=2 is Row-S which is compatible with all concurrent DML.
this is an only operation. But there is more.

It’s available in SE

Yes, I’m in Standard Edition here:


SQL> exit
Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

11g

If you want to see the behaviour in 11g:


ksqgtl *** TM-0001bf64-00000000 mode=6 flags=0x401 timeout=0 ***
alter index EMP_HIREDATE invisible
ksqgtl *** TM-0001bf64-00000000 mode=6 flags=0x401 timeout=0 ***
alter index EMP_HIREDATE visible

mode=6 is exclusive lock. You don’t want to do that when DML is running, especially when you already have a problem to fix – and this is the goal of ALTER INDEX VISIBLE.

DROP INDEX ONLINE

Still in the 12c new features, you can drop an index online, requiring only a Row-S lock on the table instead of eXclusive lock.
Here is the 10704 trace:


drop index EMP_HIREDATE online
ksqgtl *** TM-0001677E-00000000-00000003-00000000 mode=2 flags=0x400 timeout=0 ***
ksqrcl: TM-0001677E-00000000-00000003-00000000
ksqrcl: TM-0001677E-00000000-00000003-00000000

And you know what? I’m still in Standard Edition.

So what?

We can get rid of indexes that are not needed. We can make them invisible for a while and then drop them.
And we can do it online, even it Standard Edition.
However, be careful. Indexes are not used only by the optimizer. They serve as a structure to lock a range of values when you delete from a parent table, in order to prevent any current insert to become orphans. If you make the index invisible, they are still used for that. When you drop them, a table lock will be needed for those cases. More about that at #DOAG2015 or at #TECH15.