Baseline avec deux plans d'exécutions pour un ordre avec le hint INDEX

Version imprimable

25/09/2019, 13h52
Ikebukuro

Baseline avec deux plans d'exécutions pour un ordre avec le hint INDEX

Salut,

Je viens vous voir car je me suis cassé les dents sur un problème théorique en faisant des tests à la maison.
J'ai imaginé la situation suivante : un client utilise le logiciel d'un éditeur, celui-ci a mis un hint INDEX dans un SELECT et, bien sur, nous n'avons pas la main sur l'ordre SQL pour changer celui-ci.

J'ai une table de 10 010 clients avec 10 DUPONT et 10 010 MARTIN.
Dans le SELECT 1, je filtre sur le nom DUPONT (10 clients sur 10 010) : le plan d'exécution est bon, il faut utiliser l'index vue la sélectivité.
Dans le SELECT 2, je filtre sur le nom MARTIN (10 000 clients sur 10 010) : le plan d'exécution est mauvais, il utilise l'index à cause du hint MAIS, vue la sélectivité, il devrait faire un FTS (j'ai testé en enlevant le hint et le coût est divisé par 10).

Mon problème est le suivant : j'arrive à avoir dans ma base les deux ordres SQL (celui avec le hint du logiciel et celui que je joue sans hint), j'ai deux bons plans d'exécutions.
Ce que je veux, c'est créer une baseline sur le SELECT 1 et lui associer les deux plans d'exécutions. ATTENTION : le SELECT 2 a un SQL ID différent du premier car le hint INDEX ne s'y trouve pas. Donc je veux associer au SQL ID 1 le plan d'exécution du SQL ID 1 et aussi celui du SQL ID 2.

OK, rien ne me dit que le CBO prendra parfois le FTS car je continuerai a exécuter le SELECT 1 avec le hint mais je voudrais quand même faire cette expérience pour voir ce qui se passe. En effet, un hint n'est pas un ordre mais un conseil, peut-être que le CBO, en voyant les deux plans, ne tiendra pas compte du hint et utilisera l'histogramme pour évaluer la sélectivité et basculer, selon le nom, d'un plan à l'autre.

Voilà, vous avez des idées? OK, c'est un peu tordu mais quand on ne peut pas modifier un ordre SQL, il faut se creuser les méninges 8-)
26/09/2019, 10h43
Mohamed.Houri

Bonjour,

Une SPM Baseline peut contenir plusieurs plans d’exécution ENABLED et ACCEPTED. Mais ces plans ne seront utilisés que si:

• La signature de la Baseline correspond à l’exact_matching_signature du SQL_ID
• Le PlanId des plans de la Baseline sont reproductibles (PlanId=PHV2)

Vous voulez donc attacher deux plans d’exécution au SQL ID 1 à savoir :

• Un plan utilisant l’index provenant de l’optimisation du SQL_ID1
• Un plan utilisant le full table scan provenant du SQL_ID 2 (une version modifiée du SQL_ID1)

Le premier plan peut être chargé directement depuis le Cursor cache ou depuis AWR (12cR2). Le deuxième plan peut être transférer du SQL_ID2 vers le SQL_ID1 en utilisant le script que vous pouvez trouver à la fin de cet article:

https://blog.toadworld.com/why-my-ex...-shared-part-i

Ainsi vous aurez deux SPMs pour votre SQL_ID 1. Quant au plan qui sera choisi, Oracle va reproduire les deux plans et choisir le meilleur (donc il va falloir supporter un temps supplémentaire de parsing)

Bien Cordialement
Mohamed Houri
26/09/2019, 13h19
Ikebukuro

Je te remercie beaucoup, je regarderai cela dès que j'aurai beaucoup de temps libre :):)

Bon ben, ça marche pas...

Voilà ce que j'ai fais.

=======================================
Le jeu d'essai.
=======================================
Création d'une table avec une PK et un jeu d'essai très réduit de dix personnes : 9 MARTIN et 1 DUPONT, dans cet ordre.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL> CREATE TABLE zztest(id NUMBER(10) CONSTRAINT zztest_pk_id PRIMARY KEY, nom VARCHAR2(50 CHAR), prenom VARCHAR2(50 CHAR) );
Table created.
 
 
SQL> Begin
      For i in 1..9
      Loop
        INSERT INTO zztest VALUES(i, 'MARTIN', 'TOM'); 
      End loop ;
    End ;
    /
PL/SQL procedure successfully completed.
 
 
SQL> INSERT INTO zztest VALUES(10, 'DUPONT', 'David'); 
 
SQL> commit;
Commit complete.
 
 
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname =>'HR', tabname=>'ZZTEST', cascade=>TRUE);

***************************************************************
On exécute l'ordre sans et avec un ORDER BY en récupérant le plan d'exécution à chaque fois.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SQL> SELECT * FROM zztest;
        ID NOM               PRENOM                                            
---------- ------------------------------
         1 MARTIN            TOM                                               
         2 MARTIN            TOM                                               
         3 MARTIN            TOM                                               
         4 MARTIN            TOM                                               
         5 MARTIN            TOM                                               
         6 MARTIN            TOM                                               
         7 MARTIN            TOM                                               
         8 MARTIN            TOM                                               
         9 MARTIN            TOM                                               
        10 DUPONT            David                                             
10 rows selected. 
 
 
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT                                                                                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  akk1mjcvrdz1q, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
SELECT * FROM zztest                                                                                                                                                                                    
 
Plan hash value: 3582063246                                                                                                                                                                             
 
----------------------------------------------------------------------------                                                                                                                            
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                            
----------------------------------------------------------------------------                                                                                                                            
|   0 | SELECT STATEMENT  |        |       |       |     3 (100)|          |                                                                                                                            
|   1 |  TABLE ACCESS FULL| ZZTEST |    10 |   140 |     3   (0)| 00:00:01 |                                                                                                                            
----------------------------------------------------------------------------                                                                                                                            
 
13 rows selected.

On trie sur le nom maintenant.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> SELECT * FROM zztest ORDER BY nom;
        ID NOM               PRENOM                                            
---------- -------------------------------
        10 DUPONT            David                                             
         2 MARTIN            TOM                                               
         3 MARTIN            TOM                                               
         4 MARTIN            TOM                                               
         6 MARTIN            TOM                                               
         7 MARTIN            TOM                                               
         8 MARTIN            TOM                                               
         9 MARTIN            TOM                                               
         1 MARTIN            TOM                                               
         5 MARTIN            TOM                                               
10 rows selected.

Le plan d'exécution n'est pas le même, il y a un SORT en plus.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT                                                                                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  a1k6qugb0hdz1, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
SELECT * FROM zztest ORDER BY nom                                                                                                                                                                       
 
Plan hash value: 4012859079                                                                                                                                                                             
 
-----------------------------------------------------------------------------                                                                                                                           
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                           
-----------------------------------------------------------------------------                                                                                                                           
|   0 | SELECT STATEMENT   |        |       |       |     4 (100)|          |                                                                                                                           
|   1 |  SORT ORDER BY     |        |    10 |   140 |     4  (25)| 00:00:01 |                                                                                                                           
|   2 |   TABLE ACCESS FULL| ZZTEST |    10 |   140 |     3   (0)| 00:00:01 |                                                                                                                           
-----------------------------------------------------------------------------                                                                                                                           
 
14 rows selected.

Nous allons maintenant associer le plan 3582063246 à l'ordre de SQL_ID a1k6qugb0hdz1 : à savoir, un plan sans SORT pour un SELECT avec un ORDER BY.
Pour cela, on utilise les baselines du SPM (SQL Plan Management).

Etape 1 : créer une baseline pour l'ordre avec un ORDER BY.

Code:

1
2
SQL> variable cnt number;
SQL> execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => 'a1k6qugb0hdz1');

Vérification de cette baseline : elle a le SQL_HANDLE SQL_47464290ab153052 (ce qui est son id) et le plan a l'identifiant SQL_PLAN_4fjk2k2pjac2k192f0eef (on notera qu'on n'utilise pas dans cette vue le plan_hash_value, allez savoir pourquoi).
Autre point, pas de SQL_ID dans cette vue non plus.

Code:

1
2
3
4
SQL> select SQL_TEXT, SQL_HANDLE, SIGNATURE, PLAN_NAME, CREATED, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where UPPER(SQL_TEXT) like 'SELECT * FROM ZZTEST%';
SQL_TEXT                                 SQL_HANDLE                SIGNATURE	PLAN_NAME           			CREATED                      	ENA ACC FIX
-------------------------------------------------------------------------------------------------------------------------------- ---------------------------
SELECT * FROM zztest ORDER BY nom        SQL_47464290ab153052      5.1359E+18 	SQL_PLAN_4fjk2k2pjac2k192f0eef  28-SEP-19 09.52.12.000000000 AM YES YES NO

Il faut maintenant ajouter dans cette baseline le plan de l'ordre de SQL_ID akk1mjcvrdz1q.
Ah, pb, KO...

Code:

1
2
3
4
5
6
7
SQL> execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_handle => 'SQL_47464290ab153052', sql_id => 'akk1mjcvrdz1q', plan_hash_value => '3582063246');	
 
 
SQL> select SQL_TEXT, SQL_HANDLE, SIGNATURE, PLAN_NAME, CREATED, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where UPPER(SQL_TEXT) like 'SELECT * FROM ZZTEST%';
SQL_TEXT							SQL_HANDLE			   SIGNATURE    PLAN_NAME      					CREATED 						ENA ACC FIX
--------------------------------------------------------------------------- --- --- -----------------------------------------------------------------
SELECT * FROM zztest ORDER BY nom	SQL_47464290ab153052	5.1359E+18  SQL_PLAN_4fjk2k2pjac2k192f0eef  28-SEP-19 10.42.42.000000 AM	YES YES NO

Mohammed, j'ai essayé ton code (en enlevant les trim et to_number car j'avais un message d'erreur) mais cela ne marche pas.
Voilà mon code.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
declare
      v_sql_text clob;
      ln_plans pls_integer;
begin
     select replace(sql_fulltext, chr(00), ' ') into v_sql_text from v$sqlarea where sql_id = 'a1k6qugb0hdz1';
     ln_plans := dbms_spm.load_plans_from_cursor_cache (sql_id => 'akk1mjcvrdz1q', plan_hash_value => 3582063246, sql_text => v_sql_text);
     dbms_output.put_line('Plans Loaded: '||ln_plans);
end;
/
 
 
SQL> select SQL_TEXT, SQL_HANDLE, SIGNATURE, PLAN_NAME, CREATED, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where UPPER(SQL_TEXT) like 'SELECT * FROM ZZTEST%';
SQL_TEXT							SQL_HANDLE			   SIGNATURE    PLAN_NAME      					CREATED 						ENA ACC FIX
--------------------------------------------------------------------------- --- --- -----------------------------------------------------------------
SELECT * FROM zztest ORDER BY nom	SQL_47464290ab153052	5.1359E+18  SQL_PLAN_4fjk2k2pjac2k192f0eef  28-SEP-19 10.42.42.000000 AM	YES YES NO

Mais je ne comprends pas ta remarque dans ton message précédent :
"Le premier plan peut être chargé directement depuis le Cursor cache ou depuis AWR (12cR2). Le deuxième plan peut être transférer du SQL_ID2 vers le SQL_ID1 en utilisant le script que vous pouvez trouver à la fin de cet article:
Ainsi vous aurez deux SPMs pour votre SQL_ID 1." On peut avoir deux baselines pour un SQL_ID? Sinon , je ne vois pas comment, dans ton code, on transfère le SQL_ID2 vers le SQL_ID 1.

Citation:
Envoyé par Ikebukuro
Bon ben, ça marche pas...

Mohammed, j'ai essayé ton code (en enlevant les trim et to_number car j'avais un message d'erreur) mais cela ne marche pas.
Voilà mon code.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 declare v_sql_text clob; ln_plans pls_integer; begin select replace(sql_fulltext, chr(00), ' ') into v_sql_text from v$sqlarea where sql_id = 'a1k6qugb0hdz1'; ln_plans := dbms_spm.load_plans_from_cursor_cache (sql_id => 'akk1mjcvrdz1q', plan_hash_value => 3582063246, sql_text => v_sql_text); dbms_output.put_line('Plans Loaded: '||ln_plans); end; / SQL> select SQL_TEXT, SQL_HANDLE, SIGNATURE, PLAN_NAME, CREATED, ENABLED, ACCEPTED, FIXED from dba_sql_plan_baselines where UPPER(SQL_TEXT) like 'SELECT * FROM ZZTEST%'; SQL_TEXT SQL_HANDLE SIGNATURE PLAN_NAME CREATED ENA ACC FIX --------------------------------------------------------------------------- --- --- ----------------------------------------------------------------- SELECT * FROM zztest ORDER BY nom SQL_47464290ab153052 5.1359E+18 SQL_PLAN_4fjk2k2pjac2k192f0eef 28-SEP-19 10.42.42.000000 AM YES YES NO
Mais je ne comprends pas ta remarque dans ton message précédent :
"Le premier plan peut être chargé directement depuis le Cursor cache ou depuis AWR (12cR2). Le deuxième plan peut être transférer du SQL_ID2 vers le SQL_ID1 en utilisant le script que vous pouvez trouver à la fin de cet article:
Ainsi vous aurez deux SPMs pour votre SQL_ID 1." On peut avoir deux baselines pour un SQL_ID? Sinon , je ne vois pas comment, dans ton code, on transfère le SQL_ID2 vers le SQL_ID 1.
Quand vous dites cela ne fonctionne pas c’est peut-être clair pour vous mais pas pour moi. Cela dit, vous devez utiliser le plan_hash_value du sql_id "from'' et non du sql_id ''to''. i.e. 4012859079

Je viens d’utiliser votre modèle en chargeant deux SPM ayant 2 plans différents exactement comme expliqué dans mon message précédent :

• Le premier depuis le Cursor cache
• Le second en utilisant le script TransferSPM (que j’ai copié/collé depuis le site Toad)

Oui on peut avoir plusieurs SPM plans pour un seul SQL_ID. C’est pour cette raison que j’ai mis dans le graphique suivant en haut à droite : reproduce accepted and enabled planS
https://hourim.wordpress.com/2015/10...n-interaction/

En revanche, votre cas est anormalement choisi. Comment voudriez-vous qu’Oracle procède à un tri que la requête n’ a pas demandé ? Passez d’un FULL TABLE SCAN à un INDEX RANGE SCAN est compréhensible. Mais lister des lignes triées alors que cela n’a pas été demandé est, je pense, impossible. Le transfert de SPM dans ce cas précis aurait dû normalement être refusé par Oracle.

Si vous voulez avoir plus de détails sur les SPMs je vous laisse, ci-dessous, la liste des articles à ce sujet

https://hourim.wordpress.com/?s=SPM+

Notamment ceux qui traitent de l’interaction entre le CBO et les SPM

Bien Cordialement
Mohamed

03/10/2019, 10h48
Ikebukuro

Oups, je viens de me rendre compte que j'ai mélangé dans mes tests deux choses; au début je parlais de hint INDEX mais j'ai ensuite fait des tests sur le ORDER BY :oops::oops:
13/10/2019, 10h27
Ikebukuro

Bon, finalement j'abandonne l'histoire de la baseline car c'est trop complexe et je me rabats sur le SQL Translator Framework avec le package DBMS_SQL_TRANSLATOR.