Por que esse SQL é executado muito mais rápido em um servidor Oracle com o mesmo hardware / dados que outro servidor Oracle?

Question

Meu aplicativo tem um SQL que leva ~ 30 min para ser executado no servidor Oracle em produção. Leva aproximadamente o mesmo em um servidor Oracle de teste.

Por algum motivo, em outro servidor Oracle, ele é executado muito mais rápido: apenas 5 min ou mais!

Esses intervalos são realmente apenas para o SQL (sem sobrecarga de processamento do aplicativo). Eu obtive-os do Oracle Enterprise Manager. Além disso, eles são consistentes, ou seja, se você executar novamente o SQL novamente, obterá os mesmos intervalos de tempo.

Hardware, versão Oracle (10g), Os dados são os mesmos nestes três servidores. Até mesmo os planos de execução de SQL são exatamente os mesmos.

O que faz o SQL rodar muito mais rápido nesse ambiente de teste?

O SQL é um MERGE:

MERGE /*+ USE_NL(DORMANT_POINTS) */
INTO MKT_CURVE_POINT DORMANT_POINTS
USING (SELECT
// big select   
) ACTIVE_POINTS
    ON (
// ..
)
WHEN MATCHED THEN
UPDATE
SET DORMANT_POINTS.ACTIVE_PARENT_PRICE = ACTIVE_POINTS.ACTIVE_PARENT_PRICE
WHERE DORMANT_POINTS.ACTIVE_PARENT_PRICE <>
ACTIVE_POINTS.ACTIVE_PARENT_PRICE;

Eu suspeito que isso é algo sobre o cache. Eu observei um alto número de buffers em comparação com as leituras físicas no servidor que executa o SQL rapidamente. Essa proporção é menor no servidor que a executa lentamente.

O que pode explicar essa enorme diferença de desempenho?

    

Answer 1

Concorrência, travamento e travas podem desempenhar um papel. Eu suponho que o servidor de produção está fazendo algo diferente de esperar por esta consulta em particular?

A política de memória está definida como automática? Talvez o servidor de produção tenha alocado seu pool SGA de maneira diferente.

É também uma questão de quais dados estão realmente no pool compartilhado e no cache de buffer. O servidor de teste pode ter mais dados relevantes no buffer, não sendo descartado por outras consultas de produção.

Depois, há configuração de hardware. Algo simples como o cache de write back pode fazer uma grande diferença.

Divertido como é, não vamos desperdiçar todo o nosso tempo com palpites. Rastreie a consulta com intervalos completos e veja o que realmente está acontecendo:)

    

Answer 2

Coisas a considerar:

1. Os parâmetros de inicialização são os mesmos? Você insinua o cache ... isso pode ser significativo.
2. Qual é a carga relativa em cada servidor? O servidor "rápido" é pouco usado, portanto, os dados não são mais antigos do cache tão rápido?
3. O hardware é realmente idêntico? Pequenas diferenças, por exemplo, controladores RAID, podem fazer uma enorme diferença.

Bem-vindo ao mundo do ajuste de desempenho: - /

    

Answer 3

"Mesmo os planos de execução de SQL são exatamente os mesmos."

Em primeiro lugar, como você sabe disso? Em 10g, você deve encontrar o cursor em v $ sql e, em seguida, usar o sql_id em uma chamada para DBMS_XPLAN.DISPLAY_CURSOR. Isso mostra o plano de explicação realmente usado, em oposição a uma instrução EXPLAIN PLAN que é uma previsão sobre qual plano pode ser usado.

"Eu notei um alto número de buffers em comparação com as leituras físicas no servidor que executa o SQL rapidamente"

Pergunta de Daft, mas os dados são os mesmos?

Se a 'caixa rápida' estiver fazendo menos buffer do que a 'caixa lenta', ela está processando menos dados. Se estiver fazendo mais buffer do que a caixa lenta, ele está processando mais dados.

Não se concentre em leitura física. Se os dados e os planos de consulta forem os mesmos, os mesmos blocos lógicos serão processados na mesma ordem. Se acontecer de eles estarem no cache e não exigirem uma leitura física, eles serão executados mais rapidamente, mas isso não é algo que você possa controlar.

Em teoria, essa situação pode indicar que há mais memória para cache na caixa rápida do que as caixas lentas, mas se o hardware for o mesmo, isso implicaria que suas instâncias de teste e teste têm memória disponível no nível do hardware mas o banco de dados está configurado para não usá-lo, o que é bastante improvável. É mais provável que outras coisas executadas nessas caixas tenham forçado diferentes blocos de dados no cache.

    

Answer 4

Além disso, o Oracle Server Seam se adapta às cargas esperadas em vez da carga atual. Portanto, se você utilizar os servidores de maneira diferente, obterá um comportamento de cache diferente. Além disso, verifique cuidadosamente os planos de execução. Se houver uma execução ligeiramente diferente na seleção grande, como uma varredura de salto, em vez de uma varredura de índice, isso pode explicar tudo.

    

Answer 5

seu plano de explicação pode ser diferente.

faça isso:

definir linhas 200 explicar plano para

seu sql NÃO será executado. você receberá uma mensagem

"explicado"

faça isso

selecione * da tabela (dbms_xplan.display);

Faça isso nos dois bancos de dados e compare. É assim que o oracle acessa as tabelas. as probabilidades são diferentes.

Certifique-se de que suas tabelas sejam analisadas nos bancos de dados e nos índices E os dados são os mesmos.