Dalla versione 9i quando un nuovo indice viene creato, in maniera silente e del tutto automatica vengono raccolte anche le statistiche sul nuovo oggetto creato. Lo stesso principio è stato utilizzato a partire dalla versione 12c su operazioni eseguite in modalità direct path quali CTAS (Create Table AS Select) e IAS (Insert AS Select) su tabelle vuote.
Con Oracle 19c questa funzionalità è stata ulteriormente estesa agli indici che in presenza di operazioni di CTAS e IAS beneficeranno del ricalcolo “on the fly” delle statistiche.
Aggiunto un nuovo row source nell’execution plan
L’indicazione sulla raccolta delle statistiche online ci viene fornita anche nel piano di esecuzione con l’aggiunta di un nuovo row source (OPTIMIZER STATISTICS GATHERING ):
SQL> EXPLAIN PLAN FOR
2 CREATE TABLE t (id1, id2, id3, str1, CONSTRAINT t_pk PRIMARY KEY (id1)) AS SELECT rownum id1, rownum id2, rownum id3, dbms_random.string('a', 10) str1
FROM dual connect by level <= 100000;
SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic'));
-------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------
| 0 | CREATE TABLE STATEMENT | |
| 1 | LOAD AS SELECT | T |
| 2 | OPTIMIZER STATISTICS GATHERING | |
| 3 | COUNT | |
| 4 | CONNECT BY WITHOUT FILTERING | |
| 5 | FAST DUAL | |
-------------------------------------------------
la presenza del nuovo row source nell’execution plan non dà comunque alcuna garanzia sulla effettiva raccolta delle online statistics a runtime.
Online statistics gathering vengono raccolte su qualsiasi tipologia di indice?
Perché no? Ho pensato. Immagino che Online Statistics Gathering in 19c faccia qualcosa di simile ad una call a dbms_stats.gather_table_stats con il parametro cascade=> true in modo tale che le statistiche possano essere calcolate anche sugli indici.
Eseguendo alcuni test però ho riscontrato che Online Statistics Gathering non viene eseguito su tutte le tipologie di indici disponibili.
Il test che sto per proporre prevede una normale CTAS preventiva con tre colonne (id1,id2 e id3) su cui verranno creati tre differenti indici. In Particolare su id1 verrà costruita la PK, su id2 verrà creato un indice UNIQUE e sul campo id3 un indice che ammetta duplicati.
La versione del database utilizzata nei test è la 19.5:
SQL> select banner_full from v$version;
BANNER FULL
-----------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
Creo una tabella T:
CREATE TABLE t (id1, id2, id3, str1, CONSTRAINT t_pk PRIMARY KEY (id1))
AS
SELECT rownum id1, rownum id2, rownum id3, dbms_random.string('a', 10) str1
FROM dual connect by level <= 100000;
ed osservo il tempo di ricalcolo delle statistiche più altri valori di riferimento:
SQL> SELECT table_name, num_rows, blocks, last_analyzed, stale_stats FROM USER_TAB_STATISTICS;
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED STALE_STATS
----------- ---------- ---------- ---------------------- -----------
T 100000 441 13-nov-2019 15:45:32 NO
A seguire creo un indice UNIQUE ed un indice normal b-tree, in totale la tabella T avrà ho creato tre indici:
SQL> CREATE UNIQUE INDEX t_unique on t(id2);
SQL> CREATE INDEX t_no_unique on t(id3);SQL> SELECT index_name, last_analyzed, stale_stats FROM USER_IND_STATISTICS WHERE table_name='T';
INDEX_NAME LAST_ANALYZED STALE_STATS
------------------------------ ------------------------------ -----------T_PK 13-nov-2019 15:45:32 NO
T_UNIQUE 13-nov-2019 15:45:32 NO
T_NO_UNIQUE 13-nov-2019 15:45:32 NO
Uno dei prerequisiti per la raccolta delle statistiche automatiche su operazioni eseguite in direct path mode è quello di non avere dati in tabella, la tabella deve essere vuota come indicato nella documentazione Oracle:
“Specifically, bulk loads do not gather statistics automatically when any of the following conditions applies to the target table, partition, or subpartition:
- The object contains data. Bulk loads only gather online statistics automatically when the object is empty. “
Se vuoi vedere tutte le altre condizioni ti rimando al seguente link, le restanti condizioni comunque non influenzano il test proposto:
Eseguo quindi una TRUNCATE TABLE sulla tabella T per pormi nelle condizioni indicate, la tabella non deve contenere dati:
SQL> truncate table t;
Per effetto della TRUNCATE le statistiche sulla tabella e sugli indici diventano STALE:
SQL> SELECT table_name, num_rows, blocks, last_analyzed, stale_stats FROM USER_TAB_STATISTICS;
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED STALE_STATS
------------ ---------- ---------- ---------------------- -----------
T 100000 441 13-nov-2019 15:45:32 YES
SQL> SELECT index_name, last_analyzed, stale_stats FROM USER_IND_STATISTICS WHERE table_name='T';
INDEX_NAME LAST_ANALYZED STALE_STATS
------------------------------ ------------------------------ -----------T_PK 13-nov-2019 15:45:32 YES
T_UNIQUE 13-nov-2019 15:45:32 YES
T_NO_UNIQUE 13-nov-2019 15:45:32 YES
Eseguo una insert in direct path:
SQL> INSERT /*+ append */ INTO t SELECT rownum id1, rownum id2, rownum id3, dbms_random.string('a', 10) str1 FROM dual connect by level <= 100000;
100000 righe create.
SQL> commit;
Commit completato
SQL> SELECT table_name, num_rows, blocks, last_analyzed, stale_stats FROM USER_TAB_STATISTICS;
TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED STALE_STATS
------------ ---------- ---------- --------------------- -----------
T 100000 441 13-nov-2019 15:45:50 NO
SQL> SELECT index_name, last_analyzed, stale_stats FROM USER_IND_STATISTICS WHERE table_name='T';
INDEX_NAME LAST_ANALYZED STALE_STATS
-------------- ----------------------- -----------
T_PK 13-nov-2019 15:45:50 NO
T_UNIQUE 13-nov-2019 15:45:50 NO
T_NO_UNIQUE 13-nov-2019 15:45:32 YES
Eseguendo una insert in direct path ho riscontrato un’anomalia, online statistics sono state eseguite come atteso sulla tabella, sulla primary key e sull’indice UNIQUE ma non sono state eseguite sull’indice B-tree che ammette valori duplicati (T_NO_UNIQUE).
Le statistiche sull’indice T_NO_UNIQUE restano STALE. Online Statistic Gathering non sono state eseguite su questo indice, il tempo indicato nella colonna last_analyzed è rimasto invariato.
Trace dbms_stats on statistics gathering
Ho quindi verificato l’esecuzione di online statistics eseguendo una trace dbms_stats:
modifico il nome del file di trace ed apro la trace.
alter session set tracefile_identifier = 'online_stats_trace';exec dbms_stats.set_global_prefs('TRACE',4+8+16+32);
eseguo una direct path insert
INSERT /*+ append */ INTO t SELECT rownum id1, rownum id2, dbms_random.string('a', 10) str1 FROM DBA_OBJECTS;
commit;
chiudo la trace
exec dbms_stats.set_global_prefs('TRACE',0);
Nell’output del file di trace trovo l’indicazione che Online Statistics Gathering for bulk load sono state eseguite, la seguente frase trovata nel file di trace lo conferma:
"DBMS_STATS: postprocess online optimizer stats gathering for SETTEMBRINO.T"
. di seguito riporto l’estratto del file di trace:
DBMS_STATS: postprocess_stats: owner: SETTEMBRINO tabname: T tobjn: 82227 fobjn: flag: 2 cht.count: 0
DBMS_STATS: postprocess_stats on SETTEMBRINO.T tobjn: 82227 fobjn: pname: spname:
DBMS_STATS: postprocess online optimizer stats gathering for SETTEMBRINO.T: save statistics
DBMS_STATS: RAWIDX SELMAPPOS RES NNV NDV SPLIT RSIIZE ROWCNT
DBMS_STATS: -------------------------------------------------------------------------------------
DBMS_STATS: 1 1 72976 73968 3 281070 72976
DBMS_STATS: RAWIDX SELMAPPOS RES NNV NDV SPLIT RSIIZE ROWCNT
DBMS_STATS: -------------------------------------------------------------------------------------
DBMS_STATS: 2 4 72976 NULL NULL 281070 NULL
DBMS_STATS: RAWIDX SELMAPPOS RES NNV NDV SPLIT
RSIIZE ROWCNT
Trace online statistics with 10046 event
Nel trace file generato attivando l’evento 10046 ho quindi individuato una call a due procedure DBMS_STATS non documentate che dovrebbero poi essere le due procedure che si occupano di eseguire Online Statistics Gathering:
dbms_stats.postprocess_stats(:owner, :tabname, :tobj, :fobjn, :flags, :rawstats, :selmap, :clist, null, null);
e
dbms_stats.postprocess_indstats(:ilist, :flags);
Credo di non sbagliare affermando che postprocess_stats è la procedura DBMS_STATS che si occupa di eseguire le statistiche online sulla tabella mentre postprocess_indstats è la procedura che si occupa di eseguirle sugli indici. A conferma di quanto appena affermato ho individuato due call postprocess_stats, una immediatamente dopo la CTAS ed una dopo la IAS ed una call postprocess_indstats subito dopo la IAS.
Conclusioni
Dai test effettuati sembrerebbe quindi che Online Statistics Gathering for Bulk Loads non vengano eseguite su indici B-tree che consentano valori duplicati. Non mi vengono in mente motivi plausibili per giustificare il comportamento riscontrato nei test, devo quindi ritenere che si tratti di un bug.
Online Statistics Gathering inoltre non vengono eseguite anche su indici bitmap e constraint deferrable.
Qualsiasi commento e/o correzione è la benvenuta :)
English versionOracle Specialist at Performance Team @ ICTeam Spa