Estimativa de cardinalidade errada após coleta de estatísticas ORACLE

Este é um problema comum com datas ascendentes em tabelas particionadas. A primeira coisa é verificar se as estatísticas incrementais estão funcionando.

SELECT notes
  FROM dba_tab_col_statistics
 WHERE table_name = 'MYTABLE'

Você deverá ver "INCREMENTAL" para cada uma das colunas. Caso contrário, você precisará fazer com que as estatísticas incrementais funcionem corretamente. Para que as estatísticas incrementais funcionem, você deve definir estas opções:

GRANULARITY -> AUTO
INCREMENTAL -> TRUE
PUBLISH -> TRUE
ESTIMATE_PERCENT -> DBMS_STATS.AUTO_SAMPLE_SIZE
APPROXIMATE_NDV_ALGORITHM -> LIKE '%HYPERLOGLOG%'
INCREMENTAL_LEVEL -> PARTITION

Você deve então coletar estatísticas com essas configurações uma vez, o que inicialmente fará uma coleta global. Pode ser uma boa ideia excluir as estatísticas da tabela primeiro para começar do zero. Então , as coletas subsequentes devem reunir apenas partições obsoletas e usá-las para estimar os valores mínimos/máx/distintos da coluna global.

Além disso, você não deseja histogramas em uma coluna de data, a menos que tenha um valor mágico (como 31/12/9999) que esteja superrepresentado ou uma única linha com uma data falsa que remonta a 1970 ou 1900 ou alguma data boba até agora, uma média mínima/máxima simples estaria realmente errada. Os histogramas são para distorções, não para valores distribuídos uniformemente, como acontece com a maioria das colunas de data. Acho que me lembro de ter lido que os histogramas substituem as estatísticas incrementais, então esse pode ser outro motivo para excluí-los das colunas de datas sempre crescentes. Em nosso big data warehouse não temos histogramas em colunas de data, especialmente aquelas que são chaves de particionamento. Contamos apenas com incremental e geralmente funciona muito bem.

Supondo que suas estatísticas incrementais estejam funcionando, o próximo passo é verificar se a coleta de estatísticas está acontecendo tarde demais (a consulta incorreta pode ser executada logo após a modificação/carregamento principal da partição, antes que as estatísticas tenham a chance de serem coletadas nos novos dados). Para verificar isso, observe LAST_ANALYZEDe compare com a hora de início da consulta incorreta e a hora do último grande carregamento de dados.

Você também pode verificar se o valor máximo da coluna de data está aproximadamente correto. Você terá que decodificá-lo a partir de seu valor bruto. Aqui está um conjunto de funções que você pode usar para exibir o valor mínimo/máximo de suas colunas:

CREATE OR REPLACE FUNCTION CAST_RAW_TO_CHAR (bdr IN raw) 
  RETURN varchar2
  DETERMINISTIC 
AS  
BEGIN
  RETURN utl_raw.cast_to_varchar2(bdr);
EXCEPTION 
  WHEN OTHERS THEN 
    RETURN NULL;
END CAST_RAW_TO_CHAR;
/
CREATE OR REPLACE FUNCTION CAST_RAW_TO_NUMBER (bdr IN raw) 
  RETURN number
  DETERMINISTIC 
AS  
BEGIN
  RETURN utl_raw.cast_to_number(bdr);
EXCEPTION 
  WHEN OTHERS THEN 
    RETURN NULL;
END CAST_RAW_TO_NUMBER;
/
CREATE OR REPLACE FUNCTION CAST_RAW_TO_DATE (bdr IN raw) 
  RETURN date
  DETERMINISTIC 
AS  
BEGIN
  RETURN
     date'1-1-1'
     + NUMTOYMINTERVAL(
         100 * (to_number(substr(bdr,1,2), 'xx') - 100) + 
         to_number(substr(bdr,3,2), 'xx') - 101, 
       'year')
     + NUMTOYMINTERVAL(to_number(substr(bdr,5,2), 'xx')-1, 'month')
     + NUMTODSINTERVAL(to_number(substr(bdr,7,2), 'xx')-1, 'day')
     + NUMTODSINTERVAL(to_number(substr(bdr,9,2), 'xx') - 1, 'hour')   
     + NUMTODSINTERVAL(to_number(substr(bdr,11,2), 'xx') - 1, 'minute')   
     + NUMTODSINTERVAL(to_number(substr(bdr,13,2), 'xx') - 1, 'second');
EXCEPTION 
  WHEN OTHERS THEN 
    RETURN NULL;
END CAST_RAW_TO_DATE;
/

CREATE OR REPLACE FUNCTION CAST_RAW_TO_DISPLAY_STRING (bdr IN raw, data_type IN varchar2)
  RETURN varchar2
  DETERMINISTIC 
AS
BEGIN
  RETURN CASE WHEN (bdr IS NULL) THEN NULL
              WHEN (data_type LIKE '%CHAR%') THEN cast_raw_to_char(bdr) 
              WHEN (data_type = 'NUMBER') THEN TO_CHAR(cast_raw_to_number(bdr))
              WHEN (data_type = 'DATE' OR data_type LIKE '%TIMESTAMP%') THEN TO_CHAR(cast_raw_to_date(bdr))
         END;
END;
/

Agora consulte as estatísticas:

SELECT /*+ NO_MERGE(cs) NO_MERGE(tc) */
       cs.owner,
       cs.table_name,
       cs.column_name,
       cs.num_distinct,
       tc.data_type,
       cast_raw_to_display_string(cs.low_value,tc.data_type) low_value_display,
       cast_raw_to_display_string(cs.high_value,tc.data_type) high_value_display,
       cs.density,
       cs.num_nulls,
       cs.num_buckets,
       cs.last_analyzed,
       cs.sample_size,
       cs.global_stats,
       cs.user_stats,
       cs.notes,
       cs.avg_col_len,
       cs.histogram,
       cs.scope
  FROM dba_tab_col_statistics cs,
       dba_tab_cols tc
 WHERE cs.table_name = 'MYTABLE'
   AND cs.owner = tc.owner
   AND cs.table_name = tc.table_name
   AND cs.column_name = tc.column_name

Execute isso quando sua consulta for ruim (antes de coletar estatísticas) para ver se o valor máximo é significativamente menor do que deveria (e em datas que podem estar algumas semanas atrasadas ou mais). Esta é outra indicação de que as estatísticas não estão sendo coletadas a tempo. Talvez seja necessário ajustar sua janela de coleta de estatísticas ou adicionar uma chamada manual de estatísticas (sem substituir nenhum parâmetro) ao código que carrega os dados, logo após a conclusão do carregamento.

Se tudo mais falhar e você não conseguir ajustar o tempo das estatísticas com os padrões do seu aplicativo, você sempre pode jogar a toalha de estatísticas e apenas sugerir a consulta com um ou dica CARDINALITYpara OPT_ESTIMATEdizer ao Oracle para esperar um milhão de linhas dessa tabela:

SELECT /*+ CARDINALITY(x,1000000) */ *
  FROM mytable x,
       someothertable y
 WHERE x.joinid = y.id

SELECT /*+ OPT_ESTIMATE(TABLE x ROWS=1000000) */ *
  FROM mytable x,
       someothertable y
 WHERE x.joinid = y.id