No artigo "Por que o Otimizador criou um plano de execução ineficiente?" elencamos algumas condições que levam o Otimizador a gerar um plano de execução ineficiente, neste artigo vamos mostrar com mais detalhes como a utilização de função nas colunas utilizadas na cláusula WHERE podem influenciar de maneira negativa o…
Um truque que você não conhece sobre coleta de estatísticas
Neste artigo vamos verificar como a coleta do valor de CLUSTERING FACTOR de um índice pode influenciar de forma negativa o plano de execução de uma instrução SQL e o que podemos fazer para melhorar esta estatística e ajudar o Otimizador a tomar uma decisão mais assertiva.
O CLUSTERING FACTOR é uma das estatísticas mais importantes na tomada de decisão pelo Otimizador quanto a viabilidade de utilização de um índice num plano de execução, esta estatística deveria refletir o quão bem ordenados os dados da tabela estão em relação às entradas do índice. Portanto quanto melhor (menor) o CF, mais eficiente seria usar o índice, já que menos blocos da tabela seriam acessados para recuperar os dados.
O CLUSTERING FACTOR é calculado através de um FULL INDEX SCAN que analisa o ROWID de cada entrada do índice e compara com o da entrada anterior, o incremento do valor do CF ocorre da seguinte forma:
1) Caso o bloco da tabela que está sendo referenciado no ROWID seja diferente daquele da entrada de índice anterior, o CF é incrementado.
2) Caso o bloco da tabela que está sendo referenciado no ROWID seja igual ao da entrada de índice anterior, o CF não é incrementado.
Este algoritmo pode apresentar problemas em algumas situações, como neste caso: imagine uma tabela onde 200 linhas serão inseridas por duas sessões simultaneamente, cada uma insere 100 linhas e uma coluna da linha que será indexada recebe um valor numérico que é incrementado por uma SEQUENCE. Utilizando 2 FREELISTS, uma sessão vai inserir as linhas em um bloco enquanto a outra vai inserir em um segundo bloco, com os valores da SEQUENCE sendo distribuídos aleatoriamente entre os dois blocos. Desta forma a coleta de estatísticas do índice vai registrar um valor de CF próximo de 200, já que os valores sequenciais gerados pela SEQUENCE para a coluna deste índice são misturados ou alternados entre os blocos. Este valor de CF fará com que o Otimizador desconsidere a utilização do índice, embora as entradas do índice estejam bem agrupadas nos blocos da tabela.
Para facilitar a compreensão deste problema, vamos realizar uma simulação prática com as seguintes etapas:
1) Criar uma tabela, uma sequence e uma procedure para inserir linhas na tabela
2) Executar a procedure em 4 sessões SQL*PLUS simultaneamente
3) Criar um índice, coletar as estatísticas e verificar o CLUSTERING FACTOR
4) Executar uma consulta na tabela criada e verificar o plano de execução
5) Alterar o parâmetro TABLE_CACHED_BLOCKS, coletar as estatísticas e verificar o CLUSTERING FACTOR
6) Executar a mesma consulta novamente e verificar o plano de execução
1) Criar a tabela, sequence e a procedure
Vamos criar os objetos necessários para realizar a nossa 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 > create table dbtw052 (matricula number, Nome varchar2(50)); Tabela criada. SQL > SQL > create sequence dbtw052_seq order; Sequência criada. SQL > SQL > CREATE OR REPLACE PROCEDURE proc_dbtw052 AS 2 BEGIN 3 FOR i IN 1..100000 LOOP 4 INSERT INTO dbtw052 VALUES (dbtw052_seq.NEXTVAL, 'DBTimeWizard - Oracle Performance and Tuning'); 5 COMMIT; 6 END LOOP; 7 END; 8 / Procedimento criado. SQL > SQL > SELECT table_name, 2 T.tablespace_name, 3 segment_space_management 4 FROM dba_tables T, 5 dba_tablespaces F 6 WHERE T.tablespace_name = F.tablespace_name 7 AND table_name='DBTW052'; TABLE_NAME TABLESPACE_NAME SEGMEN ------------------------------ ------------------------------ ------ DBTW052 DBTW AUTO SQL > |
2) Executar a procedure em 4 sessões simultâneas
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:
Executando a procedure em sessões paralelas e com a tabela definida numa TABLESPACE cujo gerenciamento dos seguimentos é ASSM, cada sessão vai inserir as linhas em um bloco diferente da outra, criando um cenário parecido com aquele descrito na introdução do artigo.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | SQL-01 > exec proc_dbtw052; Procedimento PL/SQL concluído com sucesso. Decorrido: 00:01:08.95 SQL-01 > SQL-02 > exec proc_dbtw052; Procedimento PL/SQL concluído com sucesso. Decorrido: 00:01:00.65 SQL-02 > SQL-03 > exec proc_dbtw052; Procedimento PL/SQL concluído com sucesso. Decorrido: 00:01:09.03 SQL-03 > SQL-04 > exec proc_dbtw052; Procedimento PL/SQL concluído com sucesso. Decorrido: 00:00:57.83 SQL-04 > |
3) Criar um índice e coletar as estatísticas
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 | SQL > CREATE INDEX dbtw052_matr_idx ON dbtw052(matricula); Índice criado. SQL > SQL > SQL > SQL > EXEC dbms_stats.gather_table_stats(ownname=>user, tabname=>'DBTW052', estimate_percent=> null, cascade=> true, method_opt=>'FOR ALL COLUMNS SIZE 1'); Procedimento PL/SQL concluído com sucesso. SQL > SQL > SQL > SELECT T.table_name, 2 I.index_name, 3 T.blocks, 4 T.num_rows, 5 I.clustering_factor 6 FROM user_tables T, 7 user_indexes I 8 WHERE T.table_name = I.table_name 9 AND I.index_name = 'DBTW052_MATR_IDX'; TABLE_NAME INDEX_NAME BLOCKS NUM_ROWS CLUSTERING_FACTOR ------------------------------ ------------------------------ ---------- ---------- ----------------- DBTW052 DBTW052_MATR_IDX 3142 400000 266796 SQL > |
Analisando as estatísticas da tabela e do índice verificamos que a tabela tem 3142 blocos e o valor do CLUSTERING FACTOR é 266.796, muito próximo do valor total de linhas da tabela que é 400.000.
4) Executar uma consulta na tabela criada
Vamos executar uma consulta na tabela e verificar a eficiência do 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 | SQL > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SQL > SQL > SELECT /* dbtw521 */ * 2 FROM dbtw052 3 WHERE matricula BETWEEN 42 AND 319; MATRICULA NOME ---------- -------------------------------------------------- 271 DBTimeWizard - Oracle Performance and Tuning . . 270 DBTimeWizard - Oracle Performance and Tuning 278 linhas selecionadas. SQL > 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 '%dbtw521%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ---------------- ------------ 6ab5c2j6jfk8m 0 SQL > SQL > SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST')); antigo 2: FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST')) novo 2: FROM TABLE (dbms_xplan.display_cursor ('6ab5c2j6jfk8m', 0,'ALLSTATS LAST')) PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------- SQL_ID 6ab5c2j6jfk8m, child number 0 ------------------------------------- SELECT /* dbtw521 */ * FROM dbtw052 WHERE matricula BETWEEN :"SYS_B_0" AND :"SYS_B_1" Plan hash value: 2174329534 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 278 |00:00:00.71 | 3109 | |* 1 | FILTER | | 1 | | 278 |00:00:00.71 | 3109 | |* 2 | TABLE ACCESS FULL| DBTW052 | 1 | 279 | 278 |00:00:00.71 | 3109 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(:SYS_B_1>=:SYS_B_0) 2 - filter(("MATRICULA"<=:SYS_B_1 AND "MATRICULA">=:SYS_B_0)) 21 linhas selecionadas. SQL > |
Analisando o plano verificamos que foram selecionadas 278 linhas e o Otimizador decidiu fazer uma operação TABLE FULL SCAN pois ele estimou que se utilizasse o índice teria que acessar um numero de blocos da tabela próximo do numero de linhas selecionadas, isto baseado no valor do CLUSTERING FACTOR.
5) Alterar o parâmetro, coletar as estatísticas e verificar o CF
Para melhorar a assertividade do valor CF vamos alterar o parâmetro TABLE_CACHED_BLOCKS de 1 para 38 e coletar novas estatísticas.
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 | SQL > COL TABLE_CACHED_BLOCKS FOR A30; SQL > SELECT table_name, 2 DBMS_STATS.GET_PREFS(ownname=>USER,tabname=>table_name,pname=>'TABLE_CACHED_BLOCKS') TABLE_CACHED_BLOCKS 3 FROM dba_tables 4 WHERE table_name = 'DBTW052'; TABLE_NAME TABLE_CACHED_BLOCKS ------------------------------ ------------------------------ DBTW052 1 SQL > SQL > EXEC DBMS_STATS.SET_TABLE_PREFS(OWNNAME=>user, TABNAME=>'DBTW052', PNAME=>'TABLE_CACHED_BLOCKS', PVALUE=>38); Procedimento PL/SQL concluído com sucesso. SQL > SQL > SQL > EXEC DBMS_STATS.GATHER_INDEX_STATS(OWNNAME=>user, INDNAME=>'DBTW052_MATR_IDX', ESTIMATE_PERCENT=> null); Procedimento PL/SQL concluído com sucesso. SQL > SQL > SQL > SELECT T.table_name, 2 I.index_name, 3 T.blocks, 4 T.num_rows, 5 I.clustering_factor 6 FROM user_tables T, 7 user_indexes I 8 WHERE T.table_name = I.table_name 9 AND I.index_name = 'DBTW052_MATR_IDX'; TABLE_NAME INDEX_NAME BLOCKS NUM_ROWS CLUSTERING_FACTOR ------------------------------ ------------------------------ ---------- ---------- ----------------- DBTW052 DBTW052_MATR_IDX 3142 400000 3038 SQL > |
Após a coleta das novas estatísticas podemos observar que o CLUSTERING FACTOR (CF) diminuiu de 266796 para 3038 que é um valor bem próximo do numero de blocos da tabela (3142).
6) Executar a mesma consulta novamente e verificar o plano de execução
Feito o ajuste no CF vamos executar a consulta novamente e verificar se o índice vai ser utilizado e se vamos ter algum ganho de desempenho na consulta.
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 | SQL > ALTER SESSION SET statistics_level=ALL; Sessão alterada. SQL > SQL > SQL > SELECT /* dbtw522 */ * 2 FROM dbtw052 3 WHERE matricula BETWEEN 42 AND 319; MATRICULA NOME ---------- -------------------------------------------------- 42 DBTimeWizard - Oracle Performance and Tuning . . 319 DBTimeWizard - Oracle Performance and Tuning 278 linhas selecionadas. SQL > 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 '%dbtw522%' 4 AND sql_text NOT LIKE '%v$sql%'; SQL_ID CHILD_NUMBER ---------------- ------------ dzgjfpgbutxpp 0 SQL > SQL > SQL > SELECT * 2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST')); antigo 2: FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST')) novo 2: FROM TABLE (dbms_xplan.display_cursor ('dzgjfpgbutxpp', 0,'ALLSTATS LAST')) PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------ SQL_ID dzgjfpgbutxpp, child number 0 ------------------------------------- SELECT /* dbtw522 */ * FROM dbtw052 WHERE matricula BETWEEN :"SYS_B_0" AND :"SYS_B_1" Plan hash value: 2969862332 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 278 |00:00:00.01 | 44 | |* 1 | FILTER | | 1 | | 278 |00:00:00.01 | 44 | | 2 | TABLE ACCESS BY INDEX ROWID| DBTW052 | 1 | 279 | 278 |00:00:00.01 | 44 | |* 3 | INDEX RANGE SCAN | DBTW052_MATR_IDX | 1 | 279 | 278 |00:00:00.01 | 22 | ----------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(:SYS_B_1>=:SYS_B_0) 3 - access("MATRICULA">=:SYS_B_0 AND "MATRICULA"<=:SYS_B_1) 22 linhas selecionadas. SQL > |
Verificando o plano de execução observamos que o índice foi utilizado e o número de Buffers acessados caiu de 3109 na primeira consulta para 44 nesta consulta que utilizou o índice.
Conclusão
O parâmetro TABLE_CACHED_BLOCKS foi uma melhoria implementada no pacote DBMS_STATS no Oracle Database 11.2.0.4, com ele podemos aprimorar a qualidade do CF e dar ao Otimizador uma visão mais consistente da distribuição das linhas na tabela e sua relação com as entradas no índice. Quando estiver avaliando o desempenho de uma instrução SQL e constatar que um índice que poderia melhorar o desempenho não esta sendo utilizado, considere a possibilidade do CF não estar refletindo a real relação de ordenação entre as entradas do índice e as linhas nos blocos da tabela.
Referências
https://docs.oracle.com/cd/E11882_01/server.112
