Statistics no Oracle

Publicado na SQL Magazine, edição 74: Abril/2010

By José Eduardo Fiamengui Júnior
jeduardounicamp@gmail.com

Temas abordados: CBO, Statistics e Histograms

O otimizador é o programa que realiza a transformação de comandos SQL em planos de execução. A partir do Oracle7, introduziu-se o Cost Based Optimizer (CBO) que, como o nome sugere, baseia-se no custo das operações. A partir do Oracle10g, esse otimizador se tornou default, sendo que seu antecessor, baseado em regras é bem mais simples de ser compreendido já que funciona a partir de 15 regras pré-definidas não levando em consideração a distribuição dos dados, sendo influenciado pela sintaxe do comando e existe apenas por compatibilidade. De acordo com [1], [2] e [3], o CBO gera suas estimativas para acesso aos dados baseado nas métricas de custo, cardinalidade e seletividade, sendo influenciado por parâmetros e statistics. Daí a importância de entendermos como as statistics funcionam e auxiliam o CBO na geração do plano de execução.

O CBO é dividido em 3 componentes principais, conforme mostrado na figura 1:

Figura 1: Componentes do CBO

O Query Transformer recebe um comando proveniente do analisador gramatical e o divide em diferentes blocos relacionados entre si. Seu objetivo é verificar se existe alguma forma mais otimizada para o comando em que se obtenha o mesmo resultado, gerando assim o melhor plano de execução, para isto ele utiliza algumas técnicas como: view merging, predicate pushing, subquery unnesting e query rewrite on materialized views.

O Estimator é responsável por indicar o esforço estimado para cada plano apresentado pelo QueryTransformer. Para gerar o custo, o estimator baseia-se em duas métricas seletividade e cardinalidade que serão explicados no decorrer deste artigo.

O Plan Generator Testa diferentes planos de execução para uma consulta e escolhe aquele que apresenta o menor custo. Estes planos são gerados com variações de formas de acesso, formas de junção e ordens de junção. Para gerar o plano de execução de uma consulta, são gerados os sub-planos para cada bloco de visões não agrupadas e sub-consultas. Estes blocos são otimizados separadamente de baixo para cima, ou seja, a sub-consulta mais interna é otimizada em primeiro lugar, e um sub-plano é gerado para ela. Já a consulta mais externa é a última a ser otimizada.

Definindo rapidamente os conceitos que influenciam o comportamento do otimizador:

i) Custo: número gerado pelo otimizador para determinar o melhor método de acesso (estimativa).

ii) Seletividade: porcentagem de linhas retornadas conforme a aplicação de uma condição (filtro). A fórmula utilizada para cálculo depende da distribuição dos dados e da quantidade de buckets, para as situações que analisaremos neste artigo utilizaremos as duas equações abaixo:

Equação 1. Seletividade para valores não-populares

12Sel Esperada=1NP Values*#NP buckets#buckets’>

12Sel Esperada=#PV buckets#buckets’>

Equação 2. Para valores populares

Onde:

NP values: valores não-populares

NP buckets: buckets não-populares

PV buckets: buckets de valores populares

buckets: buckets

As variáveis acima serão melhor explicadas no decorrer do artigo.

iii) Cardinalidade: representa o número de registros retornados em uma determinada operação do plano de execução.

Equação 3. Cardinalidade

12Card Esperada=Linhas Processadas(Prod Cartesiano)*Sel Esperada’>

As statistics quantificam a distribuição dos dados e as características de armazenamento de tabelas, colunas, índices, blocos e partições. O CBO utiliza as statistics para calcular a cardinalidade e seletividade das colunas e, assim, gerar o método de acesso com menor custo. Na coleta de statistics, são armazenadas as seguintes informações:

i)                   Table statistics:

– Número de linhas;

– Número de blocos;

– Número de blocos vazios;

– Número de chained ou migrated rows;

– Data da coleta;

– Tamanho da amostra; e

– Tamanho médio da linha;

ii)                 Column statistics:

– Número de valores distintos na coluna (NDV);

– Quantidade de valores nulos; – Menor e maior valor;

– Data da coleta; e

– Tamanho da amostra;

iii)              Index statistics:

– Altura, número de blocos folhas;

– Número de chaves distintas;

– Número médio de blocos folhas por chave;

– Número de entradas, fator de clusterização;

– Data da coleta; e

– Tamanho da amostra;

iv) System statistics:

– I/O performance e utilização;

– CPU performance e utilização.

Visualizando as informações, torna-se fácil entender porque a coleta deve ser realizada periodicamente. Podemos verificar se as statistics para os objetos estão coletadas e atualizadas através das views: DBA_TABLES, DBA_TAB_COL_STATISTICS, DBA_INDEXES, DBA_CLUSTERS, DBA_TAB_PARTITIONS, DBA_TAB_SUBPARTITIONS, DBA_IND_PARTITIONS, DBA_IND_SUBPARTITIONS, DBA_PART_COL_STATISTICS, DBA_SUBPART_COL_STATISTICS.

No Oracle9i, é necessário criar um método para a coleta periódica de statistics e agendá-la para execução. A partir da versão 10g, isso é controlado pelo Oracle que, na sua instalação, cria o job GATHER_STATS_JOB e sua execução é realizada nos dias úteis entre as 22h e 06h e em todos os finais de semana. O parâmetro statistics_level controla o nível de coleta que é executado na base de dados, podendo apresentar os seguintes valores:

i)                   BASIC: statistics não são coletadas;

ii)                 TYPICAL (default e recomendada): coleta statistics de objetos e das views advisors buffer cachê, shared pool, PGA target, MTTR;

iii)              ALL: typical + statistics de SO

A atualização das statistics pode ser realizada através da package dbms_stats.    As principais funcionalidades dessa package podem ser vistas na Tabela 1:

Procedure Collects
GATHER_INDEX_STATS Statistics dos índices
GATHER_TABLE_STATS Tabelas, colunas e Statistics dos índices
GATHER_SCHEMA_STATS Statistics de todos os objetos do schema
GATHER_DATABASE_STATS Statistics para todos os objetos do banco de dados
GATHER_SYSTEM_STATS Statistics de CPU e I/O do System
EXPORT_SCHEMA_STATS Realiza export das statistics de um schema
IMPORT_SCHEMA_STATS Realiza import das statistics de um schema
LOCK_TABLE_STATS Bloqueia a coleta de statistics em uma tabela
UNLOCK_TABLE_STATS Desbloqueia a coleta de statistics em uma tabela
RESTORE_TABLE_STATS Restaura statistics de uma tabela

Tabela 1. package dbms_stats

Note que algumas destas funcionalidades podem ser utilizadas para simular ambientes, por exemplo em empresas onde o ambiente de homologação não contêm disco(espaço) suficiente para todos os dados que há em produção pode-se exportar as statistics de produção na base de homologação, desta forma consegue-se visualizar os planos de execução analisando cada método de acesso e testando a eficiência do comando SQL.

A partir do Oracle9i foi disponibilizado também o parâmetro optimizer_dynamic_sampling que controla o nível de coleta statistics em tempo real. Ou seja, coleta de informações de tabelas sem estatísticas em tempo de execução. Os valores default deste parâmetro variam de acordo com a versão, conforme mostrado na Tabela 2:

Versão Default
Oracle >= 9.0.0 0
Oracle >=9.2.0 1
Oracle >= 10.0.0 2

Tabela 2. Default optimizer_dynamic_sampling

A grande vantagem da utilização deste parâmetro é quando temos na base de dados volatile tables que são definidas como tabelas cujo conteúdo pode variar de vazio a muito grande em tempo de execução. Esta volatibilidade extrema pode resultar em statistics imprecisas e conseqüentemente em planos incorretos e com mal desempenho. Por exemplo, se as statistics são recolhidas quando a tabela está vazia o otimizador tende a favorecer este acesso como forma de restringir os registros. Para estes casos é recomendável que as statistics da tabela fiquem bloqueadas, pois assim o optimizer_dynamic_sampling será utilizado.

Agora vejamos na Tabela 3 aos níveis de coleta realizados possíveis a se definir no optimizer_dynamic_sampling:

Nível Descrição
0 Feature desabilitada
1 Coleta de tabelas sem statistics e índices
2 Coleta de todas tabelas sem estatísticas (default sample size = 32KB)
3 Idem nível 2 + tabelas que otimizador utiliza  estimativas em predicados
4 Idem nível 3 + tabelas com predicados em mais de 1 coluna
5 Idem nível 4, mas com 2 * default sample size
6 Idem nível 4, mas com 4 * default sample size
7 Idem nível 4, mas com 8 * default sample size
8 Idem nível 4, mas com 32 * default sample size
9 Idem nível 4, mas com 128 * default sample size
10 Idem nível 4, mas com todos os blocos

Tabela 3. Optimizer_dynamic_sampling níveis de coleta

Na Listagem 1 foram criadas as tabelas prob_tab e build_tab cópias da all_objects e realizamos uma consulta relacionando essas tabelas mas sem a coleta de statistics, vamos analisar qual será o plano de execução gerado pelo otimizador.

Listagem 1: Falta de Statistics

1    create table probe_tab as
2    select  10000 + rownum id,trunc(dbms_random.value(0,5000)) n1,
3    rpad(rownum,20) probe_vc, rpad(‘x’,500) probe_padding
4    from all_objects
5    where rownum <= 5000 ;

6    alter table probe_tab add constraint pb_pk primary key(id);

7    create table build_tab as Select  rownum id,
8    10001 + trunc(dbms_random.value(0,5000)) id_probe,
9    rpad(rownum,20) build_vc,
10   rpad(‘x’,500) build_padding
11   from all_objects
12   where rownum <= 5000;

13   alter table build_tab add constraint bu_pk primary key(id);
14   alter table build_tab add constraint bu_fk_pb foreign key (id_probe)
15   references probe_tab;

16   — Após a criação das tabelas, vamos verificar o PE para a query abaixo
17   — (utilizando a ferramenta dbms_xplan):

18   EXPLAIN PLAN FOR select   bu.build_vc,
19                   pb.probe_vc,
20                   pb.probe_padding
21                 from     build_tab bu,
22                      probe_tab pb
23                 where bu.id between 1 and 500
24                   and pb.id = bu.id_probe ;

25   select * from table(dbms_xplan.display) ;

25    ———————————————————–
26    | Id  | Operation                     | Name      | Rows
37    ———————————————————–
27    |   0 | SELECT STATEMENT              |           |    82
28    |   1 |  NESTED LOOPS                 |           |
29    |   2 |   NESTED LOOPS                |           |    82
30    |   3 |    TABLE ACCESS BY INDEX ROWID| BUILD_TAB |    82
31    |*  4 |     INDEX RANGE SCAN          | BU_PK     |   147
32    |*  5 |    INDEX UNIQUE SCAN          | PB_PK     |     1
34    |   6 |   TABLE ACCESS BY INDEX ROWID | PROBE_TAB |     1
35    ————————————————————

36    Predicate Information (identified by operation id):
37    —————————————————
38       4 – access(“BU”.”ID”>=1 AND “BU”.”ID”<=500)
39       5 – access(“PB”.”ID”=”BU”.”ID_PROBE”)

40   — Vamos analisar o desempenho desta consulta:

41   set timing on
42   select bu.build_vc, pb.probe_vc, pb.probe_padding
43            from build_tab bu, probe_tab pb
44           where bu.id between 1 and 500
45              and pb.id = bu.id_probe ;

46   500 linhas selecionadas.
47   Decorrido: 00:00:00.08

48   — Coletando statistics para as tabelas envolvidas:
49   EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘PROBE_TAB’);
50   Procedimento PL/SQL concluÝdo com sucesso.
51   EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘BUILD_TAB’);
52   Procedimento PL/SQL concluÝdo com sucesso.

53   EXPLAIN PLAN FOR select   bu.build_vc,
54      pb.probe_vc,
55      pb.probe_padding
56    from     build_tab bu,
57      probe_tab pb
58     where bu.id between 1 and 500
59      and pb.id = bu.id_probe ;

60   select * from table(dbms_xplan.display) ;

61    ——————————————————–
62    | Id  | Operation                    | Name      | Rows
63    ———————————————————-
64    |   0 | SELECT STATEMENT             |           |   500
65    |*  1 |  HASH JOIN                   |           |   500
66    |   2 |   TABLE ACCESS BY INDEX ROWID| BUILD_TAB |   500
67    |*  3 |    INDEX RANGE SCAN          | BU_PK     |   500
68    |   4 |   TABLE ACCESS FULL          | PROBE_TAB |  5000
69    ———————————————————-

70    Predicate Information (identified by operation id):
71    —————————————————
72       1 – access(“PB”.”ID”=”BU”.”ID_PROBE”)
73       3 – access(“BU”.”ID”>=1 AND “BU”.”ID”<=500)

74   set timing on
75   select bu.build_vc, pb.probe_vc, pb.probe_padding
76        from build_tab bu, probe_tab pb
77       where bu.id between 1 and 500
78         and pb.id = bu.id_probe ;

79   Decorrido: 00:00:00.04

Nota-se a grande diferença nos PEs gerados, já que no primeiro deles o otimizador une as tabelas através do NESTED LOOPS. Neste método o CBO elege uma fonte principal (outer table), que servirá de base para buscar os registros na fonte dependente (inner table). Para cada registro existente na fonte principal, o Oracle buscará os registros na fonte dependente que atendam às condições da fonte principal o retorno dos dados é realizado a cada colisão, mas sim no final do passo.

Após a coleta de statistics o CBO passa a utilizar o método HASH JOIN neste método uma hash table é construída para a menor das duas tabelas. A menor tabela é então colocada em memória e é usada para encontrar as linhas que combinam com a tabela maior. O retorno dos dados neste método não ocorre a cada colisão.

Os histograms ajudam o CBO a visualizar a distribuição dos dados nas colunas, quando falamos em histograms devemos pensar em:

i)                   Valores Não-Populares (PN): aparecem no máximo uma vez como END_POINT_VALUE;

ii)                 Valores Populares (PV): aparecem mais de uma vez como END_POINT_VALUE; e

iii)              Buckets: buckets onde a distribuição dos valores são armazenados.

Vamos a um exemplo didático para entender como os histograms auxiliam o CBO. Imagine uma coluna que possua 100 valores uniformemente distribuídos de 1 a 100, conforme mostrado na Figura 2:

Figura 2. distribuição uniforme

Segundo a Equação 1, temos (supondo que a coluna não tem histograms):

Sel Esperada =  1/100 * 1/1  =  0,01

Card Esperada = 100 * 0,01 = 1

Com essa distribuição o otimizador estimaria a cardinalidade corretamente e isso faria com que o acesso aos dados fosse o menos custoso.

Agora vejamos como seria a distribuição não-uniforme desses dados, na Figura 3:

Figura 3. distribuição não-uniforme

Neste caso, a maioria das linhas tem o valor 5 e apenas 10% das linhas apresentam valores entre 60 e 100. A cardinalidade seria calculada conforme demonstrado no quadro 1 e seria erroneamente estimada pelo otimizador.

Vamos imaginar a distribuição destes valores em 10 buckets e realizar os cálculos do otimizador.

Segundo a Equação 2, temos:

Sel Esperada: 4/10 = 0,4

Card Esperada = 100 * 0,4 = 40

Nota-se que desta forma a cardinalidade estimada pelo otimizador seria muito mais próxima a realidade permitindo-lhe o acesso aos dados da forma mais eficiente.

Na Listagem 2 temos um caso real de como os histograms auxiliam o otimizador:

1     select coluna, count(*) from t1
2        group by coluna
3        order by count(*) ;
4     COLUNA      COUNT(*)
5     ——-   ———-
6       1                3
7       2               39
8       3               58
9       4              400
10      5          1883969   (30%)
11      6          4314398   (69%)

12    — verificando a presença de statistics(sem histograms)
13    TABLE_NAME     NUM_ROWS LAST_ANALYZED
14    ———— ———- ————-
15    T1              6198867   17-jul-2009

16    — comando SQL

17    explain plan for select * from t1
18        where coluna in (’1’,’2’)
19        order by 1 ;
20    ————————————————
21    | Id  | Operation            |  Name   | Rows  |
23    ————————————————
24    |   0 | SELECT STATEMENT     |         |  2704K|
25    |   1 |  SORT ORDER BY       |         |  2704K|
26    |*  2 |   TABLE ACCESS FULL  | T1      |  2704K|
27    ————————————————

28    Predicate Information (identified by operation id):
29    —————————————————
30    2 – filter(“T1”.”COLUNA”=’1′ OR “T1”.”COLUNA”=’2′)

31    — criando histograms
32    EXECUTE DBMS_STATS.GATHER_TABLE_STATS (ownname => ‘SCOTT’,
33    tabname => ‘T1’,method_opt => ‘FOR COLUMNS SIZE 100 COLUNA’,
34    degree => 2, cascade => TRUE) ;

35    — explain do commando
36    explain plan for select * from t1
37        where coluna in (’1’,’2’)
38        order by 1 ;
38    ————————————————————
39    | Id  | Operation                     |  Name     | Rows  |
40    ————————————————————
41    |   0 | SELECT STATEMENT              |           |      1|
42    |   1 |  SORT ORDER BY                |           |      1|
43    |   2 |  INLIST ITERATOR              |           |       |
44    |   3 |   TABLE ACCESS BY INDEX ROWID |  T1       |      1|
45    |*  4 |    INDEX RANGE SCAN           |  IDX001   |      1|
46    ————————————————————
47    Predicate Information (identified by operation id):
48    —————————————————
49    – filter(“T1”.”COLUNA”=’1′ OR “T1”.”COLUNA”=’2′)

Listagem 2. Histograms em ação

Os principais parâmetros que deverão ser utilizados para a coleta de statistics são:

i)      estimate_percent: estima a statistic por amostragem, este valor é a porcentagem de linhas que o Oracle utilizará como amostra. Caso você coloque o valor NULL é computada as statistics de todo o objeto.

ii)    method_opt: controla para quais colunas deverão ser construído os histogramas e quantos buckets eles devem ter. A opção AUTO permite que o otimizador decida com base na carga de trabalho e distribuição de dados da coluna, a opção REPEAT recria o histograma com o mesmo número de buckets que havia anteriormente, e a opção SKEWONLY cria novo histograma conforme a distribuição dos dados. O número default de buckets é 75 e o máximo de buckets que pode ser criado é 255.

iii) cascade: controla se objetos dependentes como índices, serão analisados.

A presença de histograms pode ser verificada através da views DBA_TAB_HISTOGRAMS, DBA_HISTOGRAMS e DBA_TAB_COL_STATISTICS.

Ao mostrar estes exemplos pode ser que seja vista com bom olhos a criação de histograms para quaisquer colunas da base de dados, no entanto há um problema relacionado a histograms e a utilização de binds conhecido como bind variable peeking. Para entendermos esta feature é necessário lembramos as etapas de processamento do comando SQL, conforme mostrado na Figura 4:

Figura 4. Fases de processamento do SQL

Os comandos SQL são processados na Shared Pool que fica dentro da SGA(System Global Área), e é por sua vez é subdivida em:

i)                   Library Cache: armazena informações sobre comandos SQL e PL/SQL e são gerenciados pelo algoritmo LRU(Least Recently Used) que consiste em descartar o comando que está há mais tempo sem uso na memória e colocar o comando requisitado nessa posição; e

ii)                 Data Dictionary Cache: armazena metadados do dicionário, como informações sobre datafiles, tabelas, índices, colunas, usuários, privilégios e outros objetos.

Explicando rapidamente cada etapa de processamento:

i)                   OPEN: é aberto um cursor para processamento do comando;

ii)                 PARSE: análise sintática do comando (se o comando está escrito corretamente), análise semântica do comando(se os objetos existem e privilégios), gera o identificador do comando(hash_value), verifica se o ambiente em que o comando foi executado é o mesmo(alterações de parâmetros), transforma os comandos(para que haja mais possibilidades para geração do plano de execução) e gera os possíveis planos de execução;

iii)              BIND: atribui valores aos binds da instrução;

iv)               EXECUTE: nesta fase o comando é executado;

v) FETCH: retorno dos dados buscado pelos comandos

vi)               CLOSE: fechamento do cursor.

Após entendermos as etapas do processamento de um SQL é necessário entendermos também os conceitos de Hard Parse e Soft Parse:

i)                   Hard Parse: é o processamento do comando SQL passando por todas as etapas demonstradas na Figura 4.

ii)                 Soft Parse: é o processamento do comando SQL até o cálculo do hash value e verificação do ambiente, após isso o plano de execução é reaproveitado.

Explicados estes conceitos essenciais, podemos voltar a falar da feature bind variable peeking., Conforme demonstrado na Figura 4 normalmente os binds são atribuídos ao comando SQL após a geração do plano de execução (após a fase de PARSE). Com esta feature, para que não seja ignorada a presença de histograms nas colunas apenas no Hard Parse do comando SQL, o otimizador realiza a fase de bind anteriormente a fase de PARSE.

Pensando no exemplo da Listagem 2, quando isso nos traria problemas?

Imaginemos que no Hard Parse do nosso comando SQL os valores buscados sejam os menos populares, como 1 e 2, se na segunda execução(Soft Parse) o valor buscado seja for 6 (super popular, com 69% das linhas ), o PE gerado não será o mais eficiente já que para a busca deste valor requisições multiblocks (Full Table Scan e/ou Index Fast Full Scan) retornaria os dados mais rapidamente.

No Oracle 11g há uma nova feature denominada Adaptative Cursor que analisa colunas com a presença de histograms e caso haja alteração no valor selecionado, por exemplo, de Popular Value para Non-Popular Value o plano de execução é gerado novamente.

Agora que Conhecemos todas as possibilidades quando devemos criar histograms, especialmente em duas situações quando a coluna for utilizada na claúsula WHERE dos comandos e apresentarem distribuição não-uniforme.

Conclusão

Concluimos que a coleta periódica de statistics e o estudo de colunas não-uniformes que são utilizadas pelos filtros das operações são de grande importância para que o CBO consiga estimar corretamente a cardinalidade e seletividade para cada método de acesso e, dessa forma, chegar ao plano de execução com menor custo real.

Referências Bibliográficas
[1]  LEWIS, Jonathan. Cost Based Oracle 2005
[2] Oracle Database 10g Release 1 (10.1)
[3] Oracle 9i Database Performance Guide and Reference Release 2 (9.2)
[4] http://www.oracle-base.com/articles/10g/PerformanceTuningEnhancements10g.php, acessado em 15 de julho de 2009.
[5] http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#i42219, acessado em 15 de julho de 2009.

Sobre oradeep

José Eduardo Fiamengui Júnior Graduação: Tecnologia em Informática pela Universidade Estadual de Campinas (Unicamp) Pós-Graduação: Administração em Banco de Dados Oracle pelo Instituto Brasileiro de Tecnologia Avançada (IBTA) Mba em Gestão Estratégica em TI pela FGV OCE Certified ITIL Certified Empresa Atual: Dba Oracle e Performance Specialist na Ccee Empresa Atual: Instrutor Oracle IBTA
Esta entrada foi publicada em Oracle. ligação permanente.

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s