Oracle Resource Manager con CDB e PDB

venerdì 15 giugno 2018 alle 15:42 | Pubblicato su 12c, Diario, Installation and Configuration, Performance Tuning | Lascia un commento

Con l’introduzione dell’architettura “multitenant” in Oracle 12 si sono stati degli aggiornamenti e delle estensioni al funzionamento di Oracle Resource Manager che è lo strumento per ripartire in base a determinate esigenze le risorse disponibili su un server.  Confesso di non aver mai potuto testare e vedere in azione veramente il resource manager, credo di avere fatto solo delle piccole prove per gestire sessioni inattive e bloccanti ma non sono andato oltre. Oltre dieci anni fa (!) ho scritto un mio riassunto di cui ho parlato in questo post.  Dopo tanto tempo ho l’ambizione di fare un post riassuntivo delle novità del resource manager nell’ambito di Container database e Pluggable database.

Con l’architettura multitenant si è aggiunto un livello ulteriore su cui gestire le risorse in un database Oracle. Per cui in Oracle 12 su un database CDB il resource manager lavora a due livelli:

  • a livello CDB, in cui smista le risorse fra i PDB
  • a livello di PDB che come nella versione precedente smista le risorse fra gli utenti del singolo PDB.

Faccio riferimento a questa sezione del manuale Oracle.

CDB Resource Plans

un CDB resource plan alloca le risorse ai PDB in base al suo insieme di direttive (resource plan directives).

C’è una relazione padre-figlio tra un CDB resource plan e le sue direttive. Ogni direttiva fa riferimento a un PDB e non ci possono essere due direttive nel piano correntemente attivo che fanno riferimento allo stesso PDB.

Le direttive controllano l’allocazione delle seguenti risorse ai PDB:

  • CPU
  • Parallel execution server.

Le direttive di un CDB resource plan sono molto semplici, hanno tre parametri:

  • SHARE: rappresenta la fetta di CPU e di parallel server riservata al PDB
  • UTILIZATION_LIMIT: è un limite superiore alla percentuale di CPU utilizzabile dal PDB
  • PARALLEL_SERVER_LIMIT: è un limite superiore percentuale (sul valore impostato tramite il parametro PARALLEL_SERVERS_TARGET)

Esiste una direttiva di default per i PDB per cui non è stata definita una direttiva e assegna uno share e un limite del 100% sia per UTILIZATION_LIMIT e PARALLEL_SERVER_LIMIT.   Questa direttiva può essere modifica con la procedura dedicata del package DBMS_RESOURCE_MANAGER UPDATE_CDB_DEFAULT_DIRECTIVE.

C’è poi un’altra direttiva predefinita, chiamata AUTOTASK che si applica ai task di gestione automatica (automatic maintenance tasks) che girano nel root duranto le finestre di manutenzione. Questa direttiva può essere modificata con la procedura UPDATE_CDB_AUTOTASK_DIRECTIVE

Una cosa curiosa, scritta sul manuale, è che se un PDB viene scollegato “unplugged” la direttiva che lo riferisce viene mantenuta e nel caso il PDB venga ricollegato viene riutilizzata. Qui ho una lacuna da colmare perché mi sembra di ricordare dai miei test che dopo l’unplug l’unica cosa che si può fare con un PDB e la rimozione, dopodiché si riesce a ricollegare; mi sembra strano che venga mantenuto un riferimento a un PDB rimosso… spero di poter fare una prova al riguardo.

PDB Resource Plans

A livello di PDB si definiscono dei piani in maniera analoga a quanto si faceva prima su database non-CDB, ci sono però dei limiti:

  • un PDB resource plan non può avere sottopiani
  • un PDB resource plan può avere un massimo di otto consumer group
  • un PDB resource plan non può avere politiche di schedulazione multi-livello (multiple-level scheduling policy)

Se si collega come PDB un database non-CDB che ha dei resource plan che non rispettano i vincoli sopra Oracle modificherà i piani per farli rientrare nei vincoli.

P.S. 18/06/2018

sono riuscito a fare una prova riguardo al definire un CDB plan con delle direttive che fanno riferimento a un PDB. Ho fatto successivamente l’unplug e poi il drop del PDB ed effettivamente la direttiva che riferisce il pluggable database rimosso rimane.

Annunci

Real-Time Database Operation Monitoring

giovedì 7 giugno 2018 alle 07:24 | Pubblicato su 11g, 12c, Diario, Performance Tuning | Lascia un commento

Con la versione 11.1 Oracle ha introdotto un nuovo strumento di diagnosi e analisi delle prestazioni chiamato “Real-Time SQL Monitoring”. Si tratta di uno strumento che permette di controllare o monitorare in tempo reale l’esecuzione di query o statement SQL che durano a lungo e consumano risorse in modo significativo. Oracle in determinate condizioni, quando in una sessione gira una query che attiva l’esecuzione in parallelo o consuma più di 5 secondo di tempo CPU o I/O, comincia a registrare le principali statistiche di esecuzione dello statement quali tempo CPU, letture su disco, letture da buffer ecc e le espone tramite due viste chiamate V$SQL_MONITOR e V$SQL_PLAN_MONITOR.  Le statistiche vengono aggiornate ogni secondo e successivamente mantenute per almeno 5 minuti (qui dice così, qui dice un minuto), dopodiché se l’area di memoria in SGA dove risiedono queste informazioni è in esaurimento le statische possono venir cancellate. Si tratta di una evoluzione significativa rispetto alle analisi che si potevano e si possono ancora fare sulle classiche viste V$SQL e V$SQL_PLAN perché ad esempio le statistiche esposte sulle nuove viste sono legate alla singola esecuzione e non cumulative come nelle tradizionali V$SQL e V$SQL_PLAN. Vi è poi la possibilità, tramite procedure del package DBMS_SQLTUNE, di   avere dei report dettagliati e completi sull’esecuzione di una singola query: troviamo piano di esecuzione, valori delle bind variables. Ci sono  statistiche come il numero di righe attese e quello reale che prima era difficile avere. Naturalmente queste informazioni sono accessibili in modo comodo dalle console grafiche Cloud Control e, per 12c,  EM Express.

Con la versione 12.1 il concetto di Real-Time SQL Monitoring è stato esteso ed è diventato “Real-Time Database operation monitoring”. E’ possibile dalla versione 12c avere le stesse informazioni ma non più solo a livello di singolo statement SQL o blocco PL/SQL ma anche per un blocco di istruzioni delimitato temporalmente all’interno di una sessione che è definito appunto Database Operation. Ad esempio è possibile così monitorare complesse procedure ETL o altri tipo di procedure batch per tenere traccia dell’esecuzione ed individuare problemi di prestazioni o controllarne il consumo di risorse. Con oracle 12c è stato introdotto un nuovo package PL/SQL chiamato DBMS_SQL_MONITOR che fornisce le procedure “BEGIN_OPERATION” e “END_OPERATION” che servono appunto a delimitare le istruzioni che compongono la “database operation” che si vuole monitorare e analizzare. Oltre a quelle due procedure nel package si ritrovano le procedure “REPORT_SQL_MONITOR” e “REPORT_SQL_MONITOR_LIST” che si trovano ancora nel package DBMS_SQLTUNE, non so quale sia la differenza.

Posso solo segnalare una mancanza nel report, che da un lato da indicazioni in tempo reale anche sul punto in cui si trova l’esecuzione, dall’altro però non include le “predicate information” che si hanno ad esempio con la funzione “dbms_xplan.display_cursor”. Questo è un peccato perché costringe a fare una analisi con più strumenti per avere tutte le informazioni che possono essere utili ad analizzare le prestazioni di una query.

“Real-Time Database operation monitoring” è una “feature” del pacchetto “Oracle Database Tuning Pack” (per il quale quindi serve apposita licenza).

 

Ottimizzatore Oracle 12cR1 note varie

venerdì 25 maggio 2018 alle 25:04 | Pubblicato su 12c, Diario, Performance Tuning | 2 commenti

Gli ultimi due post che ho scritto erano dedicati alle nuove funzionalità introdotte nell’ottimizzatore delle query in oracle 12c (12.1). Ho riassunto in due post relativamente brevi una serie di  funzionalità abbastanza consistente e su quegli argomenti si potrebbe veramente scrivere molto. In realtà è già stato scritto molto, il problema è leggere tutto e riordinare tutte le informazioni. Questo post prende spunto principalmente da una letta che ho dato a un paio di post sul blog di Christian Antognini sull’argomento “Adaptive query optimization”, in particolare questo, questo  e questo.  Volendo ce ne sono anche altri, basta aver tempo di leggerli con attenzione. Io riassumerei, anche leggendo la nota del supporto Oracle “Recommendations for Adaptive Features in Oracle Database 12c Release 1 (Adaptive Features, Adaptive Statistics and 12c SQL Performance) (Doc ID 2312911.1)” , che qualche problemino su Oracle 12.1 lo si può avere con queste nuove caratteristiche e usarle e tenerle sotto controllo con tutti i parametri e le variabili coinvolte non mi sembra affatto facile. In ogni caso per riuscirci è proprio il caso di leggere bene i post di Antognini  e le note del supporto Oracle che indica, seguendo anche gli ulteriori rimandi che si trovano. Forse con la versione 12.2 le cose vanno meglio.

Per quanto mi riguarda ho voluto provare su un database di sviluppo interno a mettere in opera le SQL Plan Baselines mettendo il parametro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES a TRUE; dopo alcuni giorni però ho avuto alcune segnalazioni da sviluppatori che accedendo al database con SQL Developer avevano problemi di lentezza. Non ho avuto modo di fare una analisi seria, ho solo identificato la causa in una query lanciata da SQLDeveloper che recupera le informazioni sulle colonne delle tabelle, a quanto pare la query  rivista da oracle rimaneva per qualche motivo bloccata. Per risolvere velocemente il prolema ho dovuto reimpostare il parametro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES a FALSE e siccome non risolveva il problema ho impostato anche OPTIMIZER_USE_SQL_PLAN_BASELINES a FALSE, perché immagino che avendo comunque creato una serie di SQL Plan Baselines l’ottimizzatore continuasse ad usarle, anche non ne creava più di nuove. Essendo la query su viste di sistema (in particolare la ALL_OBJECTS per il cui il solo count usa un piano con 129 righe) non sono riuscito a capirci molto, sono però rimasto un po’ deluso da una funzionalità che dovrebbe avere lo scopo di impedire peggioramenti di prestazioni.

SQL Plan Management e SQL Plan Baselines

venerdì 4 maggio 2018 alle 04:20 | Pubblicato su 12c, Diario, Performance Tuning | Lascia un commento
Tag:

Con la versione 11.1 Oracle ha sostituito il meccanismo chiamato “Stored Outlines” con uno nuovo chiamato “SQL Plan Baselines”. L’obiettivo è rimasto lo stesso, quello di prevenire il peggioramento delle prestazioni dovuto a cambiamenti di sistema, di versione dell’ottimizzatore o altro che possa far cambiare il piano di esecuzione scelto per una query SQL. Evidentemente le stored outlines erano limitate rispetto alle nuove “SQL Plan Baselines” tanto appunto da richiedere una cosa molto più sofisticata che quindi ha preso un nuovo nome. Cercherò di riportare una sintesi di quanto riporta il manuale “Oracle 12.1 Database SQL Tuning Guide”, capitolo 23: Managing SQL Plan Baselines” Posso dire che a suo tempo ho studiato le Stored Outlines ma non sono mai riuscito ad utilizzarle perché non si adattavano alle esigenze che avevo. Le SQL Plan Baselines le ho esplorate da poco e non potrò per ora portare esperienze pratiche, mi limiterò quindi a quanto trovo sul manuale.

SQL Plan Baseline

Una SQL plan baseline (SPB) è un insieme di piani accettati che l’ottimizzatore è autorizzato ad utilizzare per uno statement SQL. Tipicamente i piani vengono introdotti in una SQL plan baseline dopo che è stato verificato che non portano a maggior uso di risorse con conseguente peggioramento delle prestazioni. Questo sistema si affianca al meccanismo dei “SQL Profile”, generati dal SQL Tuning Advisor che però sono un meccanismo reattivo, mentre l’SPB vuole essere un meccanismo proattivo: ha lo scopo di prevenire situazioni di peggioramento di prestazioni. Questo nel quadro di un aggiornamento di versione Oracle, di cambiamento di parametri o di cambiamento dell’hardware sottostante. All’interno di una SQL plan baseline ogni piano è specificato usando un insieme di “outline hints” che lo definiscono completamente. Questo si contrappone a SQL Profiles che piuttosto specificano solo degli hint che cercano di correggere errate stime sui costi del piano e quindi non forzano l’uso di un preciso piano.

SQL Plan Capture

La gestione dei piani di esecuzione tramite le SQL Plan baseline avviene in diversi passi, il primo di questi è la cattura e il salvataggio di tutte le informazioni sui piani di esecuzione di un insieme di statement SQL.  Le informazioni vengono salvate nella “SQL Management Base” (SMB). La cattura dei piani può avvenire in modo automatico tramite il settaggio del parametro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES a “true” (per default è “false”). Quando il parametro è a true il database crea una SQL plan baseline per ogni statement SQL “ripetibile” eseguito sul database stesso.

La cattura manuale avviene tramite procedure del package PL/SQL DBMS_SPM (DBMS_SPM.LOAD_PLANS_FROM_%) e consiste nel caricamento o da un “SQL tuning set (STS)” o dalla “share SQL area” o da una “tabella di staging” o da una “Stored Outline”. Non posso non riportare una immagine dal manuale che mi sembra molto chiara e completa:

Un piano caricato manualmente viene salvato come “accettato”

SQL Plan Selection

Come si vede dalla figura sopra nella SQL Management Base viene salvato anche la storia di un piano di esecuzione, cioè vengono registrati i cambi di piano

Quando il database esegue un “hard parse” di uno statement SQL l’ottimizzatore genera un piano ottimale per costo. Per default poi l’ottimizzatore cerca un piano che corrisponda nella SQL plan baseline di quello statement. Se non trova una SQL plan baseline per quello statement usa il piano gia generato (crea la SPB e aggiunge il piano come “accepted”), se la trova e il piano generato è gia nella baseline allora lo usa, se non c’è lo aggiunge alla “plan history” marcato come “unaccepted”. In quest’ultimo caso, se esistono piani fissati viene usato quello con il costo minore, se non esistono piani fissati viene usato il piano nella baseline con il costo minore, se non esistono piani riproducibili nella baseline (ad esempio perché nel frattempo è stato rimosso un indice) allora l’ottimizzatore usa il nuovo piano generato. Un piano fissato è un piano accettato e marcato manualmente come preferito.

SQL Plan Evolution

L’evoluzione di un piano è il procedimento che consiste nel verificare che i piani non accettati abbiano pretazioni almeno pari a quelli gia accettati (SQL Plan verification) e in tal caso nella loro aggiunta ai piani accettati della baseline. È possibile configurare il SQL Plan management affinché vengano eseguite tutte le possibili combinazioni dei due passi, cioè si può fare la verifica del piano senza l’aggiunta alla baseline e si può fare l’aggiunta senza fare la verifica. Il package PL/SQL DBMS_SPM fornisce le seguenti procedure:

  • SET_EVOLVE_TASK_PARAMETER
  • CREATE_EVOLVE_TASK
  • EXECUTE_EVOLVE_TASK
  • REPORT_EVOLVE_TASK
  • IMPLEMENT_EVOLVE_TASK
  • ACCEPT_SQL_PLAN_BASELINE

Queste procedure servono per avviare manualmente il processo di evoluzione. Oracle comunque raccomanda di lasciare al task automatico SYS_AUTO_SPM_EVOLVE_TASK (introdotto in Oracle 12c) il compito di fare il lavoro di verifica e accettazione durante la finestra di manutenzione pianificata (normalmente ogni notte)

SQL Management Base (SMB)

Si tratta della struttura logica dove vengono salvate le informazioni relative alle SQL plan baseline, fisicamente stanno nella tablespace SYSAUX. Le componenti sono quattro:

  • SQL statement log
  • SQL plan history (che include le sql plan baseline)
  • SQL profiles
  • SQL patches

SQL statement log

Durante il “parse” dello statement oracle calcola una “firma” (SQL signature)  normalizzando rispetto a maiuscole e minuscole e a spazi il testo, quando avviene la cattura automatica viene cercata la firma all’interno del “SQL statement log” (SQLLOG$), se non c’è l’aggiunge se c’è lo statement si può definire un “repeated SQL statement”

SQL Plan History

si tratta dell’insieme di piani di esecuzione catturati,  si quelli accettati, quindi nelle SQL plan baseline che quelli non accettati. Dalla versione 12c viene salvato il piano intero per tutti i piani, in questo modo non deve essere ricompilato lo statement per rigenerare il piano. I piani possono essere abilitati (enabled), che è il default o no. Nel caso non siano abilitati non vengono utilizzati anche se accettati.

La SQL Plan history può essere analizzata tramite la vista DBA_SQL_PLAN_BASELINES, tramite la procedure DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE è possibile vedere il dettaglio di una SQL plan baseline.

 

Oltre al parametro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES citato prima c’è un’altro parametro OPTIMIZER_USER_SQL_PLAN_BASELINES che per default è impostato a true e fa si che comunque per gli statement che hanno gia SQL plan baseline esistenti vengano aggiunti nuovi piani come non accettati. Se viene impostato a false viene disabilitato completamente l’uso delle baseline.

Sul manuale si trovano ulteriori dettagli ed esempi sulla gestione che non sto qui a riportare per non appesantire troppo il post

Ottimizzatore delle query in Oracle 12c

venerdì 27 aprile 2018 alle 27:53 | Pubblicato su 12c, Diario, Performance Tuning | Lascia un commento
Tag:

Con questo post ho l’ambizione di riassumere le principali caratteristiche dell'”ottimizzatore” di query di Oracle. L’impresa sta nel far stare in un unico post tutte le principali informazioni, cosa che non so se sarò in grado di fare vista l’abbondanza di elementi che il soggetto ha in questa versione di Oracle. Premetto che la fonte principale delle informazioni è il manuale Oracle “Database SQL Tuning Guide” versione 12.1. Trovo che la manualistica Oracle sia molto ben fatta, molto ampia è approfondita quanto basta. Siccome studiando per la certificazione ho avuto qualche difficoltà a mettere insieme e in ordine nella mia memoria tutti gli argomenti trattati in questo capitolo ho sentito la necessità di scrivere questo post che seguirà il tracciato del manuale. Se dovesse diventare troppo lungo lo spezzerò in più post.

Cominciamo con un veloce ripasso dei concetti di base, a partire dalle definizioni, tenendo conto che tradurre tutto è difficile quindi per semplicità, a differenza di quello che ho fatto nel titolo, manterrò la nomenclatura originale in inglese.

Il “query optimizer”, chiamato più in breve semplicemente “optimizer” (o ottimizzatore) è quella parte del software Oracle che si occupa di determinare il modo più efficente di recuperare i dati richiesti tramite una query SQL; il risultato dell’elaborazione dell’ottimizzatore è il “piano di esecuzione”. Sappiamo che l’SQL è un linguaggio dichiarativo, quindi l’ottimizzatore può permettersi di scegliere abbastanza liberamente come recuperare e preparare i dati richiesti tramite SQL. In Oracle l’ottimizzatore è chiamato anche “cost-based optimizer” (CBO) perché le sue elaborazioni girano attorno al concetto di costo. Ogni elemento di accesso ed elaborazione dei dati da recuperare ha un costo che può essere tempo di CPU, tempo di accesso ai dischi e tempo di comunicazione. La somma dei costi di tutti i singoli passi di un piano di esecuzione da il costo del piano di esecuzione che sarà l’elemento di valutazione del piano stesso. Alla base di tutto ci sono una serie di statistiche, sui dati e sul sistema che l’ottimizzatore usa per stimare il costo finale di un piano.

Componenti dell’ottimizzatore

Il manuale ci dice che l’ottimizzatore consiste di tre componenti:

  1. Query transformer
  2. Estimator
  3. Plan generator

Query transformer

questo componente cerca di riscrivere un blocco SQL in uno semanticamente identico ma gestibile in modo più efficente, l’esempio banale fatto sul manuale è la trasformazione di una query con un “OR” in una query con una “UNION ALL”

Estimator

direi che questo è il compente più importante, quello che calcola il costo di un piano per un blocco SQL. Gli elementi di misura che usa l'”estimator” sono:

  1. Selectivity (selettività), percentuale dei dati estratti, questo dato non viene visualizzato nei piani di esecuzione.
  2. Cardinality (cardinalità), volume dati estratto
  3. Cost (costo), gli elementi base sono CPU, I/O e memoria. È la parte più complessa, tiene conto delle risorse utilizzate, nel numero stimato di righe restituite (cardinalità), della dimensione iniziale dell’insieme dei dati, della distribuzione dei dati e delle strutture di accesso.

 

Il costo non è necessariamente legato al tempo finale di esecuzione.

Plan generator

questo componente si occupa di generare possibili piani di esecuzione, confrontarne il costo e scegliere quello con il costo minore.

Sembra chiaro che le tre componenti in fase di generazione di un piano di esecuzione interagiscono continuamente e non lavorano semplicemente per stadi successivi.

Alla base di tutto vi sono delle statistiche di sistema, ad esempio: a certe operazioni che richiedono elaborazione di CPU verrà assegnato un costo in termini di cicli di CPU, poi il sistema in qualche modo terrà conto del costo di un ciclo di CPU. Poi ci sarà un costo per l’I/O, ad esempio il sistema stimerà che recuperare un blocco da disco costa tot e anche qui il costo potrebbe dipendere dalle prestazioni del sistema di I/O, mi vien da dire  che il tempo medio potrebbe essere 1 msec o 10 msec e fra i due c’è molta differenza. Ci sono poi operazioni come l'”hash join” che necessitano sia di CPU che di memoria per fare degli ordinamenti.

Le statistiche di sistema insomma sono dati “fissi” legati essenzialmente alle potenza computazionale della macchina su cui gira il database. Vi sono poi altre statistiche più complesse e molto importanti che sono quelle sui dati, ad esempio il numero di record in una tabella, la distribuzione dei valori in un campo di una tabella ecc. Mi sento di poter semplificare molto dicendo che le statistiche sui dati vengono moltiplicate per dei coefficenti fissi che sono dati dal tipo di operazione e le statistiche di sistema. Quindi potremmo avere due sistemi con lo stesso insieme di dati ma con caratteristiche computazionali molto diverse, supponiamo uno con tanta CPU e Memoria ma un I/O “lento” e uno opposto con poca CPU e memoria ma I/O molto veloce e nei due casi una stessa query SQL potrebbe dare origine a due piani molto diversi sui due sistemi. Ovviamente vale anche la situazione inversa (più comune), sistemi con caratteristiche computazionali uguali ma insiemi dati diversi. L’esempio più facile da capire è una select su una tabella voluminosa, se il filtro fa si che venga richiesta una percentuale molto piccola dei dati della tabella tipicamente il piano di esecuzione più efficente consiste nell’utilizzare un indice (dando per pressupposto che l’indice esista), viceversa se il filtro fa si che ad essere richiesti siano quasi tutti i dati della tabella allora è più efficente scandire direttamente tutta la tabella piuttosto che perdere tempo palleggiando tra indice e tabella.

Fin qui abbiamo fatto un ripasso generale su concetti di base che valgono anche in vecchie versioni Oracle, ora vediamo un po’ di cose nuove.

Automatic tuning optimizer

L’ottimizzatore può lavorare in due modalità

  • “normal optimization”, quella con cui normalmente genera i piani di esecuzione
  • “SQL Tuning Advisor optimization”, una modalità avanzata in cui l’ottimizzatore fa una analisi più accurata di uno statement SQL, il risultato però non è il piano di esecuzione ma una serie di suggerimenti con la loro motivazione (rationale) e la stima dei benefici

In realtà questa cosa forse esiste gia dalla versione Oracle 10g….

Adaptive Query Optimization

Questa funzionalità abilita l’ottimizzatore a fare delle correzioni al volo sul piano di esecuzione e a rilevare ulteriori informazioni che possono aiutare alle successive esecuzioni. L'”ottimizzazione adattiva” è utile quando le statistiche esistenti non permettono di generare un piano ottimale.

Gli elementi che rientrano nella “Adaptive query optimization” sono diversi:

  • Adaptive plans
    • Join methods
    • Parallel distribution methods
    • Bitmap index pruning
  • Adaptive Statistics
    • Dynamic Statistics
    • Automatic reoptimization
      • statistics feedback
      • performance feedback
    • SQL Plan Directives

Mi permetto di riportare un collegamento alla figura del manuale che rende meglio l’idea:

Adaptive Query Plans

Con questo nome di definisce la caratteristica per cui l’ottimizzatore è in grado di cambiare parte del piano di esecuzione anche dopo l’inizio dell’esecuzione del piano. Un adaptive plan (piano adattivo?) è un piano che contiene più sottopiani precalcolati e comprende un “optimizer statistics collector” (raccoglitore di statistiche). Durante l’esecuzione l’ottimizzatore tramite il “collector” inizia a raccogliere informazioni sull’esecuzione e se si accorge che le stime iniziali erano sbagliate può decidere di cambiare il “default plan” (quello scelto in partenza) con un altro piano, cambiandone un pezzo, quello che può cambiare è un metodo di join (hash piuttosto che nested loop). A questo punto il collettore di statistiche si ferma e il nuovo piano diventa quello definitivo.

Affinché questa funzionalità sia operativa devono essere impostati i seguenti parametri:

  • OPTIMIZER_FEATURES_ENABLE=12.1.0.1
  • OPTIMIZER_ADAPTIVE_REPORTING_ONLY=FALSE

Entrambi hanno quei valori per default.

Il parametro “FORMAT=>’ADAPTIVE'” della funzione DBMS_XPLAN.DISPLAY_CURSOR permette di visualizzare informazioni sugli “adaptive query plan”

Sul manuale vengono riportati degli esempi per ciascuno dei tre casi possibili, metodo di join, metodo di distribuzione parallela (al riguardo credo che l’argomento meriti un approfondimento, per ora mi limito a riportare il link al manuale) e “bitmap index pruning”.

Adaptive statistics

Quando i predicati nelle query sono molto complessi le statistiche di base sui dati da sole non garantiscono stime precise, per questo c’è il supporto delle “adaptive statistics” (statistiche adattive) che sono di tre tipi, Dynamic statistics, Automatic reoptimization e SQL Plan Directives

Dynamic Statistics

durante la compilazione di una query SQL l’ottimizzatore valuta se le statistiche disponibili sono sufficienti, altrimenti per aumentare i dati a disposizione usa le statistiche dinamiche che sono una estensione di quello che nelle versioni precedenti di Oracle veniva chiamato “dynamic sampling”. Queste statistiche possono venir usate per “table scans”, “index access”, join e GROUP BY.

Le statistiche dinamiche sono attivate in funzione del valore del parametro OPTIMIZER_DYNAMIC_SAMPLING che per default ha valore 2  che indica che il campionamento viene fatto solo se una delle tabelle coinvolte non ha statistiche. Il parametro accetta valori da 0 (dynamic statistics disabilitate completamente) a 11 dynamic statistics completamente automatiche

Automatic Reoptimization

Con l’adaptive query plan abbiamo visto può solo cambiare per un piano il tipo di join o il metodo di distribuzione parallela o applicare il bitmap index pruning. Per casi più complessi, ad esempio per cambiare l’ordine dei join può intervenire la automatic reoptimization, la quale però può solo stabilire un nuovo piano di esecuzione al termine della prima esecuzione della query in modo da utilizzarlo alle successive richieste. Non è in grado di cambiare un piano in esecuzione. Questo processo può continuare anche alle successive esecuzioni con l’obbiettivo di usare gli ulteriori dati raccolti per ottimizzare. La ri-ottimizzazione avviene sulla base di due tipologie di dati: “statistics feedback” e “performance feedback”

Statistics feedback

si tratta di quello che in precedenza si chiamava “cardinality feedback”. Se durante l’esecuzione di una query SQL l’ottimizzatore verifica una delle seguenti situazioni:

  • tabelle senza statistiche
  • predicati multipli congiuntivi o disgiuntivi  (traduzione mia, spero corretta)
  • predicati contenenti operatori complessi

allora attiva il monitoraggio delle statistiche di feedback. Se al termine dell’esecuzione c’è una differenza sostanziale fra dati rilevati e stime allora l’ottimizzatore salva le informazioni ad uso futuro sotto forma di SQL plan directive. In verità nel manuale dice che le salva e anche crea una SQL plan directive. Dopo la prima esecuzione il monitoraggio delle statistiche di feedback viene disabilitato.

Performance feedback

In questo caso vengono raccolte informazioni per stabilire un grado di parallelismo ottimale. Per attivare questa funzionalità occorre settare il parametro:

  • PARALLEL_DEGREE_POLICY=ADAPTIVE

Il principio rimane lo stesso, viene confrontato il grado di parallelismo calcolato a priori con quello calcolato alla fine dell’esecuzione sulla base dei dati raccolti (ad esempio tempo di CPU).

Sul manuale una nota dice che anche si parametro PARALLEL_DEGREE_POLICY non è settato a ADAPTIVE le “statistics feedback” possono influenzare il grado di parallelismo scelto per una query.

SQL Plan Directives

Si tratta di informazioni aggiuntive che l’ottimizzatore usa per generare  un piano ottimale. Si tratta di informazioni legate a “query expressions”, non all’intera query, questo permette di utilizzarle anche per query non uguali (seppur simili).

Durante la compilazione l’ottimizzatore verifica se mancano “statistiche estese” o istogrammi e ne registra la mancanza. Alle successive chiamate di raccolta delle statistiche tramite il package DBMS_STATS verranno calcolati eventuali istogrammi mancanti e statistiche estese.

Le SQL Plan Directives (SPD) vengono salvate nella SHARED POOL e ogni 15 minuti in modo permanente sulla tablespace SYSAUX . Se per 53 settimane non vengono usate vengono eliminate automaticamete. Possono essere gestite con il package PL/SQL DBMS_SPD, sul manuale riporta un esempio d’uso dei seguenti due metodi:

  • FLUSH_SQL_PLAN_DIRECTIVE (forza la scrittura su disco)
  • DROP_SQL_PLAN_DIRECTIVE

Vi sono poi dei metodi per esportare e importare le SPD

Le viste per vedere le SPD sono:

  • DBA_SQL_PLAN_DIRECTIVES
  • DBA_SQL_PLAN_DIR_OBJECTS

Extended Statistics

Le statistiche estese sono statistiche su gruppi di colonne o statistiche su espressioni. Le normali statistiche sulle singole colonne possono non bastare se i dati di due colonne  hanno una correlazione “imprevedibile”. Tramite il package DBMS_STAT e precisamente i seguenti metodi:

  • SEED_COL_USAGE
  • REPORT_COL_USAGE
  • CREATE_EXTENDED_STATS

É possibile, in maniera semiautomatica creare estensioni basandosi un test di carico. Una volta creata l'”estensione” le statistiche verranno raccolte anche sull’estensione e utilizzate dall’ottimizzatore.

È possibile visualizzare le estensioni esistenti tramite la vista USER_STAT_EXTENSIONS

 

Con questo penso di potermi fermare qui. La lunghezza del post mi pare accettabile e il livello di approfondimento è medio, spero di riuscire a trascrivere ancora qualcosa per approfondire ed estendere la tematica.

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

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.



Pagina successiva »

Blog su WordPress.com.
Entries e commenti feeds.