Menu de navegação

[guilhermeParticipante]

Olá pessoal,
Estou basicamente com um problema de desempenho…
Estou com uma query, montada automaticamente por um sistema de BI (base Data warehouse), onde realiza parte da query acessando índices e outra parte (outras tabelas) com acesso FTS. Até aí normal… Porém utilizando o hint opt_param(‘optimizer_index_cost_adj’,0) na query, percebemos uma grande melhora no desempenho da mesma. Seguindo os planos de execução, também percebemos os acessos aos índices das tabelas que estavam vinham sendo realizado FTS.

Verificações e análises feitas:
1º – Não podemos alterar e salvar a query inserindo o hint, pois como falei, a query é montada pelo sistema.
2º – As estatísticas (tabelas, indices) do bd estão atualizadas (consulta via dba_tables,dba_indexes). Também temos ciência desta execução, pois é executada com dbms_stat.gather_schema_stats(‘owner’,cascade=>TRUE) todo domingo via agendamento cron.
3º – Percebemos uma execução cross join na query (produto cartesiano) o que achávamos que obrigava a realizar um FTS, mas se fosse assim, mesmo com o hint também não funcionaria, certo?
4º – versão do bd: Oracle 10g 10.2.0.4.

O que fazer neste caso para que acesse a tabela através dos índices, ao invés de realizar FTS sem forçar com hint?

Abs

[José Laurindo ChiappaModerador]

Opa, vou reponder em ordem diversa da que vc usou :

a) essa versão 10.2.0.4 teve ** SIM ** diversos bugs , INCLUSIVE DE PERFORMANCE, corrigidos no patchset seguinte, o 10.2.0.5 , facilmente PODE SIM SER que vc esteja enfrentando um deles… Recomendo PRA QUANTO ANTES a aplicação desse patchset E do conjunto de patches mais recente que tenha sido disponibilizado pro seu ambiente/sistema operacional/hardware

b) sim, vc estava Enganado ao supor que Obrigatoriamente uma operação de CROSS JOIN forçosamente implica em full table scan : é comum que isso aconteça, já que JOIN do tipo implica que não há (ou o Otimizador não conseguiu identificar) uma chave completa E portanto uma varredura de dados será necessária,mas Não é Obrigatório o full tabgle scan, pode ocorrer um Index Scan, pode ocorrer hashing…

c) DE FORMA ALGUMA vc pode concluir que é só executar um dbms_stat.gather_schema_stats(‘owner’,cascade=>TRUE); e TODOS OS TIPOS de informações estatísticas estão coletados, não senhor : essa sintaxe usa os defaults, que no 10g iirc significa NÃO vai contabilizar a tabela inteira (e sim pegar só uma Amostra), NÃO vai coletar completamente os Histogramas (ie, medir quantas linhas devem ser retornadas para cada valor-chave indicado no WHERE)…. Isso é importante porque a ** PRIMEIRA COISA ** que se pensa para explicar um caso como o seu (ie, Existe um índice que a inclusão via HINT comprova que dá resultado melhor mas o Otimizador não o usou) é JUSTAMENTE ISSO, ie, vc tem alguma(s) tabela(s) onde a amostrinha de leitura default e os histogramas genéricos NÂO FORAM SUFICIENTES….
O que fazer para analisar isso em primeiro lugar é :

1. isolar (através de consulta na V$SQL, ou talvez de um trace) o Texto Exato e Completo do SQL que a aplicação tá enviando para o banco

2. executar esse SQL num ambiente controlado (sql*plus é uma boa), mas ajustado para que se obtenha o PLANO DE EXECUÇÂO EXTENDIDO, que te dá info (entre outras coisas) sobre a estimativa de linhas que as estatísticas dizem que vai ter versus a qtdade efetivamente lida (ie, A-ROWS e E-ROWS) : cfrme https://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/ mostra, algumas vezes é necessário se introduzir o hint de GATHER_STATISTICS

3. obter logo após a execução do SQL via DISPLAY_CURSOR o Plano de Execução **** REAL *** (e NÃO o estimado por um comando EXPLAIN PLAN!!!) e comparar A-ROWS com E-ROWS, cfrme http://structureddata.org/2007/11/21/troubleshooting-bad-execution-plans/ exemplifica

   ==> SE para qualquer passo do Plano a diferença entre a Estimativa e a Efetiva qtdade de linhas foi significativa, vc Acabou de Provar que a sua coleta de estatísticas pelos defaults NÃO TÁ BOA, vc vai ter que alterar seu procedimento de coleta para fazer um SAMPLE (ie, leitura de blocos da tabela) maior, E se for o caso aumentar a quantidade de amostras para histogramas : procure em asktom.oracle.com por HISTOGRAM DBMS_STATS.GATHER_TABLE_STATS que vc acha diversas refs/exemplos

d) uma outra possibilidade de índice não-usado é um eventual caso onde a performance efetiva do teu sistema de I/O tá sendo totalmente diferente do que o RDBMS espera : vc pode tentar recoletar dados do hardware via GATHER_SYSTEM_STATS, https://oracle-base.com/articles/misc/cost-based-optimizer-and-database-statistics#system_stats é uma ref de exemplo..
E é claro, pode sim ser que vc tenha aí um caso tão distorcido que nem assim o Otimizador consiga equalizar, OU mesmo pode ser que a má performance do I/O seja devido à n outros SQLs também fazendo I/O e a tua controladora e/ou disk volumes até tão trabalhando no pico de eficiência mas não tão é dando conta de fazer tanto I/O : num caso desses, aí sim vale a pena Analisar a possibilidade de subir/descer o Custo de se usar ou não um índice, via parâmetros optimizer_xxxx

e) e finalmente, se nada deu certo, ou se vc quer de momento usar o quebra-galho do hint enquanto a análise mais detalhada (que TEM que ser feita!!) decorre : ***** NÃO É VERDADE **** que vc não possa injetar HINTs mesmo sem acesso ao código-fonte – ao contrário, vc tem DIVERSAS opções de fazer isso!! Pesquise (via google E na documentação!) por ORACLE OUTLINE e por ORACLE SQL PROFILE que vc acha refs diversas…
QUE FIQUE CLARO : para mim, HINTs são OU a ÚLTIMA possibilidade, a ser usada quando TUDO O MAIS FALHOU, OU então são um “quebra-galho”, uma tentativa de corrigir TEMPORARIAMENTE o comportamento de um SQL : de forma alguma eu recomendo NEM que se apele para o HINT como uma primeira opção e NEM que se pense que tá tudo bem, tá tudo Permanentemente Solucionado se vc aplicou um HINT e não fez a análise completa de ambiente…

[]s

Chiappa

[guilhermeParticipante]

@Chiappa

Irei verificar a questão de atualização de patch.
Já havíamos feito o isolamento do sql com trace, com isso conseguimos verificar com hint este tem uma performance muito melhor.
Havia também feito pesquisa sobre o SQL Profile, porém o desenvolvedor ficou de me confirmar se a estrutura sql gerada pela ferramente segue sempre o mesmo padrão, alterando somente os valores literais…

Realmente, verificando tudo isso, confirma o que eu já pensava, é um problema complexo… esta difícil de encontrar o problema raiz… vou tentar seguir os passos mencionados por você!!

Muito obrigado
[]s

[José Laurindo ChiappaModerador]

OK – eu particularmente reputo como mais trabalhoso do que complexo, mas ao menos vc já conseguiu isolar o SQL e identificou o índice que se usado comprovadamente dá melhor performance, isso já é meio caminho andado…. Siga as indicações que te dei, dando ESPECIAL ATENÇÃO, como havia dito, à questão de avaliação de qualidade das estatísticas (comparando A-ROWS com E-ROWS no plano de execução REAL e EXTENDIDO), mas ** principalmente ** investigue a questão dos HISTOGRAMAS, Principalmente se a aplicação envia SQLs se referenciando a valores ao invés de BIND VARIABLES : histogramas até podem ser usados quando se tem BINDs (principalmente com a funcionalidade de BIND PEEKING) mas quando se compara colunas da tabela diretamente com valores é aí que a informação de quantas linhas um determinado valor-chavce retorna (ie, o HISTOGRAMA) é quase líquida e certa de ser usada… Dá uma BOA lida, estude direitinho os artigos da série http://allthingsoracle.com/histograms-part-1-why/ , que eles vão te ajudar Imensamente a compreender esse importante conceito, para então poder avaliar a viabilidade de passar a fazer um sample com mais linhas (talvez na tabela toda), e o Tamanho de amostragem para os histogramas….
Um outro conselho que te dou é tentar fazer a sua análise inicialmente FORA da aplicação, ie, executando numa tool cliente o SQL que vc isolou (sq*plus, Oracle SQL developer, TOAD, PL/SQL developer, aquela que vc tiver/quiser/gostar) e aí apenas nessa sessão , numa hora em que NÂO haja outros usuários/concorrência, testar os efeitos de histogramas maiores, de sample size maior/completo, de ALTER SESSION mudando parâmetros OPTIMIZER_xxx …..

[]s

Chiappa

[Autor]

Posts