Usa SQL Tracing su Autonomous Database sull'infrastruttura Exadata dedicata

Puoi utilizzare SQL Tracing con Autonomous Database sull'infrastruttura Exadata dedicata per identificare l'origine di un carico di lavoro eccessivo del database, ad esempio un'istruzione SQL con carichi elevati nell'applicazione.

Informazioni su SQL Tracing

Quando un'operazione dell'applicazione richiede più tempo del previsto, il recupero di una traccia di tutte le istruzioni SQL eseguite nell'ambito di questa operazione con dettagli quali il tempo impiegato da tale istruzione SQL nelle fasi di analisi, esecuzione e recupero consentirà di identificare e risolvere la causa del problema di prestazioni. Per ottenere questo risultato, puoi utilizzare il trace SQL su un Autonomous Database.

Il trace SQL è disabilitato per impostazione predefinita in Autonomous Database. È necessario abilitarlo per iniziare a raccogliere i dati di trace SQL. Per tracciare le istruzioni SQL, implementare i task riportati di seguito come utente ADMIN.

Configurare SQL Tracing su Autonomous Database

Per configurare Autonomous Database per il trace SQL, effettuare le operazioni riportate di seguito.
  1. Creare un bucket per memorizzare i file di trace nello storage degli oggetti cloud.
    Per salvare i file di trace SQL, il bucket può trovarsi in qualsiasi area di memorizzazione degli oggetti cloud supportata da Autonomous Database. Ad esempio, per creare un bucket in Oracle Cloud Infrastructure Object Storage, fare riferimento a Creazione di un bucket.

    Suggerimento

    Assicurarsi di scegliere Standard come livello di storage durante la creazione del bucket in Oracle Cloud Infrastructure Object Storage perché i file di trace SQL sono supportati solo con i bucket creati nel livello di storage standard. Per informazioni sul livello di storage degli oggetti standard, vedere Informazioni sui livelli di storage.
  2. Creare una credenziale per l'account di storage degli oggetti cloud utilizzando DBMS_CLOUD.CREATE_CREDENTIAL.
    Ad esempio:
    BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username => 'adb_user@example.com', 
    password => 'password'
);END;
/

    Vedere CREATE_CREDENTIAL Procedura per i dettagli sugli argomenti per i parametri username e password per i diversi servizi di storage degli oggetti.

  3. Impostare i parametri di inizializzazione per specificare l'URL di storage degli oggetti cloud per un bucket per i file di trace SQL e per specificare le credenziali per accedere allo storage degli oggetti cloud.
    1. Impostare la proprietà del database DEFAULT_LOGGING_BUCKET per specificare il bucket di log nello storage degli oggetti cloud.
      Ad esempio, se crei il bucket con lo storage degli oggetti Oracle Cloud Infrastructure (OCI):
      SET DEFINE OFF;
ALTER DATABASE PROPERTY SET 
   DEFAULT_LOGGING_BUCKET = 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/bucket_name/o/';

      Dove namespace-string è lo spazio di nomi dello storage degli oggetti OCI e bucket_name è il nome del bucket creato in precedenza. Per ulteriori informazioni, vedere Informazioni sugli spazi di nomi dello storage degli oggetti.

      Per un elenco di aree, consulta la sezione relativa alle aree e ai domini di disponibilità.

      L'area di memorizzazione degli oggetti cloud utilizzata per i file di trace SQL può essere qualsiasi area di memorizzazione degli oggetti cloud supportata da Autonomous Database.

    2. Impostare la proprietà del database DEFAULT_CREDENTIAL sulla credenziale creata nel passo 2.
      Ad esempio:
      ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.DEF_CRED_NAME';

      È necessario includere il nome dello schema con la credenziale. In questo esempio lo schema è ADMIN.

Abilita SQL Tracing su Autonomous Database

Nota

L'abilitazione del trace SQL può compromettere le prestazioni dell'applicazione per la sessione mentre la raccolta di trace è abilitata. Questo impatto sulle prestazioni è previsto a causa del sovraccarico dovuto alla raccolta e al salvataggio dei dati di trace.

Per abilitare il trace SQL per una sessione di database, effettuare le operazioni riportate di seguito.

  1. Se lo si desidera, impostare un identificativo client per l'applicazione. Questo passo è facoltativo ma consigliato. SQL Tracing utilizza l'identificativo client come componente del nome del file di trace quando il file di trace viene scritto nell'area di memorizzazione degli oggetti cloud.
    Ad esempio:
    BEGIN
  DBMS_SESSION.SET_IDENTIFIER('sqlt_test');
END;
/
  2. Se lo si desidera, impostare un nome di modulo per l'applicazione. Questo passo è facoltativo ma consigliato. SQL Tracing utilizza il nome del modulo come componente del nome del file di trace quando il file di trace viene scritto nell'area di memorizzazione degli oggetti cloud.

    Ad esempio:

    BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('modname', null);
END;
/
  3. Abilitare la funzione SQL Trace.
    ALTER SESSION SET SQL_TRACE = TRUE;
  4. Eseguire il carico di lavoro.
    Questo passo implica l'esecuzione dell'intera applicazione o di parti specifiche dell'applicazione. Durante l'esecuzione del carico di lavoro nella sessione del database, vengono raccolti i dati di trace SQL.
  5. Disabilita trace SQL.
    Quando si disabilita il trace SQL, i dati raccolti per la sessione vengono scritti in una tabella della sessione e in un file di trace nel bucket configurato quando si imposta il trace SQL.

Disabilita trace SQL

Per disabilitare il trace SQL, effettuare le operazioni riportate di seguito.
  1. Disabilitare la funzione SQL Trace.
    ALTER SESSION SET SQL_TRACE = FALSE;
  2. Facoltativamente, in base alle esigenze per l'ambiente in uso, potrebbe essere necessario reimpostare la proprietà del database DEFAULT_LOGGING_BUCKET per cancellare il valore del bucket di log nello storage degli oggetti cloud.
    Ad esempio:
    ALTER DATABASE PROPERTY SET DEFAULT_LOGGING_BUCKET = '';
Quando si disabilita il trace SQL, i dati di trace raccolti durante l'esecuzione della sessione con il trace abilitato vengono copiati in una tabella e inviati a un file di trace nell'area di memorizzazione degli oggetti cloud.

Visualizza file di trace salvato nell'area di memorizzazione degli oggetti cloud in Autonomous Database

I dati dei file di trace SQL vengono utilizzati per analizzare le prestazioni dell'applicazione in Autonomous Database. Quando si disabilita SQL Trace nella sessione del database, i dati vengono scritti nel bucket dell'area di memorizzazione degli oggetti cloud configurato con DEFAULT_LOGGING_BUCKET.

La funzione SQL Trace scrive i dati di trace raccolti nella sessione nell'area di memorizzazione degli oggetti cloud nel seguente formato:

default_logging_bucket/sqltrace/clientID/moduleName/sqltrace_numID1_numID2.trc

Di seguito sono riportati i componenti del nome file.

  • default_logging_bucket: è il valore della proprietà del database DEFAULT_LOGGING_BUCKET. Per ulteriori informazioni, vedere Configura SQL Tracing su Autonomous Database.

  • clientID: è l'identificativo del client. Per ulteriori informazioni, vedere Abilita SQL Tracing su Autonomous Database.

  • moduleName: è il nome del modulo. Per ulteriori informazioni, vedere Abilita SQL Tracing su Autonomous Database.

  • numID1_numID2: sono due identificativi forniti dalla funzione SQL Trace. I valori numerici numID1 e numID2 distinguono in modo univoco ogni nome file di trace dalle altre sessioni utilizzando il trace e la creazione di file di trace nello stesso bucket nello storage degli oggetti cloud.

    Quando il servizio di database supporta il parallelismo e una sessione esegue una query parallela, la funzione SQL Trace può produrre più file di trace con valori numID1 e numID2 diversi.

Nota

Quando il trace SQL è abilitato e disabilitato più volte all'interno della stessa sessione, ogni iterazione di trace genera un file di trace separato nell'area di memorizzazione degli oggetti cloud. Per evitare di sovrascrivere i trace precedenti generati nella sessione, i file generati successivamente seguono la stessa convenzione di denominazione e aggiungono un suffisso numerico al nome del file di trace. Questo suffisso numerico inizia con il numero 1 ed è incrementato di 1 per ogni iterazione di trace in seguito.

Ad esempio, il nome di un file di trace generato di esempio quando si imposta l'identificativo client su "sql_test" e il nome del modulo su "modname":

sqltrace/sqlt_test/modname/sqltrace_5415_56432.trc

È possibile eseguire TKPROF per tradurre il file di trace in un file di output leggibile.

  1. Copiare il file di trace dall'area di memorizzazione degli oggetti nel sistema locale.
  2. Passare alla directory in cui viene salvato il file di trace.
  3. Eseguire la utility TKPROF dal prompt del sistema operativo utilizzando la sintassi seguente:
    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]

    I file di input e di output sono gli unici argomenti richiesti.

  4. Per visualizzare la Guida in linea, richiamare TKPROF senza argomenti.
    Per informazioni sull'uso della utility TKPROF, vedere Tools for End-to-End Application Tracing in Oracle Database 19c SQL Tuning Guide o Oracle Database 23ai SQL Tuning Guide.

Visualizza i dati di trace nella vista SESSION_CLOUD_TRACE su Autonomous Database

Quando si abilita SQL Tracing, le stesse informazioni di trace salvate nel file di trace nell'area di memorizzazione degli oggetti cloud sono disponibili nella vista SESSION_CLOUD_TRACE nella sessione in cui è stato abilitato il trace.
Mentre ci si trova ancora nella sessione del database, è possibile visualizzare i dati di trace SQL nella vista SESSION_CLOUD_TRACE. La vista SESSION_CLOUD_TRACE include due colonne: ROW_NUMBER e TRACE.
DESC SESSION_CLOUD_TRACE

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

ROW_NUMBER specifica l'ordinamento per i dati di trace trovati nella colonna TRACE. Ogni riga di output di trace scritta in un file di trace diventa una riga nella tabella ed è disponibile nella colonna TRACE.

Dopo aver disabilitato il trace SQL per la sessione, è possibile eseguire le query nella vista SESSION_CLOUD_TRACE.

Ad esempio:
SELECT trace FROM SESSION_CLOUD_TRACE ORDERBY row_number;

I dati in SESSION_CLOUD_TRACE persistono per tutta la durata della sessione. Dopo il logout o la chiusura della sessione, i dati non sono più disponibili.

Se SQL Trace è abilitato e disabilitato più volte all'interno della stessa sessione, SESSION_CLOUD_TRACE mostra cumulativamente i dati di trace per tutte le iterazioni. Pertanto, la riabilitazione del trace in una sessione dopo la disabilitazione precedente del trace non rimuove i dati di trace prodotti dall'iterazione precedente.