Histograma Oracle

Histograma

Imagine o seguinte exemplo: Na cidade de São Paulo o conjunto de torcedores de times de futebol é distribuído segundo a tabela abaixo:

torce_para        count(*)
--------------  ----------
São Paulo        4.000.000
Corinthians      8.000.000
Palmeiras        6.000.000
Santos           2.000.000
VOCEM de Assis           8

Se existe um índice em torce_para e uma query pedir os torcedores do VOCEM de Assis, é muito provável que o Oracle fará full table scan.

Mas por que? 
É muito mais barato para o Oracle fazer um FTS que um acesso a milhões de linhas do índice.

Mas existe só 8 torcedores do VOCEM, como assim milhões de linhas?
Então sem histograma a coleta de estatística para o índice será mais ou menos (total de linhas tabela) / (seletividade do índice) então 20 milhões / 5 = 4 milhões de registros aproximadamente por valor distinto no índice (Cálculo muito a grosso modo).

Porém, sabemos que isso não corresponde a verdade, então damos uma “força” para o otimizador usando histograma. Nele será armazenado a informação necessária para o otimizador decidir quando é hora de FTS (São Paulo) e quando é hora de usar índice (VOCEM de Assis).

Veja um exemplo abaixo: A tabela t tem aproximada 7.4 milhões de linhas. A coluna x_hist tem 5 valores distintos como mostrado na query abaixo:

SQL> select nvl(to_char(x_hist),'Total') x_hist, count(*)
 2    from t
 3  group by rollup(x_hist)
SQL> /

X_HIST                                        COUNT(*)
---------------------------------------- -------------
0                                              1835294
1                                              1835293
2                                              1835293
3                                              1835294
5                                                   10
Total                                          7341184

6 rows selected.

A coluna x_hist é um índice. Como nossa aplicação usa corretamente o CBO, faremos 2 coletas de estatísticas, uma sem o uso de histograma (primeira) e outra com o uso de histograma, logo após cada coleta temos a query buscando os registros por x_hist.

SQL> begin
 2   dbms_stats.gather_table_stats(
 3       user,
 4       'T',
 5       estimate_percent => 10,
 6       cascade => true,
 7       degree => 4 );
 8  end;
 9  /

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace on
SQL>
SQL> select * from t
 2   where x_hist = 5
 3  /

   OBJECT_ID OBJECT_NAME                           X_HIST
------------- ------------------------------ -------------
          97 ACCESS$                                    5
        4349 AGGXMLIMP                                  5
        4356 AGGXMLINPUTTYPE                            5
        1812 ALL_ALL_TABLES                             5
        5653 ALL_APPLY                                  5
        5672 ALL_APPLY_CONFLICT_COLUMNS                 5
        5676 ALL_APPLY_DML_HANDLERS                     5
        5687 ALL_APPLY_ERROR                            5
        5665 ALL_APPLY_KEY_COLUMNS                      5
        5657 ALL_APPLY_PARAMETERS                       5

10 rows selected.


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2986 Card=1836663 Bytes=45916575)
  1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2986 Card=1836663 Bytes=45916575)




Statistics
----------------------------------------------------------
         0  recursive calls
         0  db block gets
     31246  consistent gets
     31045  physical reads
         0  redo size
       920  bytes sent via SQL*Net to client
       651  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
        10  rows processed

Como podemos ver, mesmo com índice o otimizador escolheu Full Table Scan e o custo de consistent gets está elevado para trazer apenas 10 linhas. Está errado? Não, do ponto de vista do otimizador não. Ele não conhece que para, justamente aquele x_hist, temos somente 8 linhas e com apenas 1 leitura física poderíamos responder ao predicado. Então, o próximo passo é ajudar ao otimizador conhecer o que ele tem. Vamos analizar a tabela agora passando um size de 10 para a coluna x_hist onde ele irá conhecer os valores adequados para a decisão.

SQL>
SQL> set autotrace off
SQL>
SQL> begin
 2   dbms_stats.gather_table_stats(
 3        user,
 4        'T',
 5        estimate_percent => 10,
 6        method_opt => 'for columns x_hist size 10',
 7        cascade => true,
 8        degree => 4 );
 9  end;
10  /

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace on
SQL>
SQL> select * from t
 2   where x_hist = 5
 3  /

   OBJECT_ID OBJECT_NAME                           X_HIST
------------- ------------------------------ -------------
          97 ACCESS$                                    5
        4349 AGGXMLIMP                                  5
        4356 AGGXMLINPUTTYPE                            5
        1812 ALL_ALL_TABLES                             5
        5653 ALL_APPLY                                  5
        5672 ALL_APPLY_CONFLICT_COLUMNS                 5
        5676 ALL_APPLY_DML_HANDLERS                     5
        5687 ALL_APPLY_ERROR                            5
        5665 ALL_APPLY_KEY_COLUMNS                      5
        5657 ALL_APPLY_PARAMETERS                       5

10 rows selected.


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=25)
  1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=4 Card=1 Bytes=25)
  2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=3 Card=1)




Statistics
----------------------------------------------------------
         0  recursive calls
         0  db block gets
         6  consistent gets
         1  physical reads
         0  redo size
       920  bytes sent via SQL*Net to client
       651  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
        10  rows processed

Agora sim, o otimizador (já com as informações necessárias) fez o acesso ao índice, 1 chamada física 6 consistent gets e respondeu a nossa query de forma eficiente.

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em HISTOGRAMA, I/O TUNING, PLSQL TUNING e marcado , . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s