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…
Quebrando paradigmas de índices
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
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:
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
