Rezolvarea evenimentelor comune de performanta în Oracle

Un eveniment de așteptare (wait) este în esență înregistrarea de timp petrecută pe executarea unei părți a codului de către serverul de baze de date Oracle. Granularitatea evenimentelor de așteptare poate diferi între versiunile Oracle. Evenimentele de așteptare pot descrie interacțiunea cu sistemul de operare (citire sau scrierea), dar, de asemenea, interacțiunea dintre procese (de exemplu inter-process communication).  Un eveniment de așteptare nu este necesar un lucru rău. Fiecare proces de baze de date Oracle fie execută pe CPU, fie așteptă. În cazul în care o sesiune din SQL * Plus  nu execută nimic, atunci va exista un eveniment de așteptare pentru SQL*Net message from client, ceea ce înseamnă că baza de date așteaptă următoarea instrucțiune.

Există peste 800 de evenimente de așteptare, dar cele mai frecvent întâlnite sunt mai puține.  În acest articol încerc să acopăr cele mai populare dintre evenimentele de așteptare.

db file sequential reads

Cauze posibile:

• utilizarea unui index neselectiv
• indecși fragmentați
• I/O ridicat pe un disc sau pe mount point
• proiectarea greșită a aplicației
• performanța scăzută la citirea indecșilor poate fi afectată de un I/O lent care conduce la o medie mai mare a timpului de așteptare

Acțiuni:

• verificarea indecșilor pentru a vă asigura că sunt utilizați corespunzător
• verificați ordinea coloanelor indexate cu clauza WHERE pentru Top SQL
• rebuild index pentru cei cu un factor ridicat de clustering
  
• utilizarea partiționării pentru a reduce cantitatea de blocuri vizitate
• asigurați-vă că statisticile de optimizare sunt actualizate
• relocati datafile-urile des utilizate
• luați în considerare utilizarea multiplelor zone de buffer pools si cache pentru indecșii și tabelele utilizate frecvent cu ajutorul KEEP pool
• verificați planurile de execuție ale instrucțiunile SQL care accesează date prin indecși
• este necesar ca declarațiile SQL sa acceseze datele prin indecși?
• full table scan este fi mai eficient?
• scopul optimizatorului este de a minimiza atât numărul logic cat și fizic de I/O-uri.

Remarci:

• procesele Oracle accesează un bloc care nu se afla în SGA și așteaptă pentru ca blocul din baza de date să fie citit de pe disc în SGA
  
• un timp de așteptare semnificativ pentru db file sequential read este, cel mai probabil, o problemă de aplicație
  
• dacă valoarea coloanei CLUSTERING_FACTOR din DBA_INDEXES pentru index-ul investigat este apropiată de numărul de blocuri din tabelă, atunci cele mai multe dintre rândurile tabelei sunt ordonate. Acest lucru este de dorit.
• cu toate acestea, în cazul în care CLUSTERING_FACTOR se apropie de numărul de rânduri ale tabelei, aceasta înseamnă că rândurile din tabelă sunt ordonate aleatoriu și, prin urmare, este nevoie de mai multe I/O-uri pentru a finaliza operațiunea. Puteți îmbunătăți CLUSTERING_FACTOR prin reconstruirea tabelei, astfel încât rândurile să fie ordonate în funcție de cheia indexului și ulterior prin reconstruirea indexului
• parametrii de inițializare OPTIMIZER_INDEX_COST_ADJ și OPTIMIZER_INDEX_CACHING  pot influența optimizatorul sa favorizeze operațiile nested loops și a alege index access path peste full table scan.

db file scattered reads

Cauze posibile:

• sesiunea Oracle a solicitat și este în așteptare pentru mai multe blocuri din baze de date (< DB_FILE_MULTIBLOCK_READ_COUNT) pentru a fi încărcate de pe disc în SGA 
• full Table scans
• fast full index scans

Acțiuni:

• optimizarea multi-bloc I/O prin setarea adecvată a parametrului DB_FILE_MULTIBLOCK_READ_COUNT
• luați în considerare utilizarea partiționării pentru a reduce cantitatea de blocuri vizitate
  
• utilizarea multiplelor zone de buffer pools și cache pentru indecșii și tabelele utilizate frecvent cu ajutorul KEEP pool
• optimizarea instrucțiunii SQL care a inițiat cele mai multe wait-uri. Scopul este de a reduce numărul de citiri fizice și logice
• ar trebui ca accesul la date sa se realizeze prin full table scan sau index fast full scan? Ar fi mai eficient index range sau unique scan?
• predicatele SQL sunt corespunzătoare pentru hash sau merge join?
• în cazul în care accesul full scan este adecvat, poate interogarea paralelă îmbunătăți timpul de răspuns?
• obiectivul este de a reduce cerințele atât pentru I/O-uri logice cât și fizice, iar acest lucru este realizabil prin intermediul îmbunătățirii declaraților SQL-urilor din aplicație
• asigurați-vă că statisticile sunt actualizate

Remarci:

• în cazul în care o cerere a funcționat bine pentru un timp iar brusc a apărut un wait cu un timp mare de așteptare pe evenimentul db file scattered read  și nu a existat o schimbare de cod, ați putea dori să verificați dacă unul sau mai multi indecși au fost șterși ori au deveni inutilizabili
• sau dacă statisticile nu mai corespund cu datele din prezent

log file parallel write

Cauze posibile:

• LGWR așteaptă scrierea cache-ului redo log buffer în fișierele online log de pe disc
• I/O wait pe sistemul de exploatație al fișierelor online redo log

Acțiuni:

• reduceți cantitatea generată de redo
• nu lăsați tablespace-urile în modul hot backup mai mult timp decât este necesar
• nu folosiți RAID 5 pentru fișierele redo log
• utilizați discuri rapide pentru fișierele jurnal redo
• asigurați-vă că discurile care dețin archived redo log și fișierele online redo log sunt separate pentru a evita disputa pe disc
  
• luați în considerare utilizarea NOLOGGING sau opțiunea UNRECOVERABLE în instrucțiuni SQL

log file sync

Cauze posibile:

• procesele Oracle sunt în așteptare pentru finalizarea unui COMMIT sau ROLLBACK
  

Acțiuni:

• optimizarea LGWR pentru a ajuta transferul informațiilor pe disc. ex: nu se pune redo logs pe RAID5
• reducerea numărului total de commit-uri, astfel încât să există cât mai puține operațiuni distincte COMMIT 

buffer busy waits

Cauze posibile:

• cele două cauze principale în care buffer busy waits poate apărea sunt:
  • o alta sesiune citește blocul în buffer
  • o altă sesiune deține buffer-ul într-un mod incompatibil de cererea noastră
• acest wait indică o dispută citire/citire, citire/scriere, sau scrie/scrie
• sesiunea Oracle așteaptă să fixeze un buffer. Un buffer trebuie să fie fixat înainte de a fi citit sau modificat. Doar un singur proces poate fixa un buffer la un moment dat
• wait-ul poate fi intensificat de o dimensiune mare a unui bloc dacă mai multe rânduri pot fi cuprinse în acel bloc
• acest lucru se întâmplă atunci când o sesiune dorește să acceseze un bloc de bază de date în buffer, dar nu poate pentru ca buffer-ul este ocupat
• de multe ori din cauza mai multor procese care citesc în mod repetat aceleași blocuri (de ex: mai mulți utilizatori scanează același index sau blocuri de date)

Acțiuni:

• principala modalitate de a reduce buffer busy waits este de a reduce numărul total de I/O
• în funcție de tipul de bloc, acțiunile vor fi diferite
• Blocuri de date:
  • eliminarea blocurilor fierbinți din aplicație. Verificați indecșii scanați în mod repetat/ neselectivi
  • încercați să refacere obiectul cu un PCTFREE mai mare, astfel încât să reduceți numărul de rânduri per bloc
  • creșterea INITRANS și MAXTRANS și reduceți PCTUSED. Acest lucru va face tabela mai puțin densă
• Segment Header:
  • creșterea numărului de FREELIST-uri și FREELIST GROUP-uri
• Undo Header:
  • creșterea numărului de segmente rollback

free buffer waits

Cauze posibile:

• acest lucru înseamnă că suntem în așteptarea unui free buffer, dar nu există nici unul disponibil în memorie, deoarece există prea multe dirty buffer în cache
• fie buffer cache-ul este prea mic, fie DBWR este prea lent în scrierea modificărilor din buffer pe disc
• DBWR nu reușește să țină pasul cu cererilor de scriere
• checkpoints-urile se întâmplă prea repede – poate din cauza activității bazei de date și a redo log-urilor subdimensionate
• sortări mari și full table scan, umplere cache-ul cu blocuri modificate mai repede decât  poate să scrie pe disc procesul DBWR
  
• dacă numărul dirty buffer-urilor care trebuie să fie scrise pe disc este mai mare decât numărul pe care DBWR poate sa-l scrie per batch, atunci acest wait poate fi observat

Acțiuni:

• reducerea frecvenței checkpoints-urilor – creșterea dimensiunii fișierelor redo log
• reexaminați dimensiunea buffer cache-ului – de luat în considerare creșterea în dimensiune a buffer cache-ului din SGA
• setarea disk_asynch_io = true
• dacă nu utilizați asincron I/O creșteți numărului proceselor dbwr slaves
• asigurați-vă că nu există hot spots prin răspândirea fișierelor de date pe discuri și controlere de disc
• pre–sortarea sau reorganizarea de date poate ajuta

enqueue waits

Cauze posibile:

• acest eveniment indică un wait pentru un sistem de blocare care este legat de o altă sesiune (sau sesiuni), într-un mod incompatibil decât cel solicitat
• TX Transaction Lock
  • este de obicei o problemă de aplicație
  • acest lucru indică o blocare la nivel de rând și apare atunci când o tranzacție încearcă să actualizeze sau să șteargă rânduri care sunt în prezent blocate de o altă tranzacție
• TM DML enqueue lock
  • din cauza unor probleme generale de aplicație, în special în cazul în care constrângerile foreign key nu au fost indexate
• ST lock
  • acțiuni cum ar fi drop, truncate sau coalesce presupun modificări ale $UET (used extent table) și FET$ (free extent table) ele necesitând lock ST
  • blocajul ST indică că există mai multe sesiuni care întreprind una din cele trei acțiuni
  • alocare sau dealocarea dinamică a spațiu pe disc

Acțiuni:

• Reducerea wait-urilor
• dificultatea rezolvării acestui blocaj constă în identificarea tipului de blocaj
• ori de câte ori întâlniți un eveniment enqueue wait pentru TX enqueue, primul pas este de a afla cine este cel care a generat blocajul și dacă există mai multe cereri pentru aceeași resursă
• wait-urile pentru TM enqueue în Mode 3 sunt în primul rând din cauza coloanelor foreign key neindexate
• crearea de indecși pe foreign key <10g
• următoarele măsuri le puteți face preventiv pentru a reduce la minimum blocajele ST în baza de date:
• utilizarea tablespaces-urilor gestionate local (locally managed tablespaces)
  
• recrearea tablespaces-urilor temporare folosind comanda CREATE TEMPORARY TABLESPACE TEMPFILE … 

cache buffer chain latch

Cauze posibile:

• procesele generează acest latch atunci când au nevoie pentru a trece buffer-ul bazat pe politica de înlocuire LRU (least recently used) în buffer cache
• cache buffer LRU chain latch apare când este introdus un nou bloc în buffer cache și atunci când scrieți un buffer înapoi pe disc, în mod special atunci când se scanează un lanț LRU care conține toate blocurile murdare din buffer cache
• indecși mari neselectivi sau full table scan sunt principalii vinovați
• scanarea intensiva a buffer cache-ului poate fi cauzat, de exemplu, prin: scanarea în mod repetat a indecșilor mari neselectivi

Acțiuni:

• acest latch poate fi evitat prin punerea în aplicare a mai multor buffer pools-uri sau creșterea numărului de LRU latches prin mărirea parametrului DB_BLOCK_LRU_LATCHES 
• este posibilă reducerea cache buffer chain latch prin creșterea dimensiunii buffer cache și reducând astfel rata la care noile blocuri sunt modificare în buffer cache

direct path reads

Cauze posibile:

• aceste wait-uri sunt asociate cu operațiunile de citire directe în sesiunile PGA ocolind SGA-ul
• evenimentele de așteptare „direct path read” și „direct path write” sunt legate de operațiuni care sunt efectuate în PGA ca sortarea, grup by și hash join
  
• în timpul procesărilor de lunga durată, evenimente de așteptare „direct path read” sunt obișnuite. Cu toate acestea, pentru un sistem OLTP aceste așteptări sunt semnificative
• instrucțiunile SQL cu funcții care necesită ORDER BY, GROUP BY, UNION, DISTINCT și ROLLUP, rulează în tablespace temporar atunci când dimensiunea de intrare este mai mare decât zona de lucru din PGA

Acțiuni:

• asigurați-vă că asincron IO este configurat corect
• verificați sesiunile consumatoare de IO și verificați dacă valoarea IO poate fi redusă
• setați PGA_AGGREGATE_TARGET la valoare corespunzătoare (în cazul în care parametrul WORKAREA_SIZE_POLICY este AUTO) sau setați * _area_size manual (cum ar fi sort_area_size) și apoi puteți să setați WORKAREA_SIZE_POLICY
• ori de câte ori este posibil să folosiți: UNION ALL în loc de UNION, HASH JOIN în loc de SORT, MERGE și NESTED LOOPS în loc de HASH JOIN
• verificați pentru a vedea dacă coloanele indexului pot fi rearanjate pentru a se potrivi cu clauza ORDER BY pentru a evita scanarea întregii tabele
• interogați V$SESSTAT pentru a identifica sesiuni cu valoare mare pentru physical reads direct

Remarci:

• dimensiunea implicită a parametrului HASH_AREA_SIZE este de două ori valoarea SORT_AREA_SIZE
• o valoare mare pentru HASH_AREA_SIZE va influența optimizatorul sa aleagă hash joins în loc de nested loops
• parametrul DB_FILE_DIRECT_IO_COUNT poate avea impact în performantele direct path read. Acesta stabilește valoarea maximă a buffer-ului I/O pentru operațiunile de citire și scriere directa. Implicit este 1M în 9i

direct path writes

Cauze posibile:

• aceste waits-uri sunt asociate cu operațiile de scriere directa atunci când datele sunt scrire din PGA-ul utilizatorilor în data files-uri sau tablespace-urile temporare
• operațiunile de încărcare directă, de ex: Create Table as  Select (CTAS)
• operațiuni paralele DML
• sort IO (atunci când un sort nu încape în memorie)

Acțiuni:

• dacă fișierul indică check pe tablespace-ul temporar pentru operațiuni de sortare pe disc, atunci asigurați-vă că parametrul DISK_ASYNCH_IO este TRUE.
  
• verificați dacă asincron I/O este configurat corect
  

latch free waits

Cauze posibile:

• acest wait indică faptul că procesul este în așteptarea unui latch care este în prezent ocupat (deținut de un alt proces)
• când un eveniment latch free wait este văzut în view-ul V$SESSION_WAIT, atunci procesul nu a reușit să obțină latch-ul în timpul de așteptare al parametrului _SPIN_COUNT. Când procesele concurează pentru latch vor consuma mai multe resurse CPU. Rezultatul este un timp de răspuns mai mare.

Acțiuni:

• în cazul în care timpul de așteptare petrecut pentru latch este semnificativ, atunci cel mai bine este să se determine cauza principală
  

Remarci:

• latch-urile se aplică numai structurilor de memorie din SGA. Nu se aplică la obiectele bazei de date. In Oracle SGA au loc mai multe latch-uri și ele există pentru a proteja diverse structuri de memorie de corupere din cauza accesului concurent
• latch free waits este un efect, nu o cauză; cauza este că se fac prea multe block gets, iar block gets necesită cache buffer chain latching

library cache latch

Cauze posibile:

• library cache latches protejează declarațiile SQL din cache și definițiile obiect din library cache. Library cache latch există în scopul de a adăuga o nouă declarație în library cache.

Acțiuni:

• folosiți variabile bind ori de câte ori este posibil
• puteți reduce timpul de așteptare cauzat de library cache latch prin setarea corespunzătoare a parametrului SESSION_CACHED_CURSORS
• luați în considerare creșterea zonei de memorie shared pool

shared pool latch

Cauze posibile:

• Shared pool latch este folosit pentru a proteja operațiunile critice la alocarea și eliberarea de memorie în shared pool. Shared pool latch și library cache latches se datorează în principal parsării. Hard parse este valabil pentru cursoarele noi și cursoarele care sunt aged out și trebuie să fie re-executate.Costul de parsare pentru o nouă declarație SQL este costisitor, atât în ceea ce privește cerințele CPU cât și iterațiile de care au nevoie library cache și shared pool latches când urmează să fie dobândită și eliberată.

Acțiuni:

• se evită hard parsing-ul, atunci când este posibil se parsează o dată și se execută de mai multe ori
• dimensiunea shared_pool și utilizarea MTS (multithreaded shared servers), de asemenea, influențează shared pool latch
• soluție de compromis este setarea parametrul CURSOR_SHARING la valoarea „FORCE”. Acest lucru permite declarații care nu sunt identice sa folosească același cursor și, prin urmare, reduce utilizarea memoriei și hard parse-ingul.

row cache objects latch

Cauze posibile:

• acest latch apare atunci când procesele utilizator încearcă să acceseze valorile dicționarului de date stocat în memoria cache

Acțiuni:

• o modalitate de a reduce acest latch este prin mărirea dimensiunii shared pool (SHARED_POOL_SIZE)
• utilizarea tablespce-urilor gestionate local pentru aplicația dumneavoastră în special pentru indecși
• revizuirea și modificarea design-ul logic al bazei de date, un exemplu bun este să se fuzioneze sau să se micșoreze numărul de indecși pentru tabelele cu multe inserti-uri

Remarci:

• configurarea library cache-ului pentru o dimensiune acceptabilă, de obicei, se asigură că, cache-ul în dicționarul bazei de date este setat în mod corespunzător.