Porque uma instrução INSERT está lenta? Entendendo esperas por ‘db file sequential read’ 

Há algumas semanas um cliente ligou reclamando sobre lentidão durante uma carga de dados numa tabela.

O cenário era bastante simples:

• INSERT de milhares de registros;
• Direct-Path INSERT, usando o hint APPEND_VALUES;
• Nenhuma subquery envolvida no INSERT.

Eles estavam carregando a tabela usando uma instrução INSERT para cada agência (agency_id), com milhares de registros para cada agência. Eles reclamaram que para cada agência estava levando mais de 20 minutos para carregar os dados.

Como primeira coisa, eu verifiquei as sessões de espera no DB e observei que o usuário estava aguardando pelo evento ‘db file sequential read’ para as instruções INSERT (omiti algumas informações por questões de segurança):

A query acima é resultado do join das views de dicionário de dados GV$INSTANCE, GV$SESSION e GV$SESSION_WAIT. Você pode fazer download dessa query aqui: waiting_sessions.sql

Como vocês podem ver, nós temos a instrução INSERT aguardando por ‘db file sequential read’. A coluna LAST_CALL_ET mostra que o INSERT estava rodando por mais de 3 minutos.

A instrução INSERT:

INSERT /*+APPEND_VALUES*/ INTO USER_LOAD.TABLE_1 VALUES (COL_01,COL_02,COL_03,COL_04,COL_05,COL_06,COL_07,COL_08,COL_09,COL_10,COL_11,COL_12,COL_13,COL_14,COL_15,COL_16,COL_17,COL_18,COL_19,COL_20) VALUES (:1 ,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ,:13 ,:14 ,:15 ,:16 ,:17 ,:18 ,:19 ,:20 )

O interessante aqui é a espera por ‘db file sequential read’ para uma instrução INSERT sem nenhuma subquery envolvida.

Esse evento significa que o Oracle está lendo os blocos sequencialmente, geralmente como uma leitura de bloco único. Esse evento de espera é frequentemente associado com leituras de índice, mas não limitado a elas.

Felizmente, há uma forma de identificar quais objetos estão associados com esse evento.

O evento ‘db file sequential read’ usa alguns parâmetros para identificar onde a espera está ocorrendo. Na view GV$SESSION_WAIT nós temos as colunas abaixo. Cada coluna corresponde a uma estrutura específica no Oracle:

• P1: file#;
• P2: block#.

Nós também temos a coluna P3, que mostra quantos blocos foram lidos à partir do bloco inicial identificado pela coluna P2 e pelo datafile inicial identificado na coluna P1.

Uma breve explicação para cada coluna:

file#	block#
Esse é o file_id (número do datafile) que o Oracle está tentando ler. À partir do Oracle 8 em diante, esse é o número de arquivo absoluto (AFN).	Esse é o número do bloco onde foi iniciada a leitura no datafile citado na coluna P1 quando o Oracle começa a ler os blocos. Tipicamente somente um bloco é lido nesse momento.

Então, nós temos os valores para P1 e P2 na query acima exibida:

• P1: 1949;
• P2: 3462037.

Há outra query que podemos usar o file# e o block# para identificar o segmento associado com esses valores:

Perfeito, nós conseguimos identificar que isso é um ÍNDICE. Antes de discutirmos sobre isso, vamos explorar um pouco mais outras opções para chegar à mesma conclusão. 🙂

Nós sabemos que o SID é 1114. Há uma forma de ativar o trace para essa sessão usando o ORADEBUG. Vamos fazer isso. Abaixo, há algumas queries e comandos que nos ajudarão a realizarmos o “attach” à sessão, ativar o trace e também identificar o arquivo de trace:

Sumarizando o que nós fizemos com os comandos acima:

• Eu gerei um comando para realizar o “attach” à sessão;
• Copei e colei o comando para realizar o attach à sessão;
• Identifiquei o arquivo de trace;
• Ativei o SQL trace para essa sessão.

Agora que o trace foi ativado e o INSERT está rodando, vamos verificar o que está acontecendo, eu simplesmente executei esse comando:

Aqua está o output do comando:

Como podemos ver claramente, nós temos diversas esperas por ‘db file sequential read’. OK, você pode ver algumas informações no trace. Nós temos uma coluna chamada obj#, que é o OBJECT_ID do objeto envolvido na espera. Portanto, é fácil checar qual o objeto associado com a espera 😉

Viu? Nós conseguimos identificar o mesmo objeto!

OK, ótimo, agora vamos entender porque uma instrução INSERT está lenta e qual o papel de ‘db file sequential read’ nisso.

Primeiro, vamos entender o que é um ÍNDICE:

Índices são estruturas opcionais associadas com tabelas que permitem que consultas SQL (queries) sejam executadas mais rapidamente para as tabelas em questão.

Assim como um índice num livro ajuda você a localizar as informações mais rápido do que se não tivesse índice, um índice no Banco de Dados Oracle oferece um acesso rápido aos dados da tabela. Você pode usar índices sem ter que reescrever as queries. Seus resultados serão os mesmos, porém, você os terá mais rapidamente.

O Banco de Dados Oracle oferece vários esquemas de indexação que proporcionam funcionalidades complementares de desempenho.

Você pode ler mais sobre indexação no Oracle aqui: Database Administrator’s Guide – 21 Managing Indexes – About Indexes

Na mesma documentação nós temos isso:

“O banco de dados mantém automaticamente os índices quando você insere, atualiza e exclui linhas da tabela associada. Se você remover um índice, todos as aplicações continuarão a funcionar. No entanto, o acesso aos dados anteriormente indexados pode ser mais lento.”

Interessante, não?

Nós também temos uma outra parte interessante nesse documento: Database Performance Tuning Guide – 2 Designing and Developing for Performance – Finding the Cost of an Index

“Criar e manter uma estrutura de índice pode ser custoso e consumir recursos como espaço em disco, CPU e capacidade de I/O. Os designers devem garantir que os benefícios de qualquer índice superem os aspectos negativos da manutenção do índice.

Use este guia de estimativa simples para o custo da manutenção de índices: cada índice mantido por uma operação de INSERT, DELETE ou UPDATE nas chaves indexadas requer cerca de três vezes mais recursos do que a operação DML real na tabela. Assim, se você fizer um INSERT em uma tabela com três índices, a inserção será aproximadamente 10 vezes mais lenta do que um INSERT em uma tabela sem índices. Para operações DML, e particularmente para aplicativos com muitas inserções (INSERT), o design do índice deve ser seriamente revisado, o que pode exigir um compromisso entre o desempenho da consulta e o desempenho da inserção (INSERT).”

Portanto, vamos perguntar novamente:

Porque uma instrução INSERT está lenta e qual o papel de ‘db file sequential read’ nisso?

Não importa se nós só estamos rodando o INSERT na tabela. A estrutura de índice precisa ser mantida. Portanto, a espera por ‘db file sequential read’ durante um INSERT acontece porque está ocorrendo a manutenção do índice.\

A única forma de evitar esperas por ‘db file sequential read’ durante o INSERT é dropando os índices ou tornando-os inutilizáveis (unusable). Isso é algo que você precisa decidir junto com o arquiteto da aplicação e com o time envolvido no negócio, porque se você dropar ou tornar os índices unusable, você precisa considerar o tempo para realizar o rebuild dos mesmos.

Tenha em mente que se um índice estiver unusable, a query que previamente foi otimizada devido ao índice, agora pode rodar mais devagar realizando uma operação de full table scan. Não apenas isso, se o parâmetro SKIP_UNUSABLE_INDEXES estiver definido como FALSE, suas instruções DML (INSERT, UPDATE or DELETE) ou até mesmo o seu SELECT falharão com o erro: ORA-01502: index ‘schema.index_name’ or partition of such index is in unusable state.

Para esse cliente nessa situação específica, a carga iria acontecer apenas uma vez e eles estavam sendo negativamente impactados pela manutenção dos índices durante o INSERT. Nós decidimos tornar os índices unusable. Isso permitiu que o tempo gasto no INSERT fosse reduzido de 23 minutos para 50 segundos. Claro, quando todos os dados foram carregados na tabela e ANTES de liberarmos o banco de dados para a aplicação, nós realizamos o rebuild dos índices.

Além disso, algumas soluções de Data Warehouse usam exatamente essa abordagem: dropar o índice, carregar os dados, criar o índice – isso é feito para otimizar as cargas de dados. Mas novamente, isso precisa ser bem discutido com todos os envolvidos no negócio para evitar qualquer problema e ruído.

Abaixo há um test case bem simples onde podemos ver o comportamento com a instrução INSERT, ANTES e DEPOIS de criarmos os índices na tabela.

Primeiro, vamos criar a tabela:

Agora, vamos realizar o INSERT para um milhão de registros:

DECLARE
   v_counter NUMBER := 1;
BEGIN
   WHILE v_counter <= 1000000 LOOP
      INSERT INTO /*+APPEND_VALUES */ example_table (id, name, age, email)
      VALUES (v_counter, 'Name'||v_counter, TRUNC(DBMS_RANDOM.VALUE(18, 80)), 'email'||v_counter||'@example.com');
      v_counter := v_counter + 1; 
      IF MOD(v_counter, 1000) = 0 THEN 
         COMMIT; 
      END IF;
   END LOOP;
   COMMIT;
END;
/

Durante a execução do INSERT, execute a query waiting_sessions.sql em outra sessão:

Ótimo, não há espera por ‘db file sequential read’.

Agora, vamos criar alguns índices na tabela:

Agora, vamos executar o INSERT novamente:

DECLARE
   v_counter NUMBER := 1;
BEGIN
   WHILE v_counter <= 1000000 LOOP
      INSERT INTO /*+APPEND_VALUES */ example_table (id, name, age, email)
      VALUES (v_counter, 'Name'||v_counter, TRUNC(DBMS_RANDOM.VALUE(18, 80)), 'email'||v_counter||'@example.com');
      v_counter := v_counter + 1; 
      IF MOD(v_counter, 1000) = 0 THEN 
         COMMIT; 
      END IF;
   END LOOP;
   COMMIT;
END;
/

Durante a execução do INSERT, execute a query waiting_sessions.sql em outra sessão:

Ótimo! Agora conseguimos reproduzir o problema! Nós também temos os valores para P1 e P2, vamos verificar:

Essa é a query que nós podemos usar o file# e o block# acima listados para identificar o segmento associado com os valores obtidos anteriormente:

Como podemos ver, o segmento onde tivemos espera por ‘db file sequential read’ é o índice que criamos anteriormente:

Eu não quero soar repetitivo, mas, vamos fechar o blog post.

Fatos e Conclusão:

• INSERT de milhares de registros:
• Há alguns índices na tabela;
• Sessão esperando por ‘db file sequential read’;
• A espera está associada com a manutenção de índice durante o INSERT;
• Você precisa checar com cuidado se você dropará o índice (ou tornará ele unusable);
• Se você dropar o índice (ou torná-lo unusable), o INSERT será mais rápido, já que não haverá esperas por ‘db file sequential read’;
• Se o parâmetro SKIP_UNUSABLE_INDEXES estiver definido como TRUE, as consultas que usavam o índice anteriormente agora provavelmente realizarão um full table scan, causando um impacto de performance na execução da query;
• Se o parâmetro SKIP_UNUSABLE_INDEXES estiver definido como FALSE, você provavelmente encontrará o erro ORA-01502: index ‘schema.index_name’ or partition of such index is in unusable state.
• Se você dropar (ou tornar o índice unusable), relembre de criar ou realizar o rebuild uma vez que a carga de dados seja concluída e se o índice for necessário para melhorar a performance das suas queries.

Espero que seja útil!

Um abraço!

Vinicius