MERGE na SQLOBJ$AUXDATA com FULL TABLE SCAN e outros problemas - Oracle Database 11g

A versão Oracle Database 11gR2 realmente é uma versão repleta de recursos e melhorias que nos ajudam muito, mas muito mesmo. Algumas coisas mudaram, como o novo SPM (SQL Plan Management) que passa a gerenciar melhor a questão de planos trabalhando junto com a sua base SMB (SQL Management Base).
Quando um comando SQL é "hard parsed", o CBO (Cost Based Optimizer) produz vários planos de execução e seleciona um com o custo mais baixo. Se uma baseline de plano de SQL estiver presente, o otimizador compara apenas o plano recém-produzido com os planos já existentes na baseline. Se um plano adequado é encontrado ele é marcado como ACEITO (ACCEPTED) e o plano é utilizado. Se a baseline não tiver um plano aceito,  o otimizador avalia os planos aceitos na baseline e usa o que tiver menor custo. Se o plano de execução originalmente produzido pelo otimizador teve um custo menor que o presente na baseline, ele é adicionado a baseline como um plano NÃO-ACEITO (NON-ACCEPTED), então não é utilizado até ser verificado para não degradar o desempenho. Se uma mudança no sistema afeta todos os planos existentes aceitos, então eles são considerados como não-reproduzíveis (NON-REPRODUCIBLE), neste caso o otimizador usará o plano com o custo mais baixo.
É uma estratégia chamada de "Conservative Plan Selection Strategy". Como o otimizador preferencialmente usa um plano de execução executado e testado, mesmo que um novo plano pareça ter um desempenho melhor, somente quando o novo plano for provado que executará bem será aceito para uso.
Por padrão o parâmetro "OPTIMIZER_USE_SQL_PLAN_BASELINES" vem habilitado que faz com que o Oracle utilize baselines dos planos SQL armazenados na SMB.


Nem tudo são flores, pois justamente com este recurso tivemos problemas em uma das base de dados que hoje administro. O parâmetro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES também foi habilitado (FALSE por padrão) para que o oracle verificasse quando deve capturar baselines de plano SQL automaticamente para tentar melhorar o desempenho, pois até então o desempenho estava muito abaixo do esperado, com alto processamento, etc. Queríamos que o banco de dados capturasse as baselines e tentasse reutilizá-las quando possível. A alteração deste parâmetro para TRUE também não adiantou, não houve melhoria significativa, pois a aplicação utilizava poucas variáveis BIND no código.

Pelo gráfico do EM o desempenho estava de fato muito ruim, com altas taxas de espera relacionados a CPU então geramos um AWR para saber quais eram os TOP SQL.
Eis que surge:

MERGE INTO sqlobj$auxdata USING dual ON (:1 IS NULL) WHEN MATCHED THEN UPDATE SET description = :2, creator = nvl(:3, creator), origin = :4, version = :5, created = :6, last_modified = :7, last_verified = nvl(:8, last_verified), parse_cpu_time = null, optimizer_cost = nvl(:9, optimizer_cost), module = nvl(:10, module), action = nvl(:11, action), priority = nvl(:12, priority), optimizer_env = nvl(:13, optimizer_env), bind_data = nvl(:14, bind_data), parsing_schema_name = nvl(:15, parsing_schema_name), executions = nvl(:16, executions), elapsed_time = nvl(:17, elapsed_time), cpu_time = nvl(:18, cpu_time), buffer_gets = nvl(:19, buffer_gets), disk_reads = nvl(:20, disk_reads), direct_writes = nvl(:21, direct_writes), rows_processed = nvl(:22, rows_processed), fetches = nvl(:23, fetches), end_of_fetch_count = nvl(:24, end_of_fetch_count), task_id = nvl(:25, task_id), task_exec_name = nvl(:26, task_exec_name), task_obj_id = nvl(:27, task_obj_id), task_fnd_id = nvl(:28, task_fnd_id), task_rec_id = nvl(:29, task_rec_id), flags = 0, spare1 = null, spare2 = null WHERE signature = :30 AND category = :31 AND obj_type = :32 AND plan_id = :33 WHEN NOT MATCHED THEN INSERT (signature, category, obj_type, plan_id, description, creator, origin, version, created, last_modified, last_verified, parse_cpu_time, optimizer_cost, module, action, priority, optimizer_env, bind_data, parsing_schema_name, executions, elapsed_time, cpu_time, buffer_gets, disk_reads, direct_writes, rows_processed, fetches, end_of_fetch_count, task_id, task_exec_name, task_obj_id, task_fnd_id, task_rec_id, flags, spare1, spare2) VALUES (:34, :35, :36, :37, :38, :39, :40, :41, :42, :43, null, null, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54, :55, :56, :57, :58, :59, :60, :61, :62, :63, :64, 0, null, null)

Executando FULL TABLE SCAN na SQLOBJ$AUXDATA. Justamente o comportamento do SPM com aquele parâmetro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES habilitado. No suporte Oracle dizia que era necessário aplicar um patch, pois se tratava de um BUG (BUG 11719151). Na descrição do bug, dizia que o BUG poderia ocorrer quando quando o parâmetro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES estivesse definido para TRUE. Definimos para FALSE, mas mesmo assim nada mudou. Abrimos então um chamado com a Oracle para saber de fato o que fazer já que, teoricamente, o BUG só ocorria quando o OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES estava FALSE como também o desempenho já estava ruim antes mesmo de alterarmos o parâmetro.
Depois de algumas análises recomendaram o PATCH 11719151 de fato para corrigir o problema. O Patch poderia ser aplicado ONLINE e assim fizemos usando um comando similar a esse (para RAC com dois nós):

opatch apply online -connectString SID_Node1:Username_Node1:Password_Node1:Node1_Name,SID_Node2:Username_Node2:Password_Node2:Node2_Name

Nada sai do ar. Apesar do ambiente ser cluster, não foi necessário desligar instância alguma, os binários são alterados online, etc. Há uma ocupação extra de memória, mas tudo ocorreu bem para o primeiro banco de dados.

Após a aplicação do patch no primeiro banco de dados, o com problema, o consumo de desempenho simplesmente mudou completamente. Dá pra perceber no gráfico:

Ficou uma beleza, tudo ocorreu bem na aplicação do patch ATÉ AGORA.O patch altera os binários e acessa o banco de dados para realizar algumas alterações. Provavelmente isso poderia acontecer com as demais bases, pois utilizavam o mesmo recurso (11g) então teríamos que continuar aplicando nas demais bases de dados usando os comandos:

opatch util enableonlinepatch -connectString SID_Node1:Username_Node1:Password_Node1:Node1_Name,SID_Node2:Username_Node2:Password_Node2:Node2_Name -id 11719151 # Banco 2

opatch util enableonlinepatch -connectString SID_Node1:Username_Node1:Password_Node1:Node1_Name,SID_Node2:Username_Node2:Password_Node2:Node2_Name -id 11719151 # Banco 3

opatch util enableonlinepatch -connectString SID_Node1:Username_Node1:Password_Node1:Node1_Name,SID_Node2:Username_Node2:Password_Node2:Node2_Name -id 11719151 # Banco 4 * PAU

Quando chegamos no quarto banco de dados (de 8) outro problema aconteceu. O banco de dados passou a consumir 100% de CPU constantemente, só resolvia se abortássemos o banco, pois nada era possível fazer na máquina por conta do alto processamento. O ABORT foi sofrido, mas conseguimos. Toda vez que a instância subia, 100% CPU. Informamos ao suporte Oracle, mas eles pediram diagnósticos de AWR desta base, etc, mas era impossível, pois CPU 100% direto. Ainda bem que esta base não estava em produção, decidimos então recriá-la e "resolveu" o problema.
Para as demais bases, deu tudo certo na aplicação do patch.

Na primeira base, que estava com problemas de desempenho, tudo estava normal, porém foi percebido um comportamento estranho. Toda e qualquer consulta que já havia sido executada, ou seja, que já existia na SMB, estava dando ORA-01008. Muitas consultas na aplicação já haviam sido realizadas, então o sistema estava praticamente inoperante.
Em contato com o suporte Oracle, informamos os parâmetros em questão, inclusive o OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES já estava FALSE, ele pediu que o parâmetro OPTIMIZER_USE_SQL_PLAN_BASELINES fosse desabilitado também desabilitando assim o recurso de SMB. Neste caso o erro ORA-01008 desapareceu, portanto isso foi uma ação de contorno e então solicitamos que este problema fosse analisado pelo suporte Oracle.

Eles abriram então o BUG 11887892 e estão desenvolvendo uma solução.

Por enquanto está tudo bem e estável, porém sem este recurso do 11g por enquanto. :(

Agradecimentos ao Suporte Oracle que se demonstrou rápido e objetivo em todas as nossas solicitações.

UPDATE 25/04/2011:
O suporte Oracle nos pediu que o patch aplicado ONLINE fosse removido e reaplicado OFFLINE, pois, segundo eles, houve uma falha no desenvolvimento do patch (faltou a inclusão de um módulo) que ocasiona o problema apenas quando é aplicado ONLINE.
Tivemos então que remover o patch ONLINE, baixar tudo que estava no HOME e reaplicar no modo OFFLINE.
Depois disso tudo ficou mais bonito e os problemas não aconteceram mais. Pude voltar o parâmetro OPTIMIZER_USE_SQL_PLAN_BASELINES para TRUE.
Após o acontecido, a Oracle modificou a documentação do patch removendo a opção de aplicação ONLINE do patch.
Até a próxima!