Oracle Performance Analyzer est un outil fantastique de l’Option Real Application Testing. Il est apparu avec Oracle Database 11g. Pour l’avoir utilisé à plusieurs reprises dans des situations de migration, d’upgrade (il est possible collecter les informations originales sur une base 10g !) ou de changement d’infrastructure technique, il faut avouer que c’est une avancée majeure pour ce type de problématique. C’est aussi important que le moteur à réaction pour l’aviation ;-). Cela dit, si accrocher un réacteur à un avion n’est pas aussi simple qu’on peut l’entendre, réaliser une transformation d’un SI nécessite toujours, même avec des réacteurs, d’adresser certains points comme de :
- comprendre le contexte et les enjeux,
- fixer les objectifs,
- gérer avec précision les configurations,
- construire des sous-ensembles représentatifs qui soient représentatifs,
- gérer les risques
N’oublions pas nos fondamentaux et ni pourquoi il faut encore des administrateurs… Cet article présente un exemple des limites que vous pourrez rencontrer avec DBMS_SQLPA lié au paramétrage NLS de votre client que celui-ci ne sait pas reproduire.
Schéma d’exemple
Pour les besoins de cette démonstration, je vais utiliser une paramètre de session qui est parfois utilisé en français pour retrouver des noms ou prénoms accentués NLS_COMP. Voici un script pour créer un schéma exemple et créer une requête que nous utiliserons avec SQL Performance Analyzer :
create user demo
identified by demo
default tablespace users
temporary tablespace temp;
grant connect, resource, dba to demo;
connect demo/demo
create table T(
charkey varchar2(25 char) not null,
text varchar2(4000));
create index tidx on t(charkey);
insert into t
values ('E', rpad('X',4000,'X'));
insert into t
values ('é', rpad('X',4000,'X'));
insert into t
values ('e', rpad('X',4000,'X'));
insert into t
values ('F', rpad('X',4000,'X'));
insert into t
values ('f', rpad('X',4000,'X'));
commit;
alter session set nls_language=french;
alter session set nls_territory=france;
alter session set nls_sort=french;
alter session set nls_comp=linguistic;
select /* MARKER */ charkey
from t
where charkey between 'E' and 'f'
order by charkey;
CHARKEY
-------
E
e
é
F
f
SQL Tuning Set et SQL Performance Analyzer en action
Dans le script qui suit, vous allez capturer l’ordre SQL ainsi que l’ensemble des statistiques associées dans un SQL Tuning Set :
connect / as sysdba
col sql_id format a20 new_value sql_id
select sql_id
from v$sql
where regexp_like(sql_text, '[M]ARKER');
SQL_ID
---------------------------------------
dvxfphau4y4dx
prompt &&sql_id
begin
dbms_sqltune.drop_sqlset(
sqlset_name=>'ArKZoYD');
end;
/
begin
dbms_sqltune.create_sqlset(
sqlset_name=>'ArKZoYD',
description=>'ArKZoYD SQL Tuning Set');
end;
/
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
10) -- result_limit
) p;
DBMS_SQLTUNE.load_sqlset (
sqlset_name => 'ArKZoYD',
populate_cursor => l_cursor);
END;
/
select * from table(dbms_sqltune.SELECT_SQLSET(sqlset_name => 'ArKZoYD'));
Maintenant que l’ordre SQL est capturé, vous pouvez :
- créer une tâche SQLPA,
- enregistrer les statistiques et le plan du STS dans une première exécution du SQL PA,
- demander au SQL PA de ré-exécuter l’ordre SQL collecté,
- comparer les 2 exécutions (l’originale et la simulée)
var tname varchar2(100);
exec dbms_sqlpa.drop_analysis_task( -
task_name => 'SQLPArKZoYD');
exec :tname:=dbms_sqlpa.create_analysis_task( -
sqlset_name => 'ArKZoYD', -
task_name => 'SQLPArKZoYD');
BEGIN
DBMS_SQLPA.execute_analysis_task(
task_name => :tname,
execution_type => 'convert sqlset'
);
END;
/
col task_name format a15
col execution_name format a15 new_value XNAME
col execution_type format a50
BEGIN
DBMS_SQLPA.execute_analysis_task(
task_name => :tname,
execution_type => 'execute'
);
END;
/
TASK_NAME EXECUTION_NAME EXECUTION_TYPE
--------------- --------------- --------------
SQLPArKZoYD EXEC_76 CONVERT SQLSET
SQLPArKZoYD EXEC_77 TEST EXECUTE
BEGIN
DBMS_SQLPA.execute_analysis_task(
task_name => :tname,
execution_type => 'compare performance',
execution_params => dbms_advisor.arglist(
'execution_name1',
'EXEC_76',
'execution_name2',
'EXEC_77')
);
END;
/
select task_name, EXECUTION_NAME, EXECUTION_TYPE
from USER_ADVISOR_EXECUTIONS
where task_name='SQLPArKZoYD';
TASK_NAME EXECUTION_NAME EXECUTION_TYPE
--------------- --------------- --------------
SQLPArKZoYD EXEC_76 CONVERT SQLSET
SQLPArKZoYD EXEC_77 TEST EXECUTE
SQLPArKZoYD EXEC_78 COMPARE PERFORMANCE
var x clob
exec :x:=dbms_sqlpa.report_analysis_task( -
task_name => 'SQLPArKZoYD', -
type=>'HTML', -
level => 'ALL', -
section => 'ALL', -
execution_name => 'EXEC_78');
set long 30000
set longchunksize 30000
set lines 30000
print x
Affichez le résultat en HTML. Voici une copie de la sortie correspondante :
Remarquez bien les découvertes de SQL Performance Analyzer; il s’agit de la même base de données, du même ordre SQL, du même contenu des tables. Pourtant, celui-ci vous annonce tranquillement :
- les performances sont meilleures,
- le plan d’exécution a changé,
- le nombre de lignes ramenées est inférieur à l’exécution originale,
Conclusion
Vous l’aurez compris, les outils ne sont pas tout. La méthodologie est une autre clé. Ne vous trompez pas, mon propos n’est pas de dire que Real Application Testing n’est pas à la hauteur de ses promesses. Il est fantastique ! Gardez quand même la tête froide.
Pour en terminer avec cet exemple, supprimez le schéma demo, la tache SQLPA et le SQL Tuning Set :
connect / as sysdba
drop user demo cascade;
exec dbms_sqlpa.drop_analysis_task( -
task_name => 'SQLPArKZoYD');
exec dbms_sqltune.drop_sqlset( -
sqlset_name=>'ArKZoYD');
Et notez, dans la même veine que :
- SQL Performance Analyzer ne déclenche pas les triggers on-logon qui peuvent, par exemple, positionner des variables de session ou des catégories pour les outlines ou les profils SQL
- SQL Performance Analyzer ne reproduit pas le contexte de session (ni les paramètres NLS dans ce cas, ni bien sur les variables)
- Votre base de données (donc le SQL Tuning Set) ne collecte pas toutes les variables binds mais un maximum de 100 variables et seulement si celles-ci sont dans les clauses WHERE ou HAVING selon la documentation Oracle.
