Browse By

Histórico de estatísticas Parte 2 !

 

Apenas para complementar o post anterior, existem outras táticas que podemos utilizar no nosso dia a dia para análise de histórico de estatísticas sobre as tabelas e indices.

Existe uma (algumas) table functions do pacote DBMS_STATS que nos ajudam nesse propósito. São elas:

DIFF_TABLE_STATS_IN_STATTAB, DIFF_TABLE_STATS_IN_HISTORY, DIFF_TABLE_STATS_IN_PENDING.

Eu prefiro utilizar a DIFF_TABLE_STATS_IN_HISTORY ja que não posso adivinhar qual será o dia que o plano de execução de alguma query mudará, não tendo tempo para fazer uma cópia das estatisticas para a STATTABLE antes da mudança que eu não sei quando será e etc.

Primeiramente, tenho que saber quanto tempo possuo do possível “histórico” da tabela a ser analisada e para isso recorro á função get_stats_history_availability do pacote DBMS_STATS.

SELECT sysdate ATUAL, dbms_stats.get_stats_history_availability Historico_First_Day
FROM DUAL;

ATUAL      HISTORICO_FIRST_DAY
--------- ---------------------------------------------------------------------------
10-SEP-12 09-AUG-12 09.25.05.338675000 PM -03:00

Podemos ver acima que possuo 30 dias de histórico disponível, na qual pode ser visto também pela função:

SELECT dbms_stats.get_stats_history_retention
    FROM DUAL;

GET_STATS_HISTORY_RETENTION
---------------------------
             31

Para alterar o tempo do histórico utilize o pacote:

exec dbms_stats.alter_stats_history_retention(retention IN NUMBER);

Voltando…

No exemplo, estou analisando uma tabela de uma query, na qual houve mudança do seu plano de execução no dia de hoje.

Utilizando DIFF_TABLE_STATS_IN_HISTORY com os seguintes argumentos:

FUNCTION DIFF_TABLE_STATS_IN_HISTORY RETURNS DBMS_STATS
 Argument Name            Type            In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNNAME            VARCHAR2        IN
 TABNAME            VARCHAR2        IN
 TIME1              TIMESTAMP WITH TIME ZONE IN
 TIME2              TIMESTAMP WITH TIME ZONE IN    DEFAULT
 PCTTHRESHOLD       NUMBER            IN     DEFAULT
Select *
From   table(dbms_stats.DIFF_TABLE_STATS_IN_HISTORY('TESTEOWN','TESTETABLE', SYSDATE-1, SYSDATE));

###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE          : TESTETABLE
OWNER          : TESTEOWN
SOURCE A      : Statistics as of 09-SEP-12 02.57.50.000000 PM -03:00
SOURCE B      : Statistics as of 10-SEP-12 02.57.50.000000 PM -03:00
PCTTHRESHOLD  : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NO DIFFERENCE IN TABLE / (SUB)PARTITION STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME      SRC NDV        DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................

TORC_CLIENTE_1    A   255      .003472222  YES  0        9     C23F4 C7602 5563
                  B   255      .003861003  YES  0        9     C23F4 C7602 5581
TORC_EMITENT_1    A   3        .333333333  NO   0        4     C20A0 C20A0 54008
                  B   3        .000009144  YES  0        4     C20A0 C20A0 5581
TORC_PROCDAT_1    A   547      .001828153  NO   0        8     646F0 78700 54008
                  B   545      .002873563  YES  0        8     646F0 78700 5650
TORC_SERNOTA_1    A   2        .5          NO   0        4     4F303 4F524 54008
                  B   2        .000009144  YES  0        4     4F303 4F524 5581
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME    TYP SRC ROWS    LEAFBLK DISTKEY LF/KY DB/KY CLF     LVL SAMPSIZ
...............................................................................

                INDEX: XIE3TESTE
                .................

    XIE3TESTE       A    54010    241    54010    1     1     33853   1    54010
                    B    53958    570    53958    1     1     33822   2    53958

###############################################################################

5467554.96   << % de diferença das estatísticas (comparação)

Acima, analisei dados somente de ontem para hj (SYSDATE-1, SYSDATE), mas pode ser configurável para a quantidade que quiser desde que esteja dentro do histórico.

Vemos que na sessão NO DIFFERENCE IN TABLE / (SUB)PARTITION STATISTICS  a nível GLOBAL (informações na DBA_TABLES/PART para ficar uma pouco mais claro) não houve diferença com base no valor do THRESHOLD, que significa mostrar informações que tivessem mais que 10% de modificação (O THRESHOLD pode ser configurável também utilizando o pacote DBMS_STATS).

Podemos notar que houve modificação maior que 10% entre as colunas aprensentadas na saida do comando, em comparação com o valor “anterior”  A e o valor “atual” B.

O comando também mostra informações a nível de índice (Se houve mais que 10% de moficação na análise de comparação.) na qual as informações estão bem claras.

Na tabela a maior modificação foi sobre os valores de DENSITY, SAMPSIZ = SAMPLE SIZE e HIST = Histogramas. Nesse caso podemos notar que a coleta de cerca de 10% de estimate não foi muito adequada para essa tabela, mudando informações importantes para o otimizador criar/manter o bom plano de execução.

No índice a informação LVL e LEAFBLK foram as que tiveram um mudança significativa (LeafBlocks e BLevel)  devido a uma carga na tabela.

Com essas informações, foi possível coletar estatísticas para essa tabela de uma maneira melhor retornando a boa performance para a query.

 

Forte abraço

 

Leave a Reply

Your email address will not be published. Required fields are marked *