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.



About these ads

12 commenti »

RSS feed dei commenti a questo articolo. TrackBack URI

  1. Cristian, visto come hai risolto l’errore in dbms_xplan, mi è venuto il dubbio: non è che non trovava l’oggetto SYS.DBMS_XPLAN_TYPE_TABLE perché mancava il sinonimo?

    Non ho capito comunque cosa volevi dimostrare con l’esempio che hai proposto. Se c’è un indice univoco, Oracle, con o senza bind variable, capisce comunque che è conveniente passare per l’indice, e non propone in questo caso un piano di esecuzione sub-ottimale se shared_cursor=FORCE. Forse volevi solo evidenziare che hai capito che cursor_sharing era a FORCE perché ti sei trovato con stringhe del tipo :SYS_B_0?
    Ho sempre sentito dire che cursor_sharing=FORCE, nei casi in cui l’applicazione non fa uso di bind variables, porta complessivamente a dei miglioramenti. Se c’è qualche query che con la sostituzione automatica dei litterals funziona male si può (se si può) utilizzare l’hint cursor_sharing_exact che la blocca.

  2. sul dbms_xplan il problema è dato sempre dal settaggio del parametro cursor_sharing e solo per cursori il cui parsing sia stato fatto con il valore FORCE, infatti dal mio esempio si vede che funziona la prima volta con il parametro al valore EXACT, funziona una seconda volta dopo aver settato a livello di sessione il parametro a FORCE ma per lo stesso cursor, ma da errore per il cursore eseguito nella sessione dopo il cambiamento del parametro; non ho approfondito oltre la causa.
    Per quanto riguarda il mio esempio infatti non mostra differenze sul piano di esecuzione, ma come hai detto mostra la comparsa della variabile SYS_B_0.
    Nel caso di applicazioni cablate, in cui non è possibile cambiare gli statement introducendo le bind variables l’impostazione cursor_sharing=FORCE probabilmente è d’obbligo, ma se posso introdurre gli hint come cursor_sharing_exact forse posso mettere le bind variables e in molti casi è certamente meglio (poi naturalmente le casistiche sono infinite).

  3. Aaaadesso ho capito il tuo esempio… sei come sempre parco di espliciti passaggi logici ;-)
    Certo, se si può mettere un hint tanto vale inserire le bind variables. Però mettere un hint è più veloce: la gestione del binding (dichiarazione variabili e assegnazione valori alle variabili) la fa Oracle.

  4. hai ragione, sono già parco di mio (me lo dice anche mia moglie :) ) e in più ho scritto il post un po’ velocemente.
    Hai ragione anche sul discorso hint, aggiungere le bind variables è senz’altro più complesso, comunque spero di non trovarmi mai in una situazione simile

  5. se è possibile inserire le binds è senz’altro meglio e non ci piove, punto.

    Non è però detto che poter inserire un hint significhi anche poter inserire ed utilizzare le binds, un hint lo puoi inserire ad esempio intercettanto l’istruzione e manipolandola con le stored outline…

    Personalmente poi sarei per principio contrario sia alle stored outline che agli hints, ma quando non hai alternative spendibili subito si fa quel che si puo e soprattutto in certi ambienti lavorativi lo devi fare anche mooooolto velocemente :-)

    Ciao
    Alessandro

    • Alessandro,
      lo scenario dell’uso dell’hint cursor_sharing_exact è il seguente:
      1. applicazione scritta senza bind variables
      2. uso del parametro cursor_sharing=FORCE per limitare i danni dell’hard parsing
      3. uso dell’hint cursor_sharing_exact per quelle poche query in cui la sostituzione automatica dei literals con bind variables (cursor_sharing=FORCE) produce più danni che vantaggi (perché come si sa con i literals il CBO lavora con maggiori informazioni e quindi meglio).

      Portare tutto in bind variables, tralasciando quelle query che funzionano bene con i literals, vuol dire riscrivere completamente l’applicazione.
      Magari l’uso di queste due scorciatoie (cursor_sharing=FORCE e hint cursor_sharing_exact) porta ad una soluzione accettabile, perché deprecarla a priori?

      Riguardo: “un hint lo puoi inserire ad esempio intercettanto l’istruzione e manipolandola con le stored outline”, ovviamente in senso stretto non è inserire un hint, ma è sfruttare una stored outline.

      Sarebbe interessante l’hacking degli SQL Profiles di Oracle10g come fa Kerry Osborne nel suo blog (http://kerryosborne.oracle-guy.com/category/oracle/plan-stability/) e che afferma essere superiore alle stored outlines, ma sinceramente non sono mai riuscito a leggere approfonditamente quel popò di roba che ha scritto sull’argomento. Ogni volta che capita un problema di plan stability mi dico: “ecco questa volta mi leggo tutto e faccio come dice Kerry Osborne” ma poi la fretta mi obbliga verso strade più immediate come le stored outlines.

      • Inserire un hint è senz’altro piu semplice, e chi lo nega e dove scusa?

        Se posso intervenire in un’applicazione secondo te che faccio le uso o no le bind variables?

        Se posso intervenire in un’applicazione faccio uso o meno degli hints?

        Se posso intervenire in un’applicazione faccio uso o meno di stored outline (ad esempio) e/o profiles?

        Questo è appunto però un discorso a priori;
        se invece non posso intervenire cerco di arraggiarmi come posso e con gli strumenti a disposizione, non ho deprecato niente quindi

  6. Alessandro,
    la mia risposta a Cristian era contestuale alla situazione di applicazione scritta senza bind variables, il post infatti verte sul parametro cursor_sharing impostato a FORCE. Ho suggerito che per alcune selezionate queries con problemi, se possibile, può essere conveniente inserire l’hint cursor_sharing_exact.
    Se tu mi scrivi:
    “se è possibile inserire le binds è senz’altro meglio e non ci piove, PUNTO.” e “sarei PER PRINCIPIO contrario sia alle stored outline che agli hints”
    io capisco che per te il mio suggerimento per la particolare situazione (su cui tra l’altro Cristian ha convenuto) è comunque sbagliato: per te bisogna usare le bind variables, PUNTO. Mi sembra proprio un deprecare a priori.
    (per copiare la frasetta che dicono spesso i gurus Oracle: se non ci fosse stata mai convenienza ad impiegate l’hint cursor_sharing_exact Oracle non l’avrebbe fornito).

    Alessandro, la pensiamo allo stesso modo, non c’è nulla da dibattere, sono io che faccio troppo il pignolo sulle parole.
    Ciao

    • “se” già implica una ipotesi, una premessa posta a base di un ragionamento

      “se è possibile” per me significa che, nella condizione in cui si verifichi la premessa posta a base del ragionamento, sia preferibile una scelta rispetto ad un’altra…
      Al contrario, nella condizione cioè in cui non si verifichi, non avendo alternative dovrò fare altre scelte

      tornando quindi al discorso, il tuo suggerimento per me è quindi sbagliato se ho l’opportunità di scegliere di utilizzare le bind variables ed invece lo trovo giusto se mi è preclusa l’opportunità di seguire questa strada per diversi motivi

      idem come sopra per quanto riguarda il discorso di hints e stored outlines/profiles

      • Ho un applicativo scritto senza bind variables, 30 utenti concorrenti, ho dei tempi medi per query di oltre 10 secondi a causa dei continui hard parses, che mi rubano CPU e mi creano waits per contese nella shared pool.
        Imposto cursor_sharing=FORCE, miracolo le queries vanno tutte sotto il secondo, tutto funziona a meraviglia. Non sono tra l’altro previsti, nemmeno tra 10 anni, crescite di utenze e/o dati. Ho finito! Posso dedicarmi a polemizzare con Alessandro su Dbaitalia… ah no, lui deve riscrivere tutto l’applicativo! ;-)

  7. una tale applicazione non avrebbe passato indenne, se affidata a me la decisione, le fase di test e/o di valutazione prima del go live in produzione ;-)

    • Non valgono gli argomenti retroattivi. Tu hai detto che, se possibile, scegli sempre le bind variables. E allora cosa ci fai qui? Io, che ho impostato cursor_sharing, ho già finito, ma a tu no, al lavoro! E ricordati di fare tutti i tests e benchmarks… altrimenti Tom s’incazza ;-)


Rispondi

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. | The Pool Theme.
Entries e commenti feeds.

Iscriviti

Ricevi al tuo indirizzo email tutti i nuovi post del sito.

Unisciti agli altri 71 follower

%d blogger cliccano Mi Piace per questo: