Performance Tuning con CBO e DBMS_SHARED_POOL

giovedì 18 dicembre 2008 alle 18:13 | Pubblicato su 11g, Performance Tuning | 7 commenti
Tag: , , , ,

In questo blog  ho scritto più volte sull’argomento performance tuning con particolare riguardo ai problemi di “instabilità dei piani di esecuzione” che l’accoppiata CBO e bind variables. Da diverso tempo non mi capitano casi di query con piani esecuzione sballate (direi che è una cosa positiva), però alcuni mesi fa ho avuto un incontro presso un cliente con il suo, assieme ad un consulente Oracle per formare il DBA su come affrontare casi di query con piani di esecuzione sballati che bloccano il sistema. Infatti in precedenza c’era stato un caso simile e sia da parte mia che da parte dello stesso consulente Oracle sentito al telefono erano state suggerite due strade: il ricalcolo delle statistiche su una delle tabelle interessate o il flush della shared pool, con in rischio però che quest’ultima soluzione non fosse certa, perchè se durante il flush il piano è utilizzato, esso è “pinned” cioè bloccato e non viene espluso dalla cache. Stiamo parlando di azioni d’emergenza per sbloccare un sistema con il quale lavorano centinaia di operatori.

Un punto emerso in quell’occasione, come in altre, era il fatto che non c’era un metodo per cacciare dalla cache sono un preciso piano di esecuzione. Bene, oggi, grazie a questo post di Doug Burns, ho scoperto non solo che questo è possibile in 11g, ma anche, leggendo il post citato di Fairlie Rego, che con una patch applicabile dalla versione 10.2.0.2 è possibile anche su 10gR2.

DBMS_SHARED_POOL.PURGE

In Oracle 11g è stato aggiunto un nuovo met0do PURGE al vecchio package DBMS_SHARED_POOL, che permette di espellere singoli oggetti dalla shared pool come ad esempio singoli cursori (e quindi il relativo piano di esecuzione). Fairlie Rego fa un esempio. Non ho capito bene come si valorizza il parametro heaps ne quali sono i possibili valori (a parte il 65 usato nell’esempio). Da quel che si deduce dall’esempio in ogni caso, si elimina dalla cache tutto il cursore e suppongo anche tutti i figli (child)  e i loro piani di esecuzione. Esiste una nota Metalink, la 457309.1, che spiega abbastanza bene uso e disponibilità di questa caratteristica.

Riguardo al disponibilità ho notato una cosa bizzarra, nella nota metalink 457309.1 spiega che questo nuova procedura è disponibile come patch per le versioni 10.2.0.2 e 10.2.0.3 ed è inclusa nella patch set 10.2.0.4 (oltre alla versione 11g). A questo proposito ho notato una cosa bizzarra, controllando su una nostra installazione 10.2.0.4 Enterprise Edition non ho trovato traccia del package DBMS_SHARED_POOL, su una analoga installazione, ma Standard Edition c’è il package con tanto di procedura PURGE. Su una istanza 10.2.04 Entrerprise Edition che uso per test miei il package c’è , con la procedura PURGE. Infine sull’istanza di test 11.1.0.7, Enterprise Edition, il package c’è.

Nella documentazione del package, relativa alla versione 11g,  ho notato che si specifica che il package non viene creato sulla versione standard edition (come mai?). Ho provato a dare un’occhiata allo script di creazione dal quale si ho solo notato come la modifica relativa alla nuova procedura risalga nientemeno che ad agosto 2006.

Sulla documentazione della 10gR2 invece si dice solo che la creazione del package non è in clusa nello script catproc.sql. In effetti non ho trovato dove tale creazione sia invocata, ma su tutti i miei 10g con versione <10.2.0.4 il package è installato.

Sull’istanza 10.2.0.4 Enterprise Edition dove il package non è installato pare vi sia stato un backporting al contrario della 11g, nel senso che l’applicazione del patchset ha rimosso dalle procedure di creazione del database l’installazione del package in caso di Enterprise Edition. Infatti su quella macchina ho installato la patchset prima di creare il database. Su una istanza simile su cui ho seguito lo stesso procedimento ma ho installato Standard Edition il package c’è.

Sulla istanza di test con 10.2.0.4 Enterprise Edition la patchset 10.2.0.4 l’ho installata dopo la creazione del database (creazione avvenuta con la versione 10.2.0.3), quindi il package era già installato ed è stato “patchato” aggiungendo la nuova procedura”.

Conclusione

A parte quindi le variabili che condizionano l’installazione di default del package (mi chiedo se la sua installazione sia rischiosa), la sostanza è che Oracle ha aggiunto uno strumento che in particolari situazioni può essere utile (se funziona veramente :)).

A pensarci bene, nelle situazioni che ho visto io probabilmente ci sarebbe comunque dovuto essere uno stop di tutti gli operatore per impedire l’invocazione della query con conseguente pinning e impossibilità di rimuovere il piano di esecuzione della query.

P.S.

Terminata la scrittura di questo post ho continuato la lettura dei post indicati da Doug Burns, in particolare questo. Viene segnalato che su 10.2.0.4  la procedura DBMS_SHARED_POOL.PURGE non funziona e si rimanda alla nota metalink Note:751876.1 la quale in conclusione spiega che la procedura è “event protected”  quindi va abilitata esplicitamente tramite parametro nel init.ora, settando l’evento 5614566 (che è anche il numero della patch).  Questo rende un po’ la vita difficile

7 commenti »

RSS feed for comments on this post. TrackBack URI

  1. […] due cose nuove. Dopo la nuova procedura purge del package dbms_shared_pool di cui ho parlato nel post precedente adesso è il momento di un nuova cosa che ho scoperto grazie a questo thread di CDOS. Si tratta di […]

  2. Interessante. Effettivamente mancava lo svecchiamento di un singolo cursore dallo shared pool.

  3. in realtà c’era già qualcosa per forzare l’invailidazione di un oggetto:

    dbms_utility.invalidate(
    p_object_id IN NUMBER,
    p_plsql_object_settings IN VARCHAR2 DEFAULT NULL,
    p_option_flags IN PLS_INTEGER DEFAULT 0);

    Alessandro

  4. Ciao Alessandro,
    in effetti non ricordavo questa procedura, però ho fatto un test e non si riesce ad invalidare un cursore. Essa sembra destinata a invalidare solo oggetti PL/SQL, ho provato ha invalidare una tabella coinvolta in una query, metodo che comunque sarebbe meno granulare, perché porterebbe a invalidare tutti i cursori dipendenti da quella tabella, però non funziona (su 10.2.0.4 standard Edition). Viceversa ho fatto un test con dbms_shared_pool.purge e la cosa che ho trovato interessante è che dopo l’esecuzione della procedura, la query sulla v$sql non restituiva più record; rieseguendo la query e reinterrogando la V$SQL:
    INVALIDATIONS FIRST_LOAD_TIME LOADS EXECUTIONS
    ————- ——————- —– ———-
    0 2009-01-02/09:38:57 3 1

    Il first_load_time è lo stesso della prima esecuzione, quindi qualche informazione da qualche parte rimane (il parametro loads indica il numero di volte che il cursorsore è stato ricaricato).

  5. mi ricordo di un thread su dba-village dove affrontammo la discussione relativamente a quel parametro:

    http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=27307

    in effetti si voleva però agire su di un package body

    Alessandro

  6. ok 🙂 , dalla documentazione Oracle:

    “p_object_id must be a PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, TYPE, TYPE BODY, LIBRARY, VIEW, OPERATOR, SYNONYM, or JAVA CLASS.”

  7. per invalidare un singolo cursore in cache ho utilizzato o lo stratagemma della vista da ricompilare, oppure per invalidare + cursori inerenti una tabella aggiungendo un commento sulla tabella in questione, certo che avere un package core Oracle che fa questo per me però puo essere utile


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: