Nem só de estatísticas vive o Otimizador

janeiro 10, 2019 V. J.A. Índice

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 >

Title of the document

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:

https://dbtimewizard.com.br/mentoria

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.