Evaluarea interogărilor la serverul Oracle

Gestiunea datelor memorate în bazele de date se realizează numai prin instrucţiuni SQL. Performanţa aplicaţiilor depinde foarte mult de viteza cu care serverul de date execută aceste instrucţiuni.
Atunci când o instrucţiune SQL este trimisă serverului ORACLE pentru execuţie, se generează un Plan de Execuţie. Acest plan se generează prin parcurgerea mai multor paşi, dintre care amintim:

1. Analiza sintactică - se verifică dacă sintaxa instrucţiunii SQL este corectă
2. Analiza semantică - se verifică dacă obiectele (componentele) referite în instructiune există şi sunt accesibile (deci dacă utilizatorul curent are drepturi de utilizare a componentelor din instrucţiune)
3. Inlocuirea view-urilor- dacă instrucţiunea SQL conţine referiri la view-uri, ele se înlocuiesc cu definiţia lor (cu instrucţiuni Select-sql)
4. Transformarea instrucţiunii într-o expresie cu operatori din algebra relaţională
5. Transformarea expresiei relaţionale într-o formă optimală prin folosirea unor rezultate din algebra relaţională. Cu astfel de transformări o expresie relaţională este convertită în altă expresie relaţională echivalentă, dar care are un cost general mai mic pentru evaluare.
6. Optimizarea ultimei expresii relaţionale obţinute. Viteza de execuţie este influenţată de mulţi factori, dintre care se pot aminti: numărul şi structura indecşilor asociaţi tabelelor, structura join-urilor, condiţiile de filtrare din clauza WHERE, numărul de înregistrări, dimensiunea înregistrărilor, etc.
   Serverele de baze de date oferă modalităţi de optimizare şi de urmărire a operaţiilor implicate în execuţia instrucţiunilor SQL.
7. Generarea unui plan de execuţie
8. Evaluarea planului astfel determinat

Serverul Oracle analizează fiecare instrucţiune SQL şi alege cel mai bun plan de execuţie, din punctul de vedere al timpului de răspuns şi al resurselor necesare execuţiei instrucţiunii.

La alegerea tipului de optimizare se au în vedere mai mulţi parametri, dintre care amintim:

• Sirul de caractere din instrucţiunea sql
• Condiţiile din clauza WHERE a instrucţiunii (dacă există această clauză)
• Indecşii posibili ce pot fi folosiţi la evaluarea instrucţiunii SQL
• Tipul de optimizare cerut
• Statisticile adunate şi memorate în dicţionarul bazei de date (cu instrucţiunea ANALYZE)
• Locaţia fizică a surselor de date (pentru SQL distribuit)
• Valorile din fişierul de parametri INIT.ORA, unde se memorează iniţializările făcute pentru unii parametri de lucru de la server. Un utilizator cu drept de administrare poate vizualiza valorile curente ale parametrilor cu ajutorul view-ului system v$parameter.

Alegerea tipului de optimizare

Alegerea tipului de optimizare se poate face în unul din următoarele moduri:

1. La nivelul instanţei (global pentru toate sesiunile de lucru) prin parametrul de iniţializare OPTIMIZER_MODE. Acest parametru poate lua următoarele valori:
   • ALL_ROWS - este util dacă rezultatul se obţine din foarte multe înregistrări ale sursei de date (nu este avantajoasă utilizarea unui index). Se încearcă să se minimizeze resursele folosite la server.
   • FIRST_ROWS_n, unde n poate fi 1, 10, 100, sau 1000. Acest parametru este util dacă se precizează o dimensiune aproximativă a rezultatului care se va obţine. In funcţie de dimensiunea sursei de date, optimizatorul poate lua mai uşor o decizie cu privire la utilizarea sau nu a unui index.
   • FIRST_ROWS - este util dacă se cere ca rezultatul să fie dat într-un timp cât mai scurt, fără a se lua în considerare eventualele resurse necesare (există posibilitatea ca rezultatul final să se obţină într-un timp mai mare). Acest mod este util atunci când trebuie să se obţină o mică parte a rezultatului instrucţiunii sau dacă se utilizează indexuri.
   • RULE - cere optimizare bazată de reguli (RBO: Rule Based Optimizer) - unde la evaluarea instrucţiunii SQL se iau în considerare o mulţime de reguli
   • CHOOSE - cere optimizare bazata pe cost (CBO: Cost Based Optimizer) - unde se iau în considerare informaţiile statistice existente în dicţionarul bazei de date, dacă există aceste informaţii statistice, în caz contrar se foloseşte optimizarea bazată pe reguli
2. La nivelul sesiunii printr-o instructiune ALTER SESSION, care precizează valoarea parametrului OPTIMIZER_MODE amintit mai sus:
   

   ALTER SESSION SET OPTIMIZER_MODE=
          {ALL_ROWS | 
   	   FIRST_ROWS_1 | FIRST_ROWS_10 | FIRST_ROWS_100 | FIRST_ROWS_1000 | 
   	   FIRST_ROWS |CHOOSE | RULE}
   

3. La nivelul instrucţiunilor SQL prin folosirea unor directive, numite şi hinturi, precizate efectiv în instrucţiune. Aceste directive reprezintă sugestii pentru optimizare şi apar în instructiunea SQL. Ele schimbă configuraţia de optimizare stabilită prin fişierul de iniţializare (parametrul OPTIMIZER_MODE) sau prin instrucţiunea ALTER SESSION.
   Exemplu:
   

   SELECT /*+ALL_ROWS*/ lista_coloane FROM ...

select * from V$PARAMETER order by name;

Optimizare bazată de reguli

Optimizare bazată pe cost

Acest tip de optimizare foloseşte anumite informaţii statistice din dicţionarul de date. Astfel de informaţii statistice despre tabelele existente în baza de date, sau despre coloanele acestor tabele, clusteri şi indecşi, sunt memorate în dicţionarul de date şi sunt create (sau reactualizate) prin comenzi ANALYZE sau cu facilităţile pachetului DBMS_STATS. Dacă nu există informaţii statistice despre o sursă de date, atunci se foloseşte optimizatorul bazat pe reguli.
Se pot folosi în paralel cele două tipuri de optimizatoare dacă unele tabele sunt analizate şi altele nu. Dacă cel puţin unul din tabelele implicate în instrucţiunea SQL este analizată, atunci folosirea optimizării bazate pe cost (mai avantajoasă) devine posibilă.
Modul de analiză a tabelelor şi tipul informaţiilor calculate este importantă pentru determinarea performanţei instrucţiunilor SQL. Tot la fel de important este şi momentul în care se face analiza şi cât de des se determină aceste informaţii statistice.
Există două variante de a analiza înregistrările dintr-un tabel:

• Se analizează toate înregistrările folosind comanda ANALYZE COMPUTE, utilă pentru tabele de dimensiune mică sau medie
• Se analizează anumite înregistrări folosind comanda ANALYZE ESTIMATE, utilă pentru tabele de dimensiune foarte mare (peste 1,000,000 de înregistrări). Este recomandată folosirea clauzei FOR ALL INDEXED COLUMNS pentru tabelele cu date asimetrice (cu un număr mare de înregistrări ce au aceeaşi valoare într-o coloană).

Dacă se foloseşte optimizatorul bazat pe cost, atunci informaţiile statistice din dicţionarul de date (despre tabele, indexuri, clustere) trebuie calculate periodic, mai ales după multe operaţii de modificare sau dacă distribuţia valorilor din coloane are schimbări semnificative în timp. Pentru eficienţa operaţiilor se poate scrie un script (o procedură) care să conţină comenzi ANALYZE pentru tabele utile dintr-o schemă dată. Acest script (procedură) trebuie executat periodic (manual sau prin folosirea pachetului DBMS_JOB).

Exemple de utilizare:

ANALYZE TABLE tabel COMPUTE STATISTICS;

se evaluează întregul tabel.

ANALYZE TABLE tabel ESTIMATE STATISTICS;

se evaluează primele 1064 de înregistrări.

ANALYZE TABLE tabel ESTIMATE STATISTICS SAMPLE 10 PERCENT;

se evaluează primele 10% înregistrări.

ANALYZE TABLE tabel ESTIMATE STATISTICS SAMPLE 1000 ROWS;

se evaluează primele 1000 de înregistrări.

O altă modalitate de determinarea a informaţiilor statistice constă în folosirea pachetului dbms_stats.

Pe scurt, analiza bazată pe costuri are următorii paşi:

• Analiza instrucţiunii SQL (analiza sintactică, semantică, transformare)
• Generarea unei liste de planuri de execuţie posibile
• Determinarea unui cost estimativ al fiecărui plan de execuţie luat în considerare, folosind toate informaţiile statistice disponibile în dicţionar
• Selectarea planului de execuţie cu costul minim

Dacă un tabel a fost analizat şi nu mai sunt necesare statisticile, acestea se pot şterge folosind:

ANALYZE TABLE tabel DELETE STATISTICS;

Cu ajutorul view-ului dba_tables se pot obţine informaţii cu privire la statisticile existente.

Exemplu pentru obţinerea tabelelor dintr-o schemă pentru care există statistici colectate (coloana sample_size precizează numărul de înregistrări folosite la calculul informaţiilor statistice):

select last_analyzed, table_name, num_rows, sample_size 
from dba_tables where owner='STUD1' order by last_analyzed;

Observaţie. Analiza tabelelor, pentru informaţii statistice, este o operaţie costisitoare (ca timp) dacă dimensiunea tabelelor este mare.

Folosire index la generarea interogărilor

Din punct de vedere logic un index este o listă de perechi: o valoare a expresiei pentru care s-a creat indexul, adresele înregistrărilor care au această valoare pentru expresia de cheie.
Un index se crează:

• automat: când pentru tabel se precizează restricţiile PRIMARY KEY sau UNIQUE KEY
• manual: prin comanda CREATE INDEX

La consultarea unui tabel serverul de date are două variante:

• să parcurgă fiecare înregistrare din tabel (se "scanează" întregul tabel). Această modalitate se recomandă dacă sunt necesare mai mult de 5% din înregistrările existente în tabel.
• să se folosească un index pentru a accesa o singură înregistrare prin ROWID (identificatorul înregistrării), sau mai multe înregistrări. Această consultare este recomandată dacă sunt necesare mai puţin de 5% din înregistrările existente în tabel.

Indecşii cresc performantele instrucţiunilor SELECT, UPDATE, DELETE, deoarece se determină mai repede înregistrările folosite de aceste instrucţiuni, dar scad performanţa instrucţiunii INSERT.

Tipuri de indexuri

1. Indecşi pentru o singură coloană, memoraţi ca un B-arbore.
2. Indecşi pentru o concatenare de coloane, memoraţi ca un B-arbore.
3. Indecşi pentru valorile unei expresii (începând cu versiunea Oracle 8i). Se recomandă numai dacă în clauzele where se va folosi această expresie.

   Exemplu:
   

   create index studenti1 on studenti(substr(cnp,2,2));

4. Indecşi de tip bitmap: conţin pentru fiecare valoare (a coloanei, a colectiei de coloane, a expresiei pentru care se construieşte indexul) câte un vector de valori 1 şi 0, câte o poziţie în acest vector pentru fiecare înregistrare din tabel. O valoare binară din acest vector precizează dacă înregistrarea conţine valoarea respectivă sau nu. Este recomandat pentru coloane cu puţine valori distincte (deci există multe repetări ale valorilor), altfel dimensiunea indexului poate fi foarte mare.
   

   Exemplu:
   

   create bitmap index studenti2 on studenti(substr(cnp,6,2));

   Aceşti indecşi sunt eficienţi în interogările ce folosesc operatorul de egalitate "=" sau "IN".

5. Index pentru date xml
6. Alte tipuri de index: cluster, hash

Observaţie. Periodic este necesară re-crearea indexurilor. O cale rapidă constă în folosirea instructiunii ALTER INDEX cu optiunea REBUILD.

Exemplu:

alter index studenti1 REBUILD;