SQL Performance Analyzer (SQLPA) est l’autre bonne nouvelle de Real Application Testing Option (cf mon Post précédent à propos de Database Replay). SQLPA utilise les ordres SQL stockés dans les SQL Tuning Set. Il permet de conserver plans et statistiques de ces ordres avant et après différentes variations de votre base de données. Vous pouvez, au moyen de ses rapports d’analyse, visualiser l’impact de changements sur l’activité SQL. Vous pouvez visualiser ces variations pour 1 comme pour 1000 ordres SQL aussi simplement. Cette seconde partie consacrée à l’option Real Application Testing, illustre à travers un exemple simple comment utiliser SQL Performance Analyzer.
Si vous voulez savoir comment ça marche, regardez la doc et en particulier :
- Oracle 11g Performance Tuning Guide – 23.SQL Performance Analyzer
- Oracle 11g PL/SQL Types and Packages Reference – DBMS_SQLPA
- Oracle 11g Reference et notamment les vues :
Ce qui suit est un exemple simple pour illustrer ce que fait SQLPA et en quoi c’est différent, par exemple de Database Replay.
1. Schéma d’exemple
Pour exécuter l’exemple de ce Post, il faut créer une table et la remplir avec 10000 lignes, comme ci-dessous :
create table gark
(id number not null);
begin
for i in 1..10000 loop
insert into gark(id)
values (i);
end loop;
commit;
end;
/
2. Requête et plan
Avec SQL*Plus (ça ne marche pas avec SQL*Developer, parce qu’il génère des Ordres SQL entre les différentes partie de votre script), lancez le script ci-dessous :
set serveroutput off
col id format 99999
select a.id, b.id
from gark a, gark b
where a.id=b.id
and b.id=500;
select * from table(
dbms_xplan.display_cursor);
Ce script exécute une requête et affiche son plan ainsi que son SQL_ID et sa HASH_VALUE. Voici ce qu’il affiche par exemple :
-------------------------------------
SQL_ID 683kdkrs2dmrk, child number 0
-------------------------------------
select a.id, b.id from gark a, gark b where a.id=b.id
and b.id=500
Plan hash value: 2625395012
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15 (100)|
|* 1 | HASH JOIN | | 1 | 26 | 15 (7)|
|* 2 | TABLE ACCESS FULL| GARK | 1 | 13 | 7 (0)|
|* 3 | TABLE ACCESS FULL| GARK | 1 | 13 | 7 (0)|
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
2 - filter("A"."ID"=500)
3 - filter("B"."ID"=500)
Note
-----
- dynamic sampling used for this statement
3. Capturer la requête dans un SQL Tuning Set
Une fois la requête dans la Shared pool, vous pouvez creer un SQL Tuning Set et l’enregistrer dans ce STS. Voici comment procéder
- D’abord, créez le SQL Tuning Set
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'gark_sts',
description => 'STS for SPA demo');
END;
/
- Ensuite, chargez la requête précédente dans le SQL Tuning set avec la procédure qui suit :
accept sql_id prompt "Enter value for sql_id: "
683kdkrs2dmrk
DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (
DBMS_SQLTUNE.select_cursor_cache (
'sql_id = ''&sql_id''', -- 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_sts',
populate_cursor => l_cursor);
END;
/
PL/SQL procedure successfully completed.
- Vérifiez que l’ordre SQL est bien dans le STS à l’aide de la requête ci-dessous :
col sql format a50
set lines 120
SELECT sql_id,
substr(sql_text, 1, 50) sql
FROM TABLE(
DBMS_SQLTUNE.select_sqlset (
'gark_sts'));
L’ordre SQL retourne quelque chose comme ceci :
SQL_ID SQL
------------- ---------------------
683kdkrs2dmrk select a.id, b.id
from gark a, gark b
where a.i
4. Capturer les statistiques et les plans de l’exécution avant les changements.
Cette étape est préalable au changement et servira de base aux comparaisons ultérieures. Dans l’ordre, il faut :
- Créer une tache d’analyse pour le SQL Performance Analyzer et y référencer le STS
var v_out char(50)
begin
:v_out:=dbms_sqlpa.create_analysis_task(
sqlset_name => 'gark_sts',
task_name => 'gark_spa_task');
end;
/
print v_out
V_OUT
-------------
gark_spa_task
- Vérifier que la tache est bien créée
col TASK_NAME format a14
col ADVISOR_NAME format a24
select TASK_NAME,
ADVISOR_NAME,
created
from DBA_ADVISOR_TASKS
where task_name='gark_spa_task';
TASK_NAME ADVISOR_NAME CREATED
-------------- ------------------------ ---------
gark_spa_task SQL Performance Analyzer 15-AUG-07
- Exécuter la tache d’analyse
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'gark_spa_task',
execution_type => 'TEST EXECUTE',
execution_name => 'gark_spa_task_before');
end;
/
- Vérifier l’exécution et son statut
col TASK_NAME format a20
select execution_name,
status,
execution_end
from DBA_ADVISOR_EXECUTIONS
where task_name='gark_spa_task'
order by execution_end;
EXECUTION_NAME STATUS EXECUTION
------------------------------ ----------- ---------
gark_spa_task_before COMPLETED 15-AUG-07
5. Effectuer les changements
Nous allons ajoutez un index a la table GARK
create unique index
gark_idx on gark(id);
Index created.
6. Exécuter la tache d’analyse après les
changements.
Le script est le même qu’avant les changement. Seul le nom de l’exécution change
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'gark_spa_task',
execution_type => 'TEST EXECUTE',
execution_name => 'gark_spa_task_after');
end;
/
- Vérifier l’exécution et son statut
col TASK_NAME format a20
select execution_name,
status,
execution_end
from DBA_ADVISOR_EXECUTIONS
where task_name='gark_spa_task'
order by execution_end;
EXECUTION_NAME STATUS EXECUTION
------------------------------ ----------- ---------
gark_spa_task_before COMPLETED 15-AUG-07
gark_spa_task_after COMPLETED 15-AUG-07
7. Comparer les performances avant et après les changements
Pour ce faire, vous devez créer et exécuter une tache qui effectue la comparaison avec un script comme ci-dessous :
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'gark_spa_task',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'gark_spa_tark_compare',
execution_params => dbms_advisor.arglist(
'comparison_metric',
'buffer_gets'));
end;
/
PL/SQL procedure successfully completed.
Ensuite il vous suffit d’afficher le rapport associé à la tache à l’aide du script suivant :
variable rep CLOB;
begin
:rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK(
task_name=>'gark_spa_task',
type=>'HTML',
level=>'ALL',
section=>'ALL');
end;
/
SET LONG 100000
set LONGCHUNKSIZE 100000
set LINESIZE 200
set head off
set feedback off
set echo off
spool sts_changes.html
PRINT :rep
spool off
set head on
Remarque : Le paramètre SECTION doit avoir la valeur SUMMARY ou ALL et non pas SECTION_ALL comme indiquée dans la documentation
Vous pouvez visualiser le fichier généré avec votre navigateur Web. Voici un exemple
Si vous voulez l’extrait complet, cliquez ici
8. Autre manière de visualiser les résultats de la comparaison
Vous pouvez également sélectionnez les vues suivantes pour visualiser les résultats des exécutions :
- DBA_ADVISOR_FINDINGS
- DBA_ADVISOR_SQLPLANS
- DBA_ADVISOR_SQLSTATS
9. Supprimer exécutions, taches, et le schéma d’exemple
Vous pouvez supprimer toutes les informations d’exécution d’une tache (Avant les changements et apres les changements) à l’aide de la procédure reset_analysis_task.
begin
dbms_sqlpa.reset_analysis_task(task_name=>'gark_spa_task');
end;
/
col TASK_NAME format a20
select execution_name,
status,
execution_end
from DBA_ADVISOR_EXECUTIONS
where task_name='gark_spa_task'
order by execution_end;
no rows selected
Par ailleurs, vous pouvez créer autant d’exécutions que nécessaire.
Si vous voulez supprimer la tache en plus des exécutions, utilisez la procédure ci-dessous :
begin
dbms_sqlpa.drop_analysis_task(task_name=>'gark_spa_task');
end;
/
col TASK_NAME format a14
col ADVISOR_NAME format a24
select TASK_NAME,
ADVISOR_NAME,
created
from DBA_ADVISOR_TASKS
where task_name='gark_spa_task';
no rows selected
A noter également que vous pouvez :
- Annuler l’exécution d’une tache en cours avec la procédure cancel_analysis_task
- Interrompre l’exécution d’une tache en cours avec la procédure interrupt_analysis_task
- Reprendre l’exécution d’une tache interrompue avec la procédure resume_analysis_task
Vous trouverez le script pour supprimer les objets utilisés dans cette démonstration ci-dessous :
drop table gark cascade constraints purge;
10. Conclusion
Vous verrez l’intérêt de SQL Performance Analyzer en créant des SQL Tuning Set avec de nombreux ordres SQL comme par exemple en capturant les données issue d’AWR. Cette approche est très différente de Database Replay et ne nécessite pas de reconstruire exactement la base de données de production.
Voila qui termine cette promenade consacrée à l’option Real Application Testing, au moins pour l’instant. Si d’autres fonctionnalités sont incluent dans la Release 2, j’y ajouterai d’autres parties. D’ici là, j’aurai le temps de discuter de nombreuses autres fonctionnalités d’Oracle 11g.
