Histogramas: Qual importância deles?

março 6, 2017 V. J.A. Estatísticas

No artigo “Por que o Otimizador criou um plano de execução ineficiente?” elencamos algumas condições que levam o Otimizador a gerar um plano de execução ineficiente, neste artigo vamos abordar com mais detalhes um desses tópicos, como a repetição de dados não uniformes em uma coluna da tabela podem influenciar de maneira negativa o Otimizador.

Repetição de dados não uniformes

Quando o Otimizador gera o plano de execução de uma instrução SQL e na cláusula WHERE dessa instrução existe um filtro numa coluna da tabela que possui um número de valores distintos muito inferior ao numero total de registros da tabela, o Otimizador utiliza uma regra simples para calcular a cardinalidade, ele divide o numero total de registros da tabela pelo numero de valores distintos. Essa formula pode ser eficaz se para cada valor distinto houver uma quantidade de registros semelhante, porem ela se torna ineficaz quando um ou mais valores tem uma quantidade de registros muito superior ao outros.

Para que o Otimizador consiga calcular a cardinalidade de forma mais precisa na condição apresentada acima é necessário a utilização de um histograma na coluna cuja repetição de dados não é uniforme. Para geração desse histograma devemos utilizar o pacote DBMS_STATS.

Para ver na prática essa situação vamos realizar as seguintes operações:

1) Criar uma tabela e coletar as estatísticas sem a geração do histograma
2) Executar uma consulta que utilizará essa tabela como fonte de dados e verificar a cardinalidade estimada pelo Otimizador
3) Coletar as estastísticas da tabela gerando o histograma
4) Executar a mesma consulta novamente e verificar a cardinalidade estimada pelo Otimizador

Criar a tabela

Primeiro vamos clonar a tabela EMPLOYEES do SCHEMA HR, coletar as estatísticas sem gerar o histograma na coluna JOB_ID que será utilizada na cláusula WHERE da consulta.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SQL > SELECT * FROM V$VERSION where rownum < 2;

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

1 linha selecionada.

SQL > create table employees as select * from hr.employees;

Tabela criada.

SQL > CREATE UNIQUE INDEX EMP_EMP_ID_PK ON EMPLOYEES (EMPLOYEE_ID);

Índice criado.

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

Procedimento PL/SQL concluído com sucesso.

SQL >

Executar a consulta

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

Agora vamos executar uma consulta na tabela criada e listar o plano de execução utilizado pelo Otimizador

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

SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL >
SQL > select /* dbtw001 */ EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID, JOB_ID
2 from employees
3 where JOB_ID = 'AD_ASST';

EMPLOYEE_ID LAST_NAME DEPARTMENT_ID JOB_ID
----------- ------------------------- ------------- ----------
200 Whalen 10 AD_ASST

1 linha selecionada.

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

SQL_ID CHILD_NUMBER
------------- ------------
4aukqvu2t0yvh 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 4aukqvu2t0yvh, child number 0
-------------------------------------
select /* dbtw001 */ EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID, JOB_ID
from employees where JOB_ID = :"SYS_B_0"

Plan hash value: 1445457117

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 6 | 1 |00:00:00.01 | 4 |
-----------------------------------------------------------------------------------------

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

1 - filter("JOB_ID"=:SYS_B_0)

19 linhas selecionadas.

SQL > column "Num. linhas na tabela" new_value num_lin_tot
SQL > column "Num. linhas distintas" new_value num_lin_dist
SQL >
SQL > select count(1) "Num. linhas na tabela" from employees;

Num. linhas na tabela
---------------------
107

1 linha selecionada.

SQL > select count(distinct(JOB_ID)) "Num. linhas distintas" from employees;

Num. linhas distintas
---------------------
19

1 linha selecionada.

SQL > select round(&num_lin_tot/&num_lin_dist) CARDINALIDADE from dual;

CARDINALIDADE
-------------
6

1 linha selecionada.

SQL >

No plano de execução verificamos que o Otimizador estimou que a cardinalidade da tabela “EMPLOYEES” seriam 6 registros (E-ROWS), quando na verdade foi 1 registro (A-ROWS).

O Otimizador calculou a cardinalidade como 6 pois ele dividiu a quantidade total de registros (107) pelo número de valores distintos na coluna JOB_ID (19).

Coletar as estatísticas com histograma

Para gerar histogramas em todas as colunas que possuem repetição de dados não uniforme na tabela é necessário utilizar a cláusula “method_opt=> ‘FOR ALL COLUMNS SIZE SKEWONLY'”.

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 column_name, num_distinct, histogram
2 FROM user_tab_col_statistics
3 WHERE table_name = 'EMPLOYEES'
4 AND column_name = 'JOB_ID';

Coluna NUM_DISTINCT HISTOGRAM
-------------------- ------------ ---------------
JOB_ID 19 NONE

1 linha selecionada.

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

Procedimento PL/SQL concluído com sucesso.

SQL > SELECT column_name, num_distinct, histogram
2 FROM user_tab_col_statistics
3 WHERE table_name = 'EMPLOYEES'
4 AND column_name = 'JOB_ID';

Coluna NUM_DISTINCT HISTOGRAM
-------------------- ------------ ---------------
JOB_ID 19 FREQUENCY

1 linha selecionada.

SQL >

Executar a consulta novamente

Finalmente vamos executar a consulta na tabela com os histogramas gerados pelo pacote DBMS_STATS e verificar a qualidade da cardinalidade estimada pelo Otimizador.

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

SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL >
SQL > select /* dbtw002 */ EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID, JOB_ID
2 from employees
3 where JOB_ID = 'AD_ASST';

EMPLOYEE_ID LAST_NAME DEPARTMENT_ID JOB_ID
----------- ------------------------- ------------- ----------
200 Whalen 10 AD_ASST

1 linha selecionada.

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

SQL_ID CHILD_NUMBER
------------- ------------
dv574duqu8q1w 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 dv574duqu8q1w, child number 0
-------------------------------------
select /* dbtw002 */ EMPLOYEE_ID, LAST_NAME, DEPARTMENT_ID, JOB_ID
from employees where JOB_ID = :"SYS_B_0"

Plan hash value: 1445457117

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 1 | 1 |00:00:00.01 | 4 |
-----------------------------------------------------------------------------------------

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

1 - filter("JOB_ID"=:SYS_B_0)

19 linhas selecionadas.

SQL >

Analisando o plano de execução acima verificamos que a cardinalidade estimada pelo Otimizador foi perfeita (1 registro), igual a quantidade de registros lidos (E-ROWS = A-ROWS).

Conclusão

Nesse artigo verificamos na prática a importância do histograma para que o Otimizador consiga realizar estimativas de cardinalidade com precisão, o gerenciamento da coleta de estatísticas é fundamental para garantir a criação de histogramas em colunas de tabelas que possuem repetição de dados não uniforme e são mencionadas na cláusula WHERE das instruções SQL. O pacote DBMS_STATS pode e deve ser utilizado para fazer os ajustes necessários no processo de coleta de estatísticas de acordo com as características dos dados e como eles são atualizados.