Archives pour la catégorie MicroLearning

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.

12c: Container Database et Pluggable Databases

Publié le par
1

La 12c est sortie (Linux et Solaris):

  • La doc à lire en ligne
  • L’install à télécharger sur OTN

Au delà des nouvelles fonctionnalités, c’est un gros changement d’architecture avec la notion de CDB – Multitenant Container Database

On est habitués à ce que une instance (les process et la mémoire) travaille sur une seule base (les fichiers). En RAC on peut avoir plusieurs instances sur une seule base. Mais contrairement à d’autres SGBD, entre plusieurs bases, on ne partage aucune ressource: chacune a son dictionnaire, l’ensemble des objects Oracle (les dbms_xxx, tables systèmes), chacune a sa mémoire, ses process,…

Bien sûr on pourrait n’avoir que très peu de bases – l’idéal étant de n’avoir qu’une seule instance par serveur. Mais pour cela il faut pouvoir mettre les schémas de plusieurs applications dans une même base. C’est tout à fait possible et Oracle permet aussi bien de les séparer logiquement (par les schemas) et physiquement (par les tablespaces).

Au niveau physique, il est assez facile de gérer l’ensemble des tablespaces d’une application: on peut les backuper indépendamment, les transporter avec Transportable Tablespace, etc. et au niveau logique tout va bien si l’application accepte de ne pas être toute seule sur la base. Mais ce n’est pas le cas. Beaucoup de logiciels imposent les noms de schemas (donc on ne peut pas en avoir deux sur une même base), créent des objets publics (db links, synonymes) qui empêchent de mutualiser les ressources.

En 12c Oracle a étendu le concept de Transportable Tablespaces (ensemble de tablespaces ‘self-contained’ et export des meta-données du dictionnaire qui vont avec) avec le concept de Pluggable Databases. En y rajoutant tout pour que l’application croit être toute seule sur sa base. La Pluggable Database a son tablespace SYSTEM avec les metadonnées des tables de l’application, ses tables, ses users, etc. et des liens vers les metadonnées communes.

On a donc plusieurs containers dans une seule database gérée par une seule instance, et chacun apparaît comme une base:
Chaque Pluggable Database sur laquelle se connecte l’appli est un Container. L’application peut y faire ce qu’elle veut comme si elle était toute seule sur une base.
Les objets communs sont dans un container racine CDB$ROOT. Il y a en plus une Pluggable Database PDB$SEED en read-only qui permet de créer une nouvelle PDB facilement en la clonant – fini les longs catalog.sql/catproc.sql pour chaque création de base. Les développeurs vont pouvoir demander une nouvelle base sans avoir l’impression de demander un nouveau datacenter.

Une base qui peut contenir plusieurs conteneurs – donc plusieurs Pluggable Databases, c’est une ‘Multitenant Container Database’ ou CDB.
Bien sûr on peut continuer à créer des bases non-CDB, comme avant, sans cette notion de Pluggable Database. Elle n’ara alors qu’un seul conteneur.

c’est au CREATE DATABASE que ça se décide avec ENABLE PLUGGABLE DATABASE.

Au prochain post, la création d’une Container Database…

Requêtes de pagination ‘Top-N’

Publié le par
Répondre

On a souvent besoin d’afficher la première page d’un résultat (par exemple les 10 premières lignes) et éventuellement les pages suivantes avec des requêtes indépendantes. En attendant la 12c (très bientôt) qui implémentera la syntaxe standard qui commence à se normaliser sur tous les SGBD (OFFSET … FETCH FIRST/NEXT … ROWS) il y a plusieurs techniques possibles mais toutes n’ont pas les mêmes performances.

La démo des différentes possibilités avec plans d’exécution: demo

Déjà commençons avec deux mauvaises idées:

  • Ne pas mettre d’ORDER BY en espérant que le résultat revient trié.
    Sans ORDER BY, l’ordre du résultat n’est pas prédictible. Même s’il on passe par un index. Il faut mettre la clause ORDER BY et le tri sera évité par Oracle si l’index correspondant le permet.
  • Faire une requête normale (avec ORDER BY) et ne limiter le résultat qu’au moment du Fetch.
    Le résultat sera juste, mais les performances moins bonne que si l’optimiseur connait à l’avance le nombre de lignes dont on a besoin.(cf. plans)

rownum
La technique la plus ancienne est de faire le ORDER BY dans une sous-requêtes, puis limiter le résultat avec ROWNUM:
Exemple:
les 10 premiers enregistrements d’un contrat:

select * from (select * from TEST where contract_id=500 order by start_validity  asc) where rownum<=10;

C’est la solution qui fonctionne depuis longtemps et probablement la plus utilisée (par Hibernate par exemple).

Mais la doc 11gR2 précise cependant: The ROW_NUMBER built-in SQL function provides superior support for ordering the results of a query

row_number()
Avec la fonction analytique row_number() on compte les lignes dans une sous-requête puis on limite le résultat.

select /*+ FIRST_ROWS */ * from (select test.*,row_number()over(order by start_validity) rn from test where contract_id=500) where rn<=10  order by start_validity;

A noter:

– je lance la requête en FIRST ROWS car je je souhaite afficher rapidement ces 10 premières lignes. Avec ROWNUM, c’était implicite – le CBO passait en optimisation FIRST_ROWS(10). L’accès par INDEX RANGE SCAN est donc probablement préférable.

– j’ai utilisé FIRST_ROWS et non FIRST_ROWS(10) car sinon dans mon exemple, FULL TABLE SCAN était choisi (peut être un bug en 11.2.0.3). La bonne technique devrait être FIRST_ROWS(n).

– j’ai un index sur (contract_id,start_validity) qui peut être utilisé pour éviter un tri car ce sont des NUMBER. Avec des VARCHAR2 le plan d’exécution peut dépendre des paramètres NLS car l’ordre de l’index (binary) n’est peut-être pas le même que celui de la langue.

Le conseil: tester et vérifier les plans d’exécution et le volume nécessaire au tri sur des données représentatives afin de valider les hints et indexes.

Fonctions Analytiques: chevauchement des plages de dates

Publié le par
Répondre

Sans les fonctions analytiques, on ferait une jointure de la table avec elle-même pour voir les lignes adjacentes. Ici on utilise LEAD() et LAG() pour les voir en une seule passe avec une bien meilleur performance.

Sur une table CONTRACTS avec un contract_id et les plages de validité start_validity,end_validity la requête suivante donne les plages de dates qui se recoupent.

select contract_id,start_validity,end_validity from (
	    select
		   contract_id,start_validity,end_validity
		   ,lag  (end_validity)  over (partition by contract_id order by start_validity,end_validity) lag_end_validity
		   ,lead (start_validity)over (partition by contract_id order by start_validity,end_validity) lead_start_validity
	    from CONTRACTS
) where start_validity <= lag_end_validity or end_validity >>= lead_start_validity
order by 1,2,3
;

Le select imbriqué rajoute les dates de de validité suivantes (LEAD) et précédentes (LAG) pour chaque contrat (PARTITION BY) dans l’ordre chronologique (ORDER BY) et le select principal peut les comparer.

Démo avec plans d’exécution: demo

Sur un gros volume, le FULL TABLE SCAN unique et le WINDOW SORT sont des opérations optimales, faisant des i/o larges, possibilité de Parallel Query, de SmartScan sur Exadata,… alors que la solution jointure fait un accès par index très peu scalable.