Qual a importância das estatísticas de colunas para o Otimizador?

fevereiro 21, 2016 V. J.A. Estatísticas

O Otimizador baseado em custo (CBO) utiliza estatísticas para determinar o custo dos diversos planos de execução que ele produz para uma determinada instrução SQL e escolhe o plano com o menor custo para utilizar, portanto as informações estatísticas são cruciais na escolha do plano de execução e devem fornecer informações precisas e atualizadas para que o Otimizador consiga realizar um bom trabalho.

Num artigo anterior abordamos a importância das estatísticas de tabelas para Otimizador, neste artigo vamos abordar como as estatísticas de colunas são utilizadas pelo Otimizador na de criação de um plano de execução para uma determinada instrução SQL.

COLUMN STATISTICS

As informações estatísticas de colunas podem ser consultadas na visão USER_TAB_COLUMNS, existem muitos informações disponíveis nessa visão mas os campos mais importantes para o Otimizador são:

NUM_DISTINCT – Número de valores distintos em uma determinada coluna
DENSITY – Algoritmo para estimar a seletividade de uma coluna, formula: 1/num_distinct para colunas sem histogramas
NUM_NULLS – Número de linhas na tabela que uma determinada coluna tem o valor nulo
HISTOGRAM – Indica a existência ou tipo de histograma para uma determinada coluna

Neste artigo vamos considerar a coluna HISTOGRAM=NONE, ou seja não existe histogramas para essa coluna, vamos abordar a utilização de histogramas pelo Otimizador num futuro post. Para ilustrar na prática a utilização das estatísticas de colunas pelo Otimizador, vamos executar uma consulta simples e verificar que formula o Otimizador utiliza para calcular a cardinalidade da operação apresentada no plano de execução.

Criação da tabela para a consulta

Para execução da consulta vamos criar uma tabela TAB1, alterar um campo para nulo em algumas linhas da tabela e gerar as estatísticas dessa tabela.

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

appuser@LAB11 > SELECT * FROM V$VERSION where rownum < 2;

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

appuser@LAB11 > create table tab1
2 as
3 select *
4 from all_objects
5 where rownum <= 10000;

Table created.

appuser@LAB11 > update tab1 set object_type = null
2 where object_type = 'TABLE';

1022 rows updated.

appuser@LAB11 >
appuser@LAB11 > commit;

Commit complete.

appuser@LAB11 > begin
2 dbms_stats.gather_table_stats(
3 ownname => user,
4 tabname =>'TAB1',
5 method_opt => 'for all columns size 1'
6 );
7 end;
8 /

PL/SQL procedure successfully completed.

appuser@LAB11 > select sample_size, num_nulls, num_distinct, histogram
2 from user_tab_columns
3 where table_name = 'TAB1'
4 and column_name = 'OBJECT_TYPE';

SAMPLE_SIZE NUM_NULLS NUM_DISTINCT HISTOGRAM
----------- ---------- ------------ ---------------
8978 1022 18 NONE

appuser@LAB11 >

Plano de execução da 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 a consulta e verificar como o Otimizador calculará a cardinalidade da operação de acesso as linhas da tabela.

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

appuser@LAB11 > ALTER SESSION SET statistics_level=ALL;

Session altered.

appuser@LAB11 > select /* tst101 */ count(*)
2 from TAB1
3 where object_type = 'INDEX';

COUNT(*)
----------
1142

appuser@LAB11 > column sql_id new_value m_sql_id
appuser@LAB11 > column child_number new_value m_child_no
appuser@LAB11 >
appuser@LAB11 > SELECT sql_id, child_number
2 FROM v$sql
3 WHERE sql_text LIKE '%tst101%'
4 AND sql_text NOT LIKE '%v$sql%';

SQL_ID CHILD_NUMBER
------------- ------------
6j1gtqn30y14t 0

appuser@LAB11 > SELECT *
2 FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'));
old 2: FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'typical iostats last'))
new 2: FROM TABLE (dbms_xplan.display_cursor ('6j1gtqn30y14t', 0,'typical iostats last'))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6j1gtqn30y14t, child number 0
-------------------------------------
select /* tst101 */ count(*) from TAB1 where object_type = :"SYS_B_0"

Plan hash value: 1117438016

---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 40 (100)| | 1 |00:00:00.01 | 133 |
| 1 | SORT AGGREGATE | | 1 | 1 | 7 | | | 1 |00:00:00.01 | 133 |
|* 2 | TABLE ACCESS FULL| TAB1 | 1 | 499 | 3493 | 40 (0)| 00:00:01 | 1142 |00:00:00.01 | 133 |
---------------------------------------------------------------------------------------------------------------------

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

2 - filter("OBJECT_TYPE"=:SYS_B_0)

19 rows selected.

appuser@LAB11 > select NUM_ROWS from user_tab_statistics where table_name = 'TAB1';

NUM_ROWS
----------
10000

appuser@LAB11 > select DENSITY from user_tab_columns where TABLE_NAME = 'TAB1' and COLUMN_NAME = 'OBJECT_TYPE';

DENSITY
----------
.055555556

appuser@LAB11 > select NUM_NULLS from user_tab_columns where TABLE_NAME = 'TAB1' and COLUMN_NAME = 'OBJECT_TYPE';

NUM_NULLS
----------
1022

appuser@LAB11 > --> CARDINALITY = ( density * (num_rows - num_nulls) / num_rows ) * num_rows
appuser@LAB11 >
appuser@LAB11 > select (0.055555556*(10000-1022)/10000)*10000 CARDINALITY from dual;

CARDINALITY
-----------
498.777782

appuser@LAB11 >

Verificando o plano de execução da consulta, constatamos que o Otimizador estimou que seriam acessadas 499 linhas (E-ROWS) na operação “TABLE ACCESS FULL” da tabela TAB1, quando na verdade foram acessadas 1142 linhas (A-ROWS), esta estimativa não foi muito boa pois os valores repetidos dessa coluna não estão distribuídos de forma uniforme, neste tipo de situação para o Otimizador fazer um estimativa melhor ele precisa acessar estatísticas de histogramas.

A formula utilizada pelo Otimizador para calcular essa cardinalidade sem o auxilio de histogramas foi CARDINALITY = ( density * (num_rows – num_nulls) / num_rows ) * num_rows . Na log de execução acima foram incluídas algumas consultas para obter os valores de cada uma das variáveis da formula e o resultado final dessa equação foi 498.777782 que arredondando resulta nas 499 linhas estimadas no plano de execução.

Conclusão

Neste artigo verificamos como o Otimizador utiliza as estatísticas de tabela e colunas para estimar a cardinalidade de uma operação no plano de execução sem a utilização de estatísticas de histogramas, com a presença de histogramas o Otimizador utiliza outra formula que será tratada num futuro post. No nosso exemplo a estimativa realizada pelo Otimizador não foi muito boa pois para esse tipo de coluna o ideal seria que ele tivesse acesso as informações estatísticas de histograma da coluna indicada no filtro da cláusula WHERE (OBJECT_TYPE), com isso podemos constatar que ausência de informações estatísticas dos objetos envolvidos numa consulta podem prejudicar a qualidade das estimativas realizadas pelo Otimizador e lava-lo a montar planos de execução com desempenho ruim.