Errori pericolosi

lunedì 7 febbraio 2011 alle 07:49 | Pubblicato su Diario, Installation and Configuration, Performance Tuning, Varie | Lascia un commento
Tag: , ,

Nel giro di pochi mesi mi sono trovato ad analizzare stranissimi comportamenti di Oracle in esecuzione di query, con pericolosi errori nei risultati.  La prima volta che mi è capitato è stato su una istanza 10.1.0.3 32 bit su Windows,  in tale occasione si trattava di una macchina di sviluppo e potei fare un po’ di analisi che però non mi diedero grande aiuto. In quell’occasione la creazione di un indice specifico, comunque necessario, faceva si che la query restituisse un risultato corretto; risolto così il problema specifico lasciai un po’ perdere, nel frattempo la macchina è stata sostituita e così ho perso anche i trace che feci a suo tempo.

Poche settimane fa mi è stato sottoposto un problema analogo, questa volta però su un database di produzione, 10.1.0.5 64 bit su HP-UX.

Il problema mi è stato sottoposto da un collega che stava facendo delle indagini sui dati e che casualmente si era accorto che sostituiendo un criterio di ricerca (nella clausola WHERE) con un’altro equivalente (ma basato sulla descrizione in una tabella di decodifica anziché sull’ID numerico) i risultati della query cambiavano, erroneamente. Riporto i due casi test:


select ANAG1MATRICOLA
from ANAGRAFICA1 , ANAGRAFICA2 , DECODIFICA1
where ANAG1ID_ANAG2=ANAG2ID
and ANAG1ID_DECOD1=DECOD1ID
and ANAG1VISIBILE = '1'
and DECOD1DESCRIZIONE = 'CC-SMMAN'

0 RECORD

-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |       |       |  7419 (100)|          |
|   1 |  NESTED LOOPS                  |                | 17482 |   699K|  7419   (4)| 00:00:08 |
|   2 |   MERGE JOIN                   |                | 34949 |  1262K|  7202   (1)| 00:00:08 |
|   3 |    SORT JOIN                   |                |  1446K|    34M|  7199   (1)| 00:00:08 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| ANAGRAFICA1    |  1446K|    34M|  7199   (1)| 00:00:08 |
|   5 |      INDEX FULL SCAN           | IDXANAG1_11     |  8408 |       |    22   (5)| 00:00:01 |
|*  6 |    SORT JOIN                   |                |     1 |    12 |     3  (34)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID| DECODIFICA1    |     1 |    12 |     2   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN          | IDXDECOD101    |     1 |       |     1   (0)| 00:00:01 |
|*  9 |   INDEX UNIQUE SCAN            | IDXANAG2       |     1 |     4 |     0   (0)|          |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 4 - filter("ANAG1VISIBILE"='1')
 6 - access("ANAG1ID_DECOD1"="DECOD1ID")
 filter("ANAG1ID_DECOD1"="DECOD1ID")
 8 - access("DECOD1DESCRIZIONE"='CC-SMMAN')
 9 - access("ANAG1ID_ANAG2"="ANAG2ID")



select ANAG1MATRICOLA
from ANAGRAFICA1 , ANAGRAFICA2 , DECODIFICA1
where ANAG1ID_ANAG2=ANAG2ID
and ANAG1ID_DECOD1=DECOD1ID
and ANAG1VISIBILE = '1'
and DECOD1ID = 18

357 RECORD

------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |       |       | 14319 (100)|          |
|*  1 |  HASH JOIN          |              |   481 | 15392 | 14318  (19)| 00:00:14 |
|   2 |   NESTED LOOPS      |              |   141 |   987 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN| IDXDECOD1    |     1 |     3 |     0   (0)|          |
|   4 |    INDEX FULL SCAN  | IDXANAG2     |   141 |   564 |     1   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL | ANAGRAFICA1  |   961 | 24025 | 14317  (19)| 00:00:14 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - access("ANAG1ID_ANAG2"="ANAG2ID")
 3 - access("DECOD1ID"=18)
 5 - filter(("ANAG1ID_DECOD1"=18 AND "ANAG1VISIBILE"='1'))


TABLE_NAME                     NUM_ROWS
------------------------------ ----------------------
ANAGRAFICA2                     141
ANAGRAFICA1                     1732607
DECODIFICA1                     86

 

 

Nel primo caso la ricerca per descrizione (DECOD1DESCRIZIONE = ‘CC-SMMAN’) dava un risultato sbagliato di 0 record, nel secondo caso, andando per ID il risultato era di 357 record correttamente. Il motivo sta nel piano di esecuzione e più precisamente nel fatto che nel primo caso Oracle si ostina a usare un indice (IDXANAG1_11) che è su un campo che in questa neppure compare e che soprattutto, come si può ricavare anche dal piano sopra riportato ha valori non nulli per soli 8408 record sugli oltre 1,7 milioni presenti in quella tabella (ANAGRAFICA1).

Essendo un ambiente di produzione non possibilità di fare test “invasivi” come feci a suo tempo sul database di sviluppo, in quel caso provai a rimuovere e ricreare l’indice “sbagliato” e oracle insisteva ad utilizzarlo, esportai le tabelle e le reimportai sullo stesso database su uno schema a parte e l’errore non si ripresentava. Di mezzo, allora come oggi c’è un MERGE JOIN e sul forum di Oracle DBA Italia, dove ho sottoposto il mio problema mi è stato indicato  il bug 5921386 che calza abbastanza, anche se li si parla di MERGE JOIN OUTER e il mio è un MERGE JOIN normale, però a un certo punto nella nota oracle compare: “full index scan with potenal of  NULL value used inouter join” che è ciò che ho osservato io nel mio caso.

Hint SQL che forzano un piano di esecuzione di verso (tipo NO_USE_MERGE, o INDEX su altri indici della tabella ANAGRAFICA1) funzionano, rimane però una grossa e pericolosa falle nel CBO di oracle 10.1.05.

Lascia un commento »

RSS feed for comments on this post. TrackBack URI

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: