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!