Para melhor visualização, recomendo resolução de no mínimo 1280 x 800 e navegador Mozilla Firefox


segunda-feira, 7 de julho de 2008

Índices invisíveis? Abordando uma nova funcionalidade do Oracle 11g

Por Eduardo Legatti

Olá,

Imagine uma tabela e um índice. Se quisermos que durante o processamento de uma sentença SQL o otimizador ignore o índice ao acessar os dados da tabela, teremos que dropar o índice ou marcá-lo como inutilizável. Se utilizarmos a primeira alternativa, e caso houver uma degradação de performance da query pelo fato do índice ter sido dropado, e quisermos que o mesmo seja novamente utilizado, teremos que recriá-lo (CREATE INDEX ...). Agora, se utilizarmos a segunda alternativa, então teremos apenas que reconstruí-lo (ALTER INDEX ... REBUILD). A partir do Oracle 10g, o parâmetro skip_unusable_indexes que pode ser modificado tanto em nível de sessão como em nível de sistema, foi introduzido de forma que se o mesmo estiver setado como TRUE (valor default), o otimizador CBO irá ignorar qualquer índice marcado como inutilizável (unusuable) suprimindo o erro ORA-01502 caso o Oracle tente acessar o índice. Este recurso é útil porque não teremos mais que dropar um índice, mas apenas marcá-lo como inutilizável. A desvantagem desta abordagem é que, se quiséssemos que o otimizador enxergasse novamente o índice, teríamos que reconstruí-lo (rebuild), o que poderia causar uma overhead desnecessária.

A partir do Oracle 11g, um novo recurso foi adicionado ao gerenciamento de índices permitindo que um índice fique invisível ou não ao otimizador. Caso uma degradação de performance seja notada ao marcar um índice como invisível, poderemos então, marcá-lo novamente como visível sem precisar ter que reconstruí-lo. Portanto, no Oracle 11g um índice marcado como invisível será invisível ao otimizador, a não ser que o parâmetro optimizer_use_invisible_indexes que pode ser modificado tanto em nível de sessão (ALTER SESSION ...) como em nível de sistema (ALTER SYSTEM ...), seja setado para TRUE. Aliás, o valor padrão deste parâmetro é FALSE. Abaixo, irei demonstrar tanto a abordagem de marcar um índice como inutilizável, como a de marcar um índice como invisível no Oracle 11g. Vamos então a um exemplo prático:

-- Irei criar uma tabela de teste chamada T1
SQL> create table t1 (id number);

Tabela criada.

-- irei criar um índice no campo ID
SQL> create index i_t1 on t1 (id);

Índice criado.

-- Irei popular a tabela com valores aleatórios
SQL> insert into t1 select level from dual
  2  connect by level <= 10000;   
10000 linhas criadas.
SQL> commit;
Commit concluído.
-- Verificando a configuração atual SQL> show parameter skip_unusable_indexes NAME TYPE VALUE ------------------------------- ----------- ----------------------------- skip_unusable_indexes boolean TRUE -- Gerando o plano de execução do SQL SQL> explain plan for 2 select * from t1 where id = 100; Explicado. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------- Plan hash value: 2966378588 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| I_T1 | 1 | 13 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------

Podemos ver no plano de execução acima, que o otimizador utilizou o índice I_T1. O que acontecerá se marcamos o índice como inutilizável e setarmos o parâmetro skip_unusable_indexes para FALSE?

SQL> alter session set skip_unusable_indexes = FALSE;

Sessão alterada.

SQL> alter index i_t1 unusable;

Índice alterado.

-- Verificando o estado do índice
SQL> select index_name,status from user_indexes where index_name = 'I_T1';

INDEX_NAME                     STATUS
------------------------------ --------
I_T1                           UNUSABLE

SQL> explain plan for
  2  select * from t1 where id = 100;

explain plan for
*
ERRO na linha 1:
ORA-01502: índice 'TEST.I_T1' ou a sua partição está em estado não-utilizável


Podemos ver acima que ao tentar acessar o índice, o Oracle emitiu o erro ORA-01502. Então, vamos setar novamente o parâmetro skip_unusable_indexes para TRUE:


SQL> alter session set skip_unusable_indexes = TRUE;

Sessão alterada.

SQL> explain plan for
  2  select * from t1 where id = 100;

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Podemos ver acima, que o otimizador não considerou o índice I_T1 no plano de execução gerado, o que significa que o índice foi totalmente ignorado pelo mesmo. Se quisermos que o índice seja novamente utilizado pelo otimizador, teremos que reconstruir o índice.

-- Reconstruindo o índice
SQL> alter index i_t1 rebuild;

Índice alterado.

-- Verificando o estado do índice
SQL> select index_name,status from user_indexes where index_name = 'I_T1';

INDEX_NAME                     STATUS
------------------------------ --------
I_T1                           VALID


Agora, irei mostrar o conceito de índices invisíveis no Oracle 11g, na qual não precisaremos mais nos preocupar em reconstruir os índices inválidos ou marcados como inutilizáveis.

SQL> select * from v$version;

BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

-- Verificando a configuração atual
SQL> show parameter visible

NAME                               TYPE        VALUE
---------------------------------- ----------- --------------------------
optimizer_use_invisible_indexes    boolean     FALSE

-- Verificando a visibilidade do índice
SQL> select index_name,visibility from user_indexes where index_name='I_T1';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
I_T1                           VISIBLE


Podemos ver no resultado acima, que uma nova coluna VISIBILITY foi incluída na view USER_INDEXES. Esta nova coluna introduzida nas views *_INDEXES, mostra se o índice em questão está visível ou não ao otimizador CBO.

-- Marcando o índice como invisível
SQL> alter index i_t1 invisible;

Índice alterado.

-- Verificando a visibilidade do índice
SQL> select index_name,visibility from user_indexes where index_name='I_T1';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
I_T1                           INVISIBLE

-- Gerando o plano de execução
SQL> explain plan for
  2  select * from t1 where id = 100;

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------


Podemos ver acima, que o índice foi totalmente ignorado pelo otimizador ocasionando uma varredura integral (FTS) na tabela. Se mesmo assim ainda quisermos que o índice fique visível ao otimizador mesmo estando invisível, teremos ainda a opção de setar o parâmetro optimizer_use_invisible_indexes para TRUE.

SQL> alter session set optimizer_use_invisible_indexes = TRUE;

Sessão alterada.

SQL> explain plan for
  2  select * from t1 where id = 100;

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
Plan hash value: 2966378588

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| I_T1 |     1 |    13 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------


Agora, independente do valor do parâmetro optimizer_use_invisible_indexes, se quisermos que o índice volte a ser visível ao otimizador, precisaremos apenas marcar o índice como visível sem a necessidade de ter que reconstruí-lo:

-- Marcando o índice como visível
SQL> alter index i_t1 visible;

Índice alterado.

-- Verificando a visibilidade do índice
SQL> select index_name,visibility from user_indexes where index_name='I_T1';

INDEX_NAME                     VISIBILIT
------------------------------ ---------
I_T1                           VISIBLE


Em resumo, esta nova funcionalidade nos permitirá testar a utilização de um novo índice sem afetar o plano de execução para as sentenças SQL existentes, ou até mesmo, a de testar o efeito da execução de uma sentença SQL ao dropar um índice existente sem a necessidade realmente de ter que dropá-lo.



4 comentários:

Luciano Alvarenga M. Pires disse...

Está muito bom o Oracle 11G. Parabéns ai pela abordagem da nova feature do Oracle 11G.

Eduardo Legatti disse...

Olá Luciano,

Realmente, o Oracle 11g está repleto de novidades. No artigo de Dezembro/2007 eu postei maiores informações sobre algumas destas features. Aliás, parabéns pelo seu Blog

Até mais ...

Sidney França disse...

Olá Eduardo,

Mais uma vez queria te elogiar pelo blog e por este artigo, muito bom!! Venho lendo e acompanhando os exemplos ;). Como o post é de 2008 e o oracle é o 11g r1, já deduzi que é uma diferença de release, já que o meu é o r2. Mesmo assim queria tua opinião sobre essa diferença (se eu tiver feito certo kk): o parâmetro skip_unusable_indexes foi alterado para 'false' e o índice marcado como 'unusable' e o erro ORA-01502 não foi emitido

Microsoft Windows XP [versão 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Dominique>cd\

C:\>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Qua Nov 28 11:36:27 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Informe o nome do usußrio: system
Informe a senha:

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

.
.
.

SQL> alter session set skip_unusable_indexes = false;

SessÒo alterada.

SQL> alter index i_s1 unusable;

═ndice alterado.

SQL> select index_name,status from user_indexes where index_name = 'I_S1';

INDEX_NAME STATUS
------------------------------ --------
I_S1 UNUSABLE

SQL> explain plan for
2 select * from s1 where id = 100;

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1379979680

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 208 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| I_S1 | 16 | 208 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

1 - access("ID"=100)

Note
-----
- dynamic sampling used for this statement (level=2)

17 linhas selecionadas.

SQL>

Muito obrigado pela atenção e por esclarecer minhas dúvidas, seu blog tem sido muito importante na minha formação.

Abraços!

Eduardo Legatti disse...

Olá Sidney,

No caso do 11g R1 en versões anteriores, durante a geração do plano de execução o erro o erro é gerado assim como na execução normal do SQL. Veja abaixo:

11g R1

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

5 linhas selecionadas.

SQL> create table t1 as select level ID from dual connect by level<=100000;

Tabela criada.

SQL> create index idx1 on t1 (id);

Índice criado.

SQL> alter index idx1 unusable;

Índice alterado.

SQL> alter session set skip_unusable_indexes = false;

Sessão alterada.

SQL> select * from t1 where id=10;
select * from t1 where id=10
*
ERRO na linha 1:
ORA-01502: índice 'SYSTEM.IDX1' ou a sua partição está em estado não-utilizável



SQL> explain plan for select * from t1 where id=10;
explain plan for select * from t1 where id=10
*
ERRO na linha 1:
ORA-01502: índice 'SYSTEM.IDX1' ou a sua partição está em estado não-utilizável



SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Error: cannot fetch last explain plan from PLAN_TABLE


No caso do 11g R2, o erro acontece na execução normal do SQL, mas não na geração do plano de execução. Veja abaixo:


11g R2

SQL> select * from v$version;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

5 linhas selecionadas.

SQL> create table t1 as select level ID from dual connect by level<=100000;

Tabela criada.

SQL> create index idx1 on t1 (id);

Índice criado.

SQL> alter index idx1 unusable;

Índice alterado.

SQL> alter session set skip_unusable_indexes = false;

Sessão alterada.

SQL> select * from t1 where id=10;
select * from t1 where id=10
*
ERRO na linha 1:
ORA-01502: índice 'SYSTEM.IDX1' ou a sua partição está em estado não utilizável



SQL> explain plan for select * from t1 where id=10;

Explicado.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 3782390545

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 124 | 1612 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX1 | 124 | 1612 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("ID"=10)

Note
-----
- dynamic sampling used for this statement (level=2)

17 linhas selecionadas.


Por fim, acredito que o certo seria o 11g R2 mostrar o erro ORA-01502 mesmo na geração do plano de execução. Bug? ;-)

Abraços e até mais ...

Legatti

Postagens populares