Em um artigo anterior já mostramos a importância do índice composto para melhorar o desempenho de uma instrução SQL, agora vamos estudar um pouco como a ordem das colunas no índice composto pode impactar o desempenho de uma instrução SQL. A ordem das colunas no índice composto é importante, no…
Exadata: Será que podemos eliminar os índices das tabelas?
É 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 > |
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
http://www.oaktable.net/blog/when-exadata%E2%80%99s-storage-indexes-used?page=2
