Após a migração de um banco de dados Oracle da versão 11.2.0.4 para a versão 12.1.0.2, o processo de truncate table passou a ficar extremamente lento.
Com a nova versão do banco de dados, o comportamento do comando truncate parece ter mudado, passando a ter uma característica mais de DML.
Não sabemos (ainda) o porque dessa mudança, mas deve estar relacionada com as novas funcionalidades do produto.
Em nosso ambiente, após a migração para 12c, alguns comandos truncate estavam demorando cerca de 3 horas para serem finalizados, sendo que os mesmos na versão anterior do banco de dados eram executados em segundos.
Após pesquisas em sites, documentação da Oracle e também de um chamado aberto no MOS(My Oracle Support), obtivemos 2 soluções para o problema de lentidão encontrado durante a execução do truncate, conforme descrito abaixo:
1) Aplicar o patch 20171986
Conforme chamado aberto junto ao fornecedor (MOS), obtivemos a orientação de aplicação do patch 20171986.
É um patch pequeno e de fácil aplicação, só devemos seguir os pré-requisitos informados no arquivo readme.txt que acompanha o patch.
2) Configurar o parâmetro oculto "_dml_monitoring_enabled"
Apesar de não ser ("muito") recomendado pela Oracle a configuração de parâmetros ocultos, pois em uma migração de versão do produto esses parâmetros deverão ser removidos, é uma alternativa de solução imediata, pois não requer que o banco de dados seja reiniciado imediatamente, pois podemos configurar o parâmetro para utilização em memoria, conforme descrito no exemplo abaixo:
$ sqlplus / as sysdba
SQL> alter system set "_dml_monitoring_enabled"=FALSE scope=memory;
Obs.: Após a validação/confirmação do funcionamento do comando truncate de forma adequada com a configuração do parâmetro oculto, em um momento oportuno, podemos adicionar o parâmetro no SPFILE e reiniciar o banco de dados.
Referências:
http://blog.ora-600.pl/2015/01/27/slow-truncate-table-due-to-operations-on-mon_mods/
https://support.oracle.com