Ricerche case sensitive in Oracle 11gR2 e 12cR1

venerdì 22 agosto 2014 alle 22:03 | Pubblicato su 11g, 12c, Diario, Performance Tuning | Lascia un commento
Tag: , ,

Questo post è un aggiornamento del post “Database case sensitive su 10.2.0.3“,  aggiornamento necessario, essendo il post risalente ad oltre sette anni fa. In realtà ho fatto qualche test in questi sette anni ma non ben formalizzato, quando poi mi è stato chiesto per l’ennesima volta se e come era possibile fare ricerche case insensitive su database Oracle (versione 11.2) ho prima dato la solita risposta, poi però mi è venuto lo scrupolo di ricontrollare se fosse cambiato qualcosa sulla versione che attualmente usiamo maggiormente (11.2.0.3 o 11.2.0.4) e sulla nuova 12.1 che installai qualche mese fa per i primi test sulle novità della versione. Riporto sotto il test completo che ho eseguito rispettivamente su un Oracle 10.2.0.5, un 11.2.0.3 e un 12.1.0.1, prima però anticipo alcune conclusioni: con la 11.2.0.3 le cose sono migliorate rispetto alla 10.2, mentre fra la 11.2 e la 12.1 non noto differenze rilevanti, neanche confrontanto velocemente lo stesso capitolo della documentazione nelle tre versioni:

SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 22 15:57:31 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

PL/SQL procedure successfully completed.

Session altered.

CRISTIAN@geotutf8 > create table test_ci_search (id number,string1 varchar2(30),string2 varchar2(30) ,numero number,data date, datastamp timestamp);

Table created.

CRISTIAN@geotutf8 > create sequence stest_ci_search;

Sequence created.

CRISTIAN@geotutf8 > insert into test_ci_search
2  select stest_ci_search.nextval, dbms_random.string('A',30), dbms_random.string('P',30), dbms_random.random,sysdate, systimestamp
3  from dual connect by level <= 100000;

100000 rows created.

CRISTIAN@geotutf8 > insert into test_ci_search select stest_ci_search.nextval, upper(string1), upper(String2), numero, data,datastamp from test_ci_search;

100000 rows created.

CRISTIAN@geotutf8 > insert into test_ci_search select stest_ci_search.nextval, lower(string1), lower(String2), numero, data,datastamp from test_ci_search where id<=100000
;

100000 rows created.

CRISTIAN@geotutf8 > insert into test_ci_search values (stest_ci_search.nextval,'Cristian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@geotutf8 > insert into test_ci_search values (stest_ci_search.nextval,'CriStian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@geotutf8 > insert into test_ci_search values (stest_ci_search.nextval,'CRISTIAN','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@geotutf8 > insert into test_ci_search values (stest_ci_search.nextval,'cristian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@geotutf8 > commit;

Commit complete.

CRISTIAN@geotutf8 > select * from test_ci_search where string1 ='cristian';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300004 cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.832366 PM

CRISTIAN@geotutf8 > ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

CRISTIAN@geotutf8 > ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

CRISTIAN@geotutf8 > select * from test_ci_search where string1 ='cristian';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.012672 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.031840 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.051397 PM

300004 cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.832366 PM

CRISTIAN@geotutf8 > select * from test_ci_search where string1 like 'cris%';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.012672 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.031840 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.051397 PM

300004 cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.832366 PM

CRISTIAN@geotutf8 > create index test_ci_search_idx on test_ci_search (nlssort(string1,'nls_sort=binary_ci'));

Index created.

CRISTIAN@geotutf8 >  set autotrace on
CRISTIAN@geotutf8 > select * from test_ci_search where string1 like 'cris%';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.012672 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.031840 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.051397 PM

300004 cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.832366 PM

Execution Plan
----------------------------------------------------------
Plan hash value: 18365057

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |    46 |  3772 |   913   (1)| 00:00:11 |
|*  1 |  TABLE ACCESS FULL| TEST_CI_SEARCH |    46 |  3772 |   913   (1)| 00:00:11 |
------------------------------------------------------------------------------------

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

1 - filter("STRING1" LIKE 'cris%')

Note
-----
- dynamic sampling used for this statement

&nbsp;


C:\tmp>sqlplus cristian/cristian@//10.110.3.52/salespdb

SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 22 13:08:07 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

ERROR:
ORA-28002: the password will expire within 7 days

Last Successful login time: Wed Apr 09 2014 15:21:53 +02:00

Connected to:
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

PL/SQL procedure successfully completed.

Session altered.

CRISTIAN@//10.110.3.52/salespdb > create table test_ci_search (id number,string1 varchar2(30),string2 varchar2(30) ,numero number,data date, datastamp timestamp);

Table created.

CRISTIAN@//10.110.3.52/salespdb > create sequence stest_ci_search;

Sequence created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search
2  select stest_ci_search.nextval, dbms_random.string('A',30), dbms_random.string('P',30), dbms_random.random,sysdate, systimestamp
3  from dual connect by level <= 100000;

100000 rows created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search select stest_ci_search.nextval, upper(string1), upper(String2), numero, data,datastamp from test_ci_search;

100000 rows created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search select stest_ci_search.nextval, lower(string1), lower(String2), numero, data,datastamp from test_ci_search wh
ere id<=100000;

100000 rows created.

CRISTIAN@//10.110.3.52/salespdb >
CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search values (stest_ci_search.nextval,'Cristian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search values (stest_ci_search.nextval,'CriStian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search values (stest_ci_search.nextval,'CRISTIAN','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search values (stest_ci_search.nextval,'cristian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@//10.110.3.52/salespdb >
CRISTIAN@//10.110.3.52/salespdb > commit;

Commit complete.

CRISTIAN@//10.110.3.52/salespdb > select * from test_ci_search where string1 ='cristian';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300004 cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.484698 PM

CRISTIAN@//10.110.3.52/salespdb > ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

CRISTIAN@//10.110.3.52/salespdb > ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

CRISTIAN@//10.110.3.52/salespdb > select * from test_ci_search where string1 ='cristian';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.457087 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.466520 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.475596 PM

300004 cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.484698 PM

CRISTIAN@//10.110.3.52/salespdb > select * from test_ci_search where string1 like 'cris%';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.457087 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.466520 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.475596 PM

300004 cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.484698 PM

CRISTIAN@//10.110.3.52/salespdb > create index test_ci_search_idx on test_ci_search (nlssort(string1,'nls_sort=binary_ci'));

Index created.

CRISTIAN@//10.110.3.52/salespdb > set autotrace on
ERROR:
ORA-28002: the password will expire within 7 days

SP2-0619: Error while connecting
SP2-0611: Error enabling STATISTICS report
CRISTIAN@//10.110.3.52/salespdb > select * from test_ci_search where string1 like 'cris%';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.457087 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.466520 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.475596 PM

300004 cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.484698 PM

Execution Plan
----------------------------------------------------------
Plan hash value: 1977262958

----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Rows  | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |   780 |   159K|  1125   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_CI_SEARCH     |   780 |   159K|  1125   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | TEST_CI_SEARCH_IDX |  1404 |       |    11   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

2 - access(NLSSORT("STRING1",'nls_sort=''BINARY_CI''')>=HEXTORAW('6372697300') AND
NLSSORT("STRING1",'nls_sort=''BINARY_CI''')<HEXTORAW('6372697400'))

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

In sintesi, fino a Oracle 10.2.0.5 era possibile abilitare ricerche su stringhe in modalità case insensitive, il problema era però che dall’uso dell’indice linguistico erano escluso l’operatore LIKE, dalla versione 11.2 in poi invece anche con l’operatore LIKE viene usato l’indice. Ho riportato solo i test per 10.2.0.5 e 12.1.0.1, per 11.2.0.3 il risultato è uguale a quello di 12.1.0.1

Annunci

V$OPEN_CURSOR

martedì 10 maggio 2011 alle 10:41 | Pubblicato su Performance Tuning, Varie | 6 commenti
Tag:

Alcuni giorni abbiamo avuto un problema con l’errore oracle “ORA-01000: maximum open cursors exceeded” e quindi mi è stato chiesto di fare una prova e verificare lo statement legato al cursore che si ipotizzava una procedura non chiudesse.  Raramente ho avuto problemi con questo errore, quando mi è capitato si è riscontrato che effettivamente mancaca una chiamata al metodo java di chiusura dello statement, che corrisponde alla chiusura del cursore Oracle.

Mi è venuta in mente all’uopo la vista di sistema V$OPEN_CURSOR che secondo la documentazione della versione 10gR2 mostra i cursori che ogni sessione ha “correntemente” (currently) analizzato e aperto (opened and parsed). Per evitare che alcune mie traduzioni possano fuorviare dove ho dubbi riporto anche la dicitura originale del manuale. In realtà credo che tale definizione sia fuorviante, perché tale vista mostra i cursori attualmente in cache, senza dare informazione alcuna sul fatto che siano aperti o meno.

Faccio un piccolo passo indietro, cos’è un cursore? Anni fa mi sono scritto una sorta di guida oracle personalizzata e in questa guida mi ricordavo di aver annotato anche queste informazioni, prelevate dal manuale “Concepts” secondo il quale sono collegamenti (handle) a aree di memoria private della sessione, le “private SQL Area” che a seconda della configurazione si trovano nella Shared Pool (server condivisi o shared server) o nella PGA (server dedicati o dedicated server). Il numero massimo di cursori che una sessione può aprire è limitato dall’impostazione del parametro OPEN_CURSORS che ha come valore di default 50 secondo la documentazione ma che io sui miei database (10.2.0.3 e 10.2.0.4) trovo impostati a 300 in quanto pare sia uno dei valore così impostati dal Database Configuration Assistant che solitamente uso per creare i database.

Interrogando la vista V$OPEN_CURSOR per un determinato SID si ottengono normalmente diversi record e ciò mi ha lasciato decisamente spiazzato. Uno dei motivi pare essere l’impostazione del parametro SESSION_CACHED_CURSORS che secondo la documentazione  come default il valore ‘0’ ma che sui miei database ha come default il valore 20. Ciò significa che sulla V$OPEN_CURSOR non vengono visualizzati solo i cursori aperti per ogni sessione, ma anche quelli in cache. L’unica informazione certa che si può avere sui cursori aperti per una sessione, anche secondo la nota del supporto  oracle 743605.1 (occorre un accesso al supporto oracle per visualizzare il link) che conferma quanto ho scritto sopra, è il numero di cursori aperti (salvo bachi), ottenibile con la query:


select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current'
and a.sid=<SID>;

Anche in una discussione su Asktom mi sembra si affermasse ciò riportanto la stessa query.

Mi rimangono alcune perplessità, interrogando la V$OPEN_CURSOR per una singola sessione posso trovare un numero di record superiore al valore del parametro SESSION_CACHED_CURSOR, alcuni legati allo stesso SID ma a diverso SADDR, colonna i cui significato non è ben chiaro, anche se sospetto sia legata a chiamate ricorsive.

Statspack

martedì 3 maggio 2011 alle 03:10 | Pubblicato su Performance Tuning | 3 commenti
Tag: , ,

Da una veloce ricerca nel blog mi sembra di non aver mai dedicato un intero post a Statspack, uno degli strumenti di monitoraggio e diagnosi delle prestazioni di Oracle più utile e potente. Siccome io lo utilizzo ancora, se non altro perché non è soggetto a licenze particolari come lo sono i nuovi strumenti introdotti dalla versione 10g di Oracle, è il caso di parlarne un po’. Probabilmente non l’ho mai fatto perché ritenevo che su internet ci fossero gia sufficenti informazioni, compresi i manuali Oracle; siccome però Oracle spinge sui nuovi strumenti a pagamento nascondendo di fatto le informazioni e siccome oggi ho incontrato un problema di cui voglio tener traccia qui, oggi finalmente dedico un breve post a questo potente strumento.

Statspack è un package PL/SQL e un insieme di tabelle che servono per raccogliere statistiche sul sistem Oracle. Statspac richiede una tablespace con almeno 100 MB libero, deve essere installato sul database da monitorare da un utente con privilegi SYSDABA. Lo script per l’installazione è $ORACLE_HOME/rdbms/admin/spcreate.sql.  Lo script creerà un utente PERFSTAT.

Statspack permette di fare delle fotografie del sistema (SNAPSHOT) ovvero sia di raccogliere  le statistiche che Oracle normalmente mantiene in in dato istante temporale. Per generare uno snapshot delle statistiche bisogna eseguire come utente PERFSTAT la procedura STATSPACK.SNAP(). Ci possono essere vari livelli di statistiche da 1 a 10, da meno dettagliate a più dettagliate (il default è 5). I parametri di default si cambiano in modo permanente con la procedura STATSPACK.MODIFY_STATSPACK_PARAMETER().

Per generare un report che consiste nel riassunto delle statistiche raccolte tra due snapshot si può usare lo script $ORACLE_HOME/rdbms/admin/spreport.sql. Lo script $ORACLE_HOME/rdbms/admin/sppurge.sql si può usare per eliminare vecchi snapshot.

Per disintallare statspack si usa lo script $ORACLE_HOME/rdbms/admin/spdrop.sql, da lanciare sempre con un utenza con privilegi SYSDBA.

Oggi per la prima volta da quando lo uso ho trovato un problema su statspack, su una installazione 10.2.0.3. Il problema si manifestava con l’errore

ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated

al tentativo di creare uno snapshot.

Per fortuna qui ho subito trovato la descrizione e la soluzione al problema con il riferimento alla nota 382993.1 del supporto Oracle.

Approfitto anche per ricordare che Kerry Osborne tempo fa aveva parlato di statspack su 11g e di un paio di problemini che su questa versione sono sti introdotti.

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.

Il parametro CURSOR_SHARING

mercoledì 19 maggio 2010 alle 19:54 | Pubblicato su Diario, Performance Tuning | 12 commenti

Trovo finalmente lo spunto, la voglia e il tempo per un nuovo post su questo blog che non voglio assolutamente abbandonare, lo spunto viene da un’esperienza recente con quello che per me è il “famigerato” parametro CURSOR_SHARING.

Per capire un po’ a cosa serve e cosa fa questo parametro secondo me occorre partire dal libro “Expert Oracle Database Architecture” di Thomas Kyte, che pur avendo cinque anni  è un libro che tuttora è utilissimo e che a questo punto direi un libro che tutti coloro vogliono essere DBA Oracle devono possedere. Da pagina 42 a pagina 45 Tom Kyte spiega cosa fa questo parametro, in che casi può essere utile e quali sono gli effetti collaterali.

Questo parametro è stato introdotto con la versione 8.1.6 del database di Oracle, purtroppo la documentazione on-line per le versioni pre 9iR2 non sono in questo momento accessibili (la 8.1.7 mi manda al manuale della 8.1.6), in ogni caso dalla versione 9iR2, quella dalla quale in poi mi posso definire “esperto” il parametro prevede 3 valori possibili: EXACT, SIMILAR e FORCE. Il parametro fu introdotto per poter porre un rimedio a livello di database al problema posto da applicazioni che non facevano uso di Bind Variables e che quindi riscontravano problemi di prestazioni sul database dovuta sia al carico di lavoro per il parsing di ogni statement (non essendoci possibilità di riuso) che alla serializzazione che questa operazioni necessitano (viene spiegato velocemente anche qui). Il valore di default del parametro è EXACT, il che significa che Oracle non fa nulla,  se si imposta uno degli altri due valori (a livello di sessione o di sistema) Oracle prima del parsing tradizionale ne fa uno in cui sostituisce le costanti letterali con bind variables, in questo modo si può ovviare al problema di sovraccarico da parsing (penso soprattutto nella parte di generazione del piano di esecuzione) di statement.  Ciò ha una infinità di effetti collaterali, primo fra tutti come mostra Kyte il cambio del piano di esecuzione,  poi c’è il problema della larghezza delle colonne, che però mi pare banale. Penso che a causa dei primi problemi che accanto ai soli valori EXACT e FORCE Oracle abbia aggiunto successivamente il valore SIMILAR che probabilmente rende la fase di sostituzione dei letterali con bind variables più prudente.

Anche Antognini, a pagina 325 del suo libro, parla di questo parametro e ricorda che non ha una buona reputazione a causa di diversi bug che nella sua storia sono stati trovati e poi risolti. Anche lui come Kyte e il supporto Oracle in generale invita a testare attentamente le applicazioni quando si tocca questo parametro.

Non ho molta esperienza con questo parametro perché ho lavorato solo su database su cui opera la nostra applicazione che da sempre fa uso assiduo di bind variables, quindi a livello pratico non ho mai riscontrato necessità neppure di provare a impostare un valore diverso da EXACT, però alcune settimane fa ho avuto modo di vederne un paio di effetti su un database di produzione non in mia gestione ma su cui gira la nostra applicazione. L’effetto evidente erano chiaramente le prestazioni, in quanto per una query in particolare veniva utilizzato un piano di esecuzione talmente scarso da bloccare di fatto tutto il sistema.

Nel mio caso si trattava di una versione 10.1.0.3, l’unica installazione di produzione che abbiamo con questa versione, ce ne sono rimaste alcune con 9.2, la maggior parte sono con 10.2. Questo mi ha creato un attimo di difficoltà in quanto ai primi tentativi di studiare il piano di esecuzione attraverso l’uso del package DBMS_XPLAN ottenevo un errore ORA-22905:

user@dbprod > select * from table(dbms_xplan.display_cursor(‘fp7xpcwztyag9’));
select * from table(dbms_xplan.display_cursor(‘fp7xpcwztyag9’))
*
ERROR at line 1:
ORA-22905: cannot access rows from a non-nested table item

user@dbprod > select * from table(dbms_xplan.display_cursor(‘fp7xpcwztyag9’));

select * from table(dbms_xplan.display_cursor(‘fp7xpcwztyag9’))

*ERROR at line 1:ORA-22905: cannot access rows from a non-nested table item

Anche l’utilizzo di “set autotrace” da sql*plus non dava risultati coerenti. Per rendere un po’ l’idea riporto un caso di test:

1)  creazione tabella e indice per test


user@dbprod > create table test_table as select object_id,object_name,

2> object_type,created,timestamp,status from all_objects;

Table created.

user@dbprod > create unique index test_table_idx on test_table(object_id);

Index created.

2) test con CURSOR_SHARING=EXACT


user@dbprod > select * from test_table where object_id=25288;

OBJECT_ID OBJECT_NAME                    OBJECT_TYPE     CREATED

---------- ------------------------------ --------------- -------------------

TIMESTAMP                                                 STATUS

--------------------------------------------------------- ---------------------

25288 /de95eb5d_ConnectionAcceptor   SYNONYM         23-02-2010 18:15:06

2010-02-23:18:15:06                                       VALID

user@dbprod > select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------

SQL_ID  5z67dbw794bwq, child number 0

-------------------------------------

select * from test_table where object_id=25288

Plan hash value: 1014598258

----------------------------------------------------------------------------------------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TABLE     |     1 |    66 |     2   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | TEST_TABLE_IDX |     1 |       |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"=25288)

3) test con CURSOR_SHARING=FORCE


user@dbprod > alter session set cursor_sharing=force;

Session altered.

user@dbprod > select * from table(dbms_xplan.display_cursor('5z67dbw794bwq'));

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------

SQL_ID  5z67dbw794bwq, child number 0

-------------------------------------

select * from test_table where object_id=25288

Plan hash value: 1014598258

----------------------------------------------------------------------------------------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TABLE     |     1 |    66 |     2   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | TEST_TABLE_IDX |     1 |       |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"=25288)

19 rows selected.

user@dbprod > select /* criq3 */ * from test_table where object_id=25288;

OBJECT_ID OBJECT_NAME                    OBJECT_TYPE     CREATED

---------- ------------------------------ --------------- -------------------

TIMESTAMP                                                 STATUS

--------------------------------------------------------- ---------------------

25288 /de95eb5d_ConnectionAcceptor   SYNONYM         23-02-2010 18:15:06

2010-02-23:18:15:06                                       VALID

user@dbprod > select sql_id from v$sql where sql_text like 'select /* criq3 */ * from t%';

SQL_ID

---------------------------------------

fp7xpcwztyag9

user@dbprod > select * from table(dbms_xplan.display_cursor('fp7xpcwztyag9'));

select * from table(dbms_xplan.display_cursor('fp7xpcwztyag9'))

*

ERROR at line 1:

ORA-22905: cannot access rows from a non-nested table item

Solo dopo aver capito come far funzionare DBMS_XPLAN ho avuto l’illuminazione:


user@dbprod > select * from table(cast(dbms_xplan.display_cursor('fp7xpcwztyag9') AS SYS.DBMS_XPLAN_TYPE_TABLE));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------

SQL_ID  fp7xpcwztyag9, child number 0

-------------------------------------

select /* criq3 */ * from test_table where object_id=:"SYS_B_0"

Plan hash value: 1014598258

----------------------------------------------------------------------------------------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TABLE     |     1 |    66 |     2   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | TEST_TABLE_IDX |     1 |       |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"=:SYS_B_0)
<div></div>

Vedendo un passo analogo a  quel access(“OBJECT_ID”=:SYS_B_0) a quel punto ho verificato che il parametro in produzione risultava CURSOR_SHARING=FORCE, facendo un

ALTER SESSION SET CURSOR_SHARING=EXACT;

e rieseguendo la query incriminata si notava immediatamente la differenza di prestazioni.



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.

Automatic Segment Space Management (ASSM) – parte II

giovedì 13 agosto 2009 alle 13:19 | Pubblicato su Installation and Configuration, Performance Tuning | Lascia un commento

Il post su ASSM di ieri come molti altri miei post in questo blog è stato forse un po’ confuso, ma l’ho scritto di getto; io sono un po’ così, disordinato, cerco di migliorare ma spesso comincio a scrivere un post e mi accorgo che mi richiede più tempo e più energie di quante non ne abbia a disposizione. C’è un’altro aspetto, scrivendo un post approfondisco l’argomento per evitare di scrivere cose inesatte, ciò spesso mi porta ad espandere il contenuto del post. Oggi, anche in conseguenza ad un intervento di Mladen Gogala alla discussione su CDOS sul presunto bug di ASSM ho fatto nuove indagini.

Gogala descrive il comportamento di Oracle nel caso descritto come un possibile caso di “dirty read” in quanto un’altra sessione vede cose non committate, infatti la seconda sessione vede nel bitmap che i blocchi sono liberi prima che la prima sessione abbia committato. Durante l’esecuzione dello stesso test ho fatto vari dump del primo blocco della tabella TEST_ASSM, che contiene la bitmap di primo livello per i primi 16 blocchi, e quindi lo stato di occupazione di questi blocchi (i primi tre sono metadata come gia spiegato ieri). Quello che ho visto è che anche in fase di insert i bitmap vengono aggiornati ben prima del commit, presumo che nel momento in cui l’insert cerca di mettere un record in un blocco e in questo non c’è più spazio per l’inserimento Oracle aggiorni subito la bitmap. Anche durante il delete ho osservato lo stesso comportamento. Il caso interessante è quello di un rollback: in questo caso non ho visto aggiornate le bitmap, che indicavano sempre tutti i blocchi “75-100% free”:

0:Metadata   1:Metadata   2:Metadata   3:0-25% free
4:0-25% free   5:0-25% free   6:0-25% free   7:0-25% free
8:0-25% free   9:0-25% free   10:0-25% free   11:0-25% free
12:0-25% free   13:0-25% free   14:0-25% free   15:0-25% free

quando invece ovviamente erano pieni. Completato il rollback nel dump della bitmap dei primi 16 blocchi risultava ancora tutto lo spazio libero. A questo punto ho lanciato un insert da una terza sessione (dalla seconda facevo i dump) e tale insert con una certa mia sorpresa è durato circa due minuti e mezzo,  mostrando alla fine le seguenti statistiche (vedi nota 1):

Statistiche
——————————
231  recursive calls
152993  db block gets
52  consistent gets
57140  physical reads
4730468  redo size

Dopo meno di un minuto dal lancio dell’insert, quindi ben prima che terminasse ho fatto l’ennesimo dump dello stesso blocco contenente la bitmap dei primi secici blocchi e a questo punto risultava aggiornato indicando tutti i blocchi pieni:

0:Metadata   1:Metadata   2:Metadata   3:FULL
4:FULL   5:FULL   6:FULL   7:FULL
8:FULL   9:FULL   10:FULL   11:FULL
12:FULL   13:FULL   14:FULL   15:FULL

I successivi insert sono più veloci (infatti a questo punto le bitmap sono aggiornate).

In verità confrontanto i vari dump una differenza si nota nel blocco contenente la bitmap di secondo livello (il blocco di tipo: SECOND LEVEL BITMAP”). In questo blocco infatti vi è una sezione che elenca l’indirizzo di tutti i blocchi contenenti le bitmap di primo livello:

L1 Ranges :
——————————————————–
0x04485e89  Free: 2 Inst: 1
0x04485e91  Free: 2 Inst: 1
0x04485e99  Free: 2 Inst: 1
0x04485ea1  Free: 2 Inst: 1

Il primo campo è il dba (Data block address del blocco, Inst suppongo indichi l’istanza che ha modificato il blocco (il mio test non è in ambiente RAC ed ho visto sempre 1). Non so cosa significhi “Free”, ho visto solo i valori 1,2 e 5.

Prima ancora, sembre nella bitmap di secondo livello vi è un’altra sezione fatta così:

Dump of Second Level Bitmap Block
number: 385     nfree: 250     ffree: 128    pdba:     0x04485e8b
Inc #: 0 Objd: 126227

Number:385 sono i blocchi contenenti le bitmap di primo livello, così suddivisi:

  1. 8 per i primi 16 extent (da 8 blocchi l’uno, quindi un BMB ogni 16 blocchi)
  2. 124 per i successivi 63 extent (da 128 blocchi l’uno, quindi un BMB ogni 64 blocchi)
  3. 1 per il successivo extent (da 128 blocchi, quindi un BMB per 128 blocchi)
  4. 252 per gli ultimi 63 extent (da 1024 blocchi l’uno, quindi un BMB ogni 256 blocchi)

Non l’ho detto ma la tablespace utilizza l’allocazione automatica degli extent.

pdba:     0x04485e8b è il dba del blocco “parent”, è il blocco di tipo “PAGETABLE SEGMENT HEADER”

nfree: 250     ffree: 128  sembra no due contatori che indicano in qualche modo il numero di bitmap con spazio libero, ma non so esattamente cosa significhino. il valore che riporto è quello del dump eseguito dopo il “clean-out” ovvero l’insert eseguito dalla terza sessione dopo il rollback, prima di tale clean out è  nfree: 385     ffree: 0

Nel dump effettuato durante l’insert c’è number: 165     nfree: 3       ffree: 161

Insomma, come si vede i meccanismi che ci sono dietro ogni operazione sono complessi. Non ho testato ancora il comportamento con una tablespace MSSM ovvero con gestione dello spazio libero per i segmenti basato su freelist, che forse in questo caso veramente molto particolare si comportano meglio (ma diventa importante configurare correttamente i parametri PCTUSED, FREELIST E FREELIST GROUPS. Thomas Kyte nel suo libro dedica alcune pagine del suo ultimo libro a questo argomento.

Nota 1:

L’insert eseguito da una seconda sessione senza fare ne commit ne rollback dalla prima sessione che ha fatto il delete mostra queste statistiche:

Statistiche
——————————
920  recursive calls
83346  db block gets
69437  consistent gets
69292  physical reads
888  redo size

L’ho eseguito due volte e le statistiche sono simili. La tabella ha in tutto 72704 blocchi

P.S.

Ho scoperto che WordPress.com mi lascia caricare doc e jpeg ma non file txt, quindi ho caricato qui un file txt con un tracciato dei miei esperimenti.

Automatic Segment Space Management (ASSM)

mercoledì 12 agosto 2009 alle 12:57 | Pubblicato su Performance Tuning | 1 commento
Tag:

Dalla versione 8i (8.1.5, ricostruendo la storia delle versioni dalla documentazione online) Oracle ha introdotto le tablespace “Locally Managed” (LMT) .

Apro una piccola parentesi: per verificare con esattezza la versione  ho controllato la documentazione della versione precedente, la 8.0.5 denominata 8 senza la “i”, nel manuale “SQL Reference” della sezione “CREATE TABLESPACE” non compare la parte “…EXTENT MANAGEMENT”. Ho cercato fra i manuali un documento con l’elenco delle nuove caratteristiche, ma solo con la versione 9i (9.0.1) è stato introdotto un manuale chiamato “New Features” che però è alquanto superficiale. Chiusa la parentesi.

Le “Locally Managed Tablespaces” si distinguono dalla tradizionali “Dictionary Managed Tablespaces” (DMT) in quanto mentre nella DMT le informazioni sui blocchi allocati per gli extent veniva registrato nel dizionario dati, quindi in tabelle della tablespace SYSTEM possedute dall’utente SYS, nelle LMT queste informazioni vengono gestite mediante blocchi riservati all’inizio di ogni datafile compenente una tablespace.  La tecnica utilizzate è definita a mappe di bit (bitmap) ovvero di definisce una mappa di tanti bit quanti sono i blocchi della tablespace e questi bit vengono settati quando i blocchi vengono allocati per un’extent. In questo modo si evitano conflitti nell’accesso a queste informazioni sul dizionario dati e si rendono le operazioni più veloci (compresa la cosiddetta deframmentazione, ovvero l’unione di blocchi liberi adiacenti in questo caso). Da alcuni appunti che scrissi tempo fa mi risulta che prima della versione 9iR2 (9.2.0.1) la tablespace SYSTEM non p0teva essere creata come “Locally Managed”, a partire dalla versione 9.2.0.1 invece è possibile creare anch’essa come LMT, in questo caso non si possono più creare tablespace “Dictionary Managed”, mentre è possibile convertire tablespace Dictionary Managed in Locally Managed.

Questa premessa sulla LMT serve per introdurre la successiva novità:  le tablespace con la gestione automatica dello spazio per i segmenti, ovvero l’Automatic Segment Space Management (ASSM), da cui il titolo del post introdotte con la versione 9i (9.0.1)  come si può dedurre non facilmente dal primo manuale “new features” di Oracle ma meglio ancora (secondo me) confrontando la sezione “CREATE TABLESPACE” del manuale “SQL Reference” della versione 9.0.1 (dove compare la parte “segment_management_clause” e della versione 8.1.7 (ultima release della 8i).

Anche in questo caso la novità è l’introduzione di una bitmap. Nella gestione tradizionale, pre-9i, definita”manuale” , dello spazio dei segmenti (ovvero la tracciatura dello spazio libero nei blocchi per inserire nuovi record) si utilizzano “freelist” ovvero liste di blocchi liberi che soprattutto in caso di elevata concorrenza (tipica degli ambienti OLTP) che possono dare problemi e richiedono un po’ di configurazione (sostanzialmente occorre definire più freelist). La nuova gestione utilizzabile utilizza sembre delle bitmap, a cui sono dedicati dei blocchi all’inizio degli extent. In questo caso la tracciatura è forse anche più precisa della gestione con freelist, in quanto è in grado di dare la percentuale di spazio libero di ogni blocco (a scaglioni, 0-25%, 25-50%, 50-75% e 75-100%);  anche in questo caso si dovrebbero ridurre i conflitti in caso di elevata concorrenza, ma soprattutto si semplifica ulteriormente la gestione eliminando di fatto altri tre possibili parametri specificabili alla creazine di una tabella: PCTUSED, FREELIST e FREELIST GROUPS, come annotato in questo manuale.

Il motivo per cui ho pensato di scrivere questo post è un thread di discussione aperto recentemente su CDOS intitolato:  “Do you use ASSM?“. L’OP riporta dopo qualche post un’interessante caso di test in cui si evidenzia un caso in cui ASSM ha un difetto.  La situazione è questa: si inseriscono in una tabella un sacco di record (piuttosto grandi), si cancellano tutti i record senza committare e da un’altra sessione si inseriscono nuovi record sulla stessa tabella. In questa situazione si osservano prestazioni scarse ovvero la seconda sessione ad ogni insert deve aspettare un tempo notevole.  Incuriosito ho provato a tracciare la sessione per capire il motivo di tale lentezza nell’eseguire singoli insert (lentezza che dalla sessione che ha fatto il delete e non il commit non si osserva).  L’unica cosa che ho capito guardando il trace è che per fare l’insert Oracle si passa quasi tutti i blocchi, singolarmente, quindi si osservano numerosi “db file sequential read”. A fare luce su questo caso è intervenuto Jonathan Lewis, il quale ha spiegato che in pratica la prima sessione aggiorna subito i bitmap del segmento (ho fatto una prova facendo un dump del blocco e pare proprio così),  in questo modo la seconda sessione vede subito che tutti i blocchi hanno spazio libero per l’inserimento (cominciando dal primo extent) e va sul blocco dove però trova la transazione non committata, quindi deve passare al blocco sucessivo e così via, passandosi quindi tutti i blocchi alla disperata ricerca di un blocco libero.  Jonathan Lewis spiega che in passato Oracle aspettava ad aggiornare la bitmap ma questo aveva portato ad un baco per cui poteva accadere che non risultasse spazio libero quando invece c’era. Ho fatto una ricerca su metalink (classic, speriamo rimanga) ed ho trovato la nota  numero 2917432.8 che descrive questo problema (molto sommariamente).

Non ho capito bene il perché la prima sessione non soffra dello stesso problema, la mia ipotesi è che in qualche modo Oracle faccia questo ragionamento,  prendo per buono il primo blocco, tanto la stessa transazione che richiede lo spazio per il nuovo inserimento è la stessa che ha fatto il delete liberandolo, quindi se committa lo spazio c’è se non committa lo spazio non c’è ma neppure serve perchè non viene più fatto l’insert.

il dubbio che avevo prima di pensarci bene era il perché oracle si preoccupasse di trovare un blocco con dello spazio per fare l’insert, ma in effetti Oracle deve preparare in buffer cache un blocco con il nuovo record, in questo modo la stessa sessione poi puo’ interrogare la tabella (senza aver ancora fatto il commit) e vedere il nuovo record inserito; è un meccanismo un po’ complesso, ma indispensabile a garantire il modello READ COMMITTED.

Per capire meglio il tutto ho fatto un po’ di dump dei blocchi di un segmento creato in una tablespace con ASSM, su un 10.2.0.4. La struttura che ho visto è così:  i primi tre blocchi sono usati per tre tipologie di “metadati”, FIRS LEVEL BITMAP, SECOND LEVEL BITMAP, PAGETABLE SEGMENT HEADER. Per ogni segmento (io ho provato solo con HEAP TABLES) c’è un PAGETABLE SEGMENT HEADER, solitamente il terzo blocco, un SECOND LEVEL BITMAP, solitamente il secondo, n FIRST LEVEL BITMAP in dipendenza dal numero di blocchi (extent). Il blocco di tipo PAGETABLE SEGMENT HEADER contiene fra l’altro gli indirizzi (dba , data block address) degli altri Bitmap block e poi la mappa degli extent che appartengono al segmento. Il blocco di tipo SECOND LEVEL BITMAP contiene gli indirizzi dei bitmap block di livello 1. I blocchi di tipo FIRST LEVEL BITMAP contengono in sostanza i bitmap veri e propri, quindi il livello di occupazione per ogni blocco (approfondirò quanti blocchi può “gestire” un singolo blocco di bitmap”).

Si potrebbe approfondire meglio la descrizione della struttura dei blocchi di bitmap per la gestione ASSM, ma va oltre lo scopo di questo post che è quello di porre l’attenzione su un comportamento particolare si ASSM, a sua volta posto in evidenza sul newsgroup CDOS.

Bind Variables e V$SQL_BIND_CAPTURE

mercoledì 1 luglio 2009 alle 01:10 | Pubblicato su Performance Tuning, Varie | 1 commento
Tag: , , , ,

Sono rientrato lunedì da due settimane di “vacanze” ma ancora non posso dire di essere in forma, chissà perché ma 7/8 ore di sonno al giorno per me non sono sufficenti 😦

Ultimamente forse anche a causa della stanchezza, non ho molti spunti per scrivere nuovi post, ieri però ne ho trovato uno grazie ad un thread di discussione su oracleportal. Siccome avevo analizzato la vista V$SQL_BIND_CAPTURE tempo fa (ne avevo anche parlato in un mio post di un anno e mezzo fa) e ieri non mi ricordavo bene come funzionava e che informazioni mostra,  approfitto per un post riassuntivo.

La vista V$SQL_BIND_CAPTURE è stata introdotta con la versione 10g di Oracle e serve a visualizzare i valori delle bind variables utilizzati nell’esecuzione delle query sul database, ma con certe limitazioni, spiegate sulla documentazione. La prima è che vengono “catturare” solo le bind variables presenti nelle clausole WHERE o HAVING degli statement SQL, quindi quelle negli INSERT e negli UPDATE non sono mai catturate e visualizzate da nessuna parte. La seconda limitazione, sempre documentata, ma in modo più nascosto, è che le bind variables per uno stesso cursore vengono catturate al massimo ogni 15 minuti, per non sovraccaricare il sistema; questo è spiegato nella descrizione del campo LAST_CAPTURED.  Più precisamente il popolamento di questa vista è controllato da due parametri “nascosti” e non documentati:

  • _CURSOR_BIND_CAPTURE_INTERVAL che definisce in secondi l’intervallo minimo fra due “campionamenti” delle bind variables per due cursori; il suo valore di default è 900, pari proprio a 15 minuti
  • _CURSOR_BIND_CAPTURE_AREA_SIZE che definisce la dimensione massima dell’area di memoria occupata dalla vista, e quindi il numero massimo di record.

Una discussione su V$SQL_BIND_CAPTURE e la “cugina” V$SQL_BIND_METADATA si può trovare in un post del blog di Miladin Modrakovic che approfitto per segnalare.

Al riguardo scrisse un post anche Jonathan Lewis il quale rivelava di aver confuso il contenuto di quella vista con i valori utilizzati dall’ottimizzatore per la generazione dei piani (bind peeking). Risulta invece che il contenuto di V$SQL_BIND_CAPTURE è la versione formatatta ed estesa del contenuto del campo BIND_DATA della vista V$SQL, il quale ho verificato con due prove cambia insieme al contenuto di V$SQL_BIND_CAPTURE (non ho fatto un’analisi precisa ma vado in fiducia).

Per completezza dell’informazione, come precisato benissimo da Dion Cho in un commento dello stesso post di Lewis, le peeked binds vengono salvate da oracle nel campo v$sql_plan.other_xml, solitamente nella prima riga del piano di esecuzione.

La vista V$SQL_BIND_CAPTURE è una vista “istantanea”, nel senso che ad ogni suo aggiornamento il contenuto precedente va perso, però AWR ,per default ogni ora, salva una foto della vista nella tabella DBA_HIST_SQLBIND e quindi chi ha acquistato il Diagnostic Pack può interrogare questa tabella per fare qualche analisi, non indagini precise a causa appunto delle limitazioni del campionamento ogni 15 minuti effettuato per la V$SQL_BIND_CAPTURE E e di ogni ora per la DBA_HIST_SQLBIND.

Moltiplicazione dei cursori con JDBC

lunedì 1 giugno 2009 alle 01:59 | Pubblicato su Performance Tuning, SQL | 3 commenti
Tag: , , ,

Oggi ho fatto un piccolo test con un programma java che inserisce dati in una tabella un po’ di record. Il test nasce da una segnalazione che ho avuto molto tempo fa. Il test mi ha dato occasione di approfondire un po’ l’argomento JDBC e secondo me entra nell’argomento “applicazione indipendenti dal database”. Infatti ho verificato come in certe condizioni, l’utilizzo di JDBC standard, senza le estensioni oracle,  si può avere una proliferazione di cursori nella Shared Pool di Oracle con inevitabili conseguenze sulle prestazioni.

Cominciamo dall’inizio: tempo fa mi giunse la segnalazione che in corrispondenza dell’esecuzione di una particolare procedura (java) si aveva un picco di consumo della CPU da parte di Oracle, al punto da saturare questa risorsa sulla macchina e rallentare tutte le altre attività. Mi venne subito segnalato che vi era una proliferazione di cursori per uno statement. La cosa pareva strana, in quanto l’applicazione fa un uso abbastanza rigororoso di “bind variables”, ma un esame della vista V$SQL_SHARED_CURSOR evidenziava diversi “BIND MISMATCH”. Effettivamente, nonostante l’uso delle bind variables, vi erano delle situzioni che oggi ho finalmente approfondito, la frequente assenza di valori per alcune colonne, mai le stesse per i diversi insert. La tabella in oggetto avveva un numero elevato di campi ed ogni insert aveva dei null fra i valuri da inserire, non sempre nelle stesse colonne.

Illustro meglio cosa intendo dire con un esempio, ho una tabella T così fatta:


SVILUPPO40@perseo10 > desc t
Nome   Nullo?   Tipo
------ -------- ------------
A               NUMBER
B               NUMBER
C               TIMESTAMP(6)

e un programmino java di cui riporto un pezzo:


PreparedStatement stmt = _connection1.prepareStatement("INSERT /* - 1 */ INTO T (A, B, C) VALUES (?,?,?)");
stmt.setLong(1,1);
stmt.setLong(2,11);
stmt.setObject(3,ts);
int retval = stmt.executeUpdate();
stmt.setLong(1,1);
stmt.setLong(2,11);
stmt.setObject(3,null);
retval = stmt.executeUpdate();
stmt.close();

Inserisco due record, nel primo nella terza colonna passo un oggetto ts (è un timestamp java), nel secondo passo null. Ebbene, interrrogando la V$SQL il risultato è :


SQL_ID        CHILD_NUMBER EXECUTIONS SQLTXT
------------- ------------ ---------- ------------------------------
3823umzxhg563            0          1 INSERT /* - 1 */ INTO T (A, B,
3823umzxhg563            1          1 INSERT /* - 1 */ INTO T (A, B,

Utilizzando uno dei comodissimi script messi a disposizione da Dion Cho:


SYSTEM@perseo10 > @shared_cursor
SYSTEM@perseo10 > set echo off
Immettere un valore per 1: INSERT /* - 1 */ INTO T%
vecchio  14:           and q.sql_text like ''&1''',
nuovo  14:           and q.sql_text like ''INSERT /* - 1 */ INTO T%''',
SQL_TEXT                       = INSERT /* - 1 */ INTO T (A, B, C) VALUES (:1,:2,:3)
SQL_ID                         = 3823umzxhg563
ADDRESS                        = 2A54B848
CHILD_ADDRESS                  = 24957B88
CHILD_NUMBER                   = 0
--------------------------------------------------
SQL_TEXT                       = INSERT /* - 1 */ INTO T (A, B, C) VALUES (:1,:2,:3)
SQL_ID                         = 3823umzxhg563
ADDRESS                        = 2A54B848
CHILD_ADDRESS                  = 24977EB8
CHILD_NUMBER                   = 1
BIND_MISMATCH                  = Y
--------------------------------------------------

E infine approfondendo con la vista V$SQ_BIND_METADATA:


SYSTEM@perseo10 > select * from v$sql_bind_metadata where  address='24957B88';

ADDRESS    POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME
-------- ---------- ---------- ---------- ---------- -------------------------
24957B88          3        180         11          0 3
24957B88          2          2         22          0 2
24957B88          1          2         22          0 1

SYSTEM@perseo10 > select * from v$sql_bind_metadata where  address='24977EB8';

ADDRESS    POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME
-------- ---------- ---------- ---------- ---------- -------------------------
24977EB8          3          1         32          0 3
24977EB8          2          2         22          0 2
24977EB8          1          2         22          0 1

Mi pare interessante notare che quando da java viene fatto setObject(3,null), Oracle prende il tipo della bind variable 1 che da documentazione corrisponde a varchar2.

Dando un’occhiata alla documentazione Java ho notato l’esistenza di un metodo setNull dell’interfaccia PreparedStatement e l’ho provato:


/* caso tre */ 
 stmt = _connection1.prepareStatement("INSERT /* - 3 */ INTO T (A, B, C) VALUES (?,?,?)");
 stmt.setLong(1,1);
 stmt.setLong(2,11);
 stmt.setObject(3,ts);
 retval = stmt.executeUpdate();
 stmt.setLong(1,1);
 stmt.setNull(2,java.sql.Types.DECIMAL);
 stmt.setObject(3,ts);
 retval = stmt.executeUpdate();
 stmt.close();

in questo caso non c’è bind mismatch, infatti:


SQL_ID        CHILD_NUMBER EXECUTIONS SQLTXT
------------- ------------ ---------- ------------------------------
0zsbqbdnk1ssx            0          2 INSERT /* - 3 */ INTO T (A, B,

il cursore viene riutilizzato (le esecuzioni sono due e non ci sono altri child).

Per gestire tutti i propri tipi dato Oracle ha introdotto delle estensioni nei driver JDBC, ad esempio ha esteso l’interfaccia PreparedStatement con l’interfaccia OraclePreparedStatement, nella documentazione viene proprio spiegato questo e come utilizzarlo con un esempio.

Si vede quindi che anche la programmazione con JDBC richiede particolare attenzione per evitare situazioni di “incompatibilità di tipo dato” (datatype mismatch) che possono in ultimo incidere negativamente sulle prestazioni del database server Oracle

Pagina successiva »

Crea un sito o un blog gratuitamente presso WordPress.com.
Entries e commenti feeds.