Gli indici che il sistema consiglia, e quali creare davvero

Ogni volta che l'ottimizzatore avrebbe voluto un indice che non c'è, lo scrive. Il consiglio resta lì, e nessuno lo legge.

Poi qualcuno lo legge, trova quaranta righe, e li crea tutti.

Non farlo. È il modo più rapido di peggiorare un database. L'advisor ragiona una query alla volta: non sa quali indici hai già, non sa quante scritture prende quella tabella, e non sa che il consiglio numero 12 è quasi identico al numero 3. Non sta progettando lo schema. Sta prendendo appunti.

Che cos'è, davvero, quel consiglio

Quando l'ottimizzatore esegue una query e l'indice che gli servirebbe non c'è, fa due cose: si arrangia (spesso costruendo un indice temporaneo al volo, un MTI) e scrive in QSYS2.SYSIXADV che gli sarebbe piaciuto avere quelle chiavi.

Quella riga dice "in questa esecuzione mi sarebbe servito questo". Non dice:

se ce l'hai già, in una forma che va bene lo stesso;
se la query che l'ha generato è importante o l'ha lanciata qualcuno a mano una sera;
quanto costa mantenere quell'indice sulle scritture;
che altre otto righe della stessa tabella chiedono varianti della stessa cosa.

Tutte e quattro le risposte le devi mettere tu.

Primo passo: leggere il consiglio già condensato

Prima ancora di guardare i consigli grezzi, conviene guardarli uniti. Il sistema ha un condensatore che fonde i consigli sovrapposti della stessa tabella nelle poche forme che li coprono tutti:

SELECT *
  FROM QSYS2.CONDENSEDINDEXADVICE
 ORDER BY MTI_USED DESC, TIMES_ADVISED DESC

SELECT * perché, di questa vista, i nomi verificati sono TABLE_NAME, KEY_COLUMNS_ADVISED, MTI_USED e TIMES_ADVISED; le altre colonne le dice la query sul catalogo in fondo alla ricetta. Quaranta consigli che diventano sei è lo scenario normale. Quei sei sono un punto di partenza ragionevole; i quaranta non lo erano mai stati.

Mettila in conto: è lenta. Quella vista non legge una tabella, esegue il condensatore: rilegge tutti i consigli grezzi e li fonde ogni volta che la interroghi. Anche un semplice COUNT(*) paga lo stesso prezzo, e su un sistema dove nessuno ha mai guardato SYSIXADV può volerci parecchio.

Il rimedio è pagarla una volta sola e poi lavorare sulla copia:

CREATE TABLE QTEMP.CONSIGLI AS
  (SELECT * FROM QSYS2.CONDENSEDINDEXADVICE) WITH DATA

Da lì in poi interroghi QTEMP.CONSIGLI quanto vuoi, ordinandola e filtrandola in pace. È anche il modo più corretto di ragionarci: i consigli restano fermi mentre li stai valutando, invece di cambiare sotto mano a ogni nuova query che qualcuno lancia sul sistema.

Quanti siano i consigli grezzi lo dice questa, ed è un numero che vale la pena guardare:

SELECT COUNT(*) FROM QSYS2.SYSIXADV WHERE TABLE_SCHEMA = 'PRODLIB'

Se sono decine di migliaia, il primo problema non sono gli indici mancanti: è che quell'elenco non l'ha mai letto nessuno, e ci sono dentro anni di query lanciate una volta sola.

E possono essere molte di più: prima di 7.5 TR7 e 7.6 TR1 le righe non venivano mai potate (più sotto). Con milioni di righe il condensatore, che le rilegge tutte a ogni interrogazione, può non rispondere in un tempo ragionevole.

Quando l'elenco è enorme: restringere prima, condensare dopo

Con milioni di righe non si parte dal condensatore. Si parte dal sottoinsieme che ha davvero un segnale, e lo si legge dai consigli grezzi con una passata sola:

SELECT TABLE_NAME, KEY_COLUMNS_ADVISED,
       MTI_USED, TIMES_ADVISED, LAST_ADVISED
  FROM QSYS2.SYSIXADV
 WHERE TABLE_SCHEMA = 'PRODLIB'
   AND MTI_USED > 0
   AND LAST_ADVISED > CURRENT TIMESTAMP - 30 DAYS
 ORDER BY MTI_USED DESC
 FETCH FIRST 50 ROWS ONLY

Due filtri. MTI_USED > 0 tiene solo i casi in cui la macchina ha davvero costruito un indice temporaneo; non le opinioni, i fatti. LAST_ADVISED recente tiene solo quello che sta succedendo adesso, non l'archeologia. Il consolidamento con la regola del prefisso, più avanti, lo fai a mano su cinquanta righe invece che su milioni.

Perché quell'elenco è cresciuto così

Db2 for i pota da solo le righe vecchie, ma il meccanismo è recente: la variabile globale SYSIBMADM.QIBM_SYSIXADV_BY_DAYS dice per quanti giorni tenere una riga dopo il suo ultimo aggiornamento, e arriva con IBM i 7.6 TR1 e 7.5 TR7. Prima non c'era nessuna potatura: le righe si accumulavano e basta.

Quanti giorni sono impostati adesso:

VALUES SYSIBMADM.QIBM_SYSIXADV_BY_DAYS

Il valore predefinito è 365. Su un archivio già cresciuto a dismisura ha senso abbassarlo:

CREATE OR REPLACE VARIABLE SYSIBMADM.QIBM_SYSIXADV_BY_DAYS
      INTEGER DEFAULT 180

Va ricreata, non impostata. Un SET dentro una sessione vale solo per quel job: tutti gli altri continuano a vedere il valore predefinito. Per cambiarlo davvero serve il CREATE OR REPLACE VARIABLE qui sopra, e si controlla rilanciando il VALUES.

Se il sistema è su un rilascio precedente, la variabile non esiste e la potatura automatica non c'è. In quel caso l'unica pulizia è dal Navigator, che sotto Index Advisor offre Clear All Advised Indexes (azzera tutto e riparte da un foglio bianco) insieme a Condense e a Prune, che toglie i consigli riferiti a tabelle che non esistono più.

Azzerare è una decisione, non manutenzione. Con Clear All perdi la storia, comprese le tabelle che ricevono attenzione solo alla chiusura di fine mese o di fine anno. Se lo fai, fallo sapendo che per qualche settimana i consigli saranno pochi e parziali, e che è meglio farlo dopo aver messo da parte con una CREATE TABLE ... AS le righe con MTI_USED alto, che sono quelle che ti interessavano.

Secondo passo: guardare i consigli grezzi, ma per i segnali giusti

SELECT TABLE_NAME, KEY_COLUMNS_ADVISED,
       TIMES_ADVISED, MTI_USED, MTI_CREATED,
       FIRST_ADVISED, LAST_ADVISED, ESTIMATED_CREATION_TIME
  FROM QSYS2.SYSIXADV
 WHERE TABLE_SCHEMA = 'PRODLIB'
 ORDER BY MTI_USED DESC, TIMES_ADVISED DESC
 FETCH FIRST 20 ROWS ONLY

Nota l'ordinamento: MTI_USED prima di TIMES_ADVISED.

MTI_USED conta quante volte il sistema ha davvero costruito e usato un indice temporaneo per sopperire. È il segnale più forte che esista, perché non è un'opinione dell'ottimizzatore: è lavoro che la macchina ha realmente fatto, ripetutamente, perché tu non le avevi dato lo strumento.

TIMES_ADVISED alto conta, ma va letto insieme alle date. Un consiglio con TIMES_ADVISED = 300 concentrato fra FIRST_ADVISED e LAST_ADVISED di un solo pomeriggio è quasi sempre qualcuno che ha rilanciato la stessa query in un ciclo. Trecento volte distribuite su sei mesi sono un'altra cosa.

Un consiglio isolato, con TIMES_ADVISED = 1 e una data di tre mesi fa, non merita un indice permanente su una tabella di produzione.

Le due colonne che rispondono a "ne vale la pena"

Frequenza e MTI dicono quanto spesso. Queste due, in SYSIXADV, dicono quanto pesa:

MOST_EXPENSIVE_QUERY: quanto è costata la peggiore delle query che hanno generato quel consiglio. Un consiglio nato da una query da qualche millisecondo pesa poco anche se compare cento volte; uno nato da una query da quaranta secondi merita attenzione anche se compare tre volte.

TABLE_SIZE: quanto è grande la tabella. Un indice mancante su un'anagrafica da quattrocento righe si nota appena; lo stesso indice mancante su una tabella di movimenti da dieci milioni di righe si nota in ogni interrogazione.

SELECT TABLE_NAME, KEY_COLUMNS_ADVISED, MTI_USED, TIMES_ADVISED,
       MOST_EXPENSIVE_QUERY, TABLE_SIZE
  FROM QSYS2.SYSIXADV
 WHERE TABLE_SCHEMA = 'PRODLIB'
   AND MTI_USED > 0
 ORDER BY MOST_EXPENSIVE_QUERY DESC
 FETCH FIRST 20 ROWS ONLY

La domanda finale non è «quante volte me l'ha consigliato» ma «quanto tempo mi restituisce, e su quante righe».

Terzo passo: consolidare a mano, con la regola del prefisso

Questo è il passaggio che l'advisor non fa per te e che vale più di tutti gli altri.

Un indice su (A, B, C) serve anche le query che cercano per (A) e per (A, B). Le chiavi si leggono da sinistra: qualunque prefisso è coperto.

Quindi se hai tre consigli sulla stessa tabella:

(CODCLI)
(CODCLI, DTAORD)
(CODCLI, DTAORD, STATO)

non sono tre indici. È un indice solo, il terzo, e copre tutti e tre i casi.

Attenzione al contrario, che è l'errore speculare: (DTAORD, CODCLI) non è coperto da (CODCLI, DTAORD). L'ordine delle colonne conta, e non è intercambiabile.

La domanda da farsi su ogni gruppo di consigli è sempre la stessa: qual è il numero minimo di indici che copre tutti questi prefissi? Quasi sempre la risposta è uno o due.

Che cosa costa un indice, in concreto

Un indice non è gratis quando non lo usi. È gratis solo se nessuno scrive mai su quella tabella.

Su ogni scrittura. Ogni INSERT, UPDATE e DELETE deve aggiornare ogni indice della tabella. Su un batch notturno che inserisce milioni di righe, tre indici e dodici sono due batch diversi.

Ed è il guasto peggiore da diagnosticare, perché il sintomo compare lontano dalla causa: qualcuno ha creato degli indici per accelerare una stampa, e tre settimane dopo qualcun altro si chiede perché il caricamento ordini è diventato lento.

Sullo spazio, e su tutto ciò che lo attraversa. Gli indici occupano disco, e allungano i tempi di salvataggio, di ripristino e di riorganizzazione. Su una tabella grande, un indice in più è un pezzo in più da salvare tutte le notti.

Quarto passo: creare, e poi controllare che sia servito

L'indice si crea normalmente:

CREATE INDEX PRODLIB.ORDINI_CLI_DATA_STATO
    ON PRODLIB.ORDINI (CODCLI, DTAORD, STATO)

Poi però va verificato che l'abbia usato davvero. Creare un indice e non controllare è come non averlo creato, con lo svantaggio di pagarlo su ogni scrittura:

SELECT INDEX_NAME, LAST_QUERY_USE, QUERY_USE_COUNT
  FROM QSYS2.SYSINDEXSTAT
 WHERE TABLE_SCHEMA = 'PRODLIB'
   AND INDEX_NAME = 'ORDINI_CLI_DATA_STATO'

Se dopo qualche giorno di lavoro normale QUERY_USE_COUNT è ancora zero, quell'indice non sta servendo a nessuno. Toglilo.

Uno per volta. Crea un indice, lascia passare un ciclo di lavoro vero (un giorno, una chiusura, un batch notturno) e guarda. Se ne crei sei insieme e qualcosa peggiora, non saprai quale dei sei.

Come si ripara, se il danno è già fatto

Se qualcuno ha già creato tutto quello che l'advisor suggeriva, la strada indietro c'è, ed è la stessa vista:

SELECT TABLE_NAME, INDEX_NAME,
       LAST_QUERY_USE, QUERY_USE_COUNT,
       LAST_STATISTICS_USE, QUERY_STATISTICS_COUNT
  FROM QSYS2.SYSINDEXSTAT
 WHERE TABLE_SCHEMA = 'PRODLIB'
   AND (QUERY_USE_COUNT = 0 OR QUERY_USE_COUNT IS NULL)
 ORDER BY TABLE_NAME

Gli indici con conteggi a zero e date di ultimo utilizzo vuote sono candidati alla cancellazione: nessuna query li ha scelti, e ogni scrittura li paga.

Guarda anche le colonne delle statistiche. Un indice può non essere mai stato scelto per eseguire una query e servire lo stesso all'ottimizzatore per stimare quante righe tornerebbero : è quello che dicono LAST_STATISTICS_USE e QUERY_STATISTICS_COUNT. Prima di cancellare, controlla che siano a zero anche quelle.

Due cautele prima di lanciare DROP INDEX:

I conteggi partono dall'ultimo azzeramento. IBM li definisce «dall'ultima volta che le statistiche d'uso sono state azzerate»: un indice «mai usato» da un azzeramento recente non dimostra niente. Serve una finestra di osservazione che comprenda un ciclo completo: la chiusura di fine mese usa indici che il resto dell'anno non tocca nessuno.
Non tutto ciò che è indice è tuo. Vincoli di chiave e file logici DDS compaiono in queste viste. Cancellare l'indice che sostiene una chiave primaria non è una pulizia.

Rilasci. QSYS2.SYSIXADV e QSYS2.CONDENSEDINDEXADVICE hanno colonne che sono cambiate nei rilasci. Se una query risponde SQL0206, l'elenco vero lo dà il catalogo:

SELECT TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE
  FROM QSYS2.SYSCOLUMNS
 WHERE TABLE_SCHEMA = 'QSYS2'
   AND TABLE_NAME IN ('SYSIXADV', 'CONDENSEDINDEXADVICE')
 ORDER BY TABLE_NAME, ORDINAL_POSITION

SYSIBMADM.QIBM_SYSIXADV_BY_DAYS, che pota da sola i consigli vecchi, arriva con 7.6 TR1 e 7.5 TR7: sui rilasci precedenti non esiste e l'elenco cresce senza limite.

QSYS2.SYSINDEXSTAT riguarda i soli indici SQL; per vedere anche file logici e vincoli di chiave si usa QSYS2.SYSPARTITIONINDEXSTAT.

Fonti.