Histogramas: Quando devemos evitá-los?

agosto 9, 2018 V. J.A. Estatísticas

O uso de histogramas ajuda o Otimizador a ser mais preciso no cálculo da cardinalidade das operações no plano de execução, mas existem situações em que não devemos utilizá-los, pois eles vão induzir o Otimizador a criar planos de execução de baixa qualidade. Neste artigo vamos mostrar uma destas situações onde o Otimizador realiza um cálculo de cardinalidade muito ruim, devido a existência do histograma em uma coluna da tabela e como consequência ele vai gerar um plano de execução de baixo desempenho.

Para demonstrar esta situação, vamos realizar uma simulação prática com as seguintes etapas:

1) Criar uma tabela com um índice e coletar as estatísticas
2) Executar uma consulta na tabela criada e verificar o plano de execução
3) Eliminar o histograma da coluna incluída na cláusula WHERE da consulta
4) Executar a mesma consulta novamente e verificar o plano de execução

1) Criar a tabela, índice e estatísticas

Vamos criar os objetos necessários para realizar a nossa simulaçã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

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 dbtw054 as select * from dba_objects where rownum < 1;

Tabela criada.

SQL >
SQL > ALTER TABLE dbtw054 MODIFY (object_type varchar2(60));

Tabela alterada.

SQL >
SQL >
SQL > insert into dbtw054 select
2 OWNER,
3 OBJECT_NAME,
4 SUBOBJECT_NAME,
5 OBJECT_ID,
6 DATA_OBJECT_ID,
7 'x123456789y123456789z123456789w123'||OBJECT_TYPE,
8 CREATED,
9 LAST_DDL_TIME,
10 TIMESTAMP,
11 STATUS,
12 TEMPORARY,
13 GENERATED,
14 SECONDARY,
15 NAMESPACE,
16 EDITION_NAME
17 from dba_objects
18 where object_type in ('QUEUE',
19 'TABLE SUBPARTITION',
20 'RULE SET',
21 'CONSUMER GROUP',
22 'MATERIALIZED VIEW',
23 'PROGRAM',
24 'DATABASE LINK',
25 'JOB CLASS',
26 'EVALUATION CONTEXT',
27 'UNDEFINED',
28 'RESOURCE PLAN',
29 'CLUSTER',
30 'JAVA SOURCE',
31 'WINDOW',
32 'INDEXTYPE',
33 'RULE',
34 'CONTEXT',
35 'DIMENSION',
36 'SCHEDULER GROUP',
37 'SCHEDULE',
38 'DESTINATION',
39 'EDITION',
40 'REWRITE EQUIVALENCE',
41 'LOB PARTITION');

362 linhas criadas.

SQL >
SQL > commit;

Commit concluído.

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

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL >
SQL > create index dbtw054_IDX on dbtw054(object_type);

Índice criado.

SQL >

2) Executar uma consulta usando a 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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90

SQL > ALTER SESSION SET statistics_level = ALL;

Sessão alterada.

SQL >
SQL > SELECT /* DBTW-054.1 */ object_name,
2 owner
3 FROM DBTW054
4 WHERE object_type='x123456789y123456789z123456789w123RULE';

OBJECT_NAME OWNER
------------------------------ -------------------
SYS$SERVICE_METRICS$61 SYS
ALERT_QUE$1 SYS
SCHEDULER$_EVENT_QUEUE$1 SYS
SCHEDULER$_EVENT_QUEUE$3 SYS

SQL >
SQL >
SQL > -- Recupera o SQL_ID e o CHILD_NUMBER da consulta executada
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-054.1%'
4 AND sql_text NOT LIKE '%v$sql%';

SQL_ID CHILD_NUMBER
------------- ------------
3h619x7nhkxq9 0

SQL >
SQL > -- Gera o relatorio do plano de execucao da consulta
SQL >
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3h619x7nhkxq9, child number 0
-------------------------------------
SELECT /* DBTW-054.1 */ object_name, owner FROM DBTW054 WHERE
object_type=:"SYS_B_0"

Plan hash value: 1846229803

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 4 |00:00:00.01 | 16 |
|* 1 | TABLE ACCESS FULL| DBTW054 | 1 | 362 | 26788 | 5 (0)| 00:00:01 | 4 |00:00:00.01 | 16 |
-----------------------------------------------------------------------------------------------------------------------

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

1 - filter("OBJECT_TYPE"=:SYS_B_0)

19 linhas selecionadas.

SQL >
SQL > SELECT column_name,
2 num_distinct,
3 density,
4 num_nulls,
5 histogram,
6 num_buckets
7 FROM user_tab_col_statistics
8 WHERE table_name = 'DBTW054'
9 AND column_name='OBJECT_TYPE';

COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS HISTOGRAM NUM_BUCKETS
------------------------------ ------------ ------------ ---------- --------------- -----------
OBJECT_TYPE 22 .001381215 0 FREQUENCY 1

1 linha selecionada.

SQL >
SQL > SELECT COUNT(1)
2 FROM dbtw054;

COUNT(1)
----------
362

1 linha selecionada.

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

Observe no plano de execução que o Otimizador estimou que a operação TABLE ACCESS FULL selecionaria 362 linhas (E-ROWS) quando na verdade foram selecionada somente 4 linhas (A-ROWS), este erro absurdo ocorreu por que o Otimizador utiliza preferencialmente o histograma, quando ele existe, para fazer o cálculo da cardinalidade e quando geramos as estatísticas no inicio da simulação utilizando o pacote DBMS_STATS a criação do histograma na coluna OBJECT_TYPE considerou somente os 32 caracteres iniciais desta coluna, desprezando o resto (limite da versão 11g).

Consultando a visão de estatísticas das colunas (USER_TAB_COL_STATISTICS) verificamos que a coluna OBJECT_TYPE possui um histograma do tipo FREQUENCY com 1 BUCKET, quando deveriam ser 22 BUCKETS pois esta coluna tem 22 valores distintos. Este histograma defeituoso é o resultado da combinação de duas situações, os primeiros 32 caracteres dos valores presentes na coluna OBJECT_TYPE serem iguais em todas as linhas da tabela e ao limite de 32 caracteres da versão 11g para a criação de histogramas.

Obs: Na versão 12c este limite é de 64 caracteres.

3) Eliminar o histograma da coluna OBJECT_TYPE

Para ajudar o Otimizador na estimativa da cardinalidade das linhas que serão selecionadas na tabela, podemos eliminar o histograma criado na coluna OBJECT_TYPE e alterar as preferências da coleta de estatísticas de forma que este histograma não seja mais gerado.

1
2
3
4
5
6
7
8
9
10
11

SQL > exec dbms_stats.delete_column_stats(ownname=> USER, tabname=>'DBTW054', colname=>'OBJECT_TYPE', col_stat_type=>'HISTOGRAM');

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL >
SQL > exec dbms_stats.set_table_prefs(USER, 'DBTW054', 'METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 1 OBJECT_TYPE');

Procedimento PL/SQL concluído com sucesso.

SQL >

4) Executar a mesma consulta novamente

Vamos executar uma consulta na tabela e verificar a eficiência do plano de execução:

SQL > ALTER SESSION SET statistics_level = ALL;

Sessão alterada.

SQL >
SQL > SELECT /* DBTW-054.2 */ object_name,
2 owner
3 FROM DBTW054
4 WHERE object_type='x123456789y123456789z123456789w123RULE';

OBJECT_NAME OWNER
------------------------------ ----------------------------
SYS$SERVICE_METRICS$61 SYS
ALERT_QUE$1 SYS
SCHEDULER$_EVENT_QUEUE$1 SYS
SCHEDULER$_EVENT_QUEUE$3 SYS

4 linhas selecionadas.

SQL >
SQL >
SQL > -- Recupera o SQL_ID e o CHILD_NUMBER da consulta executada
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-054.2%'
4 AND sql_text NOT LIKE '%v$sql%';

SQL_ID CHILD_NUMBER
------------- ------------
fm9ww6785g3dm 0

1 linha selecionada.

SQL >
SQL > -- Gera o relatorio do plano de execucao da consulta
SQL >
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fm9ww6785g3dm, child number 0
-------------------------------------
SELECT /* DBTW-054.2 */ object_name, owner FROM DBTW054 WHERE
object_type=:"SYS_B_0"

Plan hash value: 87264188

-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 4 |00:00:00.01 | 6 |
| 1 | TABLE ACCESS BY INDEX ROWID| DBTW054 | 1 | 16 | 1184 | 4 (0)| 00:00:01 | 4 |00:00:00.01 | 6 |
|* 2 | INDEX RANGE SCAN | DBTW054_IDX | 1 | 16 | | 1 (0)| 00:00:01 | 4 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------------------------------------------------------------

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

2 - access("OBJECT_TYPE"=:SYS_B_0)

20 linhas selecionadas.

SQL >
SQL > SELECT column_name,
2 num_distinct,
3 density,
4 num_nulls,
5 histogram,
6 num_buckets
7 FROM user_tab_col_statistics
8 WHERE table_name = 'DBTW054'
9 AND column_name='OBJECT_TYPE';

COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS HISTOGRAM NUM_BUCKETS
------------------------------ ------------ ------------ ---------- --------------- -----------
OBJECT_TYPE 22 .045454545 0 NONE 1

1 linha selecionada.

SQL >

Após a exclusão do histograma podemos verificar que a estimativa de cardinalidade melhorou (E-ROWS=16) e ficou mais próxima da quantidade de linhas selecionas (A-ROWS=4), com isso o Otimizador considerou que a leitura das linhas utilizando um índice seria mais eficiente.

Referências

https://docs.oracle.com/cd/E11882_01/server.112

2 comments

JOSÉ GABRIEL DE NOVAIS XAVIER

agosto 13, 2018 at 07:42

Muito bom, onde trabalhei como DBA nós não usávamos histogramas, apenas indices baseado nas estatisticas dos select’s.

Aprendi mais um pouco sobre o assunto. Vlw!

Responder
- V. J.A.
  
  agosto 13, 2018 at 14:08
  
  Obrigado pelo seu comentário José Gabriel!
  Fico muito feliz que o artigo te ajudou a conhecer um pouco mais sobre Histogramas.
  
  Responder

Histogramas: Quando devemos evitá-los?

1) Criar a tabela, índice e estatísticas

2) Executar uma consulta usando a tabela criada

Referências

Artigos semelhantes:

2 comments

Deixe um comentário Cancelar resposta