Analisando SQL traces em Bancos de Dados Oracle - Parte 1

Olá pessoal,

     No artigo de hoje vou explicar o que é um arquivo de SQL trace, como gerá-lo em um Banco de Dados (BD) Oracle, e por fim (ver artigo Analisando SQL traces em Bancos de Dados Oracle - Parte 2 ), como analisar os seus pontos principais. Não entrarei nos mais profundos detalhes sobre o assunto. O objetivo principal deste artigo, é deixar claro (de um modo resumido) que existem N formas de gerar SQL trace, o porquê de eu escolher 1 delas, e como analisar os pontos principais de um trace para analisar o desempenho de uma instrução SQL.

    Antes de começar, é importante entender, que existem outros tipos de trace e que abordaremos aqui apenas o trace de instruções SQL. Para facilitar a compreensão, sempre que eu citar trace de forma indefinida dentro do artigo, estarei me referindo à SQL trace, ou seja, trace de instruções SQL. Analisar um trace é um dos métodos mais utilizados para entender em detalhes como uma instrução SQL foi executada no BD e efetuar Debug! Antes de analisá-lo, porém, é necessário ter alguns conhecimentos básicos, tais como: entender como uma instrução SQL é executada dentro do BD, o que é um arquivo trace e como gerá-lo.

1- Como uma instrução SQL é executada dentro do BD?

     Toda instrução SQL pode ser executada em um BD Oracle em até 3 passos:  

          a) Parse:

            Primeira fase da execução. Nesta fase o Otimizador do Oracle analisa basicamente a sintaxe e a semântica da instrução SQL e monta um plano de execução para ela. 

          b) Execute:

            Segunda fase da execução. Esta é a fase em que o Otimizador realmente executa a instrução SQL, conforme o plano de execução montado no estágio anterior.

          c) Fetch:

            Última fase da execução, única que não é obrigatória. Nesta fase o Otimizador retorna os dados para a sessão de usuário e isso só ocorre em instruções SELECT.

     Toda instrução SQL que é executada em um BD deve ter no mínimo 1 Parse e 1 Execute. O ideal é que ela tenha 1 Parse e N Executes, e isso só ocorre quando a instrução SQL é executada sempre com a mesma string, com variáveis bind ou com o recurso de compartilhamento de cursores habilitado. Por ser muito extenso o assunto, não abordarei mais detalhes neste artigo. O importante agora, é entender o que é Parse, Execute e Fetch, para que fique mais fácil analisar um trace na próxima parte deste artigo. Para aqueles que quiserem se aprofundar no assunto, segue abaixo a Imagem 01, que explico em detalhes nos treinamentos de SQL Tuning.
.

Imagem 01 - Fases da da execução de uma instrução SQL
Fonte: Oracle Corporation

     

2- O que é um arquivo trace?

     Trace ou especificamente SQL Trace, é um método de rastreamento de comandos SQL para verificar o que está sendo executado no BD e pode ser utilizado para corrigir erros (debug) ou otimizar SQL. No Oracle Database, o SQL Trace pode ser habilitado no nível de sessão (identificador do cliente, serviço, módulo ou ação) ou BD. Ao habilitar SQL Trace na sessão de um usuário, todas as instruções SQL que ele submeter ao BD serão registradas em um arquivo, comumente conhecido como arquivo trace. As principais informações que um arquivo trace contém são:

     - Contadores de parse, fetch e execute;
     - Tempo de CPU e o tempo total de execução de instruções SQL;
     - Quantidade de leituras físicas e lógicas;
     - Quantidade de linhas processadas.

     Os arquivos trace possuem um nome composto pelas seguintes partes: nome da instância + "_" + nome do processo de background + "_" + valor numérico + "." + extensão .trc (Ex.: orcl_mmon_27214.trc). Eles podem ser gerados em pastas diferentes, conforme a versão do Oracle Database. Para descobrir facilmente onde eles são gerados, execute no BD (conectado com um usuário que tenha privilégios de administrador), a consulta abaixo:

     SQL> show parameter background_dump_dest

     A pasta retornada no comando acima contém arquivos trace de processos de usuário e background. Trace de processos de background são gerados automaticamente pelo Oracle Database, quando ele encontra algum problema no BD. Para identificar mais facilmente os arquivos traces de usuário, pode-se acrescentar prefixos aos nomes dos arquivos, configurando-se o parâmetro TRACEFILE_IDENTIFIER (mais adiante veremos isso). Outro ponto importante a ser considerado antes de gerar traces, é o tamanho máximo de um arquivo trace, que pode ser configurado através do parâmetro MAX_DUMP_FILE_SIZE (também veremos isso adiante).

3- Como gerar um arquivo trace?

    Atualmente (até a versão 12C do Oracle Database), existem 6 métodos para habilitar SQL Trace no BD:

          1- ORADEBUG:

       É uma ferramenta de debug muito poderosa, que passou a existir na versão 8.1 do Oracle Database. Ela não é muito bem documentada e por questões de segurança, recomenda-se utilizá-la somente quando o suporte da Oracle indicar. Por existirem outros métodos atuais mais seguros que substituem esta ferramenta para gerar sql trace, não irei abordá-la. Para obter mais informações sobre oradebug, consulte o link a seguir: Oradebug.

 

          2- Parâmetro SQL_TRACE:

         Este é o método mais simples de habilitar SQL Trace e pode ser realizado configurando-se o parâmetro SQL_TRACE no nível da instância (ALTER SYSTEM) ou sessão (ALTER SESSION). Por questões de performance do BD (trace consome muitos recursos do BD), jamais habilite este parâmetro no nível da instância em um ambiente de produção. Execute os comandos do roteiro abaixo na sessão de um usuário que você deseja habilitar o trace:

  
       a) Configurando um sufixo no nome do arquivo trace:
     SQL> alter session set tracefile_identifier='hr';

       b) Configurando o tamanho máximo de um arquivo trace, também conhecido como trace dump:
     SQL> alter session set max_dump_file_size=UNLIMITED;

  

Obs.: O parâmetro MAX_DUMP_FILE_SIZE tem por padrão o valor UNLIMITED a partir da versão 8.1.6 do Oracle Database, o que significa que podemos gerar traces com tamanho ilimitado. Quando instruções SQL não estiverem aparecendo em um arquivo trace, verifique se não é o valor deste parâmetro que está limitando o tamanho do arquivo, e por este motivo, suprimindo os SQLs que você está procurando. 

       c) Iniciando a geração de trace simples:
     SQL> alter session set sql_trace=TRUE;
  
     ou:

    c) Iniciando a geração de trace de um trace estendido (que nada mais é do que um trace contendo informações adicionais, que podem incluir Wait Events e Bind Variables):
     SQL> alter session set events '10046 trace name context forever, level 12';

     Depois de habilitar o trace e executar as instruções SQL que você quer investigar, não esqueça de desabilitar o trace. Para desabilitar o trace simples, execute o comando abaixo:
     SQL> alter session set sql_trace=FALSE;
       ou, para desabilitar o trace estendido, execute:
     SQL> alter session set events '10046 trace name context off';

Obs.: Como todo parâmetro configurado em nível de sessão, os valores configurados são descartados ao efetuar logoff.

          3- Package DBMS_SUPPORT

          Esta package foi introduzida provavelmente no Oracle 8.1.7 e não vem instalada por padrão no BD. Para instalá-la, é necessário executar o script $ORACLE_HOME/rdbms/admin/dbmssupp.sql. A package DBMS_SUPPORT permite habilitar SQL Trace somente no nível da sessão (similar ao ALTER SESSION SET SQL_TRACE...), portanto, ela é mais limitada que as opções anteriores (que permitem habilitar trace no nível da instância).

    
     Para habilitar a geração de um trace estendido, execute o comando abaixo:
     SQL> BEGIN DBMS_SUPPORT.START_TRACE(true, true); END;
   
     Para desabilitar a geração de trace estendido, execute o comando abaixo:
     SQL> BEGIN DBMS_SUPPORT.STOP_TRACE; END;

          4- Package DBMS_SESSION

          Esta package foi introduzida provavelmente no Oracle 9i e é mais poderosa que as opções anteriores, pois possui recursos adicionais para habilitar trace em grupos de sessões, permitindo simplificar, por exemplo, a geração de traces na conta de um usuário que possui um pool de conexões. Para gerenciar o trace de múltiplas sessões, é necessário configurar um identificador de cliente único ao iniciar cada sessão (ver DBMS_SESSION.SET_IDENTIFIER).

  

     Para habilitar a geração de um trace, execute o comando abaixo:
     SQL> BEGIN DBMS_SESSION.SET_SQL_TRACE(true); END;
  

     Para configurar o identificar de cliente (que pode ser o nome da conta de um pool de conexões), execute o comando abaixo substituindo o valor da string:

     SQL> BEGIN DBMS_SESSION.SET_IDENTIFIER('FABIO'); END;
   
     Para desabilitar a geração de trace extendido, execute o comando abaixo:
     SQL> BEGIN DBMS_SESSION.SET_SQL_TRACE(false); END;

          5- Package DBMS_SYSTEM
          Esta package foi introduzida provavelmente no Oracle 8i e é similar à DBMS_SESSION. Difere-se apenas pela possibilidade de poder afetar qualquer sessão do BD, e não somente a sessão atual ou aquelas que tiveram um identificador de cliente previamente configuradas.

   

     Para habilitar a geração de trace na sessão de outro usuário, siga o exemplo abaixo substituindo os valores dos parâmetros:

     SQL> BEGIN 

                  DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid => 15, serial# => 2388, true);

                END;

   

     Para desabilitar a geração de trace na sessão do mesmo usuário, execute:

     SQL> BEGIN 

                  DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid => 15, serial# => 2388, false);

                END;

          6- Package DBMS_MONITOR

          A package DBMS_MONITOR foi criada no Oracle Database 10G e é uma evolução da packages DBMS_SYSTEM. Ela contém todas as funcionalidades da package anterior e permite, por exemplo, habilitar trace no nível de serviço, módulo ou ação. Essa é a package atualmente recomendada pela Oracle para gerenciar trace e deve ser utilizada em conjunto com as packages DBMS_SESSION ou DBMS_APPLICATION_INFO para gerenciar traces em diversos níveis (não somente usuários individuais).
    
     Para habilitar a geração de trace na sessão de outro usuário, siga o exemplo abaixo substituindo os valores das variáveis:

     SQL> BEGIN 

                  DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 15, serial_num => 2388, waits => true, binds => true);

                END;
    

        Para desabilitar a geração de trace na sessão do mesmo usuário, execute:

     SQL> BEGIN 

                  DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 15, serial_num => 2388);

                END;

   

      Existe ainda o AUTOTRACE, que pode ser utilizado no SQL Plus ou ferramentas similares, e que permite ver estatísticas da instrução SQL sem gerar arquivo externo, porém com poucas e limitadas informações. É importante entender que em um arquivo de SQL Trace você não encontrará o tempo de execução de blocos PL/SQL, mas somente as instruções SQL executadas dentro destes blocos (ou fora deles). Se você precisa analisar o desempenho de blocos PL/SQL, consulte a package DBMS_TRACE.

     Para descobrir onde trace está habilitado, conecte-se no BD com privilégios administrativos e consulte a visão DBA_ENABLED_TRACES. Para informações mais detalhadas sobre os métodos de geração de trace comentados acima, consulte as referências ao final do artigo.

Por hoje é só!

[]s

 
Referências: 

   - AUTOTRACE in Oracle

   - DBMS_MONITOR

   - DBMS_SESSION
   - Oracle dbms_support

   -Tuning SQL*Plushttp://www.fabioprado.net/p/sql-tuning.html