12c: Indexer les mêmes colonnes avec différents types d’index

Publié le par
Répondre

La 11g avait apporté les index invisibles: on peut créer des index qui sont maintenus, mais ne sont pas utilisés par les requêtes. Pour pouvoir les tester par exemple.
Mais il était toujours impossible de tester un différent type d’index sur des colonnes déjà indexées (pour le passer en bitmap, en reverse, le compresser,…).
L’erreur était:

ORA-01408:  such column list already indexed
Cause:  A CREATE INDEX statement specified a column that is already indexed. A single column may be indexed only once. Additional indexes may be created on the column if it is used as a portion of a concatenated index, that is, if the index consists of multiple columns.
Action: Do not attempt to re-index the column, as it is unnecessary. To create a concatenated key, specify one or more additional columns in the CREATE INDEX statement.

En 12c, cette restriction est levée dans le cas où l’index que l’on crée est invisible.
Par exemple, je n’ai pas d’erreur en exécutant ceci:

create unique index TEST1 on TEST(num) ;
create unique index TEST2 on TEST(num) reverse invisible;

On a donc 2 index sur les mêmes colonnes. Ils sont maintenus (donc utilisables) mais un seul n’est utilisé: celui qui est visible.
Il est facile de passer de l’un à l’autre en changeant leur visibilité: c’est une opération online (pas de verrou).

Deux gros avantage:

  • créer un index peut être long. On peut le faire online, mais le fait de devoir supprimer l’ancien avant nous laissait un certain temps dans index.
  • si on ne le crée pas online, la création du nouvel index peut être très rapide car elle utilise l’ancien.

On a donc le choix: maximiser la disponibilité de l’application en le créant online, ou minimiser le temps de création de l’index.

Si l’index supporte une contrainte d’intégrité, c’est un peu plus complexe.
Le détail dans la demo avec une introduction sur de nouvelles opérations online apportées par la 12c (drop index et drop constraint).

12c: Voir le texte SQL après remplacement de toutes les vues

Publié le par
Répondre

On a parfois des requêtes qui paraissent simples, mais lisent des vues sur des vues, sur des vues,…
Ou des requêtes sur lesquelles Virtual Private Database ajoute des prédicats.

En 12c, on a un moyen simple de voir la requête finale après tous les remplacement.
C’est peut-être utile pour montrer au développeur qu’une requête qui paraît simple ne l’est pas tant que ça.

Exemple sous sqlplus:

set autoprint on long 10000 longc 10000
variable c clob
exec dbms_utility.expand_sql_text(input_sql_text=>'select * from dba_objects',output_sql_text=>:c)

Un exemple sur la demo

12c: Statistiques privées pour les tables temporaires (GTT)

Publié le par
Répondre

On travaille souvent avec des volumes différents sur les Global Temporary Tables. Assez souvent, le même code est appelé en online pour quelques enregistrements ou en batch pour des millions.
Alors quelles statistiques mettre pour les Global Temporary Table concernées ?

  • Pas de statistiques. les requêtes feront du dynamic sampling.
    Oui mais c’est seulement la première qui va optimiser le plan d’exécution qui va faire ce dynamic sampling. Les suivantes réutiliseront le même plan. Et un jour on va se retrouver avec un batch qui va travailler sur des millions de lignes en faisant un NESTED LOOP qui était prévu pour quelques lignes.
  • L’autre solution jusqu’en 11g est de fixer des stats qui amènent à choisir un plan d’exécution correct pour les 2 cas.
    Très souvent, le plan prévu pour un grand nombre de lignes est valable aussi sur un faible volume. Il vaut faire un HASH JOIN même pour quelques lignes, plutôt que de risquer de faire un NESTED LOOP à partir d’une grosse table.

En 12c, une autre solution: les statistiques privées, propres à la session. Chaque session calcule les stats sur la table temporaire chargée (et en 12c c’est automatique pendant le chargement – cf. post précédent). Et si les stats sont différentes, le curseur ne sera pas partagé. On aura donc toujours un plan d’exécution optimisé pour les données de la session.

Le choix de la visibilité des stats se fait avec dbms_stats.set_table_prefs: GLOBAL_TEMP_TABLE_STATS peut être ‘SHARED’ (comme c’était avant en 11g) ou ‘SESSION’.
La réutilisation d’un curseur dépendra aussi de ces stats.

Comme d’habitude, la demo

12c: Calcul automatique des statistiques au chargement de la table

Publié le par
Répondre

En 11g après avoir chargé une table il fallait lancer dbms_stats.gather_table_stats pour avoir les statistiques correspondant aux données chargées, ce qui se rajoute au temps de chargement.
Pour les index, les stats sont calculées lors de la création de l’index.

En 12g Oracle calcule les stats pendant qu’il charge, sur un CREATE TABLE AS SELECT, ou un INSERT /*+ APPEND */ (à condition que la table soit vide – suite à un create ou un truncate) – ce qui évite d’aller relire la table ensuite pour un calcul de stats.

Attention, si on veut des histogrammes, ou calculer les stats sur les index existants, ou avoir des stats pour chaque partition, il faudra quand même lancer dbms_stats.gather_table_stats.
Mais avec l’option ‘GATHER AUTO’ dbms_stats ne recalculera que ce qui est nécessaire. Donc on se retrouve quand même avec un gain de temps pour un process ETL par exemple.

Comme d’habitude, la demo complète.

Si l’on veut ne pas calculer les stats – pour garder les anciennes ou pour garder la table sans stats, on peut utiliser le hint NO_GATHER_OPTIMIZER_STATISTICS.
Mais de toute façon, si on veut empêcher un calcul de stats il est mieux d’utiliser dbms_stats.lock_table_stats

Cela s’applique aussi sur les Global Temporary Table, pour les statistiques privées ou partagées.

Calcul de stats sur tables partitionnées

Publié le par
Répondre

Lorsqu’on charge une partition, il faut calculer les stats dessus.
Mais calculer les stats sur toutes les partitions, et les stats globales sur la table peut être très long.

On peut utiliser options=>’GATHER AUTO’ pour ne calculer les stats que sur ce qui a subi des modifications (la partition chargée et les stats globales)

On peut aussi choisir de ne calculer les stats que sur la partition et laisser les stats globales d’avant (granularity=>’PARTITION’), ou préférer faire une approximation des stats globales (granularity=>’APPROX_GLOBAL AND PARTITION’), puis ne calculer les stats globales que plus tard.

Exemple rapide dans la demo.
(démo en 12c introduisant une nouvelle fonctionnalité – à voir dans un prochain post)

A partir de la 11g, il y a aussi la possibilité d’utiliser les stats incrémentales lorsqu’on a besoin d’histogrammes à jour, où Oracle stocke un Synopsis pour éviter de relire les anciennes partitions.

12c: déplacer un datafile online

Publié le par
Répondre

Jusqu’en 11g pour déplacer un datafile (pour le mettre sur un autre filesystem) il fallait:

  • mettre le tablespace offline
  • copier le(s) fichier(s) à partir de l’OS
  • faire ALTER DATABASE RENAME FILE … TO …

Pour diminuer le downtime, il est aussi possible de faire la copie sans mettre le tablespace offline, seulement en BEGIN BACKUP. Puis il n’y aura qu’à le mettre offline que pendant le recovery.

En 12c, plus de problème. Il suffit de faire : ALTER DATABASE MOVE DATAFILE … TO …;

C’est une opération totalement online, sans aucun verrou, et très peu d’overhead: simplement pendant que Oracle fait la copie de fichier, les process qui veulent y accéder en écriture écriront à la fois dans l’ancien fichier et le nouveau.
Et ils liront uniquement l’ancien jusqu’à ce que la copie soit terminée.
Une fois que la copie est terminée, ils liront uniquement le nouveau fichier. Et l’ancien fichier sera supprimé sauf si on a précisé KEEP.

La demo montre les statistiques de session lors d’une activité sur un tablespace en cours de move.

On voit cette double écriture dans ‘physical write total bytes’. Par contre, on ne voit jamais les fichiers en double (et ils ont le même file_id), sauf si in regarde au niveau de l’OS.
L’opération ne modifie que le controlfile, c’est donc indépendant sur des bases en DataGuard, et ce n’est pas concerné par flashback database.

Dans l’alert.log on voit au début de l’opération:

Moving datafile /tmp/TESTMOVE1.dbf (2) to /tmp/TESTMOVE2.dbf

A partir de ce moment la session crée le fichier secondaire et signale aux autres session qu’elles doivent écrire en double vers ce fichier aussi.
Puis la copie commence (‘db file sequential read’ et ‘db file single write’ par i/o de 1MB)

puis lorsque tous les blocs sont copiés, on voit:

Move operation committed for file /tmp/TESTMOVE2.dbf

les session peuvent alors ne travailler que sur le deuxième fichier. Puis l’ancien fichier est supprimé.

Completed: alter database move datafile '/tmp/TESTMOVE1.dbf' to '/tmp/TESTMOVE2.dbf'

Oracle a introduit ça pour pouvoir déplacer des partitions vers un storage moins cher au lieu de purger/archiver.
Mais c’est aussi une solution pour passer en ASM sans arrêt de la base, ou pour n’importe quel changement de filesystem.
En ASM, pas besoin de cela: on a l’équivalent en rajoutant le nouveau disque et en supprimant l’ancien.

12c: possibilité de ne plus générer de redo sur les GTT

Publié le par
Répondre

Sur une Global Temporary Table, il n’est pas nécessaire de générer du redo car les données ne sont pas persistantes. Les données (table et index) sont dans un tablespace temporaire sur lequel il n’y a pas de recovery.
Par contre le undo est nécessaire, pas pour des raisons de consistent reads (puisque les données ne sont pas partagées avec d’autres sessions), mais simplement parce qu’on peut faire un rollback dans notre session.

Et malheureusement cet undo est généré dans le tablespace UNDO qui est permanent, donc protégé par du redo.

En 12c, c’est toujours le comportement par défaut, mais on a la possibilité de faire en sorte que le UNDO généré par des opérations sur des GTT soit écrit dans le tablespace temporaire Et donc de ne plus générer de redo du tout (Sauf un minimum, pour les modifications du dictionnaire par exemple).

Il suffit de positionner le paramètre suivant:

alter session set temp_undo_enabled =true;

Cette fonctionnalité a été introduite pour pouvoir utiliser les GTT dans une standby Active DataGuard (où les datafiles sont ouverts en read-only seulement). Le paramètre est par défaut à FALSE sur une base primaire.

J’ai repris une demo complète qui mesure le redo généré par chaque opération sur une table permanentes et sur une table temporaire. Si on laisse temp_undo_enabled à sa valeur par défaut, il n’y a pas de différence entre le redo généré en 11g et en 12c sur toutes les opérations de cette demo.

12c: Oracle en multi-thread

Publié le par
1

Sous Windows, Oracle tourne avec un seul process et plusieurs threads. Les process Oracle de V$PROCESS sont en fait des threads.
Sous Linux, on est habitué à avoir un process OS pour chaque process Oracle.

La 12c permet d’avoir une architecture multi-thread sous Unix/Linux aussi afin de mieux gérer les ressources:

  • C’est moins couteux de créer un thread qu’un process (normalement, avec un pool de connexion bien configuré, on ne devrait pas voir une forte activité de création de process de toute façon)
  • Les switch de contexte entre threads sont moins couteux qu’entre process car beaucoup de ressources sont partagées entre tous les threads d’un process. On verra la différence lorsque le système commence à être assez chargé (forte utilisation CPU)

Pour celà il faut:

  • Mettre le paramètre threaded_execution=true (nécessite un redémarrage bien sûr)
  • Mettre dedicated_through_broker_LISTENER=ON dans le listener.ora
    Pour que la connection ouvre un thread dans un process serveur au lieu que ce soit le listener qui crée un process.

Attention, c’est un gros changement d’architecture, il y a quelques restriction, la première que vous verrez est qu’il n’est plus possible de se connecter ‘/ as sysdba’. Il faut fournir le user/password.

Un exemple de V$PROCESS dans la demo
Dans V$PROCESS PID est le numéro de process Oracle, SPID est toujours le numéro de process de l’OS (PID), et on a STID qui donne le numéro de thread (LWP) et EXECUTION_TYPE qui dit si c’est un thread ou un process.

12c Création d’une Pluggable Database

Publié le par
Répondre

Dans la Container Database créée dans le dernier post on ne peut pas encore créer de tables.

Il faut maintenant créer une Pluggable Database pour pouvoir travailler dedans comme on avait l’habitude de travailler dans une base avant la 12c.

Et c’est très simple:

create pluggable database MANPDB01 admin user ADMIN identified by oracle;

On a besoin de préciser un utilistateur DBA pour cette Pluggable Database. Et si l’on est pas en OMF pour déterminer le nom des fichiers, on rajoutera DB_FILE_NAME_CONVERT pour convertir les fichers de PDB$SEED vers ceux de notre nouvelle PDB.

Il y a toujours une une grosse incompréhension lorsque un développeur pas trop habitué à Oracle a besoin d’un nouvel environnement et va voir le DBA pour lui demander une nouvelle base. Le développeur a l’impression de demander quelque chose de simple. Le DBA lui doit provisionner un ensemble de ressources et mettre en place tout un tas de procédures.

C’est fini. En 12c, dans un CDB, on peut créer une base en 10 secondes. Pour le DBA c’est une Pluggable Database mais le développeur la voit comme sa propre base.

La demo de cette opération très simple.

L’utilisateur se connectera avec un nom de service spécifique, pour mon exemple:

connect ADMIN/oracle@//92.168.56.112/MANPDB01.pachot.net

Le DBA pourra lui choisir de se connecter à la CDB puis passer sur la Pluggable Database avec:

alter session set container=MANPDB01

12c Création manuelle d’une Container Database

Publié le par
Répondre

Evidemment, le plus simple pour créer une Container Database est de le faire avec DBCA.

La création manuelle diffère de la création d’une 11g ou d’une 12c non-CDB principalement sur les points suivants:

  • Dans init.ora il faudra préciser:
    enable_pluggable_database=true
  • Dans CREATE DATABASE il faudra préciser
    enable pluggable database

    et (sauf si on utilise OMF pur nommer les fichiers) la clause:

    seed file_name_convert=(...)

    pour donner le nom des datafiles de PDB$SEED à partir de ceux de CDB$ROOT

  • il faut utiliser catcon.pl pour lancer les catalog.sql, catproc.sql, etc sur tous les containers

On a alors une ‘Multitenant Container Database’ avec 2 containers:
CDB$ROOT qui contient le dictionnaire et les objets communs.
PDB$SEED une Pluggable Database vide prête à être clonée pour créer des Pluggable Databases

Chaque container a au moins ses tablespaces SYSTEM et SYSAUX et un service pour s’y connecter (sauf PDB$SEED auquel on ne se connecte pas).

Le détail de la création de la CDB sur la demo

Cette base ne sert encore à rien, car la CDB n’est pas faite pour stocker des objets non-Oracle. La prochaine étape sera la création d’une Pluggable Database qui pourra alors être utilisée pour créer ce qu’on veut. Ce sera le sujet du prochain post.