Hello
En ce début d’année, après l’article PostgreSQL de Sarah sur le comparatif de schémas, je vous propose une petite étude de l’utilisation de SPM, ou SQL Plan Management sur une instance Oracle. L’idée serait de savoir, comment utiliser cette fonctionnalité SQL Plan Management avec une version Oracle Standard Edition SE2.
Le SPM ?
Pour rappel, SPM ou SQL Plan Management, est un outil permettant au DBA de stabiliser les plans d’exécutions de requêtes qui ont tendance à fortement dévier. Il y a plusieurs raisons possibles à cela.
- bind peeking. Je vous recommande cet excellent article de Ahmed Aangour sur les concepts des bind variables et les conséquences que cela implique.
- statistiques et passages d’histogrammes. Vos plans d’exécution sont fortement impactés si la répartition de vos valeurs est dite “skewed”.
- Le mécanisme ACS ou Adataptive Curosr Sharing, arrivé en version 11g, consiste à adapter les plans d’une même requête “bindée”. Le choix du plan se fera en fonction des variables enregistrées au moment du parsing.
Configurer le paramètre CURSOR_SHARING en fonction de votre application.- La valeur EXACT si vous souhaitez utiliser ACS pour les requêtes avec des bind variables (votre applicatif gère la création de binds).
- La valeur FORCE va transformer si possible les requêtes adhoc sans binds pour forcer à utiliser le mécanisme ACS (votre applicatif de génère que du code dynamique).
Avant la version 11g, le DBA avait la possibilité d’utiliser les “outlines” afin de positionner des règles (hints). Cela s’effectuait selon le texte de la requête ou bien selon son ‘hash_value’.
Il était également possible de créer des “SQL Profiles” en version 10g. L’approche etait davantage basée sur le concept de stabilité de plans en fonctions des données collectées.
Avec SQL Plan Management, il est nécessaire de créer ce que l’on appelle des “SQL Plan baselines”. Ce sont des plans de référence sur lesquels s’appuie la fonctionnalité SPM pour garantir à chaque requête de trouver le chemin le plus optimal possible.
Comment pouvons nous l’implémenter, et ce avec une version Standard Edition Oracle ? Que devons nous prendre en considération ?
Licensing lié au SQL Plan Management
Il est de coutume, lorsque l’on utilise des fonctionnalités “exotiques” Oracle, de toujours regarder au niveau du “licensing” si nous sommes dans les règles pour notre instance de bases de données.
On ne rappellera jamais assez qu’un audit Oracle peut avoir de fâcheuses conséquences si l’on utilise des options que l’on aurait, plus ou moins, oublié de déclarer.
Pour une version Standard Edition SE2, Oracle est assez clair, et ce, depuis la version 18c
Utiliser SQL Plan Management en ne déclarant qu’une et une seule SQL Plan baseline pour un et un seul SQL_ID donné.
Pour la création du SQL Plan Baseline, vous pouvez :
- aller chercher manuellement une requête encore dans le cache, puis charger celle ci
- mettre en place la capture automatique, mais attention, seule 1 SQL Plan Baseline ne sera possible pour 1 seule requête.
- migrer un stored outline vers une SQL Plan baseline via procédure MIGRATE_STORED_OUTLINE
- importer depuis une autre SQL Plan Baseline.
Dans le cadre de cet article, il s’agit d’une requête que nous avons chargé en cache et dont nous souhaitons fixer le plan.
Cas d’étude SQL Plan Management sur Oracle Standard Edition SE2
Nous avons eu récemment un souci de performances chez un de nos clients.
Chaque nuit, un applicatif métier lance un traitement de chargement afin d’alimenter certaines tables.
Nous avons relevé un souci pour une des requêtes du traitement. Un plan d’exécution changeait et les temps de réponses se dégradaient fortement et ce jusqu’au matin.
Nous devions pour remédier à ce problème, sortir le curseur, propre à cette requête, de la “shared pool”, via un DBMS_SHARED.PURGE.
Par la suite, un recalcule des statistiques sur les tables impactées.
C’est grâce à notre agent Alldb Capdata que nous mettons en place chez nos clients, dans le cadre du contrat d’infogérance passé ensemble, que nous avons pu relever toutes les informations nécessaires.
En effet, nous avons rapidement posé un diagnostic sur la requête la plus consommatrice en terme de “Elapsed time” sur la période donnée.
Vue globale des requêtes consommatrices Pourcentage de la requête la plus consommatrice
Nous avons donc relevé une requête responsable de plus de 25% du temps totale exécuté en base. Celle ci se présente sous cette forme.
Simple jointure entre 2 tables avec 3 bind variables …. mais il s’agit d’une fonction, encapsulée dans un SELECT d’une autre requête, qui génère cette requête …..
En une nuit, le nombre d’exécution par secondes a dépassé les 85 !
Nous avons remarqué que cette requête avait 2 plans calculés
La requête charge un premier plan utilisé à plus de 99%. Malheureusement pour nous, il n’est pas le plus optimal !
Temps moyen premier plan
Temps moyen second plan
Une moyenne de 3000 secondes pour le 1er plan, quand le 2nd comporte, lui, permet une exécution moyenne en moins de 60 secondes.
L’objectif, avec SPM, est donc de fixer une bonne foi pour toute le plan 4020957239 pour notre requête.
Implémentations de SQL Plan Management
Afin de partir sur des bases saines, et avec accord du client, nous avons du sortir du cache la requête en question.
Recherche si celle ci est encore en mémoire parmi nos requêtes actives.
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 FROM V$SESSION S LEFT JOIN V$SQL SA ON (SA.sql_id = S.sql_id) WHERE S.TYPE!='BACKGROUND' AND S.STATUS = 'ACTIVE';
Relever son ADDRESS et son HASH_VALUE et la purger du cache.
EXEC DBMS_SHARED_POOL.purge('&ADDRESS.,&HASH_VALUE.', 'C');Passer les statistiques sur les 2 tables qui forment notre requête, en prenant soin de calculer les histogrammes.
EXEC DBMS_STATS.GATHER_TABLE_STATS('owner','table1',method_opt='FOR ALL COLUMNS SIZE AUTO', cascade=TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('owner','table2',method_opt='FOR ALL COLUMNS SIZE AUTO', cascade=TRUE);Puis demander au client de relancer son traitement appelant cette même requête !
Vérifier que celle ci contient un nouveau chargement en cache avec cette fois ci le bon plan qui nous intéresse.
select 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)%'; SQL_ID SQL_TEXT PLAN_HASH_VALUE SQL_PLAN_BASELINE ------------- -------------------------------------- --------------- ------------------------- 0kjwvsctw4n3m SELECT MIN(O.NUM_OPERATION) FROM ***** 4020957239
Chargeons ces nouvelles valeurs dans une nouvelle SQL Plan Baseline.
VARIABLE bon_plan NUMBER BEGIN bon_plan:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id ='0kjwvsctw4n3m'); END; PL/SQL procedure successfully completed.
On se base sur “exact matching signature” de notre requête pour créer notre SQL Plan Baseline. Notre requête contient des bind variables et un CURSOR_SHARING à EXACT.
select signature, sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines where signature = (select exact_matching_signature from v$sql where sql_id='0kjwvsctw4n3m'); SIGNATURE SQL_HANDLE PLAN_NAME ENA ACC FIX ------------------- ------------------------------ ------------------------------ --- --- --- 772438594058345972 SQL_0ab840cf09a65df4 SQL_PLAN_0pf20tw4ucrgn0ada5bac YES YES NO
Maintenant que nous avons notre requête en cache et le SQL Plan Baseline créé, nous pouvons fixer cette baseline pour cette requête.
declare
plan_charger pls_integer ;
BEGIN
plan_charger := dbms_spm.alter_sql_plan_baseline(
sql_handle = 'SQL_0ab840cf09a65df4',
plan_name ='SQL_PLAN_0pf20tw4ucrgn0ada5bac',
attribute_name = 'fixed',
attribute_value = 'YES'
) ;
END ;
/
PL/SQL procedure successfully completed.
Nous vérifions La SQL Plan baseline
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); SQL_HANDLE PLAN_NAME ACC ENA FIX SQL_ID ------------------------------ ------------------------------ --- --- --- ------------- SQL_0ab840cf09a65df4 SQL_PLAN_0pf20tw4ucrgn0ada5bac YES YES YES 0kjwvsctw4n3m
Après plusieurs jours d’utilisation, le client ne nous a plus remonté de souci sur sa procédure de nuit.
Et nous avons pu voir que notre requête en question utilise bien chaque jour notre SQL Plan Baseline au moment de son chargement en cache
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'; SQL_ID SUBSTR(SQL_TEXT,1,25) PLAN_HASH_VALUE SQL_PLAN_BASELINE LAST_LOAD_TIME LAST_ACTIVE_TIME ------------- ------------------------- --------------- ----------------------------------- ------------------- ------------------- 0kjwvsctw4n3m SELECT MIN(O.NUM_OPERATIO 4020957239 SQL_PLAN_0pf20tw4ucrgn0ada5bac 2023-01-11/12:22:06 2023/01/11 12:41:46
Les informations relatives au plan chargé sont les suivantes :
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 WHERE s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE AND b.PLAN_NAME=s.SQL_PLAN_BASELINE AND s.SQL_ID='0kjwvsctw4n3m'; PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SQL handle: SQL_0ab840cf09a65df4 SQL text: SELECT MIN(O.NUM_OPERAT************* -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_0pf20tw4ucrgn0ada5bac Plan id: 182082476 Enabled: YES Fixed: YES Accepted: YES Origin: MANUAL-LOAD-FROM-CURSOR-CACHE Plan rows: From dictionary -------------------------------------------------------------------------------- Plan hash value: 4020957239 -------------------------------------------------------------------------------- | Id | Operation | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | SORT AGGREGATE | | 2 | NESTED LOOPS SEMI | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| *************** | | 4 | INDEX RANGE SCAN | INDX_*******WEB_AFF | | 5 | TABLE ACCESS BY INDEX ROWID | *************** | | 6 | INDEX UNIQUE SCAN | INDX_**** | --------------------------------------------------------------------------------
Remarques liés à SQL Plan Management
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 été le procédé à suivre ?
La version Standard Edition nous permet d’utiliser le paramètre “optimizer_capture_sql_plan_baselines” à TRUE, mais Oracle ne nous autorise à utiliser que une et une seule SQL Plan Baseline.
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean FALSE
Nous aurions donc du passer par la procédure “DBMS_SPM.DROP_SQL_PLAN_BASELINE” à chaque nouvelle SQL Plan Baseline correspondant à notre EXACT MATCHING SIGNATURE….
Autre point à prendre en considération, la vue “dba_feature_usgae_statistics” remonte bien l’utilisation de SPM, avec la date de première utilisation lors de la création de notre SQL Plan Baseline
select name c1, detected_usages c2, first_usage_date c3, currently_used c4
from dba_feature_usage_statistics where first_usage_date is not null and name like '%SQL Plan%';
times first used
feature used used now
--------------------------------------------- -------- ------------------- -----
SQL Plan Directive 68 2021/09/15 17:54:04 TRUE
SQL Plan Management 1 2023/01/07 22:49:00 TRUE
N’hésitez pas à poster des commentaires si besoin !
: -)
Continuez votre lecture sur le blog :
- DMV et problème de tri (David Baffaleuf) [SQL Server]
- Oracle et SQL Server: Les Statistiques (Benjamin VESAN) [OracleSQL Server]
- Oracle et SQL Server: L’optimizer (Benjamin VESAN) [OracleSQL Server]
- Oracle 23c : se protéger des attaques par injections SQL grâce à SQL Firewall (Emmanuel RAMI) [Oracle]
- Oracle Text pour DBA Oracle : Partie 1 (Capdata team) [Oracle]