Dans ce qui suit, l’utilisation d’AWR, d’ADDM et du SQL*Tuning advisor permet de découvrir et d’expliquer le problème associé à l’application du « Post » précédent. A travers cet exemple, il est effectué une présentation pratique et rapide de plusieurs outils des Packs diagnostic et tuning inclus dans Oracle 10g. Ce qui suit a été réalisés avec Oracle 10.2.0.3 sur Windows.

Paramétre AWR
Le paramètre d’instance statistics_level fixe le niveau d’information conservé dans AWR.

SQL> show parameter statistics_level

NAME TYPE VALUE
————————– ———– ——-
statistics_level string TYPICAL

Création du schéma de l’application
Pour les besoin de la démonstration, créez et la table DEMO#1, remplissez là et prenez les statistiques comme décrit dans le « Post » précédent.

Clichés AWR
Le principe de fonctionnement des clichés AWR est très semblable à celui du Statspack. Le package DBMS_WORKLOAD_REPOSITORY permet de les manipuler. Une première différence réside dans le fait que par défaut, la base de données 10.2 effectue un cliché AWR toutes les heures et conserve un historique de 7 jours. Pour notre exemple, nous allons déclencher 2 clichés. Un premier avant de lancer la charge SQL et un second juste après. Pour déclencher le cliché, tapez la commande suivante :

SQL> exec dbms_workload_repository.create_snapshot();

SQL> col END_INTERVAL_TIME format A25
SQL> select SNAP_ID, END_INTERVAL_TIME, SNAP_LEVEL
from DBA_HIST_SNAPSHOT
where STARTUP_TIME > systimestamp – 1/24
order by snap_id;

SNAP_ID END_INTERVAL_TIME SNAP_LEVEL
———- ————————- ———-
5 03/02/07 09:55:06,093 1
6 03/02/07 10:10:46,000 1

Démarrez la charge applicative
L’objectif de ADDM est de découvrir les problèmes des instances et du SQL généré par l’application. Il faut que que ce problème soit significatif pour être signalé. Dans le Post précédent, il est présenté un ordre qui met 0.14 seconde et qui pourrait être exécuté plus rapidement… si seulement… Cet ordre ne sera présenté comme un problème par ADDM que si son impact est important. La charge que vous devez exécuter doit donc utiliser de nombreuse fois cet ordre pour être significative. Dans une seconde fenêtre SQL*Plus lancez donc le script ci-dessous :

SQL> set timing on
SQL> declare
gg varchar2(4000);
begin
for i in 1..10000 loop
select lib into gg
from demo#1
where id=1;
end loop;
end;
/

Procédure PL/SQL terminée avec succès.

Ecoulé :00:02:15.07

Second cliché AWR
Afin de lancer ADDM, il faut créer un second cliché AWR. Comme pour le premier, effectuez les opérations ci-dessous :

SQL> exec dbms_workload_repository.create_snapshot();

SQL> col END_INTERVAL_TIME format A25
SQL> select SNAP_ID, END_INTERVAL_TIME, SNAP_LEVEL
from DBA_HIST_SNAPSHOT
where STARTUP_TIME > systimestamp – 1/24
order by snap_id;

SNAP_ID END_INTERVAL_TIME SNAP_LEVEL
———- ————————- ———-
5 03/02/07 09:55:06,093 1
6 03/02/07 10:10:46,000 1
7 03/02/07 10:40:57,375 1

Exécutez et visualisez les préconisations ADDM
Une fois le second cliché effectué, il faut lancez ADDM pour connaître les préconisations de la base de données 10.2 en ce qui concerne la charge constatée. Par défaut, il existe une tâche ADDM qui s’exécute après chaque cliché AWR. Une fois le second cliché effectué, vous aurez donc automatiquement le rapport ADDM correspondant. Pour visualiser le derniez rapport ADDM, exécutez ce qui suit :

SQL> set long 1000000
SQL> set pagesize 50000
SQL> column get_clob format a80
SQL> select dbms_advisor.get_task_report(
task_name, ‘TEXT’, ‘ALL’)
as ADDM_report
from dba_advisor_tasks
where task_id=(
select max(t.task_id)
from dba_advisor_tasks t, dba_advisor_log l
where t.task_id = l.task_id
and t.advisor_name=’ADDM’
and l.status= ‘COMPLETED’);

ADDM_REPORT
————————————————————————
DETAILED ADDM REPORT FOR TASK ‘ADDM:1139312629_1_7’ WITH ID 13
————————————————————–

Analysis Period: 03-F╔VR.-2007 from 10:10:46 to 10:40:57
Database ID/Instance: 1139312629/1
Database/Instance Names: ORCL/orcl< br /> Host Name: AOFR12992
Database Version: 10.2.0.3.0
Snapshot Range: from 6 to 7
Database Time: 1630 seconds
Average Database Load: ,9 active sessions

[…]

FINDING 3: 91% impact (1485 seconds)
————————————
Des instructions SQL particulières responsables d’une attente d’E/S
utilisateur importante ont été trouvées.

RECOMMENDATION 1: SQL Tuning, 100% benefit (1650 seconds)
ACTION: Exécutez SQL Tuning Advisor pour l’instruction SQL dont SQL_ID
est « 34fxbmz5516x3 ».
RELEVANT OBJECT: SQL statement with SQL_ID 34fxbmz5516x3 and
PLAN_HASH 3966067846
SELECT LIB FROM DEMO#1 WHERE ID=1
RATIONALE: L’instruction SQL o¨ SQL_ID = « 34fxbmz5516x3 » a été exécutée
853 fois, avec un temps ÚcoulÚ moyen de 1.9 secondes.
RATIONALE: Le temps moyen consommé pour les événements d’attente d’E/S
utilisateur par exécution a été de 1.7 secondes.

SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: La classe d’attente « E/S utilisateur » a consommÚ une part
importante du temps base de donnÚes. (91% impact [1490
seconds])
[…]

Exécutez le SQL*Tuning Advisor
Maintenant que vous avez détecté un ordre SQL qui pose problème, vous pouvez lancer le SQL Tuning Advisor pour le solutionner. Pour exécutez le SQL Tuning Advisor, on crée une tâche correspondante et on l’exécute comme ci-dessous :

SQL> variable gg varchar2(100)
SQL> exec :gg := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
         sql_text    => 'select lib from demo#1 where id=1', -
         user_name   => 'SYSTEM', -
         scope       => 'COMPREHENSIVE', -
         time_limit  => 30, -
         task_name   => 'DEMO#1', -
         description => 'Tuning d''un accès simple à la table DEMO#1');
SQL> exec dbms_sqltune.execute_tuning_task( task_name => 'DEMO#1' );

Pour affichez le résultat du SQL Tuning Advisor, tapez :

SQL > set long 10000
SQL > set longchunksize 10000
SQL > set lines 100
SQL > select dbms_sqltune.report_tuning_task('DEMO#1') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('DEMO#1')
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : DEMO#1
Tuning Task Owner                 : SYSTEM
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 30
Completion Status                 : COMPLETED
Started at                        : 02/03/2007 11:02:54
Completed at                      : 02/03/2007 11:02:59
Number of Index Findings          : 1
Number of SQL Restructure Findings: 1

-------------------------------------------------------------------------------
Schema Name: SYSTEM
SQL ID     : 6kjb7f5600zxj
SQL Text   : select lib from demo#1 where id=1

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
------------------------------------------------------------------------------
[...]
2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  Le prédicat TO_NUMBER("DEMO#1"."ID")=1 utilisé à l'ID de ligne 1 du plan
  d'exécution contient une conversion de type de données implicite sur la
  colonne indexée "ID". Cette conversion de type de données implicite empéche
  l'optimiseur de sélectionner les index sur la table "SYSTEM"."DEMO#1".

  Recommendation
  --------------
  - Réécrivez le prédicat sous une forme équivalente pour bénéficier des index.

  Rationale
  ---------
    L'optimiseur ne peut pas utiliser d'index si le prédicat est une condition
    d'inégalité ou qu'il existe une expression ou une conversion de type de
    données implicite sur la colonne indexée.

-----------------------------------------
--------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
[...]

Conclusion
Remplacez l'ordre :
SQL> select lib into gg from demo#1 where id=1;
par
SQL> select lib into gg from demo#1 where id='1';

Le résultat est le suivant
SQL> set timing on
SQL> declare
   gg varchar2(4000);
   begin
   for i in 1..10000 loop
   select lib into gg
   from demo#1
   where id='1';
   end loop;
   end;
   /

Procédure PL/SQL terminée avec succès.

Ecoulé :00:00:00.53

Soit une amélioration de plus de 2 heures en temps « elapse ». Impressionnant ?

Avant de terminer…
Supprimez la tâche associée au SQL Tuning advisor en lançant la commande ci-dessous :
SQL> exec dbms_sqltune. drop_tuning_task(‘DEMO#1’)

Dans les prochains « Post », j’illustrerai d’autres problèmes ainsi que la manière dont les Diagnostic et Tuning Packs les résolvent.

GarK!