L'auteur de l'article est
Victor Varlamov (
varlamovVp18 ), OCP.
L'article original a été publié le 07/07/2017.
Remerciements particuliers à l'auteur de la traduction -
brutaltag .
Notre système de rapports exécute généralement des centaines de longues demandes qui sont déclenchées par divers événements. Les paramètres de requête sont une liste de clients et un intervalle de temps (quotidien, hebdomadaire, mensuel). En raison des données inégales dans les tables, une requête peut produire à la fois une ligne et un million de lignes, selon les paramètres du rapport (différents clients ont différents nombres de lignes dans les tables de faits). Chaque rapport est réalisé sous la forme d'un package avec une fonction principale qui prend les paramètres d'entrée, effectue des transformations supplémentaires, puis ouvre un curseur statique avec
des variables associées et renvoie finalement ce curseur ouvert. Le paramètre DB CURSOR_SHARING est défini sur FORCE.
Dans une telle situation, il faut faire face à de mauvaises performances, à la fois dans le cas de la réutilisation du plan de requête par l'optimiseur, et lorsque
la requête est
complètement analysée avec des paramètres sous forme de
littéraux . Les variables liées peuvent provoquer un plan de requête sous-optimal.
Dans son livre «Oracle Expert Practices», Alex Gorbachev raconte une histoire intéressante racontée par Tom Kite. Chaque lundi pluvieux, les utilisateurs devaient faire face à un plan de requête modifié. C'est difficile à croire, mais c'était:
«Selon les observations des utilisateurs finaux, lorsqu'il a beaucoup plu lundi, les performances de la base de données étaient terribles. Tous les autres jours de la semaine ou le lundi, il n'y a eu aucun problème sans pluie. Lors d'une conversation avec le DBA, Tom Kite a appris que les difficultés persistaient jusqu'à ce que la base de données soit forcée de redémarrer, après quoi les performances devenaient normales. C'était une telle solution: lundi pluvieux - redémarrage. »
Il s'agit d'un cas réel, et le problème a été résolu complètement sans aucune magie, uniquement grâce à une excellente connaissance du fonctionnement d'Oracle. Je montrerai la solution à la fin de l'article.
Voici un petit exemple du fonctionnement des variables liées.
Créez un tableau avec des données inégales.
SQL> CREATE TABLE VVP_HARD_PARSE_TEST(C1 NUMBER, C2 NUMBER, C3 VARCHAR2(300)); TABLE created. SQL> INSERT INTO VVP_HARD_PARSE_TEST SELECT ROWNUM C1, CASE WHEN LEVEL < 9 THEN 1 WHEN MOD(ROWNUM, 100)=99 THEN 99 ELSE 1000000 END C2, RPAD('A', 300, 'A') C3 FROM DUAL CONNECT BY LEVEL CREATE INDEX IND_VVP_HARD_PARSE_TEST_C2 ON VVP_HARD_PARSE_TEST(C2); INDEX created. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER, TABNAME => 'VVP_HARD_PARSE_TEST', CASCADE => TRUE, METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254'); PL/SQL PROCEDURE successfully completed. SQL> SELECT histogram FROM user_tab_columns WHERE table_name = 'VVP_HARD_PARSE_TEST' AND column_name = 'C2'; HISTOGRAM
En d'autres termes, nous avons une table VVP_HARD_PARSE_TEST avec un million de lignes, où dans 10 000 cas le champ est C2 = 99, 8 enregistrements avec C2 = 1 et le reste avec C2 = 1 000 000. L'histogramme à travers le champ C2 indique l'optimiseur Oracle sur cette distribution de données. Cette situation est connue sous le nom de
répartition inégale des données , et un histogramme peut vous aider à choisir le bon plan de requête en fonction des données demandées.
Nous observons des requêtes simples sur ce tableau. Evidemment pour la demande
SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = :psi p = 1, alors INDEX RANGE SCAN est le meilleur choix, pour le cas p = 1000000, il est préférable d'utiliser FULL TABLE SCAN. Les requêtes Query1 et Query1000000 sont identiques, à l'exception du texte dans les commentaires, cela est fait pour obtenir différents identificateurs de plan de requête.
DECLARE p NUMBER; v NUMBER; BEGIN V := 0; p := 1000000; FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); v : =0; p := 1; FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v);
Examinons maintenant les plans de requête:
SQL> SELECT sql_id, child_number, executions, plan_hash_value, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT % * FROM VVP_HARD_PARSE_TEST WHERE C2%'; SQL_ID CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE SQL_TEXT
Comme vous pouvez le voir, un plan pour différentes demandes n'est créé qu'une seule fois, au moment de la première exécution (un seul curseur enfant avec CHILD_NUMBER = 0 existe pour chaque demande). Chaque requête est exécutée deux fois (EXECUTION = 2). Lors d'une analyse matérielle, Oracle récupère les valeurs des variables associées et sélectionne un plan en fonction de ces valeurs. Mais il utilise le même plan pour la prochaine exécution, même si les variables associées ont changé lors de la deuxième exécution. Des plans non optimaux sont utilisés - Query1000000 avec la variable C2 = 1 utilise FULL TABLE SCAN au lieu de INDEX RANGE SCAN, et vice versa.
Il est clair que corriger l'application et utiliser des paramètres comme littéraux dans la requête est le moyen le plus approprié pour résoudre le problème, mais cela conduit à du SQL dynamique avec ses lacunes connues. Une autre façon consiste à désactiver la requête pour les variables liées (
ALTER SESSION SET "_OPTIM_PEEK_USER_BINDS" = FALSE ) ou à supprimer les histogrammes (
lien ).
L'une des solutions possibles est une utilisation alternative des politiques d'accès aux données, également connues sous le nom de
base de données privée virtuelle (contrôle d'accès détaillé, contrôle d'
accès fin , contrôle au niveau des lignes). Cela vous permet de modifier les demandes à la volée et peut donc entraîner une analyse complète du plan de demande chaque fois que la demande utilise un contrôle d'accès détaillé. Cette technique est décrite en détail dans un
article de Randalph Geist . L'inconvénient de cette méthode est le nombre croissant d'analyses complètes et l'incapacité à manipuler les plans de requête.
Voyez ce que nous allons faire maintenant. Après analyse de nos données, nous décidons de diviser les clients en trois catégories - Grand, Moyen et Petit (LMS ou 9-5-1) - en fonction du nombre de transactions ou de transactions au cours de l'année. De plus, le nombre de lignes dans le rapport dépend strictement de la période: mensuelle - grande, hebdomadaire - moyenne, quotidienne - petite ou 9-5-1. De plus, la solution est simple - nous allons faire dépendre le prédicat de la politique de sécurité de chaque catégorie et de chaque période. Ainsi, pour chaque demande, nous obtenons 9 curseurs enfants possibles. De plus, les requêtes avec des politiques différentes nous mèneront aux mêmes identifiants de requête, ce qui permet d'implémenter SQL PLAN MANAGEMENT (sql plan baseline).
SQL> CREATE TABLE HARD_PARSE_TABLE AS SELECT * FROM dual; TABLE created. SQL> CREATE TABLE CLIENTS_HP_STATISTICS (client_seqno NUMBER, client_id VARCHAR2(255), cnt_year NUMBER); TABLE created. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (1, 'SMALL CLIENT', 8); 1 ROW inserted. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (99, 'MIDDLE CLIENT', 50001); 1 ROW inserted. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (1000000,'LARGE CLIENT', 989992); 1 ROW inserted. SQL> CREATE OR REPLACE PACKAGE FORCE_HARD_PARSE_PKG IS gc_small CONSTANT NUMBER := 1; gc_middle CONSTANT NUMBER := 5; gc_large CONSTANT NUMBER := 9; gc_client_middle CONSTANT NUMBER := 50000; gc_client_large CONSTANT NUMBER := 500000; gc_daterange_middle CONSTANT NUMBER := 10; gc_daterange_large CONSTANT NUMBER := 50; FUNCTION FORCE_HARD_PARSE(in_schema VARCHAR2, in_object VARCHAR2) RETURN VARCHAR2; PROCEDURE SET_PREDICATE (n NUMBER); PROCEDURE SET_PREDICATES (p_daterange NUMBER DEFAULT NULL, p_clientrange NUMBER DEFAULT NULL); PROCEDURE CALC_PREDICATE; PROCEDURE CALC_PREDICATES(p_date_interval NUMBER DEFAULT 1, p_client_seqno NUMBER DEFAULT NULL, p_client_id VARCHAR2 DEFAULT NULL, p_client_seqno_list VARCHAR2 DEFAULT NULL ); END FORCE_HARD_PARSE_PKG; PACKAGE created. SQL> CREATE OR REPLACE PACKAGE BODY FORCE_HARD_PARSE_PKG IS g_predicate NUMBER;
Maintenant, si nous voulons intégrer une telle technologie dans le rapport, nous devons ajouter HARD_PARSE_TABLE à la requête (cela ne la gâchera pas un peu) et appeler CALC_PREDICATES avant l'exécution de la requête principale.
Voyons comment cette technique peut transformer l'exemple précédent:
DECLARE p NUMBER; v NUMBER; BEGIN V := 0; p := 1000000; FORCE_HARD_PARSE_PKG.SET_PREDICATE(1000000); FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); v := 0; p := 1; FORCE_HARD_PARSE_PKG.SET_PREDICATE(1); FOR rec IN (SELECT * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v);
Regardons les plans d'exécution:
SQL> SELECT sql_id, child_number, executions, plan_hash_value, sql_text, s.* FROM v$sql s WHERE sql_text LIKE 'SELECT % * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2%' ORDER BY 1,2; SQL_ID CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE SQL_TEXT
Ça a l'air super! Chaque requête est exécutée deux fois, avec différents curseurs enfants et différents plans. Pour le paramètre C2 = 1 000 000, nous voyons FULL TABLE SCAN dans les deux requêtes, et pour le paramètre C1 = 1, nous voyons toujours INDEX RANGE SCAN.
Au final, je donne une solution au cas des lundis pluvieux:
«Il s'avère que chaque week-end du dimanche, il y avait une sauvegarde à froid , de sorte que tous les plans de requête ont été régénérés lors de la première exécution lundi matin. L'un des employés a généralement commencé son travail plus tôt que les autres, et son plan de demande a été bien exécuté pour les autres utilisateurs au cours de la semaine. Cependant, s'il pleuvait, cet utilisateur était en retard au début de la journée de travail en raison de problèmes avec son itinéraire du matin. Ensuite, le calcul par lots des rapports a été le premier à démarrer, mais le plan de requête était complètement mauvais pour les cas restants en raison de valeurs inappropriées des variables associées. »
Et quelques vues système utiles:
•
dba_tab_histograms, all_tab_histograms, user_tab_histograms•
v$vpd_policy•
v$sql_bind_capture•
dba_hist_sqlbind