Browse By

Histórico de estatísticas Tabelas/Indices!

As vezes encontro momentos (clientes ou conversas entre amigos) que é citado o tal do problema “do Oracle se perder“..rs.

Estava tudo bem até ontem, mas hoje do nada relatório X ou todo o sistema ficou lento, acho que o Oracle esta se perdendo.”

Se ele esta “se perdendo” então temo que acha-lo, não é ? rs. Brincadeiras a parte, sabemos que o Oracle é um “software”, possui sua inteligência “artificial” mais nada que o faça tomar vida e agir sozinho sem base de alguma informação.

Descreverei abaixo, um pequeno exemplo de uma das análises que faço sobre um ambiente que esta com a situação “parecida”, mudança de performance de alguma query para ser mais exato. Após coletar algumas informações do banco de dados, ja consigo imaginar os possíveis problemas que podem ter ocorrido para que aquela query mudasse, deixando-a lenta e dentre as possibilidades, a maioria esta relacionada a coleta de estatísticas das tabelas/índices.

Abaixo descrevo um processo simples e antigo pois esta presente desde a versão 10g, para que você identifique no cliente o histórico de coleta de estatísticas de tabela X ou tabelas envolvidas na query. (Nas versões anteriores a 10g, o dba por questão de boas práticas deveria criar algum job que coletasse as informações referentes as estatisticas que estão atualmente nas tabelas e indices antes que as novas fossem coletadas onde na versão 10g, esse processo já esta presente automaticamente.) Com isso você pode fazer uma analise realmente rápida dependendo do caso e outras nem tanto assim, mas por experiência própria, já resolvi alguns problemas na qual eu não teria tempo de fazer uma análise mais detalhada, utilizando um celular por exemplo entre outras ocasiões.

Antes de mais nada, um pequeno overview sobre as tabelas de “log”. Na versão 10g e acima foram criadas as tabelas wri$_optstat_% que armazenam o histórico de muita coisa relacionada aos dados que o otimizador utiliza, vamos vê-las:

 SINCPROD> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SINCPROD> select owner,table_name from dba_tables where lower(table_name)  like 'wri$_optstat_%';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SYS                            WRI$_OPTSTAT_TAB_HISTORY
SYS                            WRI$_OPTSTAT_IND_HISTORY
SYS                            WRI$_OPTSTAT_HISTHEAD_HISTORY
SYS                            WRI$_OPTSTAT_HISTGRM_HISTORY
SYS                            WRI$_OPTSTAT_AUX_HISTORY
SYS                            WRI$_OPTSTAT_OPR
SYS                            WRI$_OPTSTAT_SYNOPSIS_PARTGRP
SYS                            WRI$_OPTSTAT_SYNOPSIS_HEAD$
SYS                            WRI$_OPTSTAT_SYNOPSIS$

E aí estão elas, recomendo que pesquisem o objetivo de cada uma delas e as informações que as mesmas guardam, no post de hoje vou utilizar as WRI$_OPTSTAT_TAB_HISTORY, WRI$_OPTSTAT_HISTHEAD_HISTORY e WRI$_OPTSTAT_IND_HISTORY que contém histórico das estatísticas que as tabelas e indices receberam durante cada coleta.

As informações são retidas nessas tabelas por um tempo default de 31 dias sobre o parametro stats_retention da package dbms_stats e que podem ser modificadas diminuindo ou aumentando sua rentenção com a seguinte procedure: dbms_stats.alter_stats_history_retention.

Informacões sobre retenção e purge, podem ser encontradas aqui e aqui.

Voltando..Fiz algumas modificações a adaptações em scripts que o MOS usa para suas análises.

Primeiro: Criar view no usuário SYS — (Ou conceder privilégios de select nas tabelas relacionadas, aqui é um ambiente de teste.)

CREATE OR REPLACE FORCE VIEW sys.wmon_tab_stats_hist ("OWNER", "TABLE_NAME", "PARTITION_NAME", "SUBPARTITION_NAME", "OBJECT_ID", "OBJECT_TYPE", "NUM_ROWS", "BLOCKS", "AVG_ROW_LEN", "SAMPLE_SIZE", "LAST_ANALYZED", "SAVE_TIME", "VERSION_TYPE", "OBJ#", "SAVTIME", "FLAGS", "ROWCNT", "BLKCNT", "AVGRLN", "SAMPLESIZE", "ANALYZETIME", "CACHEDBLK", "CACHEHIT", "LOGICALREAD")
AS
  SELECT u.name owner,
    o.name table_name,
    NULL partition_name,
    NULL subpartition_name,
    h.obj# object_id,
    'TABLE' object_type,
    h.rowcnt num_rows,
    h.blkcnt blocks,
    h.avgrln avg_row_len,
    h.samplesize sample_size,
    h.analyzetime last_analyzed,
    CASE
      WHEN h.savtime < SYSTIMESTAMP
      THEN h.savtime
    END save_time,
    CASE
      WHEN h.savtime > SYSTIMESTAMP
      THEN 'PENDING'
      ELSE 'HISTORY'
    END version_type,
    h."OBJ#",
    h."SAVTIME",
    h."FLAGS",
    h."ROWCNT",
    h."BLKCNT",
    h."AVGRLN",
    h."SAMPLESIZE",
    h."ANALYZETIME",
    h."CACHEDBLK",
    h."CACHEHIT",
    h."LOGICALREAD"
  FROM sys.wri$_optstat_tab_history h,
    sys.obj$ o,
    sys.user$ u
  WHERE h.obj# = o.obj#
  AND o.type#  = 2
  AND o.owner# = u.user#
  UNION ALL
  SELECT u.name owner,
    o.name table_name,
    o.subname partition_name,
    NULL subpartition_name,
    h.obj# object_id,
    'PARTITION' object_type,
    h.rowcnt num_rows,
    h.blkcnt blocks,
    h.avgrln avg_row_len,
    h.samplesize sample_size,
    h.analyzetime last_analyzed,
    CASE
      WHEN h.savtime < SYSTIMESTAMP
      THEN h.savtime
    END save_time,
    CASE
      WHEN h.savtime > SYSTIMESTAMP
      THEN 'PENDING'
      ELSE 'HISTORY'
    END version_type,
    h."OBJ#",
    h."SAVTIME",
    h."FLAGS",
    h."ROWCNT",
    h."BLKCNT",
    h."AVGRLN",
    h."SAMPLESIZE",
    h."ANALYZETIME",
    h."CACHEDBLK",
    h."CACHEHIT",
    h."LOGICALREAD"
  FROM sys.wri$_optstat_tab_history h,
    sys.obj$ o,
    sys.user$ u
  WHERE h.obj# = o.obj#
  AND o.type#  = 19
  AND o.owner# = u.user#
  UNION ALL
  SELECT u.name owner,
    osp.name table_name,
    ocp.subname partition_name,
    osp.subname subpartition_name,
    h.obj# object_id,
    'SUBPARTITION' object_type,
    h.rowcnt num_rows,
    h.blkcnt blocks,
    h.avgrln avg_row_len,
    h.samplesize sample_size,
    h.analyzetime last_analyzed,
    CASE
      WHEN h.savtime < SYSTIMESTAMP
      THEN h.savtime
    END save_time,
    CASE
      WHEN h.savtime > SYSTIMESTAMP
      THEN 'PENDING'
      ELSE 'HISTORY'
    END version_type,
    h."OBJ#",
    h."SAVTIME",
    h."FLAGS",
    h."ROWCNT",
    h."BLKCNT",
    h."AVGRLN",
    h."SAMPLESIZE",
    h."ANALYZETIME",
    h."CACHEDBLK",
    h."CACHEHIT",
    h."LOGICALREAD"
  FROM sys.wri$_optstat_tab_history h,
    sys.obj$ osp,
    sys.tabsubpart$ tsp,
    sys.obj$ ocp,
    sys.user$ u
  WHERE h.obj#   = osp.obj#
  AND osp.type#  = 34
  AND osp.obj#   = tsp.obj#
  AND tsp.pobj#  = ocp.obj#
  AND osp.owner# = u.user#;

Agora vamos utilizar essa view (para obter o histórico) com um join com a view dba_tab_statistics (para obter as informações atuais).

col owner for a10
col TABLE_NAME for a18
col PARTITION_NAME for a10
col SUBPARTITION_NAME for a10
col SAVE_TIME for a40
set lines 200

SELECT  owner,
    table_name,
    partition_name,
    subpartition_name,
    object_type,
    num_rows,
    blocks,
    avg_row_len,
    sample_size,
    last_analyzed,
    CASE
      WHEN object_type = 'TABLE'
      THEN DECODE(BITAND(flags, 512), 0, 'NO', 'YES')
      WHEN object_type IN ('PARTITION', 'SUBPARTITION')
      THEN DECODE(BITAND(flags, 16), 0, 'NO', 'YES')
    END global_stats,
    CASE
      WHEN object_type = 'TABLE'
      THEN DECODE(BITAND(flags,256), 0, 'NO', 'YES')
      WHEN object_type IN ('PARTITION', 'SUBPARTITION')
      THEN DECODE(BITAND(flags,8 ), 0, 'NO', 'YES')
    END user_stats,
    save_time,
    version_type
from sys.wmon_tab_stats_hist
where owner = '&OWNER'
and table_name = '&TABLENAME'
union all
SELECT  owner,
    table_name,
    partition_name,
    subpartition_name,
    object_type,
    num_rows,
    blocks,
    avg_row_len,
    sample_size,
    last_analyzed,
    global_stats,
    user_stats,
    TO_TIMESTAMP_TZ(NULL) save_time,
    'CURRENT' version_type
  FROM sys.dba_tab_statistics
where owner = '&OWNER'
and table_name = '&TABLENAME'
ORDER BY owner, table_name, save_time DESC;

O Resultado é o seguinte:

OWNER      TABLE_NAME         PARTITION_ SUBPARTITI OBJECT_TYPE    NUM_ROWS     BLOCKS AVG_ROW_LEN SAMPLE_SIZE LAST_ANAL GLO USE SAVE_TIME                                VERSION
---------- ------------------ ---------- ---------- ------------ ---------- ---------- ----------- ----------- --------- --- --- ---------------------------------------- -------
TESTE         NOTC                                   TABLE             52356       7944         895        5288 27-AUG-12 YES NO                                           CURRENT
TESTE         NOTC                                   TABLE             52356       7944         895       52356 26-AUG-12 NO  NO  27-AUG-12 04.06.39.511803000 AM -03:00   HISTORY
TESTE         NOTC                                   TABLE             52356       7944         895       52356 26-AUG-12 NO  NO  26-AUG-12 11.19.53.431660000 PM -03:00   HISTORY
TESTE         NOTC                                   TABLE             52356       7944         895       52356 25-AUG-12 NO  NO  26-AUG-12 04.07.23.646924000 AM -03:00   HISTORY
TESTE         NOTC                                   TABLE             52328       7944         895       52328 25-AUG-12 NO  NO  25-AUG-12 11.18.28.905450000 PM -03:00   HISTORY
TESTE         NOTC                                   TABLE             52328       7944         895       52328 24-AUG-12 NO  NO  25-AUG-12 04.06.37.879536000 AM -03:00   HISTORY
...

Acima podemos ver que a primeira linha é referente aos dados atuais que estão sobre a tabela em relação ao otimizador e as linhas subsequentes são relacionadas ao histórico. Logo podemos ver que o job automático de estatísticas que executa toda a noite, coletou somente 10% das informações sobre a tabela onde nos outros dias na qual a performance estava OK, foi coletado 100%. (Existem motivos que fazem com que o Oracle realize essa modificação na coleta, como eu disse no início do post, o Oracle não faz nada sozinho. Mas isso foge do escopo do post de hoje, pois a idéia aqui é análise rápida.)

Podemos descer o nivel de análise de colunas também. Para isso crie a view abaixo para ficar mais fácil a legibilidade do código.

CREATE OR REPLACE FORCE VIEW SYS.wmon_tab_col_stat_hist ("OWNER", "TABLE_NAME", "PARTITION_NAME", "SUBPARTITION_NAME", "OBJECT_ID", "OBJECT_TYPE", "COLUMN_NAME", "COLUMN_ID", "NUM_DISTINCT", "LOW_VALUE", "HIGH_VALUE", "NUM_NULLS", "AVG_COL_LEN", "LAST_ANALYZED", "SAVE_TIME", "VERSION_TYPE", "OBJ#", "INTCOL#", "SAVTIME", "FLAGS", "NULL_CNT", "low_value_cooked", "high_value_cooked", "endpoints_count", "DENSITY", "LOWVAL", "HIVAL", "AVGCLN", "SAMPLE_DISTCNT", "SAMPLE_SIZE", "TIMESTAMP#", "EXPRESSION", "COLNAME")
AS
  SELECT u.name owner,
    o.name table_name,
    NULL partition_name,
    NULL subpartition_name,
    h.obj# object_id,
    'TABLE' object_type,
    c.name column_name,
    DECODE(c.col#, 0, TO_NUMBER(NULL), c.col#) column_id,
    h.distcnt num_distinct,
    h.lowval low_value,
    h.hival high_value,
    h.null_cnt num_nulls,
    h.avgcln avg_col_len,
    h.timestamp# last_analyzed,
    CASE
      WHEN h.savtime < SYSTIMESTAMP
      THEN h.savtime
    END save_time,
    CASE
      WHEN h.savtime > SYSTIMESTAMP
      THEN 'PENDING'
      ELSE 'HISTORY'
    END version_type,
    h."OBJ#",
    h."INTCOL#",
    h."SAVTIME",
    h."FLAGS",
    h."NULL_CNT",
    h."MINIMUM" as low_value_cooked,
    h."MAXIMUM" as high_value_cooked,
    h."DISTCNT" as endpoints_count,
    h."DENSITY",
    h."LOWVAL",
    h."HIVAL",
    h."AVGCLN",
    h."SAMPLE_DISTCNT",
    h."SAMPLE_SIZE",
    h."TIMESTAMP#",
    h."EXPRESSION",
    h."COLNAME"
  FROM sys.wri$_optstat_histhead_history h,
    sys.col$ c,
    sys.obj$ o,
    sys.user$ u
  WHERE h.obj#  = c.obj#
  AND h.intcol# = c.intcol#
  AND h.obj#    = o.obj#
  AND o.type#   = 2
  AND o.owner#  = u.user#
  UNION ALL
  SELECT u.name owner,
    o.name table_name,
    o.subname partition_name,
    NULL subpartition_name,
    h.obj# object_id,
    'PARTITION' object_type,
    c.name column_name,
    DECODE(c.col#, 0, TO_NUMBER(NULL), c.col#) column_id,
    h.distcnt num_distinct,
    h.lowval low_value,
    h.hival high_value,
    h.null_cnt num_nulls,
    h.avgcln avg_col_len,
    h.timestamp# last_analyzed,
    CASE
      WHEN h.savtime < SYSTIMESTAMP
      THEN h.savtime
    END save_time,
    CASE
      WHEN h.savtime > SYSTIMESTAMP
      THEN 'PENDING'
      ELSE 'HISTORY'
    END version_type,
    h."OBJ#",
    h."INTCOL#",
    h."SAVTIME",
    h."FLAGS",
   h."NULL_CNT" ,
    h."MINIMUM" as low_value_cooked,
    h."MAXIMUM" as high_value_cooked,
    h."DISTCNT" as "endpoints_count",
    h."DENSITY",
    h."LOWVAL",
    h."HIVAL",
    h."AVGCLN",
    h."SAMPLE_DISTCNT",
    h."SAMPLE_SIZE",
    h."TIMESTAMP#",
    h."EXPRESSION",
    h."COLNAME"
  FROM sys.wri$_optstat_histhead_history h,
    sys.tabpart$ t,
    sys.col$ c,
    sys.obj$ o,
    sys.user$ u
  WHERE h.obj#  = t.obj#
  AND t.bo#     = c.obj#
  AND h.intcol# = c.intcol#
  AND h.obj#    = o.obj#
  AND o.type#   = 19
  AND o.owner#  = u.user#
  UNION ALL
  SELECT u.name owner,
    o.name table_name,
    o.subname partition_name,
    NULL subpartition_name,
    h.obj# object_id,
    'PARTITION' object_type,
    c.name column_name,
    DECODE(c.col#, 0, TO_NUMBER(NULL), c.col#) column_id,
    h.distcnt num_distinct,
    h.lowval low_value,
    h.hival high_value,
    h.null_cnt num_nulls,
    h.avgcln avg_col_len,
    h.timestamp# last_analyzed,
    CASE
      WHEN h.savtime < SYSTIMESTAMP
      THEN h.savtime
    END save_time,
    CASE
      WHEN h.savtime > SYSTIMESTAMP
      THEN 'PENDING'
      ELSE 'HISTORY'
    END version_type,
    h."OBJ#",
    h."INTCOL#",
    h."SAVTIME",
    h."FLAGS",
    h."NULL_CNT",
    h."MINIMUM" as low_value_cooked,
    h."MAXIMUM" as high_value_cooked,
    h."DISTCNT" as "endpoints_count",
    h."DENSITY",
    h."LOWVAL",
    h."HIVAL",
    h."AVGCLN",
    h."SAMPLE_DISTCNT",
    h."SAMPLE_SIZE",
    h."TIMESTAMP#",
    h."EXPRESSION",
    h."COLNAME"
  FROM sys.wri$_optstat_histhead_history h,
    sys.tabcompart$ t,
    sys.col$ c,
    sys.obj$ o,
    sys.user$ u
  WHERE h.obj#  = t.obj#
  AND t.bo#     = c.obj#
  AND h.intcol# = c.intcol#
  AND h.obj#    = o.obj#
  AND o.type#   = 19
  AND o.owner#  = u.user#
  UNION ALL
  SELECT us.name owner,
    op.name table_name,
    op.subname partition_name,
    os.subname subpartition_name,
    h.obj# object_id,
    'SUBPARTITION' object_type,
    c.name column_name,
    DECODE(c.col#, 0, TO_NUMBER(NULL), c.col#) column_id,
    h.distcnt num_distinct,
    h.lowval low_value,
    h.hival high_value,
    h.null_cnt num_nulls,
    h.avgcln avg_col_len,
    h.timestamp# last_analyzed,
    CASE
      WHEN h.savtime < SYSTIMESTAMP
      THEN h.savtime
    END save_time,
    CASE
      WHEN h.savtime > SYSTIMESTAMP
      THEN 'PENDING'
      ELSE 'HISTORY'
    END version_type,
    h."OBJ#",
    h."INTCOL#",
    h."SAVTIME",
    h."FLAGS",
   h."NULL_CNT",
    h."MINIMUM" as low_value_cooked,
    h."MAXIMUM" as high_value_cooked,
    h."DISTCNT" as "endpoints_count",
    h."DENSITY",
    h."LOWVAL",
    h."HIVAL",
    h."AVGCLN",
    h."SAMPLE_DISTCNT",
    h."SAMPLE_SIZE",
    h."TIMESTAMP#",
    h."EXPRESSION",
    h."COLNAME"
  FROM sys.wri$_optstat_histhead_history h,
    sys.tabsubpart$ tsp,
    sys.tabcompart$ tcp,
    sys.col$ c,
    sys.obj$ os,
    sys.user$ us,
    sys.obj$ op
  WHERE h.obj#  = tsp.obj#
  AND tsp.pobj# = tcp.obj#
  AND tcp.bo#   = c.obj#
  AND h.intcol# = c.intcol#
  AND tsp.obj#  = os.obj#
  AND os.type#  = 34
  AND os.owner# = us.user#
  AND tcp.obj#  = op.obj#
  AND op.type#  = 19;

 

Agora vamos utiliza-la juntamente com as views  dba_tables,  dba_tab_cols, dba_part_col_statistics para obter histórico e dados atuais.

col LOW_VALUE for a18
col HIGH_VALUE for a25

SELECT  owner,
    table_name,
    partition_name,
    column_name,
    column_id,
    num_distinct,
    low_value,
    high_value,
    density,
    num_nulls,
    avg_col_len,
    sample_size,
    last_analyzed,
    save_time,
    version_type
  FROM sys.wmon_tab_col_stat_hist
      where  owner = '&&OWNER'
      and    table_name = '&&TABLENAME'
      and    column_name like (nvl('&&COLUMNNAME','%'))
  UNION ALL
 SELECT  a.owner,
    a.table_name,
    c.partition_name,
    b.column_name,
    b.column_id,
    b.num_distinct,
    b.low_value,
    b.high_value,
    b.DENSITY,
    b.num_nulls,
    b.avg_col_len,
    b.sample_size,
    b.last_analyzed,
    TO_TIMESTAMP_TZ(NULL) save_time,
    'CURRENT' version_type
  FROM  sys.dba_tables a,  sys.dba_tab_cols b, dba_part_col_statistics c
where a.owner = b.owner
and   b.owner = c.owner(+)
and   a.table_name = b.table_name
and   b.table_name = c.table_name(+)
and   a.owner = '&&OWNER'
and   a.table_name = '&&TABLENAME'
and b.column_name like (nvl('&&COLUMNNAME','%'))
ORDER BY 1,2, save_time DESC;

Resultado:

OWNER       TABLE_NAME          PARTITION_ COLUMN_NAME      COLUMN_ID NUM_DISTINCT LOW_VALUE        HIGH_VALUE             DENSITY  NUM_NULLS AVG_COL_LEN SAMPLE_SIZE LAST_ANAL SAVE_TIME                    VERSION
---------- ------------------ ---------- --------------- ---------- ------------ ------------------ ------------------------- ---------- ---------- ----------- ----------- --------- ---------------------------------------- -------
TESTE       NOTC             DATPROC_1      42         533 786E071D010101     78700819010101          .005912363      0          8        5588  27-AUG-12                                          CURRENT
TESTE       NOTC             DATPROC_1      42         533 786E071D010101     78700819010101          .002994012      0          8        52356 26-AUG-12 27-AUG-12 04.06.39.533139000 AM -03:00   HISTORY
TESTE       NOTC             DATPROC_1      42         533 786E071D010101     78700819010101          .002912363      0          8        52356 26-AUG-12 26-AUG-12 11.19.53.448959000 PM -03:00   HISTORY
TESTE       NOTC             DATPROC_1      42         533 786E071D010101     78700819010101          .002912363      0          8        52356 25-AUG-12 26-AUG-12 04.07.23.670461000 AM -03:00   HISTORY

Acima coloquei somente uma coluna que é utilizada no join e no predicado de igualdade, mas podemos colocar todas caso seja necessário e onde seria o ideal mas aqui é somente um teste para exemplificar.

Podemos ver que houve coleta de somente 10% sobre essa coluna modificando o valor de alguns dados importantes que o otimizador utiliza para escolher um bom plano de execução, onde nos dias normais era coletado 100%.

Vamos dar uma olhada nos indices:

Para isso, vamos criar outra view .

Como sys OU usuário de sua preferência com os privilégios necessários:

CREATE OR REPLACE FORCE VIEW SYS.wmon_ind_hist ("OWNER", "INDEX_NAME", "TABLE_OWNER", "TABLE_NAME", "PARTITION_NAME", "SUBPARTITION_NAME", "OBJECT_ID", "OBJECT_TYPE", "LEAF_BLOCKS", "DISTINCT_KEYS", "AVG_LEAF_BLOCKS_PER_KEY", "AVG_DATA_BLOCKS_PER_KEY", "CLUSTERING_FACTOR", "NUM_ROWS", "SAMPLE_SIZE", "LAST_ANALYZED", "SAVE_TIME", "VERSION_TYPE", "OBJ#", "SAVTIME", "FLAGS", "ROWCNT", "BLEVEL", "LEAFCNT", "DISTKEY", "LBLKKEY", "DBLKKEY", "CLUFAC", "SAMPLESIZE", "ANALYZETIME", "GUESSQ", "CACHEDBLK", "CACHEHIT", "LOGICALREAD")
AS
  SELECT ui.name owner,
    oi.name index_name,
    ut.name table_owner,
    ot.name table_name,
    NULL partition_name,
    NULL subpartition_name,
    h.obj# object_id,
    'INDEX' object_type,
    h.leafcnt leaf_blocks,
    h.distkey distinct_keys,
    h.lblkkey avg_leaf_blocks_per_key,
    h.dblkkey avg_data_blocks_per_key,
    h.clufac clustering_factor,
    h.rowcnt num_rows,
    h.samplesize sample_size,
    h.analyzetime last_analyzed,
    CASE
      WHEN h.savtime < SYSTIMESTAMP
      THEN h.savtime
    END save_time,
    CASE
      WHEN h.savtime > SYSTIMESTAMP
      THEN 'PENDING'
      ELSE 'HISTORY'
    END version_type,
    h."OBJ#",
    h."SAVTIME",
    h."FLAGS",
    h."ROWCNT",
    h."BLEVEL",
    h."LEAFCNT",
    h."DISTKEY",
    h."LBLKKEY",
    h."DBLKKEY",
    h."CLUFAC",
    h."SAMPLESIZE",
    h."ANALYZETIME",
    h."GUESSQ",
    h."CACHEDBLK",
    h."CACHEHIT",
    h."LOGICALREAD"
  FROM sys.wri$_optstat_ind_history h,
    sys.ind$ i,
    sys.obj$ oi,
    sys.user$ ui,
    sys.obj$ ot,
    sys.user$ ut
  WHERE h.obj#              = i.obj#
  AND i.type#              IN (1, 2, 3, 4, 6, 7, 8)
  AND BITAND(i.flags, 4096) = 0
  AND i.obj#                = oi.obj#
  AND oi.namespace          = 4
  AND oi.remoteowner       IS NULL
  AND oi.linkname          IS NULL
  AND oi.owner#             = ui.user#
  AND i.bo#                 = ot.obj#
  AND ot.owner#             = ut.user#
  UNION ALL
  SELECT ui.name owner,
    oi.name index_name,
    ut.name table_owner,
    ot.name table_name,
    oi.subname partition_name,
    NULL subpartition_name,
    h.obj# object_id,
    'PARTITION' object_type,
    h.leafcnt leaf_blocks,
    h.distkey distinct_keys,
    h.lblkkey avg_leaf_blocks_per_key,
    h.dblkkey avg_data_blocks_per_key,
    h.clufac clustering_factor,
    h.rowcnt num_rows,
    h.samplesize sample_size,
    h.analyzetime last_analyzed,
    CASE
      WHEN h.savtime < SYSTIMESTAMP
      THEN h.savtime
    END save_time,
    CASE
      WHEN h.savtime > SYSTIMESTAMP
      THEN 'PENDING'
      ELSE 'HISTORY'
    END version_type,
    h."OBJ#",
    h."SAVTIME",
    h."FLAGS",
    h."ROWCNT",
    h."BLEVEL",
    h."LEAFCNT",
    h."DISTKEY",
    h."LBLKKEY",
    h."DBLKKEY",
    h."CLUFAC",
    h."SAMPLESIZE",
    h."ANALYZETIME",
    h."GUESSQ",
    h."CACHEDBLK",
    h."CACHEHIT",
    h."LOGICALREAD"
  FROM sys.wri$_optstat_ind_history h,
    sys.indpart$ ip,
    sys.ind$ i,
    sys.obj$ oi,
    sys.user$ ui,
    sys.obj$ ot,
    sys.user$ ut
  WHERE h.obj#              = ip.obj#
  AND ip.bo#                = i.obj#
  AND i.type#              IN (1, 2, 3, 4, 6, 7, 8)
  AND BITAND(i.flags, 4096) = 0
  AND ip.obj#               = oi.obj#
  AND oi.namespace          = 4
  AND oi.remoteowner       IS NULL
  AND oi.linkname          IS NULL
  AND oi.owner#             = ui.user#
  AND i.bo#                 = ot.obj#
  AND ot.owner#             = ut.user#
  UNION ALL
  SELECT ui.name owner,
    oi.name index_name,
    ut.name table_owner,
    ot.name table_name,
    oi.subname partition_name,
    NULL subpartition_name,
    h.obj# object_id,
    'PARTITION' object_type,
    h.leafcnt leaf_blocks,
    h.distkey distinct_keys,
    h.lblkkey avg_leaf_blocks_per_key,
    h.dblkkey avg_data_blocks_per_key,
    h.clufac clustering_factor,
    h.rowcnt num_rows,
    h.samplesize sample_size,
    h.analyzetime last_analyzed,
    CASE
      WHEN h.savtime < SYSTIMESTAMP
      THEN h.savtime
    END save_time,
    CASE
      WHEN h.savtime > SYSTIMESTAMP
      THEN 'PENDING'
      ELSE 'HISTORY'
    END version_type,
    h."OBJ#",
    h."SAVTIME",
    h."FLAGS",
    h."ROWCNT",
    h."BLEVEL",
    h."LEAFCNT",
    h."DISTKEY",
    h."LBLKKEY",
    h."DBLKKEY",
    h."CLUFAC",
    h."SAMPLESIZE",
    h."ANALYZETIME",
    h."GUESSQ",
    h."CACHEDBLK",
    h."CACHEHIT",
    h."LOGICALREAD"
  FROM sys.wri$_optstat_ind_history h,
    sys.indcompart$ ip,
    sys.ind$ i,
    sys.obj$ oi,
    sys.user$ ui,
    sys.obj$ ot,
    sys.user$ ut
  WHERE h.obj#              = ip.obj#
  AND ip.bo#                = i.obj#
  AND i.type#              IN (1, 2, 3, 4, 6, 7, 8)
  AND BITAND(i.flags, 4096) = 0
  AND ip.obj#               = oi.obj#
  AND oi.namespace          = 4
  AND oi.remoteowner       IS NULL
  AND oi.linkname          IS NULL
  AND oi.owner#             = ui.user#
  AND i.bo#                 = ot.obj#
  AND ot.owner#             = ut.user#
  UNION ALL
  SELECT ui.name owner,
    oi.name index_name,
    ut.name table_owner,
    ot.name table_name,
    os.name partition_name,
    os.subname subpartition_name,
    h.obj# object_id,
    'SUBPARTITION' object_type,
    h.leafcnt leaf_blocks,
    h.distkey distinct_keys,
    h.lblkkey avg_leaf_blocks_per_key,
    h.dblkkey avg_data_blocks_per_key,
    h.clufac clustering_factor,
    h.rowcnt num_rows,
    h.samplesize sample_size,
    h.analyzetime last_analyzed,
    CASE
      WHEN h.savtime < SYSTIMESTAMP
      THEN h.savtime
    END save_time,
    CASE
      WHEN h.savtime > SYSTIMESTAMP
      THEN 'PENDING'
      ELSE 'HISTORY'
    END version_type,
    h."OBJ#",
    h."SAVTIME",
    h."FLAGS",
    h."ROWCNT",
    h."BLEVEL",
    h."LEAFCNT",
    h."DISTKEY",
    h."LBLKKEY",
    h."DBLKKEY",
    h."CLUFAC",
    h."SAMPLESIZE",
    h."ANALYZETIME",
    h."GUESSQ",
    h."CACHEDBLK",
    h."CACHEHIT",
    h."LOGICALREAD"
  FROM sys.wri$_optstat_ind_history h,
    sys.indsubpart$ isp,
    sys.indcompart$ icp,
    sys.ind$ i,
    sys.obj$ os,
    sys.obj$ oi,
    sys.user$ ui,
    sys.obj$ ot,
    sys.user$ ut
  WHERE h.obj#              = isp.obj#
  AND isp.pobj#             = icp.obj#
  AND icp.bo#               = i.obj#
  AND i.type#              IN (1, 2, 3, 4, 6, 7, 8)
  AND BITAND(i.flags, 4096) = 0
  AND isp.obj#              = os.obj#
  AND os.type#              = 35
  AND os.namespace          = 4
  AND os.remoteowner       IS NULL
  AND os.linkname          IS NULL
  AND i.obj#                = oi.obj#
  AND oi.type#              = 1
  AND oi.owner#             = ui.user#
  AND i.bo#                 = ot.obj#
  AND ot.type#              = 2
  AND ot.owner#             = ut.user#;

Agora vamos executar com a query abaixo:

select "OWNER",
       TABLE_NAME,
       index_name,        
       BLEVEL,
       LEAF_BLOCKS,
       DISTINCT_KEYS,
       AVG_LEAF_BLOCKS_PER_KEY,
       AVG_DATA_BLOCKS_PER_KEY,
       CLUSTERING_FACTOR,
       NUM_ROWS,
       SAMPLE_SIZE,
       LAST_ANALYZED,
       "SAVE_TIME",
       "VERSION_TYPE"
from SYS.wmon_ind_hist
where owner = '&&OWNER'
and table_name = '&&TABLENAME'
union all
select  owner,
       TABLE_NAME,
       index_name,        
       BLEVEL,
       LEAF_BLOCKS,
       DISTINCT_KEYS,
       AVG_LEAF_BLOCKS_PER_KEY,
       AVG_DATA_BLOCKS_PER_KEY,
       CLUSTERING_FACTOR,
       NUM_ROWS,
       SAMPLE_SIZE,
       LAST_ANALYZED,
    TO_TIMESTAMP_TZ(NULL) save_time,
    'CURRENT' version_type
FROM  sys.dba_ind_statistics
where owner = '&&OWNER'
and table_name = '&&TABLENAME'
ORDER BY owner, table_name, index_name, save_time DESC;

Resultado:

OWNER       TABLE_NAME          INDEX_NAME             BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR   NUM_ROWS SAMPLE_SIZE LAST_ANAL SAVE_TIME                      VERSION
---------- ------------------ ------------------------------ ---------- ----------- ------------- ----------------------- ----------------------- ----------------- ---------- ----------- --------- ---------------------------------------- -------
TESTE       NOTC          IDX_NOTC_WMON01               1     269        52356            1            1          35722      5228         52356 27-AUG-12                          CURRENT
TESTE       NOTC          IDX_NOTC_WMON01               1     269        52356            1            1          32100     52356         52356 26-AUG-12 27-AUG-12 04.06.41.235206000 AM -03:00   HISTORY
TESTE       NOTC          IDX_NOTC_WMON01               1     269        52356            1            1          32110     52356         52356 26-AUG-12 26-AUG-12 11.19.57.073063000 PM -03:00   HISTORY
TESTE       NOTC          IDX_NOTC_WMON01               1     269        52356            1            1          31990     52356         52356 25-AUG-12 26-AUG-12 04.07.25.828409000 AM -03:00   HISTORY
TESTE       NOTC          IDX_NOTC_WMON01               1     268        52328            1            1          32010     52328         52328 25-AUG-12 25-AUG-12 11.18.31.942616000 PM -03:00   HISTORY
...

Acima podemos ver que o indice que era escolhido antes ( performance boa), também recebeu 10% do total das estatísticas mudando alguns valores que influênciaram no otimizador para tomada de decisão, uma vez que temos outros indices que disputam esses valores com diferenças mínimas, em casas decimais.

Após coleta 100% de estatísticas sobre essa tabela em específico a performance voltou ao normal pois o otimizador recebeu maiores informações do que se fazer. (Lembrando que antes disso, outro colega já havia invalidado planos, carregado outros, tentou criar profiles, baselines onde com profile conseguiu “passar maiores informações” para que o otimizador escolhese o indice bom, mas depois disso, o profile não foi mais necessário.)

Esse post foi simples pois na realidade seu objetivo, foi somente passar algumas informações sobre as tabelas de histórico do Oracle, que armazenam as informações de cada coleta de estatística e também vimos algumas maneiras que podemos utiliza-las no dia a dia.

Existe outra tabela que utilizo para ver o histórico dos histogramas para deixar a analise mais eficaz. Seria a tabela sys.wri$_optstat_histgrm_history mas essa, deixo de lição de casa para analisarem.

Forte abraço

 

7 thoughts on “Histórico de estatísticas Tabelas/Indices!”

  1. Pingback: Histórico de estatísticas Parte 2 ! | Wellington Prado – Oracle Blog
  2. Trackback: Histórico de estatísticas Parte 2 ! | Wellington Prado – Oracle Blog
  3. Bruno says:

    Parabens em mano, show seu post

    abs

    1. admin says:

      Vlw Brunão…
      abs

  4. Marcos José says:

    Perfeito.
    Seu texto de fácil entendimento, indo justamente no problema em que a nova versão tem em relação a anterior.
    Muito obrigado.

    1. admin says:

      Vlw Marcos… Obrigado pelos comentários…

  5. Marcos José says:

    Quando informa em fazer a coleta em 100%, pensando tambem no histograma, utiliza a clausula “for all columns size auto” ou “for all indexes…”?

    1. admin says:

      Essa situação ja faz algum tempo que postei e se não me falha a memória era uma tabela que não utilizava histogramas.
      Trazendo esse caso para atualidade e para outros ambientes, tudo depende do ambiente, querys , estrutura/dados da tabela.
      Eu prefiro analisar bem e utilizar histogramas em colunas específicas, ou seja tento evitar ao máximo o “for all columns size auto” que vai criar histogramas sobre todas as colunas utilizadas e que estão gravadas na sys.col_usage$, certamente um desperdício e pode ser um tiro no pé.

      Após mapear as colunas que precisam de histograma, eu faço a manutenção com o “for all columns size repeat”

      Espero ter respondido…

      Forte abraço

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>