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 TABLE_NAME, KEY_COLUMNS_ADVISED, INDEX_TYPE,
       TIMES_ADVISED, MTI_USED, LAST_MTI_USED,
       MOST_EXPENSIVE_QUERY, AVERAGE_QUERY_ESTIMATE,
       TABLE_SIZE, ESTIMATED_CREATION_TIME
  FROM QSYS2.CONDENSEDINDEXADVICE
 WHERE TABLE_SCHEMA = 'PRODLIB'
 ORDER BY MTI_USED DESC, TIMES_ADVISED DESC

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 può essere molto peggio di decine di migliaia. Su un sistema in produzione da anni si arriva tranquillamente a decine di milioni di righe: a quel punto il condensatore non è lento, è inutilizzabile, perché deve rileggerle e fonderle tutte a ogni interrogazione.

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, e tolgono di mezzo il 99% del rumore. 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 tre colonne che rispondono a "ne vale la pena"

Frequenza e MTI dicono quanto spesso. Queste dicono quanto pesa, e sono quelle che distinguono un indice utile da uno inutile:

MOST_EXPENSIVE_QUERY: quanto è costata la peggiore delle query che hanno generato quel consiglio. Un consiglio nato da una query da qualche millisecondo non cambia la vita a nessuno, anche se compare cento volte. Uno nato da una query da quaranta secondi merita attenzione anche se compare tre volte.

TABLE_SIZE e MAX_ROW: quanto è grande la tabella. Un indice mancante su una tabella di anagrafica con quattrocento righe è una nota di colore: il sistema la scorre tutta e non se ne accorge nessuno. Lo stesso indice mancante su una tabella di movimenti da dieci milioni di righe è il motivo per cui il gestionale sembra lento.

MTI_USED_FOR_STATS: questa è la sfumatura che evita un errore. Un indice temporaneo può essere costruito per eseguire la query, oppure solo perché l'ottimizzatore voleva stimare quante righe sarebbero tornate. Sono due esigenze diverse: la prima chiede un indice, la seconda spesso si risolve con le statistiche, che costano molto meno. MTI_USED alto e MTI_USED_FOR_STATS vicino allo zero è il caso pulito in cui l'indice serve davvero.

La domanda finale, insomma, 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 due milioni di righe, la differenza fra tre indici e dodici non è teorica: è il batch che finisce alle 3 invece che all'1, e prima o poi non finisce prima dell'apertura.

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.

Sull'ottimizzatore stesso. Più indici ci sono, più piani deve valutare. Il caso limite (decine di indici quasi identici sulla stessa tabella) può rendere la scelta del piano più cara del guadagno.

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 si azzerano all'IPL. Un indice "mai usato" su una macchina riavviata ieri 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 è presente da molti rilasci. Le colonne MTI_USED, MTI_CREATED e MTI_USED_FOR_STATS sono state aggiunte dopo: se la query risponde SQL0206 (colonna non trovata) toglile e il resto funziona.

QSYS2.CONDENSEDINDEXADVICE è la vista del condensatore, e porta le stesse colonne dei consigli grezzi più quelle di giudizio: MOST_EXPENSIVE_QUERY, AVERAGE_QUERY_ESTIMATE, TABLE_SIZE, MAX_ROW, LAST_MTI_USED. Per sapere che cosa hai davvero sul tuo sistema:

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE
  FROM QSYS2.SYSCOLUMNS
 WHERE TABLE_SCHEMA = 'QSYS2'
   AND TABLE_NAME = 'CONDENSEDINDEXADVICE'
 ORDER BY 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.

• IBM — QSYS2.SYSINDEXSTAT View
• IBM — Condensing index advice
• IBM — QSYS2.INDEX_ADVICE procedure
• IBM Documentation — SYSPARTITIONINDEXES
• IBM — SYSIBMADM.QIBM_SYSIXADV_BY_DAYS global variable