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.
Vérifier les baselines pour associer ensuite le SQL_ID au SQL_Handle.
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');
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.
Vérification de la modification.
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; /
Associer le bon plan d'exécution au sql_id.
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
Tester : relancer l’ordre SQL et voir son plan s’il n’y a pas le BATCHED.
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.
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)))))
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...
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.
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
Partager