RDBMS ORACLE- Stale Statistics

Quando le statistiche di un oggetto (tabelle, partizioni o sottopartizioni) diventano STALE e quindi poco affidabili affinchè l'ottimizzatore le sfrutti?

Le statistiche diventano stale nel momento in cui dopo averle calcolate le righe all’interno della tabella, partizione o sottopartizione subiscono una movimentazione superiore al 10% del totale.

• In questo caso la tabella risulta analizzata alle 10:04 con 64 righe

SELECT OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED 

   FROM DBA_TABLES 

WHERE TABLE_NAME='STAF15_WRK';

OWNER         TABLE_NAME                       NUM_ROWS LAST_ANALYZED     

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

PIPPO         STAF15_WRK                             64 28-GEN-2015 10:04:29

• Alle 10:54 vengono effettuate delle operazioni di delete. Viene cancellato l'intero contenuto della tabella, 64 rgihe, senza effettuare la refresh delle statistiche.

SELECT TABLE_OWNER,TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP 

    FROM DBA_TAB_MODIFICATIONS 

WHERE TABLE_NAME ='STAF15_WRK' AND TABLE_OWNER='PIPPO';

TABLE_OWNER  TABLE_NAME     INSERTS    UPDATES    DELETES     TIMESTAMP         

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

PIPPO           STAF15_WRK          0           0         64 28-GEN-2015 10:54:58

•  Oracle a questo punto, poiché abbiamo che la movimentazione di righe in questo caso è pari al 100%  marca le statistiche della tabella come STALE. 
• ROUND ( (DTM.DELETES + DTM.UPDATES + DTM.INSERTS)/DT.NUM_ROWS*100)>= 10   

SELECT OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED,STALE_STATS  

    FROM DBA_TAB_STATISTICS 

WHERE TABLE_NAME='STAF15_WRK';

OWNER       TABLE_NAME    NUM_ROWS   LAST_ANALYZED        STALE_STATS

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

PIPPO         STAF15_WRK         64 28-GEN-2015 10:04:29 YES        

Da documentazione 

http://docs.oracle.com/cd/B28359_01/server.111/b28274/stats.htm#i41282

viene indicato che in questi casi I piani di accesso potrebbero essere “poor”:

“Automatic optimizer statistics collection eliminates many of the manual tasks associated with managing the query optimizer, and significantly reduces the risks of generating poor execution plans due to missing or stale statistics.”

A questo punto l'unica soluzione è la rigenerazione delle statistiche sull'oggetto STALE.

OWM - Creare e gestire Trigger

Il prodotto OWM utilizza di per se dei trigger instead off per la gestione del versionamento delle tabelle oracle. Nel momento in cui l'utente decide di utilizzare trigger sulle tabelle versionate per operazioni proprie occorre definire bene come crearli.
Da documentazione abbiamo quanto segue:

"Triggers on Version-Enabled Tables
Version-enabled tables can have triggers defined; however, the following considerations and restrictions apply:
■ Only per-row triggers are supported. Per-statement triggers are not supported.
■ The only call-out supported is to PL/SQL procedures. That is, the action_type  must be PL/SQL.
Any triggers that are not supported for version-enabled tables are deactivated when
versioning is enabled, and are activated when versioning is disabled. "

Una volta che un trigger viene creato su una tabella versionata, questo diventa parte integrante dei trigger di prodotto utilizzati per la gestione del versionamento, quindi non li troviamo più sulle tabelle di sistema ALL_TRIGGERS ma bensi su quelle del prodotto WMSYS.ALL_WM_TAB_TRIGGERS o WMSYS.WM$UDTRIG_INFO.

Adesso nasce una domanda, come si creano i trigger su una tabella versionata?
A questa domanda vi sono due risposte:

1. Il trigger si crea prima di versionare la tabella, in tal modo nel momento in cui versioniamo la tabella questo viene inglobato e gestito dal prodotto.
2. Il trigger viene creato dopo che abbiamo versionato la tabella e quindi occorre creare il trigger utilizzando la corretta procedura.

 Di seguito un esempio di come creare un trigger sopo che la tabella è stata versionata.

 begin
  DBMS_WM.BeginDDL('PIPPO.ARCHIVE');
end ;

CREATE OR REPLACE TRIGGER PIPPO.TRG_ARCHIVE_UPD_CUBEID AFTER
  INSERT ON "PIPPO"."ARCHIVE_LTS" REFERENCING NEW AS NEW FOR EACH ROW BEGIN
  UPDATE PIPPO.ARCHIVE
  SET CUBEID         =:NEW.LOCALID
  WHERE OBJECTTYPE   =:NEW.OBJECTTYPE
  AND COMMUNITYID    =:NEW.COMMUNITYID
  AND CONTEXTID      =:NEW.CONTEXTID
  AND ARCHIVECONSTRID=:NEW.ARCHIVECONSTRID
  AND ARCHIVEID      =:NEW.ARCHIVEID
  AND LOCALID        =:NEW.LOCALID
  AND (CUBEID IS NULL OR CUBEID <> :NEW.LOCALID);
END ;

begin
  DBMS_WM.CommitDDL('PIPPO.ARCHIVE');
end ;








Di seguito alcune query da eseguire per verificare la corretta creazione del trigger.

select trigger_name,trigger_type,triggering_event,table_name 
  from all_triggers 
where trigger_name = 'TRG_ARCHIVE_UPD_CUBEID';

select * 
  from ALL_WM_TAB_TRIGGERS 
where trigger_name = 'TRG_ARCHIVE_UPD_CUBEID';

select * 
  from WMSYS.WM$UDTRIG_INFO 
where trigger_name = 'TRG_ARCHIVE_UPD_CUBEID';

Poichè il versionamento delle tabelle si basa sulla pk di ogni tabella, a cui viene aggiunto il campo VERSION, diventa impossibile effettuare un update su un campo che appartiene alla chiave. Per poter modificare un campo della chiave occorre cancellare la vecchia chiave ed inserire la nuova, in tal modo si ha un nuovo record ed una nuova storia.
Come fare questo senza dover ogni volta effettuare una delete/insert, si può pensare di creare un trigger di INSTEAD OF UPDATE, in cui l'operazione di update viene sostituita da una delete/insert. Questo tipo di trigger si possono creare solo sulle viste e nel caso di tabelle versionate sulla vista che ha lo stesso nome della tabella che abbiamo versionato. Dove sta il problema? Che nel caso in cui vengano fatte operazione di tipo BEGINDDL ecc.. poichè queste ricreano le viste di prodotto il trigger si perde e và ricostruito ogni volta.
A questo punto se farlo o  non farlo lo lascio a voi decidere.... di seguito un esempio di come creare questo trigger:

CREATE OR REPLACE TRIGGER PIPPO.TRIG_ARCHIVE_UPD
INSTEAD OF UPDATE ON PIPPO.ARCHIVE REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
                DELETE FROM PIPPO.ARCHIVE
                WHERE PK=OLD.<PK>;
                
IF(SQL % ROWCOUNT > 0) THEN
                               
INSERT INTO PIPPO.ARCHIVE
   (<CAMPI>)
  VALUES
  (:NEW.<CAMPI>);
                
END IF;
END;

OWM RAC - SQL Sessions Locks

Di seguito una select per individuare le sessioni oracle che stanno effettuando operazioni legate ad OWM e che creano sei lock.
Si presuppone l'utilizzo di una RAC.

 SELECT 

    sut.username, sut.workspace, sut.sid,
    DECODE(t.ses_addr, NULL, 'INACTIVE','ACTIVE') status,
    sut.inst_id,sut.saddr ses_addr
  FROM(SELECT 

        st.username,wt.workspace, st.sid,st.saddr,st.inst_id
       FROM gv$lock dl, wmsys.wm$workspaces_table wt, gv$session st
       WHERE dl.type  = 'UL'  and dl.id1 - 1 = wt.workspace_lock_id
            and dl.sid     = st.sid) sut, gv$transaction t
  WHERE sut.saddr = t.ses_addr (+);

Per individuare il comando in esecuzione basta aggiungere una join con la gv$sqlarea effettuando una decode del campo COMMAND.

ODI - Scripting Groovy ODI SDK

RDBMS ORACLE - VPD - BIP Virtual Private Database + Oracle Business Intelligence Publisher

Introduzione

Scopo e’ descrivere una possibile implementazione di VPD da associare a BIP per ottenere un taglio dei dati da riportare in output sul report.

Requisito di security

Quello che è stato richiesto è di avere un unico report che possa servire diversi utenti, ma a seconda di quale utente apre il report questi  recupera automaticamente un adeguato set di dati per l'utente e li visualizza all'interno del report generato.

Viene quindi richiesto un taglio dei dati all’origine in modo da visualizzare per ogni utenti i soli i dati ad esso consentito visualizzare.

Una possibile soluzione potrebbe essere la seguente[1]:

Al fine di estrarre i soli dati di interesse si potrebbe usufruire di una delle funzionalità di database di Oracle, Virtual Private Database (VPD), che offre un livello di sicurezza sui dati nel database.

Enterprise BI Publisher supporta un meccanismo di autenticazione proxy, con la quale passa le informazioni dell'utente sessione fino al livello di database e si avvale della politica di sicurezza a livello di riga che è implementato a livello di database per restituire un insieme di dati appropriato.

L’utilizzo del VPD comporta lo sviluppo di una componente pl-sql a livello di database ed alla creazione di strutture di metadati che ne permettano di costruire un predicato da associare alle query che richiamano la tabella o vista posta sotto VPD.

Per la creazione delle funzioni VPD si rimanda ai manuali oracle.

Lato BIP occorre attivare l’autenticazione a livello di proxy in modo da settare le variabili di contesto da utilizzare all’interno delle funzioni di policy VPD o attivare eventualmente dei trigger jdbc che effettuano la valorizzazione delle stesse variabili di contesto fornite in automatico in fase di connessione al database.

Il meccanismo di autenticazione proxy di supporto è progettato per funzionare con qualsiasi sorgente dati tramite JDBC o una connessione JNDI.  

Su come implementare tale policy o attivare l’autenticazione da Proxy si rimanda alla manualistica del prodotto.

·         Database Security Guide 10g or the Oracle Database Security Guide 11g

·         Administrator's Guide for Oracle Business Intelligence Publisher Release 11g (11.1.1)

·         Oracle® Fusion Middleware Administrator's Guide for Oracle Business Intelligence Publisher 11g Release 1 (11.1.1)
Part Number E22255-02

Definizione Virtual Private Database (VPD)

L’implementazione di policy di VPD consentono a più utenti di accedere ad un medesimo schema vedendo ognuno solo la porzione di dati di loro interesse. L’implementazione di un VPD permette di centralizzare il controllo degli accessi, alle tabelle dati, tramite l’implementazione di regole di security evitando in questo modo un accesso non controllato alla base dati.

Il VPD permette l’implementazione di row-level security (RLS) e Fine Grained Access Control (FGAC) su tabelle e viste. L’idea alla base del FGAC è l’implementazione di un meccanismo di controllo degli accessi a livello di riga per una sessione utente in contrapposizione a un accesso concesso da grant e roles di Oracle. L’implementazione del VDP permette ad un utente l’accesso ad una tabella o vista limitandone il retrieve dei dati solo a righe alle quali sono associate particolari grant. I dati possono essere acceduti effettuando o un taglio orizzontale sulla tabella o un taglio verticale e quindi accedendo solo ad alcune colonne. A seconda degli attributi associati all’utente, a meno che l’utente non sia sys o con particolari privilegi di accesso, l’RDBMS riscrive automaticamente ogni SQL aggiungendo un predicato restituito da una policy function. Quindi Oracle raccoglie le informazioni di contesto di un’applicazione in fase di accesso utente e quindi chiama una policy function che restituisce un predicato. Un predicato è una clausola di where che qualifica un particolare set di righe all'interno della tabella.

Un modello di sicurezza VPD utilizza il pacchetto dbms_rls di Oracle (RLS sta per protezione a livello di riga) per implementare le politiche di sicurezza e contesti di applicazione. Ciò richiede  quindi di implementare e progettare una serie di regole da applicare alla visualizzazione dei dati, che verranno esplicitate nelle query con l’aggiunta in automatico di un predicato.

Di seguito un esempio di policy invocation:

---

[1] Resta ben inteso che occorre effettuare delle prove che ne diano una certezza di applicazione, in quanto la soluzione indicata viene distribuita su più livelli e prodotti Oracle.

Il VPD risulta essere un modo totalmente diverso di gestire gli accessi di Oracle rispetto a quanto basato sui meccanismi di sicurezza tradizionali (grants e roles). Possono essere individuati i seguenti benefici espressi dall’utilizzo del VPD:

·         Protezione dinamica — non c'è bisogno di mantenere i ruoli complessi e grants.

·         Multiple Security — possibilità di fornire differenti politiche di sicurezza su un oggetto. Questo rende VPD perfetto per le applicazioni Web che sono distribuite per molte aziende.

·         Nessuna back doors — gli utenti non possono ignorare/bypassare i criteri di sicurezza incorporati nelle applicazioni in quanto la politica di sicurezza è associata ai dati.

·         Possono essere definite regole di accesso complesse — con VPD, è possibile utilizzare i valori dei dati per specificare regole di accesso complesse che sarebbero stato difficile creare con concessione di sicurezza. Si può facilmente limitare l'accesso alle righe.

Tuttavia esistono anche evidenti inconvenienti nell’utilizzo del VPD:

·         Protezione a livello di colonna difficile — perché l'accesso è controllato con l'aggiunta di un clausole di where, l’accesso a livello di colonna può essere mantenuto solo mediante la definizione di viste multiple per ogni classe di utente finale.

·         Richiede Oracle ID per ogni utente — a differenza di sicurezza gestita esternamente, VPD richiede che un ID utente Oracle deve essere definito per ogni persona che si connette al database. Questo aggiunge il sovraccarico e la manutenzione. 

Applicazione di Secutiry Policies alle tabelle

Una volta definito il package di policy occorre associare alle singole tabelle la funzione di policy corretta, per fare questo si utilizza il package DBMS_RLS utilizzando le procedure:

·              dbms_rls.add_policy

·              dbms_rls.drop_policy

·              dbms_rls.enable_policy

·              dbms_rls.refresh_policy

 

View	Description
ALL_POLICIES	Describes all Oracle Virtual Private Database security policies for objects accessible to the current user.
ALL_POLICY_CONTEXTS	Describes the driving contexts defined for the synonyms, tables, and views accessible to the current user. A driving context is an application context used in an Oracle Virtual Private Database policy.
ALL_POLICY_GROUPS	Describes the Oracle Virtual Private Database policy groups defined for the synonyms, tables, and views accessible to the current user
ALL_SEC_RELEVANT_COLS	Describes the security relevant columns of the security policies for the tables and views accessible to the current user

  Tabella 1 Data Dictionary View