Nem só de estatísticas vive o Otimizador

Constraints

As estatísticas do banco de dados Oracle são a matéria prima que o Otimizador utiliza para gerar o plano de execução de uma instrução SQL, quanto melhor for a qualidade das estatísticas maior será o desempenho do plano de execução, porem o Otimizador trabalha com outras informações além das estatísticas, ele utiliza alguns parâmetros de inicialização do banco, ele pode utilizar informações de alguns recursos que influenciam em suas decisões (SQL_Profile, SQL_Patch, SQL Plan Baseline, Hints, etc…), mas os que nem todos sabem é que o Otimizador utiliza informações das constraints para definir que tipo de operação será utilizada para acessar uma tabela.
 
Neste artigo vamos mostrar um exemplo de uma consulta que deveria utilizar um índice mas devido a ausência de uma CONSTRAINT o Otimizador escolhe fazer uma operação TABLE ACCESS FULL.

Para realizar esta simulação vamos passar pelas seguintes etapas:

1) Criar uma tabela com um índice
2) Executar uma consulta na tabela que faz um COUNT() na coluna utilizada no índice
3) Verificar porque o Otimizador não utilizou o índice
4) Executar a consulta novamente utilizando o índice

1) Criar uma tabela com um índice

Vamos criar uma tabela com 500 mil registros, criar um índice na coluna CATEGORY 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
SQL > SELECT * FROM V$VERSION where rownum < 2;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

1 linha selecionada.

SQL >
SQL > create table dbtw058 (id , category, text1, text2, text3 )
  2      as
  3      select rownum, 'MOD-'||mod(rownum, 10), 'DBTimeWizard - Performance and Tuning', DBMS_RANDOM.STRING('A', 100), DBMS_RANDOM.STRING('A', 100)
  4        from dual connect by level <= 500000;

Tabela criada.

SQL >
SQL > create index dbtw058_idx on dbtw058(category);

Índice criado.

SQL >
SQL > exec dbms_stats.gather_table_stats(ownname=> USER, tabname=> 'DBTW058', cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE AUTO',force=> true,degree=> 16);

Procedimento PL/SQL concluído com sucesso.

SQL >

 

2) Executar uma consulta na tabela criada

 

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

Sessão alterada.

SQL >
SQL > SELECT /* dbtw-058.1 */ category, count(1)
  2    FROM dbtw058
  3   GROUP BY category
  4   ORDER BY category;

CATEGORY                                       COUNT(1)
-------------------------------------------- ----------
MOD-0                                             50000
MOD-1                                             50000
MOD-2                                             50000
MOD-3                                             50000
MOD-4                                             50000
MOD-5                                             50000
MOD-6                                             50000
MOD-7                                             50000
MOD-8                                             50000
MOD-9                                             50000

10 linhas selecionadas.

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

SQL_ID        CHILD_NUMBER
------------- ------------
2gt3ddpgpjp5y            0

1 linha selecionada.

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2gt3ddpgpjp5y, child number 0
-------------------------------------
SELECT /* dbtw-058.1 */ category, count(1)   FROM dbtw058  GROUP BY
category  ORDER BY category

Plan hash value: 3195600276

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |      1 |        |     10 |00:00:00.21 |   17902 |  17858 |       |       |          |
|   1 |  SORT ORDER BY      |         |      1 |     10 |     10 |00:00:00.21 |   17902 |  17858 |  2048 |  2048 | 2048  (0)|
|   2 |   HASH GROUP BY     |         |      1 |     10 |     10 |00:00:00.21 |   17902 |  17858 |  1484K|  1484K| 1021K (0)|
|   3 |    TABLE ACCESS FULL| DBTW058 |      1 |    500K|    500K|00:00:00.16 |   17902 |  17858 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------


16 linhas selecionadas.

SQL >

 

Observe no plano de execução que apesar de termos criado um índice na coluna CATEGORY, o Otimizador resolveu acessar os registros na tabela utilizando uma operação TABLE ACCESS FULL. 

3) Verificar porque o Otimizador não utilizou o índice

 

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
SQL > COL index_name FOR a20
SQL > SELECT index_name,
  2         num_rows,
  3         distinct_keys,
  4         clustering_factor,
  5         status,
  6         visibility,
  7         last_analyzed
  8    FROM user_indexes
  9   WHERE table_name = 'DBTW058';

INDEX_NAME             NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR STATUS     VISIBILIT LAST_ANA
-------------------- ---------- ------------- ----------------- ---------- --------- --------
DBTW058_IDX              500000            10            178606 VALID      VISIBLE   04/01/19

1 linha selecionada.

SQL >
SQL > COL column_name FOR a20
SQL > SELECT column_name,
  2         num_distinct,
  3         density,
  4         num_nulls,
  5         avg_col_len,
  6         histogram
  7    FROM user_tab_col_statistics
  8   WHERE table_name = 'DBTW058';

COLUMN_NAME          NUM_DISTINCT      DENSITY  NUM_NULLS AVG_COL_LEN HISTOGRAM
-------------------- ------------ ------------ ---------- ----------- ---------------
ID                         500000   .000002000          0           5 NONE
CATEGORY                       10   .100000000          0           6 NONE
TEXT1                           1  1.000000000          0          38 NONE
TEXT2                      500000   .000002000          0         101 NONE
TEXT3                      500000   .000002000          0         101 NONE

5 linhas selecionadas.

SQL >
SQL > SET lines 60
SQL > DESC dbtw058
 Nome                          Nulo?    Tipo
 ----------------------------- -------- --------------------
 ID                                     NUMBER
 CATEGORY                               VARCHAR2(44)
 TEXT1                                  CHAR(37)
 TEXT2                                  VARCHAR2(4000)
 TEXT3                                  VARCHAR2(4000)

SQL > SET lines 200
SQL >
SQL > COL segment_name FOR a20
SQL > SELECT segment_name, segment_type, ROUND(bytes/1024/1024) MB
  2    FROM user_segments
  3   WHERE segment_name IN ('DBTW058','DBTW058_IDX');

SEGMENT_NAME         SEGMENT_TYPE               MB
-------------------- ------------------ ----------
DBTW058              TABLE                     144
DBTW058_IDX          INDEX                      10

2 linhas selecionadas.

SQL >

 

Investigando o motivo pelo qual o Otimizador não utilizou o índice no plano de execução podemos chegar a algumas conclusões baseadas nas informações da tabela e do índice acima:

1) A consulta trabalha somente com a coluna CATEGORY, logo isso não seria obstáculo para utilização do índice
2) Não há registros nulos na coluna CATEGORY, logo a quantidade de registros no índice é igual a quantidade de registro na tabela
3) A descrição das colunas da tabela mostra que a coluna CATEGORY não possui uma CONSTRAINT NOT NULL

Como não existe uma CONSTRAINT NOT NULL na coluna CATEGORY o Otimizador não tem como saber se existe algum registro com valor nulo nesta coluna, pois apesar das estatísticas da coluna CATEGORY indicarem que não existem valores nulos, precisamos lembrar que a atualização das estatísticas não é dinâmica, ela reflete uma posição em um determinado momento, após a ultima coleta de estatísticas pode ter sido inserido um registro nesta tabela com valor nulo na coluna CATEGORY.

Portanto, a menos que seja criada uma CONSTRAINT NOT NULL na coluna CATEGORY, o Otimizador não utilizará o índice criado nesta coluna, pois ele não tem como saber COM PRECISÃO se o índice tem a mesma quantidade de registros existentes na tabela naquele momento.

 

4) Executar a consulta novamente utilizando o índice

Para permitir a utilização do índice vamos criar a CONSTRAINT NOT NULL na coluna CATEGORY e verificar se o Otimizador vai utilizar o índice no 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
SQL > ALTER TABLE dbtw058 MODIFY (category NOT NULL);

Tabela alterada.

SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL >
SQL >
SQL > SELECT /* dbtw-058.2 */ category, count(1)
  2    FROM dbtw058
  3   GROUP BY category
  4   ORDER BY category;

CATEGORY                                       COUNT(1)
-------------------------------------------- ----------
MOD-0                                             50000
MOD-1                                             50000
MOD-2                                             50000
MOD-3                                             50000
MOD-4                                             50000
MOD-5                                             50000
MOD-6                                             50000
MOD-7                                             50000
MOD-8                                             50000
MOD-9                                             50000

10 linhas selecionadas.

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

SQL_ID        CHILD_NUMBER
------------- ------------
bwt9h7a7ttk33            0

1 linha selecionada.

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bwt9h7a7ttk33, child number 0
-------------------------------------
SELECT /* dbtw-058.2 */ category, count(1)   FROM dbtw058  GROUP BY
category  ORDER BY category

Plan hash value: 3494643104

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |      1 |        |     10 |00:00:00.09 |    1198 |       |       |          |
|   1 |  SORT ORDER BY         |             |      1 |     10 |     10 |00:00:00.09 |    1198 |  2048 |  2048 | 2048  (0)|
|   2 |   HASH GROUP BY        |             |      1 |     10 |     10 |00:00:00.09 |    1198 |  1484K|  1484K| 1008K (0)|
|   3 |    INDEX FAST FULL SCAN| DBTW058_IDX |      1 |    500K|    500K|00:00:00.04 |    1198 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------


16 linhas selecionadas.

SQL >

 

Analisando o plano de execução podemos verificar que o Otimizador passou a utilizar o índice, o numero de buffers acessados caiu de 17K para 1K e o tempo de execução caiu de 21 centésimos de segundo para 9 centésimos.

CONCLUSÃO

Este é apenas um exemplo de como a falta de CONSTRAINTS em uma tabela pode afetar de forma negativa o plano de execução de uma instrução SQL, portanto é imperativo que durante a criação de uma base de dados as CONSTRAINTS PRIMARY KEY, UNIQUE KEY, NOT NULL, etc… sejam corretamente definidas para que o Otimizador consiga entregar um plano de execução com melhor desempenho.

 
 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 

2 comments

Deixe uma resposta

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