04 Nov Indices Invisibles
En este proceso de actualizacion en el que estoy , hemos detectado que varios queries que antes corrían con un costo relativamente bajo, ahora están corriendo con un costo, bastante alto, esto nos ha llevado a probar con Indices Invisibles, que es una nueva función de 11g.
Un índice invisible por default es, como su nombre lo menciona, invisible para el optimizador, esto te permite probar quitar o añadir un índice sin que afecte el actual plan de ejecución del query.
Con el esquema de HR de ejemplos de oracle vamos a probar como funciona:
1.-Vamos a Ver el plan de ejecución del siguiente query
select * from employees where employee_id=101;
TESTDB >EXPLAIN PLAN FOR
2 select * from employees where employee_id=101;
Explained.
TESTDB >SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 293722525
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
2 - access("EMPLOYEE_ID"=101)
14 rows selected.
2.-Como podemos ver, el query esta utilizando el índice “EMP_EMP_ID_PK” y tiene un acceso por “INDEX ROWID“
3.- Ahora vamos a convertir el índice de visible a invisible
TESTDB >alter index EMP_EMP_ID_PK invisible;
Index altered.
4.-Ya que convertimos el índice en invisible,vamos a volver a ver el plan de ejecución del query
TESTDB >EXPLAIN PLAN FOR
2 select * from employees where employee_id=101;
Explained.
TESTDB >SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1342275408
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 69 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
1 - filter("EMPLOYEE_ID"=101)
13 rows selected.
5.-Como podemos ver arriba, el optimizador ya no esta utilizando el índice “EMP_EMP_ID_PK” y tiene un acceso “FULL TABLE“.
Podemos ver abajo que el índice todavía existe, solamente esta en modo invisible. Esto te permite dejar de utilizar el índice, sin tener que tirarlo, ya que existe la posibilidad de que recrearlo te tome tiempo y esfuerzo. Toma en cuenta que el índice todavía va a estar ocupando espacio en la base de datos.
TESTDB >select status from all_objects where object_name='EMP_EMP_ID_PK' and object_type='INDEX';
STATUS
---------------------
VALID
TESTDB >select visibility from user_indexes where index_name = 'EMP_EMP_ID_PK';
VISIBILITY
---------------------------
INVISIBLE
6.-De la misma manera, ni diciéndole al optimizador que utilice el índice, lo va a utilizar.
TESTDB >EXPLAIN PLAN FOR
2 select /*+ index(EMP_EMP_ID_PK) */ * from employees where employee_id=101;
Explained.
TESTDB >SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1342275408
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 69 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
1 - filter("EMPLOYEE_ID"=101)
13 rows selected.
7.- Lo que si podemos hacer es habilitar el parámetro optimizer_use_invisible_indexes para nuestra sesión para que el optimizador lo vuelva a utilizar.
TESTDB >alter session set optimizer_use_invisible_indexes = true;
Session altered.
TESTDB >EXPLAIN PLAN FOR
2 select * from employees where employee_id=101;
Explained.
TESTDB >SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 293722525
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
2 - access("EMPLOYEE_ID"=101)
14 rows selected.
TESTDB >alter session set optimizer_use_invisible_indexes = true;
Session altered.
TESTDB >select visibility from user_indexes where index_name = 'EMP_EMP_ID_PK';
VISIBILITY
---------------------------
INVISIBLE
Conclusión
Como podemos ver, esto puede ser de gran utilidad cuando trabajamos con índices, ya que también desde que creamos el índice lo podemos hacer a este invisible para el optimizador, permitiéndonos crear un índice y utilizarlo hasta que deseemos hacerlo. Toma en cuenta de que aunque un índice sea invisible, este se va a comportar de la misma manera que un índice visible.
Sorry, the comment form is closed at this time.