Obţinerea planului de execuţie

O primă modalitate de obţinere a planului de execuţie constă în folosirea comenzii AUTOTRACE în SQL*PLUS sau SQL Developer.

SET AUTOTRACE {OFF | ON | TRACEONLY} [EXPLAIN] [STATISTICS]

După această specificare se va furniza, pentru fiecare instrucţiune SQL:

cu TRACEONLY: planul de execuţie şi informaţii statistice
cu ON EXPLAIN: numai planul de execuţie
cu ON STATISTICS: numai informaţii statistice
cu ON: rezultatul execuţiei şi planul de execuţie
cu OFF: se dezactivează afişarea planului şi a informaţiilor statistice

Pentru instrucţiunea:

select produse.cod, denumire, codp
from produse, structura
where produse.cod=structura.cod
union
select a.cod,denumire,0
from produse a left join structura b on a.cod=b.cod
where b.cod is null
order by 2;

se furnizează, cu

set autotrace on

următoarele informaţii, după extragerea înregistrărilor regăsite cu instrucţiunea select:

Execution Plan 

Plan hash value: 2302160448

------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |  1066 | 45715 |    14  (29)| 00:00:01 |
|   1 |  SORT UNIQUE           |           |  1066 | 45715 |    13  (62)| 00:00:01 |
|   2 |   UNION-ALL            |           |       |       |            |          |
|*  3 |    HASH JOIN           |           |  1025 | 44075 |     6  (17)| 00:00:01 |
|   4 |     TABLE ACCESS FULL  | STRUCTURA |  1025 |  7175 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL  | PRODUSE   |  1042 | 37512 |     3   (0)| 00:00:01 |
|*  6 |    HASH JOIN RIGHT ANTI|           |    41 |  1640 |     6  (17)| 00:00:01 |
|   7 |     TABLE ACCESS FULL  | STRUCTURA |  1025 |  4100 |     2   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL  | PRODUSE   |  1042 | 37512 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("PRODUSE"."COD"="STRUCTURA"."COD")
   6 - access("A"."COD"="B"."COD")

Statistics 
---------------------------------------------------------- 
0  recursive calls  
0  db block gets  
26  consistent gets  
0  physical reads  
0  redo size  
53974  bytes sent via SQL*Net to client  
1144  bytes received via SQL*Net from client  
71  SQL*Net roundtrips to/from client  
1  sorts (memory)  
0  sorts (disk)  

1042 rows processed

Reprezentarea grafică a acestui plan (fără unele detalii ce apar în tabelul de mai sus) este:

Pentru instrucţiunea:

select * from multproduse order by denumire;

(multproduse este un view definit în cursul precedent) planul de execuţie este:

----------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |  1066 |   107K|    14  (29)| 00:00:01 |
|   1 |  SORT ORDER BY           |             |  1066 |   107K|    14  (29)| 00:00:01 |
|   2 |   VIEW                   | MULTPRODUSE |  1066 |   107K|    13  (24)| 00:00:01 |
|   3 |    SORT UNIQUE           |             |  1066 | 45715 |    13  (62)| 00:00:01 |
|   4 |     UNION-ALL            |             |       |       |            |          |
|*  5 |      HASH JOIN           |             |  1025 | 44075 |     6  (17)| 00:00:01 |
|   6 |       TABLE ACCESS FULL  | STRUCTURA   |  1025 |  7175 |     2   (0)| 00:00:01 |
|   7 |       TABLE ACCESS FULL  | PRODUSE     |  1042 | 37512 |     3   (0)| 00:00:01 |
|*  8 |      HASH JOIN RIGHT ANTI|             |    41 |  1640 |     6  (17)| 00:00:01 |
|   9 |       TABLE ACCESS FULL  | STRUCTURA   |  1025 |  4100 |     2   (0)| 00:00:01 |
|  10 |       TABLE ACCESS FULL  | PRODUSE     |  1042 | 37512 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("PRODUSE"."COD"="STRUCTURA"."COD")
   8 - access("A"."COD"="B"."COD")

A doua posibilitate de obţinere a planului de execuţie constă în folosirea utilitarului TKPROF.

A treia posibilitate de aflare a planului de execuţie constă în folosirea instrucţiunii EXPLAIN PLAN, care utilizează tabelul temporar PLAN_TABLE, automat creat pentru fiecare utilizator.

Scriptul utlxplan.sql (din directorul \RDBMS\ADMIN\ unde este instalat sistemul Oracle) permite crearea de utilizator a tabelului PLAN_TABLE (cu peste 35 de coloane), sau un alt tabel (pentru care în acest script se va preciza numele) unde se memorează informaţii despre planurile de execuţie ale instrucţiunilor SQL.

Instrucţiunea SQL care se analizează se va converti într-o expresie relaţională (cu operatori relaţionali unari sau binari), după care se face optimizarea acestei expresii, iar rezultatul se poate reprezenta ca un arbore. Acest arbore se memorează în tabelul PLAN_TABLE. Fiecare nod are un id şi o referinţă la nodul părinte (parent_id, cu valoarea null pentru un nod rădăcină). Această instrucţiune va păstra planul de execuţie în tabelul PLAN_TABLE, în mai multe înregistrări identificate printr-o valoare a coloanei "statement_id" precizată la crearea planului:

EXPLAIN PLAN 
set statement_id='nume'
[INTO nume_tabel]
for
instructiune SQL;

Prin clauza into se precizează tabelul unde se păstrează planul de execuţie (implicit PLAN_TABLE).
De aici se poate deduce că în tabelul PLAN_TABLE se pot păstra planurile de execuţie pentru mai multe instrucţiuni SQL. Inregistrările corespunzătoare unei anumite instrucţiuni se pot identifica printr-o valoare memorată în coloana statement_id (în PLAN_TABLE se pot memora planurile pentru mai multe instrucţiuni SQL). Vom genera planul de execuţie pentru două instrucţiuni:

explain plan
set statement_id='1'
for
select produse.cod, denumire, codp 
from produse inner join structura on produse.cod=structura.cod
union
select a.cod,denumire,0 
from produse a left join structura b on a.cod=b.cod where b.cod is null 
order by 2;

explain plan
set statement_id='2'
for
select cod, denumire, codp 
from multproduse;

Din tabelul PLAN_TABLE vom preciza, în continuare, unele coloane:

statement_id - identificarea grupului de înregistrări corespunzătoare unei instrucţiuni SQL
id - identificarea unui nod. Un nod rădăcina are valoarea 0 pentru această coloană.
parent_id - identificarea înregistrării părinte, care foloseşte rezultatul operaţiei memorate în înregistrarea curentă. Valoarea acestei coloane este null pentru nodurile rădăcină (unde id=0).
operation - operaţia (acţiunea) care se efectuează (unară, binară) cu eventuale noduri incluse (dacă nu e terminal) în structura arborescentă. Prima înregistrare generată (care identifică şi instrucţiunea pentru care se construieşte planul) precizează instrucţiunea, deci putem avea operaţiile: DELETE STATEMENT, INSERT STATEMENT, SELECT STATEMENT, UPDATE STATEMENT.
options - completare pentru coloana operation
object_name - numele obiectului din baza de date folosit pentru operaţie (tabel, view, index)
object_type - tipul obiectului din baza de date folosit pentru operaţie
access_predicates - se precizează un eventual operator folosit la evaluarea operatorului
cardinality - numărul de linii examinate
optimizer - modalitatea de optimizare folosită
depth - precizează distanţa nodului curent în structura arborescentă ce precizează evaluarea instrucţiunii (cu semnificaţia asemănătoare a pseudocoloanei level de la structurile arborescente).
position - pentru nodul rădăcină din structura arborescentă este o estimare a costului execuţiei operaţiei asociate. Pentru nodurile următoare din structura arborescentă (noduri "copil") este poziţia operandului asociat în lista de operanzi ai nodului părinte (valoarea poate fi 1 sau 2).
cpu_cost, io_cost, cost - reprezintă valori estimate de optimizator pentru calculele necesare evaluării, operaţiile de i/o (număr de blocuri), costul total (estimat) pentru operaţie. Valorile nu au unităţi de măsură şi sunt utile pentru comparaţii între diverse planuri de execuţie.
bytes - estimare a numărului de octeţi necesari pentru evaluarea operaţiei
other_xml - un fragment de document XML care precizează unii parametrii folosiţi la construirea planului de execuţie (versiunea de server folosită, schema utilizată, valori folosite pentru funcţii hash, etc.)

Pentru descrierea completă a coloanelor se poate consulta documentaţia pentru plan_table.

Vom executa următoarea instrucţiune, care foloseşte datele din tabelul PLAN_TABLE şi varianta de interogare a structurilor arborescente:

select substr(lpad(' ',2*(level-1)) || to_char(level) || ' ' || operation || '(' || options || ')',1,30) operatia, 
       object_name obiect, access_predicates
from plan_table
start with id=0 and statement_id='1'
connect by prior id=parent_id and statement_id='1';

care va extrage următoarele informaţii:

OPERATIA                       OBIECT            ACCESS_PREDICATES
 ------------------------------ ----------------- ---------------------------------
1 SELECT STATEMENT()
  2 SORT(UNIQUE)
    3 UNION-ALL()
      4 HASH JOIN()                              "PRODUSE"."COD"="STRUCTURA"."COD" 
        5 TABLE ACCESS(FULL)   STRUCTURA
        5 TABLE ACCESS(FULL)   PRODUSE
      4 HASH JOIN(RIGHT ANTI)                    "A"."COD"="B"."COD" 
        5 TABLE ACCESS(FULL)   STRUCTURA
        5 TABLE ACCESS(FULL)   PRODUSE

A patra modalitate de consultare a informaţiilor legate de planul de execuţie constă în folosirea funcţiei DISPLAY din pachetul DBMS_XPLAN.
Exemple de utilizare:
Instrucţiunea:

select * from table(dbms_xplan.display('PLAN_TABLE','1','BASIC'));

furnizează:

--------------------------------------------
| Id  | Operation              | Name      |
--------------------------------------------
|   0 | SELECT STATEMENT       |           |
|   1 |  SORT UNIQUE           |           |
|   2 |   UNION-ALL            |           |
|   3 |    HASH JOIN           |           |
|   4 |     TABLE ACCESS FULL  | STRUCTURA |
|   5 |     TABLE ACCESS FULL  | PRODUSE   |
|   6 |    HASH JOIN RIGHT ANTI|           |
|   7 |     TABLE ACCESS FULL  | STRUCTURA |
|   8 |     TABLE ACCESS FULL  | PRODUSE   |
--------------------------------------------

Instrucţiunea:

select * from table(dbms_xplan.display('PLAN_TABLE','1','TYPICAL'));

are rezultatul:

------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |  1066 | 45715 |    14  (29)| 00:00:01 |
|   1 |  SORT UNIQUE           |           |  1066 | 45715 |    13  (62)| 00:00:01 |
|   2 |   UNION-ALL            |           |       |       |            |          |
|*  3 |    HASH JOIN           |           |  1025 | 44075 |     6  (17)| 00:00:01 |
|   4 |     TABLE ACCESS FULL  | STRUCTURA |  1025 |  7175 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL  | PRODUSE   |  1042 | 37512 |     3   (0)| 00:00:01 |
|*  6 |    HASH JOIN RIGHT ANTI|           |    41 |  1640 |     6  (17)| 00:00:01 |
|   7 |     TABLE ACCESS FULL  | STRUCTURA |  1025 |  4100 |     2   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL  | PRODUSE   |  1042 | 37512 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Instrucţiunea:

select * from table(dbms_xplan.display('PLAN_TABLE','1','ALL'));

extrage informaţii suplimentare faţă de varianta cu argumentul TYPICAL:

  
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SET$1    
   3 - SEL$16C51A37  
   4 - SEL$16C51A37 / STRUCTURA@SEL$1    
   5 - SEL$16C51A37 / PRODUSE@SEL$1 
   6 - SEL$EC7930DA  
   7 - SEL$EC7930DA / B@SEL$2  
   8 - SEL$EC7930DA / A@SEL$3  
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("PRODUSE"."COD"="STRUCTURA"."COD")   
   6 - access("A"."COD"="B"."COD")  
 
Column Projection Information (identified by operation id):  
-----------------------------------------------------------  
 
   1 - (#keys=3) STRDEF[150], STRDEF[22], STRDEF[22]    
   2 - STRDEF[22], STRDEF[150], STRDEF[22]    
   3 - (#keys=1) "PRODUSE"."COD"[NUMBER,22], "STRUCTURA"."CODP"[NUMBER,22], 
  "PRODUSE"."DENUMIRE"[VARCHAR2,150]
   4 - "STRUCTURA"."COD"[NUMBER,22], "STRUCTURA"."CODP"[NUMBER,22]
   5 - "PRODUSE"."COD"[NUMBER,22], "PRODUSE"."DENUMIRE"[VARCHAR2,150]  
   6 - (#keys=1) "A"."COD"[NUMBER,22], "A"."DENUMIRE"[VARCHAR2,150]    
   7 - "B"."COD"[NUMBER,22]    
   8 - "A"."COD"[NUMBER,22], "A"."DENUMIRE"[VARCHAR2,150]