Documentazione di Oracle Cloud Infrastructure

Utilizzare SPM per gestire i piani di esecuzione SQL

È possibile utilizzare SQL Plan Management (SPM) per assicurarsi che le prestazioni di runtime di un'istruzione SQL non si deteriorino a causa delle modifiche apportate al piano di esecuzione SQL (piano SQL).

SPM è un meccanismo preventivo che consente all'ottimizzatore Oracle di gestire automaticamente i piani SQL, garantendo che il database utilizzi solo piani noti o verificati. Le prestazioni di qualsiasi applicazione di database si basano pesantemente sull'esecuzione coerente delle istruzioni SQL. Il piano di esecuzione di un'istruzione SQL può cambiare in modo imprevisto per una serie di motivi, ad esempio la raccolta delle statistiche dell'optimizer, le modifiche ai parametri dell'optimizer o alle definizioni di schema o metadati. SPM fornisce "stabilità del piano" attraverso un framework che preserva i piani SQL correnti durante le modifiche all'ambiente, ma consente modifiche solo per piani migliori. Quando viene trovato un nuovo piano SQL per un'istruzione SQL, non verrà usato finché non viene verificato che le prestazioni sono uguali o migliori di quelle del piano corrente.

SPM utilizza un meccanismo proattivo denominato Baseline del piano SQL, ovvero un set di piani SQL accettati che l'ottimizzatore Oracle può utilizzare per un'istruzione SQL. Utilizzando le linee di base, SPM previene le regressioni dei piani dalle modifiche ambientali, consentendo al contempo all'ottimizzatore di scoprire e utilizzare piani migliori.

I principali componenti di SPM sono:

  • Acquisizione piano: tecniche per l'acquisizione e la memorizzazione di informazioni pertinenti sui piani nella base di gestione SQL per un set di istruzioni SQL. Acquisire un piano implica rendere SPM consapevole del piano e può essere fatto attraverso:
    • Acquisizione automatica del piano: se abilitata, il database controlla se le istruzioni SQL eseguite sono idonee per l'acquisizione automatica. Per essere idoneo per l'acquisizione automatica del piano, un'istruzione eseguita deve essere ripetibile e non deve essere esclusa da alcun filtro di acquisizione.
    • Acquisizione del piano manuale: caricamento di massa avviato dall'utente dei piani di esecuzione esistenti per le istruzioni SQL in una baseline del piano SQL.
  • Selezione piano: la capacità di ottimizzazione Oracle di rilevare le modifiche al piano in base alla cronologia del piano memorizzato e l'uso delle baseline del piano SQL per selezionare i piani in modo da evitare potenziali regressioni delle prestazioni.
  • Evoluzione del piano: il processo di aggiunta di nuovi piani alle baseline del piano SQL esistenti, manualmente o automaticamente. L'ottimizzatore Oracle verifica i nuovi piani e li aggiunge a una baseline del piano SQL esistente.

Per ulteriori informazioni su SPM e sui relativi componenti, vedere Overview of SQL Plan Management in Oracle Database SQL Tuning Guide.

Per utilizzare SPM, andare alla pagina Dettagli database gestiti e fare clic su Gestione piano SQL nel riquadro a sinistra in Risorse. È possibile eseguire i task SPM riportati di seguito nella diagnostica e gestione di Gestione database.

  • Gestire le baseline del piano SQL.
  • Sottomettere i task per caricare i piani SQL nelle baseline del piano SQL.
  • Eseguire task di configurazione quali l'abilitazione, la disabilitazione o la modifica della baseline del piano SQL, l'acquisizione automatica del piano e i parametri dei task di Automatic SPM Evolve Advisor.

Privilegi necessari per eseguire task SPM

Nella tabella seguente sono elencati i task SPM e i privilegi necessari per eseguirli.

Nota

Qualsiasi utente a cui è stato concesso il privilegio ADMINISTER SQL MANAGEMENT OBJECT può eseguire il pacchetto DBMS_SPM.
Attività Privilegi necessari
Modificare uno o più attributi di un singolo piano SQL o di tutti i piani associati a un'istruzione SQL. privilegio EXECUTE sul pacchetto SYS.DBMS_SPM.
Modificare il limite di spazio su disco per la base di gestione SQL. privilegio EXECUTE sul pacchetto SYS.DBMS_SPM.
Modificare il periodo di conservazione dei piani SQL non utilizzati. privilegio EXECUTE sul pacchetto SYS.DBMS_SPM.
Configura i filtri di acquisizione automatica. privilegio EXECUTE sul pacchetto SYS.DBMS_SPM.
Configurare il task dell'advisor di evoluzione automatica SPM. privilegio EXECUTE sul pacchetto SYS.DBMS_SPM.

Nota: solo l'utente SYS può configurare il task di Evolve Advisor SPM automatico, SYS_AUTO_SPM_EVOLVE_TASK.

Disabilita l'acquisizione automatica del piano. ALTER SYSTEM privilege
Disabilitare il task dell'advisor di evoluzione automatica SPM. privilegio EXECUTE sul pacchetto SYS.DBMS_AUTO_TASK_ADMIN.
Disabilitare il task dell'advisor di evoluzione automatica SPM ad alta frequenza. privilegio EXECUTE sul pacchetto SYS.DBMS_SPM.
Disabilita l'uso delle baseline del piano SQL memorizzate nella base di gestione SQL. ALTER SYSTEM privilege
Eliminare un singolo piano SQL o tutti i piani associati a un'istruzione SQL. privilegio EXECUTE sul pacchetto SYS.DBMS_SPM.
Abilita l'acquisizione automatica del piano. ALTER SYSTEM privilege
Abilitare il task dell'advisor di evoluzione automatica SPM. privilegio EXECUTE sul pacchetto SYS.DBMS_AUTO_TASK_ADMIN.
Abilitare il task dell'advisor di evoluzione automatica SPM ad alta frequenza. privilegio EXECUTE sul pacchetto SYS.DBMS_SPM.
Abilita l'uso delle baseline del piano SQL memorizzate nella base di gestione SQL. ALTER SYSTEM privilege
Caricare i piani SQL da snapshot AWR. privilegio EXECUTE sui pacchetti SYS.DBMS_SPM e SYS.DBMS_SCHEDULER.
Carica piani SQL dalla cursor cache. privilegio EXECUTE sui pacchetti SYS.DBMS_SPM e SYS.DBMS_SCHEDULER.
Visualizzare i dettagli della configurazione della baseline del piano SQL. Privilegio SELECT o READ per le seguenti viste:
  • SYS.DBA_SQL_MANAGEMENT_CONFIG
  • SYS.V_$SYSAUX_OCCUPANTS
  • SYS.V_$SYSTEM_PARAMETER2
  • SYS.DBA_ADVISOR_PARAMETERS
  • SYS.DBA_AUTOTASK_CLIENT
Visualizzare i dettagli della baseline del piano SQL.
  • Privilegio SELECT o READ nella vista SYS.DBA_SQL_PLAN_BASELINES.
  • Privilegi necessari per eseguire l'istruzione SQL per la quale si desidera ottenere il piano.
  • privilegio EXECUTE sul pacchetto SYS.DBMS_XPLAN.
Visualizzare le baseline del piano SQL. Privilegio SELECT o READ nella vista SYS.DBA_SQL_PLAN_BASELINES.
Visualizzare i job sottomessi per caricare le baseline del piano SQL. Privilegio SELECT o READ nella vista SYS.DBA_SCHEDULER_JOBS.
Visualizzare il numero di baseline del piano SQL aggregate in base ai relativi attributi. Privilegio SELECT o READ nella vista SYS.DBA_SQL_PLAN_BASELINES.
Visualizzare il numero di baseline del piano SQL aggregate in base all'ultima esecuzione. Privilegio SELECT o READ nella vista SYS.DBA_SQL_PLAN_BASELINES.
Visualizzare le istruzioni SQL dalla cursor cache. Privilegio SELECT o READ nella vista SYS.V_$SQL.

Gestisci baseline piano SQL

È possibile gestire le baseline del piano SQL nella scheda Basi del piano SQL.

Nella parte superiore della scheda Basi del piano SQL vengono visualizzate le caselle riportate di seguito.

  • Riepilogo: visualizza il numero totale di baseline del piano SQL e indica se sono abilitati i task Baseline del piano SQL, Acquisizione automatica del piano e Evolve Advisor SPM automatico. Nella casella Riepilogo è possibile abilitare o disabilitare i task Baseline del piano SQL, Acquisizione automatica del piano e Evolve Advisor SPM automatico facendo clic sui pulsanti Abilita o Disabilita e fornendo le credenziali del database.
  • Ultime esecuzioni di base: visualizza il numero di baseline del piano SQL in base all'ultima esecuzione. Nella casella Ultime esecuzioni di base passare il puntatore del mouse sul grafico a torta per visualizzare ulteriori dettagli e filtrare i dati visualizzati nel grafico facendo clic sulle opzioni del periodo di tempo elencate nella legenda.
  • Statistiche del piano SQL: visualizza i piani SQL suddivisi per le seguenti statistiche:
    • Abilitato: piani SQL idonei per l'uso da parte dell'ottimizzatore Oracle.
    • Accettato: i piani SQL presenti nelle baseline del piano SQL e quindi disponibili per l'uso da parte dell'ottimizzatore Oracle.
    • Riprodotti: piani SQL riprodotti dall'ottimizzatore Oracle.
    • Fisso: piani SQL accettati contrassegnati come preferiti, in modo che l'ottimizzatore Oracle consideri solo questi piani nella baseline del piano SQL.
    • Rimozione automatica: i piani SQL configurati per la rimozione automatica dopo il periodo di conservazione predefinito.

    Nella casella Statistiche piano SQL, passare il puntatore del mouse sul grafico a barre orizzontale per visualizzare ulteriori dettagli e filtrare i dati visualizzati nel grafico facendo clic sulle opzioni elencate nella legenda.

La sezione Piani SQL elenca i piani SQL eseguiti con dettagli aggiuntivi, ad esempio l'ultima esecuzione di un piano SQL, l'abilitazione, l'accettazione, la riproduzione e così via e la relativa origine. Per filtrare la lista:

  • Fare clic su una sezione del grafico a torta nella casella Ultime esecuzioni di base per filtrare in base all'ora dell'ultima esecuzione.
  • Fare clic su una barra nella casella Statistiche piano SQL per filtrare i piani in base al fatto che siano abilitati, accettati, riprodotti, corretti o configurati per la rimozione automatica.

È inoltre possibile utilizzare il campo di ricerca per eseguire la ricerca in base al testo SQL, al nome del piano o all'origine.

Nella sezione Piani SQL è possibile:

  • Fare clic sul collegamento al piano SQL nella colonna Testo SQL per visualizzare il piano SQL.
  • Fare clic sull'icona Azioni (Azioni) per un'istruzione SQL e utilizzare le opzioni riportate di seguito nel menu.
    • Modifica attributi delle istruzioni SQL: fare clic per modificare gli attributi delle istruzioni SQL. Le modifiche apportate agli attributi dell'istruzione SQL interesseranno tutti i piani SQL associati.
    • Elimina istruzione SQL: fare clic per eliminare l'istruzione SQL. Tenere presente che l'eliminazione dell'istruzione SQL eliminerà i piani SQL associati.
  • Fare clic sull'icona Azioni (Azioni) per un piano SQL e utilizzare le opzioni riportate di seguito nel menu.
    • Visualizza dettagli: fare clic per visualizzare il piano SQL.
    • Modifica attributi: fare clic per impostare o modificare i seguenti attributi del piano SQL.
      • Rimuovi automaticamente: selezionare questa casella di controllo per rimuovere (eliminare) automaticamente il piano SQL dopo il periodo di conservazione specificato.
      • Abilitato: selezionare questa casella di controllo per indicare che il piano SQL è un piano abilitato.
      • Fisso: selezionare questa casella di controllo per indicare che il piano SQL è un piano fisso.
    • Elimina: fare clic per eliminare il piano SQL dalla baseline del piano SQL.

Carica piani SQL

È possibile sottomettere un task per caricare i piani SQL nelle baseline del piano SQL nella scheda Carica piani SQL.

È possibile caricare i piani SQL dalle seguenti origini.

  • AWR: carica i piani dagli snapshot del repository del carico di lavoro automatico (AWR). Per informazioni, vedere Carica piani SQL da AWR.
    Nota

    Il supporto per il caricamento dei piani SQL da AWR è disponibile solo per i database Oracle versione 12.2 e successive.
  • Cursor cache: carica i piani dall'area SQL condivisa (cursor cache). Per informazioni, vedere Carica piani SQL da Cursor Cache.

Carica piani SQL da AWR

  1. Nella sezione Gestione piano SQL, fare clic sulla scheda Carica piani SQL.
  2. Nell'elenco a discesa Carica piano SQL da, selezionare AWR e fare clic su Carica.
  3. Nel pannello Carica piani SQL da AWR:
    1. Fornire le informazioni riportate di seguito per sottomettere il task nella sezione Generale.
      1. Nome task: rivedere il nome popolato automaticamente del task e modificarlo, se necessario.
      2. Descrizione: è possibile immettere una descrizione per il task.
      3. Inizia snapshot: immettere il numero dello snapshot iniziale nell'intervallo e selezionarlo dall'elenco a discesa.
      4. Snapshot finale: immettere il numero dello snapshot finale nell'intervallo e selezionarlo dall'elenco a discesa.
      5. Filtro testo SQL: è possibile immettere il testo SQL solo per caricare i piani che soddisfano i criteri di filtro. Se non viene fornito alcun valore, vengono selezionati tutti i piani compresi nell'intervallo di snapshot specificato in AWR.
      6. Attributi piano: selezionare le caselle di controllo riportate di seguito per specificare gli attributi del piano.
        • Fisso: selezionare questa casella di controllo per indicare che i piani caricati sono fissi.
        • Abilitato: selezionare questa casella di controllo per indicare che i piani caricati sono piani abilitati.
    2. Selezionare una delle opzioni disponibili nell'elenco a discesa Tipo di credenziale nella sezione Credenziali per specificare le credenziali del database per la connessione al database gestito. Per informazioni sui tipi di credenziali, vedere Usa credenziali per eseguire task di diagnostica e gestione.
    3. Fare clic su Salva modifiche.

Carica piani SQL da Cursor Cache

  1. Nella sezione Gestione piano SQL, fare clic sulla scheda Carica piani SQL.
  2. Nell'elenco a discesa Carica piano SQL da, selezionare Cursore cache e fare clic su Carica.
  3. Nel pannello Carica piani SQL dalla cursor cache:
    1. Fornire le informazioni riportate di seguito per sottomettere il task nella sezione Generale.
      1. Nome task: rivedere il nome popolato automaticamente del task e modificarlo, se necessario.
      2. Descrizione: è possibile immettere una descrizione per il task.
      3. Recupera baseline mediante: utilizzare una delle seguenti opzioni per caricare il piano SQL:
        • ID SQL: selezionare questo pulsante di scelta per identificare l'istruzione SQL nella cursor cache di cui si desidera caricare i piani SQL. Quando si seleziona questo pulsante di opzione, vengono visualizzati i campi riportati di seguito.
          • ID SQL: immettere l'ID dell'istruzione SQL.
          • Valore hash piano: è possibile immettere il valore hash piano del piano SQL. Se non viene fornito alcun valore, vengono caricati tutti i piani presenti nella cursor cache per l'istruzione SQL.
          • Parametro utilizzato per identificare la baseline del piano SQL in cui vengono caricati i piani: è possibile selezionare i pulsanti di opzione Testo SQL o Maniglia SQL e immettere il valore del parametro nel campo Valore parametro. Se non viene fornito alcun valore, il testo dell'istruzione SQL identificata viene estratto dalla cache del cursore e utilizzato per identificare la baseline del piano SQL in cui vengono caricati i piani. Se la baseline del piano SQL non esiste, viene creata.
        • Nome filtro: selezionare questo pulsante di scelta per specificare il filtro per identificare un'istruzione SQL o un set di istruzioni SQL. Quando si seleziona questo pulsante di opzione, vengono visualizzati i campi riportati di seguito.
          • Nome filtro: selezionare un nome di filtro dall'elenco a discesa.
          • Valore filtro: immettere il valore di filtro corrispondente.
      4. Attributi piano: selezionare le caselle di controllo riportate di seguito per specificare gli attributi del piano.
        • Fisso: selezionare questa casella di controllo per indicare che i piani caricati sono fissi. Un piano fisso è un piano accettato contrassegnato come preferito, in modo che l'ottimizzatore consideri solo i piani fissi nella baseline.
        • Abilitato: selezionare questa casella di controllo per indicare che i piani caricati sono piani abilitati. Un piano abilitato è idoneo per l'utilizzo da parte dell'ottimizzatore Oracle.
    2. Selezionare una delle opzioni disponibili nell'elenco a discesa Tipo di credenziale nella sezione Credenziali per specificare le credenziali del database per la connessione al database gestito. Per informazioni sui tipi di credenziali, vedere Usa credenziali per eseguire task di diagnostica e gestione.
    3. Fare clic su Salva modifiche.

Eseguire i task di configurazione SPM

È possibile eseguire task di configurazione SPM, ad esempio abilitare o disabilitare i parametri di acquisizione automatica e baseline del piano SQL nella scheda Configurazione.

La scheda Configurazione contiene le sezioni principali riportate di seguito.

  • Base di riferimento del piano SQL: fornisce le opzioni per abilitare o disabilitare la baseline del piano SQL. Se la baseline del piano SQL è abilitata, in questa sezione vengono visualizzati i seguenti parametri e per apportarvi modifiche, fare clic su Modifica:
    • Conservazione piano (settimane): il numero di settimane per la conservazione dei piani SQL inutilizzati prima della rimozione. Il periodo può variare da 5 a 523 settimane e l'impostazione predefinita è 53 settimane.
    • Budget spazio (%): la percentuale massima di spazio SYSAUX che la base di gestione SQL può utilizzare. L'intervallo consentito per questo limite è compreso tra l'1% e il 50% e il valore predefinito è 10%.
  • Acquisizione automatica del piano: fornisce le opzioni per abilitare o disabilitare l'acquisizione automatica del piano. Un filtro automatico consente di acquisire solo le istruzioni desiderate ed escludere le istruzioni non critiche. Questa tecnica consente di risparmiare spazio nella tablespace SYSAUX.

    Se l'acquisizione automatica del piano è abilitata, in questa sezione vengono visualizzati i seguenti filtri e per apportarvi modifiche, fare clic su Modifica:

    • Azioni da includere o Azioni da escludere: le azioni da includere o escludere dall'acquisizione automatica.
    • Moduli da includere o Moduli da escludere: i moduli da includere o escludere dall'acquisizione automatica.
    • Nomi schema di analisi da includere o Nomi schema di analisi da escludere: i nomi dello schema di analisi da includere o escludere dall'acquisizione automatica.
    • Testo SQL da includere o Testo SQL da escludere: il testo SQL da includere o escludere dall'acquisizione automatica.
    Nota

    L'acquisizione del piano selettivo (filtri) è disponibile solo per i database Oracle versione 12.2 e successive.
  • Task dell'advisor di evoluzione automatica SPM: fornisce le opzioni per abilitare o disabilitare il task dell'advisor di evoluzione automatica SPM. Se il task dell'advisor di evoluzione automatica SPM è abilitato, in questa sezione vengono visualizzati i parametri riportati di seguito e per apportarvi modifiche, fare clic su Modifica.
    • Task dell'advisor di evoluzione automatica SPM ad alta frequenza: visualizza se il task dell'advisor di evoluzione automatica SPM è abilitato per l'esecuzione più frequente.
    • Origini piano alternativo: le origini per cercare piani aggiuntivi.
    • Baseline piano alternativo: i piani alternativi che devono essere caricati. Il valore predefinito è Existing.
    • Limite piano alternativo: il numero massimo di piani da caricare in totale. Il valore predefinito è Unlimited.
    • Accetta automaticamente i piani: visualizza se i piani consigliati devono essere accettati automaticamente.
    • Limite di tempo consentito (sec): il limite di tempo globale in secondi. Tempo totale consentito per il task.
    Nota

    • Per i database autonomi, non è possibile eseguire task di configurazione di gestione del piano SQL.
    • Per modificare i parametri del task Evolve Advisor SPM automatico sono necessari i privilegi SYSDBA.
    • Il task di Evolve Advisor SPM automatico è disponibile solo per i database Oracle versione 12.2 e successive.
    • Il task dell'advisor di evoluzione automatica SPM ad alta frequenza è disponibile solo per i database Oracle 19c e versioni successive in esecuzione sulla piattaforma Oracle Exadata.