lundi 8 juin 2015

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/