Incident Oracle. Blocco applicativo causato da query inefficiente - Analisi e soluzioni

Questo articolo tecnico descrive un incident su un database Oracle, le cause (una query senza indici), e le soluzioni adottate per ripristinare le performance e prevenire il ripetersi del problema.

Il presente articolo si prefigge di spiegare un caso in cui abbiamo risolto un incident legato ad un database Oracle RAC. Il database in questione, formato da tre nodi in lettura e scrittura per garantire l’High Availability, gestito da due applicativi che erogano due servizi diversi, viene utilizzato da un elevato numero di utenti finali per ricevere delle prestazioni. Gli utenti possono essere fruitori delle prestazioni oppure erogatori delle prestazioni. Va da sé che un fermo operativo di questo database comporta un blocco totale delle applicazioni che vi poggiano sopra, rendendo impossibile per gli utenti sia erogare che ricevere servizi a causa dell’eccessiva lentezza delle interfacce. Di conseguenza, anche un disservizio di pochi minuti potrebbe causare significativi accodamenti e generare un notevole malcontento tra gli utenti.

Il Contesto. L'inizio dell'incident

Il problema è iniziato quando è stata lanciata una query che ha modificato il piano per alcune esecuzioni. Ciò ha provocato un blocco sulla tabella, che ha comportato l’accodamento di altre query che necessitavano di accedervi. Questa situazione ha generato moltissime connessioni appese al database. L’accumulo di sessioni ha raggiunto la soglia massima supportata dal database (3872 sessioni), provocando di conseguenza il fermo totale dell’intera ’applicazione.
 


 

Nella tabella si vede la situazione al momento dell’incident. La colonna MACHINE indica le macchine da cui arrivavano le connessioni.  Nella colonna COUNT(*) viene riportato il numero di connessioni che quelle macchine avevano stabilito. Il nome delle macchine è stato oscurato per mantenere la privacy del cliente

 

Analisi dell'incident

Con questa situazione drastica, si è iniziato subito a cercare la query. La query non era immediatamente identificabile in quanto non utilizzava le bind variables. Di conseguenza, a livello di database erano visibili molte query con identificativi diversi benché fossero riferite alla medesima interfaccia applicativa. Analizzando più a fondo, però, si è identificato che queste query accedevano tutte alla stessa tabella ed è stato, così, possibile trovare quella che ha causato il problema. Questa query leggeva una tabella di 13GB senza utilizzare indici. Alcuni degli identificativi (65 sessioni) avevano preso un piano errato che faceva risolvere le query molto lentamente, provocando il lock della tabella, l’accumulo di sessioni e, di conseguenza, il blocco applicativo.
 

La Soluzione

Prima di risolvere il problema era necessario trovare il piano di esecuzione che risultava errato. La query che ci ha permesso di individuarlo è stata la seguente:

select min(sample_time), max(sample_time) from dba_hist_active_sess_history where sample_time > sysdate -2 and SQL_PLAN_HASH_VALUE='123456789';

Successivamente, abbiamo cercato gli identificativi (65) che usavano questo explain plan con un’altra query, questa:

select distinct sql_id from dba_hist_sqlstat where plan_hash_value='123456789';

Grazie al risultato ottenuto con le query precedenti, siamo quindi riusciti ad ottenere un piano completo utilizzando una terza query:

select * from table(DBMS_XPLAN.DISPLAY_AWR('&SQL_ID','&PLAN_HASH_VALUE',format=>'ADAPTIVE +PEEKED_BINDS'));

Quando questa query viene eseguita il prompt chiede all’utente di fornire i valori per le variabili  SQL_ID e PLAN_HASH_VALUE

 
Enter value for sql_id: 8aaaaaaa8aaaa
Enter value for plan_hash_value: <piano_errato>
 


 

Nell’immagine il piano di esecuzione errato. Anche in questo caso i nomi delle tabelle sono stati oscurati per la privacy del cliente

Come si può notare dalla colonna Rows, la query leggeva moltissime righe ad ogni esecuzione. Questo ne causava la lentezza nella sua risoluzione.
Per sistemare il problema, era, quindi, necessario forzare il cambio di piano di esecuzione. Con le query viste in precedenza, è stato possibile trovare l’explain plan corretto e, con il comando seguente obbligare il database ad utilizzarlo.

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => ‘8aaaaaaa8aaaa’, plan_hash_value => <piano_corretto> , FIXED=>’YES’, ENABLED=>’YES’);

Nell’immagine che segue si vede il piano di esecuzione corretto (*I nomi delle tabelle sono stati oscurati per la privacy del cliente)

In questo modo, le righe lette dalla query, ora, sono nettamente di meno.

Per impedire il ripresentarsi del problema, sono state, infine, inserite nella query le bind variables. Queste variabili, permettono di ridurre il numero di query e, quindi, di diminuire la memoria utilizzata e i tempi di esecuzione. In più, riescono a dare più informazioni ai DBA e consentono loro di avere più possibilità di intervenire.

Gli step seguiti per addivenire alla soluzione dell'incident nel database Oracle RAC

 

  • Analisi della situazione per capire come risolvere il problema

  • Individuazione della query che ha causato l’incident

  • Individuazione del piano di esecuzione errato

  • Individuazione del piano di esecuzione corretto

  • Forzatura del piano di esecuzione

  • Introduzione delle bind variables nella query

 

Ti è piaciuto quanto hai letto? Iscriviti a MISPECIAL, la nostra newsletter, per ricevere altri interessanti contenuti.

Iscriviti a MISPECIAL
Contenuti simili
DIGITAL ENTERPRISE
mar 09, 2022

La continuous integration è un metodo di sviluppo software in cui gli sviluppatori aggiungono regolarmente modifiche al codice in un repository centralizzato, con la creazione di build e i test eseguiti automaticamente con lo scopo di individuare e risolvere i bug con maggiore tempestività, migliorare la qualità del software e ridurre il tempo richiesto per convalidare e pubblicare nuovi aggiornamenti.

DIGITAL ENTERPRISE
apr 24, 2025

Gestire form complessi e dinamici in applicazioni mobile, specialmente quando i campi variano in base al contesto o non sono noti a priori, può rapidamente trasformarsi in una fonte di frustrazione per gli sviluppatori.