Documentación de Oracle Cloud Infrastructure

Uso de SPM para Gestionar Planes de Ejecución de SQL

Puede utilizar la gestión de planes SQL (SPM) para asegurarse de que el rendimiento en tiempo de ejecución de una sentencia SQL no se reduce debido a cambios en el plan de ejecución SQL (plan SQL).

SPM es un mecanismo preventivo que permite al optimizador de Oracle gestionar automáticamente planes SQL, lo que garantiza que la base de datos solo utilice planes conocidos o verificados. El rendimiento de cualquier aplicación de base de datos depende en gran medida de la ejecución consistente de sentencias SQL. El plan de ejecución de una sentencia SQL puede cambiar inesperadamente por diversos motivos, como la recopilación de estadísticas del optimizador, los cambios en los parámetros del optimizador o las definiciones de esquema o metadatos. SPM proporciona "estabilidad del plan" a través de un marco que conserva los planes SQL actuales en medio de cambios en el entorno, pero permite cambios solo para mejores planes. Al detectar un nuevo plan SQL para una sentencia SQL, no se utilizará hasta que se haya verificado que tiene un rendimiento comparable o superior al del plan actual.

SPM utiliza un mecanismo proactivo denominado línea base de plan SQL, que es un juego de planes SQL aceptados que el optimizador de Oracle puede utilizar para una sentencia SQL. Mediante el uso de líneas base, SPM evita las regresiones de planes de los cambios ambientales, al tiempo que permite al optimizador detectar y utilizar mejores planes.

Los principales componentes de SPM son:

  • Captura de plan: técnicas para capturar y almacenar información relevante sobre planes en la base de gestión SQL para un juego de sentencias SQL. La captura de un plan implica hacer que SPM conozca el plan y se puede hacer a través de:
    • Captura automática de planes: cuando está activada, la base de datos comprueba si las sentencias SQL ejecutadas son aptas para la captura automática. Para ser elegible para la captura automática del plan, una sentencia ejecutada debe ser repetible y no debe ser excluida por ningún filtro de captura.
    • Captura de plan manual: carga masiva iniciada por el usuario de planes de ejecución existentes para sentencias SQL en una línea base de plan SQL.
  • Selección de plan: capacidad del optimizador de Oracle para detectar cambios de plan en función del historial de planes almacenado y el uso de líneas base de plan SQL para seleccionar planes para evitar posibles regresiones de rendimiento.
  • Evolución del plan: proceso que consiste en agregar nuevos planes a las líneas base de plan SQL existentes, ya sea de forma manual o automática. El optimizador de Oracle verifica los nuevos planes y los agrega a una línea base de plan SQL existente.

Para obtener más información sobre SPM y sus componentes, consulte Visión general de la gestión de planes SQL en la Guía de ajustes SQL de Oracle Database.

Para utilizar SPM, vaya a la página Detalles de base de datos gestionada y haga clic en Gestión de planes SQL en el panel izquierdo, en Recursos. Puede realizar las siguientes tareas de SPM en Database Management Diagnostics & Management:

  • Gestionar líneas base de plan SQL.
  • Ejecute tareas para cargar planes SQL en líneas base de plan SQL.
  • Realizar tareas de configuración como activar, desactivar o editar la línea base del plan SQL, la captura automática del plan y los parámetros de tarea del asesor de evolución automática de SPM.

Privilegios necesarios para realizar tareas de SPM

En la siguiente tabla, se muestran las tareas de SPM y los privilegios necesarios para realizarlas.

Nota

Cualquier usuario al que se le haya otorgado el privilegio ADMINISTER SQL MANAGEMENT OBJECT puede ejecutar el paquete DBMS_SPM.
Tarea Privilegios Necesarios
Cambie uno o más atributos de un único plan SQL o de todos los planes asociados a una sentencia SQL. Privilegio EXECUTE en el paquete SYS.DBMS_SPM.
Cambie el límite de espacio en disco para la base de gestión SQL. Privilegio EXECUTE en el paquete SYS.DBMS_SPM.
Cambie el período de retención de los planes SQL no utilizados. Privilegio EXECUTE en el paquete SYS.DBMS_SPM.
Configurar filtros de captura automática. Privilegio EXECUTE en el paquete SYS.DBMS_SPM.
Configure la tarea del asesor de mejora automática de SPM. Privilegio EXECUTE en el paquete SYS.DBMS_SPM.

Nota: solo el usuario SYS puede configurar la tarea del asesor de evolución automática de SPM, SYS_AUTO_SPM_EVOLVE_TASK.

Permite desactivar la captura automática de planes. ALTER SYSTEM privilege
Desactive la tarea del asesor de mejora automática de SPM. Privilegio EXECUTE en el paquete SYS.DBMS_AUTO_TASK_ADMIN.
Desactive la tarea del asesor de mejora automática de SPM de alta frecuencia. Privilegio EXECUTE en el paquete SYS.DBMS_SPM.
Desactivar el uso de líneas base de plan SQL almacenadas en la base de gestión SQL. ALTER SYSTEM privilege
Borre un único plan SQL o todos los planes asociados a una sentencia SQL. Privilegio EXECUTE en el paquete SYS.DBMS_SPM.
Permite activar la captura automática de planes. ALTER SYSTEM privilege
Active la tarea del asesor de mejora automática de SPM. Privilegio EXECUTE en el paquete SYS.DBMS_AUTO_TASK_ADMIN.
Active la tarea de asesor de mejora automática de SPM de alta frecuencia. Privilegio EXECUTE en el paquete SYS.DBMS_SPM.
Permite activar el uso de líneas base de plan SQL almacenadas en la base de gestión SQL. ALTER SYSTEM privilege
Cargar planes SQL de instantáneas de AWR. Privilegio EXECUTE en los paquetes SYS.DBMS_SPM y SYS.DBMS_SCHEDULER.
Cargar planes SQL de la caché de cursor. Privilegio EXECUTE en los paquetes SYS.DBMS_SPM y SYS.DBMS_SCHEDULER.
Ver los detalles de configuración de la línea base de plan SQL. Privilegio SELECT o READ en las siguientes vistas:
  • SYS.DBA_SQL_MANAGEMENT_CONFIG
  • SYS.V_$SYSAUX_OCCUPANTS
  • SYS.V_$SYSTEM_PARAMETER2
  • SYS.DBA_ADVISOR_PARAMETERS
  • SYS.DBA_AUTOTASK_CLIENT
Ver detalles de la línea base de plan SQL.
  • Privilegio SELECT o READ en la vista SYS.DBA_SQL_PLAN_BASELINES.
  • Privilegios necesarios para ejecutar la sentencia SQL para la que desea obtener el plan.
  • Privilegio EXECUTE en el paquete SYS.DBMS_XPLAN.
Ver líneas base de plan SQL. Privilegio SELECT o READ en la vista SYS.DBA_SQL_PLAN_BASELINES.
Vea los trabajos ejecutados para cargar las líneas base del plan SQL. Privilegio SELECT o READ en la vista SYS.DBA_SCHEDULER_JOBS.
Ver el número de líneas base de plan SQL agregadas por sus atributos. Privilegio SELECT o READ en la vista SYS.DBA_SQL_PLAN_BASELINES.
Ver el número de líneas base de plan SQL agregadas por su última ejecución. Privilegio SELECT o READ en la vista SYS.DBA_SQL_PLAN_BASELINES.
Ver las sentencias SQL de la caché de cursores. Privilegio SELECT o READ en la vista SYS.V_$SQL.

Gestionar líneas base de plan SQL

Puede gestionar líneas base de plan SQL en el separador Líneas base de plan SQL.

Los siguientes mosaicos se muestran en la parte superior del separador Líneas base de plan SQL:

  • Resumen: muestra el número total de líneas base de plan SQL y si están activadas las tareas de línea base de plan SQL, captura automática de plan y asesor de evolución automática de SPM. En el mosaico Resumen, puede activar o desactivar la línea base de plan SQL, la captura automática de plan y las tareas del asesor de evolución automática de SPM haciendo clic en los botones Activar o Desactivar y proporcionando credenciales de base de datos.
  • Últimas ejecuciones de base: muestra el número de líneas base de plan SQL en función de cuándo se ejecutaron por última vez. En el mosaico Últimas ejecuciones base, pase el mouse por el gráfico circular para ver detalles adicionales y filtre los datos que se muestran en el gráfico haciendo clic en las opciones de período de tiempo que se muestran en la leyenda.
  • Estadísticas de plan SQL: muestra los planes SQL desglosados por las siguientes estadísticas:
    • Activado: planes SQL que son elegibles para su uso por parte del optimizador de Oracle.
    • Aceptados: planes SQL que están en líneas base de plan SQL y, por lo tanto, disponibles para su uso por parte del optimizador de Oracle.
    • Reproducido: planes SQL reproducidos por el optimizador de Oracle.
    • Fijo: planes SQL aceptados marcados como preferidos, de modo que el optimizador de Oracle solo tenga en cuenta estos planes en la línea base de plan SQL.
    • Depuración automática: planes SQL configurados para que se depuren automáticamente después del período de retención por defecto.

    En el mosaico Estadísticas de plan SQL, pase el mouse por el gráfico de barras horizontales para ver detalles adicionales y filtre los datos que se muestran en el gráfico haciendo clic en las opciones que se muestran en la leyenda.

La sección Planes SQL muestra los planes SQL ejecutados con detalles adicionales, como cuándo se ejecutó por última vez un plan SQL, si está activado, aceptado, reproducido, etc., y su origen. Para filtrar la lista:

  • Haga clic en una sección del gráfico circular del mosaico Últimas ejecuciones de base para filtrar por la última hora de ejecución.
  • Haga clic en una barra del mosaico Estadísticas de plan SQL para filtrar los planes en función de si están activados, aceptados, reproducidos, corregidos o configurados para la depuración automática.

También puede utilizar el campo de búsqueda para buscar por texto SQL, nombre de plan u origen.

En la sección Planes SQL puede:

  • Haga clic en el enlace del plan SQL en la columna Texto SQL para ver el plan SQL.
  • Haga clic en el icono Acciones (Acciones) de una sentencia SQL y utilice las siguientes opciones del menú:
    • Editar atributos de sentencia SQL: haga clic para editar los atributos de sentencia SQL. Tenga en cuenta que cualquier cambio realizado en los atributos de la sentencia SQL afectará a todos los planes SQL asociados.
    • Borrar sentencia SQL: haga clic para borrar la sentencia SQL. Tenga en cuenta que al borrar la sentencia SQL se borran los planes SQL asociados.
  • Haga clic en el icono Acciones (Acciones) de un plan SQL y utilice las siguientes opciones del menú:
    • Ver detalles: haga clic para ver el plan SQL.
    • Editar atributos: haga clic para definir o editar los siguientes atributos del plan SQL.
      • Depuración automática: seleccione esta casilla de control para depurar (borrar) automáticamente el plan SQL después del período de retención especificado.
      • Activado: seleccione esta casilla de control para indicar que el plan SQL es un plan activado.
      • Fijo: seleccione esta casilla de control para indicar que el plan SQL es un plan fijo.
    • Borrar: haga clic para borrar el plan SQL de la línea base de plan SQL.

Cargar planes SQL

Puede ejecutar una tarea para cargar planes SQL en líneas base de plan SQL en el separador Cargar planes SQL.

Puede cargar planes SQL desde los siguientes orígenes:

  • AWR: cargue planes de las instantáneas del repositorio de carga de trabajo automática (AWR). Para obtener más información, consulte Carga de Planes SQL desde AWR.
    Nota

    El soporte para cargar planes SQL desde AWR solo está disponible para las bases de datos Oracle versión 12.2 y posteriores.
  • Caché de cursores: cargue planes del área SQL compartida (caché de cursores). Para obtener más información, consulte Carga de Planes SQL desde Caché de Cursores.

Carga de Planes SQL desde AWR

  1. En la sección Gestión de planes SQL, haga clic en el separador Cargar planes SQL.
  2. En la lista desplegable Cargar plan SQL desde, seleccione AWR y haga clic en Cargar.
  3. En el panel Cargar planes SQL desde AWR:
    1. Proporcione la siguiente información para ejecutar la tarea en la sección General:
      1. Nombre de tarea: revise el nombre rellenado automáticamente de la tarea y realice cambios en ella, si es necesario.
      2. Descripción: opcionalmente, introduzca una descripción para la tarea.
      3. Iniciar instantánea: introduzca el número de la instantánea inicial en el rango y selecciónela en la lista desplegable.
      4. Instantánea final: introduzca el número de la instantánea final en el rango y selecciónela en la lista desplegable.
      5. Filtro de texto SQL: si lo desea, introduzca texto SQL para cargar solo los planes que cumplen los criterios de filtrado. Si no se proporciona ningún valor, se seleccionan todos los planes del rango de instantáneas especificado en AWR.
      6. Atributos de plan: seleccione las siguientes casillas de control para especificar los atributos de plan:
        • Fijo: seleccione esta casilla de control para indicar que los planes cargados son planes fijos.
        • Activado: seleccione esta casilla de control para indicar que los planes cargados son planes activados.
    2. Seleccione una de las opciones disponibles en la lista desplegable Tipo de credencial de la sección Credenciales para especificar las credenciales de base de datos para conectarse a la base de datos gestionada. Para obtener información sobre los tipos de credenciales, consulte Uso de credenciales para realizar tareas de diagnóstico y gestión.
    3. Haga clic en Guardar cambios.

Cargar Planes SQL de Caché de Cursores

  1. En la sección Gestión de planes SQL, haga clic en el separador Cargar planes SQL.
  2. En la lista desplegable Cargar plan SQL desde, seleccione Caché de cursor y haga clic en Cargar.
  3. En el panel Cargar planes SQL de caché de cursores:
    1. Proporcione la siguiente información para ejecutar la tarea en la sección General:
      1. Nombre de tarea: revise el nombre rellenado automáticamente de la tarea y realice cambios en ella, si es necesario.
      2. Descripción: opcionalmente, introduzca una descripción para la tarea.
      3. Recuperar línea base mediante: utilice una de las siguientes opciones para cargar el plan SQL:
        • ID de SQL: seleccione este botón de radio para identificar la sentencia SQL en la caché de cursores cuyos planes SQL desea cargar. Al seleccionar este botón de radio, se muestran los siguientes campos:
          • ID SQL: introduzca el ID de la sentencia SQL.
          • Valor hash de plan: si lo desea, introduzca el valor hash de plan del plan SQL. Si no se proporciona ningún valor, se cargan todos los planes presentes en la caché de cursores para la sentencia SQL.
          • Parámetro utilizado para identificar la línea base del plan SQL en la que se cargan los planes: si lo desea, seleccione los botones de radio Texto SQL o Manejo SQL e introduzca el valor del parámetro en el campo Valor de parámetro. Si no se proporciona ningún valor, el texto de la sentencia SQL identificada se extrae de la caché de cursores y se utiliza para identificar la línea base del plan SQL en la que se cargan los planes. Si la línea base del plan SQL no existe, se crea.
        • Nombre de filtro: seleccione este botón de radio para especificar el filtro para identificar una sentencia SQL o un juego de sentencias SQL. Al seleccionar este botón de radio, se muestran los siguientes campos:
          • Nombre de filtro: seleccione un nombre de filtro de la lista desplegable.
          • Valor del filtro: introduzca el valor del filtro correspondiente.
      4. Atributos de plan: seleccione las siguientes casillas de control para especificar los atributos de plan:
        • Fijo: seleccione esta casilla de control para indicar que los planes cargados son planes fijos. Un plan fijo es un plan aceptado marcado como preferido, por lo que el optimizador solo tiene en cuenta los planes fijos de la línea base.
        • Activado: seleccione esta casilla de control para indicar que los planes cargados son planes activados. Un plan activado es apto para su uso por parte del optimizador de Oracle.
    2. Seleccione una de las opciones disponibles en la lista desplegable Tipo de credencial de la sección Credenciales para especificar las credenciales de base de datos para conectarse a la base de datos gestionada. Para obtener información sobre los tipos de credenciales, consulte Uso de credenciales para realizar tareas de diagnóstico y gestión.
    3. Haga clic en Guardar cambios.

Realización de las tareas de configuración del SPM

Puede realizar tareas de configuración de SPM, como activar o desactivar la línea base de plan SQL y los parámetros de captura automática de plan en el separador Configuración.

El separador Configuración tiene las siguientes secciones principales:

  • Línea base de plan SQL: proporciona las opciones para activar o desactivar la línea base de plan SQL. Si la línea base del plan SQL está activada, se muestran los siguientes parámetros en esta sección y, para realizar cambios en ellos, haga clic en Editar:
    • Retención de plan (semanas): número de semanas para retener planes SQL no utilizados antes de depurarlos. El período puede oscilar entre 5 y 523 semanas y el valor predeterminado es 53 semanas.
    • Presupuesto de espacio (%): porcentaje máximo de espacio SYSAUX que puede utilizar la base de gestión SQL. El rango permitido para este límite está entre 1% y 50% y el valor por defecto es 10%.
  • Captura automática de planes: proporciona las opciones para activar o desactivar la captura automática de planes. Un filtro automático permite capturar solo las sentencias que desee y excluir las sentencias no críticas. Esta técnica ahorra espacio en el tablespace SYSAUX.

    Si la captura automática del plan está activada, se muestran los siguientes filtros en esta sección y, para realizar cambios en ellos, haga clic en Editar:

    • Acciones para incluir o Acciones para excluir: acciones que se incluirán o excluirán de la captura automática.
    • Módulos que incluir o Módulos que excluir: módulos que se incluirán o excluirán de la captura automática.
    • Nombres de esquema de análisis a incluir o Nombres de esquema de análisis a excluir: nombres de esquema de análisis que se van a incluir o excluir de la captura automática.
    • Texto SQL a incluir o Texto SQL a excluir: texto SQL que se va a incluir o excluir de la captura automática.
    Nota

    La captura de plan selectivo (filtros) solo está disponible para las bases de datos Oracle versión 12.2 y posteriores.
  • Tarea del asesor de mejora automática de SPM: proporciona las opciones para activar o desactivar la tarea del asesor de mejora automática de SPM. Si la tarea Automatic SPM Evolve Advisor está activada, se muestran los siguientes parámetros en esta sección y, para realizar cambios en ellos, haga clic en Editar:
    • Tarea del asesor de mejora automática de SPM de alta frecuencia: muestra si la tarea del asesor de mejora automática de SPM está activada para que se produzca con más frecuencia.
    • Orígenes de plan alternativos: orígenes para buscar planes adicionales.
    • Líneas base de plan alternativo: planes alternativos que se deben cargar. El valor por defecto es Existente.
    • Límite de plan alternativo: número máximo de planes que se van a cargar en total. El valor por defecto es Ilimitado.
    • Aceptar planes automáticamente: muestra si los planes recomendados se deben aceptar automáticamente.
    • Límite de tiempo permitido (seg): el límite de tiempo global en segundos. Es el tiempo total permitido para la tarea.
    Nota

    • Para las bases de datos autónomas, no se pueden realizar tareas de configuración de gestión de plan SQL.
    • Se necesitan privilegios SYSDBA para editar los parámetros de la tarea del asesor de evolución automática de SPM.
    • La tarea del asesor de evolución automática de SPM solo está disponible para las bases de datos Oracle versión 12.2 y posteriores.
    • La tarea Automatic SPM Evolve Advisor de alta frecuencia solo está disponible para bases de datos Oracle 19c y posteriores que se ejecuten en la plataforma Oracle Exadata.