Blog do Marcio Portes

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.

Labels: how to