Documentação do Oracle Cloud Infrastructure

Executar Rastreamento SQL no Autonomous AI Database

Use o rastreamento SQL 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. O Autonomous AI Database suporta rastreamento baseado em sessão com o ALTER SESSION e rastreamento direcionado com o pacote DBMS_USERDIAG.

O rastreamento SQL é desativado por padrão. Para coletar dados de rastreamento, ative uma regra de rastreamento, execute a carga de trabalho que deseja investigar e, em seguida, desative a regra usando os mesmos atributos de identificação. Quando o rastreamento é desativado, o banco de dados faz upload dos dados coletados para o Cloud Object Storage. Você pode revisar os dados no arquivo de rastreamento gerado ou na exibição SESSION_CLOUD_TRACE.

O método de rastreamento determina como o banco de dados identifica as sessões a serem rastreadas. O identificador de cliente e o rastreamento de serviço, módulo e ação podem identificar sessões correspondentes em instâncias de banco de dados, enquanto o rastreamento baseado em sessão tem como alvo uma sessão específica em uma instância específica. O identificador do cliente e o serviço, o módulo e o rastreamento de ações suportam informações de variável de vinculação e evento de espera. Como o rastreamento básico não está disponível para esses métodos de rastreamento, o rastreamento de espera é ativado por padrão quando você não especifica explicitamente o rastreamento de vinculação ou espera. O rastreamento baseado em sessão suporta níveis de rastreamento básico, de vínculo e de espera.

Configurar o Rastreamento de SQL no Autonomous AI Database

Configure o banco de dados para salvar arquivos de rastreamento SQL no Cloud Object Storage antes de ativar o rastreamento SQL.

Observação

Observação: Se você ativar o rastreamento SQL, o desempenho da aplicação para a sessão poderá ser degradado enquanto a coleta de rastreamento estiver ativada. Esse impacto esperado é causado pela sobrecarga de coleta e salvamento de dados de rastreamento.

Pré-requisitos para Rastreamento SQL

A configuração a seguir é necessária para o rastreamento ALTER SESSION e o rastreamento DBMS_USERDIAG. Execute estas etapas antes de ativar o método de rastreamento.

Para configurar seu banco de dados para rastreamento SQL, faça o seguinte:

  1. Crie um bucket para armazenar arquivos de rastreamento no Cloud Object Storage.

    Para salvar arquivos de rastreamento SQL, você pode usar qualquer serviço do Cloud Object Storage suportado pelo Autonomous AI Database.

    Por exemplo, para criar um bucket no Oracle Cloud Infrastructure Object Storage:

    1. Usando o Oracle Cloud Infrastructure Console.

    2. Selecione Armazenamento no menu.

    3. Em Armazenamento, selecione Object Storage e Archive Storage.

    4. Clique em Criar Bucket.

    5. Na página Criar Bucket, informe o nome do bucket e clique em Criar.

    Se você usar o Oracle Cloud Infrastructure Object Storage, os arquivos de rastreamento SQL só serão suportados em buckets criados na camada de armazenamento padrão. Selecione Padrão como a camada de armazenamento. Consulte Visão Geral do Serviço Object Storage para obter informações sobre a camada de Armazenamento de Objetos Padrão.

  2. Crie uma credencial para 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;
/

    Observação

    Observação: Se sua conta de usuário estiver no domínio de identidades padrão, a especificação apenas do nome do usuário será suficiente. Se sua conta de usuário pertencer a um domínio de identidades não padrão, prefixe o nome do usuário com o nome do domínio de identidades seguido por /. Por exemplo, identity_domain_name/adb_user@example.com.

    Para a Oracle Cloud Infrastructure, especifique o nome do usuário da Oracle Cloud Infrastructure como username e o token de autenticação da Oracle Cloud Infrastructure como password. Consulte Como Trabalhar com Tokens de Autenticação para obter mais informações.

    Consulte Procedimento CREATE_CREDENTIAL para obter informações sobre os parâmetros e seus valores para o serviço Cloud Object Storage que você usa.

  3. Defina as propriedades do banco de dados para especificar o local do Cloud Object Storage para arquivos de rastreamento SQL e a credencial usada para acessá-lo.

    1. Defina a propriedade do banco de dados DEFAULT_LOGGING_BUCKET para especificar o bucket de log.

      Por exemplo, se você criou o bucket no Oracle Cloud Infrastructure 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/';

      Neste exemplo, namespace-string é o namespace do Oracle Cloud Infrastructure Object Storage e bucket_name é o nome do bucket que você criou. 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.

    2. Defina a propriedade do banco de dados DEFAULT_CREDENTIAL como a credencial criada na Etapa 2. Inclua o nome do esquema com a credencial. Neste exemplo, o esquema é ADMIN.

      ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.DEF_CRED_NAME';

Pré-requisitos Específicos do DBMS_USERDIAG

Os pré-requisitos comuns acima se aplicam ao rastreamento DBMS_USERDIAG. Além disso, preencha os seguintes pré-requisitos antes de usar este pacote:

  • Você deve estar conectado como usuário ADMIN ou ter o privilégio EXECUTE em DBMS_USERDIAG.
  • Para rastreamento baseado em sessão, obtenha o ID da sessão de destino e o número de série. Você pode consultar V$SESSION para identificar esses valores.
  • Para rastreamento de identificador de cliente, certifique-se de que seu aplicativo defina o identificador de cliente, por exemplo, SALES_APP.
  • Para rastreamento de serviço, módulo e ação, identifique os atributos do aplicativo usados para corresponder às sessões de destino.
  • Use o rastreamento de bind e espera somente quando precisar dessas informações de diagnóstico adicionais. Essas opções podem gerar arquivos de rastreamento grandes, afetar o desempenho do aplicativo e aumentar a memória PGA usada pela sessão enquanto os dados de rastreamento são coletados.

Depois de concluir os pré-requisitos comuns e específicos do DBMS_USERDIAG, escolha o método de rastreamento que melhor corresponda à sua meta de diagnóstico. Use ALTER SESSION para rastrear a atividade na sessão atual ou use DBMS_USERDIAG para direcionar uma carga de trabalho específica ou um grupo de sessões.

Rastrear Sessões com ALTER SESSION

Use ALTER SESSION para ativar o rastreamento SQL para a sessão do banco de dados atual. Este método captura informações de rastreamento para o trabalho executado nessa sessão.

Antes de ativar o rastreamento SQL, configure o banco de dados para salvar arquivos de rastreamento SQL. Consulte Configurar Rastreamento de SQL no Autonomous AI Database para obter mais informações.

Para ativar o rastreamento SQL para a sessão atual:

  1. (Opcional) Defina um identificador de cliente para o aplicativo. Este identificador é usado como um componente do nome do arquivo de rastreamento.

    Por exemplo:

    BEGIN
  DBMS_SESSION.SET_IDENTIFIER('sqlt_test');
END;
/

    DBMS_SESSION.SET_IDENTIFIER define o identificador do cliente para a sessão atual.

  2. (Opcional) Defina um nome de módulo para o aplicativo. O nome do módulo também é usado como um componente do nome do arquivo de rastreamento.

    Por exemplo:

    BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('modname', NULL);
END;
/

  3. Ative o recurso de rastreamento SQL.

    Por exemplo:

    ALTER SESSION SET SQL_TRACE = TRUE;

    ALTER SESSION SET SQL_TRACE = TRUE ativa o rastreamento para a sessão atual.

  4. Execute a carga de trabalho que deseja analisar. Os dados de rastreamento SQL são coletados enquanto a carga de trabalho é executada na sessão.

  5. Desativar rastreamento SQL.

Desativar Rastreamento SQL no Autonomous AI Database

Desative o rastreamento SQL após a conclusão da carga de trabalho. A desativação do rastreamento grava os dados coletados na sessão e em um arquivo de rastreamento no bucket do Cloud Object Storage configurado para rastreamento SQL.

Para desativar o rastreamento SQL para a sessão atual:

  1. Desative o recurso de rastreamento SQL.

    Por exemplo:

    ALTER SESSION SET SQL_TRACE = FALSE;

    ALTER SESSION SET SQL_TRACE = FALSE desativa o rastreamento da sessão atual.

  2. (Opcional) Redefina a propriedade do banco de dados DEFAULT_LOGGING_BUCKET para limpar o valor do bucket de log.

    Por exemplo:

    ALTER DATABASE PROPERTY SET DEFAULT_LOGGING_BUCKET = '';

    Esta instrução limpa o bucket de log configurado para rastreamento SQL.

Depois de desativar o rastreamento SQL, você poderá exibir os dados de rastreamento de uma das seguintes maneiras:

Rastrear Cargas de Trabalho com DBMS_USERDIAG

Use o pacote DBMS_USERDIAG para ativar e desativar o rastreamento SQL para uma instrução, sessão, identificador de cliente ou serviço de aplicativo, módulo e ação SQL específicos. Essa abordagem ajuda a coletar informações de diagnóstico direcionadas para a carga de trabalho SQL específica sob investigação, ao mesmo tempo em que minimiza a atividade não relacionada do banco de dados na análise.

Benefícios do Rastreamento SQL DBMS_USERDIAG

O DBMS_USERDIAG fornece os seguintes recursos:

  • Rastrear o escopo para uma instrução SQL específica usando seu SQL_ID.
  • Rastreie outra sessão ativa fornecendo seu ID de Sessão e número de Série.
  • Rastrear atividade de aplicativo por identificador de cliente.
  • Rastreie a atividade do aplicativo por serviço, módulo e ação.
  • Inclua valores de variáveis de bind e detalhes do evento de espera quando forem necessários diagnósticos mais profundos.
  • Colete estatísticas do plano de execução para a primeira execução, cada execução ou um conjunto adaptável de execuções com base na necessidade de investigação.

Ativar Rastreamento SQL para um ID SQL Específico

Use um SQL_ID para rastrear somente a instrução SQL correspondente. Neste exemplo, as informações da variável de bind e do evento de espera são coletadas e as estatísticas do plano adaptativo são ativadas.

O parâmetro sys não está operacional no Autonomous AI Database e não é usado nos exemplos deste tópico.

Por exemplo: 

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_ENABLE(
    sql_id    => '5yka4rvxpbjc6',
    binds     => 1,
    waits     => 1,
    plan_stat => 'ADAPTIVE');
END;
/

O parâmetro sql_id limita o rastreamento à instrução SQL especificada. Os parâmetros binds e waits incluem informações de variável de bind e de evento de espera, e plan_stat especifica a opção de coleta de estatísticas do plano.

Execute a carga de trabalho que usa o ID SQL 5yka4rvxpbjc6 e, em seguida, desative o rastreamento:

Por exemplo: 

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_DISABLE(
    sql_id => '5yka4rvxpbjc6');
END;
/

O parâmetro sql_id identifica a regra de rastreamento a ser desativada. Use o mesmo valor de identificação especificado ao ativar o rastreamento.

O valor sql_id deve identificar a instrução que você deseja rastrear. Os valores válidos de plan_stat são FIRST_EXECUTION, NEVER, ALL_EXECUTIONS e ADAPTIVE.

Ativar Rastreamento SQL para uma Sessão Diferente

Use o ID da sessão de destino e o número de série para rastrear outra sessão. Neste exemplo, 39 é o ID da sessão e 15593 é o número de série.

Por exemplo: 

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_ENABLE(
    sid   => 39,
    ser   => 15593,
    binds => 1,
    waits => 1);
END;
/

Os parâmetros sid e ser identificam a sessão de destino. Os parâmetros binds e waits permitem a coleta de informações de variável de bind e evento de espera.

Execute a carga de trabalho na sessão de destino e, em seguida, desative o rastreamento de uma sessão com os privilégios necessários:

Por exemplo: 

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_DISABLE(
    sid => 39,
    ser => 15593);
END;
/

Os parâmetros sid e ser identificam a regra de rastreamento associada à sessão de destino.

Se a sessão de destino sair antes de o rastreamento ser desativado, o banco de dados fará upload do arquivo de rastreamento quando a sessão sair.

Ativar Rastreamento SQL por Identificador de Cliente

Use um identificador de cliente para rastrear sessões correspondentes entre instâncias de banco de dados. Neste exemplo, o aplicativo define o identificador do cliente como SALES_APP.

Por exemplo: 

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_ENABLE(
    client_id => 'SALES_APP',
    binds     => 1,
    waits     => 1);
END;
/

O parâmetro client_id identifica as sessões do aplicativo a serem rastreadas. Os parâmetros binds e waits permitem a coleta de informações de diagnóstico adicionais.

Execute a carga de trabalho do aplicativo e desative o rastreamento usando o mesmo identificador de cliente:

Por exemplo: 

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_DISABLE(
    client_id => 'SALES_APP');
END;
/

O parâmetro client_id identifica a regra de rastreamento a ser desativada. Use o mesmo identificador de cliente especificado ao ativar o rastreamento.

Ativar Rastreamento SQL por Serviço, Módulo e Ação

Use atributos de aplicativo para rastrear sessões que usam um serviço, módulo e ação especificados. Neste exemplo, o rastreamento é limitado ao serviço HIGH, ao módulo ORDER_PROCESSING e à ação CREATE_ORDER.

Por exemplo: 

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_ENABLE(
    service => 'HIGH',
    module  => 'ORDER_PROCESSING',
    action  => 'CREATE_ORDER',
    binds   => 1,
    waits   => 1);
END;
/

Os parâmetros service, module e action identificam as sessões do aplicativo a serem rastreadas. Os parâmetros binds e waits permitem a coleta de informações de diagnóstico adicionais.

Execute a carga de trabalho do aplicativo e, em seguida, desative o rastreamento usando os mesmos valores de serviço, módulo e ação:

Por exemplo: 

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_DISABLE(
    service => 'HIGH',
    module  => 'ORDER_PROCESSING',
    action  => 'CREATE_ORDER');
END;
/

Os parâmetros service, module e action identificam a regra de rastreamento a ser desativada. Use os mesmos valores especificados ao ativar o rastreamento.

O aplicativo deve definir os atributos de serviço, módulo e ação para suas sessões. Você pode usar DBMS_APPLICATION_INFO.SET_MODULE para definir o módulo e a ação. A correspondência é executada nas instâncias do banco de dados quando os atributos do aplicativo são iguais.

Para obter mais informações, consulte:

Verificar Rastreamento SQL

Você pode consultar as seguintes visualizações para verificar se o rastreamento está ativado e para revisar as regras de rastreamento:

SELECT sid, serial#, sql_trace
FROM   V$SESSION
WHERE  sid = 39;
SELECT *
FROM   DBA_ENABLED_TRACES;

A coluna TRACE_TYPE em DBA_ENABLED_TRACES identifica o método de rastreamento, como ID SQL, sessão, identificador do cliente ou serviço, módulo e ação.

Exibir Arquivo de Rastreamento Salvo no Cloud Object Storage no Autonomous AI Database

Descreve a nomeação do arquivo de saída para arquivos de rastreamento SQL e mostra os comandos para usar TKPROF para organizar e exibir dados do arquivo de rastreamento.

Quando você desativa o rastreamento SQL, os dados de rastreamento coletados na sessão são gravados no bucket do Cloud Object Storage configurado com DEFAULT_LOGGING_BUCKET.

O recurso de Rastreamento SQL grava os dados de rastreamento coletados na sessão no Cloud Object Storage 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 do banco de dados DEFAULT_LOGGING_BUCKET. Consulte Configurar Rastreamento de SQL no Autonomous AI Database.
  • clientID: O identificador do cliente. Consulte Rastrear Sessões com ALTER SESSION.
  • moduleName: O nome do módulo. Consulte Rastrear Sessões com ALTER SESSION.
  • numID1_numID2: Dois identificadores que diferenciam exclusivamente o arquivo de rastreamento dos arquivos de rastreamento gerados por outras sessões usando o mesmo bucket. Quando uma sessão executa uma consulta paralela, o recurso de Rastreamento SQL pode produzir vários arquivos de rastreamento com valores diferentes.

Observação

Observação: Quando o rastreamento SQL é ativado e desativado várias vezes na mesma sessão, cada iteração de rastreamento gera um arquivo de rastreamento separado. Os arquivos subsequentes incluem um sufixo numérico para evitar a substituição de arquivos de rastreamento anteriores. O sufixo começa em 1 e é incrementado para cada iteração de rastreamento subsequente.

Por exemplo, o seguinte é um exemplo de nome de arquivo de rastreamento gerado quando o identificador do cliente é sqlt_test e o nome do módulo é 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 serviço Object Storage 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 necessários.

  4. Para exibir a ajuda on-line, chame TKPROF sem argumentos.

Consulte "Tools for End-to-End Application Tracing" no Oracle AI Database SQL Tuning Guide para obter informações sobre o uso do utilitário TKPROF.

Exibir Dados de Rastreamento na View SESSION_CLOUD_TRACE no Autonomous AI Database

Quando você ativa o rastreamento SQL, as mesmas informações de rastreamento salvas no arquivo de rastreamento do Cloud Object Storage ficam disponíveis na exibição SESSION_CLOUD_TRACE na sessão em que o rastreamento foi ativado.

Enquanto você ainda estiver na sessão do banco de dados, poderá exibir dados de rastreamento SQL na exibição SESSION_CLOUD_TRACE. A view inclui duas colunas: ROW_NUMBER e TRACE.

DESC SESSION_CLOUD_TRACE

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

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

Depois de desativar o rastreamento SQL, consulte a view SESSION_CLOUD_TRACE. Por exemplo: 

SELECT trace
FROM   SESSION_CLOUD_TRACE
ORDER BY row_number;

Os dados no SESSION_CLOUD_TRACE persistem durante a sessão. Depois de fazer logout ou fechar a sessão, os dados não estarão mais disponíveis.

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