Documentação do Oracle Cloud Infrastructure

Usar o SPM para Gerenciar Planos de Execução de SQL

Você pode usar o Gerenciamento de Plano SQL (SPM) para garantir que o desempenho em tempo de execução de uma instrução SQL não seja degradado devido a alterações no plano de execução SQL (plano SQL).

O SPM é um mecanismo preventivo que permite que o otimizador da Oracle gerencie automaticamente planos SQL, garantindo que o banco de dados use apenas planos conhecidos ou verificados. O desempenho de qualquer aplicativo de banco de dados depende muito da execução consistente da instrução SQL. O plano de execução de uma instrução SQL pode ser alterado inesperadamente por vários motivos, como reunir estatísticas do otimizador, alterações nos parâmetros do otimizador ou definições de esquema ou metadados. O SPM fornece "estabilidade do plano" por meio de uma estrutura que preserva os planos SQL atuais em meio a mudanças no ambiente, mas permite alterações apenas para planos melhores. Quando um novo plano SQL é encontrado para uma instrução SQL, ele não é usado até que seja verificado que ele tem desempenho comparável ou melhor que o plano atual.

O SPM usa um mecanismo proativo chamado linha de base do plano SQL, que é um conjunto de planos SQL aceitos que o otimizador Oracle pode usar para uma instrução SQL. Ao usar linhas de base, o SPM evita regressões de plano de mudanças ambientais, ao mesmo tempo em que permite que o otimizador descubra e use planos melhores.

Os principais componentes do SPM são:

  • Captura de plano: As técnicas para capturar e armazenar informações relevantes sobre planos na base de gerenciamento SQL para um conjunto de instruções SQL. A captura de um plano envolve conscientizar o SPM do plano e isso pode ser feito por meio de:
    • Captura automática de plano: Quando ativada, o banco de dados verifica se as instruções SQL executadas são elegíveis para captura automática. Para ser elegível para captura automática de plano, uma instrução executada deve ser repetível e não deve ser excluída por nenhum filtro de captura.
    • Captura de plano manual: Carga em massa iniciada pelo usuário de planos de execução existentes para instruções SQL em uma linha de base de plano SQL.
  • Seleção do plano: A capacidade do otimizador Oracle de detectar alterações do plano com base no histórico do plano armazenado e o uso de linhas de base do plano SQL para selecionar planos para evitar possíveis regressões de desempenho.
  • Evolução do plano: O processo de adicionar novos planos às linhas de base do plano SQL existentes, manual ou automaticamente. O otimizador Oracle verifica novos planos e os adiciona a uma linha de base de plano SQL existente.

Para obter mais informações sobre o SPM e seus componentes, consulte Overview of SQL Plan Management no Oracle Database SQL Tuning Guide.

Para usar o SPM, vá para a página Detalhes do banco de dados gerenciado e clique em Gerenciamento do plano SQL no painel esquerdo em Recursos. Você pode executar as seguintes tarefas SPM no Database Management Diagnostics & Management:

  • Gerenciar linhas de base do plano SQL.
  • Envie tarefas para carregar planos SQL em linhas de base do plano SQL.
  • Execute tarefas de configuração, como ativar, desativar ou editar a linha de base do plano SQL, captura automática do plano e parâmetros de tarefa do Supervisor de Evolução Automática de SPM.

Privilégios Necessários para Executar Tarefas do SPM

A tabela a seguir lista as tarefas do SPM e os privilégios necessários para executá-las.

Observação

Qualquer usuário que tenha o privilégio ADMINISTER SQL MANAGEMENT OBJECT pode executar o pacote DBMS_SPM.
Tarefa Privilégios Necessários
Altere um ou mais atributos de um único plano SQL ou todos os planos associados a uma instrução SQL. Privilégio EXECUTE no pacote SYS.DBMS_SPM.
Alterar o limite de espaço em disco para a base de gerenciamento de SQL. Privilégio EXECUTE no pacote SYS.DBMS_SPM.
Altere o período de retenção de planos SQL não utilizados. Privilégio EXECUTE no pacote SYS.DBMS_SPM.
Configurar filtros de captura automática. Privilégio EXECUTE no pacote SYS.DBMS_SPM.
Configure a tarefa do Consultor de Evolução Automática de SPM. Privilégio EXECUTE no pacote SYS.DBMS_SPM.

Observação: Somente o usuário SYS pode configurar a tarefa do Supervisor de Evolução Automática do SPM, SYS_AUTO_SPM_EVOLVE_TASK.

Desative a captura automática do plano. ALTER SYSTEM privilege
Desative a tarefa do Consultor de Evolução Automática de SPM. Privilégio EXECUTE no pacote SYS.DBMS_AUTO_TASK_ADMIN.
Desative a tarefa do Consultor de Evolução Automática de SPM de alta frequência. Privilégio EXECUTE no pacote SYS.DBMS_SPM.
Desativar o uso das linhas de base do plano SQL armazenado na base de gerenciamento do SQL. ALTER SYSTEM privilege
Elimine um único plano SQL ou todos os planos associados a uma instrução SQL. Privilégio EXECUTE no pacote SYS.DBMS_SPM.
Ative a captura automática do plano. ALTER SYSTEM privilege
Ative a tarefa do Consultor de Evolução Automática de SPM. Privilégio EXECUTE no pacote SYS.DBMS_AUTO_TASK_ADMIN.
Ative a tarefa do Consultor de Evolução Automática de SPM de alta frequência. Privilégio EXECUTE no pacote SYS.DBMS_SPM.
Ativar o uso das linhas de base do plano SQL armazenado na base de gerenciamento do SQL. ALTER SYSTEM privilege
Carregue planos SQL de instantâneos AWR. Privilégio EXECUTE nos pacotes SYS.DBMS_SPM e SYS.DBMS_SCHEDULER.
Carregue planos SQL a partir do cache do cursor. Privilégio EXECUTE nos pacotes SYS.DBMS_SPM e SYS.DBMS_SCHEDULER.
Exibir detalhes da configuração da linha de base do plano SQL. Privilégio SELECT ou READ nas seguintes views:
  • SYS.DBA_SQL_MANAGEMENT_CONFIG
  • SYS.V_$SYSAUX_OCCUPANTS
  • SYS.V_$SYSTEM_PARAMETER2
  • SYS.DBA_ADVISOR_PARAMETERS
  • SYS.DBA_AUTOTASK_CLIENT
Exibir detalhes da linha de base do plano SQL.
  • Privilégio SELECT ou READ na view SYS.DBA_SQL_PLAN_BASELINES.
  • Privilégios necessários para executar a instrução SQL para a qual você deseja obter o plano.
  • Privilégio EXECUTE no pacote SYS.DBMS_XPLAN.
Exibir linhas de base do plano SQL. Privilégio SELECT ou READ na view SYS.DBA_SQL_PLAN_BASELINES.
Exiba os jobs submetidos para carregar as linhas de base do plano SQL. Privilégio SELECT ou READ na view SYS.DBA_SCHEDULER_JOBS.
Exiba o número de linhas de base do plano SQL agregadas por seus atributos. Privilégio SELECT ou READ na view SYS.DBA_SQL_PLAN_BASELINES.
Exiba o número de linhas de base do plano SQL agregadas pela última execução. Privilégio SELECT ou READ na view SYS.DBA_SQL_PLAN_BASELINES.
Exiba as instruções SQL no cache do cursor. Privilégio SELECT ou READ na view SYS.V_$SQL.

Gerenciar Linhas de Base do Plano SQL

Você pode gerenciar linhas de base do plano SQL na guia Linhas de base do plano SQL.

Os blocos a seguir são exibidos na parte superior da guia Linhas de base do plano SQL:

  • Resumo: Exibe o número total de linhas de base do plano SQL e se a linha de base do plano SQL, a captura automática do plano e as tarefas do Supervisor de Evolução Automática do SPM estão ativadas. No mosaico Resumo, você pode ativar ou desativar a linha de base do plano SQL, a captura automática do plano e as tarefas do Supervisor de Evolução Automática do SPM clicando nos botões Ativar ou Desativar e fornecendo credenciais do banco de dados.
  • Últimas execuções da linha de base: Exibe o número de linhas de base do plano SQL com base em quando foram executadas pela última vez. No mosaico Últimas execuções da linha de base, passe o mouse sobre o gráfico de pizza para exibir detalhes adicionais e filtre os dados exibidos no gráfico clicando nas opções de período listadas na legenda.
  • Estatísticas do plano SQL: Exibe planos SQL divididos pelas seguintes estatísticas:
    • Ativado: Planos SQL elegíveis para uso pelo otimizador Oracle.
    • Aceito: Planos SQL que estão em linhas de base do plano SQL e, portanto, estão disponíveis para uso pelo otimizador Oracle.
    • Reproduzido: Planos SQL que são reproduzidos pelo otimizador Oracle.
    • Fixo: Planos SQL aceitos que são marcados como preferenciais, para que o otimizador Oracle considere apenas esses planos na linha de base do plano SQL.
    • Expurgação automática: Planos SQL configurados para serem expurgados automaticamente após o período de retenção padrão.

    No mosaico Estatísticas do plano SQL, passe o mouse sobre o gráfico de barras horizontais para exibir detalhes adicionais e filtre os dados exibidos no gráfico clicando nas opções listadas na legenda.

A seção Planos SQL lista os planos SQL executados com detalhes adicionais, como quando um plano SQL foi executado pela última vez, se ele foi ativado, aceito, reproduzido etc., e sua origem. Para filtrar a lista:

  • Clique em uma seção do gráfico de pizza no mosaico Últimas execuções da linha de base para filtrar pelo horário da última execução.
  • Clique em uma barra no mosaico Estatísticas do plano SQL para filtrar os planos com base em se eles estão ativados, aceitos, reproduzidos, fixos ou configurados para expurgação automática.

Você também pode usar o campo de pesquisa para pesquisar por texto SQL, nome do plano ou origem.

Na seção Planos SQL, você pode:

  • Clique no link do plano SQL na coluna Texto SQL para exibir o plano SQL.
  • Clique no ícone Ações (Ações) de uma instrução SQL e use as seguintes opções no menu:
    • Editar atributos de instrução SQL: Clique para editar atributos de instrução SQL. Observe que todas as alterações feitas nos atributos da instrução SQL impactarão todos os planos SQL associados.
    • Eliminar instrução SQL: Clique para eliminar a instrução SQL. Observe que a eliminação da instrução SQL eliminará os planos SQL associados.
  • Clique no ícone Ações (Ações) de um plano SQL e use as seguintes opções no menu:
    • Exibir detalhes: Clique para exibir o plano SQL.
    • Editar atributos: Clique para definir ou editar os seguintes atributos do plano SQL.
      • Expurgação automática: Marque esta caixa de seleção para expurgar (eliminar) automaticamente o plano SQL após o período de retenção especificado.
      • Ativado: Marque esta caixa de seleção para indicar que o plano SQL é um plano ativado.
      • Fixo: Marque essa caixa de seleção para indicar que o plano SQL é um plano fixo.
    • Eliminar: Clique para eliminar o plano SQL da linha de base do plano SQL.

Carregar Planos SQL

Você pode enviar uma tarefa para carregar planos SQL em linhas de base do plano SQL na guia Carregar planos SQL.

Você pode carregar planos SQL a partir das seguintes origens:

  • AWR: Carregue planos de snapshots do AWR (Automatic Workload Repository). Para obter informações, consulte Carregar Planos SQL do AWR.
    Observação

    O suporte para carregar planos SQL do AWR só está disponível para Bancos de Dados Oracle versão 12.2 e posterior.
  • Cache de cursor: Carregue planos na área SQL compartilhada (cache de cursor). Para obter informações, consulte Carregar Planos SQL do Cache do Cursor.

Carregar Planos SQL do AWR

  1. Na seção Gerenciamento de planos SQL, clique na guia Carregar planos SQL.
  2. Na lista drop-down Carregar plano SQL de, selecione AWR e clique em Carregar.
  3. No painel Carregar planos SQL do AWR:
    1. Forneça as seguintes informações para enviar a tarefa na seção Geral:
      1. Nome da tarefa: Verifique o nome preenchido automaticamente da tarefa e faça alterações nela, se necessário.
      2. Descrição: Como alternativa, informe uma descrição para a tarefa.
      3. Iniciar snapshot: Informe o número do snapshot inicial no intervalo e selecione-o na lista drop-down.
      4. Instantâneo final: Informe o número do instantâneo final no intervalo e selecione-o na lista drop-down.
      5. Filtro de texto SQL: Opcionalmente, informe o texto SQL para carregar apenas os planos que atendem aos critérios de filtragem. Se nenhum valor for fornecido, todos os planos dentro do intervalo de snapshots especificado no AWR serão selecionados.
      6. Atributos do plano: Marque as seguintes caixas de seleção para especificar os atributos do plano:
        • Fixo: Marque essa caixa de seleção para indicar que os planos carregados são fixos.
        • Ativado: Marque esta caixa de seleção para indicar que os planos carregados são planos ativados.
    2. Selecione uma das opções disponíveis na lista drop-down Tipo de credencial na seção Credenciais para especificar as credenciais do banco de dados para estabelecer conexão com o Banco de Dados Gerenciado. Para obter informações sobre tipos de credenciais, consulte Usar Credenciais para Executar Tarefas de Diagnóstico e Gerenciamento.
    3. Clique em Salvar alterações.

Carregar Planos SQL no Cache de Cursores

  1. Na seção Gerenciamento de planos SQL, clique na guia Carregar planos SQL.
  2. Na lista drop-down Carregar plano SQL de, selecione Cache de cursor e clique em Carregar.
  3. No painel Carregar planos SQL no cache de cursor:
    1. Forneça as seguintes informações para enviar a tarefa na seção Geral:
      1. Nome da tarefa: Verifique o nome preenchido automaticamente da tarefa e faça alterações nela, se necessário.
      2. Descrição: Como alternativa, informe uma descrição para a tarefa.
      3. Obter linha de base usando: Use uma das seguintes opções para carregar o plano SQL:
        • ID da SQL: Selecione este botão de opção para identificar a instrução SQL no cache do cursor cujos planos SQL você deseja carregar. Ao selecionar este botão de opção, os seguintes campos são exibidos:
          • ID da SQL: Informe o ID da instrução SQL.
          • Valor de hash do plano: Opcionalmente, informe o valor de hash do plano do plano SQL. Se nenhum valor for fornecido, todos os planos presentes no cache do cursor para a instrução SQL serão carregados.
          • Parâmetro usado para identificar a linha de base do plano SQL na qual os planos são carregados: Opcionalmente, selecione os botões de opção Texto SQL ou Identificador SQL e informe o valor do parâmetro no campo Valor do parâmetro. Se nenhum valor for fornecido, o texto da instrução SQL identificada será extraído do cache do cursor e será usado para identificar a linha de base do plano SQL na qual os planos serão carregados. Se a linha de base do plano SQL não existir, ela será criada.
        • Nome do filtro: Selecione esse botão de opção para especificar o filtro para identificar uma instrução SQL ou um conjunto de instruções SQL. Ao selecionar este botão de opção, os seguintes campos são exibidos:
          • Nome do filtro: Selecione um nome de filtro na lista drop-down.
          • Valor do filtro: Digite o valor do filtro correspondente.
      4. Atributos do plano: Marque as seguintes caixas de seleção para especificar os atributos do plano:
        • Fixo: Marque essa caixa de seleção para indicar que os planos carregados são fixos. Um plano fixo é um plano aceito marcado como preferencial, de modo que o otimizador considere apenas os planos fixos na linha de base.
        • Ativado: Marque esta caixa de seleção para indicar que os planos carregados são planos ativados. Um plano ativado é elegível para uso do otimizador Oracle.
    2. Selecione uma das opções disponíveis na lista drop-down Tipo de credencial na seção Credenciais para especificar as credenciais do banco de dados para estabelecer conexão com o Banco de Dados Gerenciado. Para obter informações sobre tipos de credenciais, consulte Usar Credenciais para Executar Tarefas de Diagnóstico e Gerenciamento.
    3. Clique em Salvar alterações.

Executar Tarefas de Configuração do SPM

Você pode executar tarefas de configuração do SPM, como ativar ou desativar a linha de base do plano SQL e os parâmetros de captura automática do plano na guia Configuração.

A guia Configuração tem as seguintes seções principais:

  • Base do plano SQL: Fornece as opções para ativar ou desativar a linha de base do plano SQL. Se a linha de base do plano SQL estiver ativada, os seguintes parâmetros serão exibidos nesta seção e, para fazer alterações neles, clique em Editar:
    • Retenção do plano (semanas): O número de semanas para reter planos SQL não utilizados antes de serem expurgados. O período pode variar entre 5 e 523 semanas e o padrão é 53 semanas.
    • Orçamento de espaço (%): A porcentagem máxima de espaço SYSAUX que a base de gerenciamento de SQL pode usar. O intervalo permitido para esse limite é entre 1% e 50% e o padrão é 10%.
  • Captura automática do plano: Fornece as opções para ativar ou desativar a captura automática do plano. Um filtro automático permite capturar apenas instruções desejadas e excluir instruções não críticas. Essa técnica economiza espaço no tablespace SYSAUX.

    Se a captura automática do plano estiver ativada, os seguintes filtros serão exibidos nesta seção e, para fazer alterações neles, clique em Editar:

    • Ações a serem incluídas ou Ações a serem excluídas: As ações a serem incluídas ou excluídas da captura automática.
    • Módulos a serem incluídos ou Módulos a serem excluídos: Os módulos a serem incluídos ou excluídos da captura automática.
    • Nomes de esquema de parsing a serem incluídos ou Nomes de esquema de parsing a serem excluídos: Os nomes de esquema de parsing a serem incluídos ou excluídos da captura automática.
    • Texto SQL a ser incluído ou Texto SQL a ser excluído: O texto SQL a ser incluído ou excluído da captura automática.
    Observação

    A captura de plano seletivo (filtros) só está disponível para Bancos de Dados Oracle versão 12.2 e posterior.
  • Tarefa do Consultor de Evolução Automática de SPM: Fornece as opções para ativar ou desativar a tarefa do Consultor de Evolução Automática de SPM. Se a tarefa do Supervisor de Evolução Automática do SPM estiver ativada, os seguintes parâmetros serão exibidos nesta seção e, para fazer alterações neles, clique em Editar:
    • Tarefa do Consultor de Evolução Automática de SPM de Alta Frequência: Exibe se a tarefa do Consultor de Evolução Automática de SPM está ativada para ocorrer com mais frequência.
    • Origens de plano alternativo: As origens para procurar planos adicionais.
    • Linhas de base do plano alternativo: Os planos alternativos que devem ser carregados. O valor padrão é Existente.
    • Limite de plano alternativo: O número máximo de planos a serem carregados no total. O valor padrão é Ilimitado.
    • Aceitar planos automaticamente: Exibe se os planos recomendados devem ser aceitos automaticamente.
    • Limite de tempo permitido (segundos): O limite de tempo global em segundos. Este é o tempo total permitido para a tarefa.
    Observação

    • Para Autonomous Databases, não é possível executar tarefas de configuração de gerenciamento de plano SQL.
    • Os privilégios SYSDBA são necessários para editar os parâmetros da tarefa do Supervisor de Evolução Automática do SPM.
    • A tarefa do Supervisor de Evolução Automática do SPM só está disponível para Bancos de Dados Oracle versão 12.2 e posterior.
    • A tarefa Automatic SPM Evolve Advisor de alta frequência só está disponível para Oracle Databases 19c e posteriores em execução na plataforma Oracle Exadata.