Um truque que você não conhece sobre coleta de estatísticas

coleta de estatisticas

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

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

 
 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 

Deixe uma resposta

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