This was first published on https://blog.dbi-services.com/invisible-indexes-its-online-in-12c-even-in-standard-edition (2015-09-14)
Republishing here for new followers. The content is related to the the versions available at the publication date
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.
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 HIREDATEIf you don’t know where that ‘info+’ is coming from, then you should read about sqlcl.
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…
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,543But 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,914If I have any issue with that, I can bring back the index visible:
SQL> alter index EMP_HIREDATE visible;   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
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 00016782Get 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.trcand 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-00000000This 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.
Yes, I’m in Standard Edition here:
SQL> exit Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
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 visiblemode=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.
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-00000000And you know what? I’m still in Standard Edition.
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.
Nice test, and yes it looks like it’s there. Index online rebuild is not ok according the license document, so inevitable getting some odd feelings about this