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!

Oletros

22 horas atrás

Meus 2 cents,

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

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)

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) */

Partition Range

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

Materialized View

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

Uso do OpenSearch (ou Elasticsearch dependendo do caso)

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

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 !

sonnic

Autor

8 horas atrás

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!