Bonjour,

J'ai le problème suivant chez un client et je n'arrive pas pour l'instant à le résoudre.

Prod : un ordre SQL de SQL_ID01 avec deux plans d'exécution, un OK, un KO. Un collègue a fait un SQL Profile pour forcer l'utilisation du bon plan et c'est OK, le SQL_ID01 utilise ce plan, le client est content :-)
PProd : le même ordre SQL de SQL_ID01 avec un mauvais plan d'exécution, le bon n'a jamais été créé.

J'ai essayé en pprod de résoudre le problème selon la procédure de mon collègue mais je n'ai pas les droits suffisants dans le Cloud Control pour créer un SQL Tuning Set et un SQL Profile.
OK, pas grave, je vais essayer alors d'utiliser une baseline car je connais mieux que les SQL Profiles.

En premier, je génère le bon plan d'exécution en ajoutant un hint à la requête car on veut remplacer "TABLE ACCESS BY INDEX ROWID BATCHED" par "TABLE ACCESS BY INDEX ROWID" : c'est la seule différence entre les deux plans en prod. Je suis bien conscient qu'en ajoutant ce hint, le texte de l'ordre SQL change et donc son sql id n'est plus SQL_ID01 mais SQL_ID02.

Une fois le bon plan d'exécution généré (838885426), je crée une baseline sur SQL_ID01 en lui associant le plan d'exécution calculé via l'ordre SQL_ID02, ce qui, je pense, ne devrais pas poser de problème (on est d'accord?).

Mon code.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
SQL> variable cnt number;
SQL> execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => '4bxmsr3vcyzpz');
Vérifier les baselines pour associer ensuite le SQL_ID au SQL_Handle.
On voit que FIXED vaut NO.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
SQL> select SQL_TEXT, SQL_HANDLE, CREATED, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where SQL_TEXT like '%accnt_sttlmnt_dt%acctng_date%acctng_mnths%AGD_ADMIN_D.CP_ALLCTN_CNTRCT_C%accnt_sttlmnt_dt%';
SQL_TEXT
--------------------------------------------------------------------------------
SQL_HANDLE
--------------------------------------------------------------------------------------------------------------------------------
CREATED                                                                     ENA ACC FIX
--------------------------------------------------------------------------- --- --- ---
select o__num, o__st, accnt_sttlmnt_dt, acctng_date, acctng_mnths, actvty_nmbr,
SQL_5afb6ec9823b3303
28-FEB-18 04.46.45.000000 PM                                                YES YES NO

la colonne FIXED est à NO : forcer à YES.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SQL_5afb6ec9823b3303',
    plan_name       => 'SQL_PLAN_5pyvft613qcs33b2c678d',
    attribute_name  => 'fixed',
    attribute_value => 'YES');
END;
/
Vérification de la modification.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
select SQL_HANDLE, plan_name, CREATED, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where SQL_HANDLE = 'SQL_5afb6ec9823b3303';
SQL_HANDLE                     PLAN_NAME                      CREATED                        ENA ACC FIX
------------------------------ ------------------------------ ------------------------------ --- --- ---
SQL_5afb6ec9823b3303           SQL_PLAN_5pyvft613qcs33b2c678d 01-MAR-18 11.16.35.000000 AM   YES YES YES
Associer le bon plan d'exécution au sql_id.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
SQL> declare  ln_ps number;
  begin
  ln_ps := dbms_spm.load_plans_from_cursor_cache(sql_id => '4bxmsr3vcyzpz' ,plan_hash_value => 838885426, sql_handle => 'SQL_5afb6ec9823b3303');
  end;
  /
PL/SQL procedure successfully completed.
Tester : relancer l’ordre SQL et voir son plan s’il n’y a pas le BATCHED.
J'ai eu du mal à reéxécuter l'ordre SQL car il a des bind variables, dont une pour deux valeurs différentes (opérateur IN et je crois que Oracle n'aime pas faire du EXECUTE IMMEDIATE dans ce cas là), il est très long etc etc donc je pense que mes tests ne sont pas bons. Merci à Franck pour son post disant comment exécuter un ordre via un SQL_ID.
Pour info, la clause WHERE : where ((state in (:1,:2)) and ((support <= :3) and ((pptmnt <= :4) and ((getet = :5) and (accnt > :6)))))

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
SQL> set autotrace on
SQL> declare  c clob;
  begin
  select sql_fulltext into c from v$sqlarea where  sql_id = '4bxmsr3vcyzpz';
  execute immediate c using 3,1,79680,79680,'cN0fU00005lDz',79653;
  end;
  /
 
PL/SQL procedure successfully completed.
PB : même en autotrace, on ne voit pas le plan d’exécution et donc on ne sait pas si la correction est OK...


Je vérifie les plans d'exécution : je n'ai pas le bon (838885426).
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
SQL> select plan_hash_value from v$sql where sql_id = '4bxmsr3vcyzpz';
 
PLAN_HASH_VALUE
---------------
       75831835
       75831835
       75831835

A partir de là je coince, impossible de savoir pourquoi Oracle ne prend pas en compte ma baseline et, plus grave, je ne sais pas où chercher les infos pour m'aider...


Voilà, si vous avez des conseils, je vous en remercie