Menu de navegação

[TyttoParticipante]

Gostaria da ajuda de vocês para entender se o OLTP vai me ajudar de fato.
Tenho uma aplicação ERP, e como sabem, ERP’s são movidos a dados.
Praticamente tudo que se faz num ERP acaba realizando um CRUD em tabelas de banco.
Tenho dezenas de procedures, functions e triggers que possuem regra de negócio pesada.
Tenho diversas rotinas que consultam grandes volumes de dados, geralmente rotinas de apurações de final de mês, que acessam até 1 milhão registros numa tabela principal fazendo join’s com quase uma dezena de outras (guardem suas críticas aos joins pois é legado e estamos removendo/evitando 🙂 )
Nessa aplicação, tenho tabelas grandes de movimentações, com cerca de 5 milhões de registros, e essas tabelas são muito acessadas diariamente pelos usuários, fazendo CRUD nela a todo momento.

Mesmo com bons recursos de hardware e otimização de banco, as transações são demoradas, pois acessam tabelas grandes e concorridas. As consultas grandes, com os joins também não demoradas, mesmo que sendo feitas fora de horário de CRUD.

Claro que o assunto é mais profundo e exige uma avaliação, mas a questão é: O OLTP pode a princípio me ajudar especificamente nesse cenário? Ou o OLTP é só para in-memory de tabelas para consultas de grandes volumes de dados?
Tenho algumas conclusões, mas gostaria da visão de vocês.

Desde já agradeço a ajuda.

[José Laurindo ChiappaModerador]

Bom, antes de tudo existem ** diversas ** options/tecnologias (tanto built-ins quanto add-ons, licenciados à parte ou não) com ‘OLTP’ no meio do nome num banco de dados Oracle Enterprise Edition : vc está falando DE QUE EXATAMENTE, vc está rotulando de ‘OLTP’ O QUE ???? Será que é OLTP COMPRESSION (também conhecida como Advanced Compression) ?? Oracle Database Appliance OLTP Database Shapes ?? IN-MEMORY e/ou RESULTCACHE (que a doc Oracle indica especificamente para processamento/manipulação de dados sob procedimentos/normas de programação OLTP, ie, On Line Transaction Processing) ? Detalhes plz, O QUE EXATAMENTE vc está pondo nesse caixotão genérico com o nome de ‘OLTP’ ???
Mas de modo geral : sim, quando se fala em ERP, devido Justamente ao volume de dados via de regra vc obtém melhores resultados com técnicas/features de banco voltados para grandes volumes de dados/DW, que são voltadas a Reduzir a quantidade de I/Os e/ou maximizar throughput pra acessar os dados – são coisas como Particionamento, Paralelel SQL, Indexação Parcial, cópia/pré-processamento dos dados com Views materializadas, Arquivamento dos dados (ie, remover dados ‘antigos’ para uma outra tabela/partição), técnicas de Evitar UPDATEs e DELETEs (por exemplo, se o processamento vai fazer um UPDATE ou um DELETE em mais de 80% de uma tabela gigante, há casos onde é muito mais performante vc fazer uma cópia dos poucos dados a manter pra uma outra tabela em APPEND-mode e Parallel do que gerar a montanha de REDO e UNDO que o UPDATE ou o DELETE geram)…. Via de regra, caching extra (que é o que o in-memory e o result cache fazem) NÂO adiantam num ERP porque o montão de dados a processar é muuuuito maior do que a RAM disponível…. Igualmente, compactar os dados (via Advanced Compression) muito provavelmente vai é te dar uma PERFORMANCE PIOR, pois pra poder ser usada a informação TEM que ser descompactada, o que Consume I/Os e poder de CPU : dificilmente um sistema que Já Está engargalado (acho eu, pelo que vc descreve) vai ter o I/O e o poder de CPU extras pra descompactar os dados a serem usados….
E mesmo sem conhecer teu ambiente, Observo que :

1. seja OLTP ou DW o ‘estilo’ de processamento, com certeza quando se fala de consultas em volumes gigantes de dados, é *** CRUCIAL *** não só vc evitar processamento (com views materializadas que já te dão o Agrupamento de dados que vc precisa por exemplo : é *** IRRACIONAL *** vc ter uma rotina de fechamento de mês que varre toda a tabela pra obter uma soma/agrupamento, óbvio que é mil vezes melhor vc ter uma VIEW MATERIALIZADA que já faça isso por você, talvez um pouquinho a cada noite se não for viável uma vm com refresh on commit), MAS também fazer o processamento o mais otimizado possível. Por exemplo, usando Funções Analíticas vc obtém Running Totals, registros anteriores/posteriores/TOP-n registros numa determinada lógica SEM PRECISAR DE SUB-QUERY, evitando assim um segundo acesso à mesma tabela na query…. Igualmente, eu NÃO COMPREENDO UPSERTs e correlatos feitos manualmente, via de regra MERGE é a melhor opção….
O que eu quero dizer aqui é que é FUNDAMENTAL que a pessoal Conheça Profundamente os recursos built-ins para consultas e SQLs no RDBMS Oracle, só assim se consegue re-escrever uma query ou um SQL longos/pesados o mais eficientemente possível

2. TRIGGERs (e constraints/índices) tem impacto DIRETO na capacidade de fazer cargas em APPEND-mode e Parallel SQL : assim, se hoje vc está em meio a um processo de os rever/otimizar/eliminar (ou pel menos os DESATIVAR TEMPORARIAMENTE, durante alguns processamentos batch, talvez) onde/se/quando possível , vc tá num bom caminho

3. Quando se fala em PERFORMANCE, a regra de ouro é : com os dados já fisicamente Otimizados o melhor possível (ie, usando&abusando de índices parciais, append-mode, Particionamento, cluster tables e índices, GTTs, etc, etc) E com as estatísticas o melhor possível coletadas (com HISTOGRAMAS onde adequado, sempre Atualizadas, com estatísticas de sistema, enfim) vc PRIMEIRO tem que tentar fazer o processamento/consulta NUM SÓ SELECT, sem cursor loops nem nada assim, e fazendo os JOINs necessários diretamente, usando PARALELISMO NO SQL, Analytics, WHERE clause/CTE, enfim, toda a riquiza de recursos do SQL… SOMENTE SE não foi mesmo possível fazer o necessário tudinho num só SQL, SÓ AÍ vc passa pro PL/SQL, sempre nesse caso cuidando de usar&abusar dos recursos do PL/SQL, como ARRAY PROCESSING/BULK COLLECT, PIPELINED FUNCTIONs, eventual paralelismo manual via DBMS_PARALLEL….

[]s

Chiappa

[TyttoParticipante]

Obrigado pela ajuda.

Na verdade eu gostaria de uma visão do que de fato desse “caixotão” poderia ser utilizado para melhorar a performance da aplicação conforme os detalhes que passei.

Na verdade estou procurando algo que SEM ALTERAÇÃO de código/consultas da aplicação poderia representar melhora de performance, mesmo que dependesse da atualização do banco para utilização de alguma feature.

É como se avaliando essas definições da aplicação você pudesse dizer qual recurso do caixotão genérico eu devesse aprofundar para conseguir alguma melhora a curto prazo sem reescrever nada.

[José Laurindo ChiappaModerador]

Ok : a sua pergunta então é se QUALQUER uma das builts-ins/técnicas de programação e/ou add-ons do banco Oracle que se referem á processamento no ‘estilo’ OLTP pode te ajudar, e SEM NENHUMA alteração no código….
Bom, primeiro sobre a parte de não reescrita eu DUVIDO : não há features ‘mágicas’, OLTP-related ou não, que peguem um SQL mal escrito e/ou mal implementado, que está fazendo trocentos I/Os desnecessários e o ‘corrija’ : CEDO ou TARDE vc vai SIM se deparar com a necessidade de reescrita : eu já vi casos nos meus tempos de EBS onde o SQL original era tão ruim que foi declarado sem salvação e o cliente optou por criar uma tela e um report CUSTOMIZADOS que faziam o que era preciso…. E é claro, ERP ou não, por princípio alteração/otimização de SQLs é algo que deveria vir do Fornecedor : às vezes vc consegue alterar o SQL sem acesso ao fonte (via Profiles, views com rename de tabelas, ou reescrita de SQL automática, vide minha palestra no dbabrasil 1.0 em http://docplayer.com.br/25266808-Sql-factoring-ajustes-de-sql-sem-acesso-aos-fontes-por-jose-laurindo-chiappa.html para refs) mas IMHO a responsa TEM que ser do Fornecedor….
Aí, sobre features/técnicas/add-ons/produtos OLTP adaptáveis ao RDBMS Oracle em geral : não, como eu disse DIFICILMENTE algo relacionado à OLTP vai ser de utilidade prum ERP justamente devido aos Grandes Volumes de dados – vc VAI TESTAR no seu ambiente, óbvio, mas como dito em lrage databases caches, compactações e coisas do tipo ou ultrapassam a RAM disponível no volume necessário de dados a acessar OU exigem processamento extra (pra descompactações e coisaradas assim), o que num sistema já engargalado DIFICILMENTE vai ser viável, essa é a minha Avaliação para eles em cima do que vc nos informa….

Como dito antes, em grandes volumes de dados muito PROVAVELMENTE técnicas/softwares/recursos voltados pra OLTP vão ser inusáveis, o que DEVE SER muito mais aplicável à essa realidade são técnicas/features/softwares/recursos de DW/batch processing…. O grande detalhe é que praticamente TODAS essas técnicas/refs/recursos DW (seja Global temporary Tables para tabelas de uso temporário, Particionamento, Clustering, Views materializadas, índices parciais, e outras) NÃO SÃO AUTOMÁTICAS, exigem que vc ALTERE a implementação física e/ou o modelo de dados, seja renomeando um objeto do ERP e o substituindo por um seu, seja incluindo novos objetos, alterando um objeto e movendo os dados para incluir partições nele, criando novo índice, etc : da mesma forma que muitas vezes é complexo obter as autorizações para se alterar código e/ou se obter um código refatorado, TAMBÉM não é incomum vc encontrar impedimentos/enpecilhos para a alteração de modelo e/ou de implementação física do modelo….

==> Sem alteração NENHUMA (nem de modelo, nem física nem de código) num banco de grande volume de dados eu Acredito que só algumas POUCAS features/técnicas DW (como Parallel SQL, por exemplo) vão poder afetar a performance do código, e mesmo assim em poucos casos – é quase CERTO que melhorias significativas vão exigir alterações nas tabelas, no modelo E alguma re-eescrita de código (que pode até ser automática , como no caso de Outlines e SQL profiles), mas REESCRITA e ALTERAÇÔES NO MODELO/BANCO imho vai ser INESCAPÁVEIS para ganhos palpáveis de performance….
Minha resposta então é : faça um teste mas sem grandes expectativas pra OLTP, as quais IMHO não vai ser aplicáveis pra você (afaik NADA no caixotão “OLTP” vai te ser útil) e trate de analisar viabilidade e implementar o que puder das features/recursos DW, é isso….

[]s

Chiappa

[José Laurindo ChiappaModerador]

E adicionalmente :

a. não basta só implementar o recurso de qquer jeito e achar que já está top : por exemplo, um sujeito desavisado poderia simplesmente particionar a tabela de VENDAS por DATA_DA_VENDA e achar que já tá megamaster – veja https://connor-mcdonald.com/2018/07/25/hyper-partitioned-index-avoidance-thingamajig/ por exemplo, onde uma análise mais Apurada da necessidade levou à uma política de particionamento mais ousada e com muuuuito mais chances de ser efetiva, dada as restrições/modo de uso da tabela de VENDAS…..

b. a maior parte dos recursos de DW, além de exigirem alterações (no modelo, no banco, na implementação física, etc) TAMBÉM implicam em maior consumo de recursos de hardware , seja digamos espaço em disco (tal como o caso das views materializadas, que sendo como são CÓPIAS dos dados originais Obviamente consomem espaço extra), seja recursos de hardware (como o Paralelismo por exemplo, que abre N sessões cada uma consumindo CPU e I/O e RAM extras) : nem preciso dizer que o PLANEJAMENTO é algo fundamental aqui, lançar (digamos) um SQL Paralelo consumindo 4x mais recursos bem na hora em que o sistema tá em pico de uso Não É um uso inteligente….

c. que os recursos/técnicas/itens/features de DW podem ser Brutalmente efetivos não se discute : veja abaixo um simples exemplo dum Treinamento que ministrei há algum tempo pra um cliente,

Exemplo :

=> crio uma tabela ‘grande’ (alguns milhões de linhas) :

SYSTEM:@O11GR2SE:SQL>create table BIG_TABLE_EMP (EMP_ID number, EMP_NAME varchar2(100), ORG_NAME varchar2(50));
SYSTEM:@O11GR2SE:SQL>create sequence SEQ_BIG_TABLE_EMP;
SYSTEM:@O11GR2SE:SQL>alter table BIG_TABLE_EMP nologging;
SYSTEM:@O11GR2SE:SQL>select count(*) from BIG_TABLE_EMP;

COUNT(*)
———-
10842150

SYSTEM:@O11GR2SE:SQL>

=> como este teste está sendo feito numa máquina onde só eu utilizo o RDBMS, a potência completa de I/O (que nem é tanta assim, é um notebook com discos SATA ) está toda a minha disposição – assim, para eu ter um volume de dados que custe alguns minutos pra ser lido e que portanto dê diferença no processamento serial x paralelo, vou ter que criar tabela de vários milhões de linhas :

SYSTEM:@O11GR2SE:SQL>ed
Gravou file afiedt.buf

1 BEGIN
2 for i in 1..150 loop
3 insert /*+ APPEND */ into BIG_TABLE_EMP (select SEQ_BIG_TABLE_EMP.nextval, object_name, lpad(‘XXXXXXX’, 45, ‘*’) from ALL_OBJECTS);
4 COMMIT;
5 end loop;
6* END;
SYSTEM:@O11GR2SE:SQL>/

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:05:46.73
SYSTEM:@O11GR2SE:SQL>

=> OK : agora vou abrir duas telas de sql*plus, e em uma vou fazer um SQL (UPDATE no caso) serial, ‘comum’, onde apenas essa sessão vai solicitar pro Sistema Operacional os I/Os necessários, um de cada vez, E na segunda tela vou fazer o mesmo UPDATE em Paralelo (usando o Parallel mode manual, de ‘pobre’, não-automático) :

SYSTEM:@O11GR2SE:SQL>set SQLPROMPT ‘SYSTEM:@O11GR2SE#1:SQL>’
SYSTEM:@O11GR2SE#1:SQL>

e na outra janela :

SYSTEM:@O11GR2SE:SQL>set SQLPROMPT ‘SYSTEM:@O11GR2SE#2:SQL>’
SYSTEM:@O11GR2SE#2:SQL>

=> primeiro faremos o processo normal serial na janela 1 :

SYSTEM:@O11GR2SE#1:SQL>set timing on
SYSTEM:@O11GR2SE#1:SQL>update BIG_TABLE_EMP set ORG_NAME=’ABC’;

10842150 linhas atualizadas.

Decorrido: 00:12:45.87
SYSTEM:@O11GR2SE#1:SQL>

==> Excruciantemente lento… Vou encerra a transação (desfazendo essas alterações com ROLLBACK, para liberar minha área/tablespace de undo/rollback E meus redo log files), senão não vou ter recursos no meu banco-teste pra fazer operações, E (claro) o ROLLBACK vai consumir tanto tempo ou mais do que o processamento :

SYSTEM:@O11GR2SE#1:SQL>rollback;

Rollback concluído.

Decorrido: 00:21:24.34
SYSTEM:@O11GR2SE#1:SQL>

==> Agora farei a mesma Operação na janela#2 mas em Parallel :

SYSTEM:@O11GR2SE#2:SQL>set timing on
SYSTEM:@O11GR2SE#2:SQL>exec DBMS_PARALLEL_EXECUTE.CREATE_TASK (‘Update Org’);

Procedimento PL/SQL concluído com sucesso.

SYSTEM:@O11GR2SE:SQL>DECLARE
2 c_task_name CONSTANT VARCHAR2(128) := ‘UPDATE_ORG’;
3 BEGIN
4 dbms_parallel_execute.create_chunks_by_rowid (task_name => c_task_name
5 , table_owner => USER
6 , table_name => ‘BIG_TABLE_EMP’
7 , by_row => TRUE
8 , chunk_size => 1000);
9 —
10 dbms_parallel_execute.run_task (task_name => c_task_name
11 , sql_stmt => q’$ update BIG_TABLE_EMP
12 set ORG_NAME = ‘ABC’
13 where rowid between :start_id and :end_id $’
14 , language_flag => DBMS_SQL.native
15 , parallel_level => 5);
16 —
17 dbms_parallel_execute.drop_task(c_task_name);
18 END;
19 /

Procedimento PL/SQL concluído com sucesso.

Decorrido: 00:05:17.42

SYSTEM:@O11GR2SE:SQL>

===> Evidência que os dados foram gravados OK :

SYSTEM:@O11GR2SE:SQL>select count(*) from BIG_TABLE_EMP where ORG_NAME=’ABC’;

COUNT(*)
———-
10842150

Decorrido: 00:00:07.70
SYSTEM:@O11GR2SE:SQL>

=> É INQUESTIONÁVEL o ganho de performance, completou a mesma tarefa em quase UM TERÇO do tempo… E isso num banco DE TESTE, que roda no meu notebook pessoal : imagina os ganhos que vc pode obter usando os recursos/features/options/técnicas DW num hardware PROFISSIONAL….

[]s

Chiappa

[TyttoParticipante]

Obrigado pela esclarecedora ajuda.

[Autor]

Posts