Exadata: Será que podemos eliminar os índices das tabelas?

Database Index in Exadata

É comum ouvir outras pessoas dizendo que no Exadata não precisamos mais dos índices de banco de dados tradicionais pois o Exadata responde melhor sem eles e que podemos excluir todos os índices para liberar espaço no Storage.
Na prática as coisas são um pouco diferentes, existem muitos cenários onde os índices de banco de dados ainda são críticos para garantir o desempenho do banco em um ambiente Exadata.

Neste artigo vamos demonstrar duas situações onde a utilização do índice tradicional do banco continua fazendo toda a diferença em termos de performace e até mesmo os recursos fantásticos do Exadata não conseguem supera-lo.

Utilização de funções na consulta

O banco de dados Oracle dispõe de varias funções nativas que podem ser utilizadas na instrução SQL, muitas dessas funções são resolvidas no Exadata Storage que na terminologia do Exadata corresponde ao “cell offloaded”, porem algumas funções não estão disponíveis ainda para “cell offloaded” na versão 11.2.0.4, para identificar quais funções não são resolvidas pelo Exadata Storage consulte a visão “V$SQLFN_METADATA“.

 

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
SQL>select NAME, OFFLOADABLE from v$sqlfn_metadata where name in ('MIN','MAX','AVG','SUM','COUNT') order by 1;

NAME       OFFLOADABLE
---------- ---------------
AVG        NO
AVG        NO
AVG        NO
AVG        NO
AVG        NO
AVG        NO
COUNT      NO
COUNT      NO
MAX        NO
MAX        NO
MIN        NO
MIN        NO
SUM        NO
SUM        NO
SUM        NO
SUM        NO
SUM        NO
SUM        NO

18 rows selected.

SQL>

 

Uma das situações em que o tradicional índice de banco de dados pode ajudar a melhorar a performace no Exadata são as instruções SQL que utilizam funções que não são resolvidas no “cell offloaded”. Para demonstrar essa situação vamos executar uma consulta com a função MIN() sem a utilização de índice e executar a mesma consulta utilizando um índice para comparar o desempenho dessa consulta nas duas situações.

Consulta sem utilização de índice

Para executar a consulta vamos criar uma tabela com 5GB e coletar as estatísticas da mesma.

 

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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
SQL > SELECT * FROM V$VERSION where rownum < 2;

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

1 row selected.

SQL >
SQL > alter session set workarea_size_policy=MANUAL;

Session altered.

SQL >
SQL > alter session set sort_area_size=2000000000;

Session altered.

SQL > create table dbtw01 as
  2     select 'DBTimeWizard - Performance and Tuning' as produto,
  3            mod(rownum,5)                            as codigo,
  4            mod(rownum,1000)                         as cliente_id ,
  5            mod(rownum,10000)                        as postal_id,
  6            5000                                     as total_vendas,
  7            trunc(sysdate - 9999 + mod(rownum,10000)) as data_venda
  8       from dual connect by level<=2e7;

Table created.

SQL > alter table dbtw01 nologging;

Table altered.

SQL >
SQL > insert /*+ append */ into dbtw01 select * from dbtw01;

20000000 rows created.

SQL >
SQL > commit;

Commit complete.

SQL >
SQL > insert /*+ append */ into dbtw01 select * from dbtw01;

40000000 rows created.

SQL >
SQL > commit;

Commit complete.

SQL >
SQL > select round(bytes/1024/1024/1024,1) as "Tamanho GB"
  2    from user_segments
  3   where segment_name = 'DBTW01';

Tamanho GB
----------
       5.6

1 row selected.

SQL >
SQL > exec dbms_stats.gather_table_stats(ownname=> USER, tabname=> 'DBTW01', cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE SKEWONLY');

PL/SQL procedure successfully completed.

SQL > alter table dbtw01 logging;

Table altered.

SQL >
SQL > ALTER SESSION SET statistics_level=ALL;

Session altered.

SQL >
SQL > set timing on;
SQL >
SQL > select /* NO_INDEX_DBTW01 */ min(cliente_id) from dbtw01;

MIN(CLIENTE_ID)
---------------
              0

1 row selected.

Elapsed: 00:00:08.97
SQL >
SQL > set timing off;
SQL >
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 '%NO_INDEX_DBTW01%'
  4    AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
2r831g60a8zx3            0

1 row selected.

SQL >
SQL >
SQL > SELECT *
  2   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2r831g60a8zx3, child number 0
-------------------------------------
select /* NO_INDEX_DBTW01 */ min(cliente_id) from dbtw01

Plan hash value: 2690647991

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |        |      1 |        |       |   195K(100)|          |      1 |00:00:08.93 |     719K|    719K|
|   1 |  SORT AGGREGATE            |        |      1 |      1 |     4 |            |          |      1 |00:00:08.93 |     719K|    719K|
|   2 |   TABLE ACCESS STORAGE FULL| DBTW01 |      1 |     80M|   305M|   195K  (1)| 00:39:09 |     80M|00:00:04.84 |     719K|    719K|
----------------------------------------------------------------------------------------------------------------------------------------


14 rows selected.

SQL >

 

Title of the document

Ajudo DBAs e analistas de sistema a se destacarem em suas empresas

e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:

No plano de execução podemos observar que a consulta não utiliza índice de banco, ela faz um FULL TABLE SCAN no Exadata Storage e o tempo de execução é de aproximandamente 9 segundos.

Consulta com utilização de índice

Agora vamos criar um índice na coluna “cliente_id” e executar a mesma consulta novamente:

 

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
SQL > create index idx_dbtw01 on dbtw01(cliente_id);

Index created.

SQL >
SQL >
SQL > ALTER SESSION SET statistics_level=ALL;

Session altered.

SQL >
SQL > set timing on;
SQL >
SQL > select /* WITH_INDEX_DBTW01 */ min(cliente_id) from dbtw01;

MIN(CLIENTE_ID)
---------------
              0

1 row selected.

Elapsed: 00:00:00.01
SQL >
SQL > set timing off;
SQL >
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 '%WITH_INDEX_DBTW01%'
  4    AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
b5wms71vkkmw1            0

1 row selected.

SQL >
SQL >
SQL > SELECT *
  2   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b5wms71vkkmw1, child number 0
-------------------------------------
select /* WITH_INDEX_DBTW01 */ min(cliente_id) from dbtw01

Plan hash value: 2619944286

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |            |      1 |        |       |     3 (100)|          |      1 |00:00:00.01 |       3 |      2 |
|   1 |  SORT AGGREGATE            |            |      1 |      1 |     4 |            |          |      1 |00:00:00.01 |       3 |      2 |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IDX_DBTW01 |      1 |      1 |     4 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |      2 |
--------------------------------------------------------------------------------------------------------------------------------------------


14 rows selected.

SQL >

 

No plano de execução podemos observar que a consulta utiliza o índice criado e o tempo de resposta da consulta cai para menos de 1 centésimo de segundo.

Exadata Storage Indexes

O Exadata Storage Index (SI) é um dos recursos que agiliza o acesso aos dados armazenados no storage, os segmentos armazenados no Exadata Storage são divididos em pequenas partes (default 1 MB) denominadas Storage regions e para cada uma delas são criados índices em memoria que armazenam os valores minimos e máximos para cada uma das colunas que são referenciadas na claúsula WHERE das instruções SQL. Esse indice é utilizado para eliminar o acesso desnecessário ao disco, identificando as Storage regions que não possuem os valores que estão sendo solicitados pelos filtros da claúsula WHERE.

Porem a eficácia do Storage Index e diretamente proporcional ao fator de clusterização das colunas nas Storage regions, ou seja, quanto mais organizados os dados estiverem nas colunas das Storage regions maior será a eficácia do SI para desprezar as regions que não precisam ser lidas.

Desta forma existem colunas cujos valores e sequência de atualização não permitem a utilização eficaz do Storage Index, nestes casos a utilização do índice tradicional de banco de dados continua sendo uma ótima opção para melhoria da performance da instrução SQL.Veremos a seguir um exemplo prático desta situação.

Consulta sem utilização de índice

Para executar a consulta vamos criar uma tabela com 5GB e coletar as estatísticas da mesma.

 

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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
SQL > SELECT * FROM V$VERSION where rownum < 2;

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

1 row selected.

SQL >
SQL > alter session set workarea_size_policy=MANUAL;

Session altered.

SQL >
SQL > alter session set sort_area_size=2000000000;

Session altered.

SQL >
SQL > create table dbtw02 as
  2     select 'DBTimeWizard - Performance and Tuning' as produto,
  3            mod(rownum,5)                            as codigo,
  4            mod(rownum,1000)                         as cliente_id ,
  5            round(dbms_random.value(10000,0))        as postal_id,
  6            5000                                     as total_vendas,
  7            trunc(sysdate - 9999 + mod(rownum,10000)) as data_venda
  8       from dual connect by level<=2e7;

Table created.

SQL >
SQL >
SQL > alter table dbtw02 nologging;

Table altered.

SQL >
SQL > insert /*+ append */ into dbtw02 select * from dbtw02;

20000000 rows created.

SQL >
SQL > commit;

Commit complete.

SQL >
SQL > insert /*+ append */ into dbtw02 select * from dbtw02;

40000000 rows created.

SQL >
SQL > commit;

Commit complete.

SQL >
SQL > select round(bytes/1024/1024/1024,1) as "Tamanho GB"
  2    from user_segments
  3   where segment_name = 'DBTW02';

Tamanho GB
----------
       5.5

1 row selected.

SQL >
SQL > exec dbms_stats.gather_table_stats(ownname=> USER, tabname=> 'DBTW02', cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE SKEWONLY');

PL/SQL procedure successfully completed.

SQL >
SQL > alter table dbtw02 logging;

Table altered.

SQL >
SQL > ALTER SESSION SET statistics_level=ALL;

Session altered.

SQL >
SQL > set timing on;
SQL >
SQL > select /* NO_INDEX_DBTW02 */ *
  2    from dbtw02
  3   where cliente_id = 711
  4     and postal_id = 7348;

PRODUTO                                   CODIGO CLIENTE_ID  POSTAL_ID TOTAL_VENDAS DATA_VEND
------------------------------------- ---------- ---------- ---------- ------------ ---------
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 02-JAN-13
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 02-JAN-13
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 02-JAN-13
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 02-JAN-13
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15

16 rows selected.

Elapsed: 00:00:22.22
SQL >
SQL > set timing off;
SQL >
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 '%NO_INDEX_DBTW02%'
  4    AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
6hs5v87115nss            0

SQL >
SQL >
SQL > SELECT *
  2   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));
old   2:  FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'))
new   2:  FROM TABLE (dbms_xplan.display_cursor ('6hs5v87115nss',         0,'typical iostats last'))

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6hs5v87115nss, child number 0
-------------------------------------
select /* NO_INDEX_DBTW02 */ *   from dbtw02  where cliente_id = 711
and postal_id = 7348

Plan hash value: 202550308

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |        |      1 |        |       |   195K(100)|          |     16 |00:00:22.20 |     719K|    455K|
|*  1 |  TABLE ACCESS STORAGE FULL| DBTW02 |      1 |      8 |   480 |   195K  (1)| 00:39:10 |     16 |00:00:22.20 |     719K|    455K|
---------------------------------------------------------------------------------------------------------------------------------------

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

   1 - storage(("POSTAL_ID"=7348 AND "CLIENTE_ID"=711))
       filter(("POSTAL_ID"=7348 AND "CLIENTE_ID"=711))


20 rows selected.

SQL >
SQL >
SQL > select name, value
  2    from v$mystat s, v$statname n
  3   where s.statistic# = n.statistic#
 4     and name like '%storage index%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index                             0

SQL >

 

Examinando o plano de execução acima verificamos que a consulta fez um FTS e retornou o resultado após 22 segundos, consultando as visões de estatíticas do banco podemos observar tambem que o Storage Index não foi utilizado.

Consulta com utilização de índice

Agora vamos criar um índice na coluna “postal_id” e executar a mesma consulta novamente:

 

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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
SQL > create index idx_dbtw02 on dbtw02(postal_id) parallel 16;

Index created.

SQL >
SQL > alter index idx_dbtw02 noparallel;

Index altered.

SQL >
SQL > ALTER SESSION SET statistics_level=ALL;

Session altered.

SQL >
SQL > set timing on;
SQL >
SQL > select /* WITH_INDEX_DBTW02 */ *
  2    from dbtw02
  3   where cliente_id = 711
  4     and postal_id = 7348;

PRODUTO                                   CODIGO CLIENTE_ID  POSTAL_ID TOTAL_VENDAS DATA_VEND
------------------------------------- ---------- ---------- ---------- ------------ ---------
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 02-JAN-13
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 02-JAN-13
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 02-JAN-13
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 02-JAN-13
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15
DBTimeWizard - Performance and Tuning          1        711       7348         5000 29-SEP-15

16 rows selected.

Elapsed: 00:00:00.18
SQL >
SQL >
SQL > set timing off;
SQL >
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 '%WITH_INDEX_DBTW02%'
  4    AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
901fc75zzrnrq            0

SQL >
SQL >
SQL > SELECT *
  2   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));
old   2:  FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'))
new   2:  FROM TABLE (dbms_xplan.display_cursor ('901fc75zzrnrq',         0,'typical iostats last'))

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  901fc75zzrnrq, child number 0
-------------------------------------
select /* WITH_INDEX_DBTW02 */ *   from dbtw02  where cliente_id = 711
  and postal_id = 7348

Plan hash value: 388684596

---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |      1 |        |       |  7977 (100)|          |     16 |00:00:00.17 |    7957 |    641 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| DBTW02     |      1 |      8 |   480 |  7977   (1)| 00:01:36 |     16 |00:00:00.17 |    7957 |    641 |
|*  2 |   INDEX RANGE SCAN          | IDX_DBTW02 |      1 |   7999 |       |    19   (0)| 00:00:01 |   7984 |00:00:00.01 |      22 |     19 |
---------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter("CLIENTE_ID"=711)
   2 - access("POSTAL_ID"=7348)


21 rows selected.

SQL >

 

No plano de execução acima constatamos que a consulta utilizou o índice criado e terminou em apenas 17 centésimo de segundos. 

Conclusão

Os dois exemplos acima mostram que eliminar índices de forma indiscriminada após uma migração para o Exadata não é uma estratégia adequada em termos de performance, mas tambem encontramos situações no ambiente Exadata em que a não utilização do índice melhora a performance da instrução SQL, portanto a estratégia mais adequada é analisar os casos onde o tempo de resposta é insatisfatório e fazer o ajustes necessários.

Referências

https://richardfoote.wordpress.com/2012/12/18/storage-indexes-vs-database-indexes-part-i-minmax-maxwells-silver-hammer/

https://richardfoote.wordpress.com/2012/12/19/storage-indexes-vs-database-indexes-part-ii-clustering-factor-fast-track/

http://www.oaktable.net/blog/when-exadata%E2%80%99s-storage-indexes-used?page=2
 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Deixe um comentário

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