O Otimizador do Oracle precisa da nossa ajuda?

Extended statistics

As estatísticas sobre os dados de uma tabela são fundamentais para o Otimizador escolher qual a maneira mais eficiente de acessar esses dados, mas existem informações sobre os dados que não são coletadas de forma automática e portanto não estão disponíveis para o Otimizador sem que haja uma intervenção manual, o que causa em algumas situações a escolha de planos de execução ineficientes. Respondendo a questão proposta no titulo desse artigo: sim, em algumas situações especificas precisamos ajudar o Otimizador, coletando estatísticas adicionais.

Neste artigo vamos abordar as “Extended statistics”, vamos simular uma consulta onde o Otimizador não consegue estimar a cardinalidade da operação de forma correta e como consequência cria um plano de execução ineficiente.

Conceito “Extended statistics”

As “Extended statistics” foram implementadas no Banco Oracle para tratar dois tipos de situações onde as estatísticas existentes não fornecem informações suficientes para o Otimizador fazer uma boa estimativa da cardinalidade:

1. Quando existem múltiplos predicados em diferentes colunas na clausula WHERE de uma instrução SQL para uma tabela e existe algum tipo de relação entre essas colunas, por exemplo quando temos uma tabela de veículos automotores e nela temos duas colunas: Nome do veiculo e Fabricante. Nesse caso existe uma relação entre essas duas colunas pois os registros existentes na tabela de um determinado veiculo só podem ser de um determinado fabricante. (Ex: Uno/Fiat, HB20/Hyndai, Fiesta/Ford,etc..)

2. Quando um predicado utiliza uma expressão, por exemplo uma função do Oracle ou uma função criada pelo usuário.

Simulação “Extended statistics”

A seguir vamos simular a consulta de uma tabela onde duas colunas possuem um relacionamento e em função disso as estatísticas tradicionais não são capazes de fornecer as informações necessárias para o Otimizador fazer uma boa estimativa da cardinalidade da operação, na sequência vamos criar as “Extended statistics” e verificar que com esse tipo de informações o Otimizador vai melhorar significativamente a estimativa da cardinalidade e como consequência melhorar o plano de execução.

Criação da tabela

Primeiro vamos criar a tabela que utilizaremos na consulta da nossa simulação e popular com dados que tenham alguma relação entre duas colunas:

 

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
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 dbtw01 (id number, code1k number, code100 number, text varchar(40) );

Table created.

SQL >
SQL > DECLARE
  2
  3     v_id     NUMBER := 1;
  4     var_x    NUMBER := 1;
  5     l_num    number;
  6
  7  BEGIN
  8
  9     FOR var1 IN 1..100
 10     LOOP
 11        var_x := 1;
 12        FOR var2 IN 1..2
 13        LOOP
 14           l_num := round(dbms_random.value(100,0));
 15           insert into dbtw01 values(v_id, var_x, l_num, 'DBTimeWizard - Performance and Tuning');
 16           v_id := v_id + 1;
 17        END LOOP;
 18        var_x := 4;
 19        FOR var2 IN 1..8
 20        LOOP
 21           l_num := round(dbms_random.value(100,0));
 22           insert into dbtw01 values(v_id, var_x, l_num, 'DBTimeWizard - Performance and Tuning');
 23           v_id := v_id + 1;
 24        END LOOP;
 25        var_x := 7;
 26        FOR var2 IN 1..50
 27        LOOP
 28           l_num := round(dbms_random.value(100,0));
 29           insert into dbtw01 values(v_id, var_x, l_num, 'DBTimeWizard - Performance and Tuning');
 30           v_id := v_id + 1;
 31        END LOOP;
 32        var_x := 11;
 33        FOR var2 IN 1..150
 34        LOOP
 35           l_num := round(dbms_random.value(100,0));
 36           insert into dbtw01 values(v_id, var_x, l_num, 'DBTimeWizard - Performance and Tuning');
 37           v_id := v_id + 1;
 38        END LOOP;
 39        var_x := 13;
 40        FOR var2 IN 1..300
 41        LOOP
 42           l_num := round(dbms_random.value(100,0))+var_x;
 43           insert into dbtw01 values(v_id, var_x, l_num, 'DBTimeWizard - Performance and Tuning');
 44           v_id := v_id + 1;
 45        END LOOP;
 46        var_x := 17;
 47        FOR var2 IN 1..490
 48        LOOP
 49           l_num := round(dbms_random.value(100,0));
 50           insert into dbtw01 values(v_id, var_x, l_num, 'DBTimeWizard - Performance and Tuning');
 51           v_id := v_id + 1;
 52        END LOOP;
 53        var_x := 19;
 54        FOR var2 IN 1..200
 55        LOOP
 56           insert into dbtw01 values(v_id, var_x, 35, 'DBTimeWizard - Performance and Tuning');
 57           v_id := v_id + 1;
 58        END LOOP;
 59        COMMIT;
 60     END LOOP;
 61
 62  END;
 63  /

PL/SQL procedure successfully completed.

SQL >
SQL > create index dbtw01_idx on dbtw01(code100, code1k) compress 2;

Index created.

SQL >
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 >
SQL > select column_name, num_distinct, histogram from dba_tab_col_statistics where table_name='DBTW01';

COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
TEXT                                      1 FREQUENCY
CODE100                                 114 FREQUENCY
CODE1K                                    7 FREQUENCY
ID                                   120000 NONE

SQL >

 

Execução da consulta

Agora vamos executar uma consulta que vai acessar a tabela criada na etapa anterior:

 

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
SQL > ALTER SESSION SET statistics_level=ALL;

Session altered.

SQL >
SQL > select /* dbtw101 */ id, code1k, code100, text
  2    from dbtw01
  3   where code1k=17
  4     and code100=35;

        ID     CODE1K    CODE100 TEXT
---------- ---------- ---------- ----------------------------------------
       598         17         35 DBTimeWizard - Performance and Tuning
       527         17         35 DBTimeWizard - Performance and Tuning
       539         17         35 DBTimeWizard - Performance and Tuning
..............
    119364         17         35 DBTimeWizard - Performance and Tuning

485 rows selected.

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 '%dbtw101%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
9sscfjtmkf8q2            0

SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9sscfjtmkf8q2, child number 0
-------------------------------------
select /* dbtw101 */ id, code1k, code100, text   from dbtw01  where
code1k=:"SYS_B_0"    and code100=:"SYS_B_1"

Plan hash value: 658621498

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |      1 |        |       |   273 (100)|          |    485 |00:00:00.01 |     980 |
|*  1 |  TABLE ACCESS FULL| DBTW01 |      1 |   8486 |   406K|   273   (1)| 00:00:04 |    485 |00:00:00.01 |     980 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("CODE100"=:SYS_B_1 AND "CODE1K"=:SYS_B_0))


19 rows selected.

SQL >

 

A consulta retornou 485 linhas e para tal realizou uma operação “TABLE ACCESS FULL”, o Otimizador escolheu esta operação pois ele estimou que teria que acessar 8.486 linhas e para essa quantidade de linhas o acesso utilizando um índice seria mais oneroso.

 

Utilizando “Extended statistics”

Agora vamos dar uma pequena ajuda ao Otimizador, para tal vamos criar as “Extended statistics” das duas colunas que estão sendo utilizadas na clausula WHERE e executar novamente a mesma 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
70
71
72
73
74
75
76
77
78
79
80
81
SQL > SELECT DBMS_STATS.CREATE_EXTENDED_STATS(null,'dbtw01', '(code1k, code100)') FROM DUAL;

DBMS_STATS.CREATE_EXTENDED_STATS(NULL,'DBTW01','(CODE1K,CODE100)')
---------------------------------------------------------------------------------------------------------------------------------------------------
SYS_STUGYIUDX9_XG#MKTWSLK2DJ54

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 >
SQL > select column_name, num_distinct, histogram from dba_tab_col_statistics where table_name='DBTW01';

COLUMN_NAME                    NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
SYS_STUGYIUDX9_XG#MKTWSLK2DJ54          595 HEIGHT BALANCED
TEXT                                      1 FREQUENCY
CODE100                                 114 FREQUENCY
CODE1K                                    7 FREQUENCY
ID                                   120000 NONE

SQL >
SQL > select /* dbtw102 */ id, code1k, code100, text
  2    from dbtw01
  3   where code1k=17
  4     and code100=35;

        ID     CODE1K    CODE100 TEXT
---------- ---------- ---------- ----------------------------------------
       598         17         35 DBTimeWizard - Performance and Tuning
       527         17         35 DBTimeWizard - Performance and Tuning
       539         17         35 DBTimeWizard - Performance and Tuning
..........
    119364         17         35 DBTimeWizard - Performance and Tuning

485 rows selected.

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 '%dbtw102%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
59v6y95ffxr2y            0

SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  59v6y95ffxr2y, child number 0
-------------------------------------
select /* dbtw102 */ id, code1k, code100, text   from dbtw01  where
code1k=:"SYS_B_0"    and code100=:"SYS_B_1"

Plan hash value: 954539352

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |      1 |        |       |    78 (100)|          |    485 |00:00:00.01 |     344 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DBTW01     |      1 |    148 |  7400 |    78   (0)| 00:00:01 |    485 |00:00:00.01 |     344 |
|*  2 |   INDEX RANGE SCAN          | DBTW01_IDX |      1 |    148 |       |     1   (0)| 00:00:01 |    485 |00:00:00.01 |      35 |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CODE100"=:SYS_B_1 AND "CODE1K"=:SYS_B_0)


20 rows selected.

SQL >

 

A consulta retornou 485 linhas novamente e o Otimizador estimou que seriam 148 linhas, essa estimativa esta muito próxima do valor real, para essa quantidade de linhas a utilização de um índice é mais eficiente, por esta razão ele utilizou o índice DBTW01_IDX.

Conclusão

Quando comparamos a quantidade de buffers acessados pelas duas consultas, podemos constatar que a segunda consulta foi mais eficiente, pois enquanto na primeira consulta foram acessados 980 buffers, na segunda a quantidade caiu para 344 buffers.

A falha na estimativa da cardinalidade da operação pelo Otimizador é uma das principais razões que levam o Otimizador a criar planos de execução ineficientes.

As “Extended statistics” podem e devem ser utilizadas sempre que o Otimizador não consegue fazer uma boa estimativa da cardinalidade da operação, pois os predicados presentes na clausula WHERE de uma instrução SQL possuem algum relacionamento ou quando a instrução SQL utiliza função no predicado.

Referências

https://blogs.oracle.com/optimizer/entry/extended_statistics

https://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF94725

 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Deixe uma resposta

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