Início / dba / Perguntas / 56130
Accepted
fejesjoco
Asked: 2014-01-05 06:25:56 +0800 CST

O Oracle não está usando um índice exclusivo para uma chave longa

  • 772

Eu tenho uma tabela com 250 mil linhas em meu banco de dados de teste. (Existem algumas centenas de milhões em produção, podemos observar o mesmo problema lá.) A tabela possui um identificador de string nvarchar2(50), não nulo, com um índice exclusivo (não é o PK).

Os identificadores são compostos por uma primeira parte que possui 8 valores diferentes em meu banco de dados de teste (e cerca de mil em produção), depois um sinal @ e, finalmente, um número, de 1 a 6 dígitos. Por exemplo, pode haver 50 mil linhas que começam com 'ABCD_BGX1741F_2006_13_20110808.xml@' e são seguidas por 50 mil números diferentes.

Quando eu consulto uma única linha com base em seu identificador, a cardinalidade é estimada em 1, o custo é muito baixo, funciona bem. Quando eu consulto mais de uma linha com vários identificadores em uma expressão IN ou uma expressão OR, as estimativas para o índice estão completamente erradas, então uma verificação completa da tabela é usada. Se eu forçar o índice com uma dica, é muito rápido, a varredura completa da tabela é realmente executada uma ordem de magnitude mais lenta (e muito mais lenta na produção). Portanto, é um problema do otimizador.

Como teste, dupliquei a tabela (no mesmo schema+tablespace) com exatamente o mesmo DDL e exatamente o mesmo conteúdo. Eu recriei o índice exclusivo na primeira tabela para uma boa medida e criei exatamente o mesmo índice na tabela clone. eu fiz um DBMS_STATS.GATHER_SCHEMA_STATS('schemaname',estimate_percent=>100,cascade=>true);. Você pode até ver que os nomes dos índices são consecutivos. Então agora a única diferença entre as duas tabelas é que a primeira foi carregada em ordem aleatória por um longo período de tempo, com blocos espalhados no disco (em um tablespace junto com várias outras tabelas grandes), a segunda foi carregada como uma em lote INSERIR-SELECIONAR. Fora isso, não consigo imaginar nenhuma diferença. (A tabela original foi reduzida desde a última grande exclusão e não houve uma única exclusão depois disso.)

Aqui estão os planos de consulta para o doente e a tabela clone (as strings sob o pincel preto são as mesmas em toda a imagem e também sob o pincel cinza.):

planos de consulta

(Neste exemplo, há 1867 linhas que começam com o identificador que é preto escovado. Uma consulta de 2 linhas produz uma cardinalidade de 1867*2, uma consulta de 3 linhas produz uma cardinalidade de 1867*3, etc. Não é possível seja uma coincidência, a Oracle parece não se importar com o final dos identificadores.)

O que poderia causar esse comportamento? Obviamente, seria muito caro recriar a tabela em produção.

USER_TABLES: http://i.stack.imgur.com/nDWze.jpg USER_INDEXES : http://i.stack.imgur.com/DG9um.jpg Alterei apenas o esquema e o nome do tablespace. Você pode ver que os nomes da tabela e do índice são iguais aos da captura de tela do plano de consulta.

oracle oracle-11g-r2

3 respostas

  1. Eu encontrei a solução! É tão lindo e eu realmente aprendi MUITO sobre o Oracle.

    Em uma palavra: histogramas.

    Comecei a ler muito sobre como o CBO da Oracle funciona e me deparei com histogramas. Eu não entendi completamente, então dei uma olhada na tabela USER_HISTOGRAMS e voilá. Havia várias linhas para a tabela doente e praticamente nada para a tabela clonada. Para a tabela doente, havia uma linha para cada uma das 8 diferentes partes iniciais do identificador. E esta é a chave: eles foram cortados em 32 caracteres, antes do sinal @. Como eu disse, a primeira parte das chaves é altamente repetitiva, elas se tornam diferentes após o sinal @.

    Parece que os histogramas podem ser mais poderosos do que o simples fato de que um índice único sempre tem uma cardinalidade de 0 ou 1 para um determinado valor. Quando eu estava consultando mais de 2 linhas, o Oracle olhou para o histograma, pensou que poderia haver dezenas de milhares de valores para aquela parte inicial do identificador e tirou o CBO do curso.

    Excluí os histogramas dessa coluna na tabela antiga e o problema desapareceu!

    Mais leitura: https://blogs.oracle.com/optimizer/entry/how_do_i_drop_an_existing_histogram_on_a_column_and_stop_the_auto_stats_gathering_job_from_creating

    • 8
  2. Best Answer

    (Isso responde à outra pergunta sobre por que os histogramas são diferentes.)

    Os histogramas são criados por padrão com base na inclinação da coluna e se a coluna foi usada em um predicado relevante. Copiar o DDL e os dados não é suficiente, as informações da carga de trabalho também são importantes.

    De acordo com o Guia de ajuste de desempenho :

    Ao descartar uma tabela, as informações de carga de trabalho usadas pelo recurso de coleta de histograma automático e o histórico de estatísticas salvo usado pelos procedimentos RESTORE_*_STATS são perdidos. Sem esses dados, esses recursos não funcionam corretamente.

    Por exemplo, aqui está uma tabela com dados distorcidos, mas sem histograma:

    drop table test1;
create table test1(a date);
insert into test1 select date '2000-01-01'+level from dual connect by level <= 10;
insert into test1 select date '2000-01-01' from dual connect by level <= 1000;
begin
    dbms_stats.gather_table_stats(user, 'TEST1');
end;
/
select histogram from user_tab_columns where table_name = 'TEST1';

HISTOGRAM
---------
NONE

    Executar a mesma coisa, mas com uma consulta antes das estatísticas serem coletadas, gerará um histograma.

    drop table test1;
create table test1(a date);
insert into test1 select date '2000-01-01'+level from dual connect by level <= 10;
insert into test1 select date '2000-01-01' from dual connect by level <= 1000;
select count(*) from test1 where a = sysdate; --Only new line
begin
    dbms_stats.gather_table_stats(user, 'TEST1');
end;
/
select histogram from user_tab_columns where table_name = 'TEST1';

HISTOGRAM
---------
FREQUENCY
    • 7

  3. Enviei um e-mail para Jonathan Lewis sobre isso e recebi uma resposta muito útil:

    A estranheza no cálculo é uma consequência dos limites dos histogramas baseados em caracteres, veja particularmente:

    http://jonathanlewis.wordpress.com/2010/10/13/frequency-histogram-5/ http://jonathanlewis.wordpress.com/2010/10/19/frequency-histograms-6/

    Observando o exemplo, a consulta é para uma lista IN, não para uma única linha, portanto, meu palpite inicial seria que o otimizador usou uma estratégia genérica para calcular a seletividade de várias linhas, em vez de ter um código de caso especial para um lista IN em uma chave primária. Eu acho que não seria muito difícil para eles reconhecerem este caso, mas os desenvolvedores provavelmente não consideraram que valeria a pena o esforço.

    Eu recomendo fortemente a leitura das postagens do blog que ele vincula, elas descrevem em detalhes a limitação dos histogramas que você está executando, por exemplo:

    Conclusão : Se você tiver strings razoavelmente longas e semelhantes em uma coluna que é um bom candidato para um histograma de frequência (por exemplo, uma coluna de status muito descritiva), você terá um problema se um valor muito raro parecer idêntico a um muito popular valor até os primeiros 32 caracteres. Você pode descobrir que a única solução é alterar a lista de valores legais (embora várias estratégias envolvendo colunas virtuais ou índices baseados em funções possam contornar o problema).

    • 6

relate perguntas