Ottimizzazione di una query

martedì 23 febbraio 2010 alle 23:13 | Pubblicato su Performance Tuning | 19 commenti
Tag: ,

Una volta scrivevo molto di più; adesso sono settimane che penso: “ecco uno spunto per un post sul mio blog!” poi passa la giornata e mi passa ogni stimolo. Insomma sto passando un periodo molto confuso.

Lo spunto per questo post risale a qualche giorno fa (forse ormai saranno anche settimane) si tratta di una prova che ho fatto nel disperato tentativo di far andare più veloce una query. Si tratta di una query relativamente semplice, ma che lavora su una tabella molto grossa (attualmente viaggia sui 35 milioni di record e aumenta di circa 80000 record al giorno). La query viene utilizzata per un report.  Il problema di questa tabella, oltra a essere grande è che viene interrogata in decine di modi diversi quindi anche determinare degli indici validi è difficile. Però essendomi stato richiesto in particolare di ottimizzare (o almeno provarci) una query specifica ho fatto un po’ di prove. Inizialmente ho fatto delle prove direttamente in produzione, poi non riuscendo ad ottenere nulla ho deciso di provare a vedere che impatto potevano avere degli indici mirati, quindi ho esportato la tabella e l’ho importata in ambiente di test (dove la tabella non era popolata in modo significativo). Ho fatto due tentativi di indici e mi sono reso conto che Oracle preferiva fare uno “INDEX SKIP SCAN” su un indice preesistente, apparentemente meno selettivo, piuttosto che usare un nuovo indice dedicato e apparentemente più selettivo. Sono giunto alla conclusione (assolutamente empirica) che il clustering factor incida negativamente, anche se in realtà pare alto per tutti gli indici.

Ho deciso allora di provare gli strumenti di Oracle il quale ha scoperto che ci poteva essere un piano di esecuzione migliore (che usava l’indice che avevo creato io)

Il risultato è quello dell’immagine, il primo picco è l’esecuzione normale, la seconda con l’outline il SQL Profile (vedi nota) suggerita da Oracle.

La conclusione cui voglio giungere (perché io non amo le interfacce grafiche e non credo negli strumenti che risolvono tutti i problemi) è che l’ottimizzatore Oracle funziona bene, mentre sugli strumenti di diagnostica e di “tuning” (o ottimizzazione) sembra (qui però sono sulla 10.2.0.4) ci sia qualcosa da dire🙂

Nota (17/03/2010):

Grazie a Kyle Hailey che mi ha fatto notare che poteva essere un SQL Profile e non una Outline come avevo scritto inizialmente. Questo evidenzia una mia grossa lacuna al riguardo che spero di colmare presto e conto di scriverne un post.

19 commenti »

RSS feed for comments on this post. TrackBack URI

  1. Ma quanto semplice è sta query? Possibile che Oracle non riesca a tunare in questi casi?
    Scusa la banalità ma le statistiche (object e system) sono sufficientemente adeguate?
    Anche la storia dell'”INDEX SKIP SCAN” è un bel mistero. In passato mi sono imbattuto in piani che lo contenevano ed erano sempre inefficienti, e mi sembra di aver sempre letto che dovrebbe essere l’ultima spiaggia per Oracle, quindi evidentemente non è un’operazione performante.

    • Riporto la query, anche se senza informazioni sui dati non credo aiuti molto:

      SELECT campo_1 AS C1,
      COUNT(campo_2) AS c2
      FROM (SELECT campo_1, campo_2
      FROM tabellone
      WHERE campo_1 IS NOT NULL
      AND NVL (campo_3, 0) = 1
      AND campo_4 > 0
      AND campo_5 < 100
      AND campo_6 in (124)
      GROUP BY campo_1, campo_2
      )
      GROUP BY campo_1

      La tua richiesta non è banale, le statistiche sugli oggetti sono aggiornate e apparentemente adeguate, quelle di sistema sono quelle di default, ma secondo me adeguate.
      Tieni conto che dal punti di vista pratico, ovvero dal tempo di esecuzione (la misura che conta veramente per l’utente) il piano con l’INDEX SKIP SCAN è vereamente quello più efficente.

      • Ciao Cristian,
        i tablespace dove risiedono gli oggetti sono ASSM?
        Siccome parlavi di clustering_factor, farei subito una prova della cosa su di un tablespace di tipo MSSM, non fosse altro per togliermi il dubbio

      • Ciao Alessandro,
        perché tiri in ballo il “Segment Space Management”? Che legame c’è con tra SSM e il clustering factor?

      • Lo confesso, non ho mai usato db (se non per qualche test particolare) con tablespace MSSM, anche questo è ASSM e in questo momento no so se riuscirò fare un test, anche perché non so se ho spazio sulla macchina di test.

    • Ciao Cristian,

      premetto che non conosco il contesto della query, applicativo-indici-DB, ma non si può semplificare la query in questo modo?

      SELECT campo_1 AS C1,
      COUNT(distinct campo_2) AS c2
      FROM tabellone
      WHERE campo_1 IS NOT NULL
      AND campo_3 = 1
      AND campo_4 > 0
      AND campo_5 < 100
      AND campo_6 = 124
      GROUP BY campo_1;

      • Talos,
        credo che Cristian abbia sostituito dei valori al posto di variabili (un indizio è che scrive “campo_6 in (124)”, invece di come semplicemente indichi tu: campo_6=124); in tal caso non sarebbe naturalmente vero:
        NVL(campo_3, 0) = var campo_3 = var
        … inoltre la funzione NVL() impedirebbe di utilizzare un indice in campo_3 (a meno di non impiegare function index) e Cristian questo lo sa.

        Per il resto concordo con te.

      • Ciao Talos.
        Si, io francamente non ci avevo pensato, ho fatto una prova, ma dal punto di vista del costo computazione non cambia nulla. Per il discorso delle veriabili è come dice Roberto

  2. Accidenti, credevo che il tabellone fosse almeno una delle tabelle implicate in qualche join. La query è veramente semplice, al di là dell’inline viev per una post elaborazione, è proprio lineare SELECT- FROM-WHERE… e Oracle non riesce a suggerire gli indici ottimali! e dire che il Tuning Pack se lo fa pagare caro, e pretende anche l’Enterprise Edition!
    La questione statistiche è certamente centrale, perché sono i dati su cui Oracle lavora, dicevo banale perché so benissimo che ne conosci l’importanza, ma è l’unico apparente motivo che potrebbe portare l’Automatic Tuning Optimizer ad sub-ottimali suggerimenti.

    Anche gli istogrammi sono buoni?

  3. Ciao Roberto,
    mi è venuto in mente del materiale sul libro di Jonathan Lewis Cost-Based Fundamentals , mi pare il Capitolo 5, quando ho letto che Cristian lo ha menzionato (il clustering_factor)

    • Eh, quel libro non ho mai avuto il piacere (o meglio la necessaria volontà) di leggere😦 . Il capitolo che citi in effetti pone in relazione MSSM e clustering factor. E io che pensavo che MSSM c’entrasse al massimo con le performances legate alle INSERTs… troooppo scarso!

      • Cribbio! ce l’ho qua sotto il telefono! purtroppo anche io non ce l’ho mai fatta a leggerlo tutto, appena ho un attimo provo a dare un’occhiata al capitolo

      • come ho letto clustering_factor mi è balenata in mente questa cosa, ma mica è detto che ci sia un nesso reale con l’argomento🙂

  4. altra cosa da verficare è che INDEX SKIP SCAN viene utilizzato in genere, ma ci sono sempre deroghe e/o casi particolari come sappiamo, quando l’ordine delle colonne di un indice composto non è corretto rispetto alla cardinalità….

    Questo è un altro aspetto che valuterei ed anche per primo rispetto al discorso del clustering_factor ed ASSM (oppure MSSM con freelists multiple, vedere captitolo 5 del libro di Jonathan per i test case)

  5. he first peak is small and the second large so the conclusion is that the Suggested Outline (should be profile, not outline)is wrong?
    Not sure if it’s just my Karma but the 3 times I’ve accepted profiles by the SQL Tuning advisor the plan always came out worse.
    Sorry you don’t like the graphics. That the graphics on that web page were designed by me:
    http://sites.google.com/site/youvisualize/active-session-history

    • wow! thank you kyle to stop by here.
      First, you are right, it is SQL Profile, not Outline and yes, suggested Sql profile is wrong because makes things worst; if i’ve not misunderstood, you are confirming my experience that is that SQL profiles suggested by SQL Tuning profile are not good.
      About graphics, it is not graphics what i don’t like, because they are very useful to have a broader view, what i don’t like very much are GUI’s, also if even GUI’s are often useful🙂

      • Il nome anglofono del tuo blog ha attirato un vip, evviva! Però il contenuto italiota gli ha impedito di apportare il ben che minimo contributo😦
        (gli è solo servito per fare pubblicità al fatto che è lui che ha programmato quel pezzetto di Oracle… la vanità è sempre dietro l’angolo)
        L’argomento secondo me non è che l’SQL Tuning Advisor a volte sbaglia, ma perché in questo caso abbia sbagliato nonostante la banalità della query.
        Occasione mancata!

  6. ma si è saputo niente di quale fosse il problema?

    • non ho approfondito ulteriormente, perché come spesso accade ci si è resi conto che l’informazione estratta con quella query non era poi così importante e si è optato per la sua rimozione😐


Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

Blog su WordPress.com.
Entries e commenti feeds.

%d blogger cliccano Mi Piace per questo: