TRUNCATE TABLE demorando, por quê?

Olá pessoal,

     Ao longo de quase 1 década lecionado treinamentos de SQL Tuning, ouvi algumas vezes alunos dizerem que haviam executado o comando TRUNCATE em uma determinada tabela e que ele estava demorando quase o mesmo tempo que o comando DELETE (sem WHERE, na mesma tabela), mas eu particularmente nunca tinha passado por isso, e por este motivo, desconfiava do que havia ouvido... até o momento em que fui atualizar a VM utilizada nos treinamentos da Oramaster para o Oracle Database 18c, aí começa a história baseada no título deste artigo.

     Em Janeiro de 2018 eu estava fazendo um upgrade em um script que utilizo nos treinamentos de SQL TUNING para demonstrar e comentar as diferenças entre um comando TRUNCATE e um comando DELETE sem WHERE, e percebi que o TRUNCATE na nova tabela (que é muito maior que a tabela do script antigo) estava executando quase com o mesmo tempo do DELETE. Achei muito estranho, testei várias vezes, e vi que o tempo do TRUNCATE não era bom, executando-o de acordo com os passos de um roteiro que eu havia criado. Resolvi então pesquisar no MOS para ver se tinha algum doc explicando o porquê disso estar acontecendo, se era algum bug ou algo parecido, mas para a minha felicidade não era bug, e sim algo normal de acordo com os passos que eu estava executando antes do comando TRUNCATE. Encontrei a resposta no MOSC (My Oracle Support Community) em um tópico com o título "TRUNCATE TABLE hangs", e resolvi compartilhar esse caso aqui no blog:

My Oracle Support Community, tópico "TRUNCATE TABLE hangs"

     É importante salientar que normalmente a execução do comando TRUNCATE é muito rápida, pois ele não "limpa" os blocos de dados da tabela, como faz um DELETE convencional, ele apenas movimenta a marca d´água (high water mark, também conhecida pela sigla HWM) da tabela, fazendo uma espécie de "reset" nela, o que é muito melhor em termos de desempenho do próprio comando, e também para o desempenho das próximas operações de Full Table Scan (FTS) que ocorrerão na tabela, pois ela não terá bloco algum. O DELETE apaga os dados dos blocos, mas ele continuam fazendo parte da tabela, ou seja, a tabela terá blocos vazios, que serão escaneados mesmo sem ter conteúdo, em uma operação de FTS. A única vantagem do comando DELETE é que ele é transacional, permitindo que você desfaça a alteração de forma rápida, caso tenha cometido algum engano!

     Um TRUNCATE normalmente demora alguns centésimos de segundos, enquanto que um DELETE sem WHERE pode demorar horas, se a tabela for grande, com tamanho por exemplo, de algumas centenas de GBs. Mas como (quase) toda regra tem a sua exceção, em determinadas situações um TRUNCATE também pode demorar, e isso ocorrerá, normalmente, se os dados da tabela em que o comando estiver sendo executado estiverem na Buffer Cache (BC). Nestes casos há um tempo de execução maior, que não envolve apenas a movimentação da marca d´água, mas também o tempo para limpar os blocos de dados dessa tabela na BC (e algumas coisinhas a mais que serão informadas mais adiante).

     Segue abaixo um script que você pode usar para comparar o tempo de um TRUNCATE (em situações normais) e o tempo de um DELETE sem WHERE na mesma tabela:

Pré-requisitos:
Ter instalado em algum banco de dados Oracle qualquer o schema SOE, utilizado no Swingbench.

Passo 1: Criando a tabela de testes (CUSTOMERS2) com 5.555.554 linhas e tamanho de 712 MB:
CREATE TABLE SOE.CUSTOMERS2 AS
SELECT * FROM SOE.CUSTOMERS WHERE ROWNUM < 5555555;

Passo 2: Executando o TRUNCATE:

TRUNCATE TABLE SOE.CUSTOMERS2;

Imagem 01 - TRUNCATE sem dados da tabela na BC

Veja na imagem 01 que o tempo de execução do TRUNCATE foi de apenas 0.094s.

Passo 3: Recriando a tabela para ela conter dados novamente:

DROP TABLE SOE.CUSTOMERS2;

CREATE TABLE SOE.CUSTOMERS2 AS

SELECT * FROM SOE.CUSTOMERS WHERE ROWNUM < 5555555;

         
Passo 4: Executando o comando DELETE:
DELETE FROM SOE.CUSTOMERS2;

Imagem 02 - Tempo do comando DELETE sem WHERE

Veja na imagem 02 que o tempo de execução do DELETE foi de 21.406s, 

um tempo muito superior ao do TRUNCATE, que teve uma execução quase que instantânea.

     Agora executaremos outro script em que o comando DELETE será executado antes do TRUNCATE e executaremos um ROLLBACK depois do DELETE, para que os dados apagados voltem para a tabela, de modo que a tabela tenha dados novamente para que o TRUNCATE efetivamente tenha o que apagar. 

Passo 1: Recriando a tabela de testes (CUSTOMERS2) com 5.555.554 linhas e tamanho de 712 MB:

DROP TABLE SOE.CUSTOMERS2;

CREATE TABLE SOE.CUSTOMERS2 AS

SELECT * FROM SOE.CUSTOMERS WHERE ROWNUM < 5555555;

Passo 2: Executando o comando DELETE:
DELETE FROM SOE.CUSTOMERS2;

O tempo médio de execução desse DELETE não muda em relação à execução anterior, ele continuará sendo executando em um tempo similar ao da Imagem 02.

Passo 3: Efetuando o comando ROLLBACK para que os dados retornem à tabela:

ROLLBACK;

Passo 4: Executando o TRUNCATE:

TRUNCATE TABLE SOE.CUSTOMERS2;

Imagem 03 - Tempo do TRUNCATE após DELETE + ROLLBACK

Veja na imagem 03 que desta vez o tempo de execução do TRUNCATE foi de 4.2s, ou seja, aproximadamente 5 X menor que o DELETE, mas aproximadamente 45 X maior que o tempo do TRUNCATE da primeira execução.

    É importante comentar que ao executar o TRUNCATE TABLE quando há dados da tabela na BC, normalmente ocorrerá o evento de espera (Wait Event) "enq: RO - fast object reuse", conforme pode ser observado na "Imagem 04" abaixo:

Imagem 04 - Wait Event "enq: RO - fast object reuse" ocorrendo ao 

     Mais detalhes sobre este evento de espera e sua causa podem ser consultados no doc do MOS com o título "Resolving Issues Where 'enq: RO - fast object reuse' Contention Seen During Drop or Truncate Operations (Doc ID 1475659.1)". Para aqueles que desejam imediatamente obter um pouco mais de informações sobre ele, seguem abaixo alguns trechos deste documento:

The RO enqueue known as "Multiple object resue" enqueue, is used to synchronise operations between foreground process and a background process such as DBWR or CKPT. It is typically used when dropping objects or truncating tables.

Following is the sequence of events When a truncate/drop occurs:

1. Foreground process acquires the "RO" enqueue in exclusive mode
2. Cross instance calls (or one call if it is a single object) are issued ("CI" enqueue is acquired)
3. CKPT processes on each of instances requests the DBWR to write the dirty buffers to the disk and invalidate all the clean buffers.
4. After DBWR completes writing all blocks, the foreground process releases the RO enqueue.

The time waited is also influenced by the time taken to scan the buffer cache for dirty blocks to be written to disk and flushed from cache. The larger the buffer cache, the longer it will take to find these blocks. If buffer cache is extremely large,decreasing its size has seen to be affective in improving performance.

    Agora vamos à conclusão final: Por que o TRUNCATE demorou mais na última execução? Porque o DELETE e ROLLBACK que foram executados previamente carregaram os dados da tabela na BC, e isso acarretou em executar uma série de operações que estão bem descritas nos trechos acima extraídos do MOS. Se você quiser fazer mais testes e não ver essa lentidão, inclua no script a execução do comando "alter system flush buffer_cache" antes do TRUNCATE e verá que a execução dele será sempre muito rápida quando não há dados da tabela em memória (na BC). ATENÇÃO: Execute o Flush na BC apenas em ambientes de testes, e jamais em ambientes de produção! 

Por hoje é só!

Espero que você tenha gostado e aprendido algo!

[]s