SQL Baseline pas prise en compte
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:
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:
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:
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:
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:
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:
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:
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 :D
