EXPLAIN PLAN ed EXECUTION PLAN
L'istruzione EXPLAIN PLAN visualizza l'execution plan scelto da Oracle optimizer per l'istruzione di select, update, insert e delete.
L' execution plan contiene la sequenza di operazioni eseguite da Oracle per far girare la statement.
Quando usi Explain Plan, Oracle inserisce le informazioni dell'execution plan nella tabella PLAN_TABLE che deve esistere nel corrispondente user a cui appartiene la statement da analizzare.
Creazione della tabella <nome_schema>.PLAN_TABLE
$ Sqlplus /as sysdba
SQL> explain plan for <inseire la statemente da anlizzare> ;
Per generare l'output a video:
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
Per visualizzare a monitor
SQL> COL PLAN_TABLE_OUTPUT FOR A180;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
$ Sqlplus /as sysdba
Per individuare più facilmente la query conviene aggiungere un commento
E poi per risalire all'sql_id fare
Una volta indidivuato sql_id lo uso nella vista che contiene il piano di esecuzione memorizzato nella Library Cache.
La query restituisce 12 righe che vuol dire che oracle ha eseguito 12 operazioni per restiruire il risultato. Con qusta query posso risalire al piano di esecuzione
CPU_COST indica il numero di cpu cycle necessari per l'operazione
DEPTH indica il livello di indentazione delle operazioni e le righe con lo stesso valore hanno la stessa indentazione.
IO_COST è il numero stimato di data block letti per la corrispondente operazione
OPTIMIZER utilizziamo la metodologia COST BASED OPTIMIZATION e può avere i valori FIRST_ROWS, FIRST_ROWS_IN e ALL_ROWS. L'ultima è quella di default e vuol dire che vengono lette tutte le righe in memoria contemporaneamente. Invece FIRST_ restituisce solo le prime righe e viene usato in applicazioni come Oracle Forms in cui l'utente visualizza solo un set iniziale di valori.
Il valore ANALYZED indica che l'optimizer usa già le statistiche degli oggetti e non deve generarle dinamicamente.
Da dove inizio l'analisi?.PNG)
Il costo della OR è alto, cambiare la query e usare BETWEEN AND
3) Controllare ACCESS METHODS
L' execution plan contiene la sequenza di operazioni eseguite da Oracle per far girare la statement.
Quando usi Explain Plan, Oracle inserisce le informazioni dell'execution plan nella tabella PLAN_TABLE che deve esistere nel corrispondente user a cui appartiene la statement da analizzare.
Creazione della tabella <nome_schema>.PLAN_TABLE
$ Sqlplus /as sysdba
SQL> ALTER SESSION SET current_schema=<nome_schema>;
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sqlGernerare la explan plan
SQL> explain plan for <inseire la statemente da anlizzare> ;
Per generare l'output a video:
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
|* 2 | FILTER | | | | |
| 3 | TABLE ACCESS FULL | FEL_ESTREMIATTOPDL | | | |
|* 4 | FILTER | | | | |
|* 5 | INDEX UNIQUE SCAN | SYS_C007405 | | | |
| 6 | SORT AGGREGATE | | | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 7 | NESTED LOOPS | | | | |
| 8 | NESTED LOOPS | | | | |
| 9 | TABLE ACCESS FULL| FEL_PDLFASESTATO | | | |
|* 10 | INDEX UNIQUE SCAN| SYS_C007606 | | | |
|* 11 | INDEX UNIQUE SCAN | SYS_C007405 | | | |
| 12 | SORT AGGREGATE | | | | |
| 13 | MERGE JOIN | | | | |
|* 14 | INDEX UNIQUE SCAN | SYS_C007405 | | | |
|* 15 | FILTER | | | | |
| 16 | TABLE ACCESS FULL| FEL_PDLFASESTATO | | | |
-------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "FEL_OWN"."FEL_ESTREMIATTO"
.................................................
.......................................Note: rule based optimization
68 rows selected.
Per visualizzare a monitor
SQL> COL PLAN_TABLE_OUTPUT FOR A180;
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 12 |
| 1 | SORT AGGREGATE | | 1 | 8 | |
|* 2 | FILTER | | | | |
| 3 | INDEX FAST FULL SCAN | SYS_C0040516 | 1182 | 9456 | 7 |
|* 4 | FILTER | | | | |
| 5 | NESTED LOOPS | | 1 | 47 | 3 |
|* 6 | INDEX UNIQUE SCAN | SYS_C0040516 | 1 | 8 | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 7 | VIEW | VW_SQ_1 | 1 | 39 | 2 |
| 8 | SORT GROUP BY | | 1 | 21 | 2 |
|* 9 | FILTER | | | | |
| 10 | NESTED LOOPS | | 1 | 21 | 2 |
|* 11 | INDEX UNIQUE SCAN| SYS_C0040516 | 1 | 8 | 1 |
|* 12 | INDEX RANGE SCAN | SYS_C0012133 | 1 | 13 | 1 |
| 13 | SORT AGGREGATE | | 1 | 33 | |
| 14 | NESTED LOOPS | | 1 | 33 | 2 |
| 15 | NESTED LOOPS | | 1 | 27 | 2 |
|* 16 | INDEX RANGE SCAN | SYS_C0012133 | 1 | 19 | 2 |
|* 17 | INDEX UNIQUE SCAN | SYS_C0040516 | 1 | 8 | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 18 | INDEX UNIQUE SCAN | SYS_C0040730 | 1 | 6 | |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT /*+ */ 0 FROM "FEL_ESTREMIATTO"
.............
.................
.Note: cpu costing is off
66 rows selected.
ERRORI
SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Error: cannot get definition for table 'PLAN_TABLE'
ORA-00980: synonym translation is no longer valid
Vuol dire che esiste un sinonimo non valido perchè punta ad una tabella inesistente.
SQL> select * from dba_synonyms where table_name='PLAN_TABLE';
OWNER SYNONYM_NA TABLE_OWNE TABLE_NAME DB_LI
---------- ---------- ---------- ------------------------------ --------------------------------------
PUBLIC PLAN_TABLE SYS PLAN_TABLE
OFFICE PLAN_TABLE AD2099 PLAN_TABLE
La tabella SYS.PLAN_TABLE non esiste e quindi cancello il suo sinonimo e poi creo un nuovo sinonimo pubblico che punta allo schema contenente la query da analizzare.
SQL> drop public synonym PLAN_TABLE;
SQL> create public synonym plan_table for <nome_schema>.plan_table;
SQL> select * from dba_synonyms where table_name='PLAN_TABLE';
OWNER SYNONYM_NA TABLE_OWNE TABLE_NAME DB_LI
---------- ---------- ---------- ------------------------------ ---------------------------------------
PUBLIC PLAN_TABLE <nome_schema> PLAN_TABLE
OFFICE PLAN_TABLE AD2099 PLAN_TABLE
SQL Developer tool
Cliccare su EXPLAIN PLAN oppure F10
Se vogliamo conforntarlo con l'EXECUTION PLAN e cioè con l'esatto piano di esecuzione usato, occorre prima eseguire la query
Ora risalire al valore sql_id
select * from V$SQL
where sql_text like '%select s.prod_id from sales s, customers c where s.cust_id=c.cust_id;%';
Per individuare più facilmente la query conviene aggiungere un commento
select /*query_JD*/s.prod_id
from sales s, customers c
where s.cust_id=c.cust_id;
select * from V$SQL where sql_text like '%query_JD%'
select * from V$SQL_PLAN where sql_id ='5yfaf6u6sp91r';
select hash_value, operation, optimizer, cost, cardinality from v$sql_plan where sql_id ='5yfaf6u6sp91r';
Ma non ècomodo da leggere alloca conviene usare la funzione DBMS_XPLAN
Estraiamo l'executon plan che viene eseguito ora:
select * from TABLE( DBMS_XPLAN.DISPLAY_CURSOR('5yfaf6u6sp91r'));
Può essere differente dall'explain plan memorizzato nel PLAN_TABLE che sarebbe la schermata alla sua sinistra.
I valori sono differenti se il db è poco movimentato altrimenti l'execution plan ha valori dfferenti dalll'explan paln che si basa sulle statistiche.Come si legge un explain plan della query seguente?
select /*my_query*/p.prod_id, p.prod_name, s.amount_sold, s.quantity_sold
from sales s, products p
where s.prod_id=p.prod_id and p.prod_id=13;
Risalgo al campo sql_id:
select * from v$sql where sql_text like '%my_query%';
Risalgo all'execution plan:
select * from table(dbms_xplan.display_cursor('bn6ukpzwmrmp3'));
DEPTH indica il livello di indentazione delle operazioni e le righe con lo stesso valore hanno la stessa indentazione.
OPTIMIZER utilizziamo la metodologia COST BASED OPTIMIZATION e può avere i valori FIRST_ROWS, FIRST_ROWS_IN e ALL_ROWS. L'ultima è quella di default e vuol dire che vengono lette tutte le righe in memoria contemporaneamente. Invece FIRST_ restituisce solo le prime righe e viene usato in applicazioni come Oracle Forms in cui l'utente visualizza solo un set iniziale di valori.
Il valore ANALYZED indica che l'optimizer usa già le statistiche degli oggetti e non deve generarle dinamicamente.
Da dove inizio l'analisi?
- 1) chi sono le tabelle e che fanno?
- 2) eseguire l'explain plan e individuare la riga più costosa. COST_ID
- explain plan for
SELECT p.prod_id,p.prod_name, s.amount_sold, s.quantity_sold
FROM sales s, products p, customers c
WHERE s.prod_id = p.prod_id
and s.CUST_ID = c.CUST_ID
and s.cust_id in (2,3,4,5);
select * from table(dbms_xplan.display());
Scendere fino ad individuare la riga che causa il problema.
.PNG)
Il costo maggiore è 208 e scendendo vediamo dall'ID=8 che l'istruzione s.cust_id in (2,3,4,5); viene trasformata da QUERY OPTIMIZER in s.cust_id =2 or s.cust_id=3 or s.cust_id=4 ....
- SELECT p.prod_id,p.prod_name, s.amount_sold, s.quantity_sold
FROM sales s, products p, customers c
WHERE s.prod_id = p.prod_id
and s.CUST_ID = c.CUST_ID
and s.cust_id BETWEEN 2 AND 5;
3) Controllare ACCESS METHODS
Ad esempio un TABLE ACCESS FULL non va bene perché corrisponde ad un full table scan. Usare indici.
4) Controllare la CARDINALITY
Se il valore è altro vuol dire che ci sono problemi se ci aspettiamo meno righe o se come nell'esmpio di prima tra id=4 vale 521 e id=3 vale 72.
Comunque questo valore è calcolato dalla Selectivity che è un parametro calcolato dalle statistiche.
5) Controllare JOIN METHODS e JOIN TYPE
se incorretti peggiorano le performance
6) Controllare PARTITION PRUNING
se la tabella è partizionata, le prestazioni aumentano notevolmente.
7) Controllare View, Prodotto cartesiani, esecuzioni parallele...