Quebrando paradigmas de índices

column group statistics

Quando um índice tem baixa seletividade a probabilidade do Otimizador utiliza-lo é muito pequena, mas existe uma alteração que podemos aplicar ao índice que confunde o Otimizador quando este realiza o calculo de cardinalidade e como consequência o Otimizador passa a utilizar o índice no plano de execução. O mais incrível de tudo isso é que o plano de execução gerado por um erro de estimativa do Otimizador é melhor que o plano gerado inicialmente sem o erro de cardinalidade.

 
 

Neste artigo vamos ver uma situação que ocorre normalmente no gerenciamento de índices e que pode produzir efeitos colaterais positivos ou negativos, mas que precisam ser administrados para evitar que a sua aplicação passe a ter um desempenho indesejado após a alteração de um índice.

 
 

Para realizar esta simulação vamos:

1) Criar 1 tabela com um índice composto
2) Executar a consulta e verificar que o plano de execução não utiliza o índice
3) Alterar o índice composto incluindo mais uma coluna
4) Executar a consulta novamente e verificar que o plano de execução passa a utilizar o índice
5) Criar uma estatística de grupo de colunas das colunas da cláusula WHERE
6) Executar a consulta novamente e verificar que o plano de execução volta ao seu padrão original

1) Criar a tabela da simulação

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
SQL > SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL >
SQL > -----------------------
SQL > -- Definição da tabela
SQL > -----------------------
SQL >
SQL > CREATE TABLE dbtw055 AS
  2  SELECT mod(rownum,5) col1,
  3         mod(rownum,5) col2,
  4         rownum  col3,
  5         'DBTimeWizard - Oracle Performance and Tuning'   text
  6    FROM dual
  7  CONNECT BY level <= 1000000;

Tabela criada.

SQL >
SQL >
SQL >
SQL > ---------------------
SQL > -- Criação do índice
SQL > ---------------------
SQL >
SQL > CREATE INDEX dbtw055_comp2_idx ON dbtw055 (col1,col2);

Índice criado.

SQL >
SQL > -------------------------
SQL > -- Coleta de estatísticas
SQL > -------------------------
SQL >
SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW055',method_opt=>'for all columns size auto',cascade=>true,force=>true,degree=>16);

Procedimento PL/SQL concluído com sucesso.

SQL >

 

2) Executar a consulta e verificar o plano de execução

 

Vamos realizar uma consulta na tabela incluindo no filtro da cláusula WHERE as duas colunas presentes no índice criado.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
SQL > ALTER SESSION SET statistics_level = ALL;

Sessão alterada.

SQL >
SQL > SELECT /* DBTW-055.1 */ DISTINCT text
  2    FROM (SELECT text
  3            FROM dbtw055
  4           WHERE col1 = 0
  5             AND col2 = 0);

TEXT
--------------------------------------------
DBTimeWizard - Oracle Performance and Tuning

SQL >
SQL > -- Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%DBTW-055.1%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
b229f8sznx4p1            0

SQL >
SQL > -- Gera o relatório do plano de execução da consulta
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));
antigo   1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'))
novo   1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('b229f8sznx4p1',         0,'typical iostats last'))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b229f8sznx4p1, child number 0
-------------------------------------
SELECT /* DBTW-055.1 */ DISTINCT text   FROM (SELECT text
FROM dbtw055          WHERE col1 = :"SYS_B_0"            AND col2 =
:"SYS_B_1")

Plan hash value: 2734070855

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |       |  2328 (100)|          |      1 |00:00:04.74 |    8405 |   8402 |
|   1 |  HASH UNIQUE       |         |      1 |      1 |    51 |  2328   (1)| 00:00:28 |      1 |00:00:04.74 |    8405 |   8402 |
|*  2 |   TABLE ACCESS FULL| DBTW055 |      1 |    200K|  9960K|  2323   (1)| 00:00:28 |    200K|00:00:04.72 |    8405 |   8402 |
---------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(("COL1"=:SYS_B_0 AND "COL2"=:SYS_B_1))


21 linhas selecionadas.

SQL >
SQL > select index_name,
  2         blevel,
  3         num_rows,
  4         distinct_keys,
  5         clustering_factor,
  6         leaf_blocks
  7    from dba_indexes
  8   where table_name = 'DBTW055';

INDEX_NAME                         BLEVEL   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR LEAF_BLOCKS
------------------------------ ---------- ---------- ------------- ----------------- -----------
DBTW055_COMP2_IDX                       2    1000000             5             42197        2315

SQL >

 

Verificando o plano de execução acima constatamos que as linhas foram recuperadas da tabela utilizando a operação TABLE ACCESS FULL, o Otimizador estimou corretamente que seriam acessadas 200 mil linhas da tabela (20%), ou seja a tabela contem 1 milhão de linhas e 5 valores distintos, conforme consulta na visão de estatísticas, portanto cada valor vai selecionar 200 mil linhas.

3) Alterar o índice composto incluindo mais uma coluna

Agora vamos imaginar que para melhorar a performance de uma outra consulta seria necessário que este índice possuísse mais uma coluna. Para incluir mais uma coluna no índice temos que elimina-lo e recria-lo com a coluna adicional.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL > -------------------------
SQL > -- Criação do novo índice
SQL > -------------------------
SQL >
SQL > DROP INDEX dbtw055_comp2_idx;

Índice eliminado.

SQL >
SQL > CREATE INDEX dbtw055_comp3_idx ON dbtw055 (col1,col2,col3);

Índice criado.

SQL >

 

4) Executar a consulta novamente e verificar o plano de execução

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
SQL > ALTER SESSION SET statistics_level = ALL;

Sessão alterada.

SQL >
SQL > SELECT /* DBTW-055.2 */ DISTINCT text
  2    FROM (SELECT text
  3            FROM dbtw055
  4           WHERE col1 = 0
  5             AND col2 = 0);

TEXT
--------------------------------------------
DBTimeWizard - Oracle Performance and Tuning

SQL >
SQL > -- Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%DBTW-055.2%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
dfyurfj25bnq4            0

SQL >
SQL > -- Gera o relatório do plano de execução da consulta
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));
antigo   1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'))
novo   1: SELECT * FROM TABLE (dbms_xplan.display_cursor ('dfyurfj25bnq4',         0,'typical iostats last'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dfyurfj25bnq4, child number 0
-------------------------------------
SELECT /* DBTW-055.2 */ DISTINCT text   FROM (SELECT text
FROM dbtw055          WHERE col1 = :"SYS_B_0"            AND col2 =
:"SYS_B_1")

Plan hash value: 264121747

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |      1 |        |       |  1807 (100)|          |      1 |00:00:00.12 |    8961 |
|   1 |  HASH UNIQUE                 |                   |      1 |      1 |    51 |  1807   (1)| 00:00:22 |      1 |00:00:00.12 |    8961 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DBTW055           |      1 |  40000 |  1992K|  1805   (1)| 00:00:22 |    200K|00:00:00.09 |    8961 |
|*  3 |    INDEX RANGE SCAN          | DBTW055_COMP3_IDX |      1 |  40000 |       |   123   (0)| 00:00:02 |    200K|00:00:00.03 |     559 |
--------------------------------------------------------------------------------------------------------------------------------------------

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

   3 - access("COL1"=:SYS_B_0 AND "COL2"=:SYS_B_1)


22 linhas selecionadas.

SQL >
SQL > select index_name,
  2         blevel,
  3         num_rows,
  4         distinct_keys,
  5         clustering_factor,
  6         leaf_blocks
  7    from dba_indexes
  8   where table_name = 'DBTW055';

INDEX_NAME                         BLEVEL   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR LEAF_BLOCKS
------------------------------ ---------- ---------- ------------- ----------------- -----------
DBTW055_COMP3_IDX                       2    1000000       1000000             42010        3010

SQL >

 

Analisando o plano de execução da consulta acima após a alteração no índice podemos constatar que a tabela passou a ser acessada utilizando o índice, esta alteração no plano de execução ocorreu pois o Otimizador errou no calculo da cardinalidade da operação (estimou 40 mil linhas), este erro ocorreu pois ao incluir mais uma coluna no índice a sua estatística passou a refletir a combinação das 3 colunas, mas a consulta que estamos avaliando apresenta somente duas colunas no filtro da cláusula WHERE.

Outra observação importante é que a consulta utilizando o índice´leva 12 centésimos de segundos para ser concluída, enquanto a consulta com a operação TABLE ACCESS FULL demorou 4 segundos e 74 centésimos, a explicação para esta diferença é que no primeiro plano de execução houve leitura física no disco (devido a operação FULL SCAN), enquanto no segundo os blocos foram recuperados direto do Buffer Cache. (Blocos acessados por índices permanecem mais tempo no Buffer Cache)

5) Criar estatística de grupo de colunas

 

Para ajudar o Otimizador a calcular a cardinalidade da operação corretamente podemos criar uma estatística de grupo de colunas (também conhecida como estatísticas estendidas) para as duas colunas presentes no filtro da cláusula WHERE.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL > exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'DBTW055',method_opt=>'for columns (col1,col2) size 1',cascade=>true,force=>true,degree=>16);

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL > SELECT column_name, num_distinct, num_nulls, histogram, extension
  2    FROM user_tab_col_statistics TCS,
  3         user_stat_extensions TSE
  4   WHERE TCS.column_name=TSE.extension_name(+)
  5     AND TCS.table_name='DBTW055';

COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS HISTOGRAM       EXTENSION
------------------------------ ------------ ---------- --------------- ------------------------
SYS_STUFLHATC5RBD6JHJZWT$X2AAH            5          0 NONE            ("COL1","COL2")
COL1                                      5          0 NONE
COL3                                1000000          0 NONE
COL2                                      5          0 NONE
TEXT                                      1          0 NONE

SQL >

 

6) Executar a consulta novamente e verificar que o plano de execução

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
SQL > ALTER SESSION SET statistics_level = ALL;

Sessão alterada.

SQL >
SQL > SELECT /* DBTW-055.3 */ DISTINCT text
  2    FROM (SELECT text
  3            FROM dbtw055
  4           WHERE col1 = 0
  5             AND col2 = 0);

TEXT
--------------------------------------------
DBTimeWizard - Oracle Performance and Tuning

SQL >
SQL > -- Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%DBTW-055.3%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
dms6c908t4vs9            0

SQL >
SQL > -- Gera o relatório do plano de execução da consulta
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dms6c908t4vs9, child number 0
-------------------------------------
SELECT /* DBTW-055.3 */ DISTINCT text   FROM (SELECT text
FROM dbtw055          WHERE col1 = :"SYS_B_0"            AND col2 =
:"SYS_B_1")

Plan hash value: 2734070855

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |       |  2328 (100)|          |      1 |00:00:01.04 |    8405 |   8402 |
|   1 |  HASH UNIQUE       |         |      1 |      1 |    51 |  2328   (1)| 00:00:28 |      1 |00:00:01.04 |    8405 |   8402 |
|*  2 |   TABLE ACCESS FULL| DBTW055 |      1 |    200K|  9960K|  2323   (1)| 00:00:28 |    200K|00:00:01.02 |    8405 |   8402 |
---------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(("COL1"=:SYS_B_0 AND "COL2"=:SYS_B_1))


21 linhas selecionadas.

SQL >

 

Observando o plano de execução acima verificamos que a estimativa de cardinalidade da operação voltou a ser calculada corretamente e o numero de linhas estimadas (200 mil linhas) é igual ao numero de linhas efetivamente selecionadas da tabela.

 

Referências

https://docs.oracle.com/cd/E29505_01/server.1111

 
 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 

Deixe uma resposta

O seu endereço de email não será publicado. Campos obrigatórios são marcados com *