JDeveloper et Coherence /*+ Premier pas */

Tangosol Coherence permet de mettre en Grid les données utilisées par les applications Java (et .Net). Un intérêt immédiat pour les applications JavaEE et de permettre de stocker et distribuer les informations de sessions entre plusieurs serveurs d'applications Java. Pour en savoir plus sur le rachat de Tangosol par Oracle, cliquez ici ! Dans ce qui suit, nous allons créer rapidement un programme qui bénéficie des qualités de Cohérence :

Installer Tangosol Coherence...
Pour télécharger une version de démonstration de Coherence, créez un compte gratuit sur leur site web puis cliquez sur le lien suivant pour télécharger la version qui vous convient : http://www.tangosol.com/product-downloads.jsp.
a) Décompresser la distribution dans un répertoire de votre choix.
b) Installer et configurer Java 5.0
c) Tester l'installation de Coherence, en tapant : "java -jar coherence.jar", comme dans l'écran ci-dessous.


Créer un projet et ajouter les JAR coherence
Pour en savoir plus reportez-vous à la page correspondante sur le site de Tangosol. Dans un premier temps, lancez JDeveloper et ajouter les libraries Coherence à l'outils et à un nouveau projet.
a) Sélectionner le menu "Tools... -> Manage Libraries..."
b) Sélectionner le dossier User et cliquez sur "New..."
c) Donner lui le nom "Coherence" et ajoutez au CLASSPATH les entrées lib/coherence.jar et lib/tangosol.jar.
d) Ajouter une Application (Menu File -> New... -> General -> Application). Utiliser "No Template [All Technologies]". Donnez lui un nom ainsi qu'un nom au projet qui constitue cette application.
e) Cliquer avec le bouton droit sur le projet et sélectionner "Projet Properties..". Sélectionner "Libraries", cliquez sur "Add Library". Ajouter la librairie créée précédemment au projet !


Créer une classe Java qui utilise Coherence
Ajouter une classe Java au projet. Sélectionner le menu : File -> New... -> General -> Java Class. Appelez-là démo et ajouter lui les lignes qui suivent :

• Pour pouvoir utiliser les classes de cohérence, importez les 2 classes suivantes :
  import com.tangosol.net.CacheFactory;
  import com.tangosol.net.NamedCache;
• Pour initialiser le cache cohérence, tapez la ligne suivante :
  NamedCache cache = CacheFactory.getCache("VirtualCache");
• Pour mettre une chaine de caractère dans le cache et l'identifier avec la clé key, tapez :
  cache.put(key, "hello");
• Pour récupérer une donnée dans le cache qui a la clé key, tapez :
  (String)cache.get("demo");

Le résultat attendu ressemble à la classe ci-dessous :

import com.tangosol.net.CacheFactory;
import com.tangosol.net.NamedCache;

public class Demo {
public Demo() {
NamedCache cache = CacheFactory.getCache("VirtualCache");
String key = "demo";
cache.put(key, "hello");
System.out.println("Le résultat du cache est : "+ (String)cache.get("demo"));
}

public static void main(String[] args) {
Demo demo = new Demo();
}
}

Créer un fichier XML de configuration du cache.
Ajouter un fichier XML au projet en sélectionnant le menu : File -> New... -> General -> XML -> XML Document. Appeler le fichier my-config.xml par exemple et ajouter le contenu inclus dans le fichier attaché ici.

Une fois le fichier ajouté au projet, ajoutez la référence à ce fichier dans la ligne d'exécution de l'application. Si vous voulez l'ajouter dans JDeveloper, sélectionner le projet et avec le bouton droit, sélectionner "Project Properties". Sélectionner "Run/Debug", Selectionner la configuration utilisée (e.g. default) et cliquer sur "Edit...". Dans la zone de texte Java Options, taper : "-Dtangosol.coherence.cacheconfig=[path-to-file]/my-config.xml". comme dans l'écran ci-dessous :



Exécuter l'application...
Sélectionner la classe Demo et sélectionner "Run" sur le bouton contextuel (bouton droit de la souris). Le texte qui suit s'affiche dans la fenêtre de Log de JDeveloper :

D:\jdev\jdevstudio10132\jdk\bin\javaw.exe -client -classpath D:\jdev\jdevstudio10132\jdev\mywork\Application1\Project1\classes;D:\coherence-322b371\tangosol\lib\coherence.jar;D:\coherence-322b371\tangosol\lib\tangosol.jar -Dtangosol.coherence.cacheconfig=D:\jdev\jdevstudio10132\jdev\mywork\Application1\Project1\my-config.xml Demo

Tangosol Coherence Version 3.2.2/371

2007-03-31 19:02:00.562 Tangosol Coherence 3.2.2/371 (thread=main, member=n/a): Loaded operational configuration from resource "jar:file:/D:/coherence-322b371/tangosol/lib/coherence.jar!/tangosol-coherence.xml"
2007-03-31 19:02:00.562 Tangosol Coherence 3.2.2/371 (thread=main, member=n/a): Loaded license data from "jar:file:/D:/coherence-322b371/tangosol/lib/tangosol.jar!/tangosol-license.xml"

******************************************************************************
*
* Tangosol Coherence is licensed by Tangosol, Inc.
*
* Licensed for evaluation use from 2007-03-19 until 2007-05-01 (30 days
* remaining)
* Tangosol Coherence: DataGrid Edition
* Tangosol Coherence: Caching Edition
* Tangosol Coherence: Data Client
* Tangosol Coherence: Application Edition
* Tangosol Coherence: Real-Time Client
* Tangosol Coherence: Compute Client
*
* A production license is required for production use. For more information,
* see http://www.tangosol.com/license.jsp.
*
* Copyright (c) 2000-2006 Tangosol, Inc.
*
******************************************************************************

2007-03-31 19:02:00.843 Tangosol Coherence DGE 3.2.2/371 (thread=main, member=n/a): Loaded operational overrides from resource "jar:file:/D:/coherence-322b371/tangosol/lib/coherence.jar!/tangosol-coherence-override-eval.xml"
2007-03-31 19:02:00.937 Tangosol Coherence DGE 3.2.2/371 (thread=main, member=n/a): Loaded cache configuration from file "D:\jdev\jdevstudio10132\jdev\mywork\Application1\Project1\my-config.xml"
2007-03-31 19:02:02.109 Tangosol Coherence DGE 3.2.2/371 (thread=Cluster, member=n/a): This Member(Id=2, Timestamp=2007-03-31 19:02:01.914, Address=192.168.1.10:8089, MachineId=26890, Edition=DataGrid Edition, Mode=Evaluation, CpuCount=2, SocketCount=1) joined cluster with senior Member(Id=1, Timestamp=2007-03-31 17:11:46.718, Address=192.168.1.10:8088, MachineId=26890, Edition=DataGrid Edition, Mode=Evaluation, CpuCount=2, SocketCount=1)
Le résultat du cache est : hello
Process exited with exit code 0.

GarK!

PS: Vous pouvez télécharger le projet Jdeveloper 10.1.3.2 ici. Vous devrez encore, (1) installer Coherence, (2) ajouter les .jar au projet et (3) changer le répertoire du fichier XML dans les options d'exécution de la classe Demo.

Index (ou presque)

Avant de créer un index, il est parfois intéressant (pour ceux qui ont des tables de plusieurs dizaines de Gigabytes) d'évaluer

• l'impact d'un index sur un ordre SQL (ou une charge)
• l'espace nécessaire pour cet index.

Et bien répondre à ces 2 questions est possible et même très simple. Pour les besoins de ce qui suit, nous allons créer une table comme ceci :

SQL> create table gark(id number, lib varchar2(4000));

SQL> begin
for i in 1..50000 loop
insert into gark values (i, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
|| 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
|| 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
|| 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
|| 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
|| 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
|| 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
||'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
||'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
||'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
||'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
||'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
||'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
||'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
||'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
||'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
||'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
||'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
||'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
||'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
/

SQL> exec dbms_stats.gather_table_stats(USER, 'GARK');

Impact d'un index sur un ordre SQL
Il est possible de créer un index virtuel, c'est à dire, référencé dans le dictionnaire mais pas rempli. Pour ça, il suffit d'utiliser le mot clé NOSEGMENT dans l'ordre de création de l'index.

Voici un requête et son plan d'exécution (sans surprise !) :
SQL> select * from gg where id=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 1795868617

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1016 | 1580 (1)| 00:00:19 |
|* 1 | TABLE ACCESS FULL| GARK | 1 | 1016 | 1580 (1)| 00:00:19 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=10)

On veux voir l'effet d'un index sans le créer réellement, on peut utiliser les 2 commandes
create unique index gark_idx on gark(id) nosegment;
alter session set "_use_nosegment_indexes"=true;

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

SQL> select * from gg where id=10;

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------
Plan hash value: 2270987589

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1016 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| GG | 1 | 1016 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | GARK_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------
2 - access("ID"=10)

Taille d'un index
Pour connaitre la taille d'un index, il suffit d'utiliser le package DBMS_SPACE en lui passant comme paramètre l'ordre CREATE INDEX comme ceci :

SQL> set serveroutput on
SQL> declare
v_used_bytes number;
v_alloc_bytes number;
begin
dbms_space.create_index_cost('CREATE UNIQUE INDEX GARK_IDX ON GARK(ID)',
v_used_bytes, v_alloc_bytes);
dbms_output.put_line('La taille des donnees indexees est de : '||to_char(v_used_bytes) ||' Octets');
dbms_output.put_line('La taille de de l''index une fois cree sera de : '||to_char(v_alloc_bytes) ||' Octets');
end;
/

La taille des donnees indexees est de : 246395 Octets
La taille de de l'index une fois cree sera de : 851968 Octets

Et biensûr, pour continuer sur les index, regardez le SQL Access Advisor et la surveillance de l'utilisation des index.

GarK!

Quelques notes à propos de Perl

Perl est, avec SQL, de loin mon langage préféré pour écrire des scripts à utiliser avec Oracle. Voici ci-dessous quelques notes utiles pour construire et exécuter des scripts Perl avec une base de données :

Configurer l'environnement pour utiliser Perl (la version incluse dans 10g sous linux)
A- Il faut modifier la variable PATH pour y inclure le programme Perl (pas celui inclus dans /usr/bin/perl).
$ export PATH=$ORACLE_HOME/perl/bin:$PATH

B- Ensuite, il faut mettre à jour la variable PERL5LIB pour pointer vers les modules Perl que vous voulez utiliser avec vos programmes.
$ export PERL5LIB=$ORACLE_HOME/perl/lib/perl/lib/5.8.3

Menu dynamique
Il existe un module Perl qui permet de construire très simplement des menus dynamiques (pour en savoir plus cliquez ici). C'est très pratique pour permettre la sélection entre plusieurs lignes d'une requête SQL ! Voici ci-dessous comment l'utiliser :
A- Ajoutez le fichier Menu.pm dans le répertoire $ORACLE_HOME/perl/lib/perl/lib/5.8.3/Term

B- Ecrire un programme Perl qui a les caractéristiques suivantes :

• inclure le module Perl Term::Menu comme ceci :
• use Term::Menu;
  
• Créer un tableau hash comme ceci :
• my %hash = ();
• %hash = ( "7ab3ba" => ["Select /* DEMO 1 */ 'Gark' from dual", '1'],
  "e3b1b1" => ["Select /* DEMO 2 */ 'Gark' from dual", '2'],
  "6avvc2" => ["Select /* DEMO 3 */ 'Gark' from dual", '3'],
  "15ffad2" => ["Select /* DEMO 4 */ 'Gark' from dual", '4'],);
  
• Créer le menu ainsi qu'une variable résult qui contiendra le résultat. Vous pouvez changer les différents libellés du menu :
  
• my $menu = new Term::Menu( beforetext => "Selectionnez un des menus ci-dessous :", aftertext => "Saississez votre choix :",);
• my $result = $prompt->menu(%hash);

• Afficher la valeur de la ligne sélectionnée :
• print "Le SQL_ID selectionne est le $result\n";
• Attendez 2 secondes et effacez l'écran
• sleep 2;
• system(($^O eq 'MSWin32') ? 'cls' : 'clear');

Et voilà, le texte complet du script Perl est donc le suivant :
use Term::Menu;
system(($^O eq 'MSWin32') ? 'cls' : 'clear');
my %hash = ();
my $menu = new Term::Menu( beforetext => "Selectionnez un des menus ci-dessous :",
aftertext => "Saississez votre choix :",);
my $result = "1";
while ( $result ne "q" ) {
%hash = ( "7ab3ba" => ["Select /* DEMO 1 */ 'Gark' from dual", '1'],
"e3b1b1" => ["Select /* DEMO 2 */ 'Gark' from dual", '2'],
"6avvc2" => ["Select /* DEMO 3 */ 'Gark' from dual", '3'],
"15ffad2" => ["Select /* DEMO 4 */ 'Gark' from dual", '4'],
"q" => ["Quitter", 'q'],);
$result = $menu->menu(%hash);
print "Le SQL_ID selectionne est le $result\n";
sleep 2;
system(($^O eq 'MSWin32') ? 'cls' : 'clear');
}

Exécuter des ordres SQL avec Perl DBI
Le module DBI permet d'exécuter des ordres SQL directement dans Perl, sans passer par SQL*Plus. Ci-dessous, nous allons montrer un exemple d'utilisation de ce module (L'avantange d'utiliser la version Perl de la base de données est que ce module est installé.

A- Pour exécuter du DBI, il faut d'abord positionner les variables d'environnement convenablement, comme ceci :

• export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
  
• export LD_LIBRARY_PATH=$ORACLE_HOME/lib
• export PERL5LIB=$ORACLE_HOME/perl/lib/5.8.3:$ORACLE_HOME/perl/lib/site_perl/5.8.3/i686-linux-thread-multi
• export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH

B- Créez un script gark.pl qui requête la base de données

use strict;
use DBI;
my $dbh = DBI->connect( 'dbi:Oracle:ORCL', 'scott', 'tiger',
) || die "Database connection not made: $DBI::errstr";

my $sql = qq{ SELECT empno, ename FROM emp };
my $sth = $dbh->prepare( $sql );
$sth->execute();

my( $empno, $ename );
$sth->bind_columns( undef, \$empno, \$ename );

while( $sth->fetch() ) {
print "$empno, $ename\n";
}

$sth->finish();
$dbh->disconnect();

Remarques :
L'alias TNS pour se connecter à ma base de données est ORCL
L'utilisateur et le mot de passe de la base de données sont scott et tiger

C- Exécutez le script ce-dessus en lançant la commande :
$ perl gark.pl
7369, SMITH
7499, ALLEN
7521, WARD
7566, JONES
7654, MARTIN
7698, BLAKE
7782, CLARK
7788, SCOTT
7839, KING
7844, TURNER
7876, ADAMS
7900, JAMES
7902, FORD
7934, MILLER

Pour en savoir plus sur l'utilisation du SQL avec Oracle, voici quelques liens utiles :

• http://www.saturn5.com/~jwb/dbi-examples.html
• http://www.perl.com/pub/a/1999/10/DBI.html
  

GarK!

Quelques scripts... 10g

Oracle 10g offre plusieurs scripts et table-fonction pour générer des rapports d'audit :

• awrrpt.sql génère un rapport AWR représentant l'activité de la base de données entre 2 clichés (i.e. la table fonction DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML et DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT)
  
• awrddrpt.sql génère les différences entre 2 rapports AWR (i.e. la table fonction DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_HTML ou DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_TEXT)
• awrsqrpt.sql affiche les informations relative à un ordre SQL dans un ensemble de clichés AWR (i.e. la table fonction DBMS_WORKLOAD_REPOSITORY.AWR_SQL_REPORT_HTML ou DBMS_WORKLOAD_REPOSITORY.AWR_SQL_REPORT_TEXT)
  
• ashrpt.sql génère un rapport ASH (i.e. la table fonction DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML ou DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_TEXT)

D'autre part, les rapports ci-dessous permettent de diagnostiquer et proposer des améliorations à votre base de données :

• addmrpt.sql gènère un rapport ADDM
  
• sqltrpt.sql génère un rapport du Tuning Advisor sur une requête selectionnée

Un dernier rapport pour voir ce qui est utilisé de votre base de données... pour voir ce que votre application utilise :

• dbfusrpt.sql (Feature Usage) génère un rapport avec l'ensemble des fonctionnalités utilisées dans une base de données.
  

GarK!

Up, Down, Left and Right...

SQL*Plus sous Linux bien plus simple : http://www.dizwell.com/prod/node/56 !

GarK!

Créer un SQL Tuning Set (STS) /*+ Fast and Furious */

Il y a quelque temps, nous présentions comment utiliser le SQL Tuning Advisor en précisant directement l'ordre SQL. C'était dans le thread Exemple d'ordre SQL #5 et #6 /*+Anti-Pattern*/. dans l'exemple ci-dessous, je vais précisez comment se servir d'un SQL Tuning Set (STS) pour réaliser le même type d'opérations.

SQL tuning Set (STS)
Le SQL Tuning Set regroupe un ensemble d'ordre SQL ainsi que les informations associées. C'est un outils qui permet facilement :

• De capturer de nombreux ordres stockées dans la SHARED POOL ou dans AWR
• D'échanger ces informations entre des bases de données (Production & Pré-production)
• D'utiliser les outils SQL Tuning Advisor et SQL Access Advisor simplement.

Créer un SQL Tuning Set
Pour créer un STS, il faut pouvoir exécuter le package DBMS_SQLTUNE, il suffit de lancer la commande suivante :

SQL> begin
dbms_sqltune.create_sqlset(
sqlset_name=>'GarK',
description=>'GarK SQL Tuning Set');
end;
/

Générer les ordres SQL que l'on ajoutera au STS
Pour mettre des ordres SQL dans le SQL tuning Set, il faut que les ordres soient exécutés. La façon la plus simple est de créer le STS sur la production et de l'exporter ensuite sur un environnement de test. Dans l'exemple ci-dessous, l'ordre SQL est créé sur un environnement de test à partir de scripts. Pour faciliter la capture, je positionne une valeur à MODULE comme ceci :

SQL> exec dbms_application_info.set_module('TUNING', null);
SQL> [SESSION SQL]
SQL> exec dbms_application_info.set_module(null,null);

Pour visualiser les ordres ainsi mis dans la shared pool, on peut alors tapez l'ordre ci-dessous :
SQL> select module, sql_text, hash_value, sql_id from v$sql
where module ='TUNING';

Ajouter les ordres au STS
Il y a plusieurs manière d'ajouter les ordres dans un STS. Soit à partir d'AWR soit à partir de la shared pool. Dans l'exemple ci-dessous, nous allons utiliser la shared pool et les primitives suivantes :

• DBMS_SQLTUNE.select_cursor_cache qui retourne une sélection de requêtes avec toutes les données nécessaires pour la constitution du STS.
• DBMS_SQLTUNE.load_sqlset qui permet d'ajouter un ensemble d'ordres SQL au STS.
  

Ci-dessous un exemple de bloc PL/SQL qui capture tous les ordres SQL dont la valeur MODULE est égale à 'TUNING' dans V$SQL :
SQL> DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.select_cursor_cache (
'module = ''TUNING''', -- basic_filter
NULL, -- object_filter
NULL, -- ranking_measure1
NULL, -- ranking_measure2
NULL, -- ranking_measure3
NULL, -- result_percentage
1) -- result_limit
) p;

DBMS_SQLTUNE.load_sqlset (
sqlset_name => 'GarK',
populate_cursor => l_cursor);
END;
/

Remarque :
Il n'est pas possible de mettre directement un ordre car le STS contient un certain nombre de statistiques d'exécution. Même utiliser DBMS_SQL.PARSE ne permet pas de capturer ensuite l'ordre dans le STS !)

Visualiser le contenu du STS
La fonction de table DBMS_SQLTUNE.select_sqlset retourne l'ensemble des informatios contenues dans le STS. On l'utilise comme indiqué ci-dessou :

SQL> SELECT *
FROM TABLE(DBMS_SQLTUNE.select_sqlset ('GREGORY'));

Lancer le SQL Tuning Advisor avec le STS
Voici le code qui permet de lancer et visualiser le résultat du SQL Tuning Advisor avec le STS. Pour plus de détails, reportez-vous à mon thread Exemple d'ordre SQL #5 et #6 /*+Anti-Pattern*/.

SQL> DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sqlset_name => 'GarK',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'GARK_TUNING_TASK',
description => 'Tuning task for an SQL tuning set.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'GARK_TUNING_TASK');

SQL> SELECT task_name, status FROM dba_advisor_log WHERE task_name = 'GARK_TUNING_TASK';

SQL> SET LONG 50000;
SQL> SET PAGESIZE 5000
SQL> SET LINESIZE 200
SQL> SELECT DBMS_SQLTUNE.report_tuning_task('GARK_TUNING_TASK') AS recommendations FROM dual;
SQL> SET PAGESIZE 24

Eventuellement implémentez les proposition de l'assistant (Après y avoir sérieusement réfléchi !)
=> C'est écrit dans le rapport précédent.

Supprimer ce que vous avez créé précédemment
La tâche de tuning :
SQL> exec DBMS_SQLTUNE.drop_tuning_task (task_name => 'GARK_TUNING_TASK');

Le SQL Tuning Set :
SQL> exec DBMS_SQLTUNE.drop_sqlset('GarK');

GarK!

Capturer un plan pour le forcer /*+ Mauvaise idee */

Ce que je vais décrire ci-dessous est sûrement une très mauvaise idée, si c'est mal utilisé. Toutefois, imaginer le cas suivant :

• Vous avez une requête qui s'exécute sur un serveur Oracle 9i/8i avec un certain plan.
• Vous le migrer sur un nouveau serveur en 10g et son plan change.
• Comment comparer le gain (ou la perte) dûe au nouveau plan ?

Une idée possible, peut-être d'utiliser le paramètre COMPATIBLE=9.2.0 sur votre base 10g. C'est toutefois pas évident d'être sûr que le plan sera identique car les statistiques sont clés et il est probable que vous changiez de méthode de prise de statistiques (J'ai un super fit en 10g avec l'option method_opt=>'FOR ALL COLUMNS SIZE AUTO' de DBMS_STATS)

Une autre idée consiste à forcer le plan de votre 9i au moyen de HINTS. Le problème si votre plan fait 3369 étapes et 5 niveaux de vues, c'est que c'est pas évident à reconstruire, pour vous aider :

• Je ne suis pas un admirateurs des outlines (D'ailleurs, ça sera remplacé par un truc beaucoup plus sioux en 11.1 !) mais en l'occurence, ça peut vous aider pour ce problème. Regardez les vues USER_OUTLINE_HINTS et la commande CREATE OUTLINE. (pour en savoir plus, regardez cet article de Jonathan Lewis (Encore lui !).
  
• EN 10g (et pas avant, dommage), l'event 10053 (C'est là où je voulais en venir !) affiche en fin de plan un HINT permettant de figer le plan... Les migrations vers la 11, vont être enfantines. Ci-dessous un exemple.

SQL> alter session set events '10053 trace name context forever';

SQL> select /* event 10053 */ e.empno, d.loc
from dept d, emp e
where empno>1000
and d.deptno=e.empno;

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

Attention, il faut que la requête soit parsée pour que le fichier soit généré (d'où l'utilité du commentaire. Ensuite, il faut se déconnecter et de regarder le fichier généré dans le répertoire correspondant au paramètre user_dump_dest (ou $ORACLE_BASE/admin/{DBNAME}/udump en OFA). En dessous de la trace effective du calcul du plan, vous avez les informations correspondant à l'OUTLINE qui s'affiche :

$ ls -ltr
-rw-r----- 1 oracle oinstall 38243 Mar 22 13:57 orcl_ora_4209.trc

cat orcl_ora_4209.trc

db_version : 10.2.0.3
parse_schema : SCOTT
plan_hash : 459569910
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "E"@"SEL$1" ("EMP"."EMPNO"))
INDEX_RS_ASC(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
USE_NL(@"SEL$1" "D"@"SEL$1")
END_OUTLINE_DATA
*/

GarK!


Statistiques d'un plan /*+ Need 4 Speed */

Le hint GATHER_PLAN_STATISTICS permet d'afficher l'ensemble des statistiques d'exécution d'un plan sous Oracle. Voici dans l'exemple ci-dessous les différences de sorties selon l'utilisation ou non de ce hint.

Avant de commencer
Il faut noter que nous allons utiliser la fonction de table dbms_xplan.display_cursor qui permet d'afficher un plan d'exécution d'un ordre dans la shared pool. Si vous utilisez cette fonction avec les paramètres : null,null,'ALLSTATS LAST', la fonction retourne les informations du dernier ordre exécuté dans la session.
2 remarques pour que ça marche :

• Il faut accéder à v$session pour ça.
• Il faut que le dernier order SQL exécuté soit effectivement l'ordre recherché. Sous SQL*Plus par exemple, ne positionnez pas "set serveroutput on" ni "set autotrace on"

Exécuter un ordre sans le hint
SQL> select e.empno, d.loc
2 from dept d, emp e
3 where empno>1000
4 and d.deptno=e.empno;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 3u40yubzq6u1g, child number 0
-------------------------------------
select e.empno, d.loc from dept d, emp e where empno>1000 and
d.deptno=e.empno

Plan hash value: 459569910

---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 1 | NESTED LOOPS | | 1 |
|* 2 | INDEX RANGE SCAN | PK_EMP | 14 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 |
---------------------------------------------------------

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

2 - access("E"."EMPNO">1000)
4 - access("D"."DEPTNO"="E"."EMPNO")
filter("D"."DEPTNO">1000)

Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level

Exécuter un ordre avec le hint
SQL> select /*+ gather_plan_statistics */ e.empno, d.loc
from dept d, emp e
where empno>1000
and d.deptno=e.empno;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 34tyumtrfbxkw, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ e.empno, d.loc from dept d, emp e where empno>1000
and d.deptno=e.empno

Plan hash value: 459569910

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | PK_EMP | 1 | 14 | 14 |00:00:00.01 | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 14 | 1 | 0 |00:00:00.01 | 2 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 14 | 1 | 0 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------

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

2 - access("E"."EMPNO">1000)
4 - access("D"."DEPTNO"="E"."EMPNO")
filter("D"."DEPTNO">1000)

Pour finir...
TKPROF permet biensûr aussi d'afficher les statistiques d'un ordre SQL. Pour créer un fichier trace et exécuter TKPROF, il faut activer SQL_TRACE pour la session (ou l'event 10046) et exécuter la requête comme ci-dessous :
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

SQL> select e.empno, d.loc
from dept d, emp e
where empno>1000
and d.deptno=e.empno;

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

Ensuite il faut se déconnecter et de regarder le fichier généré dans le répertoire correspondant au paramètre user_dump_dest (ou $ORACLE_BASE/admin//udump en OFA)

$ ls -ltr
-rw-r----- 1 oracle oinstall 2382 Mar 22 13:24 orcl_ora_4178.trc

On exécute ensuite TKPROF comme ci-dessous
tkprof orcl_ora_4178.trc orcl_ora_4178.tkprof explan=scott/tiger sys=n

On peut enfin visualiser le contenu du fichier généré. Voici un exemple pour la requête précédente :

select e.empno, d.loc
from dept d, emp e
where empno>1000
and d.deptno=e.empno

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.02 0.02 0 3 0 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (SCOTT)

Rows Row Source Operation
------- ---------------------------------------------------
0 NESTED LOOPS (cr=3 pr=0 pw=0 time=5367 us)
14 INDEX RANGE SCAN PK_EMP (cr=1 pr=0 pw=0 time=636 us)(object id 51252)
0 TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=4164 us)
0 INDEX UNIQUE SCAN PK_DEPT (cr=2 pr=0 pw=0 time=2973 us)(object id 51250)

Et voilà... biensûr ça ne permet pas de tuner un ordre SQL mais en apprenant où votre requête passe un temps significatif ou se trompe, c'est un début. Les advisors de la 10g vous aideront à aller encore plus loin !

GarK!

Oracle Application Express 3.0 (aka HTMLDB)

Ca va faire très plaisir au(x) fan(s) en France (Je suis sûr qu'il l'étudie depuis des mois en secret), APEX 3.0 est disponible sur OTN.

GarK!

A propos de RAC et Linux

Si vous n'avez pas suivi l'actualité récente ou beaucoup moins :

• Sergio explique comment mettre au niveau Oracle Enterprise Linux pour l'installeur avec up2date ici
• Wim explique un peu plus Oracle Unbreakable Linux ici
• Vous pouvez utiliser Infiniband pour l'interconnect de RAC sur Linux ici
• Vous pouvez désinstaller le cluster simplement (toujours sur Linux) ici
• Les scripts que vous pouvez utiliser avec RAC (y compris pour sécuriser une "Single Instance" avec le Clusterware) sont ici

GarK!

ASH

J'avais vu une présentation de Graham Wood à propos de ASH puis... perdu ! Elle était tout simplement sur OTN, ici. J'en profite pour donner une démonstration intéressante sur le site du PSOUG.

GarK!

Doug Burns PX /*+Basics*/

Une video de Doug Burns qui explique le principe du parallelisme Oracle : http://www.pythian.com/blogs/400/pythian-goodies-oracle-parallel-basics

GarK!