Menu de navegação

[buoroParticipante]

Boa tarde, está constatado por meio de análise do banco de dados que o ERP que utilizamos executa instruções SQL repetitivas e não faz uso de variáveis de bind, ocasionando percentuais altíssimos de reparse e consequente lentidão extrema. Após diversos testes, contando inclusive com esclarecimentos deste fórum em postagem anterior, cheguei até o parâmetro CURSOR_SHARING e setando-o como FORCE na sessão, melhorou muito o tempo de execução. Algo em torno de 20 vezes mais rápido.
Porém, o fornecedor declara que conforme documentação da Oracle pode haver efeitos colaterais no uso de CURSOR_SHARING = FORCE relacionado ao retorno de campos string com tamanho maior que o esperado. Na prática gostaria de entender como eu poderia ter algum problema com relação à isso: retorno de campos string com tamanho maior que o esperado? Poderia ajudar a esclarecer? Obrigado

[José Laurindo ChiappaModerador]

Tudo jóia ? Então, vou começar com os Conceitos envolvidos : para o RDBMS saber se um SQL já foi “compilado” ou não, se está no cache de memória ou não, ele se baseia no TEXTO DO SQL, exatamente como foi recebido…. Assim, digamos que uma aplicação envie um SQL do tipo :

select coluna1, coluna 2 from tabela where coluna1 = 45;

depois envie um outro SQL :

select coluna1, coluna 2 from tabela where coluna1 = 10;

e depois um SQL :

select coluna1, coluna 2 from tabela where coluna1 = 100;

==> PERCEBA que os textos desses SQL Não SÃO rigorosamente Igualzinhos entre si (possuem SIM diferença, no caso os caracteres no final de cada string SQL) : sendo assim, CADA UM desses SQLs foi interpretado como um SQL diferente, PORTANTO cada um deles teve que ser validado, ‘compilado’, cada um deles ocupou memória no cache de SQLs, para CADA UM DELES o RDBMS teve que montar um plano de execução completo, esse plano de execução foi mantido em cache também, o RDBMS teve que CHECAR DETALHADAMENTE cada um dos objetos que o SQL usa pra confirmar que estão válidos/acessíveis, teve que CONSULTAR as estatísticas internas de cada objeto…. Essa operação toda se chama PARSE e é COMPLEXA e gasta uma boa quantidade de memória e poder de CPU, então quanto MENOS parse vc fizer, melhor….
No caso exemplo que citei, a solução desse BUG (e SIM, SQL que não usa BIND VARIABLEs onde necessário é um BUG DA APLICAÇÂO, completamente!!) é SIMPLES, bastaria apenas que o Aplicativo ao invés de indicar o valor FIXO na string passe a usar uma VARIÁVEL e a cada vez só mude o valor da variável, o TEXTO DO SQL em si fica sempre o mesmo, mais ou menos tipo :

VARIABLE x number;

exec :X := 45;

select coluna1, coluna 2 from tabela where coluna1 = :X;

exec :X := 10;

select coluna1, coluna 2 from tabela where coluna1 = :X;

exec :X := 100;

select coluna1, coluna 2 from tabela where coluna1 = :X;

==> neste exemplo acima, PERCEBA que o texto do SQL NUNCA MUDOU, ele sempre é :

select coluna1, coluna 2 from tabela where coluna1 = :X;

==> PORTANTO, como o texto é sempre o mesmo, o banco NÂO FOI OBRIGADO a fazer PARSE a cada execução, POUPANDO assim CPU, memória e I/O (já que o RDBMS não teve que consultar seus metadados internos a cada execução e PÔDE se benefiar do cache de SQLs E do cache de Planos de Execução)…

Isso estabelecido, antes de discutir a questão do CURSOR SHARING que fique claro :

a) isso NÂO É UMA INVENÇÃO DA ORACLE, https://technet.microsoft.com/en-us/library/ms175580(v=sql.105).aspx mesmo ou http://tranpeter.blogspot.com.br/2013/10/using-bind-variables-on-sql-server-or.html por exemplo já registrma que também a Microsoft recomenda usar BINDING para performance ótima.. Isso é uma Conceito Fundamental que a tua Aplicação está VIOLANDO, okdoc ?? PORTANTO é um BUG, e BUG tem que ser corrigido é pelo FORNECEDOR DA APLICAÇÃO, e nunca por você, sim sim ?? Vamos discutir um possível WORKAROUND mas isso Não Exime o fornecedor…

e

b) QUALQUER alteração que vc vá fazer tem que ser MUITO BEM TESTADA, e (principalmente para ERPs) vc TEM que acionar o SUPORTE da Aplicação : via de regra vc TEM que solicitar PERMISSÃO ao fornecedor para fazer Qualquer Alteração, seja no modelo (com criação/remoção de índices, com refeitura de tabelas, com política de Auditoria, etc, etc), ou seja no funcionamento e Parametrização do banco em si…

==> Agora sim, falando sobre a GAMBIARRA, o WORK-AROUND do CURSOR_SHARING se vc for obrigado a isso (digamos, porque o vagal do Fornecedor se Recusa a corrigir o bug) E depois que vc obteve PERMISSÃO do fornecedor pra usar esse ajuste no banco : o que o RDBMS faz com esse param ativo é tentar varrer o texto dos SQLs que recebe e procurar por strings fixas, que ele remover do texto do SQL e substituir por uma variável que o banco mesmo vai criar, com um nome interno absurdo lá dele tipo SYS_xyz123… No meu exemplo, com o setting ativo, o SQL :

select coluna1, coluna 2 from tabela where coluna1 = 45;

seria Analisado pelo RDBMS, ele perceberia que tinha que criar uma variável (digamos que o nome inventado dela seja SYS_xyz123) :

VARIABLE SYS_xyz123 VARCHAR(200);

e PROSSEGUINDO a análise, o texto seria substituído, ficando :

select coluna1, coluna 2 from tabela where coluna1 = :SYS_xyz123;

e colocaria na variável o valor que ele retirou do texto :

EXEC : SYS_xyz123 := 45;

e EM TESE com isso o SQL editado ficou EQUIVALENTE ao texto original… Similarmente, os outros textos também seriam substituídos, ficando também como “select coluna1, coluna 2 from tabela where coluna1 = :SYS_xyz123;” – só o valor da variável SYS_xyz123 é que iria mudando…

Parece algo inocente, fácil de fazer e tranquilo, não é ?? Pois NÂO É, há diversas possíveis Armadilhas e Efeitos Colaterais que podem vir daí :

1. essa análise e edição do texto do SQL ** OBVIAMENTE ** consome CPU, memória e I/O – não é do NADA que o RDBMS vai conseguir fazer isso, e TUDO que vc manda o RDBMS fazer é overhead…. NESTE EXEMPLO, o texto do SQL era simples e curto, então esse OVERHEAD foi marginal, mas NEM SEMPRE é assim, em especial para softwares complexos como ERPs, certamente vc já deve ter visto exemplares Muuuuito mais complexos aí no seu ERP

2. o coitado do RDBMS *** não tem como *** Adivinhar o DATATYPE e nem o TAMANHO da variável que ele vai ter que criar, então ele vai CHUTAR… Isso PODE levar à criação de planos de execução não-ótimos : por exemplo, se o banco tem um índice mantido numa coluna do tipo DATE – mantendo um valor DATE na estritira do índice, portanto – e o texto editado pelo CURSOR_SHARING tá passando uma string na comparação, o RDBMS vai ter que fazer uma CONVERSÃO IMPLÍCITA, convertendo a string para DATE ou a coluna DATE para string …. SE ele optar por converter o DATE para string, o índice NÃO MAIS poderá ser usado, pois o que o índice tem armazenado não são strings….

3. CURSOR_SHARING é um setting TUDO ou NADA, ie : com ele TODAS as strings serão substituídas por variável criadas internamente pelo RDBMS… Isso pode levar a efeitos colaterais como :

a) imagine um SQl tipo : SELECT colunuas, colunas FROM tabela WHERE coluna in (‘AAA’, ‘ABC’, ‘EFG’, ‘XYZ’, etc) onde essa lista de valores possíveis tem digamos 1000 valores – com o CURSOR_SHARING ativo como FORCE, o RDBMS ia ter que criar MIL VARIÁVEIS… Além do overhead disso, nós Sabemos que as variáveis são armazenadas num “cache”, numa área de memória especial chamada PGA : será que a tua PGA tem espaço suficiente para tudo isto ???

b) um dos POUCOS motivos para não se usar BIND VARIABLES ocorre num caso quando vc quer usar as ESTATÌSTICAS do Otimizador Oracle : na operação normal do banco o RDBMS Oracle pode ser instruído a coletar essas ESTATÍSTICAS, que entre outras informações registram o maior e o menor valor existente na coluna analisada e quantas linhas estão gravadas na tabela para cada ocorrência do valor….
Digamos que o desenvolvedor escreveu SELECT WHERE colunacomestatísticas = 45 porque ele ** SABE ** que para esse valor 45 tem poucas linhas, se vc ativar o BINDING automático OBRIGATORIAMENTE vai ser enfiada uma bind variable no lugar do valor, e para BIND VARIABLEs nem sempre possuímos Estatísticas adequadas… Em alguns casos vc pode Corrigir isso com o mecanismo de BIND PEEKING, mas nem sempre…

4. maior chance de BUGs : evidentemente, como esse setting Não é default E reconhecidamente pode dar efeitos colaterais diversos, a Oracle não o testa tão profundament – imho com isso há estatisticamente MAIS CAHNCE de vc encontrar bugs/comportamentos inesperados do RDBMS se vc o Ativar, então, pois vc estará trabalhando com o banco FORA do recomendado e Esperado pela Oracle

5. Possíveis efeitos estéticos : se vc tem um SQL que faz select substr( coluna, 1, 5 ) from tabela;, o RDBMS ** sabe ** que o resultado dessa expressão vai ter 5 caracteres de comprimento, então o programa-cliente pode reservar 5 espaços na tela para o exbir.. Com o binding automático não dá pra saber o tamanho máximo da expressão…
*** ACREDITO *** que é isso, inclusive, a que o teu Fornecedor estava se referindo quando falou de ‘retornar strings maiores do que o esperado’ : provavelmente a linguagem de programação que ele usa na Aplicação lá dele deve CONFIAR nesse tamanho para exibir os dados ou coisa assim…

Esses são mais ou menos os principais ‘problemas’ que vc pode encontrar setando esse parãmetro, que justamente por causa deles Não é default e Não é recomendado pela Oracle, sendo INCLUSIVE já Avisado que na versão 12 ele está Planejado para ser removido/oculto…

===>>> OU SEJA, em resumo : SE vc for Obrigado a fazer essa gambiarra, PEÇA PERMISSÂO pro fornecedor antes e TESTE CUIDADOSAMENTE ANTES DE O USAR pra ver se vc não cai em nenhum dos pontos indicados….

[]s

Chiappa

OBS : como refs pra tudo o que eu disse, além da Documentação vc pode usar https://asktom.oracle.com/pls/apex/asktom.search?tag=cursor-sharing-200205 e https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5180609822543 …

[Autor]

Posts