Disjunctive Subquery

Il y’a quelques semaines j’ai eu à analyser la requête suivante sur une base de production:

SELECT Entities.EntityId, 
  Entities.EntityName, 
  Entities.LegalName 
FROM Entities 
WHERE (Entities.EntityId IN 
  (SELECT Agreements.PrincipalId 
  FROM Agreements 
  WHERE ( ( (Agreements.BusinessLine         IN (1, 2, 3) 
  AND Agreements.PrincipalManagingLocationId IN (144, 15, 16)) 
  AND Agreements.AgreementGroupId            IS NULL) 
  AND Agreements.BusinessLine NOT            IN (4)) 
  ) 
OR Entities.EntityId IN 
  (SELECT Agreements.CounterpartyId 
  FROM Agreements 
  WHERE ( ( (Agreements.BusinessLine         IN (1, 2, 3) 
  AND Agreements.PrincipalManagingLocationId IN (144, 15, 16)) 
  AND Agreements.AgreementGroupId            IS NULL) 
  AND Agreements.BusinessLine NOT            IN (4))   )) ;

Cette requête s’exécutait en un peu moins de 21 minutes pour retourner 258 lignes. Quand on regarde la requête de plus près on se rend compte qu’elle contient un bloc principal accédant à la table ENTITIES et une double sous requête séparée par une clause OR.

Voici son plan d’exécution :

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |      1 |        |    258 |00:20:52.02 |     210M|
|*  1 |  FILTER            |            |      1 |        |    258 |00:20:52.02 |     210M|
|   2 |   TABLE ACCESS FULL| ENTITIES   |      1 |  64326 |  64368 |00:00:00.08 |    1400 |
|*  3 |   TABLE ACCESS FULL| AGREEMENTS |  64368 |      1 |     19 |00:10:22.58 |     105M|
|*  4 |   TABLE ACCESS FULL| AGREEMENTS |  64349 |      1 |    239 |00:10:28.89 |     104M|
------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(( IS NOT NULL OR  IS NOT NULL))

   3 - filter(("AGREEMENTS"."PRINCIPALID"=:B1 AND

              INTERNAL_FUNCTION("AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID") AND

              "AGREEMENTS"."BUSINESSLINE"<>4 AND INTERNAL_FUNCTION("AGREEMENTS"."BUSINESSLINE")

              AND "AGREEMENTS"."AGREEMENTGROUPID" IS NULL))

   4 - filter(("AGREEMENTS"."COUNTERPARTYID"=:B1 AND

              INTERNAL_FUNCTION("AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID") AND

              "AGREEMENTS"."BUSINESSLINE"<>4 AND INTERNAL_FUNCTION("AGREEMENTS"."BUSINESSLINE")

              AND "AGREEMENTS"."AGREEMENTGROUPID" IS NULL))

La première chose qui nous marque dans le plan c’est que l’optimiseur (CBO) n’a pas unnesté la subquery pour effectuer une opération de semi-jointure (voir mon article sur les semi-joins) plus performante que l’opération FILTER qu’on voit dans le plan. En effet, les statistiques d’exécution du plan montrent que la table ENTITIES parcouru via un Full Table Scan retourne 64368 lignes (cf. colonne A-ROWS de l’opération 2) et la table AGREEMENTS est accédée à 2 reprises autant de fois qu’il y’a de lignes dans la table ENTITIES (cf. colonne STARTS du plan). Le nombre de logical I/Os qui en résulte est très important (210M). On est ici dans un cas de Disjunctive Subquery, c’est-à-dire qu’à cause de la clause OR le CBO ne peut unnester la sous-requête. Le seul moyen d’obtenir un plan efficace pour cette requête est de la réécrire afin de remplacer le OR par un UNION ALL. 

Voici la requête réécrite :

SELECT
  Entities.EntityId,
  Entities.EntityName,
  Entities.LegalName
FROM Entities
WHERE (Entities.EntityId IN
  (SELECT Agreements.PrincipalId
  FROM Agreements
  WHERE ( ( (Agreements.BusinessLine         IN (1, 2, 3)
  AND Agreements.PrincipalManagingLocationId IN (144, 15, 16))
  AND Agreements.AgreementGroupId            IS NULL)
  AND Agreements.BusinessLine NOT            IN (4))
  ))
UNION all
SELECT
  Entities.EntityId,
  Entities.EntityName,
  Entities.LegalName
FROM Entities
WHERE (Entities.EntityId NOT IN
  (SELECT Agreements.PrincipalId
  FROM Agreements
  WHERE ( ( (Agreements.BusinessLine         IN (1, 2, 3)
  AND Agreements.PrincipalManagingLocationId IN (144, 15, 16))
  AND Agreements.AgreementGroupId            IS NULL)
  AND Agreements.BusinessLine NOT            IN (4))
  ))
AND (Entities.EntityId IN
  (SELECT Agreements.CounterpartyId
  FROM Agreements
  WHERE ( ( (Agreements.BusinessLine         IN (1, 2, 3)
  AND Agreements.PrincipalManagingLocationId IN (144, 15, 16))
  AND Agreements.AgreementGroupId            IS NULL)
  AND Agreements.BusinessLine NOT            IN (4))
  )) ;

L’idée est de supprimer la clause OR en écrivant 2 requêtes distinctes séparées par un UNION ALL. 

Le plan qui en résulte est le suivant :

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |      1 |        |    258 |00:00:00.04 |    4308 |       |       |          |
|   1 |  SORT UNIQUE                   |             |      1 |    466 |    258 |00:00:00.04 |    4308 | 38912 | 38912 |34816  (0)|
|   2 |   UNION-ALL                    |             |      1 |        |    536 |00:00:00.04 |    4308 |       |       |          |
|   3 |    NESTED LOOPS                |             |      1 |        |    268 |00:00:00.02 |    2134 |       |       |          |
|   4 |     NESTED LOOPS               |             |      1 |    233 |    268 |00:00:00.02 |    1866 |       |       |          |
|*  5 |      TABLE ACCESS FULL         | AGREEMENTS  |      1 |    233 |    268 |00:00:00.02 |    1634 |       |       |          |
|*  6 |      INDEX UNIQUE SCAN         | PK_ENTITIES |    268 |      1 |    268 |00:00:00.01 |     232 |       |       |          |
|   7 |     TABLE ACCESS BY INDEX ROWID| ENTITIES    |    268 |      1 |    268 |00:00:00.01 |     268 |       |       |          |
|   8 |    NESTED LOOPS                |             |      1 |        |    268 |00:00:00.01 |    2174 |       |       |          |
|   9 |     NESTED LOOPS               |             |      1 |    233 |    268 |00:00:00.01 |    1906 |       |       |          |
|* 10 |      TABLE ACCESS FULL         | AGREEMENTS  |      1 |    233 |    268 |00:00:00.01 |    1634 |       |       |          |
|* 11 |      INDEX UNIQUE SCAN         | PK_ENTITIES |    268 |      1 |    268 |00:00:00.01 |     272 |       |       |          |
|  12 |     TABLE ACCESS BY INDEX ROWID| ENTITIES    |    268 |      1 |    268 |00:00:00.01 |     268 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter((INTERNAL_FUNCTION("AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID") AND "AGREEMENTS"."BUSINESSLINE"<>4 AND

              INTERNAL_FUNCTION("AGREEMENTS"."BUSINESSLINE") AND "AGREEMENTS"."AGREEMENTGROUPID" IS NULL))

   6 - access("ENTITIES"."ENTITYID"="AGREEMENTS"."PRINCIPALID")

  10 - filter((INTERNAL_FUNCTION("AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID") AND "AGREEMENTS"."BUSINESSLINE"<>4 AND

              INTERNAL_FUNCTION("AGREEMENTS"."BUSINESSLINE") AND "AGREEMENTS"."AGREEMENTGROUPID" IS NULL))

  11 - access("ENTITIES"."ENTITYID"="AGREEMENTS"."COUNTERPARTYID") 

On constate que le nombre de logical reads est passé de 210M à seulement 4308 et que la requête répond de manière quasi instantané au lieu de 20 minutes. Dans le nouveau plan on voit que l’opération FILTER a disparu pour laisser place à de vraies jointures (NESTED LOOP en l’occurrence).

L’inconvénient pour mon client était que cette requête était générée par un progiciel et qu’il n’était donc pas possible de la réécrire. On a donc laissé la requête telle quelle en espérant que l’éditeur nous fournisse rapidement une nouvelle version du progiciel intégrant la réécriture de la requête telle que je l’avais préconisée. Quelques semaines plus tard j’apprends que la base en question a migré de 11.2.0.2 à 11.2.0.4 et que les perfs s’en trouvent largement améliorées. En comparant les bases je me suis rendu compte que la requête précédente s’exécutait désormais très bien sans que le code n'ait été touchée mais avec le plan suivant :

 
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |      1 |        |     41 |00:00:00.01 |    3354 |       |       |          |
|   1 |  NESTED LOOPS                |             |      1 |     40 |     41 |00:00:00.01 |    3354 |       |       |          |
|   2 |   NESTED LOOPS               |             |      1 |     40 |     41 |00:00:00.01 |    3313 |       |       |          |
|   3 |    VIEW                      | VW_NSO_1    |      1 |     40 |     41 |00:00:00.01 |    3268 |       |       |          |
|   4 |     HASH UNIQUE              |             |      1 |     40 |     41 |00:00:00.01 |    3268 |  2170K|  2170K| 1340K (0)|
|   5 |      UNION-ALL               |             |      1 |        |    618 |00:00:00.01 |    3268 |       |       |          |
|*  6 |       TABLE ACCESS FULL      | AGREEMENTS  |      1 |     20 |    309 |00:00:00.01 |    1634 |       |       |          |
|*  7 |       TABLE ACCESS FULL      | AGREEMENTS  |      1 |     20 |    309 |00:00:00.01 |    1634 |       |       |          |
|*  8 |    INDEX UNIQUE SCAN         | PK_ENTITIES |     41 |      1 |     41 |00:00:00.01 |      45 |       |       |          |
|   9 |   TABLE ACCESS BY INDEX ROWID| ENTITIES    |     41 |      1 |     41 |00:00:00.01 |      41 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter(("AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID"=162 AND "AGREEMENTS"."BUSINESSLINE"=1 AND

              "AGREEMENTS"."BUSINESSLINE"<>4 AND "AGREEMENTS"."AGREEMENTGROUPID" IS NULL))

   7 - filter(("AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID"=162 AND "AGREEMENTS"."BUSINESSLINE"=1 AND

              "AGREEMENTS"."BUSINESSLINE"<>4 AND "AGREEMENTS"."AGREEMENTGROUPID" IS NULL))

   8 - access("ENTITIES"."ENTITYID"="COUNTERPARTYID")

On constate que désormais l’optimiseur a pu merger la sous requête pour pouvoir la joindre avec la table ENTITIES. On voit dans le plan les opérations suivantes :

- VIEW VW_NSO_1
- UNION-ALL

On comprend que le CBO a transformé la requête en instanciant une vue qu’il a nommé VW_NSO_1 et qu’il a créé 2 blocs dans la vue séparés par un UNION. D’ailleurs dans la trace 10053 on voit que la requête transformée par le CBO est la suivante :

 SELECT "ENTITIES"."ENTITYID" "ENTITYID",
  "ENTITIES"."ENTITYNAME" "ENTITYNAME",
  "ENTITIES"."LEGALNAME" "LEGALNAME"
FROM (
  (SELECT "AGREEMENTS"."COUNTERPARTYID" "COUNTERPARTYID"
  FROM "ALGOV5_CIB"."AGREEMENTS" "AGREEMENTS"
  WHERE "AGREEMENTS"."BUSINESSLINE"             =1
  AND "AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID"=162
  AND "AGREEMENTS"."AGREEMENTGROUPID"          IS NULL
  AND "AGREEMENTS"."BUSINESSLINE"              <>4
  )
UNION
  (SELECT "AGREEMENTS"."PRINCIPALID" "PRINCIPALID"
  FROM "ALGOV5_CIB"."AGREEMENTS" "AGREEMENTS"
  WHERE "AGREEMENTS"."BUSINESSLINE"             =1
  AND "AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID"=162
  AND "AGREEMENTS"."AGREEMENTGROUPID"          IS NULL
  AND "AGREEMENTS"."BUSINESSLINE"              <>4
  )) "VW_NSO_1",
  "ALGOV5_CIB"."ENTITIES" "ENTITIES"
WHERE "ENTITIES"."ENTITYID"="VW_NSO_1"."COUNTERPARTYID";

Dans la trace on voit également les éléments suivants :

Dans la trace on voit également les éléments suivants :
Registered qb: SET$7FD77EFD 0xd9e73be0 (SUBQ INTO VIEW FOR COMPLEX UNNEST SET$E74BECDC)
SU:   Checking validity of unnesting subquery SET$E74BECDC (#6)
*** 2015-06-01 10:49:47.061
SU:   Passed validity checks.
SU:   Transform an ANY subquery to semi-join or distinct.

Je pense que le "SUBQ INTO VIEW FOR COMPLEX UNNEST" correspond à la nouvelle fonctionnalité du CBO à l’origine de cette réécriture intelligente. Pour m’assurer que le nouveau plan était bien lié au code du CBO en 11.2.0.4 j’ai testé la requête en settant le parameter optimizer_features_enable à “11.2.0.2” et j’ai effectivement obtenu le mauvais plan à savoir celui sans le unnest de la subquery:

alter session set optimizer_features_enable='11.2.0.2'

Plan hash value: 289829209

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |      1 |        |     41 |00:07:24.66 |     214M|
|*  1 |  FILTER            |            |      1 |        |     41 |00:07:24.66 |     214M|
|   2 |   TABLE ACCESS FULL| ENTITIES   |      1 |  65613 |  65637 |00:00:00.02 |    1447 |
|*  3 |   TABLE ACCESS FULL| AGREEMENTS |  65637 |      1 |      3 |00:01:56.25 |     107M|
|*  4 |   TABLE ACCESS FULL| AGREEMENTS |  65634 |      1 |     38 |00:05:28.24 |     107M|
------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(( IS NOT NULL OR  IS NOT NULL))

   3 - filter(("AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID"=162 AND

              "AGREEMENTS"."PRINCIPALID"=:B1 AND "AGREEMENTS"."BUSINESSLINE"=1 AND

              "AGREEMENTS"."BUSINESSLINE"<>4 AND "AGREEMENTS"."AGREEMENTGROUPID" IS NULL))

   4 - filter(("AGREEMENTS"."COUNTERPARTYID"=:B1 AND

              "AGREEMENTS"."PRINCIPALMANAGINGLOCATIONID"=162 AND "AGREEMENTS"."BUSINESSLINE"=1

              AND "AGREEMENTS"."BUSINESSLINE"<>4 AND "AGREEMENTS"."AGREEMENTGROUPID" IS NULL))

A vrai dire la rééecriture par le CBO de la requête s’effectue dès la version 11.2.0.3. et non pas à partir de la version 11.2.0.4. J’ai pu valider ça en mettant le parametre optimizer_features_enable à “11.2.0.3” et observer que le plan observé en 11.2.0.4 était choisi.

Pour plus d’informations concernant le Disjunctive Subquery, je vous invite à lire ces 2 articles écrits par Mohamed Houri:
http://www.toadworld.com/platforms/oracle/w/wiki/11081.tuning-a-disjunctive-subquery.aspx  
https://hourim.wordpress.com/2014/05/12/disjunctive-subquery/

Forcer un plan d'une requête à partir d'une autre requête

Dans un article précèdent, j'avais montré comment il était possible grâce aux SQL profiles d'ajouter un hint à une requête identifiée par son SQL_ID sans avoir à toucher au code de la requête.

Cette fois il s'agit d'une autre problématique. J'ai deux requêtes similaires mais chacune avec un SQL_ID différent et je veux que la première utilise le plan de la 2ème. Grâce à un script de Kerry Osborne et l'utilisation de la procédure DBMS_SQLTUNE il est possible d'attacher le plan d'une requête A à une requête B.

Pour améliorer les performances d'une requête d'un client, j'ai récemment eu à utiliser cette technique. Je vais tenter dans cet article de vous expliquer comment j'ai procédé.

Mon client m'a envoyé un email la semaine dernière car il se plaignait d'une requête s'exécutant lentement en PROD alors qu'elle était plutôt rapide en recette. En me connectant sur les 2 bases et en exécutant la requête  j'ai pu m'apercevoir qu'effectivement en PROD le plan différait de celui en RECETTE. 

Voici un extrait du plan en PROD. On voit qu'il génère 1992K logical I/Os:

Plan hash value: 685980531


------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |               |      1 |        |     18 |00:01:18.50 |    1992K|      2 |       |       |          |
|   1 |  SORT AGGREGATE                        |               |     18 |      1 |     18 |00:00:00.01 |      74 |      0 |       |       |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID          | CI_FT         |     18 |      4 |     44 |00:00:00.01 |      74 |      0 |       |       |          |

Le plan en recette ne génère quant à lui que 2K logical reads.

Plan hash value: 3994356561

------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |               |      1 |        |     18 |00:00:01.40 |    2158 |     41 |       |       |          |
|   1 |  SORT AGGREGATE                        |               |     18 |      1 |     18 |00:00:00.01 |      55 |      0 |       |       |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID          | CI_FT         |     18 |      4 |     44 |00:00:00.01 |      55 |      0 |       |       |          |

Tout d'abord ma première idée a été de tester le plan de la base de recette en prod. Pour ce faire j'ai récupéré l'outline (c'est à dire l'ensemble des hints qui constituent le plan) du plan de la base de rectte en utilisant l'opion ADVANCED de la fonction DBMS_XPLAN:

SQL> explain plan for
........

SQL> SELECT * FROM table(dbms_xplan.display(NULL, NULL, 'advanced'));

.......

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$16")
      INDEX_RS_ASC(@"SEL$16" "FT2"@"SEL$16" ("CI_FT"."BILL_ID"))
      PUSH_SUBQ(@"SEL$16")
      NLJ_BATCHING(@"SEL$15" "PAY"@"SEL$15")
      USE_NL(@"SEL$15" "PAY"@"SEL$15")
      USE_NL(@"SEL$15" "FT"@"SEL$15")
      LEADING(@"SEL$15" "BILL2"@"SEL$15" "FT"@"SEL$15" "PAY"@"SEL$15")
      INDEX(@"SEL$15" "PAY"@"SEL$15" ("CI_PAY"."PAY_ID"))
      INDEX_RS_ASC(@"SEL$15" "FT"@"SEL$15" ("CI_FT"."MATCH_EVT_ID"))
      INDEX_RS_ASC(@"SEL$15" "BILL2"@"SEL$15" ("CI_BILL"."BILL_ID"))
      USE_HASH_AGGREGATION(@"SEL$17")
      USE_NL(@"SEL$17" "PAY"@"SEL$17")
      LEADING(@"SEL$17" "BILL2"@"SEL$17" "PAY"@"SEL$17")
      INDEX_RS_ASC(@"SEL$17" "PAY"@"SEL$17" ("CI_PAY"."ACCT_ID"))
      INDEX_RS_ASC(@"SEL$17" "BILL2"@"SEL$17" ("CI_BILL"."BILL_ID"))
      USE_HASH_AGGREGATION(@"SEL$0D753FAC")
      NLJ_BATCHING(@"SEL$0D753FAC" "FT"@"SEL$18")
      USE_NL(@"SEL$0D753FAC" "FT"@"SEL$18")
      USE_NL(@"SEL$0D753FAC" "FT2"@"SEL$19")
      LEADING(@"SEL$0D753FAC" "BILL2"@"SEL$18" "FT2"@"SEL$19" "FT"@"SEL$18")
      INDEX(@"SEL$0D753FAC" "FT"@"SEL$18" ("CI_FT"."MATCH_EVT_ID"))
      INDEX_RS_ASC(@"SEL$0D753FAC" "FT2"@"SEL$19" ("CI_FT"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$0D753FAC" "BILL2"@"SEL$18" ("CI_BILL"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$267CE17A" "A1"@"SEL$11" ("CI_FT"."BILL_ID"))
      NLJ_BATCHING(@"SEL$7B312CD2" "MATCH"@"SEL$12")
      USE_NL(@"SEL$7B312CD2" "MATCH"@"SEL$12")
      USE_NL(@"SEL$7B312CD2" "FT2"@"SEL$12")
      LEADING(@"SEL$7B312CD2" "A1"@"SEL$13" "FT2"@"SEL$12" "MATCH"@"SEL$12")
      INDEX(@"SEL$7B312CD2" "MATCH"@"SEL$12" ("CI_MATCH_EVT"."MATCH_EVT_ID"))
      INDEX_RS_ASC(@"SEL$7B312CD2" "FT2"@"SEL$12" ("CI_FT"."MATCH_EVT_ID"))
      INDEX_RS_ASC(@"SEL$7B312CD2" "A1"@"SEL$13" ("CI_FT"."BILL_ID"))
      USE_HASH_AGGREGATION(@"SEL$10")
      USE_NL(@"SEL$10" "TST"@"SEL$10")
      LEADING(@"SEL$10" "BILL2"@"SEL$10" "TST"@"SEL$10")
      NO_ACCESS(@"SEL$10" "TST"@"SEL$10")
      INDEX_RS_ASC(@"SEL$10" "BILL2"@"SEL$10" ("CI_BILL"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$2" "CI_FT"@"SEL$2" ("CI_FT"."BILL_ID"))
      INDEX(@"SEL$3" "CI_BSEG"@"SEL$3" ("CI_BSEG"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$4" "CI_BSEG"@"SEL$4" ("CI_BSEG"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$5" "CI_BSEG"@"SEL$5" ("CI_BSEG"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$6" "CI_BSEG"@"SEL$6" ("CI_BSEG"."BILL_ID"))
      NLJ_BATCHING(@"SEL$7" "ME"@"SEL$7")
      USE_NL(@"SEL$7" "ME"@"SEL$7")
      LEADING(@"SEL$7" "FT"@"SEL$7" "ME"@"SEL$7")
      INDEX(@"SEL$7" "ME"@"SEL$7" ("CI_MATCH_EVT"."MATCH_EVT_ID"))
      INDEX_RS_ASC(@"SEL$7" "FT"@"SEL$7" ("CI_FT"."BILL_ID"))
      NLJ_BATCHING(@"SEL$8" "L"@"SEL$8")
      USE_NL(@"SEL$8" "L"@"SEL$8")
      LEADING(@"SEL$8" "BCHAR"@"SEL$8" "L"@"SEL$8")
      INDEX(@"SEL$8" "L"@"SEL$8" ("CI_CHAR_VAL_L"."CHAR_TYPE_CD" "CI_CHAR_VAL_L"."CHAR_VAL"
              "CI_CHAR_VAL_L"."LANGUAGE_CD"))
      INDEX_RS_ASC(@"SEL$8" "BCHAR"@"SEL$8" ("CI_BILL_CHAR"."BILL_ID" "CI_BILL_CHAR"."CHAR_TYPE_CD"
              "CI_BILL_CHAR"."SEQ_NUM"))
      NO_ACCESS(@"SEL$9" "MA_BALANCE"@"SEL$9")
      NO_ACCESS(@"SEL$14" "ELEMENTS"@"SEL$14")
      INDEX_RS_ASC(@"SEL$20" "CI_FT"@"SEL$20" ("CI_FT"."BILL_ID"))
      NLJ_BATCHING(@"SEL$21" "ME"@"SEL$21")
      USE_NL(@"SEL$21" "ME"@"SEL$21")
      LEADING(@"SEL$21" "FT"@"SEL$21" "ME"@"SEL$21")
      INDEX(@"SEL$21" "ME"@"SEL$21" ("CI_MATCH_EVT"."MATCH_EVT_ID"))
      INDEX_RS_ASC(@"SEL$21" "FT"@"SEL$21" ("CI_FT"."BILL_ID"))
      INDEX_RS_ASC(@"SEL$1" "BILL"@"SEL$1" ("CI_BILL"."ACCT_ID"))
      OUTLINE(@"SEL$13")
      OUTLINE(@"SEL$12")
      OUTLINE(@"SEL$19")
      OUTLINE(@"SEL$18")
      OUTLINE(@"SEL$10")
      OUTLINE(@"SET$1")
      MERGE(@"SEL$13")
      OUTLINE(@"SEL$61262C81")
      OUTLINE(@"SEL$11")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$21")
      OUTLINE_LEAF(@"SEL$20")
      OUTLINE_LEAF(@"SEL$14")
      OUTLINE_LEAF(@"SET$2")
      UNNEST(@"SEL$19")
      OUTLINE_LEAF(@"SEL$0D753FAC")
      OUTLINE_LEAF(@"SEL$17")
      OUTLINE_LEAF(@"SEL$15")
      OUTLINE_LEAF(@"SEL$16")
      OUTLINE_LEAF(@"SEL$9")
      OUTLINE_LEAF(@"SEL$10")
      PUSH_PRED(@"SEL$10" "TST"@"SEL$10" 1)
      OUTLINE_LEAF(@"SET$5715CE2E")
      OUTLINE_LEAF(@"SEL$7B312CD2")
      OUTLINE_LEAF(@"SEL$267CE17A")
      OUTLINE_LEAF(@"SEL$8")
      OUTLINE_LEAF(@"SEL$7")
      OUTLINE_LEAF(@"SEL$6")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      ALL_ROWS
      OPT_PARAM('optimizer_index_caching' 50)
      OPT_PARAM('optimizer_index_cost_adj' 30)
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
...................

(Je vous ai épargné ce qui n'était pas utile dans l'output)

Ensuite j'ai copié cet outline pour le mettre sous forme de hint dans la requête et je l'ai exécutée en PROD. J'ai obtenu le même plan avec des stats d'exécution équivalents à celle de la recette.

L'idéal aurait été de faire une analyse approfondie pour comprendre pourquoi un mauvais plan était choisi en PROD mais le temps ne nous le permettait pas et mon client était satisfait du plan en recette d'autant plus que la requête n'est pas censé être modifiée et que les tables ont une volumétrie stable.

La solution la plus efficace dans ce cas était donc de forcer le bon plan en demandant au client d'ajouter l'ensemble des hints constituant l'outline du bon plan dans le code de la requête. L'inconvénient c'est que mon client n'avait pas la possibilité de modifier cette requête et il n'y avait pas dans l'historique d'exécution de la requête en PROD le bon plan ou un plan avec des statistiques d'exécution satisfaisantes.

Et c'est là que le script de Kerry Osborne entre en jeu:

----------------------------------------------------------------------------------------
--
-- File name:   move_sql_profile.sql
--
-- Purpose:     Moves a SQL Profile from one statement to another.
-
-- Author:      Kerry Osborne
--
-- Usage:       This scripts prompts for four values.
--
--              profile_name: the name of the profile to be attached to a new statement
--
--              sql_id: the sql_id of the statement to attach the profile to
--
--              category: the category to assign to the new profile 
--
--              force_macthing: a toggle to turn on or off the force_matching feature
--
-- Description: This script is based on a script originally written by Randolf Giest. 
--              It's purpose is to allow a statements text to be manipulated in whatever
--              manner necessary (typically with hints) to get the desired plan. Then 
--              once a SQL Profile has been created on the new statement, it's SQL Profile
--              can be moved (or attached) to the orignal statement with unmodified text.
--
-- Mods:        This script should now work wirh all flavors of 10g and 11g.
--              
--
--              See kerryosborne.oracle-guy.com for additional information.
----------------------------------------------------------------------------------------- 

accept profile_name -
       prompt 'Enter value for profile_name: ' -
       default 'X0X0X0X0'
accept sql_id -
       prompt 'Enter value for sql_id: ' -
       default 'X0X0X0X0'
accept category -
       prompt 'Enter value for category (DEFAULT): ' -
       default 'DEFAULT'
accept force_matching -
       prompt 'Enter value for force_matching (false): ' -
       default 'false'

----------------------------------------------------------------------------------------
--
-- File name:   profile_hints.sql
--
---------------------------------------------------------------------------------------
--
set sqlblanklines on


declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
version varchar2(3);
l_category varchar2(30);
l_force_matching varchar2(3);
b_force_matching boolean;
begin
 select regexp_replace(version,'\..*') into version from v$instance;

if version = '10' then

-- dbms_output.put_line('version: '||version);
   execute immediate -- to avoid 942 error 
   'select attr_val as outline_hints '||
   'from dba_sql_profiles p, sqlprof$attr h '||
   'where p.signature = h.signature '||
   'and name like (''&&profile_name'') '||
   'order by attr#'
   bulk collect 
   into ar_profile_hints;

elsif version = '11' then

-- dbms_output.put_line('version: '||version);
   execute immediate -- to avoid 942 error 
   'select hint as outline_hints '||
   'from (select p.name, p.signature, p.category, row_number() '||
   '      over (partition by sd.signature, sd.category order by sd.signature) row_num, '||
   '      extractValue(value(t), ''/hint'') hint '||
   'from sys.sqlobj$data sd, dba_sql_profiles p, '||
   '     table(xmlsequence(extract(xmltype(sd.comp_data), '||
   '                               ''/outline_data/hint''))) t '||
   'where sd.obj_type = 1 '||
   'and p.signature = sd.signature '||
   'and p.name like (''&&profile_name'')) '||
   'order by row_num'
   bulk collect 
   into ar_profile_hints;

end if;

select
sql_fulltext
into
cl_sql_text
from
v$sqlarea
where
sql_id = '&&sql_id';

dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text
, profile => ar_profile_hints
, category => '&&category'
, name => 'PROFILE_'||'&&sql_id'||'_moved'
-- use force_match => true
-- to use CURSOR_SHARING=SIMILAR
-- behaviour, i.e. match even with
-- differing literals
, force_match => &&force_matching
);
end;
/
undef profile_name
undef sql_id
undef category
undef force_matching

L'idée de ce script est d'attacher un plan d'une requête (qu'on a réussi à obtenir d'une manière ou d'une autre) à une requête s'exécutant avec un plan non satisfaisant et qu'on ne peut modifier.

L'exécution de ce script est en fait la dernière étape d'un plan en 3 étapes:

1) Exécuter la requête avec le bon plan (en ajoutant les hints de l'outline)

2) Création d'un SQL profile pour y coller le plan obtenu en (1)

3) Coller le SQL Profile créé en (2) à la requête exécutée par l'application

La 3ème étape correspond en fait à l'exécution du script de Kerry Osborne.

L'étape 1 je l'ai réalisée lorsque j'ai exécuté la requête avec l'outline.

L'étape 2 consiste à exécuter un autre script de kerry Osborne que j'avais expliqué dans un de mes tous premiers articles. A l'étape 1 j'ai obtenu un SQL_ID dccyz592gpzpq pour lequel je veux créer un SQL profile qui va me permettre de figer le plan obtenu:

SQL> @sp_create_sql_profile.sql
SQL> ----------------------------------------------------------------------------------------
SQL> --
SQL> -- File name:      create_sql_profile.sql
SQL> --
SQL> -- Purpose:        Create SQL Profile based on Outline hints in V$SQL.OTHER_XML.
SQL> --
SQL> -- Author: Kerry Osborne
SQL> --
SQL> -- Usage:       This scripts prompts for four values.
SQL> --
SQL> --              sql_id: the sql_id of the statement to attach the profile to (must be in the shared pool)
SQL> --
SQL> --              child_no: the child_no of the statement from v$sql
SQL> --
SQL> --              profile_name: the name of the profile to be generated
SQL> --
SQL> --              category: the name of the category for the profile
SQL> --
SQL> --              force_macthing: a toggle to turn on or off the force_matching feature
SQL> --
SQL> -- Description:
SQL> --
SQL> --              Based on a script by Randolf Giest.
SQL> --
SQL> -- Mods:        This is the 2nd version of this script which removes dependency on rg_sqlprof1.sql.
SQL> --
SQL> --              See kerryosborne.oracle-guy.com for additional information.
SQL> ---------------------------------------------------------------------------------------
SQL> --
SQL>
SQL> -- @rg_sqlprof1 '&&sql_id' &&child_no '&&category' '&force_matching'
SQL>
SQL> set feedback off
SQL> set sqlblanklines on
SQL>
SQL> accept sql_id -
>        prompt 'Enter value for sql_id: ' -
>        default 'X0X0X0X0'
Enter value for sql_id: dccyz592gpzpq
SQL> accept child_no -
>        prompt 'Enter value for child_no (0): ' -
>        default '0'
Enter value for child_no (0):
SQL> accept profile_name -
>        prompt 'Enter value for profile_name (PROF_sqlid_planhash): ' -
>        default 'X0X0X0X0'
Enter value for profile_name (PROF_sqlid_planhash):
SQL> accept category -
>        prompt 'Enter value for category (DEFAULT): ' -
>        default 'DEFAULT'
Enter value for category (DEFAULT):
SQL> accept force_matching -
>        prompt 'Enter value for force_matching (FALSE): ' -
>        default 'false'
Enter value for force_matching (FALSE): TRUE
SQL>
SQL> declare
  2  ar_profile_hints sys.sqlprof_attr;
  3  cl_sql_text clob;
  4  l_profile_name varchar2(30);
  5  begin
  6  select
  7  extractvalue(value(d), '/hint') as outline_hints
  8  bulk collect
  9  into
 10  ar_profile_hints
 11  from
 12  xmltable('/*/outline_data/hint'
 13  passing (
 14  select
 15  xmltype(other_xml) as xmlval
 16  from
 17  v$sql_plan
 18  where
 19  sql_id = '&&sql_id'
 20  and child_number = &&child_no
 21  and other_xml is not null
 22  )
 23  ) d;
 24
 25  select
 26  sql_fulltext,
 27  decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||plan_hash_value,'&&profile_name')
 28  into
 29  cl_sql_text, l_profile_name
 30  from
 31  v$sql
 32  where
 33  sql_id = '&&sql_id'
 34  and child_number = &&child_no;
 35
 36  dbms_sqltune.import_sql_profile(
 37  sql_text => cl_sql_text,
 38  profile => ar_profile_hints,
 39  category => '&&category',
 40  name => l_profile_name,
 41  force_match => &&force_matching
 42  -- replace => true
 43  );
 44
 45    dbms_output.put_line(' ');
 46    dbms_output.put_line('SQL Profile '||l_profile_name||' created.');
 47    dbms_output.put_line(' ');
 48
 49  exception
 50  when NO_DATA_FOUND then
 51    dbms_output.put_line(' ');
 52    dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Child: '||'&&child_no'||' not found in v$sql.');
 53    dbms_output.put_line(' ');
 54
 55  end;
 56  /
old  19: sql_id = '&&sql_id'
new  19: sql_id = 'dccyz592gpzpq'
old  20: and child_number = &&child_no
new  20: and child_number = 0
old  27: decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||plan_hash_value,'&&profile_name')
new  27: decode('X0X0X0X0','X0X0X0X0','PROF_dccyz592gpzpq'||'_'||plan_hash_value,'X0X0X0X0')
old  33: sql_id = '&&sql_id'
new  33: sql_id = 'dccyz592gpzpq'
old  34: and child_number = &&child_no;
new  34: and child_number = 0;
old  39: category => '&&category',
new  39: category => 'DEFAULT',
old  41: force_match => &&force_matching
new  41: force_match => TRUE
old  52:   dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Child: '||'&&child_no'||' not found in v$sql.');
new  52:   dbms_output.put_line('ERROR: sql_id: '||'dccyz592gpzpq'||' Child: '||'0'||' not found in v$sql.');

On vérifie qu'un SQL profile nommé PROF_dccyz592gpzpq_3994356561 a bien été créé:

SQL> @sp_list_sql_profiles.sql
SQL> col category for a15
SQL> col sql_text for a70 trunc
SQL> select name, category, status, sql_text, force_matching
  2  from dba_sql_profiles
  3  where sql_text like nvl('&sql_text','%')
  4  and name like nvl('&name',name)
  5  order by last_modified
  6  /
Enter value for sql_text:
old   3: where sql_text like nvl('&sql_text','%')
new   3: where sql_text like nvl('','%')
Enter value for name:
old   4: and name like nvl('&name',name)
new   4: and name like nvl('',name)

NAME                           CATEGORY        STATUS   SQL_TEXT                                                       FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROF_dccyz592gpzpq_3994356561  DEFAULT         ENABLED  SELECT                                                         YES

Et c'est ce SQL profile qu'on veut coller à la requête exécutée par l'application et pour ce faire on utilise le script que j'ai affiché plus haut:

SQL> @sp_move_sql_profile.sql
SQL> ----------------------------------------------------------------------------------------
SQL> --
SQL> -- File name:      move_sql_profile.sql
SQL> --
SQL> -- Purpose:        Moves a SQL Profile from one statement to another.
SQL> -
> -- Author:      Kerry Osborne
SQL> --
SQL> -- Usage:       This scripts prompts for four values.
SQL> --
SQL> --              profile_name: the name of the profile to be attached to a new statement
SQL> --
SQL> --              sql_id: the sql_id of the statement to attach the profile to
SQL> --
SQL> --              category: the category to assign to the new profile
SQL> --
SQL> --              force_macthing: a toggle to turn on or off the force_matching feature
SQL> --
SQL> -- Description: This script is based on a script originally written by Randolf Giest.
SQL> --              It's purpose is to allow a statements text to be manipulated in whatever
SQL> --              manner necessary (typically with hints) to get the desired plan. Then
SQL> --              once a SQL Profile has been created on the new statement, it's SQL Profile
SQL> --              can be moved (or attached) to the orignal statement with unmodified text.
SQL> --
SQL> -- Mods:        This script should now work wirh all flavors of 10g and 11g.
SQL> --
SQL> --
SQL> --              See kerryosborne.oracle-guy.com for additional information.
SQL> -----------------------------------------------------------------------------------------
SQL>
SQL> accept profile_name -
>        prompt 'Enter value for profile_name: ' -
>        default 'X0X0X0X0'
Enter value for profile_name: PROF_dccyz592gpzpq_3994356561
SQL> accept sql_id -
>        prompt 'Enter value for sql_id: ' -
>        default 'X0X0X0X0'
Enter value for sql_id: 0496r075a27c8
SQL> accept category -
>        prompt 'Enter value for category (DEFAULT): ' -
>        default 'DEFAULT'
Enter value for category (DEFAULT):
SQL> accept force_matching -
>        prompt 'Enter value for force_matching (false): ' -
>        default 'false'
Enter value for force_matching (false): true
SQL>
SQL>
SQL> ----------------------------------------------------------------------------------------
SQL> --
SQL> -- File name:      profile_hints.sql
SQL> --
SQL> ---------------------------------------------------------------------------------------
SQL> --
SQL> set sqlblanklines on
SQL>
SQL> declare
  2  ar_profile_hints sys.sqlprof_attr;
  3  cl_sql_text clob;
  4  version varchar2(3);
  5  l_category varchar2(30);
  6  l_force_matching varchar2(3);
  7  b_force_matching boolean;
  8  begin
  9   select regexp_replace(version,'\..*') into version from v$instance;
 10
 11  if version = '10' then
 12
 13  -- dbms_output.put_line('version: '||version);
 14     execute immediate -- to avoid 942 error
 15     'select attr_val as outline_hints '||
 16     'from dba_sql_profiles p, sqlprof$attr h '||
 17     'where p.signature = h.signature '||
 18     'and name like (''&&profile_name'') '||
 19     'order by attr#'
 20     bulk collect
 21     into ar_profile_hints;
 22
 23  elsif version = '11' then
 24
 25  -- dbms_output.put_line('version: '||version);
 26     execute immediate -- to avoid 942 error
 27     'select hint as outline_hints '||
 28     'from (select p.name, p.signature, p.category, row_number() '||
 29     '      over (partition by sd.signature, sd.category order by sd.signature) row_num, '||
 30     '      extractValue(value(t), ''/hint'') hint '||
 31     'from sys.sqlobj$data sd, dba_sql_profiles p, '||
 32     '     table(xmlsequence(extract(xmltype(sd.comp_data), '||
 33     '                               ''/outline_data/hint''))) t '||
 34     'where sd.obj_type = 1 '||
 35     'and p.signature = sd.signature '||
 36     'and p.name like (''&&profile_name'')) '||
 37     'order by row_num'
 38     bulk collect
 39     into ar_profile_hints;
 40
 41  end if;
 42
 43
 44  /*
 45  declare
 46  ar_profile_hints sys.sqlprof_attr;
 47  cl_sql_text clob;
 48  begin
 49  select attr_val as outline_hints
 50  bulk collect
 51  into
 52  ar_profile_hints
 53  from dba_sql_profiles p, sqlprof$attr h
 54  where p.signature = h.signature
 55  and name like ('&&profile_name')
 56  order by attr#;
 57  */
 58
 59  select
 60  sql_fulltext
 61  into
 62  cl_sql_text
 63  from
 64  v$sqlarea
 65  where
 66  sql_id = '&&sql_id';
 67
 68  dbms_sqltune.import_sql_profile(
 69  sql_text => cl_sql_text
 70  , profile => ar_profile_hints
 71  , category => '&&category'
 72  , name => 'PROFILE_'||'&&sql_id'||'_moved'
 73  -- use force_match => true
 74  -- to use CURSOR_SHARING=SIMILAR
 75  -- behaviour, i.e. match even with
 76  -- differing literals
 77  , force_match => &&force_matching
 78  );
 79  end;
 80  /
old  18:    'and name like (''&&profile_name'') '||
new  18:    'and name like (''PROF_dccyz592gpzpq_3994356561'') '||
old  36:    'and p.name like (''&&profile_name'')) '||
new  36:    'and p.name like (''PROF_dccyz592gpzpq_3994356561'')) '||
old  55: and name like ('&&profile_name')
new  55: and name like ('PROF_dccyz592gpzpq_3994356561')
old  66: sql_id = '&&sql_id';
new  66: sql_id = '0496r075a27c8';
old  71: , category => '&&category'
new  71: , category => 'DEFAULT'
old  72: , name => 'PROFILE_'||'&&sql_id'||'_moved'
new  72: , name => 'PROFILE_'||'0496r075a27c8'||'_moved'
old  77: , force_match => &&force_matching
new  77: , force_match => true

PL/SQL procedure successfully completed.

Ce script prend notamment en paramètre le nom du SQL profile qu'on veut attacher et le SQL_ID de la requête pourlaquelle on veut forcer le plan. Dans mon cas la requête en question avait pour SQL_ID 0496r075a27c8.

Si j'affiche les SQL profiles de ma base je vois que j'ai maintenant un 2ème SQL Profile nommé PROFILE_0496r075a27c8_moved et qui est attaché au SQL_ID 0496r075a27c8:

NAME                           CATEGORY        STATUS   SQL_TEXT                                                       FOR
------------------------------ --------------- -------- ---------------------------------------------------------------------- ---
PROF_dccyz592gpzpq_3994356561  DEFAULT         ENABLED  SELECT                                                         YES
PROFILE_0496r075a27c8_moved    DEFAULT         ENABLED  SELECT   DECODE (bill.alt_bill_id, 0, ' ', alt_bill_id) alt_bill_id,   YES

Maintenant lorsque mon client lance son application c'est le bon plan qui est exécuté. D'ailleurs lorsque j'affiche le plan exécuté désormais pour cette requête je vois la note suivante à la fin plan qui m'indique que c'est bien grâce au SQL profile que ce plan a été généré:

Note
-----
   - SQL profile PROFILE_0496r075a27c8_moved used for this statement

Dans le même thème, voir aussi les articles suivants:

Forcer un plan d'exécution via un SQL profile

Ajouter un hint sans toucher à la requête

Ajouter un hint sans toucher à la requête (grâce au SQL patch)

Voir également l' article de mon ami Mohamed Houri expliquant comment utiliser SPM pour obtenir à peu près le même résultat que moi avec les SQL profiles:

How to attach a hinted SPM baseline to a non hinted sql query?

L'importance du LOCAL_LISTENER

J'ai été alerté aujourd'hui par des utilisateurs qui obtenaient l'erreur ci-dessous lorsqu'ils souhaitaient accéder à une base de pré-production:

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

HPUX-ia64 Error: 2: No such file or directory

ID de processus : 0

ID de session : 0,  Numéro de série : 0

En général je vois cette erreur lorsqu'on tente d'accéder à une base qui est arrêtée. Pourtant pour cette base j'arrivais à me connecter localement sans problème et la base est bien OPEN. Néanmoins, lorsque je me connectais à distance j'obtenais le même message que les utilisateurs:

ihgbdd@vp2186:/projets/ihg/home/ihgbdd $  sqlplus user_test/USER_TEST_#1@PMIP00

SQL*Plus: Release 11.2.0.3.0 Production on Mar. Mai 27 14:42:41 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

HPUX-ia64 Error: 2: No such file or directory

ID de processus : 0

ID de session : 0,  Numéro de série : 0

C'est donc que le problème ne se situait pas au niveau de l'instance elle-même mais plutôt au niveau de la configuration OracleNet.
Une connexion Easy Connect me donnait le même message d'erreur:

ihgbdd@vp2186:/projets/ihg/home/ihgbdd $  sqlplus user_test/USER_TEST_#1@psu459:1550/PMIP00

SQL*Plus: Release 11.2.0.3.0 Production on Mar. Mai 27 14:43:37 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

HPUX-ia64 Error: 2: No such file or directory

ID de processus : 0

ID de session : 0,  Numéro de série : 0

Le TNSPING par contre fonctionnait bien.

ihgbdd@vp2186:/projets/ihg/home/ihgbdd/RUB/RUB1.100.11 $ tnsping pmip00

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 27-MAI  -2014 14:38:52

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Fichiers de paramètres utilisés :

/soft/oracle/product/client/11.2.0.3/network/admin/sqlnet.ora

Adaptateur TNSNAMES utilisé pour la résolution de l'alias

Tentative de contact de (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = psu459)(PORT = 1550))) (CONNECT_DATA = (SERVICE_NAME = PMIP00)))

OK (20 msec)

J'en concluais donc que le listener était bien démarré et qu'il écoutait sur le port 1550 qui (vous l'aurez sans doute noter) ne correspond pas au numéro de port par défaut.

A ce moment là me sont revenus mes souvenirs des cours d'admin Oracle DBA1: Pour que l'enregistrement dynamique d'une instance auprès du listener se fasse il faut 

- soit utiliser le nom et le port du listener par défaut 

- soit (si le nom ou le port ne sont pas ceux par défaut) définir une entrée TNS comme valeur du paramètre LOCAL_LISTENER.
Je suis donc allé vérifier ce que me donnait la valeur de ce paramètre pour ma base en question:

SQL> sho parameter listener

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

listener_networks                    string

local_listener                       string

remote_listener                      string

Comme je me doutais, le paramètre n'était pas setté.

Voilà donc la cause de mon problème. Sans cette indication l'instance ne sait pas auprès de quel listener il doit s'enregistrer ni comment le contacter. Ce paramètre est censé lui indiquer le nom du listener ainsi que le numéro du port écouté par ce listener.

Comme j'ai un fichier TNSNAMES.ora sur mon serveur de données j'ai pu lui indiquer directement le nom de l'alias TNS pour la base en question :

SQL>  alter system set local_listener='PMIP00';

System altered.

Si je n'avais pas de fichier TNSNAMES.ora il aurait fallu que j'indique comme valeur du paramètre la partie ADDRESS de l'entrée TNS: (ADDRESS = (PROTOCOL = TCP)(HOST = psu459)(PORT = 1550)).

Une fois le paramètre setté l'accès à la base peut s'effectuer sans problème

hgbdd@vp2186:/projets/ihg/home/ihgbdd $ sqlplus user_test/USER_TEST_#1@PMIP00

SQL*Plus: Release 11.2.0.3.0 Production on Mar. Mai 27 15:19:25 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connecté à :

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

CONCLUSION:
Si vous n'utilisez pas les valeurs par défaut pour un LISTENER il faut bien penser à setter le paramètre LOCAL_LISTENER sinon l'enregistrement automatique de votre instance auprès du LISTENER ne pourra se faire et vos connexions distantes à la base ne fonctionneront pas.

Quand le CBO choisit l'index non-unique à la place de l'index unique

J'ai constaté cette semaine sur une des bases sur lesquelles je travaille que la requête suivante s'est mis à bien tourner du jour au lendemain:

DELETE FROM SCDAT.TRANSRPDATES WHERE TRANSRPDATES.TRANSIK = :v1  AND TRANSRPDATES.ACCIK = :v2;

On peut constater cela en regardant les stats au niveau de l'AWR:

SNAP_ID   NODE BEGIN_INTERVAL_TIME            SQL_ID        PLAN_HASH_VALUE        EXECS    AVG_ETIME        AVG_LIO    AVG_PIO     AVG_ROWS

---------- ------ ------------------------------ ------------- --------------- ------------ ------------ -------------- ---------- ------------

        55      1 20/02/14 17:00:25,756          9ytthuuffcgy5      2252980867        1,263        1.271       15,226.1 ,406967538            1

        56      1 20/02/14 18:00:41,362          9ytthuuffcgy5                        2,915        1.238       15,213.3 ,208919383            1

        57      1 20/02/14 19:00:54,602          9ytthuuffcgy5                        2,822        1.258       15,195.6 ,242026931            1

        58      1 20/02/14 20:00:07,686          9ytthuuffcgy5                        2,895        1.248       15,178.0  ,12193437            1

        59      1 20/02/14 21:00:21,148          9ytthuuffcgy5                        2,918        1.242       15,160.2 ,257710761            1

        60      1 20/02/14 22:00:36,358          9ytthuuffcgy5                        2,888        1.248       15,142.3 ,233379501            1

        61      1 20/02/14 23:00:51,105          9ytthuuffcgy5                        2,881        1.232       15,124.6 ,144741409            1

        62      1 21/02/14 00:00:04,016          9ytthuuffcgy5                        2,932        1.231       15,106.6 ,224079127            1

        63      1 21/02/14 01:00:17,342          9ytthuuffcgy5                        2,949        1.224       15,084.1 ,143438454            1

        64      1 21/02/14 02:00:30,694          9ytthuuffcgy5                        2,952        1.223       15,070.2 ,103319783            1

        65      1 21/02/14 03:00:43,780          9ytthuuffcgy5                        2,959        1.220       15,051.9 ,100033795            1

        66      1 21/02/14 04:00:56,915          9ytthuuffcgy5                        2,924        1.214       15,033.7 ,157660739            1

        67      1 21/02/14 05:00:09,943          9ytthuuffcgy5                        2,971        1.216       15,015.5 ,100302928            1

        68      1 21/02/14 06:00:23,053          9ytthuuffcgy5                        2,989        1.208       14,997.1 ,096353295            1

        69      1 21/02/14 07:00:36,366          9ytthuuffcgy5                        2,991        1.207       14,978.6 ,235372785            1

        70      1 21/02/14 08:00:49,468          9ytthuuffcgy5                        2,943        1.207       14,960.2 ,285762827            1

        71      1 21/02/14 09:00:02,396          9ytthuuffcgy5                        3,001        1.203       14,941.7 ,154948351            1

        72      1 21/02/14 10:00:15,559          9ytthuuffcgy5                        2,803        1.201       14,920.2 ,172315376            1

       148      1 24/02/14 17:00:54,112          9ytthuuffcgy5      2250495236       63,142         .001           70.2 ,077428653            1

On voit qu'on a eu un switch de plan le 24/02/2014. Avant cette date le plan utilisé générait environ 15000 logical reads par exécution alors que le 24/02/14 le nouveau plan ne générait plus que 70 LR par exécution.

Voyons à quoi ressemble ces 2 plans:

Plan hash value: 2250495236

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT             |                |       |       |     3 (100)|          |
|   1 |  DELETE                      | TRANSRPDATES   |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TRANSRPDATES   |     1 |    40 |     3   (0)| 00:00:01 |
|   3 |    INDEX UNIQUE SCAN         | P_TRANSRPDATES |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Plan hash value: 2252980867

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT             |                      |       |       |     2 (100)|          |
|   1 |  DELETE                      | TRANSRPDATES         |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TRANSRPDATES         |     1 |    92 |     2   (0)| 00:00:01 |
|   3 |    INDEX RANGE SCAN          | R_TRANSRPDATES_ACCIK |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

On remarque que le bon plan consiste en une opération INDEX UNIQUE SCAN alors que le mauvais correspond à une opération INDEX RANGE SCAN. En gros, la requête s'exécute bien lorsque l'index unique P_TRANSRPDATES est utilisé, et s'exécute nettement moins bien lorsque l'index non-unique R_TRANSRPDATES_ACCIK est utilisé.

L'index unique est un index composite sur les 2 colonnes impliquées dans la requête alors que l'index non-unique est un index sur la colonne ACCIK uniquement.

La question légitime qu'on se pose c'est "pourquoi le CBO a décidé d'utiliser l'index non-unique avant la date du 24/02 alors que l'index unique existait bien?".

Pour répondre à cette question j'ai fait un comparatif des stats entre la date du jour et la date du 21/02 en utilisant la procédure DIFF_TABLE_STATS_IN_HISTORY du package DBMS_STATS:

SELECT *
FROM table(dbms_stats.diff_table_stats_in_history(
             ownname      => 'SCDAT',
             tabname      => 'TRANSRPDATES',
             time1        => systimestamp - to_dsinterval('5 00:00:00'),
             time2        => NULL,
             pctthreshold => 10));
  
###############################################################################


STATISTICS DIFFERENCE REPORT FOR:
.................................


TABLE         : TRANSRPDATES
OWNER         : SCDAT
SOURCE A      : Statistics as of 21/02/14 09:19:30,901433 +01:00
SOURCE B      : Current Statistics in dictionary
PCTTHRESHOLD  : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................


OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE  
...............................................................................


TRANSRPDATES                T   A   0          33172      0          0         
                                B   63293      33172      40         63293     
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


COLUMN STATISTICS DIFFERENCE:
.............................


COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................


ACCIK           A   0       0          NO   0       0      0      
                B   1       ,000008023 YES  0       4    C2020 C2020 5415   
RPDEFIK         A   0       0          NO   0       0      0      
                B   1       ,000008023 YES  0       2    80    80    5415   
TRANSIK         A   0       0          NO   0       0      0      
                B   63048   ,000016047 YES  0       6    C4043 C4054 5415   
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................


OBJECTNAME      TYP SRC ROWS    LEAFBLK DISTKEY LF/KY DB/KY CLF     LVL SAMPSIZ
...............................................................................




                              INDEX: P_TRANSRPDATES
                              .....................


P_TRANSRPDATES  I   A   0       0       0       0     0     0       2   0      
                    B   63293   763     63293   1     1     31603   2   63293  


                           INDEX: R_TRANSRPDATES_ACCIK
                           ...........................


R_TRANSRPDATES_ I   A   0       0       0       0     0     0       2   0      
                    B   63293   223     1       223   580   580     2   63293  


                          INDEX: R_TRANSRPDATES_RPDEFIK
                          .............................


R_TRANSRPDATES_ I   A   0       0       0       0     0     0       2   0      
                    B   63293   214     1       214   580   580     2   63293  
###############################################################################

On constate qu'en réalité à la date du 21 les stats étaient à zéro c'est à dire que les stats avaient été calculées alors que la table était vide.

Il ne faut pas confondre ici les stats à zéro et les stats à NULL. Les stats à NULL signifient une absence de stats et donc dans ce cas le Dynamic Sampling peut être activé selon la valeur du paramètre OPTIMIZER_DYNAMIC_SAMPLING.

Dans notre cas les stats étaient à zéro avant le 24 ce qui veut dire que le CBO estimait qu'il n'y avait pas de données dans la table alors qu'en réalité bien sûr il y'en avait.

L'idée ensuite est de pouvoir reproduire le mauvais plan en activant une trace 10053 lorsque les stats sont à zéro.

J'ai pour cela restauré les stats à la date du 21/02:

exec DBMS_STATS.RESTORE_TABLE_STATS (ownname=>'SCDAT', tabname=>'TRANSRPDATES', as_of_timestamp=>TO_DATE('21/02/2014 09:19:30', 'DD/MM/YYYY HH24:MI:SS'));

@10053
explain plan for
DELETE FROM SCDAT.TRANSRPDATES WHERE TRANSRPDATES.TRANSIK = :v1  AND TRANSRPDATES.ACCIK = :v2;
@dis_10053

Plan hash value: 2252980867

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT             |                      |     1 |    92 |     2   (0)| 00:00:01 |
|   1 |  DELETE                      | TRANSRPDATES         |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TRANSRPDATES         |     1 |    92 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | R_TRANSRPDATES_ACCIK |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("TRANSRPDATES"."TRANSIK"=TO_NUMBER(:V1))
   3 - access("TRANSRPDATES"."ACCIK"=TO_NUMBER(:V2))

BINGO!! L'optimiseur a choisi l'index non-unique.

Jettons un oeil à la trace du CBO:

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TRANSRPDATES  Alias: TRANSRPDATES
    #Rows: 0  #Blks:  33172  AvgRowLen:  0.00  ChainCnt:  0.00
Index Stats::
  Index: P_TRANSRPDATES  Col#: 1 2
    LVLS: 2  #LB: 0  #DK: 0  LB/K: 0.00  DB/K: 0.00  CLUF: 0.00
  Index: R_TRANSRPDATES_ACCIK  Col#: 2
    LVLS: 2  #LB: 0  #DK: 0  LB/K: 0.00  DB/K: 0.00  CLUF: 0.00
  Index: R_TRANSRPDATES_RPDEFIK  Col#: 6
    LVLS: 2  #LB: 0  #DK: 0  LB/K: 0.00  DB/K: 0.00  CLUF: 0.00
***************************************
1-ROW TABLES:  TRANSRPDATES[TRANSRPDATES]#0
Access path analysis for TRANSRPDATES
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TRANSRPDATES[TRANSRPDATES] 
  Column (#1): TRANSIK(
    AvgLen: 22 NDV: 0 Nulls: 0 Density: 0.000000 Min: 0 Max: 0
  Column (#2): ACCIK(
    AvgLen: 22 NDV: 0 Nulls: 0 Density: 0.000000 Min: 0 Max: 0
  ColGroup (#1, Index) P_TRANSRPDATES
    Col#: 1 2    CorStregth: 0.00
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 1.0000
  Table: TRANSRPDATES  Alias: TRANSRPDATES
    Card: Original: 0.000000  Rounded: 1  Computed: 0.00  Non Adjusted: 0.00
  Access Path: TableScan
    Cost:  9540.36  Resp: 9540.36  Degree: 0
      Cost_io: 9479.00  Cost_cpu: 236232408
      Resp_io: 9479.00  Resp_cpu: 236232408
  Access Path: index (UniqueScan)
    Index: P_TRANSRPDATES
    resc_io: 2.00  resc_cpu: 15583
    ix_sel: 0.000000  ix_sel_with_filters: 0.000000 
    Cost: 2.00  Resp: 2.00  Degree: 1
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 1.0000
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 1.0000
  Access Path: index (AllEqUnique)
    Index: P_TRANSRPDATES
    resc_io: 2.00  resc_cpu: 15583
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
    Cost: 2.00  Resp: 2.00  Degree: 1
  Access Path: index (AllEqRange)
    Index: R_TRANSRPDATES_ACCIK
    resc_io: 2.00  resc_cpu: 14443
    ix_sel: 0.010000  ix_sel_with_filters: 0.010000 
    Cost: 2.00  Resp: 2.00  Degree: 1
  Best:: AccessPath: IndexRange
  Index: R_TRANSRPDATES_ACCIK
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 0.00  Bytes: 0

Le CBO calcule le COST pour chaque index et on voit que le COST est à 2 pour chacun d'eux.

Logiquement on s'imagine qu'il prendrait l'index unique mais en fait pas du tout...il prend l'index non unique.

Mon sentiment au départ était de dire que le CBO choisissait l'index qui était potentiellement le plus petit c'est à dire celui qui contenait le moins de clés d'index. 

En effet dans mon cas l'index non unique est définie sur seulement une seule colonne alors que l'index unique est définie sur 2 colonnes.

Pour en savoir plus j'ai décidé d'ouvrir une discussion sur le forum d'OTN et j'ai envoyé un mail au spécialiste du CBO Jonathan LEWIS pour l'inviter à y répondre.

Bien sûr Jonathan a répondu et voici sa réponse:

For a tie in the cost of the index: at one time the choice was alphabetical by name but a fix came in some time in 10g to select the index with the larger number of distinct keys.

At present is seems to be:

If all indexes are unique and the costs are the same then tie-break on number of distinct keys, if those match then alphabetical.
If all indexes are non-unique and the costs are the same then tie-break on number of distinct keys, if those match then alphabetical.
If there is a mixture of unique and non-unique then NON-unique are preferred

Donc, selon Jonathan, lorsqu'on a un COST identique et qu'on est en présence à la fois d'un index UNIQUE et d'un index non-unique, le CBO choisirait automatiquement l'index non-unique.
Je trouve ça complètement illogique surtout lorsque l'on sait que les index range scan par rapport aux index unique scan entrainent un surcoût lié notamment au nombre de latchs plus importants générés et au fait qu'en cas d'index range scan oracle doit checker l'entrée d'index suivant (juste au cas où) car cette opération par définition peut retourner plusieurs lignes alors qu'avec un index unqiue scan Oracle est sûr de n'avoir au plus qu'une seule entrée d'index correspondante.

Je vous invite à lire l'article de Richard FOOTE sur ce sujet.

Pour en revenir à mon cas et pour confirmer mon hypothèse de départ sur le nombre de colonnes, j'ai (sur les conseils de mon ami Guy-Georges DROGBA) généré de nouveau le plan après avoir cette fois désactivé le CPU costing:

exec DBMS_STATS.RESTORE_TABLE_STATS (ownname=>'SCDAT', tabname=>'TRANSRPDATES', as_of_timestamp=>TO_DATE('21/02/2014 09:19:30', 'DD/MM/YYYY HH24:MI:SS'));

alter session set "_optimizer_cost_model"=io;

@10053

explain plan for DELETE FROM SCDAT.TRANSRPDATES WHERE TRANSRPDATES.TRANSIK = :v1  AND TRANSRPDATES.ACCIK = :v2;

@dis_10053

@plan

Plan hash value: 2250495236

-------------------------------------------------------------------------------

| Id  | Operation                    | Name           | Rows  | Bytes | Cost  |

-------------------------------------------------------------------------------

|   0 | DELETE STATEMENT             |                |     1 |    92 |     2 |

|   1 |  DELETE                      | TRANSRPDATES   |       |       |       |

|   2 |   TABLE ACCESS BY INDEX ROWID| TRANSRPDATES   |     1 |    92 |     2 |

|*  3 |    INDEX UNIQUE SCAN         | P_TRANSRPDATES |     1 |       |     2 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------


   3 - access("TRANSRPDATES"."TRANSIK"=TO_NUMBER(:V1) AND

              "TRANSRPDATES"."ACCIK"=TO_NUMBER(:V2))



alter session set "_optimizer_cost_model"=cpu;

explain plan for DELETE FROM SCDAT.TRANSRPDATES WHERE TRANSRPDATES.TRANSIK = :v1  AND TRANSRPDATES.ACCIK = :v2;

@plan

Plan hash value: 2252980867

-----------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------------

|   0 | DELETE STATEMENT             |                      |     1 |    92 |     2   (0)| 00:00:01 |

|   1 |  DELETE                      | TRANSRPDATES         |       |       |            |          |

|*  2 |   TABLE ACCESS BY INDEX ROWID| TRANSRPDATES         |     1 |    92 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | R_TRANSRPDATES_ACCIK |     1 |       |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("TRANSRPDATES"."TRANSIK"=TO_NUMBER(:V1))

   3 - access("TRANSRPDATES"."ACCIK"=TO_NUMBER(:V2))


exec DBMS_STATS.RESTORE_TABLE_STATS (ownname=>'SCDAT', tabname=>'TRANSRPDATES', as_of_timestamp=>systimestamp-1, force=> TRUE);

Ah! cette fois lorsque le CPU costing est désactivé c'est bien l'index unique qui est pris en compte. Jonathan Lewis dans la discussion que j'ai ouverte sur OTN disait qu'il était possible que cette fois le choix ait été effectué en prenant en compte l'ordre alphabétique.
Vu qu'alphabétiquement P_TRANSRPDATES soit avant R_TRANSRPDATES_ACCIK on peut effectivement penser cela.

J'ai donc refait le test en renommant l'index non unique en A_TRANSRPDATES_ACCIK pour qu'il soit alphabétiquement avant l'index unique:

exec DBMS_STATS.RESTORE_TABLE_STATS (ownname=>'SCDAT', tabname=>'TRANSRPDATES', as_of_timestamp=>TO_DATE('21/02/2014 09:19:30', 'DD/MM/YYYY HH24:MI:SS'));
alter session set "_optimizer_cost_model"=io;
alter index SCDAT.R_TRANSRPDATES_ACCIK rename to A_TRANSRPDATES_ACCIK;
@10053
explain plan for DELETE FROM SCDAT.TRANSRPDATES WHERE TRANSRPDATES.TRANSIK = :v1  AND TRANSRPDATES.ACCIK = :v2;
@dis_10053
@plan


Plan hash value: 2250495236


-------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------
|   0 | DELETE STATEMENT             |                |     1 |    92 |     2 |
|   1 |  DELETE                      | TRANSRPDATES   |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| TRANSRPDATES   |     1 |    92 |     2 |
|*  3 |    INDEX UNIQUE SCAN         | P_TRANSRPDATES |     1 |       |     2 |
-------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   3 - access("TRANSRPDATES"."TRANSIK"=TO_NUMBER(:V1) AND
              "TRANSRPDATES"."ACCIK"=TO_NUMBER(:V2))


Note
-----
   - cpu costing is off (consider enabling it)


alter session set "_optimizer_cost_model"=cpu;
explain plan for DELETE FROM SCDAT.TRANSRPDATES WHERE TRANSRPDATES.TRANSIK = :v1  AND TRANSRPDATES.ACCIK = :v2;
@plan


Plan hash value: 1992853963


-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT             |                      |     1 |    92 |     2   (0)| 00:00:01 |
|   1 |  DELETE                      | TRANSRPDATES         |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TRANSRPDATES         |     1 |    92 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | A_TRANSRPDATES_ACCIK |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - filter("TRANSRPDATES"."TRANSIK"=TO_NUMBER(:V1))
   3 - access("TRANSRPDATES"."ACCIK"=TO_NUMBER(:V2))


   
alter index SCDAT.A_TRANSRPDATES_ACCIK rename to R_TRANSRPDATES_ACCIK;
exec DBMS_STATS.RESTORE_TABLE_STATS (ownname=>'SCDAT', tabname=>'TRANSRPDATES', as_of_timestamp=>systimestamp-1, force=> TRUE);

C'est toujours l'index unique qui est pris en compte. Donc on peut dire que dans mon cas lorsque le CPU costing est désactivé c'est l'index unique qui est pris en compte alors que si le CPU costing est activé c'est l'index non unique qui est pris en compte.

Et la raison c'est surement qu'il est moins couteux d'un point de vue CPU de parcourir un index sur une seule colonne (mon index non-unique) qu'un index sur 2 colonnes (mon index unique).

J'ai écrit cet article pour montrer que l'optimiseur d'Oracle a des secrets au niveau de son algorithme que même le grand Jonathan LEWIS n'a pas encore totalement percé car le comportement du CBO varie énormément selon les paramètres, les modèles de données et les requêtes impliquées.
Néanmoins, la vraie conclusion de cette petite expérience c'est qu'il faut absolument se méfier des stats calculés sur des tables vides pour éviter des mésaventures qui peuvent s'avérer extrêmement coûteuses dans un environnement de production.

Les statistiques étendues (2)

Il y'a un peu plus de 2 ans déjà j'avais rédigé un article décrivant le principe des statistiques étendues en 11g.
J'avais tenté d'expliquer comment les stats étendues pouvaient aider le CBO à estimer de meilleures cardinalités lorsqu'on avait des colonnes corrélées ou des expressions dans nos prédicats.

Ces derniers jours j'ai justement eu affaire à 2 problèmes de performances (sur des bases 11g) liés à l'absence de stats sur des colonnes corrélées et des fonctions appliquées à certaines colonnes.

Je me suis donc dit que ces 2 cas réels pouvaient constituer un second article permettant d'illustrer l'article que j'avais écrit.

Cas 1: Statistiques étendues sur une expression

Le premier problème concernait la requête suivante:

SELECT
   to_char(SYSDATE, 'YYYYMMDD') AS DATE_EXTRACTION,
   RBQ.RBQ_NUM_CONTR AS CPP_NUM_CONTR,
   RBQ.RBQ_NUM_CONTR,
   CB.CBQ_CD_ETABLISSEMENT,
   CB.CBQ_CD_GUICHET,
   CB.CBQ_CLE_COMPTE,
   RBQ.RBQ_CD_NAT_COORD_BQE,
   Substr(CB.CBQ_NUM_COMPTE,1,7) AS CBQ_NUM_COMPTE1,
   Substr(CB.CBQ_NUM_COMPTE,8,3) AS CBQ_NUM_COMPTE2,
   Substr(CB.CBQ_NUM_COMPTE,11,1) AS CBQ_NUM_COMPTE3 FROM
   TDO_D_ASSIN_COORD_BQE      CB,
   TDO_D_ASSIN_ROLE_COORD_BQE RBQ,
   TDO_D_ASSIN_CONTR_ASSU     CON,
   TDO_R_PDV_DISTRIB          PDVD
WHERE
   RBQ.RBQ_NUM_CONTR in  (select NUM_CONTRAT from    GIC_DEM_LISTE_CONTRATS  )
   AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '398'
   AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '399'
   AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '400'
   AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '412'
   --
   AND CB.CBQ_ID_PERSONNE = RBQ.RBQ_ID_PERSONNE
   AND CB.CBQ_ID_TEC_COORD_BQE = RBQ.RBQ_ID_TEC_COORD_BQE
   --
   AND CON_NUM_CONTR(+) = RBQ.RBQ_NUM_CONTR
   AND (      CON.CON_CD_PVE_SOUS IS NULL
            OR (       PDVD_CD_POINT_VENTE = CON.CON_CD_PVE_SOUS
                  AND PDVD_CD_DISTRIB = '400000'
                  )
            );

Cette requête existait depuis longtemps mais, suite à l'implémentation du partitioning sur certaines tables, elle s'était mise à tourner pendant des dizaines d'heures.
Même si la clause de partitioning n'était effectivement pas indiquée dans la clause WHERE de la requête (et ça c'est pas bien!!!) le client voulait quand même que cette requête puisse s'exécuter correctement en attendant que la correction soit apportée.
Dans la requête on peut noter les expressions suivantes:

substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '398'    AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '399'    AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '400'    AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '412'

En fait le critère de partitionnement est un champ qui correspond justement aux 3 premiers caractères du champ RBQ_NUM_CONTR manipulé dans les clauses ci-dessus.
Jetons un œil au plan d’exécution de la requête(la requête n'ayant pas pu aboutir j'ai uniquement le plan sans les stats d’exécutions):

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                              |    89 | 12460 | 31322   (8)| 00:01:50 |       |       |
|   1 |  NESTED LOOPS                           |                              |    89 | 12460 | 31322   (8)| 00:01:50 |       |       |
|   2 |   NESTED LOOPS                          |                              |    89 | 12460 | 31322   (8)| 00:01:50 |       |       |
|*  3 |    HASH JOIN                            |                              |    89 |  7565 | 31056   (8)| 00:01:49 |       |       |
|   4 |     NESTED LOOPS                        |                              |    89 |  6675 | 31033   (8)| 00:01:49 |       |       |
|   5 |      NESTED LOOPS OUTER                 |                              |   115 |  7015 | 26689   (8)| 00:01:34 |       |       |
|   6 |       PARTITION LIST ALL                |                              |   115 |  5060 | 26459   (8)| 00:01:33 |     1 |   227 |
|*  7 |        TABLE ACCESS FULL                | TDO_D_ASSIN_ROLE_COORD_BQE   |   115 |  5060 | 26459   (8)| 00:01:33 |     1 |   227 |
|   8 |       TABLE ACCESS BY GLOBAL INDEX ROWID| TDO_D_ASSIN_CONTR_ASSU       |     1 |    17 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  9 |        INDEX UNIQUE SCAN                | PK_ASSIN_CON_NUM_CONTR       |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 10 |      INDEX FAST FULL SCAN               | UK_TDO_R_PDV_DISTRIB         |     1 |    14 |    38   (6)| 00:00:01 |       |       |
|  11 |     INDEX FAST FULL SCAN                | PK_GIC_DEM_LISTE_CONTRATS    | 20000 |   195K|    22   (5)| 00:00:01 |       |       |
|* 12 |    INDEX RANGE SCAN                     | PK_ASSIN_CBQ_TEC_COORD_PERSO |     1 |       |     2   (0)| 00:00:01 |       |       |
|  13 |   TABLE ACCESS BY INDEX ROWID           | TDO_D_ASSIN_COORD_BQE        |     1 |    55 |     3   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   3 - access("RBQ"."RBQ_NUM_CONTR"="NUM_CONTRAT")
   7 - filter(SUBSTR("RBQ"."RBQ_NUM_CONTR",1,3)<>'398' AND SUBSTR("RBQ"."RBQ_NUM_CONTR",1,3)<>'399' AND
              SUBSTR("RBQ"."RBQ_NUM_CONTR",1,3)<>'400' AND SUBSTR("RBQ"."RBQ_NUM_CONTR",1,3)<>'412')
   9 - access("CON_NUM_CONTR"(+)="RBQ"."RBQ_NUM_CONTR")
  10 - filter("CON"."CON_CD_PVE_SOUS" IS NULL OR "PDVD_CD_POINT_VENTE"="CON"."CON_CD_PVE_SOUS" AND "PDVD_CD_DISTRIB"='400000')
  12 - access("CB"."CBQ_ID_PERSONNE"="RBQ"."RBQ_ID_PERSONNE" AND "CB"."CBQ_ID_TEC_COORD_BQE"="RBQ"."RBQ_ID_TEC_COORD_BQE")

On voit que le CBO a choisi d'attaquer en premier lieu la table TDO_D_ASSIN_ROLE_COORD_BQE en estimant que celle-ci retournerait (après avoir appliqué les critères avec la fonction SUBSTR) seulement 115 lignes .
Si on effectue un comptage sur cette table on se rend compte que le nombre de lignes retournées est en réalité de 18 millions:

select count(*) from TDO_D_ASSIN_ROLE_COORD_BQE RBQ
where substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '398'
AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '399'
AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '400'
AND substr(RBQ.RBQ_NUM_CONTR, 1, 3) <> '412';
   
  COUNT(*)
----------
  18449114
  
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                        |      1 |        |      1 |00:00:48.70 |   31949 |  31825 |
|   1 |  SORT AGGREGATE       |                        |      1 |      1 |      1 |00:00:48.70 |   31949 |  31825 |
|*  2 |   INDEX FAST FULL SCAN| CK_ASSIN_RBQ_NUM_CONTR |      1 |    115 |     18M|00:00:44.48 |   31949 |  31825 |
-------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - filter((SUBSTR("RBQ"."RBQ_NUM_CONTR",1,3)<>'398' AND SUBSTR("RBQ"."RBQ_NUM_CONTR",1,3)<>'399' AND
              SUBSTR("RBQ"."RBQ_NUM_CONTR",1,3)<>'400' AND SUBSTR("RBQ"."RBQ_NUM_CONTR",1,3)<>'412'))

Puisque le CBO est incapable d'estimer une sélectivité correcte lorsqu'une fonction est appliquée à une colonne, d'où diable sort-il ces 115 lignes?
D'après le chapitre 5 du livre de Jonathan Lewis, l'optimiseur appliquerait une sélectivité de 5% pour chaque prédicat (avec expression) de type NOT EQUAL.
Vu qu'on a 4 prédicats on se retrouve au final avec une sélectivité de 0.05*0.05*0.05*0.05 = 0.00000625
La colonne NUM_ROWS de DBA_TABLES pour la table TDO_D_ASSIN_ROLE_COORD_BQE renvoyant 18 449 114 lignes, si on y applique la sélectivité précédente on obtient bien 115 lignes:
18449114*0.00000625 = 115.30

Cette mauvaise estimation conduit le CBO à choisir cette table comme table directrice du NESTED LOOP pour la joindre avec la table TDO_D_ASSIN_CONTR_ASSU.
Les estimations étant totalement biaisées c'est tout le reste du plan qui est faussé.
La solution en 11g consiste à calculer des statistiques étendues sur l'expression substr(RBQ.RBQ_NUM_CONTR, 1, 3):

BEGIN
 DBMS_STATS.gather_table_stats
 (ownname => 'ADWH00',
 tabname => 'TDO_D_ASSIN_ROLE_COORD_BQE',
 method_opt => 'FOR COLUMNS (substr(RBQ_NUM_CONTR, 1, 3)) size 1'
 );
END;
/

J'ai mis le paramètre d'instance ENABLE_DDL_LOGGING à TRUE et voici ce qu'on voit dans le fichier ALERT lorsqu'on calcule des stats étendues sur l'expression substr(RBQ_NUM_CONTR, 1, 3):

alter table "ADWH00"."TDO_D_ASSIN_ROLE_COORD_BQE" add (SYS_STUXQ$Y7DH65G7U2BC3$3Y_3NF as (substr(RBQ_NUM_CONTR, 1, 3)) virtual BY USER for statistics)

Le calcul de stats étendues sur l'expression génère en réalité la création d'une colonne virtuelle sur la table TDO_D_ASSIN_ROLE_COORD_BQE.

Une fois les stats étendues calculées (c-a-d une fois la colonne virtuelle créée), on obtient le plan suivant lorsqu'on exécute de nouveau la requête:

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                              |      1 |        |  18229 |00:03:09.28 |     429K|    311K|       |       |          |
|   1 |  CONCATENATION                           |                              |      1 |        |  18229 |00:03:09.28 |     429K|    311K|       |       |          |
|*  2 |   HASH JOIN                              |                              |      1 |  20717 |  18229 |00:02:03.16 |     288K|    228K|  2494K|  1736K| 2540K (0)|
|*  3 |    INDEX FAST FULL SCAN                  | UK_TDO_R_PDV_DISTRIB         |      1 |  28501 |  28497 |00:00:00.02 |     167 |      0 |       |       |          |
|   4 |    NESTED LOOPS OUTER                    |                              |      1 |  20717 |  18229 |00:02:03.07 |     288K|    228K|       |       |          |
|*  5 |     HASH JOIN                            |                              |      1 |  20717 |  18229 |00:02:02.62 |     232K|    228K|  2327K|  1129K| 3297K (0)|
|*  6 |      HASH JOIN                           |                              |      1 |  20717 |  18261 |00:01:22.93 |   86307 |  83282 |  2129K|  2004K| 2289K (0)|
|   7 |       INDEX FAST FULL SCAN               | PK_GIC_DEM_LISTE_CONTRATS    |      1 |  20000 |  20000 |00:00:00.02 |    2718 |      0 |       |       |          |
|   8 |       PARTITION LIST ALL                 |                              |      1 |     18M|     18M|00:01:09.89 |   83589 |  83282 |       |       |          |
|*  9 |        TABLE ACCESS FULL                 | TDO_D_ASSIN_ROLE_COORD_BQE   |    227 |     18M|     18M|00:01:02.25 |   83589 |  83282 |       |       |          |
|  10 |      TABLE ACCESS FULL                   | TDO_D_ASSIN_COORD_BQE        |      1 |     16M|     16M|00:00:27.26 |     146K|    145K|       |       |          |
|  11 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | TDO_D_ASSIN_CONTR_ASSU       |  18229 |      1 |  18229 |00:00:00.41 |   55907 |      0 |       |       |          |
|* 12 |      INDEX UNIQUE SCAN                   | PK_ASSIN_CON_NUM_CONTR       |  18229 |      1 |  18229 |00:00:00.23 |   37677 |      0 |       |       |          |
|  13 |   NESTED LOOPS                           |                              |      1 |      1 |      0 |00:01:06.09 |     141K|  83282 |       |       |          |
|  14 |    NESTED LOOPS                          |                              |      1 |      1 |      0 |00:01:06.09 |     141K|  83282 |       |       |          |
|  15 |     NESTED LOOPS                         |                              |      1 |      1 |      0 |00:01:06.09 |     141K|  83282 |       |       |          |
|* 16 |      FILTER                              |                              |      1 |        |      0 |00:01:06.09 |     141K|  83282 |       |       |          |
|  17 |       NESTED LOOPS OUTER                 |                              |      1 |      1 |  18261 |00:01:06.08 |     141K|  83282 |       |       |          |
|* 18 |        HASH JOIN                         |                              |      1 |  20717 |  18261 |00:01:05.80 |   86306 |  83282 |  2129K|  2004K| 2283K (0)|
|  19 |         INDEX FAST FULL SCAN             | PK_GIC_DEM_LISTE_CONTRATS    |      1 |  20000 |  20000 |00:00:00.02 |    2718 |      0 |       |       |          |
|  20 |         PARTITION LIST ALL               |                              |      1 |     18M|     18M|00:00:52.92 |   83588 |  83282 |       |       |          |
|* 21 |          TABLE ACCESS FULL               | TDO_D_ASSIN_ROLE_COORD_BQE   |    227 |     18M|     18M|00:00:45.32 |   83588 |  83282 |       |       |          |
|  22 |        TABLE ACCESS BY GLOBAL INDEX ROWID| TDO_D_ASSIN_CONTR_ASSU       |  18261 |      1 |  18261 |00:00:00.26 |   54798 |      0 |       |       |          |
|* 23 |         INDEX UNIQUE SCAN                | PK_ASSIN_CON_NUM_CONTR       |  18261 |      1 |  18261 |00:00:00.13 |   36537 |      0 |       |       |          |
|* 24 |      INDEX FAST FULL SCAN                | UK_TDO_R_PDV_DISTRIB         |      0 |  36746 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 25 |     INDEX RANGE SCAN                     | PK_ASSIN_CBQ_TEC_COORD_PERSO |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  26 |    TABLE ACCESS BY INDEX ROWID           | TDO_D_ASSIN_COORD_BQE        |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("PDVD_CD_POINT_VENTE"="CON"."CON_CD_PVE_SOUS")
   3 - filter("PDVD_CD_DISTRIB"='400000')
   5 - access("CB"."CBQ_ID_PERSONNE"="RBQ"."RBQ_ID_PERSONNE" AND "CB"."CBQ_ID_TEC_COORD_BQE"="RBQ"."RBQ_ID_TEC_COORD_BQE")
   6 - access("RBQ"."RBQ_NUM_CONTR"="NUM_CONTRAT")
   9 - filter((SUBSTR("RBQ_NUM_CONTR",1,3)<>'398' AND SUBSTR("RBQ_NUM_CONTR",1,3)<>'399' AND SUBSTR("RBQ_NUM_CONTR",1,3)<>'400' AND 
              SUBSTR("RBQ_NUM_CONTR",1,3)<>'412'))
  12 - access("CON_NUM_CONTR"="RBQ"."RBQ_NUM_CONTR")
  16 - filter("CON"."CON_CD_PVE_SOUS" IS NULL)
  18 - access("RBQ"."RBQ_NUM_CONTR"="NUM_CONTRAT")
  21 - filter((SUBSTR("RBQ_NUM_CONTR",1,3)<>'398' AND SUBSTR("RBQ_NUM_CONTR",1,3)<>'399' AND SUBSTR("RBQ_NUM_CONTR",1,3)<>'400' AND 
              SUBSTR("RBQ_NUM_CONTR",1,3)<>'412'))
  23 - access("CON_NUM_CONTR"="RBQ"."RBQ_NUM_CONTR")
  24 - filter((LNNVL("PDVD_CD_POINT_VENTE"="CON"."CON_CD_PVE_SOUS") OR LNNVL("PDVD_CD_DISTRIB"='400000')))
  25 - access("CB"."CBQ_ID_PERSONNE"="RBQ"."RBQ_ID_PERSONNE" AND "CB"."CBQ_ID_TEC_COORD_BQE"="RBQ"."RBQ_ID_TEC_COORD_BQE")

Tout d'abord on note que la requête s'exécute en 3 minutes (alors qu'elle n'aboutissait pas sans les stats étendues). Ensuite on voit que la cardinalité est bien estimée pour la table TDO_D_ASSIN_ROLE_COORD_BQE (18M pour les colonnes E-ROWS et A-ROWS).
Enfin, on constate que grâce à cette bonne estimation la table n'est plus attaquée via un NESTED LOOP mais que le CBO a opté pour un HASH JOIN tout à fait justifié.

Cas 2: Stats étendues sur des colonnes corrélées

Le second problème concernait la requête suivante qui mettait 2h34 pour s'exécuter:

SELECT DISTINCT
 FLC_DT_FIN_ECH_FLUX Date_arrete,
 ASS_NUM_CONTR_COLLECT_CNP Num_contrat,
 ASS_NUM_CONTRACTANT Id_coll,
 SUM (
 DECODE (ASS_NUM_CONTRACTANT,
 '00698', 12 * ADH_SOMME_MVT_RELATIFS_TTC,
 '77009', 12 * ADH_SOMME_MVT_RELATIFS_TTC,
 '90074', 12 * ADH_SOMME_MVT_RELATIFS_TTC,
 4 * ADH_SOMME_MVT_RELATIFS_TTC))
FROM odd_flux_cid a, odd_assure b, odd_info_adhesion d
where a.num_integration in (8118,8112,8069,8186,8148,8119,8094,8070,8187,8149,8120,8095,8071,8121,8096,8072,8188,8150,8122,8097,8073,8189,8151,8123,8098,8074,8190,8152,8125,8099,8075,8191,8153,8127,
8100,8192,8154,8129,8101,8077,8193,8155,8130,8102,8078,8194,8156,8131,8103,8079,8195,8158,8132,8104,8080,8197,8159,8133,8105,8081,8162,8134,8106,8082,8198,8163,8135,8107,8083,8200,8164,8136,8108,8084,
8137,8110,8085,8165,8138,8109,8086,8202,8166,8139,8111,8087)
 AND b.num_integration = d.num_integration
 AND ASS_ID_FLUX = ADH_ID_FLUX
 AND ASS_NUM_CCOLTE = ADH_NUM_CCOLTE
 AND ASS_NUM_REF_ASS = ADH_NUM_REF_ASS
 AND a.num_integration = b.num_integration
 AND ASS_ID_FLUX = flc_id_flux
 AND ASS_ID_DELEGATAIRE = flc_id_delegataire
GROUP BY FLC_DT_FIN_ECH_FLUX, ASS_NUM_CONTR_COLLECT_CNP, ASS_NUM_CONTRACTANT;




1469 rows selected.


Elapsed: 02:34:56.28


Plan hash value: 863059560


-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                      |      1 |        |   1469 |00:11:35.38 |      13M|    392K|       |       |          |
|   1 |  HASH GROUP BY                         |                      |      1 |  11546 |   1469 |00:11:35.38 |      13M|    392K|   855K|   855K| 2582K (0)|
|   2 |   NESTED LOOPS                         |                      |      1 |        |     11M|02:34:29.63 |      13M|    392K|       |       |          |
|   3 |    NESTED LOOPS                        |                      |      1 |  11546 |     11M|02:32:29.42 |    2327K|    258K|       |       |          |
|   4 |     NESTED LOOPS                       |                      |      1 |   7504 |   7949K|00:01:24.63 |    1442K|    184K|       |       |          |
|   5 |      PARTITION RANGE INLIST            |                      |      1 |   1310 |     86 |00:00:01.02 |     173 |    172 |       |       |          |
|*  6 |       TABLE ACCESS FULL                | ODD_FLUX_CID         |     86 |   1310 |     86 |00:00:01.02 |     173 |    172 |       |       |          |
|   7 |      PARTITION RANGE AND               |                      |     86 |      6 |   7949K|00:01:20.55 |    1442K|    184K|       |       |          |
|*  8 |       TABLE ACCESS BY LOCAL INDEX ROWID| ODD_ASSURE           |     86 |      6 |   7949K|00:01:17.40 |    1442K|    184K|       |       |          |
|*  9 |        INDEX RANGE SCAN                | PK_ASS_ASSURE        |     86 |     56 |   7949K|00:00:17.86 |   34929 |  34746 |       |       |          |
|  10 |     PARTITION RANGE AND                |                      |   7949K|      1 |     11M|02:30:54.44 |     884K|  74033 |       |       |          |
|* 11 |      INDEX RANGE SCAN                  | PK_ADH_INFO_ADHESION |   7949K|      1 |     11M|00:01:04.85 |     884K|  74033 |       |       |          |
|  12 |    TABLE ACCESS BY LOCAL INDEX ROWID   | ODD_INFO_ADHESION    |     11M|      2 |     11M|00:01:46.81 |      11M|    134K|       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   6 - filter(("A"."NUM_INTEGRATION"=8069 OR "A"."NUM_INTEGRATION"=8070 OR "A"."NUM_INTEGRATION"=8071 OR "A"."NUM_INTEGRATION"=8072 OR
              "A"."NUM_INTEGRATION"=8073 OR "A"."NUM_INTEGRATION"=8074 OR "A"."NUM_INTEGRATION"=8075 OR "A"."NUM_INTEGRATION"=8077 OR "A"."NUM_INTEGRATION"=8078
              OR "A"."NUM_INTEGRATION"=8079 OR "A"."NUM_INTEGRATION"=8080 OR "A"."NUM_INTEGRATION"=8081 OR "A"."NUM_INTEGRATION"=8082 OR
              "A"."NUM_INTEGRATION"=8083 OR "A"."NUM_INTEGRATION"=8084 OR "A"."NUM_INTEGRATION"=8085 OR "A"."NUM_INTEGRATION"=8086 OR "A"."NUM_INTEGRATION"=8087
              OR "A"."NUM_INTEGRATION"=8094 OR "A"."NUM_INTEGRATION"=8095 OR "A"."NUM_INTEGRATION"=8096 OR "A"."NUM_INTEGRATION"=8097 OR
              "A"."NUM_INTEGRATION"=8098 OR "A"."NUM_INTEGRATION"=8099 OR "A"."NUM_INTEGRATION"=8100 OR "A"."NUM_INTEGRATION"=8101 OR "A"."NUM_INTEGRATION"=8102
              OR "A"."NUM_INTEGRATION"=8103 OR "A"."NUM_INTEGRATION"=8104 OR "A"."NUM_INTEGRATION"=8105 OR "A"."NUM_INTEGRATION"=8106 OR
              "A"."NUM_INTEGRATION"=8107 OR "A"."NUM_INTEGRATION"=8108 OR "A"."NUM_INTEGRATION"=8109 OR "A"."NUM_INTEGRATION"=8110 OR "A"."NUM_INTEGRATION"=8111
              OR "A"."NUM_INTEGRATION"=8112 OR "A"."NUM_INTEGRATION"=8118 OR "A"."NUM_INTEGRATION"=8119 OR "A"."NUM_INTEGRATION"=8120 OR
              "A"."NUM_INTEGRATION"=8121 OR "A"."NUM_INTEGRATION"=8122 OR "A"."NUM_INTEGRATION"=8123 OR "A"."NUM_INTEGRATION"=8125 OR "A"."NUM_INTEGRATION"=8127
              OR "A"."NUM_INTEGRATION"=8129 OR "A"."NUM_INTEGRATION"=8130 OR "A"."NUM_INTEGRATION"=8131 OR "A"."NUM_INTEGRATION"=8132 OR
              "A"."NUM_INTEGRATION"=8133 OR "A"."NUM_INTEGRATION"=8134 OR "A"."NUM_INTEGRATION"=8135 OR "A"."NUM_INTEGRATION"=8136 OR "A"."NUM_INTEGRATION"=8137
              OR "A"."NUM_INTEGRATION"=8138 OR "A"."NUM_INTEGRATION"=8139 OR "A"."NUM_INTEGRATION"=8148 OR "A"."NUM_INTEGRATION"=8149 OR
              "A"."NUM_INTEGRATION"=8150 OR "A"."NUM_INTEGRATION"=8151 OR "A"."NUM_INTEGRATION"=8152 OR "A"."NUM_INTEGRATION"=8153 OR "A"."NUM_INTEGRATION"=8154
              OR "A"."NUM_INTEGRATION"=8155 OR "A"."NUM_INTEGRATION"=8156 OR "A"."NUM_INTEGRATION"=8158 OR "A"."NUM_INTEGRATION"=8159 OR
              "A"."NUM_INTEGRATION"=8162 OR "A"."NUM_INTEGRATION"=8163 OR "A"."NUM_INTEGRATION"=8164 OR "A"."NUM_INTEGRATION"=8165 OR "A"."NUM_INTEGRATION"=8166
              OR "A"."NUM_INTEGRATION"=8186 OR "A"."NUM_INTEGRATION"=8187 OR "A"."NUM_INTEGRATION"=8188 OR "A"."NUM_INTEGRATION"=8189 OR
              "A"."NUM_INTEGRATION"=8190 OR "A"."NUM_INTEGRATION"=8191 OR "A"."NUM_INTEGRATION"=8192 OR "A"."NUM_INTEGRATION"=8193 OR "A"."NUM_INTEGRATION"=8194
              OR "A"."NUM_INTEGRATION"=8195 OR "A"."NUM_INTEGRATION"=8197 OR "A"."NUM_INTEGRATION"=8198 OR "A"."NUM_INTEGRATION"=8200 OR
              "A"."NUM_INTEGRATION"=8202))
   8 - filter("ASS_ID_DELEGATAIRE"="FLC_ID_DELEGATAIRE")
   9 - access("A"."NUM_INTEGRATION"="B"."NUM_INTEGRATION" AND "ASS_ID_FLUX"="FLC_ID_FLUX")
       filter(("B"."NUM_INTEGRATION"=8069 OR "B"."NUM_INTEGRATION"=8070 OR "B"."NUM_INTEGRATION"=8071 OR "B"."NUM_INTEGRATION"=8072 OR
              "B"."NUM_INTEGRATION"=8073 OR "B"."NUM_INTEGRATION"=8074 OR "B"."NUM_INTEGRATION"=8075 OR "B"."NUM_INTEGRATION"=8077 OR "B"."NUM_INTEGRATION"=8078
              OR "B"."NUM_INTEGRATION"=8079 OR "B"."NUM_INTEGRATION"=8080 OR "B"."NUM_INTEGRATION"=8081 OR "B"."NUM_INTEGRATION"=8082 OR
              "B"."NUM_INTEGRATION"=8083 OR "B"."NUM_INTEGRATION"=8084 OR "B"."NUM_INTEGRATION"=8085 OR "B"."NUM_INTEGRATION"=8086 OR "B"."NUM_INTEGRATION"=8087
              OR "B"."NUM_INTEGRATION"=8094 OR "B"."NUM_INTEGRATION"=8095 OR "B"."NUM_INTEGRATION"=8096 OR "B"."NUM_INTEGRATION"=8097 OR
              "B"."NUM_INTEGRATION"=8098 OR "B"."NUM_INTEGRATION"=8099 OR "B"."NUM_INTEGRATION"=8100 OR "B"."NUM_INTEGRATION"=8101 OR "B"."NUM_INTEGRATION"=8102
              OR "B"."NUM_INTEGRATION"=8103 OR "B"."NUM_INTEGRATION"=8104 OR "B"."NUM_INTEGRATION"=8105 OR "B"."NUM_INTEGRATION"=8106 OR
              "B"."NUM_INTEGRATION"=8107 OR "B"."NUM_INTEGRATION"=8108 OR "B"."NUM_INTEGRATION"=8109 OR "B"."NUM_INTEGRATION"=8110 OR "B"."NUM_INTEGRATION"=8111
              OR "B"."NUM_INTEGRATION"=8112 OR "B"."NUM_INTEGRATION"=8118 OR "B"."NUM_INTEGRATION"=8119 OR "B"."NUM_INTEGRATION"=8120 OR
              "B"."NUM_INTEGRATION"=8121 OR "B"."NUM_INTEGRATION"=8122 OR "B"."NUM_INTEGRATION"=8123 OR "B"."NUM_INTEGRATION"=8125 OR "B"."NUM_INTEGRATION"=8127
              OR "B"."NUM_INTEGRATION"=8129 OR "B"."NUM_INTEGRATION"=8130 OR "B"."NUM_INTEGRATION"=8131 OR "B"."NUM_INTEGRATION"=8132 OR
              "B"."NUM_INTEGRATION"=8133 OR "B"."NUM_INTEGRATION"=8134 OR "B"."NUM_INTEGRATION"=8135 OR "B"."NUM_INTEGRATION"=8136 OR "B"."NUM_INTEGRATION"=8137
              OR "B"."NUM_INTEGRATION"=8138 OR "B"."NUM_INTEGRATION"=8139 OR "B"."NUM_INTEGRATION"=8148 OR "B"."NUM_INTEGRATION"=8149 OR
              "B"."NUM_INTEGRATION"=8150 OR "B"."NUM_INTEGRATION"=8151 OR "B"."NUM_INTEGRATION"=8152 OR "B"."NUM_INTEGRATION"=8153 OR "B"."NUM_INTEGRATION"=8154
              OR "B"."NUM_INTEGRATION"=8155 OR "B"."NUM_INTEGRATION"=8156 OR "B"."NUM_INTEGRATION"=8158 OR "B"."NUM_INTEGRATION"=8159 OR
              "B"."NUM_INTEGRATION"=8162 OR "B"."NUM_INTEGRATION"=8163 OR "B"."NUM_INTEGRATION"=8164 OR "B"."NUM_INTEGRATION"=8165 OR "B"."NUM_INTEGRATION"=8166
              OR "B"."NUM_INTEGRATION"=8186 OR "B"."NUM_INTEGRATION"=8187 OR "B"."NUM_INTEGRATION"=8188 OR "B"."NUM_INTEGRATION"=8189 OR
              "B"."NUM_INTEGRATION"=8190 OR "B"."NUM_INTEGRATION"=8191 OR "B"."NUM_INTEGRATION"=8192 OR "B"."NUM_INTEGRATION"=8193 OR "B"."NUM_INTEGRATION"=8194
              OR "B"."NUM_INTEGRATION"=8195 OR "B"."NUM_INTEGRATION"=8197 OR "B"."NUM_INTEGRATION"=8198 OR "B"."NUM_INTEGRATION"=8200 OR
              "B"."NUM_INTEGRATION"=8202))
  11 - access("B"."NUM_INTEGRATION"="D"."NUM_INTEGRATION" AND "ASS_ID_FLUX"="ADH_ID_FLUX" AND "ASS_NUM_CCOLTE"="ADH_NUM_CCOLTE" AND
              "ASS_NUM_REF_ASS"="ADH_NUM_REF_ASS")
       filter(("D"."NUM_INTEGRATION"=8069 OR "D"."NUM_INTEGRATION"=8070 OR "D"."NUM_INTEGRATION"=8071 OR "D"."NUM_INTEGRATION"=8072 OR
              "D"."NUM_INTEGRATION"=8073 OR "D"."NUM_INTEGRATION"=8074 OR "D"."NUM_INTEGRATION"=8075 OR "D"."NUM_INTEGRATION"=8077 OR "D"."NUM_INTEGRATION"=8078
              OR "D"."NUM_INTEGRATION"=8079 OR "D"."NUM_INTEGRATION"=8080 OR "D"."NUM_INTEGRATION"=8081 OR "D"."NUM_INTEGRATION"=8082 OR
              "D"."NUM_INTEGRATION"=8083 OR "D"."NUM_INTEGRATION"=8084 OR "D"."NUM_INTEGRATION"=8085 OR "D"."NUM_INTEGRATION"=8086 OR "D"."NUM_INTEGRATION"=8087
              OR "D"."NUM_INTEGRATION"=8094 OR "D"."NUM_INTEGRATION"=8095 OR "D"."NUM_INTEGRATION"=8096 OR "D"."NUM_INTEGRATION"=8097 OR
              "D"."NUM_INTEGRATION"=8098 OR "D"."NUM_INTEGRATION"=8099 OR "D"."NUM_INTEGRATION"=8100 OR "D"."NUM_INTEGRATION"=8101 OR "D"."NUM_INTEGRATION"=8102
              OR "D"."NUM_INTEGRATION"=8103 OR "D"."NUM_INTEGRATION"=8104 OR "D"."NUM_INTEGRATION"=8105 OR "D"."NUM_INTEGRATION"=8106 OR
              "D"."NUM_INTEGRATION"=8107 OR "D"."NUM_INTEGRATION"=8108 OR "D"."NUM_INTEGRATION"=8109 OR "D"."NUM_INTEGRATION"=8110 OR "D"."NUM_INTEGRATION"=8111
              OR "D"."NUM_INTEGRATION"=8112 OR "D"."NUM_INTEGRATION"=8118 OR "D"."NUM_INTEGRATION"=8119 OR "D"."NUM_INTEGRATION"=8120 OR
              "D"."NUM_INTEGRATION"=8121 OR "D"."NUM_INTEGRATION"=8122 OR "D"."NUM_INTEGRATION"=8123 OR "D"."NUM_INTEGRATION"=8125 OR "D"."NUM_INTEGRATION"=8127
              OR "D"."NUM_INTEGRATION"=8129 OR "D"."NUM_INTEGRATION"=8130 OR "D"."NUM_INTEGRATION"=8131 OR "D"."NUM_INTEGRATION"=8132 OR
              "D"."NUM_INTEGRATION"=8133 OR "D"."NUM_INTEGRATION"=8134 OR "D"."NUM_INTEGRATION"=8135 OR "D"."NUM_INTEGRATION"=8136 OR "D"."NUM_INTEGRATION"=8137
              OR "D"."NUM_INTEGRATION"=8138 OR "D"."NUM_INTEGRATION"=8139 OR "D"."NUM_INTEGRATION"=8148 OR "D"."NUM_INTEGRATION"=8149 OR
              "D"."NUM_INTEGRATION"=8150 OR "D"."NUM_INTEGRATION"=8151 OR "D"."NUM_INTEGRATION"=8152 OR "D"."NUM_INTEGRATION"=8153 OR "D"."NUM_INTEGRATION"=8154
              OR "D"."NUM_INTEGRATION"=8155 OR "D"."NUM_INTEGRATION"=8156 OR "D"."NUM_INTEGRATION"=8158 OR "D"."NUM_INTEGRATION"=8159 OR
              "D"."NUM_INTEGRATION"=8162 OR "D"."NUM_INTEGRATION"=8163 OR "D"."NUM_INTEGRATION"=8164 OR "D"."NUM_INTEGRATION"=8165 OR "D"."NUM_INTEGRATION"=8166
              OR "D"."NUM_INTEGRATION"=8186 OR "D"."NUM_INTEGRATION"=8187 OR "D"."NUM_INTEGRATION"=8188 OR "D"."NUM_INTEGRATION"=8189 OR
              "D"."NUM_INTEGRATION"=8190 OR "D"."NUM_INTEGRATION"=8191 OR "D"."NUM_INTEGRATION"=8192 OR "D"."NUM_INTEGRATION"=8193 OR "D"."NUM_INTEGRATION"=8194
              OR "D"."NUM_INTEGRATION"=8195 OR "D"."NUM_INTEGRATION"=8197 OR "D"."NUM_INTEGRATION"=8198 OR "D"."NUM_INTEGRATION"=8200 OR
              "D"."NUM_INTEGRATION"=8202))

Si on regarde le plan on voit que sur les 2h34 d'exécution on a 2h30 passées sur l'accès à la table ODD_INFO_ADHESION via l'opération "PARTITION RANGE AND".
Cette opération est exécutée 7949000 fois (cf. colonne STARTS) car la jointure précédente entre les tables ODD_FLUX_CID et ODD_ASSURE retourne 7949000 lignes (cf. opération 4 du plan).
Si on regarde la colonne E-ROWS on voit que l'optimiseur estime que la jointure ne retournerait que 7504 lignes. C'est cette mauvaise estimation qui induit derrière le NESTED LOOP hyper couteux sur la table ODD_INFO_ADHESION.
La jointure avec la table ODD_ASSURE s'effectue sur les colonnes NUM_INTEGRATION, ASS_ID_FLUX et ASS_ID_DELEGATAIRE.
Effectuons un comptage sur la table ODD_ASSURE juste avec la clause NUM_INTEGRATION:

select count(*) from ODD_ASSURE b
where b.num_integration in (8118,8112,8069,8186,8148,8119,8094,8070,8187,8149,8120,8095,8071,8121,8096,8072,8188,8150,8122,8097,8073,8189,8151,8123,8098,8074,8190,8152,8125,8099,8075,8191,8153,8127,
   8100,8192,8154,8129,8101,8077,8193,8155,8130,8102,8078,8194,8156,8131,8103,8079,8195,8158,8132,8104,8080,8197,8159,8133,8105,8081,8162,8134,8106,8082,8198,8163,8135,8107,8083,8200,8164,8136,8108,8084,
   8137,8110,8085,8165,8138,8109,8086,8202,8166,8139,8111,8087);
   
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |      1 |        |      1 |00:00:12.96 |   34926 |  34925 |
|   1 |  SORT AGGREGATE            |               |      1 |      1 |      1 |00:00:12.96 |   34926 |  34925 |
|   2 |   INLIST ITERATOR          |               |      1 |        |   7949K|00:00:11.72 |   34926 |  34925 |
|   3 |    PARTITION RANGE ITERATOR|               |     86 |   7967K|   7949K|00:00:09.01 |   34926 |  34925 |
|*  4 |     INDEX RANGE SCAN       | PK_ASS_ASSURE |     86 |   7967K|   7949K|00:00:06.37 |   34926 |  34925 |
---------------------------------------------------------------------------------------------------------------

On voit que le nombre de lignes retournées est toujours de 7949K alors que cette fois on a utilisé uniqument la colonne NUM_INTEGRATION.
Il semble que les 2 autres colonnes n'influent pas sur la cardinalité. Cela revient donc à dire qu'il existe une corrélation entre les 3 colonnes NUM_INTEGRATION, ASS_ID_FLUX et ASS_ID_DELEGATAIRE.
Il serait donc intéressant de calculer des stats étendues pour la table ODD_ASSURE sur ces 3 colonnes ainsi que pour la table ODD_FLUX_CID:

BEGIN
 DBMS_STATS.gather_table_stats('AODD02','ODD_ASSURE',
 method_opt => 'FOR COLUMNS (NUM_INTEGRATION,ASS_ID_FLUX,ASS_ID_DELEGATAIRE) size 1',
 NO_INVALIDATE => FALSE,
 FORCE => TRUE
 );
END;
/


BEGIN
 DBMS_STATS.gather_table_stats('AODD02','ODD_FLUX_CID',
 method_opt => 'FOR COLUMNS (NUM_INTEGRATION,FLC_ID_FLUX,FLC_ID_DELEGATAIRE) size 1',
 NO_INVALIDATE => FALSE,
 FORCE => TRUE
 );
END;
/

Si on relance la requête on obtient désormais le plan suivant:

------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                   |      1 |        |   1469 |00:01:35.85 |     283K|    288K|   5872 |       |       |          |         |
|   1 |  HASH GROUP BY        |                   |      1 |    355K|   1469 |00:01:35.85 |     283K|    288K|   5872 |   855K|   855K|   17M (0)|         |
|   2 |   PARTITION RANGE AND |                   |      1 |     53M|     11M|00:01:19.64 |     283K|    288K|   5872 |       |       |          |         |
|*  3 |    HASH JOIN          |                   |     86 |     53M|     11M|00:01:15.20 |     283K|    288K|   5872 |   972K|   972K|  372K (0)|         |
|*  4 |     TABLE ACCESS FULL | ODD_FLUX_CID      |     86 |   1322 |     86 |00:00:00.52 |     172 |    172 |      0 |       |       |          |         |
|*  5 |     HASH JOIN         |                   |     86 |     53M|     11M|00:01:01.45 |     282K|    288K|   5872 |  1889M|    35M|   20M (0)|   12288 |
|*  6 |      TABLE ACCESS FULL| ODD_ASSURE        |     86 |     37M|   7949K|00:00:11.45 |     146K|    146K|      0 |       |       |          |         |
|*  7 |      TABLE ACCESS FULL| ODD_INFO_ADHESION |     86 |     54M|     11M|00:00:19.97 |     136K|    136K|      0 |       |       |          |         |
------------------------------------------------------------------------------------------------------------------------------------------------------------

Les stats étendues ont permis d'obtenir une cardinalité pas tout à fait exact mais en tout cas plus proche de la réalité.
Les NESTED LOOP ont laissé place à des HASH JOIN ce qui permet de faire tourner la requête en 1 minute 35 au lieu de 2h34.

J'espère que ces 2 exemples tirés de la réalité vous permettront de comprendre comment grâce aux statistiques étendues on peut aider l'optimiseur à avoir une connaissance plus intelligente des données et ainsi lui permettre de nous trouver un plan adequat.