Qual a importância das estatísticas de tabelas para o Otimizador?

Estatísticas de tabelas

O Otimizador baseado em custo (CBO) utiliza estatísticas para determinar o custo dos diversos planos de execução que ele produz para uma determinada instrução SQL e escolhe o plano com o menor custo para utilizar, portanto as informações estatísticas são cruciais na escolha do plano de execução e devem fornecer informações precisas e atualizadas para que o Otimizador consiga realizar um bom trabalho.

Existem três tipos de estatísticas de objetos: TABLE STATISTICS, COLUMN STATISTICS e INDEX STATISTICS. Nesse artigo vamos explorar um pouco as estatísticas de tabelas (TABLE STATISTICS), vamos fazer uma simulação e ver como o Otimizador utiliza essas informações.

 

TABLE STATISTICS

As informações estatísticas de tabela podem ser consultadas na visão USER_TAB_STATISTICS, existem muitas informações disponíveis nessa visão mas os campos mais importantes para o Otimizador são:

NUM_ROWS        – Número de linhas na tabela
AVG_ROW_LEN  – Tamanho médio de uma linha na tabela

 

Para ilustrar na prática a utilização das estatísticas de tabelas, vamos fazer uma simulação simples com uma consulta utilizando o “SAMPLE SCHEMA HR” que faz parte da instalação do banco de dados Oracle, nessa simulação vamos executar a mesma consulta duas vezes, uma sem estatísticas e outra com estatísticas, o parâmetro OPTIMIZER_DYNAMIC_SAMPLING será desativado a nível de sessão pois o objetivo é verificar como o Otimizador utiliza as estatísticas de tabela na montagem do plano de execução.

Caso tenha alguma dúvida na leitura do plano de execução gerado pelo pacote DBMS_XPLAN consulte a série de artigos que vai ajuda-lo a interpretar essas informações:

1. Como verificar a ordem que as operações são realizadas
2. Como interpretar os valores estatísticos estimados
3. Como interpretar os valores estatísticos coletados durante a execução
4. Como interpretar as seções não estatísticas do plano de execução

Consulta sem as estatísticas de tabelas

Para execução da consulta sem as estatísticas de tabelas vamos criar uma nova tabela copia da tabela EMPLOYEES e não vamos coletar as estatísticas, em seguida vamos verificar como o Otimizador estimou o numero de linhas dessa tabela.

 

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
HR@LAB11 > SELECT * FROM V$VERSION where rownum < 2;

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

1 linha selecionada.

Decorrido: 00:00:00.07
HR@LAB11 >
HR@LAB11 > CREATE TABLE EMP
  2  AS
  3  SELECT * FROM EMPLOYEES;

Tabela criada.

HR@LAB11 >
HR@LAB11 >
HR@LAB11 > SELECT num_rows,  avg_row_len
  2    FROM user_tab_statistics
  3   WHERE table_name = 'EMP';

  NUM_ROWS AVG_ROW_LEN
---------- -----------


HR@LAB11 > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

HR@LAB11 > Alter session set optimizer_dynamic_sampling=0;

Sessão alterada.

HR@LAB11 >
HR@LAB11 >
HR@LAB11 > select /* tst102 */
  2         *
  3    from EMP;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DAT JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- -------- ---------- ---------- -------------- ---------- -------------
        198 Donald               OConnell                  DOCONNEL                  650.507.9833         21/06/07 SH_CLERK         2600                       124            50
        199 Douglas              Grant                     DGRANT                    650.507.9844         13/01/08 SH_CLERK         2600                       124            50
        200 Jennifer             Whalen                    JWHALEN                   515.123.4444         17/09/03 AD_ASST          4400                       101            10
        201 Michael              Hartstein                 MHARTSTE                  515.123.5555         17/02/04 MK_MAN          13000                       100            20
        202 Pat                  Fay                       PFAY                      603.123.6666         17/08/05 MK_REP           6000                       201            20
..
..
..
        197 Kevin                Feeney                    KFEENEY                   650.507.9822         23/05/06 SH_CLERK         3000                       124            50

107 linhas selecionadas.

HR@LAB11 >
HR@LAB11 >
HR@LAB11 > column sql_id new_value m_sql_id
HR@LAB11 > column child_number new_value m_child_no
HR@LAB11 >
HR@LAB11 > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%tst102%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
923fg367zbm89            0

HR@LAB11 >
HR@LAB11 >
HR@LAB11 > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));
antigo   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'))
novo   2:   FROM TABLE (dbms_xplan.display_cursor ('923fg367zbm89',         0,'typical iostats last'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  923fg367zbm89, child number 0
-------------------------------------
select /* tst102 */        *   from EMP

Plan hash value: 3956160932

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |    107 |00:00:00.01 |      12 |      2 |
|   1 |  TABLE ACCESS FULL| EMP  |      1 |    409 | 54397 |     3   (0)| 00:00:01 |    107 |00:00:00.01 |      12 |      2 |
-----------------------------------------------------------------------------------------------------------------------------


13 linhas selecionadas.

HR@LAB11 >

 

No plano de execução acima observamos que a consulta retornou 107 linhas (A-Rows) porem o otimizador estimou 409 (E-Rows), esta estimativa é baseada em valores padrões que o Otimizador assume quando não existem estatísticas para a tabela.

Default values

Para chegar ao valor 409 (E-Rows) o Otimizador utilizou a formula {num_of_blocks * (block_size – cache_layer) / avg_row_len}, o valor “block_size” é 8.192 no banco que estamos realizando essa simulação, o valor “cache_layer” = 24 e o valor “avg_row_len” quando não existem estatísticas para a tabela é 100 bytes conforme tabela acima. Para calcular o resultado da formula falta descobrir o valor de “num_of_blocks” que pode ser 100 ou valor real baseado no “extent map”, a seguir vamos utilizar o pacote “DBMS_SPACE.UNUSED_SPACE” para descobrir o numero de blocos utilizados pela tabela.

 

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
dbauser@LAB11 > show parameters db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
dbauser@LAB11 >
dbauser@LAB11 >
dbauser@LAB11 > create or replace
  2  procedure show_space
  3   (p_segname in varchar2,
  4    p_owner in varchar2 default user,
  5    p_type in varchar2 default 'TABLE',
  6    p_partition in varchar2 default NULL )
  7    authid current_user
  8  as
  9  l_free_blks number;
 10  l_total_blocks number;
 11  l_total_bytes number;
 12  l_unused_blocks number;
 13  l_unused_bytes number;
 14  l_LastUsedExtFileId number;
 15  l_LastUsedExtBlockId number;
 16  l_LAST_USED_BLOCK number;
 17
 18  procedure p( p_label in varchar2, p_num in number )
 19  is
 20  begin
 21  dbms_output.put_line( rpad(p_label,40,'.') ||
 22  p_num );
 23  end;
 24
 25  begin
 26  dbms_space.unused_space
 27  ( segment_owner => p_owner,
 28  segment_name => p_segname,
 29  segment_type => p_type,
 30  partition_name => p_partition,
 31  total_blocks => l_total_blocks,
 32  total_bytes => l_total_bytes,
 33  unused_blocks => l_unused_blocks,
 34  unused_bytes => l_unused_bytes,
 35  LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
 36  LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
 37  LAST_USED_BLOCK => l_LAST_USED_BLOCK );
 38
 39  p( 'Total Blocks', l_total_blocks );
 40  p( 'Unused Blocks', l_unused_blocks );
 41  p( 'Blocks Used', l_total_blocks-l_unused_blocks );
 42
 43  end;
 44  /

Procedimento criado.

dbauser@LAB11 > set serveroutput on
dbauser@LAB11 > exec show_space('EMP','HR');
Total Blocks............................8
Unused Blocks...........................3
Blocks Used.............................5

Procedimento PL/SQL concluído com sucesso.

dbauser@LAB11 >
dbauser@LAB11 > select 5*(8192-24)/100 from dual;

5*(8192-24)/100
---------------
          408,4

dbauser@LAB11 >

 

Conforme resultado da “procedure” acima o número de blocos usados pela tabela são 5 e aplicando esse valor na formula informada na documentação da Oracle temos um resultado de 408,4 que é o mesmo valor arrendondado que aparece no plano de execução no campo E-Rows = 409.

Consulta com as estatísticas de tabelas

Agora vamos coletar as estatísticas da tabela utilizada na nossa simulação, executar a consulta novamente e observar quantas linha o Otimizador vai estimar para 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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
HR@LAB11 > BEGIN
  2     dbms_stats.gather_table_stats(
  3     ownname => user,
  4     tabname => 'EMP',
  5     estimate_percent => 100,
  6     method_opt => 'for all columns size auto', cascade => TRUE);
  7  END;
  8  /

Procedimento PL/SQL concluído com sucesso.

HR@LAB11 >
HR@LAB11 > SELECT num_rows,  avg_row_len
  2    FROM user_tab_statistics
  3   WHERE table_name = 'EMP';

  NUM_ROWS AVG_ROW_LEN
---------- -----------
       107          69

HR@LAB11 >
HR@LAB11 >
HR@LAB11 >
HR@LAB11 > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

HR@LAB11 > Alter session set optimizer_dynamic_sampling=0;

Sessão alterada.

HR@LAB11 >
HR@LAB11 >
HR@LAB11 > select /* tst104 */
  2         *
  3    from EMP;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DAT JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- -------- ---------- ---------- -------------- ---------- -------------
        198 Donald               OConnell                  DOCONNEL                  650.507.9833         21/06/07 SH_CLERK         2600                       124            50
        199 Douglas              Grant                     DGRANT                    650.507.9844         13/01/08 SH_CLERK         2600                       124            50
        200 Jennifer             Whalen                    JWHALEN                   515.123.4444         17/09/03 AD_ASST          4400                       101            10
        201 Michael              Hartstein                 MHARTSTE                  515.123.5555         17/02/04 MK_MAN          13000                       100            20
        202 Pat                  Fay                       PFAY                      603.123.6666         17/08/05 MK_REP           6000                       201            20
...
...
...
        197 Kevin                Feeney                    KFEENEY                   650.507.9822         23/05/06 SH_CLERK         3000                       124            50

107 linhas selecionadas.

HR@LAB11 >
HR@LAB11 >
HR@LAB11 > column sql_id new_value m_sql_id
HR@LAB11 > column child_number new_value m_child_no
HR@LAB11 >
HR@LAB11 > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%tst104%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
85hb1va9m5s58            0

HR@LAB11 >
HR@LAB11 >
HR@LAB11 > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));
antigo   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'))
novo   2:   FROM TABLE (dbms_xplan.display_cursor ('85hb1va9m5s58',         0,'typical iostats last'))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  85hb1va9m5s58, child number 0
-------------------------------------
select /* tst104 */        *   from EMP

Plan hash value: 3956160932

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |    107 |00:00:00.01 |      11 |
|   1 |  TABLE ACCESS FULL| EMP  |      1 |    107 |  7383 |     3   (0)| 00:00:01 |    107 |00:00:00.01 |      11 |
--------------------------------------------------------------------------------------------------------------------


13 linhas selecionadas.

HR@LAB11 >

 

Após a coleta de estatísticas podemos observar que estimativa de linhas selecionadas é igual ao número de linhas que a consulta retorna (107 linhas), ou seja, quando as estatísticas existem o Otimizador as utiliza.

Conclusão

Nesta simulação utilizamos uma consulta simples, a existência ou não de estatística não fez diferença em termos de plano de execução pois neste caso a consulta não tem filtro na clausula WHERE e a execução da operação “TABLE ACCESS FULL” na tabela “EMP” é inevitável. Entretanto se essa consulta fosse uma junção de duas tabelas, a existência das estatísticas de tabela seria crucial na decisão de qual tipo de JOIN seria utilizado nessa operação.

Referências

http://docs.oracle.com/cd/E25054_01/server.1111/e16638/stats.htm

http://kamranagayev.com/2011/10/31/dumping-an-oracle-data-block-reading-the-output-and-doing-some-math/

 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Deixe uma resposta

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