Oracle 9I : Évolutions et Optimisations

SheikYerbouti

Synthèse des principales évolutions et optimisations de la version 9i du noyau Oracle. ♪

- Titre : Oracle 9I : Évolutions et Optimisations

- Auteur : SheikYerbouti

- Parution : 5 janvier 2004

- Licence : Copyright ® 2004 SheikYerbouti. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. Paramètres d'initialisation

- II. Gestion mémoire

- II-A. Mémoire PGA

- II-A-1. Nouveaux paramètres dynamiques

- II-A-2. Statistiques supplémentaires dans :

- II-B. Mémoire SGA dynamique

- II-C. Statistiques

- III. Gestion des fichiers de données et des tablespaces

- III-A. OMF : Oracle Managed Files

- III-B. Tablespace temporaire par défaut

- III-C. Gestion des segments d'annulation

- III-D. Support de taille multiple de bloc

- III-E. Gestion automatique d'espace libre dans les tablespaces gérés localement

- IV. Gestion des transactions récupérables (RESUMABLE)

- V. Améliorations de Database Resource Manager

- V-A. Active Session Pool

- V-B. Quota d'undo

- VI. Améliorations de Enterprise Manager

- VII. Amélioration des performances

- VII-A. Optimiseur CBO

- VII-B. Package DBMS_STATS

- VII-C. Nouveaux indicateurs d'optimisation (hints) et modes pour l'optimiseur

- VII-D. Édition/modification des plans d'exécution stockés

- VII-E. Optimisation du partage des curseurs

- VII-F. Index

- VIII. Améliorations du partitionnement

- VIII-A. Partitions sur liste

- VIII-B. Maintien des Index globaux

- VIII-C. Chargement direct en parallèle

- IX. Améliorations pour le DataWareHouse

- X. Migration et mise à niveau de la base de données

- XI. Oracle 9i : Conforme à la norme ISO SQL 1999

- XI-A. Jointures

- XI-A-1. Produit cartésien de deux tables (Cross Joins)

- XI-A-2. Jointures « Naturelles »

- XI-A-3. Équijointures

- XI-A-4. Jointures avec clause « ON »

- XI-A-5. Jointures externes

- XI-B. Expressions de type CASE

- XI-B-1. Fonction EXTRACT

- XI-B-2. CASE simple : similaire au DECODE

- XI-B-3. CASE de recherche

- XI-B-4. NULLIF

- XI-B-5. COALESCE

- XI-C. Sous-requêtes scalaires

- XI-D. Groupes d'enregistrements

- XI-D-1. GROUPING SETS

- XI-D-2. Colonnes composites

- XII. Améliorations du SQL, PL/SQL et nouveaux types de données

- XII-A. Nouveaux Types

- XII-B. Support de l'Unicode

- XII-C. Compilation du PL/SQL en mode natif

- XII-D. Amélioration niveau Objet

- XIII. Haute disponibilité

- XIII-A. Restauration

- XIII-B. Index

- XIII-C. Tables

- XIII-D. Analyse Validate Structure : Exécutable même pendant l'accès intensif de la table

- XIII-E. Oracle Flashback

- XIV. Real Application Clusters(9i) (Parallel Server en 8i)

- XV. Logminer : Améliorations

- XVI. Oracle 9i Data Guard (StandBy Database en 8i)

- XVII. Remerciements

I - Paramètres d'initialisation

SPFile : Nouveau fichier Binaire d'initialisation géré par Oracle.

- Toutes les modifications par ALTER SYSTEM deviennent permanentes.

- Paramètres d'initialisation dynamiques grâce au SPfile.

- S'il existe, est prioritaire par rapport au PFile (Pfile devient facultatif).

- Se trouve par défaut sous %ORACLE_HOME%/database (NT)
$ORACLE_HOME%/dbs (Unix).

- À comme nom spfile<sid>.ora et ne peut pas être mentionné dans la commande Startup (au contraire du Pfile).

- Est créé à partir du Pfile (nécessite les privilèges Sysdba ou Sysoper) avec :

- CREATE SPFILE = ‘NomDuSpfile'

FROM PFILE = ‘NomPfile'

On peut le créer avec les noms par défaut avec :

CREATE SPFILE FROM PFILE

- Est modifiable par la commande :

ALTER SYSTEM SET paramètre = valeur
[COMMENT='moncommentaire']
[SCOPE = MEMORY | SPFILE |BOTH]
[DEFERRED]

Où :

- MEMORY : en mémoire uniquement (par défaut comme la 8i) ;

- SPFILE : uniquement dans le SPFILE ;

- BOTH : en mémoire et dans le SPFILE ;

- DEFERRED : pour les paramètres dynamiques, les active pour les sessions futures (pas celles en cours) ;

- COMMENT : commentaire associé au paramètre de longueur maximale de 255 caractères ;

- Son contenu est montré par la vue V$SPPARAMETER ;

- Est exportable vers un Pfile (nécessite les privilèges Sysdba ou Sysoper) avec :

CREATE PFILE = ‘NomDuPfile' FROM SPFILE = ‘NomDuSpfile'

- Peut être partagé entre plusieurs instances d'un parallel server (Real Application Cluster en 9i).

II - Gestion mémoire

II-A - Mémoire PGA

II-A-1 - Nouveaux paramètres dynamiques

- PGA_AGGREGATE_TARGET
Représente le total PGA de toutes les sessions ouvertes à atteindre.

- WORKAREA_SIZE_POLICY (MANUAL OU AUTO)
Valeur par défaut à MANUAL si PGA_AGGREGATE_TARGET n'est pas défini (le tuning mémoire est alors géré par les *_AREA_SIZE).

- Taille totale de la PGA < PGA_AGGREGATE_TARGET en mode auto.

II-A-2 - Statistiques supplémentaires dans :

- a - V$SYSSTAT et V$SESSTAT :
work area memory allocate
work area executions - optimal size
work area executions - one pass size
work area executions - multipass size

- b - V$PROCESS
PGA_USED_MEM
PGA_ALLOC_MEM
PGA_MAX_MEM

II-B - Mémoire SGA dynamique

- Sa taille peut changer dynamiquement pendant le fonctionnement d'une instance (par l'intermédiaire du Buffer Cache et de la Shared Pool Size).

- Nouvelle unité d'allocation : le Granule.
C'est une unité d'allocation mémoire contiguë et qui dépend de la taille de la SGA :
=> Si SGA_MAX_SIZE < 128 Mo, taille granule = 4Mo ;
=> Si SGA_MAX_SIZE >= 128 Mo, taille granule = 16Mo ;
=> taille de la SGA exprimée en granules et en contient au moins trois (Fixed SGA (dont Redo Buffer), buffer Cache, Share Pool).

- L'allocation des granules est montrée par la vue V$BUFFER_POOL.

- La Shared Pool peut être retaillée dynamiquement avec la commande :

ALTER SYSTEM
SET SHARED_POOL_SIZE = 128 M;

Cette taille doit être un multiple du granule et doit être inférieure à SGA_MAX_SIZE.

Le Buffer Cache peut être retaillé dynamiquement avec la commande :

ALTER SYSTEM
SET DB_CACHE_SIZE= 64 M;

Cette taille doit être un multiple du granule et doit être inférieure à
SGA_MAX_SIZE. Elle doit être différente de 0.

=> db_block_buffer inutilisé en 9i

Nouveaux paramètres : DB_CACHE_SIZE, DB_KEEP_CACH_SIZE, et DB_RECYCLE_CACHE_SIZE.
Paramètres obsolètes et bientôt supprimés : DB_BLOCK_BUFFER, BUFFER_POOL_KEEP et BUFFER_POOL_RECYCLE.

II-C - Statistiques

Statistiques qui prédisent le comportement de la base lorsque l'on modifie la taille du cache. Est activé en utilisant la commande ALTER SYSTEM sur le paramètre DB_CACHE_ADVICE (ON, OFF ou READY). Les résultats sont consultables à l'aide de la vue V$DB_CACHE_ADVICE.

N. B. Les paramètres LOG_BUFFER, LARGE_POOL_SIZE et JAVA_POOL_SIZE restent statiques en 9i.

III - Gestion des fichiers de données et des tablespaces

III-A - OMF : Oracle Managed Files

Fichier géré par le SGBD, sert pour les control files, les datafiles et les redo logs :

- sert principalement pour les bases de test ;

- crée un nom unique pour chaque fichier et se charge de les supprimer automatiquement en cas de suppression logique ;

- les répertoires physiques sont gérés par deux paramètres dynamiques :

- DB_CREATE_FILE_DEST : utilisé pour les Data Files(Fichiers de données),

- DB_CREATE_ONLINE_LOG_DEST_n (1 <= n <= 5) : utilisé pour les controls files et les redo logs.

Ex. : la commande suivante peut être lancée pour définir le premier paramètre :

ALTER SYSTEM SET db_create_file_dest = ‘/oracle/dbs/files'

Si seul le premier paramètre est positionné, alors tous les fichiers (control, données et redo) seront localisés dans le même répertoire.

Nommage des fichiers gérés par OMF (voir note Métalink n° 159888.1) :
Oracle Version 9.0.1.1 : ora_<nomdutablespace>_8caracteresaleatoires.dbf (même chose pour .log) ;
Oracle Version 9.0.1.2 : o1_mf_<nomdutablespace>_8caracteresaleatoires_.dbf (même chose pour .log) ;

OMF reconnaît ses fichiers par le préfixe et l'extension, la syntaxe a changé à partir de la 9.0.1.2, car « ora_ » était beaucoup utilisé avant OMF.

Pour créer des control files OMF :

- il faut définir un ou les deux paramètres dynamiques ;

- ne pas spécifier le paramètre CONTROL_FILES lors de la création de la base :

- un control file est créé dans chacun des DB_CREATE_ONLINE_LOG_DEST_n,

- ou dans DB_CREATE_FILE_DEST sinon,

- les fichiers sont affichés dans le fichier d'alerte ;

- si SPFILE est défini, les control files générés sont automatiquement inclus dans ce fichier binaire.

Pour créer des redo logs OMF :

1. Définir les paramètres DB_CREATE_ONLINE_LOG_DEST, les groupes seront créés dans chacun des DB_CREATE_ONLINE_LOG_DEST_1 et DB_CREATE_ONLINE_LOG_DEST_2 ;

2. Chaque groupe aura deux membres ;

3. Chaque fichier a comme taille par défaut : 100 Mo.

Pour ajouter un groupe :

ALTER DATABASE ADD LOGFILE ;

Pour supprimer un groupe :

LTER DATABASE DROP LOGFILE GROUP 3 ;

Les fichiers journaux archivés ne peuvent être de type OMF.
Pour créer des tablespaces de type OMF :

- DB_CREATE_FILE_DEST doit être défini ;

- utiliser la syntaxe :

CREATE TABLESPACE omf_2 ;

- le fichier de données associé est créé dans DB_CREATE_FILE_DEST ;

- la clause DATAFILE n'est pas obligatoire ;

- la taille par défaut est de 100 Mo ;

- le tablespace est créé en AUTOEXTENT UNLIMITED ;

- à la suppression du tablespace, le fichier associé est supprimé automatiquement.

On peut lancer aussi la commande :

CREATE TABLESPACE omf_2
DATAFILE SIZE 500M ;

N. B. Les tablespaces sont créés automatiquement en EXTENT MANAGEMENT LOCAL en 9i. Pour utiliser le dictionnaire de données, il faut spécifier EXTENT MANAGEMENT DICTIONARY.

Pour ajouter un fichier de données à un tablespace OMF :

ALTER TABLESPACE TBS1 ADD DATAFILE;

Pour supprimer un tablespace et ses fichiers de données non OMF :

DROP TABLESPACE TBS1
INCLUDING CONTENTS AND DATAFILES;

III-B - Tablespace temporaire par défaut

En 9i, possibilité de définir un tablespace temporaire par défaut autre que SYSTEM.

1. À la création de la base avec la clause :

DEFAULT TEMPORARY TABLESPACE tmp1
TEMPFILE '/oracle/dbs/temp01.dbf' SIZE 100M

2. Avec la commande :

ALTER DATABASE db1
DEFAULT TEMPORARY TABLESPACE tmp1.

III-C - Gestion des segments d'annulation

Nouveau paramètre d'initialisation : UNDO_MANAGEMENT
Deux valeurs :

- AUTO : géré par l'instance (sur un seul tablespace de type UNDO_TABLESPACE).
nécessite d'avoir les paramètres suivants :

- UNDO_MANAGEMENT = AUTO,

- UNDO_TABLESPACE = UNDO1.

(Le fonctionnement des rollbacks segments de SYSTEM est identique aux versions antérieures et un rollback segment est créé automatiquement dans le tablespace SYSTEM.) ;

- MANUAL : géré par le DBA. (Identique à la gestion des rollbacks segments avant la 9i.)
Nécessite d'avoir les paramètres suivants :

- UNDO_MANAGEMENT=MANUAL ;

- CREATE TABLESPACE RBS ;

- CREATE ROLLBACK SEGMENT rbs1 STORAGE (…) ;

- ROLLBACK_SEGMENTS= (rbs1,rbs2).

Création d'un undo tablespace :

- à la création de la base, avec la clause supplémentaire :

UNDO TABLESPACE undotbs1
DATAFILE '/oracle/dbs/undotbs01.dbf' SIZE 50M

(si au démarrage d'une instance, UNDO_MANAGEMENT=AUTO et qu'il n'y a pas de clause UNDO_TABLESPACE, il en crée un par défaut, de nom SYS_UNDOTBS, en AUTOEXTEND ON, et dans un fichier de 10 Mo).

- après la création de la base avec :

CREATE UNDO TABLESPACE
DATAFILE '/oracle/dbs/undotbs01.dbf' SIZE 50M

(Ce tablespace ne peut servir que pour stocker les données d'annulation).

Modification d'un undo tablespace :

ALTER TABLESPACE undotbs1
ADD DATAFILE …
RENAME …
DATAFILE [ONLINE, OFFLINE]
BEGIN BACKUP
END BACKUP

Suppression d'un undo tablespace :

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

le undo tablespace utilisé (un à la fois) peut être modifié à l'aide de la commande :

ALTER SYSTEM
SET UNDO_TABLESPACE=tbsundo2;

En résumé, la gestion des segments d'annulation peut se faire avec les nouveaux paramètres :

- UNDO_MANAGEMENT : MANUAL ou AUTO ;

- UNDO_TABLESPACE : Nom du tablespace d'annulation ;

- UNDO_SUPPRESS_ERRORS : TRUE OU FALSE (par défaut).
=> si positionné à TRUE, supprime les messages d'erreurs affichés, quand on est en mode AUTO et qu'une opération manuelle est exécutée (telle que ALTER ROLLBACK SEGMENT ou ALTER SESSION SET TRANSACTION USE ROLLBACK SEGMENT) ;

- UNDO_RETENTION: Durée (par défaut 30 s) pendant laquelle la donnée modifiée est gardée= Flashback.

Les informations d'utilisation des segments UNDO (auto ou manuel) sont stockées dans la vue V$UNDOSTAT.

III-D - Support de taille multiple de bloc

Le DB_BLOCK_SIZE utilisé à la création de la base (et servant pour la création du tablespace SYSTEM), ne peut être changé sans recréer la base et sert comme taille par défaut lors de la création d'un tablespace.
Néanmoins, pour chaque tablespace créé manuellement, la taille du bloc est maintenant paramétrable :

CREATE TABLESPACE TBS1
DATAFILE '/oracle/dbs/tbs01.dbf' SIZE 10 M
BLOCKSIZE 4096;

(2 Ko <= BLOCKSIZE <= 32Ko).

N. B.

- Toutes les partitions d'un objet partitionné doivent être réparties dans des tablespaces de tailles de bloc identiques.

- Tous les tablespaces temporaires doivent utiliser la taille de bloc standard.

Pour gérer ces tailles différentes de bloc, des paramètres (dynamiques) de taille de buffer doivent être configurés :

- DB_CACHE_SIZE : taille réservée aux blocs standards de la base ;

- DB_nK_CACHE_SIZE : Taille allouée aux différents blocs non standards, par défaut à 0 (n=2, 4, 8,16 et 32), excluant celle des blocs standard.

Uniquement pour les blocs standard, deux paramètres peuvent être utilisés (ils ne sont pas inclus dans DB_CACHE_SIZE) :

- DB_KEEP_CACHE_SIZE (au lieu de BUFFER_POOL_KEEP auparavant) : garde les tables en mémoire plus longtemps ;

- DB_RECYCLE_CACHE_SIZE (au lieu de BUFFER_POOL_RECYCLE auparavant) : inverse du précédent.

III-E - Gestion automatique d'espace libre dans les tablespaces gérés localement

Espaces libres et occupés sont décrits dans des bitmaps, et permet de savoir combien de blocs sont libres dans chaque segment.
Stockés dans des bitmapped blocs (BMB).
Permet d'améliorer le processus d'insertion.

Un tablespace géré avec des bitmaps doit être de la forme :

CREATE TABLESPACE tbs1
DATAFILE '/oracle/dbs/tbs1.dbf'
SIZE 10M EXTENT MANAGEMENT LOCAL SEGMENT (tablespace géré localement).
SPACE MANAGEMENT AUTO. (Gestion automatique).

Les vues DBA_TABLESPACES, DBA_TABLES et DBA_SEGMENTS ont été modifiées pour stocker ces nouvelles informations.

IV - Gestion des transactions récupérables (RESUMABLE)

Permet de suspendre une opération qui est en train de planter par manque d'espace et de la reprendre une fois la correction apportée (sans message d'erreur affiché).
Erreurs récupérables du type: ORA-1653: Unable to extend table … by … in tablespace …
Liste complète :
ORA-1562, 1628, 1630, 1631, 1632, 1650, 1652, 1653, 1654, 1655, 1656, 1683, 1684, 1685, 1688, 1691, 1692, 1693, 1694, 3233, 3234, 3235, 3236, 3238, 3239 et 1536.

Activation du mode récupérable :

ALTER SESSION ENABLE RESUMABLE
NAME 'update ma table' TIMEOUT 3600; (par défaut à 2h)

Désactivation du mode récupérable :

ALTER SESSION DISABLE RESUMABLE;

Pour pouvoir effectuer ces opérations, il faut posséder le privilège système : RESUMABLE.
Pour modifier le délai d'expiration (timeout), on peut utiliser :

ALTER SESSION ENABLE RESUMABLE TIMEOUT montimeout;

- Utiliser le package DBMS_RESUMABLE :

- DBMS_RESUMABLE.Set_Timeout (montimeout) => ma session en cours ;

- DBMS_RESUMABLE.Set_Session_Timeout (Session_Id, montimeout) => n'importe quelle session en cours.

Pour modifier le timeout, on peut utiliser :

ALTER SESSION ENABLE RESUMABLE NAME 'monnom';

Quand une opération de type récupérable tombe en erreur par manque de place, seul le fichier ALERT trace l'erreur.
Les opérations suivantes sont récupérables :

- requêtes de type Select ;

- ordres DML ;

- ordres DDL ;

- import et export ;

- SQL*Loader.

Les sessions « Récupérées » peut être tracées en utilisant un trigger de type : AFTER SUSPEND ON DATABASE.
Deux vues ont été créées pour suivre les opérations récupérables en cours (pas de persistance des infos) :

- USER_RESUMABLE ;

- DBA_RESUMABLE.

V - Améliorations de Database Resource Manager

V-A - Active Session Pool

Définit le nombre de sessions simultanées actives par groupe de consommateurs (de ressources).
=> géré par plusieurs paramètres d'initialisation (positionnés par défaut à UNLIMITED) :

- ACTIVE_SESSION_POOL_P1 : nombre de sessions actives qui établissent le seuil d'un groupe de consommateurs ;

- QUEUEING_P1 : temps d'attente maximum (en s) pour une session en file d'attente, après cette durée, la session est annulée avec une erreur.

Temps maximum d'exécution estimé (par défaut à UNLIMITED) :

- MAX_ESTIMATED_EXEC_TIME : temps maximal estimé autorisé pour une transaction avant son exécution. Si la transaction dépasse ce seuil, elle n'est pas exécutée.

Changement automatique de groupe de consommateurs :
Trois directives sont possibles (par défaut, les deux premières sont à UNLIMITED, le 3e à FALSE) :

- SWITCH_GROUP : groupe sur lequel on a commuté ;

- SWITCH_TIME : temps (en s) durant laquelle une session doit être exécutée avant de changer de groupe ;

- SWITCH_ESTIMATE : Si positionné à TRUE, le temps d'exécution est utilisé pour décider quand une opération va être switchée, et ce, peut-être avant qu'elle commence.

Si les deux derniers paramètres sont positionnés, la session est commutée automatiquement.

V-B - Quota d'undo

Nouveau paramètre de directive de plan de ressource :

- UNDO_POOL (Kb), par défaut à UNLIMITED, restreint les opérations de type DML, et correspond au quota d'annulation par groupe de consommateurs (après ce quota, aucun ordre DML n'est autorisé).

Est créé ou modifié par les procédures suivantes :

DBMS_RESOURCE_MANAGER.Create_Plan_Directive(PLAN => 'Mon_Plan',
GROUP_OR_SUBPLAN => 'support',
COMMENT =>  'OLTP sessions',
UNDO_POOL => 10000);

et

DBMS_RESOURCE_MANAGER.Create_Plan_Directive(PLAN => 'Mon_Plan',
GROUP_OR_SUBPLAN => 'support',
UNDO_POOL => 25000);

Les vues suivantes ont été adaptées : V$SESSION, V$MYSESSION, V$RSRC_CONSUMER_GROUP et DBA_RSRC_PLANS.

VI - Améliorations de Enterprise Manager

OEM est géré par Entreprise Manager Console (= fusion de la console et de DBA Studio dans Oracle 8i).
Il peut se lancer en deux modes :

- Autonome (Standalone) : connexion directe aux bases Oracle ;

- OMS : Oracle Management Server(sysman/oracle), basé sur un référentiel (repository) propre et qui se connecte aux bases via un agent (service sous NT) installé sur chacune d'elles.

En mode autonome, certaines fonctionnalités nécessitent l'installation d'un référentiel (en local), indépendant d'un OMS.
Nouvelle entité en 9i : Management Region = Groupe de nœuds (bases) et de serveurs d'administration (management server)(OMS) propre à une région géographique.

OEM gère via l'interface graphique toutes les nouvelles fonctionnalités de 9i décrites dans les chapitres précédents :

- SPFILE ;

- UNDO MANAGEMENT (gestion des segments d'annulation automatique) ;

- jeu de caractères (Character set) UNICODE ;

- gestion dynamique de la mémoire ;

- tablespace temporaire par défaut ;

- différentes tailles de blocs ;

- temps moyen de récupération de la base (MTTR: Mean Time to Recovery) ;

- améliorations des sauvegardes et récupérations (géré par RMAN) ;

- « Advanced Queuing ».

OEM comporte une vue graphique de la vue dynamique V$DB_CACHE_ADVICE
=> permet de déterminer quelle taille de cache est optimale afin de diminuer les lectures physiques sur le disque.

Autres nouvelles fonctionnalités :

- Génération de rapports en HTML

- => sur la configuration complète de la base,

- => sur les propriétés d'un objet sélectionné dans l'arbre graphique de la console.

Amélioration de la gestion des événements (events)(nécessite l'agent Oracle9i).

VII - Amélioration des performances

VII-A - Optimiseur CBO

- Ajout de trois colonnes dans la table PLAN_TABLE :

- cpu_cost : cout CPU estimé de l'opération ;

- io_cost : cout accès disque estimé de l'opération ;

- temp_space : espace temporaire estimé par l'opération.

- Estimation tient compte de l'utilisation réseau et CPU, des effets du cache (sur les jointures) et de la préextraction (prefetching) (rapatrie plusieurs blocs feuilles d'un index en un seul accès disque).

VII-B - Package DBMS_STATS

- Nouvelle valeur pour le paramètre estimate_percent : AUTO_SAMPLE_SIZE
=> estime la taille de l'échantillon nécessaire pour obtenir les meilleurs gains de performances.

- Nouvelles options pour le paramètre size :

- REPEAT : répète la forme de l'histogramme déjà existant ;

- AUTO : histogramme basé sur la distribution des données et l'utilisation de la colonne par l'application ;

- SKEWONLY : histogramme basé uniquement sur la distribution des données => intéressant pour la première « collecte » de statistiques (sinon AUTO).

Ex :

execute DBMS_STATS.GATHER_SCHEMA_STATS(…..
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt   => 'for all columns size AUTO')

- Nouvelles procédures :

- DBMS_STATS.GATHER_SYSTEM_STATS : récupère des statistiques système durant un intervalle de temps défini ;

- DBMS_STATS.SET_SYSTEM_STATS: Spécifie certains paramètres ;

- DBMS_STATS.GET_SYSTEM_STATS: Récupère certains paramètres.

Ex :

execute DBMS_STATS.GATHER_SYSTEM_STATS(interval => 120, stattab=>'messtats', statid=>'app')

Avec :

- intervalle en minutes ;

- stattab : nom de la table où seront stockées les stats ;

- statid : identifiant des stats générées.

(Avant de lancer cette commande, il faut activer une tâche (job) avec : ALTER SYSTEM SET job_queue_process=1, si la commande est lancée à l'aide d'un job.)

VII-C - Nouveaux indicateurs d'optimisation (hints) et modes pour l'optimiseur

OPTIMIZER_MODE (paramètre d'initialisation) ou OPTIMIZER_GOAL (à l'intérieur d'une session) peuvent prendre ces nouvelles valeurs :

- FIRST_ROWS_1 ;

- FIRST_ROWS_10 ;

- FIRST_ROWS_100 ;

- FIRST_ROWS_1000.

Ex :

ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS_100;

L'indicateur FIRST_ROWS (à l'intérieur d'une commande SQL) peut prendre n'importe quelle valeur.

Ex. :

Select /*+  FIRST_ROWS (25)  */

VII-D - Édition/modification des plans d'exécution stockés

Les plans d'exécution stockés dans les tables réservées du schéma « outln », peuvent être édités puis publiés vers les autres utilisateurs après optimisation du (des) plan(s) en question.

Les différentes parties modifiables sont :

- l'ordre des jointures ;

- méthodes des jointures ;

- méthodes d'accès (full scan ou par index) ;

- méthodes de distribution (parallel query) ;

- réécriture des sous-requêtes et vues.

En plus des plans publics qui sont stockés dans le schéma outln et ne sont pas modifiables, 9i ajoute les plans privés propres à une session et stockés sur son propre schéma d'exécution. Ce plan n'est visible par les autres sessions que s'il est sauvegardé en tant que « public ».
Les plans publics sont utilisés quand le paramètre USE_STORED_OUTLINES vaut TRUE.
Les plans privés sont utilisés quand le paramètre USE_PRIVATE_OUTLINES vaut TRUE.

Pour utiliser la commande CREATE OUTLINE FROM, il est nécessaire de posséder :

- le privilège SELECT_ANY_CATALOG ;

- le privilège CREATE ANY_OUTLINE.

La commande DBMS_OUTLN_EDIT.create_edit_tables (valable depuis la 8i) :

- crée les tables nécessaires dans le schéma en cours pour dupliquer et éditer les plans stockés (<=> exécution du script utledtol.sql)

- nécessite d'avoir le privilège EXECUTE sur le package DBMS_OUTLN_EDIT.

La colonne outline_sid à été ajoutée à V$SQL : session oracle à partir de laquelle le plan a été rapatrié.

Pour contrôler l'utilisation des plans privés, le paramètre de session peut être modifié :

ALTER SESSION SET USE_PRIVATE_OUTLINES = [TRUE|FALSE|nom_categorie]

- TRUE : active les plans privés dans la catégorie DEFAULT ;

- FALSE : désactive les plans privés ;

- nom_categorie : Active les plans privés dans la catégorie nom_categorie.

La commande qui crée les plans stockés est la suivante :

CREATE [OR REPLACE] [PUBLIC|PRIVATE] OUTLINE [nom_outline]
[FROM  [PUBLIC | PRIVATE] nom_outline_source]
[FOR CATEGORY nom_catégorie] [ON statement]

VII-E - Optimisation du partage des curseurs

Plans d'exécution non recalculés à chaque fois : les valeurs littérales sont remplacées par des variables de substitution (et différents plans possibles avec différentes valeurs).
Trois valeurs possibles pour le paramètre dynamique CURSOR_SHARING :

- FORCE : pas de remplacement ;

- EXACT (par défaut) : partage pour toutes les valeurs ;

- SIMILAR (nouveauté 9i) : l'optimiseur examine l'ordre SQL avant de décider de recalculer (ou pas) le plan.

Les plans d'exécution sont maintenant stockés en cache et consultables avec la vue V$SQL_PLAN.
Pour activer (/désactiver) le monitoring sur un index :

ALTER INDEX mon_index MONITORING USAGE;
ALTER INDEX mon_index NOMONITORING USAGE;

Ces infos sont stockées dans la vue V$OBJECT_USAGE. (Purgés à chacun des ces ordres sur l'index suivi.)

VII-F - Index

Utilisation d'un index même si la 1re colonne n'est pas spécifiée (sauf pour les reverses ou bitmap index) = skip scanning.
Bitmap Join Index : Index bitmap créé à partir de jointures.

CREATE BITMAP INDEX mon_index_bitmap
ON sales (c.region)
FROM sales s, customers c
Where c.cust_id = s.cust_id

=> sera utilise dans l'ordre suivant :

SELECT SUM(s.cost)
FROM sales s, customers c
WHERE s.cust_id = c.cust_id
AND c.region ='Est';

=> permet d'utiliser seulement une table (sales) et l'index bitmap.

Restrictions :

- limitation au parallel DML sur la table fille uniquement (pas sur les tables de référence) ;

- les tables concernées peuvent être mises à jour une à la fois seulement ;

- les tables ne peuvent apparaître plus d'une fois dans la requête ;

- les tables ne peuvent pas être organisées en index ou être temporaires ;

- les colonnes citées dans l'index n'appartiennent qu'aux tables maîtres (ou de référence) ;

- la colonne de jointure sur la table maître ne peut être qu' une clé primaire ou unique ;

- si la clé primaire est une clé composée, toutes les colonnes doivent apparaître dans la jointure.

VIII - Améliorations du partitionnement

VIII-A - Partitions sur liste

Ex. :

CREATE TABLE CLIENTS
(CLIENT_ID        VARCHAR2(7),
CLIENT_ADRESS       VARCHAR2(32),
CLIENT_DEPT        VARCHAR2(2))
PARTITION BY LIST (CLIENT_REGION)
STORAGE (INITIAL 100K NEXT 10K PCTINCREASE 0) TABLESPACE tbs_cli
(PARTITION region_nord
VALUES ('62','75','92','60','45','44')
STORAGE(INITIAL 25K, NEXT 5K) TABLESPACE tbs_cli1
PARTITION region_sud
VALUES ('86','87','33','13','69','84'));

=> les instructions de stockage pour la table servent d'information par défaut pour les partitions (sauf si celles-ci sont spécifiées au niveau de la partition, (STORAGE(INITIAL 25K, NEXT 5K) TABLESPACE tbs_cli1)).
=> si une valeur insérée au niveau de la table n'appartient pas aux listes, une erreur Oracle sera générée.
=> on peut, dans un select, faire une recherche à l'aide d'une égalité, d'une liste de valeurs (opérateur IN) ou d'un opérateur de comparaison (<=, <, >, >= …).

Ajout d'une partition : les valeurs ajoutées ne doivent pas exister dans les listes déjà créées.

ALTER TABLE CLIENT
ADD PARTITION region_autre
VALUES ('20','97');

Si le nom de la partition n'est pas spécifié, celui-ci sera généré automatiquement et commencera par SYS_P###.

Fusion (Merge) de deux partitions :

ALTER TABLE CLIENTS
MERGE PARTITIONS nord_est, nord_ouest
INTO PARTITION region_nord;

=> Les instructions de stockage pour la table servent d'information par défaut pour la partition de fusion (sauf si celles-ci sont spécifiées au niveau de la partition).
Les index (locaux et globaux) deviennent « Unusable ».

Ajout de valeur à une partition :

ALTER TABLE CLIENTS
MODIFY PARTITION region_sud
ADD VALUES ('09','31');

Suppression de valeur d'une partition (et non pas d'une partition) :

ALTER TABLE CLIENT
MODIFY PARTITION region_sud
DROP VALUES ('87');

=> il ne doit pas exister de ligne en table pour la valeur supprimée (DELETE obligatoire dans le cas inverse).
=> ne supprime pas toutes les valeurs de la partition.

Éclatement (Split) de partition en deux :

ALTER TABLE CLIENTS
SPLIT PARTITION region_nord
VALUES ('44','45','92')
INTO (PARTITION region_nord_ouest
TABLESPACE tbs_cli2
PARTITION region_nord_est) ;

=> Les trois valeurs spécifiées ci-dessous remplissent la première sous-partition créée, les autres valeurs remplissant la seconde sous-partition.
=> Les index globaux deviennent « unusable ».

Restrictions :

- la valeur Null n'est pas acceptée dans les listes ;

- partition sur plusieurs colonnes non acceptées ;

- la liste des valeurs ne peut dépasser 4 Ko ;

- chaque liste doit comporter au moins une valeur.

VIII-B - Maintien des Index globaux

En 8i, les index globaux sur une table partagée sur plusieurs partitions deviennent 'unusable' si un ordre DDL est exécuté sur la table portant l'index.
En 9i, ceux-ci peuvent rester utilisables si la clause suivante est ajoutée :

ALTER TABLE matable
DROP SUBPARTITION tab1
UPDATE GLOBAL INDEXES
PARALLEL (DEGREE 4);

=> Valables pour les ordres ADD, DROP, MOVE, TRUNCATE, SPLIT, MERGE, EXCHANGE et COALESCE.
=> ne fonctionne pas sur les tables organisées en index.

VIII-C - Chargement direct en parallèle

En 8i, les chargements directs en parallèle (après le high water mark) ne se faisaient qu'avec un processus esclave par partition.
En 9i, plusieurs processus esclaves peuvent être utilisés par partition, ceci améliorant les temps de chargement. L'indicateur (hint) à utiliser est le suivant :
/*+ PARALLEL DEGREE n */ (avec n = nombre de processus /partition).

IX - Améliorations pour le DataWareHouse

- Tablespaces transportables : support de différentes tailles de bloc entre deux bases.

- Oracle Change Data Capture (CDC) : à l'aide de déclencheurs (triggers), Oracle capture les ordres DML et les insère dans une table de modifications (change table) afin que les changements puissent être publiés ultérieurement (et seulement les changements).
Pour publier les changements d'une application transactionnelle vers un DataWareHouse :

- sélectionner les tables (sources) pour lesquelles on veut surveiller les changements ;

- utiliser le package DBMS_LOGMNR_CDC_PUBLISH pour récupérer les changements sur ces tables ;

- publier les changements à l'aide des tables de modifications ;

- donner ou révoquer les droits de select sur ces tables de modification.

Pour Souscrire :

- utiliser le package DBMS_LOGMNR_CDC_SUBSCRIBE pour souscrire aux tables source ;

- préparer les vues du souscripteur qui permettent de faire un select sur les tables de modification ;

- purger puis supprimer les vues devenues inutiles.

Note : il faut bien sûr donner les droits d'exécution sur les deux packages précités. Les tables de modification sont gérées en interne par LogMiner.

- Vues supportant CDC :

- CHANGE_SOURCES: liste des changements des sources ;

- CHANGE_SETS: liste des ensembles de changements ;

- CHANGE_TABLES: liste les tables de modification ;

- DBA_SOURCE_TABLES : liste des tables source publiées ;

- DBA_PUBLISHED_COLUMNS :liste des colonnes des tables source publiées ;

- DBA_SUBSCRIPTIONS : liste des souscriptions enregistrées ;

- DBA_SUBSCRIBED_TABLES : liste des tables publiées pour lesquelles les souscripteurs ont souscrit ;

- DBA_SUBSCRIBED_COLUMNS : liste des colonnes des tables publiées pour lesquelles les souscripteurs ont souscrit.

- Pipelines Data Transformation (9i).

- External Tables : Tables en lecture seule dont les données sont stockées dans des fichiers plats et décrivent comment les données sont présentées à la base de données :

- table virtuelle ;

- aucun ordre DML n'est autorisé ;

- pas d'indexation possible ;

- droits gérés par les privilèges système : SELECT TABLE et READ DIRECTORY ;

- complémentaires à SQL*Loader ;

- créées avec l'ordre SQL :

CREATE TABLE order_items_ext
(order_id number (12), line_item_id number (3) ,
product_id number (6), unit_price number (8,2),
quantity number(8))
ORGANIZATION EXTERNAL           => table externe
(TYPE oracle_loader       => type d'accès (drivers utilisés)
DEFAULT DIRECTORY  ext_tables    => Répertoire contenant les fichiers
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
BADFILE 'bad_order'
LOGFILE 'log_order'
MISSING FIELS ARE NULL)
LOCATION ('order_items1.dat','order_items2.dat'));

Les vues suivantes ont été créées pour stocker les informations des tables externes :

- DBA_EXTERNAL_TABLES ;

- DBA_EXTERNAL_LOCATIONS.

Fonctions sur tables :

- permettent de faire des transformations sur les données d'une table ;

- les données en entrée peuvent consister en un ensemble de données ;

- renvoient un ensemble d'enregistrements ;

- peuvent être parallélisées ;

- utilisées dans la clause FROM d'un ordre SELECT.

Ex. :

CREATE OR REPLACE FUNCTION transform (p r.ref_cur_type)
RETURN table_order_items_type (=> type prédéfini par l'utilisateur)
PIPELINED is
BEGIN
FOR enr in p LOOP
-- This is simple but in the real world…
enr.quantity:= enr.quantity*2;
pipe row (enr);
END LOOP;
RETURN;
END;

La nouvelle instruction PL/SQL PIPELINED ramène un seul enregistrement résultat, suspend l'exécution de la fonction et la redémarre quand le programme appelant demande l'enregistrement suivant.
Cette fonction peut être appelée par :

SELECT * FROM
TABLE ( transform (cursor (select * from order_items_ext)));
Multitable INSERT :

- insertion dans plusieurs tables simultanément ;

- peut être utilisé en interne pour rafraîchir des vues matérialisées ;

- peut bénéficier de la parallélisation et du mécanisme « Direct Load ».

Insert Inconditionnel (ALL) : Les clauses INTO sont exécutées une fois par enregistrement.

INSERT ALL
INTO sales     VALUES (today,product_id,quantity)
INTO prd_sales VALUES (today,product_id,total)
SELECT TRUNC(s.sales_date) today,
S.product_id,
SUM(s.amount_sold) total,
SUM(s.quantity) quantity,
FROM sales_activity_direct s, product_information p
WHERE s.product_id = p.product_id
AND trunc(sales_date)=trunc(sysdate)
GROUP BY trunc(sales_date), s.product_id;

Pivoting Insert : idem que précédemment sauf que toutes les insertions se font dans la même table
plusieurs enregistrements créés pour un enregistrement lu.

Insert Conditionnel (ALL) :

INSERT ALL
WHEN product_id in ('01','05','06') THEN
INTO sales_01     VALUES (today, product_id, total)
WHEN product_id in ('02','03','04') THEN
INTO sales_02 VALUES (today, product_id, total)
SELECT TRUNC (s.sales_date) today,
S.product_id,
SUM (s.amount_sold) total,
FROM sales;

Conditionnel FIRST_INSERT : Si la 1re condition est vraie, il ne traite pas les autres :

INSERT FIRST
WHEN SALARY > 40000 THEN
INTO EMPLOYE_BOSS (name, salary)
WHEN SALARY > 25000 THEN
INTO EMPLOYE_CADRE (name, salary)
WHEN SALARY > 15000 THEN
INTO EMPLOYE_SIMPLE (name, salary)
ELSE
INTO EMPLOYE_OTHERS (name, salary)
SELECT name, salary from emp;

Instruction MERGE : « Fusion » d'insert et update suivant conditions :
si l'enregistrement existe => UPDATE ;
si l'enregistrement n'existe pas => INSERT.

MERGE INTO emp e
USING emp_ref s
ON (e.emp_id = s.emp_id)
WHEN MATCHED THEN UPDATE
SET e.name = s.name
WHEN NOT MATCHED THEN INSERT (emp_id, name … )
VALUES (emp_id, name … );

Améliorations sur les vues matérialisées :
les différentes actions possibles (rafraîchissement, réécriture) sur ces vues sont identifiables par les procédures DBMS_MVIEW.EXPLAIN_MVIEW et DBMS_MVIEW.EXPLAIN_REWRITE.

X - Migration et mise à niveau de la base de données

Oracle 7.3 -> 9i : Migration.
Oracle 8.0.6 -> 9i : Upgrade.

Server Manager n'existe plus :

- Utilisation de SQL*PLUS qui possède les mêmes fonctionnalités :

- sqlplus /nolog : pour ouvrir SQL*Plus sans se connecter,

- puis connect …. as sysdba ;

- connect internal ne fonctionne plus.

Remplacer tous les LONG par des LOB (LONG=>CLOB ou LONG RAW => BLOB) avec l'ordre :

ALTER TABLE matable
MODIFY(nom_long CLOB);

(il faut vérifier qu'il y a deux fois la place du LOB pour faire la conversion).

Utiliser plutôt DBMS_STAT que ANALYZE.

Nouveaux types :

- AnyData ;

- AnyDataSet ;

- AnyType ;

- XMLType ;

- TIMESTAMP (Précision) : stocke la date avec les heures, minutes, secondes et fractions de seconde (de 0 à 9 décimales) ;

- INTERVAL DAY TO SECOND : période de temps en jours, heures, minutes et secondes ;

- INTERVAL YEAR TO MONTH : période de temps en années et mois.

XI - Oracle 9i : Conforme à la norme ISO SQL 1999

XI-A - Jointures

XI-A-1 - Produit cartésien de deux tables (Cross Joins)

Select e.ename,
d.deptno
From emp e CROSS JOIN
dept d;

XI-A-2 - Jointures « Naturelles »

Jointures qui lient toutes les colonnes communes aux deux tables.
(Ici équivalent à une jointure explicite sur deptno) :

Select e.ename,
deptno
From emp e NATURAL JOIN
dept d;

=> Les colonnes de même nom doivent avoir le même type.
=> On ne préfixe jamais les colonnes communes (dans ce cas-là), sinon message d'erreur.
=> Un « select * " ne ramènera que les colonnes distinctes des deux tables.

XI-A-3 - Équijointures

Similaire au « Natural joins »

Select e.ename,
deptno
From emp e JOIN
dept d USING (deptno)
Where d.dept_name = 'PARIS';

XI-A-4 - Jointures avec clause « ON »

Select e.ename,
deptno
From emp e JOIN
dept d ON (e.deptno = d.deptno) JOIN
region r ON (d.regionno = r.regionno)
Where r.region_name = 'EUROPE';

=> La clause « ON » peut contenir n'importe quel prédicat, y compris des sous-requêtes.

XI-A-5 - Jointures externes

Select e.ename,
deptno
From emp e {LEFT|RIGHT|FULL} OUTER JOIN
dept d ON (e.deptno = d.deptno)
Where d.dept_name = 'PARIS';

=> Avec :

- LEFT : rapatrie les données de la table de gauche ;

- RIGHT : rapatrie les données de la table de droite ;

- FULL : rapatrie les données des deux tables.

XI-B - Expressions de type CASE

XI-B-1 - Fonction EXTRACT

EXTRACT (YEAR FROM madate)

=> récupère l'année stockée dans la date fournie.

XI-B-2 - CASE simple : similaire au DECODE

Select e.ename , (CASE salary
WHEN 10000 THEN 'EMPLOYE'
WHEN 15000 THEN 'CADRE'
ELSE 'BIG BOSS' END) status
From Emp e;

XI-B-3 - CASE de recherche

Select e.ename, (CASE
WHEN salary < 10000 THEN 'EMPLOYE'
WHEN salary > 15000 THEN 'CADRE'
ELSE 'BIG BOSS' END) status
From Emp e;

XI-B-4 - NULLIF

Ramène Null si la 1re expression est égale à la 2e sinon ramène la 1re.
NULLIF (expr1,expr2) :

IF expr1 = expr2 Then
return null
ELSE
return expr1;
End If;

XI-B-5 - COALESCE

Extension de NVL => ramène la 1re valeur non nulle.

COALESCE(expr1,expr2,expr3,...,exprn)

XI-C - Sous-requêtes scalaires

Ramène une ligne avec une seule valeur

Select t.libelle_type_client ,
(Select count (*)
From Client c
Where c.typ_cli = t.typ_cli) Total
From Type_Client T;

=> peut être utilisé partout sauf dans les clauses GROUP BY ou condition WHEN des triggers.

XI-D - Groupes d'enregistrements

XI-D-1 - GROUPING SETS

Équivalent à des unions de requêtes avec des GROUP BY, mais cette fois-ci, la requête n'est exécutée qu'une fois.

select time_id, support_id , item_id,
sum( montant) as cumul
From sales
Group By GROUPING SETS
((time_id, support_id , item_id),
(time_id, support_id),
(time_id, item_id))

=> ramène trois groupes d'enregistrements : un pour le 1er triplet, un pour le 2e couple et un pour le dernier couple.

XI-D-2 - Colonnes composites

- GROUP BY ROLLUP (a, b, c) -> crée quatre regroupements :

- un pour le triplet (a,b,c) ;

- un pour le couple (a,b) ;

- un pour (a) ;

- un pour le total général.
((a,(b,c)) ne ramène alors que trois trois groupes).

GROUP BY CUBE (a, b, c) => ramène 2 puissance 3 soit huit groupes possibles :

- a, b et c ;

- a, b, tout (c) ;

- a, tout (b), c ;

- a, tout (b), tout(c) ;

- tout (a), b et c ;

- tout (a), b, tout (c) ;

- tout (a), tout (b), c ;

- tout (a), tout (b), tout (c) = total général.

- Ces trois types de regroupement sont bien sûr cumulables.

XII - Améliorations du SQL, PL/SQL et nouveaux types de données

XII-A - Nouveaux Types

- TIMESTAMP : format de date incluant jusqu'au milliardième de seconde.

- TIMESTAMP WITH TIME ZONE : TIMESTAMP + décalage en heures de la zone.

- TIMESTAMP WITH LOCAL TIME ZONE : TIMESTAMP + décalage en heures et minutes de la zone intégrée dans la date affichée.

- INTERVAL YEAR TO MONTH : stocke le nombre d'années et de mois d'une période.

- INTERVAL DAY TO SECOND : stocke le nombre de jours, heures, minutes, secondes et fractions de seconde d'une période.

- Paramètre statique de base TIME_ZONE :

- peut avoir comme valeur soit un nombre d'heures et de minutes, soit un nom de zone ;

- peut être spécifié à la création de la base ou après avec un ALTER DATABASE.

- Heures d'été / d'hiver : géré automatiquement par la base pour TIMESTAMP WITH TIME ZONE et TIMESTAMP WITH LOCAL TIME ZONE (pas pour TIMESTAMP).

La vue V$TIMEZONE_NAMES décrit tous les fuseaux horaires.

XII-B - Support de l'Unicode

- Seul utilisable pour le national character set.

- En UTF-8 (1 à 4 octets) ou UTF-16 (2 * 2 octets).

XII-C - Compilation du PL/SQL en mode natif

Nouveaux paramètres d'initialisation pour pouvoir compiler en C :

- PLSQL_NATIVE_LIBRARY_DIR : répertoire de stockage des objets créés par le compilateur ;

- PLSQL_NATIVE_MAKE_UTILITY : nom de l'utilitaire Make ;

- PLSQL_NATIVE_MAKE_FILE_NAME : chemin complet du Make File ;

- PLSQL_NATIVE_C_COMPILER : chemin complet du compilateur C (prend par défaut celui du système d'exploitation) ;

- PLSQL_NATIVE_LINKER : chemin complet de l'éditeur de liens (linker) (sous UNIX) ;

- PLSQL_COMPILER_FLAG : 'INTERPRETED' ou 'NATIVE' (peut être modifié au cours de la session).

XII-D - Amélioration niveau Objet

- Héritage complet pour les types SQL.

- Combinaisons multiples possibles entre les collections, tables imbriquées (Nested tables) et VARRAY.

- Type : FINAL ou NOT FINAL (signifie qu'il y a encore des fils dessous ).

- Pouvoir de substitution: une colonne d'un type peut contenir des instances de son type ou de n'importe quel de ses sous-types.

- Vues hiérarchiques : du même genre que pour les types et sous-types.

- Privilèges système :

- UNDER ANY TYPE : permet de créer des sous-types sous des types non finaux ;

- UNDER ANY VIEW : permet de créer des sous-vues ;

- WITH HIERARCHY OPTION : Donne (par un GRANT) un privilège objet sur tous ses fils.

- SQL*Loader, Import et Export supportent ces nouvelles fonctionnalités.

- Possibilité d'évolution des Types d'Objet :

- avant 9i, seul l'ajout de méthodes était autorisé ;

- une modification de l'objet est répercutée sur les objets le référençant ;

- les attributs et méthodes peuvent être ajoutés, supprimés ou modifiés ;

- les types SQL peuvent être modifiés sans être recréés.

XIII - Haute disponibilité

XIII-A - Restauration

Pour améliorer le temps de restauration, Oracle ne restaure que les blocs en mémoire qui n'ont pas été écrits sur le disque (il balaye pour cela deux fois les redo logs).
Un nouveau paramètre dynamique FAST_START_MTTR_TARGET détermine le temps maximal (en secondes : de 0 à 3600) que prendra une restauration (défini par le DBA).
Le changement de ce paramètre induit automatiquement le changement de deux autres :

- FAST_START_IO_TARGET : nombre de blocs de données à récupérer ;

- LOG_CHECKPOINT_INTERVAL : intervalle de temps maximum pour limiter le nombre d'enregistrements de redo logs à lire.

(Ces deux paramètres ne peuvent pas être modifiés manuellement.)

La vue V$INSTANCE_RECOVERY permet de suivre les points de synchronisation (checkpoints) et leurs impacts sur le temps de restauration.

XIII-B - Index

De nouveaux types d'index peuvent être reconstruits en ligne :

- index à clé inversée ;

- index basé sur une fonction ;

- index à clé compressée sur les tables normales ;

- index à clé compressée sur les tables organisées en index.

Une table organisée en index peut être reconstruite en ligne :

ALTER TABLE matable MOVE ONLINE TABLESPACE data1 OVERFLOW TABLESPACE data1;

Pour améliorer les performances d'un index basé sur un IOT, il stocke chaque entrée de l'index dans la DBA (Data Block Address). Si beaucoup de mises à jour sont intervenues, cette DBA n'est alors plus à jour, pour la reconstruire, il faut lancer en ligne l'ordre suivant :

ALTER INDEX monindex UPDATE BLOCK REFERENCES;

XIII-C - Tables

Une table peut être réorganisée en ligne si celle-ci contient une clé primaire (Oracle utilise des vues matérialisées pour cela).
Étapes pour réorganiser une table :

- vérifier que la table est réorganisable en ligne avec DBMS_REDEFINITION.Can_Redef_Table ;

- créer la table d'intérim qui servira à stocker les mises à jour effectuées lors de la redéfinition ;

- réorganiser la table avec DBMS_REDEFINITION.Start_Redef_Table ;

- créer les objets et triggers rattachés à la table d'intérim ;

- finir la réorganisation avec DBMS_REDEFINITION.Finish_Redef_Table.

Pour synchroniser la table initiale et celle d'intérim, on appelle la procédure :

DBMS_REDEFINITION.Sync_Interim_Table.

Pour arrêter le traitement en cas d'erreur, on appelle la procédure :

DBMS_REDEFINITION.Abort_Redef_Table.

Limitations :

- colonnes de clés primaires non modifiables ;

- tables des schémas SYS ou SYSTEM non modifiables ;

- colonnes de type LONG ou FILE non modifiables ;

- réorganisation dans le même schéma ;

- nouvelles colonnes ne peuvent être déclarées NOT NULL qu'après la réorganisation.

XIII-D - Analyse Validate Structure : Exécutable même pendant l'accès intensif de la table

XIII-E - Oracle Flashback

- Permet de créer une vue de la base à une date située dans le passé.

- Peut démarrer à partir d'une date ou d'un numéro SCN.

- Seules les transactions commitées jusqu'à ce moment sont visibles.

- Valable au niveau de la session.

- Se base sur les tablespaces d'annulation : plus on veut regarder loin dans le temps, plus ceux-ci doivent être importants.

- Utilise le package DBMS_FLASHBACK.

- Pour spécifier le temps de rétention :

ALTER SYSTEM SET UNDO_RETENTION= tempsensecondes;

- Se lance avec la commande :

DBMS_FLASHBACK.Enable_At_Time('30/08/2002 0:51:42');

(il prend le N° SCN le plus proche de la date spécifiée, dans un intervalle de 5 minutes).

- Se termine avec la commande :

DBMS_FLASHBACK.disable.

ou en fermant la session.

- ne s'applique qu'aux ordres DML.

Complément d'information

Vous pouvez également consulter l'article Oracle FlashBack par Helyos

XIV - Real Application Clusters(9i) (Parallel Server en 8i)

Les ressources utilisées en commun par plusieurs instances sont gérées par le Global Cache Service. Elles peuvent avoir trois états :

- Null (N) => non utilisée ;

- Shared (S) => partagée entre plusieurs instances ;

- Exclusive (X) => réservée par une seule instance.

Les ressources peuvent être de deux types :

- Local => utilisées par un seul serveur ;

- Global => utilisées par plusieurs serveurs.

Le processus de Cache Fusion permet d'éviter une écriture sur le disque, en créant une copie locale d'un bloc en cache (sur une instance A) sur une autre instance (instance B).
La configuration typique d'un RAC est d'avoir une seule instance active primaire (ACTIVE_INSTANCE_COUNT=1) et une instance secondaire.
Cette instance primaire reçoit toutes les demandes Oracle Net Service et est basculée, en cas de défaillance, sur l'instance B qui devient à son tour primaire.
Les paramètres d'initialisation à utiliser sont les suivants pour activer un RAC :

- GC_FILES_TO_LOCKS : assigne le Global Cache Service à de multiples blocs ;

- INSTANCE_NUMBER : identifiant de l'instance, un numéro par instance, par défaut à 1 ;

- INSTANCE_NAME : nom de l'instance, généralement le nom du SID.

XV - Logminer : Améliorations

Oracle LogMiner est un outil permettant de lire, analyser et interpréter le contenu des redo logs.
Une fois lancé avec DBMS_LOGMNR.LogMnr_Start (…), les résultats apparaissent dans la vue V$LOGMNR_CONTENTS (vide si Logminer n'est pas démarré) avec, entre autres les colonnes :

- sql_redo : donne l'ordre SQL exécuté puis stocké dans les fichiers redo ;

- sql_undo : génère l'ordre inverse à l'ordre SQL utilisé.

Pour afficher la clé primaire d'une table mise à jour, il faut lancer l'ordre suivant :

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

(Si la table ne contient pas de clé primaire, toutes les colonnes de la table sont tracées.)
Depuis la 9i, les ordres DDL sont maintenant lisibles avec LogMiner.

Pour extraire le dictionnaire de données, la commande suivante doit être lancée :

DBMS_LOGMNR_D.Build('dictionary.ora','/oracle/dbs',option =>dbms_logmnr_d.store_in_flat_file) (vers un fichier plat)
option => dbms_logmnr_d.store_in_redo_logs) (vers les redo logs).

N. B. Pour extraire vers les fichiers redo, la base doit être en mode Archive Log.

Logminer ne supporte pas les objets suivants :

- LONG ;

- Type Objet ;

- Les collections (Varray …) ;

- objets de type REF ;

- les tables organisées en index (IOT).

LogMiner Viewer est l'outil graphique qui permet d'effectuer les mêmes opérations, mais à partir de Enterprise Manager.

Complément d'information

Vous pouvez également consulter l'article Oracle LogMiner par Helyos

XVI - Oracle 9i Data Guard (StandBy Database en 8i)

La physical standby database est une copie exacte de la base de données active (primaire) et qui prend le relai si la base primaire ne fonctionne plus. Elle est synchronisée bloc par bloc à la base primaire et est soit montée non ouverte, soit ouverte, mais en lecture seule.

Data Guard Broker : interface graphique qui administre une base de données primaire avec ses standby databases associées, et envoie les redo logs sur les autres sites en mode :

- synchrone : toute modification est répercutée immédiatement sur la standby => pas de perte de données ;

- asynchrone : modification envoyée avec un délai par l'intermédiaire d'un processus esclave.
(cet outil existe aussi en ligne de commande : DGMGRL.

Data Guard Manager : outil de suivi de l'environnement et de la configuration des différentes bases (nodes) de données (primaire et standby) :

- nécessite d'avoir installé Oracle Management Server (OMS) et les agents sur chacune des bases.

Graceful Switchover : permutation volontaire de la base primaire en base de standby (et réciproquement).
Pour pouvoir exécuter la permutation (switchover), il faut que :

- la base de données primaire soit arrêtée proprement (en mode normal, immédiate ou transactionnel) ;

- les redo logs archivés soient disponibles ;

- les redo logs, datafiles et control files de la base primaire soient disponibles.

La commande SQL à exécuter est :

- pour la base primaire :

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY

- puis faire un shutdown, un start nomount puis un mount standby database ;

- pour la base de standby :

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL PRIMARY

(Les mêmes commandes sont exécutées de manière transparente à travers l'outil graphique.)

L'état de la base est visible avec la vue V$DATABASE et la colonne DATABASE_ROLE (valeur PRIMARY (par défaut) ou STANDBY).
Un nouveau paramètre STANDBY_PRESERVE_NAMES, s'il est positionné à TRUE, implique que si un fichier est créé ou supprimé sur la base primaire, l'équivalent sera effectué sur la base de STANDBY.

Le Log Transport Service (indépendant depuis la 9i) copie les fichiers redo de la primaire vers la standby et ce, sous trois modes :

- synchronous data copy : fichier copié par logwriter (LGWR) de manière synchrone ;

- asynchronous data copy : fichier copié par logwriter (LGWR) de manière asynchrone ;

- batched data copy : fichier copié de manière traditionnelle (comme 8i) avec le processus ARCH.

Le paramètre Log_Archive_Dest_n (n=1..10): permet de spécifier des paramètres pour la base primaire et les autres bases de type standby.
(Il faut donc positionner au moins deux paramètres, les résultats sont affichés dans la vue V$ARCHIVE_DEST).

L'écriture des archives logs sur disque peut être :

- synchrone : attribut AFFIRM positionné dans le paramètre Log_Archive_Dest_n ;

- asynchrone : attribut NOAFFIRM positionné dans le paramètre Log_Archive_Dest_n.

Pour se protéger d'un problème d'écriture des archives logs du primaire vers la standby, il faut utiliser la commande :

ALTER DATABASE SET STANDBY DATABASE PROTECTED

et pour supprimer cette protection :

ALTER DATABASE SET STANDBY DATABASE UNPROTECTED

Log_Gap : délai en minutes des redos sur la base standby avant que ceux-ci soient disponibles (attribut de Log_Archive_Dest_n).

Depuis la 9i, Logwriter écrit à la fois en local et dans les redo logs distants (appelés STANDBY_REDO_LOG).
Et depuis la 9.2, un nouveau mode apparaît, c'est le LOGICAL STANDBY :

- la base de secours (standby) est ouverte ;

- les modifications sur la primaire sont écrites sur la standby par le process server de cette dernière, ce qui ne garantit plus une équivalence totale entre les deux bases.

XVII - Remerciements

Chaleureux remerciements à Developpez.com, l'équipe SGBD, et particulièrement Goshiz et Pomalaix.