Preciso rodar a seguinte consulta na minha aplicação para carregar JList’s que me indicam os arquivos txt´s que preciso carregar no meu banco de dados diariamente:
Uso banco ORACLE. O grande problema é que esta tabela está com uma quantidade grande de registros (mais de 50 milhões atualmente e com uma taxa de crescimento de 10 mil registros por dia).
Esta consulta está sendo ineficiente porquê está levando mais de 5 minutos para executar… já indexei os campos que são chave de pesquisa e nada…
Gostaria de receber sugestões para solucionar o problema…
O primeiro passo eh identificar onde esta o gargalo. Por exemplo, esta consulta retorna quantos registros? Se retorna 10 milhoes, o gargalo pode ser a rede Outra possivel fonte para o problema eh o tempo que o Oracle esta levando para executar a consulta. Neste caso, eh melhor consultar um DBA Serio, isso pode ser causado por literalmente centenas de problemas, que vao desde “hardware ja eh pequeno para o banco de dados” ateh configuracao do Oracle em si, passando por uso inadequado dos indices (ele pode estar usando um indice quando deveria estar usando outro).
A consulta retorna poucos registros… Menos de 50 registros.
Isso é meio complicado partenon, porquê eu não conheço o DBA, o servidor fica em outra cidade e para se fazer qualquer requisição, têm que passar por um processo burocrático. Não acho que vão revisar o servidor para verificar isso…
Com certeza… Mas daí terei 30 milhões de registros na minha aplicação para manipular e extrair o que quero …
Se tiver um jeito de deixar essa bomba para o servidor filtra, não seria melhor?
Pelo que imagino, a consulta retorna poucos registros (50 datas) mas é necessário fazer um table scan ou um index scan dessa tabela (CADCTRL.GDD) porque nessa consulta em particular o código do fornecedor é uma condição pouco seletiva (digamos que se a tabela tiver 50 milhões de linhas, uma consulta como esta:
retorne 30 milhões de linhas, por exemplo. É isso que ocorre?
(É melhor você fazer um select count() antes para ver se é isso, porque, como você disse, o servidor está em outra cidade, e se você disparar uma consulta tão grande assim, você irá afogar o link com a outra cidade).
Você provavelmente irá ter de olhar o modelo de dados e ver se há possibilidade de dividir sua consulta em algumas outras. Por exemplo, pode não fazer sentido você usar uma condição tão abrangente assim em certos casos, e você possa pôr mais cláusulas no “where” para estreitar sua consulta.
Entao acho que isso mata a charada. Voce esta fazendo um distinct em 30 milhoes de registros, para retornar apenas 50. Nao sei exatamente como o Oracle faz (nao sou DBA), mas ate onde sei, ele primeiro pega todos estes 30 milhoes e depois faz o “distinct” deles. Por isso que esta tao lento.
Faca um teste: rode a consulta com distinct, pegue os IDs e depois rode a mesma consulta, sem distinct, mas especificando os IDs. Voce vai ver que ele demora muito mais com o distinct do que quando se busca direto pela chave primaria. Entao, assumindo que esta eh a unica forma de extrair os dados que vc quer, o problema nao esta em sua aplicacao. Esta na otimizacao do banco de dados
Se isso for suficiente para provar que o problema eh no Oracle, voce pode tentar criar uma tabela que eh populada de tempos em tempos com o resultado desta sua query pesada. Mas sinceramente, eu acho que sua empresa deveria contratar um DBA, nem que seja para dar consultoria de um ou dois dias… Um DBA Oracle pode dar conselhos melhores sobre Oracle do que a maioria de nos aqui do forum de Java
Eu estava pensando se criasse uma tabela para armazenar o resultado dessa consulta (tipo uma unidade de persistência da consulta) e que minha aplicação fosse atualizando quando houvessem mudanças…
Assim na hora de rodar aquela consulta, seria muito mais rápida, porquê eu teria o último resultado dela armazenado.
Mas não sei se seria a melhor solução em termos técnicos (ou seja, se seria gambiarra ou uma solução que muitos adotam)…se alguém atualizasse o banco fora da aplicação, pronto, já furaria o esquema…
[quote=entanglement][quote]
SELECT DISTINCT(DATA_ARQUIVO_FONTE) FROM CADCTRL.GDD WHERE FORNECEDOR = ?
[/quote]
Pelo que imagino, a consulta retorna poucos registros (50 datas) mas é necessário fazer um table scan ou um index scan dessa tabela (CADCTRL.GDD) porque nessa consulta em particular o código do fornecedor é uma condição pouco seletiva (digamos que se a tabela tiver 50 milhões de linhas, uma consulta como esta:
retorne 30 milhões de linhas, por exemplo. É isso que ocorre?
(É melhor você fazer um select count() antes para ver se é isso, porque, como você disse, o servidor está em outra cidade, e se você disparar uma consulta tão grande assim, você irá afogar o link com a outra cidade).
[/quote]
É isso que ocorre mesmo. Retornou aproximadamente 30 milhões.
[quote=entanglement]Você provavelmente irá ter de olhar o modelo de dados e ver se há possibilidade de dividir sua consulta em algumas outras. Por exemplo, pode não fazer sentido você usar uma condição tão abrangente assim em certos casos, e você possa pôr mais cláusulas no “where” para estreitar sua consulta.
[/quote]
Ok, vou analisar quais campos podem restringir mais a busca
Entao acho que isso mata a charada. Voce esta fazendo um distinct em 30 milhoes de registros, para retornar apenas 50. Nao sei exatamente como o Oracle faz (nao sou DBA), mas ate onde sei, ele primeiro pega todos estes 30 milhoes e depois faz o “distinct” deles. Por isso que esta tao lento.
Faca um teste: rode a consulta com distinct, pegue os IDs e depois rode a mesma consulta, sem distinct, mas especificando os IDs. Voce vai ver que ele demora muito mais com o distinct do que quando se busca direto pela chave primaria. Entao, assumindo que esta eh a unica forma de extrair os dados que vc quer, o problema nao esta em sua aplicacao. Esta na otimizacao do banco de dados
Se isso for suficiente para provar que o problema eh no Oracle, voce pode tentar criar uma tabela que eh populada de tempos em tempos com o resultado desta sua query pesada. Mas sinceramente, eu acho que sua empresa deveria contratar um DBA, nem que seja para dar consultoria de um ou dois dias… Um DBA Oracle pode dar conselhos melhores sobre Oracle do que a maioria de nos aqui do forum de Java ;-)[/quote]
É, pensei em fazer uma tabela auxiliar para isso mesmo… não sei se seria “gambiarra”.
Você precisa consultar o DBA Oracle da tal empresa para checar se um índice
DATA_ARQUIVO_FONTE + FORNECEDOR + sei lá o quê
ou coisa parecida pode melhorar o resultado da sua consulta. (Eu sou um zero à esquerda em SQL, portanto não sei exatamente que consulta seria boa para essa sua situação em particular.) Deve haver algum índice que não piore os resultados das inserções/atualizações em geral, e melhore sua consulta em particular.
Pensando um pouco mais sobre seu problema, eu vejo tres possiveis alternativas para serem testadas:
Use “group by” ao inves do distinct:
Crie uma nova tabela chamada “DATA_ARQUIVO_FORNECEDOR”, popule esta tabela com uma consulta parecida com a acima e depois crie uma trigger na tabela CADCTRL.GDD para inserir um registro na DATA_ARQUIVO_FORNECEDOR caso a data nao exista para o fornecedor em questao. Entao, na sua aplicacao, vc seleciona a partir desta nova tabela.
Caso nenhuma das acima seja viavel/possivel, vc pode ler sobre “query caching” no Hibernate. Mas para isso funcionar direito, voce deve ter pouco movimento na tabela CADCTRL.GDD e ela deve sofrer alteracoes apenas por sua aplicacao.
Caso nenhuma das tres seja possivel/viavel, entao realmente acho que vc precisa consultar um profissional Oracle
[quote=entanglement]Você precisa consultar o DBA Oracle da tal empresa para checar se um índice
DATA_ARQUIVO_FONTE + FORNECEDOR + sei lá o quê
ou coisa parecida pode melhorar o resultado da sua consulta. (Eu sou um zero à esquerda em SQL, portanto não sei exatamente que consulta seria boa para essa sua situação em particular.) Deve haver algum índice que não piore os resultados das inserções/atualizações em geral, e melhore sua consulta em particular.
[/quote]
Ok… pensei em algumas coisas. Como limitar a consulta para o último mês somente, para ver se resolve. Se fosse necessário fazer uma consulta extensiva, o cara selecionava para buscar os registros de todos os anos. Preciso testar algum critério desse tipo.
[quote=partenon]Pensando um pouco mais sobre seu problema, eu vejo tres possiveis alternativas para serem testadas:
Use “group by” ao inves do distinct:
[/quote]
Eu já tinha tentado essa tática mas deu na mesma…
Essa idéia parece boa… na hora de atualizar, geralmente insiro 10 mil registros diários. Acho que não haverá grandes problemas em colocar um trigger.
Funciona dessa forma: Ao clicar em um botão, a aplicação atualiza uma lista (JList) com os nomes dos arquivos txt que preciso importar. Para esse processo, ele roda a consulta. Atualmente só a aplicação que faz a atualização e só por via de uma operação, mas eventualmente pode ser necessária carga manual (imprevistos ou quando são migrados de outra fonte). Por isso que tava querendo evitar usar algum cache - mas não estou vendo muita saída. Talvez um cache seria uma boa alternativa.
Se eu entendi certo, somente a sua aplicação altera esta tabela, certo?
Então não há necessidade de se criar uma trigger para isso. Basta vc cuidar na sua aplicação mesmo os pontos de INSERT nessa tabela, e popular essa nova tabela.
Eu até faria de uma maneira um pouco diferente está ideia: criaria está outra tabela tabela, mas não duplicaria a informação do caminho do arquivo. Faria a tabela GDD ter uma FK apontando para a tabela DATA_ARQUIVO_FONTE e somente nesta ter a informação do caminho do arquivo. Mas isto é como eu faria. Vc deve fazer como melhor lhe convier.
criando um indice utilizando os campos retornados + os campos filtrados a consulta se resumiria ao indice, o que deveria retornar rapidamente (leria bem menos blocos)
já analisou o plano de execução desta query?
possivelmente ele pode estar dando um table scan, que numa tabela desse tamanho é terrível
se for testar o plano recomendo criar uma versão menor desta tabela (100k registros), para agilizar os testes.
você tem acesso full a este banco? tem usuário para rodar qualquer comando?
pode mandar atualizar estatísticas da tabela e fazer testes com indices.
mas isso num horário de pouco movimento, pois criar um indice nessa tabela deve demorar muito.
que edição/ versão de oracle está utilizando?
dependendo da edição/versão você pode criar uma materialized view com esta query.
seria a mesma coisa de criar esta tabela auxiliar, mas o oracle se encarregaria de mantê-la atualizada de tempos em tempos…
Outra possivel fonte para o problema eh o tempo que o Oracle esta levando para executar a consulta. Neste caso, eh melhor consultar um DBA 