Blog

  • Données / Sécurité, Oracle

Stabiliser l’exécution des requêtes SQL avec SQL Plan Baseline (SPM)

 

Une fonctionnalité Oracle à ne pas occulter pour stabiliser les performances et fixer le plan d’exécution des requêtes SQL est l’utilisation des plans de référence ou baseline, fonctionnalité qui a été introduite avec la version 11g d’Oracle Enterprise Edition. Cette option vous affranchit de toute implémentation de type SQL_Profile ou autre stored outline.

Ainsi, lors de la mise en œuvre d’un partitionnement mensuel (INTERVAL) sur certaines tables chez un client, les temps d’exécution des requêtes se comportaient de façon erratique et pouvaient varier de quelques secondes à plusieurs minutes.
Du fait de l’implémentation du partitionnement, les plans d’exécution étaient totalement différents de ceux d’origine et, de plus, les statistiques pas toujours actualisées pour le segment courant, ce qui nuisait grandement à la recherche du meilleur plan d’exécution pour certaines requêtes.

Pour palier ce manque et stabiliser les plans d’exécution, je pris l’option d’utiliser les plans SQL ‘baseline’ qui garantissent l’exécution du plan quelle que soit la fraîcheur des statistiques sur les tables partitionnées.

En scrutant les tables AWR, si vous disposer des options ‘diagnostic et tuning pack’, il est possible d’extraire les différents plans associés à une même requête et sélectionner ensuite le meilleur plan d’exécution.

Ainsi, la requête suivante nous permet d’extraire tous les plans d’exécution pour une période de 31 jours sur les tables qui nous intéressent :

       select ss.sql_id,
               ss.PLAN_HASH_VALUE,
               avg(ELAPSED_TIME_DELTA/(executions_delta*1000)),
               max(sn.END_INTERVAL_TIME),
               sum(executions_delta)
               from DBA_HIST_SQLSTAT ss,DBA_HIST_SNAPSHOT sn
               where (ss.sql_id) = (select distinct sql_id from
                                     (select distinct sql_id, count(PLAN_HASH_VALUE)
                                     from DBA_HIST_SQL_PLAN sp
                                     where OBJECT_NAME in ('HIST_RUBR_EASY','HIST_CONS_EASY')
                                     and trunc(timestamp) > trunc(sysdate - 31)
                                     group by sql_id having count(distinct PLAN_HASH_VALUE)>1)
                                     where ss.sql_id=sql_id)
               and ss.snap_id=sn.snap_id
               and executions_delta > 0
               and trunc(sn.END_INTERVAL_TIME) > trunc(sysdate -31)
               group by ss.sql_id, ss.PLAN_HASH_VALUE
               order by 1, 3;

Contrôle des requêtes SQL

Nous allons intégrer cette requête dans une petite procédure Check_SQL_Plan qui nous permettra de visualiser toutes les requêtes  ayant plusieurs plans d’exécution sur un mois, cette même procédure pourra générer le plan de référence (SQL_BASELINE) le plus performant lorsque la variable ‘load’ est positionnée à ‘Y’, ce qui appellera la procédure SQL_ID_STS
Procédure Check_SQL_Plan

CREATE OR REPLACE Procedure Check_SQL_Plan(load varchar2 default 'N')
is
      sql_id varchar2(30);
      save_sql_id varchar2(30);
      plan_hash_value integer;
      nb_sql_id integer;
      elapsed_time number(12,6);
      last_time date;
      executions integer;
      cursor multi_plan is
      select ss.sql_id,
             ss.PLAN_HASH_VALUE,
             avg(ELAPSED_TIME_DELTA/(executions_delta*1000)),
             max(sn.END_INTERVAL_TIME),
             sum(executions_delta)
             from DBA_HIST_SQLSTAT ss,DBA_HIST_SNAPSHOT sn
             where (ss.sql_id) = (select distinct sql_id from
                                   (select distinct sql_id, count(PLAN_HASH_VALUE)
                                   from DBA_HIST_SQL_PLAN sp
                                   where OBJECT_NAME in ('HIST_RUBR_EASY,'HIST_CONS_EASY')
                                   and trunc(timestamp) > trunc(sysdate - 31)
                                   group by sql_id having count(distinct PLAN_HASH_VALUE)>1)
                                   where ss.sql_id=sql_id)
             and ss.snap_id=sn.snap_id
             and executions_delta > 0
             and trunc(sn.END_INTERVAL_TIME) > trunc(sysdate -31)
             group by ss.sql_id, ss.PLAN_HASH_VALUE
             order by 1, 3;
begin
     nb_sql_id:=0;
     save_sql_id:='';
     open multi_plan;
     loop
     fetch multi_plan into sql_id, plan_hash_value, elapsed_time, last_time, executions;
     exit when multi_plan%notfound;
        if sql_id = save_sql_id then
             save_sql_id:=sql_id;
             DBMS_OUTPUT.PUT_LINE('sql_id '||sql_id||' plan_hash_value '||plan_hash_value||' elapsed_time '||elapsed_time||' executions '||executions||' last '||last_time);
      else
             nb_sql_id:=nb_sql_id+1;
             save_sql_id:=sql_id;
             DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------------------------');
             DBMS_OUTPUT.PUT_LINE('SQL_ID '||sql_id||' PLAN_HASH_VALUE '||plan_hash_value||' ELAPSED_TIME '||elapsed_time||' EXECUTIONS '||executions||' LAST '||last_time);
                if load = 'Y' then
                     SQL_ID_STS (sql_id,plan_hash_value);
             end if;
      end if;
     end loop;
     close multi_plan;
     DBMS_OUTPUT.PUT_LINE('====================================================================================================');
     DBMS_OUTPUT.PUT_LINE('Number of SQL requests : '||nb_sql_id||' ');
end;
/

L’appel de la procédure Check_SQL_Plan retourne le résultat suivant :

SQL> set serveroutput on
SQL> exec Check_SQL_Plan;
----------------------------------------------------------------------------------------------------
SQL_ID bt964581jctg4 PLAN_HASH_VALUE 1174584832 ELAPSED_TIME 2026,803583 EXECUTIONS 10 LAST 24/01/2018 16:00
sql_id bt964581jctg4 plan_hash_value 1106752388 elapsed_time 3640,747133 executions 18 last 22/01/2018 19:00
----------------------------------------------------------------------------------------------------
..........................
..........................
----------------------------------------------------------------------------------------------------
SQL_ID 9y6tw62zt47y5 PLAN_HASH_VALUE 241959788 ELAPSED_TIME 1,991597 EXECUTIONS 60 LAST 26/03/2018 17:00
sql_id 9y6tw62zt47y5 plan_hash_value 1473071490 elapsed_time 3,69367 executions 42968 last 26/03/2018 17:00
sql_id 9y6tw62zt47y5 plan_hash_value 3249646734 elapsed_time 12,9634 executions 1625428 last 17/04/2018 16:30
sql_id 9y6tw62zt47y5 plan_hash_value 1827570884 elapsed_time 14,4605 executions 680208 last 05/04/2018 03:30
====================================================================================================
Number of SQL requests : 33
Procédure PL/SQL terminée avec succès.

Génération des plans de référence

Pour générer le plan de référence, nous devons appeler la procédure SQL_ID_STS en passant pour paramètres l’identifiant de la requête (sql_id) et celui du plan d’exécution (plan_hash_value)
Procédure SQL_ID_STS

create or replace Procedure SQL_ID_STS (sql_id varchar2,plan_hash_value integer)
is
     snap_min pls_integer;
     snap_max pls_integer;
     sts_name varchar2(40);
     filter   varchar2(80);
     my_int   pls_integer;
     baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;
begin
     select min(SNAP_ID) into snap_min from dba_hist_snapshot;
     select max(SNAP_ID) into snap_max from dba_hist_snapshot;
     DBMS_OUTPUT.PUT_LINE('Snap Min : '|| snap_min||' Snap Max : '||snap_max||' sql_id : '||sql_id||' plan_hash_value : '||plan_hash_value);
     sts_name:=sql_id||'_STS';
     dbms_sqltune.create_sqlset(sqlset_name => sts_name , description => sts_name);
     filter:='sql_id='||CHR(39)||sql_id||CHR(39);
     filter:='sql_id='||CHR(39)||sql_id||CHR(39)||' and plan_hash_value='||plan_hash_value;
     open baseline_ref_cur for
     select VALUE(p) from table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(snap_min,snap_max,filter,NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) p;
     DBMS_SQLTUNE.LOAD_SQLSET(sts_name, baseline_ref_cur);
     filter:='sql_id='||CHR(39)||sql_id||CHR(39)||' and plan_hash_value='||plan_hash_value;
     my_int := dbms_spm.load_plans_from_sqlset (sqlset_name => sts_name,sqlset_owner => 'SYS',basic_filter => filter, fixed => 'YES', enabled => 'YES');
     DBMS_OUTPUT.PUT_line('Load baseline plan => sql_id : '||CHR(39)||sql_id||CHR(39)||' plan_hash_value : '||plan_hash_value||' Loaded Plan => '||my_int);
     dbms_sqltune.drop_sqlset(sqlset_name => sts_name);
     close baseline_ref_cur;
end;
/
SQL> exec SQL_ID_STS ('9y6tw62zt47y5',241959788);

Pour générer le plan de référence, il suffit d’exécuter la procédure SQL_ID_STS puis de contrôler l’identifiant du plan de référence de la manière suivante :

SQL> exec SQL_ID_STS ('9y6tw62zt47y5',241959788);
Snap Min : 40796 Snap Max : 52471 sql_id : 9y6tw62zt47y5 plan_hash_value : 241959788
Load baseline plan => sql_id : '9y6tw62zt47y5' plan_hash_value : 241959788 Loaded Plan => 1
Procédure PL/SQL terminée avec succès.
SQL> select SQL_HANDLE from DBA_SQL_PLAN_BASELINES where trunc(created)=trunc(sysdate);
SQL_HANDLE
------------------------------
SQL_c3274b5d9b99c6e4
SQL>

Contrôle des plans de référence

Pour retrouver la requête et son plan d’exécution dans DBA_SQL_PLAN_BASELINES, il faut recalculer l’identifiant de la requête (SQL_ID) à partir de son texte en précisant l’identifiant du plan de référence désiré (SQL_HANDLE).
Ainsi, la procédure suivante permet de contrôler l’identifiant de la requête (SQL_ID) et son plan d’exécution (PLAN_HASH_VALUE) pour un plan de référence donné :

SQL> set serveroutput on
Create or replace procedure WHICH_SQL_ID (v_sql_handle in varchar2)
is
v_sqlid VARCHAR2(13);
v_num number;
BEGIN
dbms_output.put_line('SQL_ID       '||' '|| 'PLAN_HASH_VALUE' || ' ' || 'SQL_HANDLE                    ' || ' ' || 'PLAN_NAME');
dbms_output.put_line('-------------'||' '|| '---------------' || ' ' || '------------------------------' || ' ' || '--------------------------------');
for a in (select sql_handle, plan_name, trim(substr(g.PLAN_TABLE_OUTPUT,instr(g.PLAN_TABLE_OUTPUT,':')+1))
          plan_hash_value,
          sql_text
                 from (select t.*,
                              c.sql_handle,
                              c.plan_name,
                              c.sql_text from dba_sql_plan_baselines c,
                              table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(c.sql_handle, c.plan_name)) t
                 where c.sql_handle = v_sql_handle) g
          where PLAN_TABLE_OUTPUT like 'Plan hash value%') loop
v_num := to_number(sys.UTL_RAW.reverse(sys.UTL_RAW.SUBSTR(sys.dbms_crypto.hash(src => UTL_I18N.string_to_raw(a.sql_text || chr(0),'AL32UTF8'), typ => 2),9,4)) || sys.UTL_RAW.reverse(sys.UTL_RAW.SUBSTR(sys.dbms_crypto.hash(src => UTL_I18N.string_to_raw(a.sql_text || chr(0),'AL32UTF8'), typ => 2),13,4)),RPAD('x', 16, 'x'));
v_sqlid := '';
FOR i IN 0 .. FLOOR(LN(v_num) / LN(32))
LOOP
v_sqlid := SUBSTR('0123456789abcdfghjkmnpqrstuvwxyz',FLOOR(MOD(v_num / POWER(32, i), 32)) + 1,1) || v_sqlid;
END LOOP;
dbms_output.put_line(v_sqlid ||' ' || rpad(a.plan_hash_value,15) || ' ' || rpad(a.sql_handle,30) ||  ' ' || rpad(a.plan_name,30));
end loop;
end;
/

Il suffit ensuite de renseigner l’identifiant du plan de référence pour la procédure which_sql_id afin d’afficher la requête à laquelle le plan d’exécution de référence s’applique :

SQL> set serveroutput on
SQL> set lines 132
SQL> exec which_sql_id('SQL_c3274b5d9b99c6e4');
SQL_ID        PLAN_HASH_VALUE SQL_HANDLE                     PLAN_NAME
------------- --------------- ------------------------------ --------------------------------
9y6tw62zt47y5 241959788       SQL_c3274b5d9b99c6e4           SQL_PLAN_c69ubbqdtmjr4a08d0f40
Procédure PL/SQL terminée avec succès.

La vérification du plan d’exécution de référence s’effectue en renseignant les parmètres SQL_HANDLE et PLAN_NAME de la fonction DISPLAY_SQL_PLAN_BASELINE :

SQL> select * from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE
('SQL_c3274b5d9b99c6e4','SQL_PLAN_c69ubbqdtmjr4a08d0f40'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
SQL handle: SQL_c3274b5d9b99c6e4
SQL text: WITH TABL AS (SELECT R.FILL_HORA_PARA AS FILL_HORA_PARA, R.FILL_HORA
          AS FILL_HORA_R, RL.FILL_HORA AS FILL_HORA_RL, H.NOMB AS NOMB,
          R.FILL_JOUR_ADDI AS FILL_JOUR_ADDI_R, RL.FILL_JOUR_ADDI AS
          FILL_JOUR_ADDI_RL, R.FILL_FORF_PARA AS FILL_FORF_PARA, R.FILL_FORF AS
          FILL_FORF_R, RL.FILL_FORF AS FILL_FORF_RL, R.CODE_TYPE_MONT AS
          CODE_TYPE_MONT, H.MONT_SALA AS MONT_SALA, R.FILL_MALA AS FILL_MALA_R,
          RL.FILL_MALA AS FILL_MALA_RL, R.FILL_REMU_PARA AS FILL_REMU_PARA,
          R.FILL_REMU AS FILL_REMU_R, RL.FILL_REMU AS FILL_REMU_RL,
          RL.MONT_HEUR_EQUI AS MONT_HEUR_EQUI, R.CODE_TYPE_RUBR AS
          CODE_TYPE_RUBR FROM HIST_RUBR_EASY H, RUBRIQUE R, RUBRIQUE_LIEN RL
          WHERE H.ID_RUBR = R.ID_RUBR AND H.PERI = R.PERI AND R.PERI = :B3 AND
          RL.ID_RUBR(+) = R.ID_RUBR AND RL.ID_SOCI(+) = :B2 AND H.ID_SALA = :B1
          ) SELECT NVL( SUM( CASE WHEN A.NOMB != 0 THEN CASE CASE WHEN
          A.FILL_HORA_PARA = 1 AND A.FILL_HORA_RL = 1 THEN 1 ELSE CASE WHEN
          A.FILL_HORA_PARA = 1 AND A.FILL_HORA_RL = 2 THEN 2 ELSE CASE WHEN
          A.FILL_HORA_PARA = 1 AND A.FILL_HORA_RL = 3 THEN 3 ELSE CASE WHEN
          A.FILL_HORA_R = 1 THEN 1 ELSE CASE WHEN A.FILL_HORA_R = 2 THEN 2 ELSE
          CASE WHEN A.FILL_HORA_R = 3 THEN 3 ELSE 2 END END END END END END
          WHEN 1 THEN 1 WHEN 3 THEN -1 ELSE 0 END ELSE 0 END * NVL(A.NOMB,0) )
          ,0) AS FILL_HORA__L, NVL( SUM( CASE WHEN A.NOMB != 0 AND
          A.CODE_TYPE_RUBR = 1 THEN CASE CASE WHEN A.FILL_JOUR_ADDI_RL = 1 THEN
          1 ELSE CASE WHEN A.FILL_JOUR_ADDI_RL = 2 THEN 2 ELSE CASE WHEN
          A.FILL_JOUR_ADDI_RL = 3 THEN 3 ELSE CASE WHEN A.FILL_JOUR_ADDI_R = 1
          THEN 1 ELSE CASE WHEN A.FILL_JOUR_ADDI_R = 2 THEN 2 ELSE CASE WHEN
          A.FILL_JOUR_ADDI_R = 3 THEN 3 ELSE 2 END END END END END END WHEN 1
          THEN 1 WHEN 3 THEN -1 ELSE 0 END ELSE 0 END * NVL(A.NOMB,0) ) ,0) AS
          FILL_JOUR_ADDI__L, NVL( SUM( CASE CASE WHEN A.FILL_FORF_PARA = 1 AND
          A.FILL_FORF_RL = 1 THEN 1 ELSE CASE WHEN A.FILL_FORF_PARA = 1 AND
          A.FILL_FORF_RL = 2 THEN 2 ELSE CASE WHEN A.FILL_FORF_PARA = 1 AND
          A.FILL_FORF_RL = 3 THEN 3 ELSE CASE WHEN A.FILL_FORF_R = 1 THEN 1
          ELSE CASE WHEN A.FILL_FORF_R = 2 THEN 2 ELSE CASE WHEN A.FILL_FORF_R
          = 3 THEN 3 ELSE 2 END END END END END END WHEN 1 THEN 1 WHEN 3 THEN
          -1 ELSE 0 END * DECODE(A.CODE_TYPE_MONT,0,-1,1) * NVL(A.MONT_SALA,0)
          ) ,0) AS FILL_FORF__L, NVL( SUM( CASE WHEN A.CODE_TYPE_RUBR = 1 THEN
          CASE CASE WHEN A.FILL_MALA_RL = 1 THEN 1 ELSE CASE WHEN
          A.FILL_MALA_RL = 2 THEN 2 ELSE CASE WHEN A.FILL_MALA_RL = 3 THEN 3
          ELSE CASE WHEN A.FILL_MALA_R = 1 THEN 1 ELSE CASE WHEN A.FILL_MALA_R
          = 2 THEN 2 ELSE CASE WHEN A.FILL_MALA_R = 3 THEN 3 ELSE 2 END END END
          END END END WHEN 1 THEN 1 WHEN 3 THEN -1 ELSE 0 END ELSE 0 END *
          DECODE(A.CODE_TYPE_MONT,0,-1,1) * NVL(A.MONT_SALA,0) ) ,0) AS
          FILL_MALA__L, NVL( SUM( CASE WHEN A.CODE_TYPE_RUBR = 1 THEN CASE CASE
          WHEN A.FILL_REMU_PARA = 1 AND A.FILL_REMU_RL = 1 THEN 1 ELSE CASE
          WHEN A.FILL_REMU_PARA = 1 AND A.FILL_REMU_RL = 2 THEN 2 ELSE CASE
          WHEN A.FILL_REMU_PARA = 1 AND A.FILL_REMU_RL = 3 THEN 3 ELSE CASE
          WHEN A.FILL_REMU_R = 1 THEN 1 ELSE CASE WHEN A.FILL_REMU_R = 2 THEN 2
          ELSE CASE WHEN A.FILL_REMU_R = 3 THEN 3 ELSE 2 END END END END END
          END WHEN 1 THEN 1 WHEN 3 THEN -1 ELSE 0 END ELSE 0 END *
          DECODE(A.CODE_TYPE_MONT,0,-1,1) * NVL(A.MONT_SALA,0) ) ,0) AS
          MONT_EXON_BRUT__L, NVL( SUM( CASE WHEN A.NOMB!=0 AND
          A.CODE_TYPE_RUBR=1 THEN CASE CASE WHEN A.MONT_HEUR_EQUI = 1 THEN 1
          ELSE CASE WHEN A.MONT_HEUR_EQUI = 2 THEN 2 ELSE CASE WHEN
          A.MONT_HEUR_EQUI = 3 THEN 3 ELSE 2 END END END WHEN 1 THEN 1 WHEN 3
          THEN -1 ELSE 0 END ELSE 0 END * NVL(A.NOMB,0) ) ,0) AS
          NOMB_HEUR_EQUI__L FROM TABL A
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_c69ubbqdtmjr4a08d0f40         Plan id: 2693599040
Enabled: YES     Fixed: YES     Accepted: YES     Origin: MANUAL-LOAD
Plan rows: From dictionary
--------------------------------------------------------------------------------
Plan hash value: 241959788
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                  |       |       |   430 (100)|          |       |       |
|   1 |  SORT AGGREGATE                       |                  |     1 |    92 |     0   (0)|          |       |       |
|   2 |   NESTED LOOPS OUTER                  |                  |   111 | 10212 |   430   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                       |                  |    99 |  7029 |   232   (0)| 00:00:01 |       |       |
|   4 |     PARTITION RANGE SINGLE            |                  |   108 |  2592 |    16   (0)| 00:00:01 |   KEY |   KEY |
|   5 |      TABLE ACCESS BY LOCAL INDEX ROWID| HIST_RUBR_EASY   |   108 |  2592 |    16   (0)| 00:00:01 |   KEY |   KEY |
|   6 |       INDEX RANGE SCAN                | PK_HRS_PART      |   113 |       |     3   (0)| 00:00:01 |   KEY |   KEY |
|   7 |     TABLE ACCESS BY INDEX ROWID       | RUBRIQUE         |     1 |    47 |     2   (0)| 00:00:01 |       |       |
|   8 |      INDEX UNIQUE SCAN                | PK_RUBRIQUE_2    |     1 |       |     1   (0)| 00:00:01 |       |       |
|   9 |    TABLE ACCESS BY INDEX ROWID        | RUBRIQUE_LIEN    |     1 |    21 |     2   (0)| 00:00:01 |       |       |
|  10 |     INDEX UNIQUE SCAN                 | PK_RUBRIQUE_LIEN |     1 |       |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------------
82 lignes sélectionnées.

 

kanam02
kanam02
Voir tous ses articles
Articles récents

Mentions légales & Politique de confidentialité