Installation 11gR2 sur Ubuntu ( partie 1 )

Dernièrement, j'ai acheté un portable Linux ( 64 bits, Ubuntu LTS version 10.04 ) chez Novatux. Pour une fois, je vais faire un peu de pub, car le support technique y est remarquable ( merci Laurent ).

Sur ce dernier, je viens d'installer une 11gR2 en m'appuyant sur les liens suivants:
- http://www.pythian.com/news/2329/installing-oracle-11gr1-on-ubuntu-904-jaunty-jackalope/
- http://www.pythian.com/news/13291/installing-oracle-11gr2-enterprise-edition-on-ubuntu-10-04-lucid-lynx/

L'installation ( instance + database + OEM ) s'est bien déroulée. Quelques conseils:
- Bien respecter la phase préparatoire ( installation des librairies manquantes, création de l'utilisateur oracle, paramétrage système ... );
- Installer le package sux avant de lancer le binaire runinstaller et positionner la variable DISPLAY à :0.0;
- Ne pas tenir compte des erreurs relatives aux packages, OUI  ( Oracle Universal Installer ) ne prenant pas en compte Ubuntu;
- Vérifier au préalable votre configuration graphique, vous pouvez en effet rencontrer une fenêtre blanche lors de l'installation et avoir quelques difficultés pour la valider.

Analyse d'une requête

Ce lien donne une liste d'outils permettant d'effectuer un diagnostic sur une requête SQL: http://oracle-randolf.blogspot.com/2009/02/basic-sql-statement-performance.html.

Shared server

Dans un environnement Solaris, j'ai dernièrement testé une connexion à une instance configurée en mode shared server. Pour information, l'instance appartient à un RAC 10g à 2 noeuds. Les clients sont écrits en PRO*C et se connectent à un dispatcher via un client Oracle 9i.

Les résultats sont les suivants:
- La durée de connexion est comprise en moyenne entre 20 à 25 ms.
- La durée de lecture d'une ligne d'une table de 20 colonnes ( lecture utilisant un index de la table ) est comprise entre 7 à 12 ms.
- La durée de lecture d'une ligne d'une table de 120 colonnes ( lecture utilisant un index de la table ) est comprise entre 11 à 16 ms.

Pour aller plus loin:
- Une description du mode shared server.
- Une description sur les connexions Oracle.

SPM ( SQL Plan Management )

Quelques liens:
- http://optimizermagic.blogspot.com/search/label/sql plan management
- http://www.oracle-base.com/articles/10g/AutomaticSQLTuning10g.php#sql_tuning_sets
- http://www.oracle-base.com/articles/11g/AutomaticSqlTuning_11gR1.php  

Ce nouveau concept de la 11g permet de figer les plans d'exécution comme les stored outlines. De plus, il collecte pour une requête un meilleur plan d'exécution si l'optimiseur  a trouvé un chemin d'accès plus performant pour récupérer les données. De cette manière, on est désormais à l'abri des changements brusques des plans d'exécution ( qui n'a pas connu cela sur une base de production ? ), tout en pouvant améliorer les temps d'exécution des requêtes de manière contrôlée.

Méthode de détection d'un problème ( partie 2 )

Si les vues associées aux wait events ne donnent aucun indice, on peut alors étudier les statistiques d'une session. Pour ce faire, on utilise les vues v$sesstat et v$statname. Pour information, une description succincte des statistiques est disponible dans une annexe du document de référence ( 11g: http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/stats.htm#i29468 ).

L'outil de T.Poder, snapper, permet entre autres de visualiser les statistiques d'une session. Il est décrit à cette adresse: http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper.

Par ailleurs, vous pouvez analyser un problème à partir des outils d'Oracle ( statspack ou awr ). Ce lien http://jonathanlewis.wordpress.com/statspack-examples/ vous aidera à interpréter ce type de rapport. L'outil ADDM permet également de recueillir quelques pistes d'optimisation. 

Si tous ces outils ne vous permettent pas de résoudre votre problème, il reste alors à utiliser un outil comme dtrace ( http://hub.opensolaris.org/bin/view/Community+Group+dtrace/WebHome ), l'idée étant de descendre au niveau de la couche système pour détecter l'origine du problème.

Cardinality feedback

Quelques liens:
- http://dioncho.wordpress.com/2009/12/17/trivial-research-on-the-cardinality-feedback-on-11gr2/
- http://wedostreams.blogspot.com/2009/12/hidden-undocumented-and-adaptive-cursor.html 
- http://jonathanlewis.wordpress.com/2009/12/16/adaptive-optimisation/ 

En se connectant en tant que SYS, vous pouvez visualiser les caractéristiques d'un paramètre caché de la manière suivante:
select ksppinm as param,
       y.ksppstvl as ses_val,
       z.ksppstvl as inst_val,
       decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') as ses_modif,
       decode(bitand(ksppiflg/65536,3),1, 'IMMEDIATE',2,'DEFERRED', 3,'IMMEDIATE','FALSE') as system_modif,
       ksppdesc as descr
from x$ksppi x, x$ksppcv y, x$ksppsv z
where x.indx = y.indx
  and x.indx = z.indx
  and ksppinm like '\_%' escape '\'
order by ksppinm;

Extended cursor sharing

Jusqu'à la version 11g, le mécanisme dit de peek bind variable posait parfois des problèmes lorsque la sélectivité des différentes valeurs des bind variables était différente. Désormais, le mécanisme dit d'extended cursor sharing permet de corriger ce défaut après quelques exécutions.

Pour illustrer ce nouveau mécanisme, on crée une nouvelle table:
create table adapt_curs_share (
   idf number,
  date_ins date,
  chaine varchar2(80),
  nombre number not null )
tablespace 'nom_tablespace';

create sequence seq_adapt_curs_share
increment by 1
start with 1
;  

create index idx_adapt_curs_share_nombre on adapt_curs_share(nombre) tablespace 'nom_tablespace';

On l'alimente de la manière suivante:
begin
   for i in 1..100000
   loop
      insert into adapt_curs_share
      (idf, date_ins, chaine, nombre)
      values
      (seq_adapt_curs_share.nextval, sysdate, dbms_random.string('P', 80), abs(mod(dbms_random.random(),1000 )));
   end loop;
  
   commit;
end;

On calcule les statistiques et on nettoie le shared pool :
dbms_stats.gather_table_stats(
ownname => 'schéma',
tabname => 'adapt_curs_share',
method_opt=>'for all columns size 1',
cascade => true);

alter system flush shared pool;

Avant d'ouvrir la session sous DOS,  il est souhaitable de positionner le bon jeu de caractères:

On positionne le niveau le plus élevé pour les statistiques afin de recueillir plus d'informations au niveau des plans d'exécution:
alter session set statistics_level = all;

Une fois la configuration mise en place, on exécute sous sqlplus les instructions suivantes:
variable n number
exec :n := 999
select count(idf) from adapt_curs_share where nombre < :n; ( résultat: 99893 )
exec :n :=3
select count(idf) from adapt_curs_share where nombre < :n; ( résultat: 308 )

Le plan d'exécution de la requête est le suivant: 

Le contenu de la bind variable est 999, la valeur utilisée par la première exécution de la requête. Etant donné la faible sélectivité de la requête, un access full est effectué sur la table adapt_curs_share. La deuxième requête, bien que très sélective, va utiliser le même plan d'exécution, d'où l'idée d'améliorer le mécanisme de peek bind variable via le concept d'extended cursor sharing.

Dans la vue v$sql, les colonnes is_bind_sensitive ( utilisation du peek bind variable ), is_bind_aware ( utilisation de l'extended cursor sharing ) et is_shareable ( curseur partagé ou non ) donnent pour ce curseur le triplet (Y, N, Y).

Suite à ces deux premières exécutions, on effectue les opérations suivantes:

exec :n :=3
select count(idf) from adapt_curs_share where nombre < :n;
exec :n :=999
select count(idf) from adapt_curs_share where nombre < :n;

Pour le curseur 9fyq0vr5fh4dn, on observe désormais dans la vue v$sql trois fils au lieu d'un. Le fils 0 a exécuté les deux premières requêtes. Il utilise le mécanisme du peek bind variable, mais pas celui de l'extended cursor sharing et il n'est plus partageable.

Le fils 1 a exécuté la troisième requête ( bind variable = 3 ). Il utilise le peek bind variable, l'extended cursor sharing et il est partageable. Son plan d'exécution est le suivant;

Le fils 2 a exécuté la quatrième requête ( bind variable = 999 ). Il utilise le peek bind variable, l'extended cursor sharing et il est partageable. Son plan d'exécution est le suivant;

Si on exécute de nouveau la requête avec les deux valeurs 3 et 999, seuls les fils 1 et 2 sont utilisés.

Pour aller plus loin sur ce nouveau concept lié à la 11g, vous pouvez aussi consulter les vues v$sql_cs_statistics, v$sql_cs_selectivity et v$sql_cs_histogram.