ArKZoYd: 04/2010

30 avril 2010

Le patch 10.2.0.5 pour Oracle Database est dehors

Au moins les versions Linux x86 et x86_64; il peut être téléchargé sur My Oracle Support, sous le numéro 8202632 ; Happy Patching Week-End !

Configurer Streams avec Oracle Enterprise Manager Grid Control 11g

Depuis la version 10.2.0.5 d'Enterprise Manager, Oracle Streams peut être configuré depuis le Grid Control. Je vous propose d'illustrer quelques unes de ces possibilités à travers la mise en place de la réplication monodirectionnelle d'une table entre 2 bases de données. Bien sur, certaines choses peuvent encore progresser et en particulier pour couvrir l'étendue des possibilités offertes par Streams. Enfin, il faut reconnaître à Oracle que "ça a de la gueule". Assez disserté, plongeons dans les possibilités offertes par Enterprise Manager

Remarques préliminaires:
Pour mettre en oeuvre Streams, certains pré-requis sont nécessaires. Ceux-ci sont à compléter par le fait que vous ne pouvez pas créer n'importe quel type de configuration Streams depuis l'assistant de configuration. Les scripts générés correspondent, en effet, soit à une configuration "Downstreams Capture", soit à l'utilisation d'une des procédures MAINTAIN_TABLES, MAINTAIN_SCHEMAS, MAINTAIN_TABLESPACES ou MAINTAIN_GLOBAL de DBMS_STREAMS_ADM. Vous ne pourrez donc pas, encore et par exemple, mettre en oeuvre une configuration utilisant la capture synchrone depuis Grid Control. D'un autre côté, toutes les configurations peuvent être supervisées, y compris les plus complexes et EM offre de superbes interfaces avec sur les topologies et sur Streams Performance Advisor (i.e. les packages UTL_SPADV et DBMS_STREAMS_ADVISOR_ADM).

Configuration de la réplication d'une table

Commençons par un exemple simple; nous allons mettre en place la réplication monodirectionnelle d'une table entre deux bases de données. Cette configuration est assez représentative des possibilités offertes par les assistants de configuration de Streams et surtout très simple.

Avant de commencer, vérifiez quelques éléments de configuration :

La base de données doit être en mode archivelog
Créez un tablespace STREAMS_TBS pour les files d'attentes de l'administrateur Streams
On supposera que la table existe sur la base de données source (e.g. SCOTT.DEPT sur BLACK) et qu'elle n'existe pas sur la base de données cible (e.g. WHITE). On supposera également que l'utilisateur propriétaire de la table existe sur la destination. J'ai donc créé l'utilisateur SCOTT à cet effet.

Première bonne surprise, la création de l'administrateur Streams est pris en charge par l'assistant et vous pouvez même créer tous les administrateurs de toutes vos bases de données en une seule opération. Pour cela, sélectionnez "Data Movement -> Streams -> Setup" puis, si vous n'êtes pas connecté en tant qu'Administrateur Streams, vous pourrez sélectionner "Streams Administrator User". Remplissez alors le formulaire de création des administrateurs Streams pour toutes vos bases de données comme ci-dessous :

Vous l'aurez compris pour configurer Streams, il faut être connecté avec un Administrateur Streams. Sélectionnez le menu "Logout " reconnectez-vous à votre base de données avec l'administrateur Streams. Une fois l'opération effectuée, vous pouvez lancer l'assistant de configuration de la réplication de tables à partir du menu "Data Movement -> Streams -> Setup" comme ci-dessous:

Laissez-vous ensuite guider dans la configuration:

Choisissez une ou un ensemble de table à répliquer sur votre base de données:

Sélectionnez ensuite les espaces pour stocker les fichiers d'export/import datapump ainsi que les options de configuration avancées de Streams :

Sélectionnez quand l'opération sera réalisée :

Enfin, validez l'ensemble des opérations qui seront effectuées à travers le récapitulatif proposé. Vous pouvez même accéder et de modifier les scripts générés pour l'occasion :

Une fois la réplication en place, n'oubliez pas de tester son fonctionnement.

Gérer vos configurations Streams

Vous pouvez visualiser votre configuration Streams à partir du menu "Data Movement -> Streams -> Manage Replication. Les différents écrans qui s'appuient sur DBMS_STREAMS_ADVISOR_ADM et les vues associées permettent de suivre le fonctionnement d'Oracle Streams sur la base de données locale comme vous pouvez vous en rendre compte ci-dessous:

Vous pouvez aller encore plus loin grâce au menu Advisor Central -> Streams Performance Advisor qui offre, en s'appuyant sur le package UTL_SPADV, une vue globale de la configuration et des performances associées au niveau global :

L'assistant vous présente également un niveau détaillé pour chacun des composants Streams sur l'ensemble des bases de données impliquées :

Superviser Oracle Streams

Enterprise Manager offre également un ensemble de mesures et d'alertes associées à Streams. Vous décrouvrirez ces mesures en explorant l'ensemble des mesures disponibles pour votre base de données :

Pour se rendre compte du fonctionnement de la supervision de Streams, vous pouvez, par exemple, faire échouer un process. Etant donné que nous avons mis en place une configuration monodirectionnelle, un moyen simple consiste à générer un conflit en adaptant le script ci-dessous à votre contexte :

-- Sur la base destination, modifier les données
-- pour que la modification à suivre détecte un conflit et échoue
connect scott@white
update dept set LOC='PARIS'
where deptno=10;

commit;
-- Sur la base source, modifiez les données
-- qui se propagera jusqu'à arriver en conflit et faire échouer le process d'apply
connect scott@black
update dept set LOC='PARIS'
where deptno=10;

commit;

Après quelques secondes (sauf si votre process d'apply ignore les erreurs), vous découvrirez une alerte sur votre base de données destination indiquant que le processus d'apply a échoué:

Conclusion

Pour ce qui est de résoudre le problème, ne cherchez pas, vous ne pouvez pas encore le faire depuis la console ! Il faut interroger la vue DBA_APPLY_ERROR et utiliser le package DBMS_APPLY_ADM de manière adéquate :

Il faut bien continuer à justifier nos salaires. Il n'empêche, quand on fait le bilan de ce qui est désormais disponible et surtout d'où on vient, on ne peut qu'applaudir avec enthousiasme. Je reste curieux de voir comment GoldenGate pourra exploiter les forces de Streams dans la prochaine version... C'est bien de ça dont il s'agit ?

24 avril 2010

Oracle Enterprise Manager 11.1 en Action...

Ce n'est plus vraiment une news... Oracle Enterprise Manager 11g est disponible sur Linux x86 et x86_64 ! Publiée puis dé-publiée puis re-publiée, la documentation associée est également disponible sur Oracle Technology Network. Téléchargez-la vite avant une éventuelle re-dé-publication ;-).

J'ai eu l'occasion d'explorer le produit et , côté base de données, 2 nouveautés très intéressantes :

D'abord, le support complets des SQL Profiles, des SQL Performance Management Baselines et SQL Patchs (cf copie d'écran ci-dessous)
L'intégration directe à My Oracle Support depuis la console

J'en ai longuement discuté avec mon client du moment et on attaque la production dès lundi...

Protégez vos sessions et bien plus avec SCREEN

Si vous n'utilisez pas screen au quotidien, c'est, soit que vous n'en avez pas entendu parlé, soit que vous n'êtes pas administrateur. A l'instar de nohup, screen vous permet de ne pas perdre le fil de vos scripts et opérations en cours. Non seulement screen est beaucoup plus simple d'utilisation que nohup, mais la commande permet de travailler sans script, de collaborer, utiliser plusieurs terminaux et même de tracer vos opérations. Je vous propose de trouver quelques cas d'utilisation de screen si dessous.

Perdre et reprendre ses sessions

L'utilisation de screen est extrêmement simple. Pour débuter, lorsque vous êtes connecté à un serveur Linux (ou Unix avec screen installé), tapez simplement :

screen

Et voilà : vous ne pouvez plus perdre votre session, sauf si, bien sur, vous sortez de screen avec la commande exit. Faites maintenant l'expérience suivante, tapez quelques commandes (ls, rm, mkfs, etc...) puis fermez brutalement votre terminal ou votre session Putty. Pour récupérer les opérations en cours, reconnectez vous avec le même utilisateur à votre serveur Linux et repérez les sessions screen en cours avec la commande ci-dessous :

screen -ls
There is a screen on:
 19693.pts-0.arkzoyd-easyteam (04/24/2010 12:57:58 PM) (Detached)
1 Socket in /var/run/screen/S-root.

Pour vous attacher de nouveau à votre session perdue, tapez :

screen -r 19693.pts-0.arkzoyd-easyteam

ou plus simplement:

screen -r pts-0

Quelques Notes:
Vous pouvez nommer votre session screen avec -S pour la retrouver plus facilement
Vous pouvez vous détacher de votre session à tout moment pour vous y attacher de nouveau plus tard en tapant Ctrl+a puis Ctrl+d

Partager une session à plusieurs

Une autre fonctionnalité intéressante de screen est la capacité de s'attacher à plusieurs au même écran. Pour en faire l'expérience, lancez 2 terminaux connecté au même utilisateurs sur votre serveur Linux. Sur le premier terminal, tapez

screen

sur le second, repérez la session screen puis utilisez screen -x pour vous y attacher :

screen -ls
There is a screen on:
 20697.pts-0.arkzoyd-easyteam (04/24/2010 02:52:32 PM) (Attached)
1 Socket in /var/run/screen/S-root.

screen -x -r 20697.pts-0.arkzoyd-easyteam

Tapez alors des commandes dans l'un et l'autre de vos terminaux et constatez qu'ils sont effectivement partagés. Vous pouvez faire la même chose depuis 2 ordinateurs situés chacun à l'autre bout du monde.

Journaliser ses opérations

Si vous êtes administrateur, enregistrez l'ensemble des opérations que vous effectuez est souvent très utile. Bien sur, vous pouvez utiliser la commande script. Vous pouvez surtout utiliser l'option -L de screen qui génère un fichier journal. Vous pouvez également à tout moment activer la journalisation à l'aide des combinaisons de touche Ctrl+a puis H.

Pour en savoir plus

Si vous ne connaissiez pas encore, screen deviendra sans doute bientôt une de vos commandes préférée. Pour en savoir plus, utilisez la combinaison de touche Ctrl+a puis ?, tapez man screen ou encore info screen. Aucun doute que vous personnaliserez bientôt vos environnements avec les fichiers de ressources

23 avril 2010

Oracle Enterprise Linux 5.5 et Ubuntu sur 2 partitions différentes

Si vous avez une partition de votre laptop, par exemple, sur laquelle vous voulez installer Oracle EL et que vous avez déjà une configuration GRUB, il suffit de l'installer sans GRUB et de modifier le fichier de configuration du bootloader (e.g. /boot/grub/menu.lst sous Ubuntu). L'entrée que vous ajouterez ressemblera à celle ci-dessous :

# This entry is added for OEL or RHEL 5.5 on /dev/sda4
title           Oracle Enterprise Linux 5 Update 5
root            (hd0,3)
kernel          /boot/vmlinuz-2.6.18-194.el5PAE root=LABEL=/ ro rhgb quiet
initrd          /boot/initrd-2.6.18-194.el5PAE.img

Vous changerez les versions du noyau et surtout le root de démarrage qui indique la partition de boot :

hd0 pour indiquer le premier disk (hard disk)
3 pour indiquer la 4e partition puisque la numérotation commence à 0

J'en entends déjà crier VirtualBox mais pour des démonstrations des produits Oracle sur un laptop, rien ne vaut, parfois encore, le métal nu.

16 avril 2010

Bug de la documentation 11gR2 "DBMS_XPLAN.DISPLAY_CURSOR"

Contrairement à ce qui est annoncé dans la documentation, DBMS_XPLAN.DISPLAY_CURSOR n'affiche pas l'ensemble des curseurs dans la shared pool lorsque vous ne précisez que le paramètre sql_id. La section ci-dessous extraite de la documentation est juste fausse:

child_number
Child number of the cursor to display. If not supplied, the execution plan of all cursors matching the supplied sql_id parameter are displayed. The child_number can be specified only if sql_id is specified.

En fait, elle est fausse à 2 titres :

d'abord le nom du paramètre n'est pas child_number mais cursor_child_no
ensuite la valeur par défaut n'est pas null mais 0

Pour vérifier ces 2 points, exécutez la requête ci-dessous et faite l'extraction de la section qui vous intéresse :

select text
  from dba_source
 where type='PACKAGE'
   and name='DBMS_XPLAN'
 order by line;

[...]

-- display from V$SQL_PLAN (or V$SQL_PLAN_STATISTICS_ALL)
function display_cursor(sql_id           varchar2 default  null,
                        cursor_child_no  integer  default  0,
                        format           varchar2 default  'TYPICAL')
return dbms_xplan_type_table
pipelined;

[...]

Pour afficher l'ensemble des curseurs pour une requête en 11.2, tapez donc

select *
  from table(dbms_xplan.display_cursor('&sql_id',null));

et non pas

select *
  from table(dbms_xplan.display_cursor('&sql_id'));

Remarquez que ça fait plusieurs fois que je rencontre un problème où tous les curseurs ne sont pas affichés avec cette fonction, y compris dans Oracle Enterprise Manager GridControl. En 10.2.0.4, le problème était bien pire... On imagine qu'ils ont corrigé le code mais pas la documentation.

Index B*Tree, INDEX FULL SCAN et fonctions dans une clause WHERE

Commençons par un peu de sémantique ; quel est d'après vous la différence entre les 2 clauses WHERE ci-dessous ?

"X like 'A%'"
"substr(X,1,1)='A' and X IS NOT NULL"

Il ne me semble pas qu'il y en ait aucune. Notez que je ne demande qu'à me tromper, mais l'opérateur like se comporte comme l'opérateur = vis à vis des valeurs NULL; en outre le jeux de caractère n'impacte pas la fonction substr.

Et pourtant, il existe une différence de taille, ainsi si X est une colonne de type caractères et qu'il existe un index, la première clause pourra utiliser un algorithme INDEX RANGE SCAN tandis que la seconde pourra uniquement utiliser un algorithme INDEX FULL SCAN. Le premier ne parcourant dans la plupart des cas qu'une partie de l'index tandis que le second comme son nom l'indique parcourt l'ensemble de l'index.

Vous n'y croyez pas ?

Un petit exemple pour vous persuader...

(*) Note:
Oracle n'est pas capable d'anticiper l'équivalence entre ID IS NOT NULL et substr(id,1,1) IS NOT NULL, d'où l'ajout du prédicat "X IS NOT NULL" dans ma condition

create table scott.T(id varchar2(10),
              text varchar2(4000))
tablespace users;

insert into scott.T
(select dbms_random.string(null,1)||'A'||dbms_random.string(null,8),
   rpad('Z',1000,'Z')
from dual
connect by level<=10000);

commit;

exec dbms_stats.gather_table_stats('SCOTT','T',method_opt=>'for all columns size 254');

create index scott.tidx on scott.t(id) tablespace users;

select /*+ INDEX(T TIDX) */ count(text)
from SCOTT.T
where substr(id,1,1) = 'B' and id is not null;

COUNT(TEXT)
-----------
   371

set lines 150 pages 1000
select * from table(dbms_xplan.display_cursor(format=>'LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2rpx4at6w6z8g, child number 1
-------------------------------------
select /*+ INDEX(T TIDX) */ count(text)   from SCOTT.T  where
substr(id,1,1) = 'B' and id is not null

Plan hash value: 1193005838

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |   132 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |  1012 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |   100 |    98K|   132   (0)| 00:00:02 |
|*  3 |    INDEX FULL SCAN           | TIDX |   100 |       |    32   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter((SUBSTR("ID",1,1)='B' AND "ID" IS NOT NULL))

Et maintenant, observons ce qui se passe avec une clause like 'B%'

select count(text)
from SCOTT.T
where id like 'B%';

COUNT(TEXT)
-----------
       371

set lines 150 pages 1000
select * from table(dbms_xplan.display_cursor(format=>'LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  50yxmygfa1xc0, child number 0
-------------------------------------
select count(text)   from SCOTT.T  where id like 'B%'

Plan hash value: 196377347

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |   358 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |  1012 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |   354 |  349K |   358   (0)| 00:00:05 |
|*  3 |    INDEX RANGE SCAN          | TIDX |   354 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID" LIKE 'B%')
filter("ID" LIKE 'B%')

Vous apprécierez, j'en suis sur, le fait que le coût du 2nd plan est plus élevé alors qu'en réalité les performances sont relativement identiques... Techniquement parlant, le premier plan est même moins bon !

Et maintenant, le 2nd effet f(X)

Si maintenant, vous exécutez la première requête sans hint, surprise; l'index n'est pas utilisé :

select count(text)
from SCOTT.T
where substr(id,1,1) = 'B' and id is not null;

COUNT(TEXT)
-----------
 371

set lines 150 pages 1000

select * from table(dbms_xplan.display_cursor(format=>'LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  gb56bxddtsp2p, child number 0
-------------------------------------
select count(text)   from SCOTT.T  where substr(id,1,1) = 'B' and id is
not null

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   410 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |  1012 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   100 |    98K|   410   (1)| 00:00:05 |
---------------------------------------------------------------------------

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

2 - filter((SUBSTR("ID",1,1)='B' AND "ID" IS NOT NULL))

Et ceci malgré le fait que le coût de ce plan est de manière évidente plus élevé que le coût du plan avec l'index. La raison est dans la trace 10053 et identique à celle déjà présenté avec un index reverse et un index range scan dans mon article précédent. Le plan est juste ignoré. Pour vous en persuader, regardez le fichier généré ci-dessous :

alter session set events '10053 trace name context forever, level 1';

select /* TEST */ count(text) from SCOTT.T
where substr(id,1,1) = 'B'
and id is not null;

alter session set events '10053 trace name context off';

select tracefile from v$process p, v$session s
where s.sid=sys_context('USERENV', 'SID')
and p.addr=s.paddr;
TRACEFILE
---------------------------------------------------------------
/u01/app/oracle/diag/rdbms/black/BLACK/trace/BLACK_ora_3998.trc

Conclusion:

Supprimez la table :

drop table scott.t purge;

Et tentez d'expliquer ça à un développeur, VOUS !

14 avril 2010

optimizer_dynamic_sampling=3+

Préambule:
Il y a (au moins) un gros contre-sens dans cet article. Merci à Chris Antognini pour ses corrections. Je propose néanmoins de laisser cet article tel quel. Lisez bien les commentaires pour comprendre de quoi il parle. Cela me rappellera que je raconte souvent des c... et ça vous invitera à garder un esprit critique sur ce que j'écris parfois.

Si vous ne l'utilisez pas en précisant une table, le hint dynamic_sampling, comme le paramètre optimizer_dynamic_sampling ont 2 effets : d'abord l'aggressivité, c'est à dire le nombre de blocs explorés, avec laquelle l'échantillonnage dynamique des statistiques est réalisé lors du hard parse; ensuite, selon le niveau, les tables/prédicats sur lesquels l'échantillonnage est réalisé. Pour savoir avec précision et de manière directe les requêtes imbriquées lancées par l'optimiseur, activez la trace 10053.

Concernant les tables/prédicats évalués dans le cadre de l'échantillonnage dynamique, si les spécificités des niveaux 1 et 2 (les tables sans statistiques) et du niveau 4 (les tables dont 2 colonnes ou plus sont impliquées dans les prédicats de la requête) sont explicites, le niveau 3 est plus obscur... La documentation dit en effet, dans un anglais péremptoire : "All tables for which standard selectivity estimation used a guess for a predicate that is a potential dynamic sampling predicate". Soyons honnête, cette phrase, n'est pas des plus limpide et pourtant je parle anglais.

Je vous propose d'illustrer à partir de quelques exemples ce que cela signifie en réalité. Cela dit, même si je comprends bien l'idée exprimée, écrire une phase en français qui ait un sens est un défi que je ne relèverai pas.

Le principe fondateur

Le principe de départ est assez simple et d'ailleurs très bien décrit dans cet article de Tom Kyte. Il s'agit de constater que dans certains cas, la cardinalité d'une étape du plan de la requête ne peut pas être déduit des statistiques collectées sur les colonnes en jeu; Oracle, dans ce cas, joue aux dés. L'exemple qui vient immédiatement à l'esprit est l'utilisation d'une fonction. En effet Oracle n'est pas capable (je sens la déception monter !), ayant les statistiques de distribution d'une colonne X d'estimer les statistiques de f(X)... Et cela, même si elle a le code de la fonction f ou que la fonction est built-in. Elle ne peut donc que "deviner" la sélectivité. En bien le niveau 3 permet justement de changer le comportement de l'optimiseur et de transformer une divination en estimation au moyen d'un échantillonnage dynamique. Je vais illustrer cet effet.

Un exemple

Pour illustrer le fonctionnement du niveau 3 du paramètre optimizer_dynamic_sampling, nous allons commencer par créer une table exemple dans le schéma SCOTT :

drop table scott.t purge;

create table scott.T(id varchar2(20),
                   num number,
                   text varchar2(1000)) tablespace users;

insert into SCOTT.T(id, num, text)
select 'Z'||'A'||dbms_random.string(null, 8),
       rownum,
       rpad('Z',1000,'Z')
  from dual connect by level <=100000;  commit;  exec dbms_stats.gather_table_stats('SCOTT', 'T', method_opt=>'for all columns size 254')

Dans un premier temps, regardons le plan et la cardinalité retournée par un plan avec une clause WHERE simple :

set pages 1000 lines 180

select count(text)
from scott.T
where num=1;

COUNT(TEXT)
-----------
        1

select * from table(dbms_xplan.display_cursor(format=>'LAST +NOTE'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  ddt4d158qutbm, child number 0
-------------------------------------
select count(text)   from scott.T  where num=1

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4122 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |  1006 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |  1006 |  4122   (1)| 00:00:50 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter("NUM"=1)

Comme vous pouvez vous en rendre compte, l'estimation dans ce cas est cohérente avec la réalité puisque l'optimiseur estime qu'une seule ligne est retournée... mais ce cas est assez simple; essayez avec une fonction dans la clause WHERE :

select count(text)
from scott.T
where mod(num,2)=1;

COUNT(TEXT)
-----------
    50000

select * from table(dbms_xplan.display_cursor(format=>'LAST +NOTE'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5kyjptmkcv6g4, child number 0
-------------------------------------
select count(text)   from scott.T  where mod(num,2)=1

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4123 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |  1006 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  1000 |   982K|  4123   (1)| 00:00:50 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter(MOD("NUM",2)=1)

Comme vous vous en rendez compte, dans ce cas, la cardinalité retournée est très loin de la réalité. Si vous utilisez le hint dynamic_sampling, en revanche la cardinalité est estimée avec beaucoup plus de précision lors de la phase de hard parse :

select /*+dynamic_sampling(3)*/ count(text)
from scott.T
where mod(num,2)=1;

COUNT(TEXT)
-----------
    50000

select * from table(dbms_xplan.display_cursor(format=>'LAST +NOTE'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  ac5kg2brvt5xh, child number 0
-------------------------------------
select /*+dynamic_sampling(3)*/ count(text)   from scott.T  where
mod(num,2)=1

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4123 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |  1006 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 49770 |    47M|  4123   (1)| 00:00:50 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter(MOD("NUM",2)=1)

Note
-----
 - dynamic sampling used for this statement (level=3)

Tout semble logique...

Et maintenant, surprise !

Vous croyez avoir tout compris ? Essayez de comprendre ce qui suit :

select /*+dynamic_sampling(3)*/ count(text)
from scott.T
where mod(num,2)=3;

select * from table(dbms_xplan.display_cursor(format=>'LAST +NOTE'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bs94bgtcbvjvu, child number 0
-------------------------------------
select /*+dynamic_sampling(3)*/ count(text)   from scott.T  where
mod(num,2)=3

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4123 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |  1006 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  1000 |   982K|  4123   (1)| 00:00:50 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter(MOD("NUM",2)=3)

Et oui, la note relative à l'échantillonnage dynamique n'apparait pas ;-). La réponse est dans la 10053, comme vous le verrez ci-dessous :

alter session set events '10053 trace name context forever, level 1';

select /*+dynamic_sampling(3) test2*/ count(text)
from scott.T
where mod(num,2)=3;

alter session set events '10053 trace name context off';

col tracefile format a100 new_value tracefile

select tracefile
from v$process p, v$session s
where p.addr=s.paddr
 and s.sid=sys_context('USERENV', 'SID');

TRACEFILE
---------------------------------------------------------------
/u01/app/oracle/diag/rdbms/black/BLACK/trace/BLACK_ora_2678.trc

!vi &&tracefile

*** 2010-04-13 20:26:10.964
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 3).

*** 2010-04-13 20:26:10.964
** Generated dynamic sampling query:
  query text :
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, CASE WHEN MOD("T"."NUM",2)=3 THEN 1 ELSE 0 END AS C2 FROM "SCOTT"."T" SAMPLE BLOCK (0.203988 , 1) SEED (1) "T") SAMPLESUB

*** 2010-04-13 20:26:10.965
** Executed dynamic sampling query:
  level : 3
  sample pct. : 0.203988
  actual sample size : 217
  filtered sample card. : 0
  orig. card. : 100000
  block cnt. table stat. : 15197
  block cnt. for sampling: 15197
  max. sample block cnt. : 32
  sample block cnt. : 31
  min. sel. est. : 0.01000000
** Not using dynamic sampling for single table sel. or cardinality.
DS Failed for : ----- Current SQL Statement for this session (sql_id=34ctndsfp77ss) -----
select /*+dynamic_sampling(3) test2*/ count(text)
from scott.T
where mod(num,2)=3
Table: T  Alias: T
  Card: Original: 100000.000000  Rounded: 1000  Computed: 1000.00  Non Adjusted: 1000.00
Access Path: TableScan
  Cost:  4122.64  Resp: 4122.64  Degree: 0
    Cost_io: 4118.00  Cost_cpu: 150244524
    Resp_io: 4118.00  Resp_cpu: 150244524
Best:: AccessPath: TableScan
       Cost: 4122.64  Degree: 1  Resp: 4122.64  Card: 1000.00  Bytes: 0

***************************************

Quelques explication de ce qui précède :

"actual sample size : 217" indique que les 31 blocs échantillonnées représentent 217 lignes de la table
"filtered sample card. : 0" indique que des 217 lignes testée, 0 sont retournées par la condition "MOD("T"."NUM",2)=3" soit une sélectivité de 0/217=0
"min. sel. est. : 0.01" indique que pour être retenue, la sélectivité doit être de 1% des lignes soit, la table contenant 100,000 lignes, 1000 lignes
"DS Failed" indique que l'évaluation du dynamic sampling ne peut pas être retenu; la sélectivité renvoyée étant inférieure à 1%, c'est la valeur "divinatoire" de 1% qui sera retenue soit 1000 lignes pour la cadinalité

On constate donc que dans ce cas, même si l'optimiseur utilise un échantillonnage dynamique, il ne considère pas le résultat associé. La raison apparaît clairement dans la trace : la sélectivité est inférieure à la sélectivité minimale choisie par l'optimiseur de 1%. Ce fonctionnement est un garde fou contre les erreurs de plans du à une sous-estimation par l'échantillonnage. Celui-ci vise, en effet, à favoriser la détection d'une sélectivité faible et pas l'inverse. C'est d'ailleurs pour cela que la note "dynamic sampling" n'apparait pas dans le plan. Même si celui-ci est calculé, n'est pas considéré. Vous noterez donc qu'on retrouve dans ce cas la même valeur de cardinalité que celle retournée par le plan sans utilisation de dynamic_sampling de notre premère requête avec "MOD("T"."NUM",2)=1".

Un autre cas...

Les cas où l'optimiseur est réduit à deviner la sélectivité ne se restreint pas à l'utilisation de fontions; commençons par un exemple avec une clause "id like 'Z%'". Vous constaterez que l'utilisation du dynamic_sampling n'a aucun effet. Vous pourrez le vérifier ci-dessous ou avec une trace 10053 (par vous même) :

set pages 1000 lines 130

select /*+dynamic_sampling(3) test2*/ count(text)
from scott.T
where id like 'Z%';

select * from table(dbms_xplan.display_cursor(format=>'LAST +NOTE'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  a98fydtxy86qn, child number 0
-------------------------------------
select /*+dynamic_sampling(3) test2*/ count(text)   from scott.T  where
id like 'Z%'

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4122 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |  1012 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   100K|    96M|  4122   (1)| 00:00:50 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter("ID" LIKE 'Z%')

En revanche dans le cas de la clause "id like '_A%'" qui ne peut pas être estimé, l'optimiseur utilisera bien le dynamic sampling :

set pages 1000 lines 130
select /*+dynamic_sampling(3) test2*/ count(text)
from scott.T
where id like '_A%';

select * from table(dbms_xplan.display_cursor(format=>'LAST +NOTE'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  gycbg9xcu4w1q, child number 0
-------------------------------------
select /*+dynamic_sampling(3) test2*/ count(text)   from scott.T  where
id like '_A%'

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |  4122 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |  1012 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   100K|    96M|  4122   (1)| 00:00:50 |
---------------------------------------------------------------------------

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

 2 - filter(("ID" LIKE '_A%' AND "ID" IS NOT NULL))

Note
-----
 - dynamic sampling used for this statement (level=3)

Conclusion

Et voilà qui éclaire, je l'espère, une phrase un peu mystérieuse de la documentation. Mais au fait, si vous utilisez cette fonctionnalité, évitez si possible de la positionner au niveau de l'instance et surtout sur les environnements OLTP. Enfin, ce que j'en dis, c'est surtout si vous voulez favoriser la stabilité de vos applications. Si vous aimez pouvoir faire appel à des consultants qui vous raconte des choses qui ne vous serviront plus jamais... faites-le

13 avril 2010

Téléchargez Oracle Enterprise Manager Grid Control 11.1

Depuis E-Delivery... pour Linux x86 et x86_64 et en attendant qu'ils re-publient la documentation mystérieusement disparue il y a quelques jours ; ETA 2:45.

12 avril 2010

Comment combattre les erreurs humaines

J'ai travaillé sur des environnements Oracle en production pendant plusieurs années, y compris sur des environnements très critiques. J'ai rencontré des problèmes matériels sur des serveurs, sur des SAN, sur des switchs. J'ai vécu plusieurs corruptions sur NFS ou dues à des bugs Oracle. J'ai également vécu coupures d'électricité, inondations (y compris au 4e étage) et déclenchements intempestifs des sécurités incendies.

Quoiqu'il en soit et malgré tout, il est évident que la première cause d'indisponibilité est, et de loin, l'erreur humaine. Préparés ou non... avec les meilleurs experts du monde, les changements voulus ou accidentels sont l'ennemi #1 de la disponibilité des systèmes. Ma récente expérience ne peut que confirmer ce que je vous dis et pourtant, je n'y suis pour rien.

Et bien (en réponse aux incrédules!) croyez-le ou non, il est possible de combattre les erreurs humaines... certes avec des technologies comme les snapshots, les standby ou les technologies de flashback mais surtout avec un peu de méthode, beaucoup de bon sens et de la rigueur. Parmi ces trucs qui vous permettent d'augmenter la disponibilité de vos systèmes, on peut citer par exemple :

La prudence et la tempérance (vertus cardinales)
Les tests systématiques des sauvegardes et plans de reprise
La formalisation et la validation des changements
La ségrégation des accès
L'analyse et gestion spécifique des risques associés aux changements
La mise en place d'une gestion et d'un suivi des changements
L'élaboration de plans de retour arrière

Dans ce domaine, petits et grands, y compris moi-même, ont encore beaucoup à apprendre. Il y a quelques années, j'ai découvert sous d'autres cieux, une solution qui permet, d'enregistrer ce qui se passe sur vos systèmes à travers la mise en place de passerelles dont l'utilisation est conservée. Cette solution, Observe-IT est la seule que j'ai trouvée encore aujourd'hui pour ce type d'approche.

Si vous êtes un adepte des caméras dans les lycées, vous apprécierez d'en mettre également au bureau. Quoique qu'éthiquement très discutable, cela permet d'expliquer la cause de certains problèmes et de les prévenir. Vous apprécierez... ou pas ! Reste que si je n'étais pas complètement foiré, je mettrais un peu d'argent dans une société comme celle-là.

Et vous, comment combattez-vous les erreurs humaines ?

10 avril 2010

Positionnez un hint grâce au "SQL Profile" par Kerry Osborne

La méthode n'est pas très orthodoxe, convenons-en. Elle permet d'introduire des hints sur des requêtes, ce qui, très souvent est le contraire d'une bonne idée. Elle le fait grâce à un SQL profile au lieu d'un Outline ou d'une baseline SQL Plan Management qui sont les outils faits pour ça. Enfin, elle n'est pas documentée donc non supportée.

Et pourtant, il y a quelques semaines, j'ai démontré l'intérêt d'une préconisation sur un plan en utilisant un SQL Profile. En fait il s'agissait d'introduire un niveau supplémentaire de "dynamic sampling" sur une requête sans toucher aux paramètres d'instance ou de session. Cette approche est intéressante à plusieurs égards :

D'abord, elle permet de le faire même si cursor_sharing=exact et que vos requêtes ont des valeurs littérales qui changent, grâce au paramètre force_match
Ensuite, elle ne fixe pas le plan mais permet d'ajouter uniquement certains hints bien choisis comme dynamic_sampling, gather_plan_statistics ou monitor
Enfin, elle vous permet de le faire sans toucher au code

Mais rendons à César... Le script dont il s'agit est disponible sur Internet. Il appartient à Kerry Osborne, lequel à un blog avec quelques articles exceptionnels. Il s'appuie essentiellement sur :

La procédure dbms_sqltune.import_sql_profile, non documentée, qui permet de créer un profile SQL à partir du texte d'une requête
Le type sqlprof_attr qui permet d'ajouter (via le paramètre profile), les hints de votre choix comme paramètres du SQL profile
La capacité que vous aurez de retrouver le texte d'une requête dans la shared_pool... ou à l'endroit de votre choix (AWR, Statspack, votre code)

Pour créer un SQL Profile, définissez les paramètres sql_id, profile_name, category, force_matching et hint et vous pourrez le voir utilisé dans la section "note" du plan d'exécution une fois celui-ci re-parsé.

Dans mon cas, nous avons fini par modifier le code du batch concerné avec l'aide des développeurs. Toutefois, nous avons gagné plusieurs heures en corrigeant le plan sur les environnements de tests avant de nous retourner vers une autre équipe. Je me suis dit que quelques-uns d'entre-vous qui n'ont pas encore eu la chance de croiser ces articles aimeraient y jeter un œil. Quelqu'un a dit intéressant ?

05 avril 2010

Type CHAR et espaces

Contrairement au type VARCHAR2, le type CHAR a une longueur fixe. Pour permettre aux chaines de caractères d'être insérée dans un type CHAR, même si le nombre de caractères ou octets ne correspond pas, les chaines sont implicitement complétées pas des espaces. L'exemple ci-dessous met en évidence ce fonctionnement avec la valeur par défaut de blank_trimming :

SQL> show parameter blank_trimming

NAME           TYPE    VALUE
-------------- ------- ------------------------------
blank_trimming boolean FALSE

SQL> create table x(a char(10),b char(5));

Table created.

SQL> insert into x(a) values ('XXXXX');

1 row created.

SQL> commit;

Commit complete.

Cela dit, si les espaces complètent les chaînes, ils ne sont pas supprimés:

SQL> update x set b=a;
update x set b=a
            *
ERROR at line 1:
ORA-12899: value too large for column "SYS"."X"."B" (actual: 10, maximum: 5)

Pour changer ce comportement et être compatible avec les niveaux transitionels et supérieur de SQL-92, vous pouvez positionner le paramètre blank_trimming à true (il s'agit d'un paramètre statisque) :

alter system set blank_trimming=true scope=spfile;

SQL> startup force;
ORACLE instance started.

Total System Global Area 1065353216 bytes
Fixed Size            1223440 bytes
Variable Size          281019632 bytes
Database Buffers      775946240 bytes
Redo Buffers            7163904 bytes
Database mounted.
Database opened.

SQL> update x set b=a;

1 row updated.

SQL> commit;

Commit complete.

Comme vous pouvez vous en rendre compte, les espaces sont implicitement supprimés dans ce cas:

select '|'||a||'|' a, '|'||b||'|' b
from x;

A         B
------------ -------
|XXXXX       | |XXXXX|

Intéressant paramètre, même si les effets de bords d'un paramètre positionné au niveau de l'instance sont sans doute plus importants qu'on imagine à priori. L'alternative est une utilisation adéquate des fonctions ltrim et rtrim. Vous pouvez supprimer la table pour en terminer avec cet exemple :

SQL> drop table x purge;

Table dropped.

04 avril 2010

Des nouvelles de Oracle Database 11.2

Quelques nouvelles intéressantes à propos d'Oracle 11g Release 2 :

Oracle 11g Release 2 est disponible sur Windows (x64 uniquement pour l'instant) et peut-être téléchargée sur OTN.
SAP certifie désormais Oracle 11g Release 2 sur le kernel 640_EX2. Vous découvrirez d'ailleurs plein d'informations sur les fonctionnalités supportées par SAP sur ce site en plus de la publication "Oracle for SAP".
Vous pouvez accéder au teaser SunOracle IronMan 2 ainsi qu'au fond d'écran sur la page dédié à Stark Industries sur Oracle.com.

Pour finir, un lien assez instructif pour ceux qui veulent apprendre l'anglais IT.