Il problema di cui parlo in questo post si riferisce ad una query eseguita in PARALLEL su un Exadata versione 12.2 che utilizzava una tabella T1 in modalità compressa. Avevo riscontrato una errata stima della cardinality su una tabella di sole 204 righe. L’errata stima su questa tabella comprometteva un piano di esecuzione complesso, venivano scelti come metodo di accesso alcuni Nested Loop su tabelle di centinaia di milioni di righe. Questo era il punto critico del piano di esecuzione:

----------------------------------------------------+--------
| Id | Operation                       | Name       | Rows  |
----------------------------------------------------+--------
...
| 18 |    HASH JOIN                    |            | 1     |
| 19 |      JOIN FILTER CREATE         | :BF0002    | 1     |
| 20 |       TABLE ACCESS STORAGE FULL | T1         | 1     |
| 21 |      JOIN FILTER USE            | :BF0002    | 10M   |
| 22 |       PX BLOCK ITERATOR         |            | 10M   |
| 23 |        TABLE ACCESS STORAGE FULL| T2         | 10M   |
...
-------------------------------------------------------------

L’optimizer Oracle riteneva di uscire dalla tabella T1 con 0 righe (1 di default) e la stima sull’Hash Join di riga 18 risultava errata. In realtà il numero di righe estratte dalla tabella T1 era superiore a 100 e il risultato (reale) dell’hash join di fatto era di qualche milione di righe non di 1 riga. Il problema veniva quindi propagato in tutto il piano di esecuzione con conseguenze disastrose. La tabella T1 come già detto era una tabella molto piccola (solo 204 righe) e aveva la particolarità di essere stata definita utilizzando la Compression (preferisco evitare la compressione su una tabella piccola come questa). I frequenti aggiornamenti (Update) sulla tabella hanno cambiato la posizione delle righe (righe migrate) lasciando quasi tutti blocchi vuoti , questa era la situazione sullo stato di frammentazione della tabella:

Unformatted Blocks    .....................   112
FS1 Blocks (0-25)     .....................   1
FS2 Blocks (25-50)    .....................   0
FS3 Blocks (50-75)    .....................   0
FS4 Blocks (75-100)   .....................   15
Full Blocks           .....................   5
Total Blocks          ...................     8,232
Total Bytes           ...................     67,436,544
Total MBytes          ...................     64
Unused Blocks         ...................     8,064
Unused Bytes          ...................     66,060,288
Last Used Ext FileId  ...................     18
Last Used Ext BlockId ...................     147,794,688
Last Used Block       ...................     128

la tabella T1 aveva quindi 8382 blocchi di cui 5 pieni e 16 parzialmente vuoti e più di 8000 blocchi vuoti.

Che tipo di impatto aveva lo stato di frammentazione della tabella T1 sulla stima della sua cardinality?

Nella query analizzata insisteva sulla tabella T1 un predicato del tipo BETWEEN dt_init_val AND dt_end_val e anche con statistiche fresche il Query Optimizer decideva di utilizzare un campionamento dinamico (molto sfortunato) pari a 6 per cercare di capire il numero di righe che si trovavano nell’intervallo (dt_init_val .. dt_end_val).

In particolare, veniva utilizzato un campionamento di solo 1,5% dei blocchi e la sfortuna (per Oracle ma anche mia) è stata quella che i 128 blocchi utilizzati come sample erano tutti blocchi vuoti per effetto dell’alta frammentazione della tabella.

Disastro!!! Il Query Optimizer quindi credeva di uscire con 0 righe dalla tabella T1, il piano risultava decisamente sbagliato.

Ecco la sezione della trace 10053 dove veniva stimata la selectivity del predicato:

** Executed dynamic sampling query:
     level : 6
     sample pct. : 1.550293
     actual sample size : 0
     filtered sample card. : 0
     orig. card. : 204
     block cnt. table stat. : 8192
     block cnt. for sampling: 8192
     max. sample block cnt. : 128
     sample block cnt. : 127 min. s
     el. est. : 0.37009804
** Using single table dynamic sel. est. : 0.00000000

La query utilizzata dal Query Optimizer per calcolare la cardinality era la seguente:

** Generated dynamic sampling query:
     
        query text :
SQL> SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE RESULT_CACHE(SNAPSHOT=3600) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */
    2     NVL(SUM(C1),0), NVL(SUM(C2),0)
    3     FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T1") FULL("T1") NO_PARALLEL_INDEX("T1") */
    4     1 AS C1,
    5     CASE WHEN "T1"."DT_INIT_VAL"<=TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')      6 AND "T1"."DT_END_VAL">=TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
    7    THEN 1
    8    ELSE 0
    9    END AS C2
    10   FROM "SETTEMBRINO"."T1" SAMPLE BLOCK (1.550293 , 1) SEED (1) "T1"
    11   ) SAMPLESUB;

NVL(SUM(C1),0) NVL(SUM(C2),0) 
-------------- --------------
             0              0

Come detto in precedenza l’intervento del Dynamic Sampling provocava un sample dell’1,55% dei blocchi, il valore 0 presente nella colonna NVL(SUM(C2),0) rappresenta il valore frutto del calcolo della cardinality applicando il predicato sulla tabella T1.

Ovviamente capito il problema trovata una soluzione (da un punto di vista delle performance), con la tabella riorganizzata (ALTER TABLE MOVE) il piano risultava ottimale e i blocchi sono diminuiti da 8192 a soli 37, un campione del 100% veniva utilizzato dal Query Optimizer che da quel momento aveva la stima sulla cardinality corretta. Quindi prestare attenzione quando si definisce una tabella.