Usar Rastreamento SQL no Autonomous Database no Exadata Infrastructure Dedicado

Você pode usar o Rastreamento de SQL com o Autonomous Database on Dedicated Exadata Infrastructure para ajudar a identificar a origem de uma carga de trabalho excessiva do banco de dados, como uma instrução SQL de carga alta em seu aplicativo.

Sobre o Rastreamento SQL

Quando uma operação de aplicativo leva mais tempo do que o esperado, obter um rastreamento de todas as instruções SQL executadas como parte dessa operação com detalhes como tempo gasto por essa instrução SQL nas fases de parsing, execução e extração ajudará você a identificar e resolver a causa do problema de desempenho. Você pode usar o rastreamento de SQL em um Autonomous Database para fazer isso.

O rastreamento de SQL é desativado por padrão no Autonomous Database. Ative-o para começar a coletar os dados de rastreamento SQL. Para rastrear instruções SQL, implemente as seguintes tarefas como usuário ADMIN:

Configurar Rastreamento de SQL no Autonomous Database

Para configurar o seu Autonomous Database para rastreamento de SQL, faça o seguinte:
  1. Crie um bucket para armazenar arquivos de rastreamento no Cloud Object Storage.
    Para salvar os arquivos de rastreamento de SQL, o bucket poderá estar em qualquer Armazenamento de Objetos na Nuvem que o Autonomous Database suportar. Por exemplo, para criar um bucket no Oracle Cloud Infrastructure Object Storage, consulte Criando um Bucket.

    Dica:

    Certifique-se de escolher Padrão como camada de armazenamento ao criar seu bucket no Oracle Cloud Infrastructure Object Storage porque os arquivos de rastreamento de SQL só são suportados com buckets criados na camada de armazenamento padrão. Para obter informações sobre a Camada Padrão de Armazenamento de Objetos, consulte Noções Básicas de Camadas de Armazenamento.
  2. Crie uma credencial para a sua conta do Cloud Object Storage usando DBMS_CLOUD.CREATE_CREDENTIAL.
    Por exemplo:
    BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username => 'adb_user@example.com', 
    password => 'password'
);END;
/

    Consulte Procedimento CREATE_CREDENTIAL para obter detalhes sobre os argumentos dos parâmetros username e password para diferentes serviços de armazenamento de objetos.

  3. Defina parâmetros de inicialização para especificar o URL do Cloud Object Storage de um bucket para arquivos de rastreamento de SQL e para especificar as credenciais de acesso ao Cloud Object Storage.
    1. Defina a propriedade de banco de dados DEFAULT_LOGGING_BUCKET para especificar o bucket de registro em log no Cloud Object Storage.
      Por exemplo, se você criar o bucket com o Oracle Cloud Infrastructure (OCI) Object Storage:
      SET DEFINE OFF;
ALTER DATABASE PROPERTY SET 
   DEFAULT_LOGGING_BUCKET = 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/bucket_name/o/';

      Em que namespace-string é o namespace do OCI Object Storage e bucket_name é o nome do bucket que você criou anteriormente. Consulte Noções Básicas de Namespaces do serviço Object Storage para obter mais informações.

      Consulte Regiões e Domínios de Disponibilidade para obter uma lista de regiões.

      O Cloud Object Store que você usa para arquivos de Rastreamento de SQL pode ser qualquer um suportado pelo Autonomous Database.

    2. Defina a propriedade do banco de dados DEFAULT_CREDENTIAL como a credencial criada na Etapa 2.
      Por exemplo:
      ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.DEF_CRED_NAME';

      A inclusão do nome do esquema com a credencial é obrigatória. Neste exemplo, o esquema é ADMIN.

Ativar o Rastreamento de SQL no Autonomous Database

Observação:

A ativação do rastreamento de SQL pode prejudicar o desempenho do aplicativo para a sessão enquanto a coleta de rastreamento está ativada. Esse impacto no desempenho é esperado por causa da sobrecarga de coletar e salvar dados de rastreamento.

Para ativar o rastreamento de SQL para uma sessão de banco de dados, faça o seguinte:

  1. Opcionalmente, defina um identificador de cliente para o aplicativo. Esta etapa é opcional, mas recomendada. O rastreamento de SQL usa o identificador de cliente como componente do nome do arquivo de rastreamento quando esse arquivo é gravado no Armazenamento de Objetos na Nuvem.
    Por exemplo:
    BEGIN
  DBMS_SESSION.SET_IDENTIFIER('sqlt_test');
END;
/
  2. Opcionalmente, defina um nome de módulo para o aplicativo. Esta etapa é opcional, mas recomendada. O rastreamento de SQL usa o nome do módulo como componente do nome do arquivo de rastreamento quando esse arquivo é gravado no Armazenamento de Objetos na Nuvem.

    Por exemplo:

    BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('modname', null);
END;
/
  3. Ative o recurso Rastreamento SQL.
    ALTER SESSION SET SQL_TRACE = TRUE;
  4. Execute sua carga de trabalho.
    Esta etapa envolve a execução de todo o aplicativo ou de partes específicas dele. Enquanto você executa sua carga de trabalho na sessão do banco de dados, os dados de rastreamento de SQL são coletados.
  5. Desativar Rastreamento SQL.
    Quando você desativa o rastreamento de SQL, os dados coletados para a sessão são gravados em uma tabela na sua sessão e em um arquivo de rastreamento no bucket configurado quando você configura o rastreamento de SQL.

Desativar Rastreamento SQL

Para desativar o rastreamento de SQL, faça o seguinte:
  1. Desative o recurso Rastreamento SQL.
    ALTER SESSION SET SQL_TRACE = FALSE;
  2. Opcionalmente, conforme necessário para seu ambiente, talvez você queira redefinir a propriedade de banco de dados DEFAULT_LOGGING_BUCKET para limpar o valor do bucket de registro em log no Cloud Object Storage.
    Por exemplo:
    ALTER DATABASE PROPERTY SET DEFAULT_LOGGING_BUCKET = '';
Quando você desativa o rastreamento de SQL, os dados de rastreamento coletados enquanto a sessão é executada com o rastreamento ativado são copiados para uma tabela e enviados para um arquivo de rastreamento no Armazenamento de Objetos na Nuvem.

Exibir Arquivo de Rastreamento Salvo no Armazenamento de Objetos na Nuvem no Autonomous Database

Use dados do arquivo de rastreamento de SQL para analisar o desempenho do aplicativo no Autonomous Database. Quando você desativa o rastreamento de SQL em sua sessão de banco de dados, os dados são gravados no bucket do Armazenamento de Objetos na Nuvem configurado com DEFAULT_LOGGING_BUCKET.

O recurso de Rastreamento de SQL grava os dados de rastreamento coletados na sessão no Armazenamento de Objetos na Nuvem no seguinte formato:

default_logging_bucket/sqltrace/clientID/moduleName/sqltrace_numID1_numID2.trc

Os componentes do nome do arquivo são:

  • default_logging_bucket: é o valor da propriedade de banco de dados DEFAULT_LOGGING_BUCKET. Consulte Configurar Rastreamento de SQL no Autonomous Database para obter mais informações.

  • clientID: é o identificador de cliente. Consulte Ativar Rastreamento de SQL no Autonomous Database para obter mais informações.

  • moduleName: é o nome do módulo. Consulte Ativar Rastreamento de SQL no Autonomous Database para obter mais informações.

  • numID1_numID2: são dois identificadores fornecidos pelo recurso de Rastreamento de SQL. Os valores numéricos numID1 e numID2 distinguem exclusivamente cada nome de arquivo de rastreamento de outras sessões usando o rastreamento e criando arquivos de rastreamento no mesmo bucket do Cloud Object Storage.

    Quando o serviço de banco de dados suporta paralelismo e uma sessão executa uma consulta paralela, o recurso de Rastreamento de SQL pode produzir vários arquivos de rastreamento com diferentes valores numID1 e numID2.

Observação:

Quando o rastreamento de SQL é ativado e desativado várias vezes dentro da mesma sessão, cada iteração de rastreamento gera um arquivo de rastreamento separado no Armazenamento de Objetos na Nuvem. Para evitar a substituição de rastreamentos anteriores gerados na sessão, os arquivos gerados subsequentemente seguem a mesma convenção de nomenclatura e adicionam um sufixo numérico ao nome do arquivo de rastreamento. Esse sufixo numérico começa com o número 1 e é incrementado em 1 para cada iteração de rastreamento depois disso.

Por exemplo, veja a seguinte amostra de nome de arquivo de rastreamento gerado quando você define o identificador de cliente como "sql_test" e o nome do módulo como "modname":

sqltrace/sqlt_test/modname/sqltrace_5415_56432.trc

Você pode executar TKPROF para converter o arquivo de rastreamento em um arquivo de saída legível.

  1. Copie o arquivo de rastreamento do Armazenamento de Objetos para o sistema local.
  2. Navegue até o diretório no qual o arquivo de rastreamento é salvo.
  3. Execute o utilitário TKPROF no prompt do sistema operacional usando a seguinte sintaxe:
    tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n]
 [aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table]
 [explain=user/password] [record=filename4] [width=n]

    Os arquivos de entrada e saída são os únicos argumentos obrigatórios.

  4. Para exibir a Ajuda on-line, chame TKPROF sem argumentos.
    Para obter informações sobre o uso do utilitário TKPROF, consulte Tools for End-to-End Application Tracing no Oracle Database 19c SQL Tuning Guide ou no Oracle Database 23ai SQL Tuning Guide.

Exibir Dados de Rastreamento na View SESSION_CLOUD_TRACE no Autonomous Database

Quando você ativa o Rastreamento de SQL, as mesmas informações de rastreamento salvas no arquivo de rastreamento do Armazenamento de Objetos na Nuvem estão disponíveis na view SESSION_CLOUD_TRACE na sessão em que o rastreamento foi ativado.
Enquanto ainda estiver na sessão do banco de dados, você poderá exibir dados de rastreamento de SQL na view SESSION_CLOUD_TRACE. A view SESSION_CLOUD_TRACE inclui duas colunas: ROW_NUMBER e TRACE.
DESC SESSION_CLOUD_TRACE

Name       Null? Type
---------- ----- ------------------------------
ROW_NUMBER       NUMBER
TRACE            VARCHAR2(32767)

A coluna ROW_NUMBER especifica a ordenação dos dados de rastreamento encontrados na coluna TRACE. Cada linha da saída de rastreamento gravada em um arquivo de rastreamento se torna uma linha na tabela e está disponível na coluna TRACE.

Após a desativação do rastreamento SQL da sessão, você poderá executar consultas na view SESSION_CLOUD_TRACE.

Por exemplo:
SELECT trace FROM SESSION_CLOUD_TRACE ORDERBY row_number;

Os dados em SESSION_CLOUD_TRACE persistem durante a sessão. Após você fazer log-out ou fechar a sessão, os dados não estarão mais disponíveis.

Se o Rastreamento de SQL for ativado e desativado várias vezes na mesma sessão, SESSION_CLOUD_TRACE mostrará os dados de rastreamento de todas as iterações cumulativamente. Portanto, a reativação do rastreamento em uma sessão após a desativação anterior do rastreamento não remove os dados de rastreamento produzidos pela iteração anterior.