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

Annunci

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.

Gestione Audit su database Oracle

giovedì 1 febbraio 2018 alle 01:05 | Pubblicato su 11g, 12c, Diario, Installation and Configuration | Lascia un commento
Tag:

Il titolo del post è pretenzioso e di fatto non rispecchia il vero contenuto in quanto coprire l’argomento “Audit” in Oracle è una cosa abbastanza difficile per la sua complessità. Il manuale “Security Guide” della versione 12.1 copre l’argomento con tre capitoli. Vi sono poi un po’ di novità sulla versione 12.1 rispetto alla 11.2. E’ un argomento su cui faccio fatica a raccapezzarmi, quindi provo ad affrontarlo per gradi.

Il post precedente è un esempio di quanto faccia fatica a dedicarmi all’approfondimento di nuovi (per me) temi. Cerco di rimediare alla confusione di quel post con un post più specifico e spero più ordinato e preciso in cui parlo esclusivamente della gestione della pulizia degli “audit” generati da Oracle.

Comincio con una informazione certificata dal supporto Oracle con la nota 308066.1 la quale spiega che anche con i parametri audit_trail=NONE e audit_sys_operations=FALSE vengono generati sotto il percorso indicato dal parametro audit_file_dest dei file .aud. In particolare vengono sempre e comunque generati dei file in cui si registrano le connessioni con utenti SYSDBA o SYSOPER e le operazioni di avvio e arresto dell’istanza. Questo vale sia per Oracle 11.2 che 12.1

Ora, i file sono piccoli ma possono diventare numerosi, nel mio caso ci sono procedure che si collegano con utenze privilegiate per fare dei controlli automatici e quindi vengono generati centinaia di file al giorno. Dopo qualche mese i file diventano migliaia. Da notare che se il filesystem si riempie non si riesce neppure ad accedere al database con l’utenza SYSDBA perché il sistema si lamenta di non riuscire a scrivere il file di audit. Ora, per gestire tutti i tipi di Audit Oracle mette a disposizione un package PL/SQL che si chiama DBMS_AUDIT_MGMT. I tipi di Audit che Oracle gestisce sono diversi, con la versione 12 è stato introdotto lo UNIFIED Audit Trail per mettere in un unico posto tutte le informazioni ma per retrocompatibilità esistono i tipi presenti nella versione precedente e questi sono:

  • File xml (AUDIT_TRAIL_XML)
  • File normali (AUDIT_TRAIL_OS
  • Record standard sulla tabella SYS.AUD$
  • record di Fine Grained Audit (FGA) sulla tabella SYS.FGA_LOG$)
  • Per la 12 record sulla vista UNIFIED_AUDIT_TRAIL

Ora per ciascuno di questi “canali” di audit c’è una costante nel package DBMS_AUDIT_MGMT.

Se ci si vuole limitare alla pulizia dei file .aud senza alcun criterio particolare il modo più semplice è l’uso della procedura DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL in questo modo:

BEGIN
 DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
 USE_LAST_ARCH_TIMESTAMP => FALSE );
END;
/

Con l’aiuto della procedura DBMS_AUDIT_MGMT.CREATE_PURGE_JOB si può pianificare una pulizia automatica periodica, ad esempio:

begin
 DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, 
 AUDIT_TRAIL_PURGE_INTERVAL => 24,
 AUDIT_TRAIL_PURGE_NAME => 'OS_Audit_Trail_PJ',
 USE_LAST_ARCH_TIMESTAMP => FALSE );
END;
/

Con gli esempi sopra vengono eliminati solo gli audit su file, se si vuole eliminare tutto, quindi ad esempio anche il contenuto della tabella AUD$ occorre fare un passo preliminare di inizializzazione. Occorre utilizzare la procedura DBMS_AUDIT_MGMT.INIT_CLEANUP che stando alla documentazione semplicemente sposta le tabelle dalla tablespace SYSTEM a SYSAUX, cosa che forse è sempre e comunque utile (al punto da chiedersi perché Oracle non le metta direttamente li in automatico. Infatti con lo unified audit trail è così). Quindi, ad esempio:

BEGIN
 DBMS_AUDIT_MGMT.INIT_CLEANUP(
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
 DEFAULT_CLEANUP_INTERVAL => 12 );
END;
/

La nota del supporto Oracle 1243324.1 riferendosi a Oracle 10.2.0.5 riferisce che il parametro DEFAULT_CLEANUP_INTERVAL è di fatto inusato, non mi è chiaro se cambia qualcosa sulle versioni 11 e 12.  Di sicuro l’intervallo da indicare sulla procedura CREATE_PURGE_JOB è obbligatorio e quello viene usato.

begin
 DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, 
 AUDIT_TRAIL_PURGE_INTERVAL => 24,
 AUDIT_TRAIL_PURGE_NAME => 'ALL_Audit_Trail_PJ',
 USE_LAST_ARCH_TIMESTAMP => FALSE );
END;
/

 

Su Oracle 12 le procedure hanno dei parametri in più per gestire l’architettura multitenant, quindi nel mio caso alla procedura CREATE_PURGE_JOB ho aggiunto il parametro:

 container=>DBMS_AUDIT_MGMT.CONTAINER_ALL

Il default del parametro “container” è CONTAINER_CURRENT.

Combinando in maniera saggia i parametri o usando il parametro LAST_ARCHIVE_TIMESTAMP si possono organizzare politiche più elaborate; alla fine, capiti i concetti di base le cose sono abbastanza semplici e facili da capire

Manutenzione log e audit in Oracle

martedì 30 gennaio 2018 alle 30:35 | Pubblicato su 11g, 12c, Diario, Installation and Configuration | Lascia un commento
Tag:

Una cosa che non ho mai realmente approfondito, prima di adesso, era la manutenzione e pulizia dei vari file di log che un database server Oracle genera. In realtà, in condizioni normali, non c’è questo gran bisogno di fare manutenzione, per questo non me ne sono mai curato seriamente.  Ultimamente però mi è capitato spesso di dover fare pulizia in emergenza, in particolare ho avuto difficoltà con i file generati dal sistema di “Auditing” di Oracle. Il sistema di Auditing di Oracle fra l’altro è una cosa che nelle ultime versioni si è evoluto ed è abbastanza complesso.  Senza entrare quindi troppo nel dettaglio mi limito a dire che sembra che in qualunque condizione Oracle generi sempre e comunque un nuovo file .aud sotto $ORACLE_BASE/admin/<dbname>/adump ogni volta che avviene un accesso con una utenza SYSDBA. A causa di sistemi che fanno accessi automatici con questa utenza mi sono trovato quindi dopo un breve periodo di attività un numero esorbitante di file .aud. Il fatto è che oltre un certo numero anche rimuoverli con un semplice “rm” diventa difficile perché si ottiene un errore perché il sistema si lamenta che la lista dei file è troppo lunga…. Per un po’ ho sopperito come potevo manualmente, poi ho deciso di affrontare la questione in modo un po’ più  serio. Ho verificato quanto si riporta qui, ovvero che la generazione di questi file non può essere disattivata, infatti confermo che continua anche impostando il parametro AUDIT_TRAIL al valore “NONE”. Quello che è più importante secondo me di quel post è il primo commento, il quale riporta una procedura abbastanza semplice per gestire in modo automatico la pulizia di questi file direttamente da Oracle. Si tratta di usare il package PL/SQL DBMS_AUDIT_MGMT. Io al momento ho semplificato con queste tre chiamate:

BEGIN
 DBMS_AUDIT_MGMT.INIT_CLEANUP(
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
 DEFAULT_CLEANUP_INTERVAL => 1 );
END;
/
exec sys.dbms_audit_mgmt.set_last_archive_timestamp(audit_trail_type => sys.dbms_audit_mgmt.audit_trail_os,last_archive_time => sysdate - 3);
BEGIN
 DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, 
 AUDIT_TRAIL_PURGE_INTERVAL => 1,
 AUDIT_TRAIL_PURGE_NAME => 'ALL_Audit_Trail_PJ',
 USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

 

Di fatto viene schedulato un “job” di pulizia dei file di audit. Prima di poter creare il job vero e proprio occorre fare una inizializzazione. Secondo la documentazione uno dei passi dell’inizializzazione è spostare le tabelle su cui vengono registrati i dati di audit dalla tablespace SYSTEM a SYSAUX. In effetti ho avuto su un paio di database, durante la chiamata della procedura INIT_CLEANUP, errori di spazio sulla tablespace SYSAUX. La cosa bizzarra è che anche se la tablespace era in autoestensione la procedura mi dava errore comunque, l’unico modo quindi è allocare manualmente lo spazio espandendo il datafile.

La procedura CREATE_PURGE_JOB crea il “job” che si occuperà della pulizia dei file (e per come l’ho impostato io anche dei dati a db) più vecchi. Il punto è stabilire il periodo di ritenzione. Infatti la procedura di pulizia posso confermare gira appena si crea il job, poi dovrebbe girare secondo la schedulazione impostata, nel mio caso ogni ora (forse è un po’ esagerato ma sono ancora in fase sperimentale).  Senza la chiamata di SET_LAST_ARCHIVE_TIMESTAMP non si otterrà molto. Infatti il principio è che bisogna prima “archiviare” i file, quindi si dice a Oracle fino a che data e ora  i dati sono stati archiviati e così Oracle con il job schedulato li cancellerà….. ora il dubbio che mi è venuto scrivendo è che se non aggiorno almeno periodicamente il timestamp con la chiamata di quella procedura il job di cancellazione dopo la prima chiamata non cancellerà più nulla. Ipotizzo sia così ma mi riservo di verificare nei prossimi giorni. In ogni caso va detto che uno dei pregi di queste procedure è che sono molto rapide, nel mio caso con centinaia di migliaia di file l’operazione è stata molto più rapida che non a farla cancellando i file a mano.

Molto banalmente, leggendo la documentazione ho capito che, se non mi interessa un tubo degli audit, basta chiamare la procedura di creazione del job con il parametro USE_LAST_ARCH_TIMESTAMP  a false, secondo la documentazione il job in questo modo cancellerà tutto.

Un’altra parte importante di gestione log è la gestione del cosiddetto ADR, Automatic Diagnostic Repository, il posto dove Oracle scrive log e trace vari.  In questo caso Oracle prevede gia per default delle procedure di pulizia di questa area. Utilizzando l’utility ADRCI con il comando “show control” si possono estrapolare i due parametri SHORTP_POLICY e LONGP_POLICY che indicano il periodo di ritenzione in ora per rispettivamente per quelli che Oracle definisce file di “breve vita” e “lunga vita”. Per default i valori sono rispettivamente 30 giorni e 365 giorni. A parte l’inghippo derivante alla possibilità di avere più “homes” i valori si possono facilmente modificare con i comandi “set control” ad esempio nel mio caso:

set control (shortp_policy=100)
set control (longp_policy=300)

 

In automatico sembra che la pulizia giri o due giorni dopo l’avvio dell’istanza o ogni 7 giorni (note oracle 1196437.1 e 1446242.1). Per forzare una pulizia immediata basta il semplice comando “purge”

L’unico inghippo è il file alert in formato testo che si trova sotto la directory trace, quello non viene gestito da adrci, quindi va gestito a “mano”, si può fare tranquillamente a caldo facendo da sistema operativo un “mv”. (conferma qui)

Rileggendo velocemente il post, scritto abbastanza in fretta mi rendo conto sia un po’ confuso, come confusa è stata la mia ricerca a causa della scarsità di tempo a disposizione. Siccome però il tempo continuerà a scarseggiare per ora lo pubblico così e spero poi di poter fare qualche approfondimento.

Oracle 12.1.0.2 Datapump import ORA-00001: unique constraint (SYS.PK_COL_GROUP_USAGE$) violated

lunedì 4 dicembre 2017 alle 04:40 | Pubblicato su 12c, Diario | Lascia un commento

Mi è uscito un titolo piuttosto lungo per questo post ma volevo riportare più informazioni possibili sul problema che ho incontrato e che qui voglio descrivere.

Qualche tempo fa, dopo che ci pensavo da mesi ho deciso di implementare delle procedure PL/SQL per gestire importazioni ed esportazioni con Oracle Datapump. Preso poi dall’entusiamo ho implementato una procedura per “duplicare” schemi db, operazione che mi viene richiesta sempre più frequentemente. Devo precisare che lavoro sia con Oracle 11.2 che con Oracle 12.1  per cui mi sono basato sulla documentazione della 11.2. Non mi risulta poi che a livello di funzionalità base, come quelle che uso io, ci siano differenze sostanziali fra le due versioni. Ho fatto qualche test su Oracle 11.2 e non ho avuto problemi, su Oracle 12.1 invece mi sono trovato sui log il seguente messaggio:

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.STATS_LOAD [MARKER]
MARKER
ORA-00001: unique constraint (SYS.PK_COL_GROUP_USAGE$) violated
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 105
ORA-06512: at “SYS.KUPW$WORKER”, line 11265
—– PL/SQL Call Stack —–
object line object
handle number name
0xfa0df780 27116 package body SYS.KUPW$WORKER
0xfa0df780 11286 package body SYS.KUPW$WORKER
0xfa0df780 24286 package body SYS.KUPW$WORKER
0xfa0df780 24415 package body SYS.KUPW$WORKER
0xfa0df780 20692 package body SYS.KUPW$WORKER
0xfa0df780 10206 package body SYS.KUPW$WORKER
0xfa0df780 13381 package body SYS.KUPW$WORKER
0xfa0df780 3173 package body SYS.KUPW$WORKER
0xfa0df780 12035 package body SYS.KUPW$WORKER
0xfa0df780 2081 package body SYS.KUPW$WORKER
0xca7f92f8 2 anonymous block
DBMS_STATS.EXPORT_STATS_FOR_DP
In STATS_LOAD with process_order 3538
Fixing up the name in the impdp stat table
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.STATS_LOAD [MARKER]
ORA-30926: unable to get a stable set of rows in the source tables
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPW$WORKER”, line 11259
—– PL/SQL Call Stack —–
object line object
handle number name
0xfa0df780 27116 package body SYS.KUPW$WORKER
0xfa0df780 11286 package body SYS.KUPW$WORKER
0xfa0df780 24286 package body SYS.KUPW$WORKER
0xfa0df780 24415 package body SYS.KUPW$WORKER
0xfa0df780 10105 package body SYS.KUPW$WORKER
0xfa0df780 13381 package body SYS.KUPW$WORKER
0xfa0df780 3173 package body SYS.KUPW$WORKER
0xfa0df780 12035 package body SYS.KUPW$WORKER
0xfa0df780 2081 package body SYS.KUPW$WORKER
0xca7f92f8 2 anonymous block
DBMS_STATS.EXPORT_STATS_FOR_DP
In STATS_LOAD with process_order 3538
Fixing up the name in the impdp stat table
Job “EXPDPUSER”.”TESTCRIDP_IMP” stopped due to fatal error at Mon Dec 4 13:49:14 2017 elapsed 0 00:37:36

Fra l’altro la procedura non ha dato alcun errore. Da una prima ricerca sono capitato su questa discussione sul forum oracle, dalla quale emerge gia il suggerimento di escludere le statistiche Oracle, cosa che io per la verità non ho mai sentito il bisogno di fare e quindi non ho mai fatto. Poi non ricordo più come, sono arrivato alla nota del supporto Oracle: “DataPump Import (IMPDP) Reports ORA-1 When Processing SCHEMA_EXPORT/STATISTICS/MARKER (Doc ID 2277109.1)

Quindi ho deciso che era ora di escludere almeno dall’importazione le statistiche  e così cercando aiuto nell’implementazione di questa modifica sono anche capitato su questo interessante post di Mike Smithers che suggerisce anche un altro valido motivo per escludere le statistiche. Devo dire che questo aspetto delle prestazioni sull’importazione delle statistiche non ho approfondito anche se credo meriterebbe.

Ho riprovato tutte le combinazioni e io sono riuscito a riprodurre il problema solo con la procedura PL/SQL di duplicazione  la quale utilizza un database link che punta a se stesso. La stessa cosa avviene se uso il comando impdp sempre con il dtabase link.  L’import da un dump su file, sia interfaccia PL/SQL che da comando “impdp” non mi ha mai dato questo problema.

 

MAX/MIN con KEEP (DENSE_RANK FIRST/LAST ORDER BY …)

venerdì 5 maggio 2017 alle 05:23 | Pubblicato su 11g, 12c, Diario, SQL | Lascia un commento

Devo dire che mi mancava questo aspetto del mio lavoro, trovare soluzioni a problemi, scoprendo ed approfondendo nuove cose. Partendo dalle fonti che ho trovato per il mio post precedente (al riguardo ho aggiunto al “blogroll” l’interessante blog di Stewart Ashton, dal quale sono partito oggi) ho avuto modo di leggere a approndire un post che ha cinque anni, cinque anni in cui non ricordo di aver mai visto l’operatore di cui parla. La cosa effettivamente molto bizzarra è che la sezione KEEP… oggetto di quel post non viene menzionata nella documentazione in corrispondenza delle funzioni MIN e MAX, neanche nella sezione dedicata alle funzioni analitiche (anche perché non sembra rientrare in quella categoria) ma come indicato da Rob Van Vijk nel post viene descritta (sommariamente direi) alle funzioni FIRST e LAST  che sono in realtà solo una parte della clausola KEEP. I link alla documentazione Oracle riportati nel post del 2012 nel frattempo sono stati invalidati da un cambiamento fatto da Oracle, c’è sicuramente lo stesso problema anche in molti miei vecchi post.

Ho poco da aggiungere all’ottimo post di Rob Van Vijk che riporta un semplice caso di test e le sue prove. Io direi che l’utilizzo delle funzioni MAX/MIN(..) KEEP (DENSE_RANK FIRST/LAST ORDER BY ..) mi pare possa snellire la query nelle casistiche interessate rispetto all’uso della funzione analitica. Ovviamente non prendo neanche in considerazione l’approccio con il NOT EXIST che aveva senso il secolo scorso prima che fossero introdotte le funzioni analitiche

CAST e MULTISET

mercoledì 3 maggio 2017 alle 03:33 | Pubblicato su 11g, 12c, Diario, SQL | Lascia un commento

Oggi partendo da una procedura commissionatami  che doveva fare un aggiornamento dei dati sono arrivato ad approfondire un operatore che forse ho visto di sfuggita in passato ma certamente non ho usato ne’ capito, ragion per cui oggi, avendo un attimo di tempo ho deciso di dedicarmici.

Il punto di partenza è una organizzazione dei dati di cui ignoro le motivazioni alla base ma che mi sembra, in un database relazionale, poco corretta; si tratta infatti di un campo di tipo VARCHAR2 che contiene degli id numerici che fanno riferimento ad un campo di un’altra tabella separati da una virgola. Concedo il beneficio del dubbio ma non ho capito perché non si è usato una tabella di relazione come in un database relazionale si dovrebbe fare. Il fatto è che a livello di SQL fare anche una banale join per estrapolare i dati diventa una cosa assai complicata.  Per cercare di spiegarmi cerco di riportare un caso di test:

create table T1 (
 t1_id number,
 t1_desc varchar2(30)
);
create table T2 (
 t2_id number,
 t2_t1ids varchar2(90),
 t2_desc varchar2(30)
);

insert into T1 values (1,'a');
insert into T1 values (2,'b');
insert into T1 values (3,'c');
insert into T1 values (4,'d');
insert into T2 values (1,'1,2','uno');
insert into T2 values (2,'2,3,4','due');
insert into T2 values (3,'1,4','tre');
insert into T2 values (4,'4','quattro');

Ora sul campo T2.T2_T1IDS ho degli id separati da virgola in un campo di tipo stringa che dovrebbero fare riferimento al campo T1.T1_ID. In un database relazionale ci sarebbe una tabella T3 che come struttura avrebbe due campi, uno che punta a T1.T1_ID e uno che punta a T2.T2_ID, se poi voglio estrarre i dati li ricostruisco con una join sulle tre tabelle; qui non è così facile, magari a me sfugge qualcosa ma di sicuro se scrivo questo non va bene:

SVILUPPO@svil112 > select * from t2 join t1 on (t1_id in t2_t1ids);
select * from t2 join t1 on (t1_id in t2_t1ids)
 *
ERROR at line 1:
ORA-01722: invalid number

Io sono giunto alla conclusione che bisogna in qualche modo fare un parsing del campo T2.T2_T1IDS. In passato ho fatto delle procedurine per fare una cosa simile ma siccome non mi piacevano molto e mi sembravano più complesse del necessario ho pensato di fare una nuova ricerca su internet, in pratica ho cercato una funzione inversa rispetto a LISTAGG e sono arrivato a questa discussione su ORAFAQ. Qui ho trovato due interessanti soluzioni che ho deciso di approfondire e capire e questo è il motivo di questo post. La prima, applicata al mio caso di test dovrebbe essere più o meno così:

SVILUPPO@svil112 > with test as
 2 (select * from t2 where t2_desc='due' )
 3 select * from
 4 (select t2_id,regexp_substr(t2_t1ids, '[^,]+', 1, level) t1id
 5 from test
 6 connect by level <= length(regexp_replace(t2_t1ids, '[^,]+')) + 1)
 7 join t1 on t1_id=t1id;

 T2_ID T1ID T1_ID T1_DESC
---------- ---- ---------- ------------------------------
 2 2 2 b
 2 3 3 c
 2 4 4 d

Da quello che ho capito io la funzione regexp_substr(t2_t1ids, ‘[^,]+’, 1, level) prende una occorrenza di tutto ciò che non contiene al suo interno una virgola, “level” indica quale occorrenza, quindi la sottoquery restituisce un record per ogni id nel campo t2_t1ids.  Nella discussione viene fatto un intervento che spiega come quella prima soluzione non funziona nel caso di più record estratti dalla query nella sezione “WITH”, infatti:

SVILUPPO@svil112 > with test as
 2 (select * from t2 where t2_desc='due' or t2_desc='tre' )
 3 select * from
 4 (select t2_id,regexp_substr(t2_t1ids, '[^,]+', 1, level) t1id
 5 from test
 6 connect by level <= length(regexp_replace(t2_t1ids, '[^,]+')) + 1)
 7 join t1 on t1_id=t1id;

  T2_ID T1ID T1_ID T1_DESC
---------- ---- ---------- ------------------------------
 3 1 1 a
 2 2 2 b
 2 3 3 c
 2 3 3 c
 2 4 4 d
 3 4 4 d
 2 4 4 d
 2 4 4 d
 3 4 4 d
 2 4 4 d

La “connect by level<….” provoca una moltiplicazione dei record nel risultato che non va bene. Anche qui avrei da approfondire perché confesso che non ho chiarissimi alcuni dettagli del funzionamento delle query gerarchiche. Se non ho capito male al primo livello ci sono tutti i valori della tabella diciamo n, al secondo livello per ogni valore ci sono  n figli e così via; qui la cosa si complica e richiederà altri approfondimenti.

La variante suggerita per risolvere il problema nel caso più generico è un po’ più complessa ed applicata al mio caso di test dovrebbe essere così:

SVILUPPO@svil112 > WITH test AS
 2 (SELECT * FROM t2 WHERE t2_desc='due' or t2_desc='tre' )
 3 SELECT * FROM
 4 (SELECT t2_id,REGEXP_SUBSTR(t2_t1ids, '[^,]+', 1, b.column_value) t1id
 5 FROM test a CROSS JOIN
 6 TABLE
 7 (
 8 CAST
 9 (
 10 MULTISET
 11 (
 12 SELECT LEVEL FROM DUAL
 13 CONNECT BY LEVEL <= REGEXP_COUNT(a.t2_t1ids, '[^,]+')
 14 )
 15 AS SYS.odciNumberList
 16 )
 17 ) b
 18 )
 19 join t1 on t1_id=t1id;

T2_ID T1ID T1_ID T1_DESC
---------- ---- ---------- ------------------------------
 2 2 2 b
 2 3 3 c
 2 4 4 d
 3 1 1 a
 3 4 4 d

Così mi pare corretta, seppur un po’ lunga.

Nell’ultima soluzione proposta ci sono alcune cose che io non conoscevo molto bene, la prima è l’utilizzo della sintassi CROSS JOIN che serve a generare un prodotto cartesiano con la sintassi ANSI della JOIN. La seconda cosa è quello che sembra un operatore MULTISET, in realtà è parte dell’operatore CAST. Come dice la documentazione MULTISET informa Oracle di prendere il risultato della sottoquery e restituire un valore “collection”. Questa “collection” viene convertita nel tipo predefinito SYS.odciNumberList che è documentato qui. A suo volta questo tipo può essere dato in pasto all’operatore TABLE e così di fatto la lista di valori è trasformata al volo in una tabella.

Non credo e spero di dover ritornare su situazioni analoghe a questa, in ogni caso quando capiterà avrò pronto qui qualche appunto che mi aiuterà.

 

Riferimenti:

Tipi predefiniti (Database Data Cartridge Developer’s Guide 11.2) , per 12.1 qui.

OraFaq

 

P.S.

2017/05/04: credo valga la pena di riportare qui un altro post interessante che ho trovato oggi: https://stewashton.wordpress.com/2016/06/22/new-improved-in-lists/

SQL*Plus 12.2

giovedì 20 aprile 2017 alle 20:10 | Pubblicato su 12c, SQL, Varie | 1 commento
Tag:

Oggi ho provato a installare la versione 12.2 del nuovo instant client Oracle versione 12.2 sul mio pc aziendale con sistema operativo Win7. Non ci sono motivi particolari per cui ci sia la necessità di passare ad usare questa versione se non al puro scopo di testarla e tal proposito mi sono ricordato di un post di Mike Dietrich (anche perché l’ho letto ieri 🙂 ) dove si parla che una fra le novità di questa versione di SQL*Plus è la diversità di comportamento nel caricamento del file “login.sql”. Io normalmente uso sul mio pc una configurazione per cui lancio uno script .cmd che a sua volta chiama un’altro script che setta le opportune variabili d’ambiente, fra cui SQLPATH affinché punti a una directory dove trovo una serie di script di utilità (per molti dei quali vanno i ringraziamenti a Tanel Poder)  e l’utilissimo login.sql che imposta l’ambiente iniziale per SQL*Plus come garba a me. Con la nuova versione di SQL*Plus effettivamente l’esecuzione automatica di login.sql che si trova sotto il percorso puntato dalla variabile d’ambiente SQLPATH non funziona. Infatti il post di Franck Pachot cui fa riferimento Dietrich fa tutti i test dettagliati su ambiente Linux, peccato però che come scrive Dietrich alla fine del suo post:

On Windows please be aware of (credits to Tim Hall):

  • Bug 25804573SQL PLUS 12.2 NOT OBSERVING SQLPATH IN REGISTRY OR ENV VARIABLE FOR LOGIN.SQL

 

Ti pareva se su Winzoz non c’era qualche fastidio…

Sinceramente non mi è chiaro come aggirare il problema, la soluzione che ho adottato io sembra bruttina ma è l’unica che ho trovato in modo veloce: ho rinominato sqlplus.exe in sqlplus_orig.exe, ho creato un file sqlplus.cmd il cui contenuto è:

sqlplus_orig.exe %1 @login

Ora, prima ho provato a non rinominare il file .exe confidando che la logica di ricerca degli eseguibili mettesse prima i .cmd e dopo i .exe ma non sembra esssere così. La cosa che invece mi perplede abbastanza è il fatto che se metto @login.sql non funziona, @login si. Anche se invoco successivamente @login.sql non funziona, funziona se lo invoco con il percorso completo

SYSTEM@svil121 > @login.sql
SYSTEM@svil121 > @c:\oracle\conf\login.sql

PL/SQL procedure successfully completed.


Session altered.

Questa particolarità vale solo per il file login.sql, gli altri script funzionano sia che metta l’estensione  e sia che non la metta

 

 

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

Oracle 12c: top n query e default

giovedì 16 gennaio 2014 alle 16:13 | Pubblicato su 12c, SQL | 4 commenti
Tag: , ,

Questo post nasce da un lungo giro, come mi è capitato più volte in passato; ieri approfittando di un momento di calma ho dato una occhiata ai miei “feed”, strumento che ormai pare obsoleto e rimpiazzato per i più (ma non da me) da twitter che però non ritengo possa rimpiazzare come funzionalità l’accopiata blog+feed, direi anzi che twitter va verso la direzione di creare confusione e far perdere informazioni preziose… ma sto divagando. Ieri quindi ho dato una letta a qualche articolo sui blog che seguo ormai da anni e che parlano di database Oracle, in particolare leggevo gli ultimi post di Jonathan Lewis, che rappresenta ancora una preziosissima risorsa. Finito di leggere un post l’occhio mi è caduto sul blogroll che in testa riporta questo interessante link; si tratta di una pagina in cui a sua volta sono raggruppati un sacco di link a fonti di informazione sul nuovo Oracle 12c. Scorrendo i link mi sono soffermato su questo, non tanto per l’argomento quanto per l’autore che mi era un nome noto. Arrivato al post di Connor Mcdonald l’argomento mi ha incuriosito e sono andato alla sua fonte, un articolo di Tom Kyte. Come si vede un giro abbastanza lungo (almeno per il livello medio di molti utilizzatori di internet); comunque sull’articolo di Tom Kyte ho deciso di approfondire. Ho così rispolverato la macchina virtuale con Oracle 12c, mi ci è voluto un attimo per riorientarmi e trovare il container, creare un utente (e prima una tablespace dedicata), poi sono riuscito a ripetere gli esempi mostrati, prima sui Default sui valori delle colonne nelle tabelle, dove Oracle ha sicuramente colmato una lacuna, permettendo di definire come valore di default per una colonna il numero generato da una sequence, creata esplicitamente e separatamente o implicitamente e legata alla tabella con la specifica “generated as [default] identity“. Un po’ più bizzarra la funzionalità “default on NULL” che però evidentemente per molti può risultare utile. Non ho testato la miglioria sull’aggiunta di colonne con valori di default, ma sembra anche essa una grande cosa (ho passato brutti momenti in passato con l’aggiunta di colonne con valore di default a tabelle molto grandi).

Infine l’argomento “top n query“: anche questo mi è  parso interessante, finché non ho notato una differenza fra le mie prove e quanto mostrato da Tom. Ho ripetuto le prove anche su una precedente installazione di Oracle 12c in cui ho installato Enterprise Edition (mentre in ambiente virtuale ho installato una standard edition per fare prove sulla “Multitenant Architecture“) e il risultato è diverso da quello mostrato di Tom, a livello di piano di esecuzione, ecco i miei risultati:

CRISTIAN@10.110.3.52/salespdb > create table t2 as select * from all_objects;

Table created.

CRISTIAN@10.110.3.52/salespdb > create index idx_t2 on t2(owner,object_name);

Index created.

CRISTIAN@10.110.3.52/salespdb > select owner,object_name,object_id from t2
 2 order by owner,object_name fetch first 5 rows only;

OWNER OBJECT_NAME OBJECT_ID
----------------------------- ------------------------------ ----------
APEX_040200 APEX 87057
APEX_040200 APEXWS 86726
APEX_040200 APEX_ADMIN 88996
APEX_040200 APEX_APPLICATIONS 87697
APEX_040200 APEX_APPLICATION_ALL_AUTH 87765

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 3975347511

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 76285 | 21M| | 1101 (1)| 00:00:01 |
|* 1 | VIEW | | 76285 | 21M| | 1101 (1)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 76285 | 2756K| 3600K| 1101 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T2 | 76285 | 2756K| | 362 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
 2 - filter(ROW_NUMBER() OVER ( ORDER BY "OWNER","OBJECT_NAME")<=5)


Statistics
----------------------------------------------------------
 0 recursive calls
 0 db block gets
 1300 consistent gets
 0 physical reads
 0 redo size
 614 bytes sent via SQL*Net to client
 475 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 1 sorts (memory)
 0 sorts (disk)
 5 rows processed

La cosa che mi “perprime” è che con la classica tecnica del ROWNUM ho questo:


CRISTIAN@10.110.3.52/salespdb > select * from ( select owner,object_name,object_id from t2
 2 order by owner,object_name) where rownum<=5;

OWNER OBJECT_NAME OBJECT_ID
----------------------------- ------------------------------ ----------
APEX_040200 APEX 87057
APEX_040200 APEXWS 86726
APEX_040200 APEX_ADMIN 88996
APEX_040200 APEX_APPLICATIONS 87697
APEX_040200 APEX_APPLICATION_ALL_AUTH 87765

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 2015243804

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 725 | 8 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 5 | 725 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 76285 | 2756K| 8 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | IDX_T2 | 5 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

1 - filter(ROWNUM<=5)


Statistics
----------------------------------------------------------
 0 recursive calls
 0 db block gets
 9 consistent gets
 0 physical reads
 0 redo size
 614 bytes sent via SQL*Net to client
 475 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 5 rows processed

Nel mio caso non usa l’indice, mi è venuto lo scrupolo di provare a vedere le statistiche e il costo del piano usando un hint per forzare l’uso dell’indice:


CRISTIAN@10.110.3.52/salespdb > select /*+ INDEX(T2 IDX_T2) */owner,object_name,object_id from t2
 2 order by owner,object_name fetch first 5 rows only;

OWNER OBJECT_NAME OBJECT_ID
----------------------------- ------------------------------ ----------
APEX_040200 APEX 87057
APEX_040200 APEXWS 86726
APEX_040200 APEX_ADMIN 88996
APEX_040200 APEX_APPLICATIONS 87697
APEX_040200 APEX_APPLICATION_ALL_AUTH 87765

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 2588503356

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 76285 | 21M| 62581 (1)| 00:00:03 |
|* 1 | VIEW | | 76285 | 21M| 62581 (1)| 00:00:03 |
|* 2 | WINDOW NOSORT STOPKEY | | 76285 | 2756K| 62581 (1)| 00:00:03 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 76285 | 2756K| 62581 (1)| 00:00:03 |
| 4 | INDEX FULL SCAN | IDX_T2 | 76285 | | 454 (1)| 00:00:01 |
----------------------------------------------------------------------------------------

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

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
 2 - filter(ROW_NUMBER() OVER ( ORDER BY "OWNER","OBJECT_NAME")<=5)


Statistics
----------------------------------------------------------
 1 recursive calls
 0 db block gets
 9 consistent gets
 0 physical reads
 0 redo size
 614 bytes sent via SQL*Net to client
 475 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 5 rows processed

Le statistiche sono migliorate, ma il costo indicato per il  piano di esecuzione è elevato. Come spiega bene Christian Antognini sul suo libro “Troubleshooting Oracle Performance” (e come la mia esperienza conferma) piani di esecuzione non ottimali molto spesso sono generati da previsioni sbagliate, infatti (ma si intravedeva gia prima):

</pre>
CRISTIAN@10.110.3.52/salespdb > select /*+ gather_plan_statistics */ owner,object_name,object_id from t2
 2 order by owner,object_name fetch first 5 rows only;

OWNER OBJECT_NAME OBJECT_ID
----------------------------- ------------------------------ ----------
APEX_040200 APEX 87057
APEX_040200 APEXWS 86726
APEX_040200 APEX_ADMIN 88996
APEX_040200 APEX_APPLICATIONS 87697
APEX_040200 APEX_APPLICATION_ALL_AUTH 87765

Elapsed: 00:00:00.07
CRISTIAN@10.110.3.52/salespdb > select plan_table_output from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID g467gxjwprt0g, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ owner,object_name,object_id from
t2 order by owner,object_name fetch first 5 rows only

Plan hash value: 3975347511

----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.04 | 1300 | | | |
|* 1 | VIEW | | 1 | 76285 | 5 |00:00:00.04 | 1300 | | | |
|* 2 | WINDOW SORT PUSHED RANK| | 1 | 76285 | 5 |00:00:00.04 | 1300 | 4096 | 4096 | 4096 (0)|
| 3 | TABLE ACCESS FULL | T2 | 1 | 76285 | 76285 |00:00:00.13 | 1300 | | | |
----------------------------------------------------------------------------------------------------------------------

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

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
 2 - filter(ROW_NUMBER() OVER ( ORDER BY "OWNER","OBJECT_NAME")<=5)
22 rows selected.

Elapsed: 00:00:00.06
<pre>

Cioè nel mio caso oracle non si aspetta 5 righe ma tutte le 76285 presenti nella tabella, nonostante il fatto che sia scritto nella query che il risultato conterrà 5 righe. Perché a me non funziona come dovrebbe quindi non è ancora chiaro, ho provato a ricalcolare statistiche a cambiare client (oltre che server) ma non ho visto miglioramenti, un tema da indagare.

P.S.

Attenzione che sulla funzionalità FETCH..OFFSET è gia stato rilevato un baco, se ne parla qui, io ho effettuato lo stesso test riportato da Connor Mcdonald nel primo commento con lo stesso risultato.

 

 

 

Pagina successiva »

Blog su WordPress.com.
Entries e commenti feeds.