![\"twitter\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" "\\"Share")
<\/a><\/a>![\"linkedin\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" "\\"Share")
<\/a>![\"mail\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" "\\"Share")
<\/a>![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/spm11-300x258.jpg\")
<\/p>\n
Hello<\/p>\n
En ce d\u00e9but d’ann\u00e9e, apr\u00e8s l’article PostgreSQL de Sarah sur le comparatif de sch\u00e9mas<\/a>, je vous propose une petite \u00e9tude de l’utilisation de SPM, ou SQL Plan Management sur une instance Oracle. L’id\u00e9e serait de savoir, comment utiliser cette fonctionnalit\u00e9 SQL Plan Management avec une version Oracle Standard Edition SE2.<\/p>\n <\/p>\n Pour rappel, SPM ou SQL Plan Management, est un outil permettant au DBA de stabiliser les plans d’ex\u00e9cutions de requ\u00eates qui ont tendance \u00e0 fortement d\u00e9vier. Il y a plusieurs raisons possibles \u00e0 cela.<\/p>\n Avant la version 11g, le DBA avait la possibilit\u00e9 d’utiliser les “outlines” afin de positionner des r\u00e8gles (hints). Cela s’effectuait selon le texte de la requ\u00eate ou bien selon son ‘hash_value’. Avec SQL Plan Management, il est n\u00e9cessaire de cr\u00e9er ce que l’on appelle des “SQL Plan baselines”. Ce sont des plans de r\u00e9f\u00e9rence sur lesquels s’appuie la fonctionnalit\u00e9 SPM pour garantir \u00e0 chaque requ\u00eate de trouver le chemin le plus optimal possible.<\/p>\n Comment pouvons nous l’impl\u00e9menter, et ce avec une version Standard Edition Oracle ? Que devons nous prendre en consid\u00e9ration ?<\/p>\n <\/p>\n Il est de coutume, lorsque l’on utilise des fonctionnalit\u00e9s “exotiques” Oracle, de toujours regarder au niveau du “licensing” si nous sommes dans les r\u00e8gles pour notre instance de bases de donn\u00e9es. Pour une version Standard Edition SE2, Oracle est assez clair, et ce, depuis la version 18c<\/p>\n <\/p>\n Utiliser SQL Plan Management en ne d\u00e9clarant qu’une et une seule SQL Plan baseline pour un et un seul SQL_ID donn\u00e9.<\/p>\n Pour la cr\u00e9ation du SQL Plan Baseline, vous pouvez :<\/p>\n Dans le cadre de cet article, il s’agit d’une requ\u00eate que nous avons charg\u00e9 en cache et dont nous souhaitons fixer le plan.<\/p>\n <\/p>\n Nous avons eu r\u00e9cemment un souci de performances chez un de nos clients.<\/p>\n Chaque nuit, un applicatif m\u00e9tier lance un traitement de chargement afin d’alimenter certaines tables. C’est gr\u00e2ce \u00e0 notre agent Alldb Capdata que nous mettons en place chez nos clients, dans le cadre du contrat d’infog\u00e9rance pass\u00e9 ensemble, que nous avons pu relever toutes les informations n\u00e9cessaires. <\/p>\n Vue globale des requ\u00eates consommatrices\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Pourcentage de la requ\u00eate la plus consommatrice<\/p>\n <\/p>\n Nous avons donc relev\u00e9 une requ\u00eate responsable de plus de 25% du temps totale ex\u00e9cut\u00e9 en base. Celle ci se pr\u00e9sente sous cette forme.<\/p>\n <\/p>\n Simple jointure entre 2 tables avec 3 bind variables …. mais il s’agit d’une fonction, encapsul\u00e9e dans un SELECT d’une autre requ\u00eate, qui g\u00e9n\u00e8re cette requ\u00eate ….. La requ\u00eate charge un premier plan utilis\u00e9 \u00e0 plus de 99%. Malheureusement pour nous, il n’est pas le plus optimal !<\/p>\n <\/p>\n Temps moyen premier plan<\/p>\n Temps moyen second plan<\/p>\n <\/p>\n Une moyenne de 3000 secondes pour le 1er plan, quand le 2nd comporte, lui, permet une ex\u00e9cution moyenne en moins de 60 secondes.<\/p>\n L’objectif, avec SPM, est donc de fixer une bonne foi pour toute le plan 4020957239 pour notre requ\u00eate.<\/p>\n <\/p>\n Afin de partir sur des bases saines, et avec accord du client, nous avons du sortir du cache la requ\u00eate en question.<\/p>\n Recherche si celle ci est encore en m\u00e9moire parmi nos requ\u00eates actives.<\/p>\n Relever son ADDRESS et son HASH_VALUE et la purger du cache.<\/p>\n Passer les statistiques sur les 2 tables qui forment notre requ\u00eate, en prenant soin de calculer les histogrammes.<\/p>\n Puis demander au client de relancer son traitement appelant cette m\u00eame requ\u00eate ! <\/p>\n Chargeons ces nouvelles valeurs dans une nouvelle SQL Plan Baseline.<\/p>\n <\/p>\n On se base sur “exact matching signature” de notre requ\u00eate pour cr\u00e9er notre SQL Plan Baseline. Notre requ\u00eate contient des bind variables et un CURSOR_SHARING \u00e0 EXACT.<\/p>\n <\/p>\n <\/p>\n Maintenant que nous avons notre requ\u00eate en cache et le SQL Plan Baseline cr\u00e9\u00e9, nous pouvons fixer cette baseline pour cette requ\u00eate.<\/p>\n <\/p>\n Nous v\u00e9rifions La SQL Plan baseline<\/p>\n <\/p>\n <\/p>\n Apr\u00e8s plusieurs jours d’utilisation, le client ne nous a plus remont\u00e9 de souci sur sa proc\u00e9dure de nuit.<\/p>\n Et nous avons pu voir que notre requ\u00eate en question utilise bien chaque jour notre SQL Plan Baseline au moment de son chargement en cache<\/p>\n <\/p>\n Les informations relatives au plan charg\u00e9 sont les suivantes :<\/p>\n <\/p>\n <\/p>\n <\/p>\n Voici quelques remarques que nous pouvons faire au sujet de SPM pour notre version Standard Edition SE2. Ici, nous avons pu fixer un plan qui nous paraissait bon, mais si nous n’avions pas pu trouver un plan correct, quel aurait \u00e9t\u00e9 le proc\u00e9d\u00e9 \u00e0 suivre ? <\/p>\n Nous aurions donc du passer par la proc\u00e9dure “DBMS_SPM.DROP_SQL_PLAN_BASELINE” \u00e0 chaque nouvelle SQL Plan Baseline correspondant \u00e0 notre EXACT MATCHING SIGNATURE….<\/p>\n Autre point \u00e0 prendre en consid\u00e9ration, la vue “dba_feature_usgae_statistics” remonte bien l’utilisation de SPM, avec la date de premi\u00e8re utilisation lors de la cr\u00e9ation de notre SQL Plan Baseline<\/p>\n <\/p>\n <\/p>\n N’h\u00e9sitez pas \u00e0 poster des commentaires si besoin !<\/p>\n : -)<\/p>\n <\/p>\n Hello En ce d\u00e9but d’ann\u00e9e, apr\u00e8s l’article PostgreSQL de Sarah sur le comparatif de sch\u00e9mas, je vous propose une petite \u00e9tude de l’utilisation de SPM, ou SQL Plan Management sur une instance Oracle. L’id\u00e9e serait de savoir, comment utiliser cette… Continuer la lecture Le SPM ?<\/h3>\n
\n
\nConfigurer le param\u00e8tre CURSOR_SHARING en fonction de votre application.<\/p>\n\n
\nIl \u00e9tait \u00e9galement possible de cr\u00e9er des “SQL Profiles” en version 10g. L’approche etait davantage bas\u00e9e sur le concept de stabilit\u00e9 de plans en fonctions des donn\u00e9es collect\u00e9es.<\/p>\nLicensing li\u00e9 au SQL Plan Management<\/h3>\n
\nOn ne rappellera jamais assez qu’un audit Oracle peut avoir de f\u00e2cheuses cons\u00e9quences si l’on utilise des options que l’on aurait, plus ou moins, oubli\u00e9 de d\u00e9clarer.<\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/spm1.jpg\")
![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/spm2.jpg\")
<\/p>\n\n
Cas d’\u00e9tude SQL Plan Management sur Oracle Standard Edition SE2<\/h3>\n
\nNous avons relev\u00e9 un souci pour une des requ\u00eates du traitement. Un plan d’ex\u00e9cution changeait et les temps de r\u00e9ponses se d\u00e9gradaient fortement et ce jusqu’au matin.
\nNous devions pour rem\u00e9dier \u00e0 ce probl\u00e8me, sortir le curseur, propre \u00e0 cette requ\u00eate, de la “shared pool”, via un DBMS_SHARED.PURGE.
\nPar la suite, un recalcule des statistiques sur les tables impact\u00e9es.<\/p>\n
\nEn effet, nous avons rapidement pos\u00e9 un diagnostic sur la requ\u00eate la plus consommatrice en terme de “Elapsed time” sur la p\u00e9riode donn\u00e9e.<\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/spm4-300x201.jpg\")
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/spm5.jpg\")
<\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/spm6.jpg\")
<\/p>\n
\nEn une nuit, le nombre d’ex\u00e9cution par secondes a d\u00e9pass\u00e9 les 85 !
\nNous avons remarqu\u00e9 que cette requ\u00eate avait 2 plans calcul\u00e9s<\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/spm7.jpg\")
<\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/spm8.jpg\")
<\/p>\n![\"\"](\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/spm9.jpg\")
<\/p>\nImpl\u00e9mentations de SQL Plan Management<\/h3>\n
select S.username, S.program,S.event,TO_CHAR(sysdate-(S.last_call_et\/86400),'YYYY\/MM\/DD HH24:MI:SS') "LAST_EXEC", SA.sql_text,SA.plan_hash_value,sa.address,sa.hash_value\r\nFROM V$SESSION S LEFT JOIN V$SQL SA ON (SA.sql_id = S.sql_id)\r\nWHERE S.TYPE!='BACKGROUND' AND S.STATUS = 'ACTIVE';\r\n\r\n<\/pre>\n
EXEC DBMS_SHARED_POOL.purge('&amp;amp;ADDRESS.,&amp;amp;HASH_VALUE.', 'C');<\/pre>\nEXEC DBMS_STATS.GATHER_TABLE_STATS('owner','table1',method_opt='FOR ALL COLUMNS SIZE AUTO', cascade=TRUE);\r\nEXEC DBMS_STATS.GATHER_TABLE_STATS('owner','table2',method_opt='FOR ALL COLUMNS SIZE AUTO', cascade=TRUE);<\/pre>\n
\nV\u00e9rifier que celle ci contient un nouveau chargement en cache avec cette fois ci le bon plan qui nous int\u00e9resse.<\/p>\nselect sql_id, sql_text, plan_hash_value, sql_plan_baseline , last_load_time, last_active_time from v$sql where sql_text like '%SELECT MIN(O.NUM_OPERATION)%';\r\n\r\nSQL_ID SQL_TEXT PLAN_HASH_VALUE SQL_PLAN_BASELINE\r\n------------- -------------------------------------- --------------- -------------------------\r\n0kjwvsctw4n3m SELECT MIN(O.NUM_OPERATION) FROM ***** 4020957239\r\n<\/pre>\n
VARIABLE bon_plan NUMBER\r\nBEGIN\r\n bon_plan:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id ='0kjwvsctw4n3m');\r\nEND;\r\n\r\nPL\/SQL procedure successfully completed.\r\n<\/pre>\n
select signature, sql_handle, plan_name, enabled, accepted, fixed\r\nfrom dba_sql_plan_baselines \r\nwhere signature = (select exact_matching_signature from v$sql where sql_id='0kjwvsctw4n3m');\r\n\r\nSIGNATURE SQL_HANDLE PLAN_NAME ENA ACC FIX\r\n------------------- ------------------------------ ------------------------------ --- --- ---\r\n772438594058345972 SQL_0ab840cf09a65df4 SQL_PLAN_0pf20tw4ucrgn0ada5bac YES YES NO\r\n\r\n<\/pre>\n
declare\r\n plan_charger pls_integer ;\r\nBEGIN\r\n plan_charger := dbms_spm.alter_sql_plan_baseline(\r\n sql_handle = 'SQL_0ab840cf09a65df4',\r\n plan_name ='SQL_PLAN_0pf20tw4ucrgn0ada5bac',\r\n attribute_name = 'fixed',\r\n attribute_value = 'YES'\r\n) ;\r\nEND ;\r\n\/\r\n\r\nPL\/SQL procedure successfully completed.\r\n<\/pre>\n
select b.SQL_HANDLE,b.PLAN_NAME,B.accepted,b.enabled,b.fixed,s.sql_id from dba_sql_plan_baselines b inner join v$sql s on (b.signature=s.EXACT_MATCHING_SIGNATURE);\r\n\r\nSQL_HANDLE PLAN_NAME ACC ENA FIX SQL_ID\r\n------------------------------ ------------------------------ --- --- --- -------------\r\nSQL_0ab840cf09a65df4 SQL_PLAN_0pf20tw4ucrgn0ada5bac YES YES YES 0kjwvsctw4n3m\r\n\r\n<\/pre>\n
select sql_id, substr(sql_text,1,25), plan_hash_value, sql_plan_baseline , last_load_time, last_active_time from v$sql where sql_id='0kjwvsctw4n3m';\r\n\r\nSQL_ID SUBSTR(SQL_TEXT,1,25) PLAN_HASH_VALUE SQL_PLAN_BASELINE LAST_LOAD_TIME LAST_ACTIVE_TIME\r\n------------- ------------------------- --------------- ----------------------------------- ------------------- -------------------\r\n0kjwvsctw4n3m SELECT MIN(O.NUM_OPERATIO 4020957239 SQL_PLAN_0pf20tw4ucrgn0ada5bac 2023-01-11\/12:22:06 2023\/01\/11 12:41:46\r\n<\/pre>\n
SELECT PLAN_TABLE_OUTPUT FROM V$SQL s, DBA_SQL_PLAN_BASELINES b, TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(b.sql_handle,b.plan_name,'basic')) t\r\nWHERE s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE AND b.PLAN_NAME=s.SQL_PLAN_BASELINE AND s.SQL_ID='0kjwvsctw4n3m';\r\nPLAN_TABLE_OUTPUT\r\n--------------------------------------------------------------------------------\r\n--------------------------------------------------------------------------------\r\nSQL handle: SQL_0ab840cf09a65df4\r\nSQL text: SELECT MIN(O.NUM_OPERAT*************\r\n--------------------------------------------------------------------------------\r\n--------------------------------------------------------------------------------\r\nPlan name: SQL_PLAN_0pf20tw4ucrgn0ada5bac Plan id: 182082476\r\nEnabled: YES Fixed: YES Accepted: YES Origin: MANUAL-LOAD-FROM-CURSOR-CACHE\r\nPlan rows: From dictionary\r\n--------------------------------------------------------------------------------\r\nPlan hash value: 4020957239\r\n--------------------------------------------------------------------------------\r\n| Id | Operation |\r\n--------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT |\r\n| 1 | SORT AGGREGATE |\r\n| 2 | NESTED LOOPS SEMI |\r\n| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| *************** |\r\n| 4 | INDEX RANGE SCAN | INDX_*******WEB_AFF |\r\n| 5 | TABLE ACCESS BY INDEX ROWID | *************** |\r\n| 6 | INDEX UNIQUE SCAN | INDX_**** |\r\n--------------------------------------------------------------------------------\r\n\r\n<\/pre>\n
Remarques li\u00e9s \u00e0 SQL Plan Management<\/h3>\n
\nLa version Standard Edition nous permet d’utiliser le param\u00e8tre “optimizer_capture_sql_plan_baselines” \u00e0 TRUE, mais Oracle ne nous autorise \u00e0 utiliser que une et une seule SQL Plan Baseline.<\/p>\n\r\nNAME TYPE VALUE\r\n------------------------------------ ----------- ------------------------------\r\noptimizer_capture_sql_plan_baselines boolean FALSE\r\n<\/pre>\n
select name c1, detected_usages c2, first_usage_date c3, currently_used c4\r\nfrom dba_feature_usage_statistics where first_usage_date is not null and name like '%SQL Plan%';\r\n\r\n times first used\r\nfeature used used now\r\n--------------------------------------------- -------- ------------------- -----\r\nSQL Plan Directive 68 2021\/09\/15 17:54:04 TRUE\r\nSQL Plan Management 1 2023\/01\/07 22:49:00 TRUE\r\n\r\n<\/pre>\n
<\/a><\/a><\/a>","protected":false},"excerpt":{"rendered":"