3
sonnic
2 min de leitura ·

Dúvida sincera: Como escalar consultas em Oracle com múltiplos filtros e alto volume (90M+ registros)?

Estou com um projeto que tenho tido algumas limitações de escala e que vem atrapalhando o desenvolvimento de uma API a nível de mercado para aguentar as solicitações.

Objetivo
Preciso criar uma API que irá disponibilizar para meu parceiros uma lista de pedidos realizados, uma lista de eventos realizados em cada pedido e etc. Enfim são vários end-points que permite o meu parceiro fazer uma pesquisa por filtros de período e alguns parâmetros seletivo.

Cenário atual
As informações estão registradas no oracle exadata que hoje possui alguns índices básicos.
Para buscar essas informações hoje utilizamos procedure onde ela realiza a busca e materializa os dados resultado em uma tabela. Existem casos onde um único dia do período pode atingir 3 milhões de registros de pedido. Essa foi a única forma até então otimizada de buscar esses dados.

Recebemos a requisição pela API retornamos um ID de processo e depois meu parceiro fica verificando se os dados já estão prontos.

Atualmente existe N possibilidades das informações serem filtradas, mas não posso criar um índice para cada combinação de filtro que meus parceiros fizer, pois afetaria o processo de registro desses dados que é realizado em massa.

Tentativas que falharam

Resposta síncrona e paginação por cursor

Tentamos realizar uma consulta ainda mais simplificada que a da procedure, utilizando a abordagem de paginação por cursor, mas esbarramos no volume de dados. Para que a paginação funcione corretamente precisamos realizar a ordenação com o order by IDPEDIDO. Se o parceiro me pede um intervalo de 30 dias preciso ordenar mais de 90M de registros. O que leva horas e fica inviável para uma resposta síncrona.

Existem casos onde o retorno de dados é bem pequeno, 10, 20 registros, mas o universo de dados que precisamos percorrer para retornar é gigante. O que torna a consulta lenta e por não existir índices de acordo com o filtro realizado o oracle pega alguns planos ruins.

Resposta assíncrona

Se continuarmos a utilizar a procedure retornando a resposta assíncrona, ainda assim continuo com o problema da paginação, pois se materializo o resultado da consulta feita em uma tabela e em seguida busco dela para retornar ao parceiro via API, não consigo dar o order by pelo volume de dados. Podemos ter casos onde haverá o estouro do limite de tamanho do body na resposta.

Um ponto importante de ressaltar sobre o uso da procedure é que se temos uma alta quantidade de solicitações dos nossos parceiros corremos o risco de até travar o banco de dados com wait events.

Com isso gostaria da ajuda de vocês e suas experiências com alto volume de dados para encontrar outras alternativas que possam contribuir com a escala desse projeto.

Obrigado!

Carregando publicação patrocinada...
1

Meus 2 cents,

Sem conhecer melhor a estrutura fica complicado dar direcoes mais exatas, mas vamos no basico:

  1. paginacao

SELECT * FROM (
SELECT
idpedido, data_pedido, cliente, status
FROM
pedidos
WHERE
data_pedido BETWEEN :inicio AND :fim
AND idpedido > :ultimo_id_recebido
ORDER BY
idpedido ASC
)
WHERE ROWNUM <= 50;

A vantagem desta abordagem eh que se voce tiver um indice por idpedido, o oracle faz um range scan e o rownum forca a parar a partir do 50 registros - isso agiliza um bocado (mas so faz sentido se voce usar a paginacao baseada em um indice conhecido)

  1. Hints

O oracle tem um negocio chamado 'hints', o que permite forcar algum comportamento durante a query, p.ex. forcar o uso de um campo/indice ou mesmo criar um indice que fica desabilitado para o dia-a-dia exceto naquele uso especifico.

de uma olhada em Invisible Indexes e /*+ INDEX(tabela invisible_index_x) */

  1. Partition Range

No oracle eh possivel dividir uma tabela internamente baseado em um campo. Talvez particionar por data seja interessante.

  1. Materialized View

Sao views pre-processadas, se voce tem um "padrao" de pesquisas pode agilizar o processo

  1. Uso do OpenSearch (ou Elasticsearch dependendo do caso)

Dependendo do caso o OpenSearch pode ser uma alternativa, mas implica em infra mais complexa

  1. Cache usando MySQL/MariaDB, Redis, Cassandra, PostgreSQL

Uma alternativa ao OpenSearch/Elastisearch seria usar um banco "comum" para cache apenas de leitura - neste caso precisa entender como funcionam os dados para escolher a alternativa mais interessante.

A vantagem eh que sendo bancos "read-only" voce pode otimizar tudo no full, sem se preocupar em corrupcao de dados por queda - se der merda, so reconstroi a partir do oracle.

O OpenSearch/Elasticsearch eh a melhor alternativa (antes dele existir ja usei cache com bancos "read-only", e tambem funcionou OK) - mas tem o problema da replicacao de dados, onde o ideal eh fazer isso em batch, mas novamente, depende muito de como seus dados sao organizados.

Nao esqueca de compartilhar os caminhos seguidos e o resultado

Saude e Sucesso !

1

Muito obrigado pela sua colaboração!

Vou trazer um pouco mais de detalhes...

Atualmente tenho alguns indices como disse e dois deles são, um pelo IDPEDIDO e outro pelos campos (IDPEDIDO, DATAPEDIDO), os registros usam partition range pela data do pedido.

Gostaria que o ROWNUM <= 50 funcionasse seria uma mão na roda, mas infelizmente no select interno tenho alguns filtros pela data identificador do parceiro e outros filtros que o parceiro pode adicionar como refrencia e etc...

Com o order by idpedido no select interno ele acaba fazendo um index full scan que degrada a performance da consulta.

EXPLAIN PLAN FOR
SELECT *
FROM (
    SELECT *
    FROM PEDIDO
    WHERE IDPARCEIRO = 20
      AND DTTRANSACAO BETWEEN TO_DATE('01/03/2006','DD/MM/YYYY')
                          AND TO_DATE('26/03/2026','DD/MM/YYYY')
      AND IDGATEWAY IN (1,2,4,10)
      AND SITUACAO IN ('P')
    ORDER BY IDPEDIDO
)
WHERE ROWNUM <= 50;

Resultado do explain

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |    50 |   157K|   271   (0)| 00:00:01 |       |       |
|*  1 |  COUNT STOPKEY                       |             |       |       |            |          |       |       |
|   2 |   VIEW                               |             |    51 |   160K|   271   (0)| 00:00:01 |       |       |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| PEDIDO      |    83M|    27G|   271   (0)| 00:00:01 | ROWID | ROWID |
|   4 |     INDEX FULL SCAN                  | PKPEDIDO    |  4387 |       |    19   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------

Sobre os indices invisiveis ainda preciso aprofundar se eles afetariam o insert e update dos dados, pelo que vi ele fica invisível apenas ao otimizador de consultas.

Olhando para um banco "read-only" ou OpenSearch/Elasticsearch é uma ideia que ainda não fiz nenhuma tentativa. Se puder compartilhar como utilizou a replicação dos dados o que deu certo e errado seria de grande valia.

Hoje meus dados estão organizados em tabelas particionadas, mas tenho vários tenant de banco de dados com dados de um ou vários parceiros.

Obrigado!

1

Meus 2 cents extendidos,

Minha situacao foi um projeto de IoT onde dispositivos mandavam telemetria e a pesquisa poderia acontecer de diversas formas.

Como resolvi:

  1. Separar tabela de persistencia de tabela de consulta

A de persistencia nao tinha indices, exceto pela PK (indices sao um problema serio em tabelas de alto volume)

Eu tinha uma tabela simples separada que mantinha a data e a PK (em alguns casos - sem PK so a Heap), assim se precisasse por alguma razao consultar os registros inseridos no dia X, consultava a tabela simples, pela qual sabia o range de ID que tinha de usar para a primera tabela.

Cheguei a usar um ID como BigInt e numeracao que comecava em ANOMESDIASEQ, p.ex., 260328000000001 e uma cron que resetava isso para o dia seguinte (260329000000000) mas no final das contas comecei a usar a tabela com ID normal e a outra so indicando o ID inicial e a data dele.

  1. Quando chegava um registro de telemetria, ele ia para 2 filas separadas

Fila 1: insert na tabela de persistencia

Fila 2: insert na tabela de consulta, totalmente assincrona

  1. Na tabela de consulta, joguei sujo como podia

3.1. As vezes nao era apenas uma tabela de consulta, as vezes tinha mais de uma com campos e normalizacao diferentes para diferentes tipos de consulta

3.2. Mandei a 3FN as favas - se um JOIN me complicava a vida (latencia), era eliminado com o campo equivalente sendo de-normalizado direto na tabela de consulta. Eh de matar DBA de desgosto e comia espaco em disco, mas agilidade contava mais

3.3. Em alguns BD se voce criar um indice que seja igual a tabela, ele ignora a tabela e usa o indice direto para manter os dados. Tem custo de insercao, mas depois a consulta e recuperacao sao absurdamente altos. Por isso filas separadas - a fila de consulta era assincrona e poderia demorar mais para inserir os dados, mas isso nao era um problema.

3.4. Logs de SQL eram registrados e olhavamos o que o pessoal consultava mais - criamos indices e otimizacoes especificas para aquilo que era mais usado.

3.5. Nao dava da escalar verticalmente (muito caro e nao era tao eficiente), enntao era tudo na horizontal: criei instancias separadas para consultas especificas.

3.6. Tabela de consulta nao tinha "redo logs" e tudo era cache

3.7. Lotes: a fila de consulta fazia buferizacao antes de inserir, de forma a mandar lotes de registros, tanto para diminuir overhead de rede quando processamento nos BDs de consulta (e tambem nao ficar com tanto sync wait).

3.8. Tabelas de consulta realizam sync para disco "se e quando" desse - o prioritario era manter em memoria o maximo possivel.

Tudo isso agilizava, mas o trade-off era risco de ter de reprocessar a fila de consulta caso uma instancia travasse (por isso tinha a tabela dos ID e data da tabela de persistencia) - mas na pratica do dia-a-dia isso se mostrou um medo que raramente ocorria.

Saude e Sucesso !

1

Meus 2 cents extendidos,

  1. Como disse no outro post, talvez aqui voce precise jogar sujo.

Por exemplo, tente este hint:

SELECT /*+ FULL(PEDIDO) PARALLEL(8) */ * ...

Para ignorar a PK e forcar o scan - o que neste caso provavelmente dara melhor resultado que ficar lendo o indice.

  1. PoC e tentar o Elastisearch

Como ele trabalha com indices bitmap, para o teu tipo de consulta provavelmente dara mais certo. Poe uns 10M de registros e veja o resultado.

Saude e Sucesso !

1

Eu não sei nada sobre Oracle. Mas trabalhei um bom tempo com MySQL e algo que eu via sempre eram queries que não usavam o índice mais importante que era o de data. Isso ocorria quando a query usavam função pra converter datahora pra date,o que invalidava o índice no MySQL.

Tem algo parecido assim no Oracle?

Vi na sua resposta a outro colega que sua query possui TO_DATE, por isso pensei nessa possibilidade.

Além disso, seria possível adicionar um filtro obrigatório no intervalo de datas? Como ele tem uma boa granularidade, isso diminui bastante a quantidade de registros em que os demais filtros (os sem índice) precisam ser executados.

Outra solução que fizemos em um caso foi dividir "fisicamente" a tabela em duas. Registros recentes (no nosso caso 6 meses) em uma, registros anteriores a isso em uma histórico. Dependendo do intervalo filtrado no momento, o sistema decide qual tabela buscar ou faz um union das duas.
Assim focamos nos casos mais comuns e não na exceção.