Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Membre averti Avatar de macben
    Inscrit en
    mars 2004
    Messages
    546
    Détails du profil
    Informations personnelles :
    Âge : 36

    Informations forums :
    Inscription : mars 2004
    Messages : 546
    Points : 433
    Points
    433

    Par défaut Plan d'exécution et base line

    Bonjour,

    Est-il possible de forcer une requête (identifiée par son sql_id) à utiliser un plan d'exécution (identifié par un plan_hash_value) ?

    Par un base line ?

    Je m'explique. Sur un infocentre j'ai une requête qui s'est mise à dérivé du jour au lendemain (de 4 mins de temps d'exécution à 6 heures), sans raison apparente (ni ré-écriture, ni administration bdd).

    Je constate dans la vue dba_hist_sql_plan que le plan a changé depuis que l'on constate la dérive.

    Je voudrais donc ré-affecter l'ancien plan. (sans passer par un profile qui est une mécanique que je trouve un peu compliquée).

    J'ai les base_line d'activées et d'ailleurs sur cette requête j'en ai 5 dont je vois les noms depuis la database control. Or si je vais dans la vue dba_sql_plan_baselines je ne retrouve pas mes baseline (identifié par leur sql_handle).

    Mes questions sont donc :

    - Pourquoi je ne vois pas mes baselines dans la vue dba_sql_plan_baselines alors que je les vois dans la database control ?
    - Comment définir un baseline depuis un plan_hash_value connu et l'affecter à un sql_id ?



    merci,

  2. #2
    Membre actif Avatar de petitfrere
    Profil pro
    Inscrit en
    septembre 2006
    Messages
    259
    Détails du profil
    Informations personnelles :
    Âge : 36
    Localisation : France

    Informations forums :
    Inscription : septembre 2006
    Messages : 259
    Points : 278
    Points
    278

    Par défaut

    Pour répondre a votre première question: Je ne sais pas je viens de faire un test, je vois bien mes baselines depuis le grid control et depuis sqlplus


    Pour votre deuxième question, il y a un tres bon article de arkzoid (petite recherche sur "big brother" )qui explique comment faire cela ou du moins vous pouvez le faire assez facilement apres lecture de son article, la difficulté (celle que j'ai pu rencontrer) et de récupérer le bon hash de la requête (caché dans les meandre de l'historique de tt les requêtes SQL).

    Il m'est arrivé de régénérer le bon plan d’exécution en ré-exécutant la requête problématique a la main avec un 'HINT' pour utiliser le bon plan d'exec, et de récupérer son hash pour creer une baseline SQL

  3. #3
    Membre éprouvé

    Homme Profil pro
    Inscrit en
    mars 2010
    Messages
    501
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : mars 2010
    Messages : 501
    Points : 1 255
    Points
    1 255

    Par défaut

    Citation Envoyé par macben Voir le message
    Bonjour,

    Comment définir un baseline depuis un plan_hash_value connu et l'affecter à un sql_id ?
    J'ai essayé de reproduire votre cas, mais après plusieurs tentatives, je n'ai pas réussi à affecter une baseline prédéfinie à un sql_id précis. Je vous laisse ici mon modèle et si quelqu'un a une idée qu'il y contribue

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
     
    SQL>  create table t2
      2      (col1  number
      3      ,col2  varchar2(50)
      4      ,flag  varchar2(2));
     
     
    SQL>  insert into t2
      2      select rownum
      3      ,      lpad('X',50,'X')
      4      ,      case when rownum = 1
      5                  then 'Y1'
      6                  when rownum = 2
      7                  then 'Y2'
      8                  when mod(rownum,2) = 0
      9                  then 'N1'
     10                 else 'N2'
     11            end
     12     from   dual
     13    connect by rownum <= 100000;
     
    100000 rows created.
     
    SQL> create index i1 on t2 (flag);
     
    Index created.
     
     
    SQL> var n varchar2(2)
    SQL> exec :n := 'Y1';
     
    SQL> alter session set optimizer_capture_sql_plan_baselines = TRUE;
     
    SQL> select count(1) from t2 where flag = :n;
     
      COUNT(1)
    ----------
             1
     
    SQL> select count(1) from dba_sql_plan_baselines where sql_text like '%t2%';
     
      COUNT(1)
    ----------
             0
     
    SQL> select count(1) from t2 where flag = :n;
     
      COUNT(1)
    ----------
             1
     
    SQL> select count(1) from dba_sql_plan_baselines where sql_text like '%t2%';
     
      COUNT(1)
    ----------
             1 -- faut executer deux fois avant de le voir être capturé
     
    SQL> alter session set optimizer_capture_sql_plan_baselines = FALSE;
     
    SQL> select sql_handle,  accepted from dba_sql_plan_baselines where sql_text like '%t2 where flag%';
     
    SQL_HANDLE                     ACC
    ------------------------------ ---
    SQL_38c7f32b9603f5d8           YES
     
    SQL> select count(1) from t2 where flag = :n;
     
      COUNT(1)
    ----------
             1
     
    SQL> select * from table(dbms_xplan.display_cursor);
     
    SQL_ID  9zr7y0b3d8h5m, child number 2
    -------------------------------------
    select count(1) from t2 where flag = :n
     
    Plan hash value: 3900446664
     
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |       |       |     1 (100)|          |
    |   1 |  SORT AGGREGATE   |      |     1 |     3 |            |          |
    |*  2 |   INDEX RANGE SCAN| I1   |     9 |    27 |     1   (0)| 00:00:01 |
    --------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - access("FLAG"=:N)
     
    Note
    -----
       - SQL plan baseline SQL_PLAN_3jjzm5fb07xfs3f0011ba used for this statement
     
     
     
    SQL> alter session set optimizer_capture_sql_plan_baselines = TRUE;
     
    SQL> select /*+ full (t2) */ count(1) from t2 where flag = :n;
     
      COUNT(1)
    ----------
             1
     
    SQL> select /*+ full (t2) */ count(1) from t2 where flag = :n;
     
      COUNT(1)
    ----------
             1
     
    SQL> select sql_handle,  accepted from dba_sql_plan_baselines where sql_text like '%t2 where flag%';
     
    SQL_HANDLE                     ACC
    ------------------------------ ---
    SQL_38c7f32b9603f5d8           YES
    SQL_d82a3703500d9194           YES
     
    SQL> alter session set optimizer_capture_sql_plan_baselines = FALSE;
     
     
    SQL> select count(1) from t2 where flag = :n;
     
      COUNT(1)
    ----------
             1
     
    SQL> select * from table(dbms_xplan.display_cursor);
     
    SQL_ID  9zr7y0b3d8h5m, child number 2
    -------------------------------------
    select count(1) from t2 where flag = :n
     
    Plan hash value: 3900446664
     
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |       |       |     1 (100)|          |
    |   1 |  SORT AGGREGATE   |      |     1 |     3 |            |          |
    |*  2 |   INDEX RANGE SCAN| I1   |     9 |    27 |     1   (0)| 00:00:01 |
    --------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - access("FLAG"=:N)
     
    Note
    -----
       - SQL plan baseline SQL_PLAN_3jjzm5fb07xfs3f0011ba used for this statement
    Maintenant, ma requête sans le hint full, utilise le plan dans la baseline SQL_38c7f32b9603f5d8(index range scan)
    Mais je veux qu'elle utilise le plan de la baseline SQL_d82a3703500d9194 (full scan). Je vais donc commencer par
    rapatrier les infos du plan de cette baseline(full table scan)

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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
     
    SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name => 'SQL_PLAN_dhajr0d80v4cn1c6cf506'));
     
    --------------------------------------------------------------------------------
    SQL handle: SQL_d82a3703500d9194
    SQL text: select /*+ full (t2) */ count(1) from t2 where flag = :n
    --------------------------------------------------------------------------------
     
    --------------------------------------------------------------------------------
    Plan name: SQL_PLAN_dhajr0d80v4cn1c6cf506         Plan id: 476902662
    Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
    --------------------------------------------------------------------------------
     
    Plan hash value: 3321871023
     
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |     1 |     3 |   241   (3)| 00:00:01 |
    |   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
    |*  2 |   TABLE ACCESS FULL| T2   | 25000 | 75000 |   241   (3)| 00:00:01 |
    ---------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - filter("FLAG"=:N)
    que je vais finir par associer à ma requête (via son sql_id). Et c'est là où se situe mon problème.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
     
    var pls number
    SQL> begin
      2   :pls := dbms_spm.load_plans_from_cursor_cache(sql_id => '9zr7y0b3d8h5m'
      3                                                ,plan_hash_value => 3321871023
      4                                                ,sql_handle      => 'SQL_38c7f32b9603f5d8');
      5  end;
      6  /
    Malgré avoir affecté le plan SQL_38c7f32b9603f5d8 à mon SQL_id, ce plan n'est pas choisi. J'ai ensuite "disablé" le plan de l'ancienne baseline (index range scan) mais cela n'a pas permis au plan SQL_38c7f32b9603f5d8 d'être affecté à mon SQL_id.

    Il est peut-être tout simplement impossible d'attribuer une baseline (un plan d'execution) à une requête ayant une "force_matching_signature" différente du sql_id qui est à l'origine de la capture de ladite baseline.
    Bien Respectueusement
    www.hourim.wordpress.com

    "Ce qui se conçoit bien s'énonce clairement"

  4. #4
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    octobre 2007
    Messages
    5 602
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : octobre 2007
    Messages : 5 602
    Points : 11 535
    Points
    11 535

    Par défaut

    How does SQL Plan Management match SQL statements to SQL plan baselines?
    SQL statements are matched to SQL plan baselines using the signature of the SQL statement. A signature is a unique SQL identifier generated from the normalized SQL text (uncased and with whitespaces removed).

  5. #5
    Membre éprouvé

    Homme Profil pro
    Inscrit en
    mars 2010
    Messages
    501
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations forums :
    Inscription : mars 2010
    Messages : 501
    Points : 1 255
    Points
    1 255

    Par défaut

    Est-il possible de forcer une requête (identifiée par son sql_id) à utiliser un plan d'exécution (identifié par un plan_hash_value) ?
    Par un base line ?
    Oui c'est possible. J'ai enfin réussi à le simuler. Reprenez mon exemple ci-dessus
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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
     
    SQL> SELECT count(1) FROM t2 WHERE flag = :n;
     
    SQL_ID  8mq4n7mxp5ddr, child number 1
    -------------------------------------
    SELECT count(1) FROM t2 WHERE flag = :n
     
    Plan hash value: 3900446664
     
    --------------------------------------------------------------------------
    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |       |       |     1 (100)|          |
    |   1 |  SORT AGGREGATE   |      |     1 |     6 |            |          |
    |*  2 |   INDEX RANGE SCAN| I1   |     2 |    12 |     1   (0)| 00:00:01 |
    --------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - access("FLAG"=:N)
     
    Note
    -----
       - dynamic sampling used for this statement (level=2)
       - SQL plan baseline SQL_PLAN_3jjzm5fb07xfs3f0011ba used for this statement
    j'ai une requête protégée par une baseline qui utilise un index range scan. Je ne veux plus qu'elle soit protégée par ce plan. Je veux qu'elle le soit par un autre plan d'une autre baseline qui utilise un full scan. Ce plan correspond au select suivant
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
     
    SQL> SELECT /*+ full (t2) */ count(1) FROM t2 WHERE flag = :n;
     
    SQL_ID  23665n23gqrju, child number 0
    -------------------------------------
    SELECT /*+ full (t2) */ count(1) FROM t2 WHERE flag = :n
     
    Plan hash value: 3321871023
     
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |       |       |   452 (100)|          |
    |   1 |  SORT AGGREGATE    |      |     1 |     6 |            |          |
    |*  2 |   TABLE ACCESS FULL| T2   |     2 |    12 |   452   (3)| 00:00:02 |
    ---------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - filter("FLAG"=:N)
     
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    Ok. Je vais donc commencer par créér une baseline de ce second sql

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     
    SQL> -- create a sql plan baseline for full plan
    SQL> variable cnt number;
    SQL> execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => '23665n23gqrju');
     
    PL/SQL procedure successfully completed.
    Et je n'oublie pas de "disabler" ma baseline d'origine
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
     
    SQL> -- disable l'ancienne baseline
    SQL> declare
      2   ln_ps number;
      3  begin
      4  
      5  ln_ps := dbms_spm.alter_sql_plan_baseline(sql_handle => 'SQL_38c7f32b9603f5d8'
      6                                           ,plan_name => 'SQL_PLAN_3jjzm5fb07xfs3f0011ba'
      7                                           ,attribute_name => 'enabled'
      8                                           ,attribute_value => 'NO');
      9  end;
     10  /
     
    PL/SQL procedure successfully completed.
    Si bien que maintenant j'ai l'image suivante:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
     
    SQL> select sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;
     
    SQL_HANDLE                     PLAN_NAME                      ENA ACC
    ------------------------------ ------------------------------ --- ---
    SQL_38c7f32b9603f5d8           SQL_PLAN_3jjzm5fb07xfs3f0011ba NO  YES  -- ancienne baseline avec index
    SQL_d82a3703500d9194           SQL_PLAN_dhajr0d80v4cn1c6cf506 YES YES  -- nouvelle baseline avec full scan
    Enfin il ne me reste plus qu' affecter le sql_id du full scan(23665n23gqrju) + son plan avec le full scan(23665n23gqrju) à mon SQL_HANDLE qui correspond à mon toute première baseline (SQL_38c7f32b9603f5d8)
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
     
    SQL> declare
      2   ln_ps number;
      3  begin
      4  
      5  ln_ps := dbms_spm.load_plans_from_cursor_cache(sql_id => '23665n23gqrju'
      6                                           ,plan_hash_value => 3321871023
      7                                           ,sql_handle => 'SQL_38c7f32b9603f5d8');
      8  end;
      9  /
     
    PL/SQL procedure successfully completed.
    Et voilà ce que j'obtiens lorsque j'exécute ma requête d'origine
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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
     
    SQL> SELECT count(1) FROM t2 WHERE flag = :n;
     
      COUNT(1)
    ----------
             2
     
    SQL_ID  8mq4n7mxp5ddr, child number 1
    -------------------------------------
    SELECT count(1) FROM t2 WHERE flag = :n
     
    Plan hash value: 3321871023
     
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |       |       |   239 (100)|          |
    |   1 |  SORT AGGREGATE    |      |     1 |     6 |            |          |
    |*  2 |   TABLE ACCESS FULL| T2   |   763 |  4578 |   239   (2)| 00:00:01 |
    ---------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - filter("FLAG"=:N)
     
    Note
    -----
       - SQL plan baseline SQL_PLAN_3jjzm5fb07xfs1c6cf506 used for this statement
    Bien Respectueusement
    www.hourim.wordpress.com

    "Ce qui se conçoit bien s'énonce clairement"

Discussions similaires

  1. Plan d'exécution pas logique
    Par pat29 dans le forum Administration
    Réponses: 6
    Dernier message: 07/03/2008, 14h37
  2. Réponses: 12
    Dernier message: 22/06/2006, 10h26
  3. Plan d' exécution
    Par rod59 dans le forum Décisions SGBD
    Réponses: 2
    Dernier message: 15/06/2006, 21h50
  4. Comparer des plan d'exécution
    Par sygale dans le forum Oracle
    Réponses: 7
    Dernier message: 06/04/2006, 17h58
  5. Exécuter une Base Access
    Par Mout85 dans le forum Access
    Réponses: 2
    Dernier message: 23/06/2005, 12h43

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo