Optimisation d'un 'sort'

**bidi** · 30/10/2009, 11h42

Hello,

Voilà c'est très simple

Disons que j'ai 3 tables:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
ACTOR (+- 1800000 rows)
FILES (+- 1700000 rows)
FILE_CONTRACT (+-500000 rows)

la requête paginée suivante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
select * 
  from 
( select rownum rnum, a.*
    from (SELECT contract.INTEGRATED_FILE_ID
          FROM FILE_CONTRACT file_contract,
               FILES contract,
               ACTOR declarant
          WHERE file_contract.CONTRACTOR_ID=declarant.ID
          AND file_contract.FILE_ID=contract.ID
          AND declarant.ACTOR_TYPE_CODE ='contractor'
          AND declarant.BCE_NUM=999999724
          ORDER BY contract.INTEGRATED_FILE_ID desc
         ) a
   where rownum < 25 
)
where rnum >= 10;

qui contient donc un order by (pour la pagination).

les indexes sur les champs utilisés dans cette requête sont:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
ACTOR(BCE_NUM)
FILES(ID,INTEGRATED_FILE_ID)
FILE_CONTRACT(CONTRACTOR_ID,FILE_ID)

l'explain plan:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                         |  Name                           | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                                 |     1 |    26 |    10 |
|*  1 |  VIEW                             |                                 |     1 |    26 |    10 |
|*  2 |   COUNT STOPKEY                   |                                 |       |       |       |
|   3 |    VIEW                           |                                 |     1 |    13 |    10 |
|*  4 |     SORT ORDER BY STOPKEY         |                                 |     1 |    40 |    10 |
|   5 |      NESTED LOOPS                 |                                 |     1 |    40 |     8 |
|   6 |       NESTED LOOPS                |                                 |     4 |   116 |     4 |
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
|*  7 |        TABLE ACCESS BY INDEX ROWID| ACTOR                           |     1 |    18 |     2 |
|*  8 |         INDEX RANGE SCAN          | ACTOR_M2_BCE_NUM                |     2 |       |     1 |
|*  9 |        INDEX RANGE SCAN           | FILE_CONTRACT_M1_CONTRACTOR_ID  |     1 |    11 |     2 |
|* 10 |       INDEX RANGE SCAN            | FILES_M3_INT_FILE_ID            |  1635K|    17M|     1 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("from$_subquery$_001"."RNUM">=10)
   2 - filter(ROWNUM<25)
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
   4 - filter(ROWNUM<25)
   7 - filter("DECLARANT"."ACTOR_TYPE_CODE"='contractor')
   8 - access("DECLARANT"."BCE_NUM"=416451583)
   9 - access("FILE_CONTRACT"."CONTRACTOR_ID"="DECLARANT"."ID")
  10 - access(SYS_OP_DESCEND("FILE_CONTRACT"."FILE_ID")=SYS_OP_DESCEND("CONTRACT"."ID"))
       filter("FILE_CONTRACT"."FILE_ID"="CONTRACT"."ID")

Le problème c'est que c'est quand même assez couteux... Ca tombe même en time out (30sec!) quand la db est chargée ;(

Et ça à l'air de venir de la clause order by. (Normal j'imagine que ça prenne du temps mais si quelqu'un sait comment optimiser ça, en jouant sur les indexes ou autre chose...)

Voici, pour info les traces, on voit clairement la différence avec la clause order by ou sans:

Avec:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=1 Bytes=26)
   1    0   VIEW (Cost=10 Card=1 Bytes=26)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=10 Card=1 Bytes=13)
   4    3         SORT (ORDER BY STOPKEY) (Cost=10 Card=1 Bytes=40)
   5    4           NESTED LOOPS (Cost=8 Card=1 Bytes=40)
   6    5             NESTED LOOPS (Cost=4 Card=4 Bytes=116)
   7    6               TABLE ACCESS (BY INDEX ROWID) OF 'ACTOR' (Cost
          =2 Card=1 Bytes=18)
 
   8    7                 INDEX (RANGE SCAN) OF 'ACTOR_M2_BCE_NUM' (NO
          N-UNIQUE) (Cost=1 Card=2)
 
   9    6               INDEX (RANGE SCAN) OF 'FILE_CONTRACT_M1_CONTRA
          CTOR_ID' (NON-UNIQUE) (Cost=2 Card=1 Bytes=11)
 
  10    5             INDEX (RANGE SCAN) OF 'FILES_M3_INT_FILE_ID' (NO
          N-UNIQUE) (Cost=1 Card=1635731 Bytes=17993041)
 
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5970  consistent gets
       1630  physical reads
          0  redo size
        669  bytes sent via SQL*Net to client
        368  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         15  rows processed

et sans:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=26)
   1    0   VIEW (Cost=8 Card=1 Bytes=26)
   2    1     COUNT (STOPKEY)
   3    2       NESTED LOOPS (Cost=8 Card=1 Bytes=40)
   4    3         NESTED LOOPS (Cost=4 Card=4 Bytes=116)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'ACTOR' (Cost=2 C
          ard=1 Bytes=18)
 
   6    5             INDEX (RANGE SCAN) OF 'ACTOR_M2_BCE_NUM' (NON-UN
          IQUE) (Cost=1 Card=2)
 
   7    4           INDEX (RANGE SCAN) OF 'FILE_CONTRACT_M1_CONTRACTOR
          _ID' (NON-UNIQUE) (Cost=2 Card=1 Bytes=11)
 
   8    3         INDEX (RANGE SCAN) OF 'FILES_M3_INT_FILE_ID' (NON-UN
          IQUE) (Cost=1 Card=1635731 Bytes=17993041)
 
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         59  consistent gets
          0  physical reads
          0  redo size
        640  bytes sent via SQL*Net to client
        368  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processed

Voilà

j'ai l'impression que ce sont les physical reads qui plombent tout mais je ne sais pas trop d'ou ça vient... et même s'il y a moyen d'optimiser ça...

Merci pour ceux qui ont lu jusqu'au bout

**pacmann** · 30/10/2009, 13h21

Salut !

Est-ce que tes conditions :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
          AND declarant.ACTOR_TYPE_CODE ='contractor'
          AND declarant.BCE_NUM=999999724

... identifient un declarant de manière unique ?

Si oui, y a-t-il concrètement une contrainte ?
Parce que si c'est le cas, il ne devrait pas y avoir de tri en passant par l'index...

Sinon, le je suis un peu traumatisé par les 1635K indiqués dans le plan...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
|* 10 |       INDEX RANGE SCAN            | FILES_M3_INT_FILE_ID            |  1635K|    17M|     1 |

Tu es sensé avoir combien de lignes sans les ROWNUM (toutes pages comprises, donc) ?

**bidi** · 30/10/2009, 13h53

Envoyé par pacmann

Salut !

Est-ce que tes conditions :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
          AND declarant.ACTOR_TYPE_CODE ='contractor'
          AND declarant.BCE_NUM=999999724

... identifient un declarant de manière unique ?

Oui

Envoyé par pacmann

Si oui, y a-t-il concrètement une contrainte ?

Heu non...

Envoyé par pacmann

Parce que si c'est le cas, il ne devrait pas y avoir de tri en passant par l'index...

Je vais essayer en rajoutant la contrainte alors.

Envoyé par pacmann

Sinon, le je suis un peu traumatisé par les 1635K indiqués dans le plan...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
|* 10 |       INDEX RANGE SCAN            | FILES_M3_INT_FILE_ID            |  1635K|    17M|     1 |

Tu es sensé avoir combien de lignes sans les ROWNUM (toutes pages comprises, donc) ?

Environ 3000 dans ce cas... d'où la pagination. J'ai raté quelque chose ou il y a quelque chose que je ne fais pas bien?

Pour info, 'Businessement parlant', cette requête est donc censée ramener la liste des déclarations d'un déclarant par paquet de 15 donc. En lui présentant les plus nouvelles en premier.

Merci pour ton aide

**bidi** · 30/10/2009, 14h06

Heu sorry, je ne peux pas mettre de contrainte BCE_NUM/ACTOR_TYPE_CODE

parce parfois BCE_NUM peut être null et on peut avoir des cas de ce genre:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
 
BCE_NUM   |   ACTOR_TYPE_CODE
---------------------------------
null      |    labo
null      |    labo
...
...

Donc sorry

**mnitu** · 30/10/2009, 15h16

Envoyé par bidi

...Voilà

j'ai l'impression que ce sont les physical reads qui plombent tout mais je ne sais pas trop d'ou ça vient... et même s'il y a moyen d'optimiser ça...

Merci pour ceux qui ont lu jusqu'au bout

Pour quoi il y a autant de consistents gets ? A priori le tri (sorts) se fait en mémoire seulement.
Sinon, le plan est presque identique dans le deux cas donc ce n'est pas le order by qui pose des soucis.

**bidi** · 30/10/2009, 15h26

Je n'en sais rien pourquoi il y a autant de consistents gets justement

Ce que je constate c'est qu'il y en a autant justement quand la clause order by est présente.

Et je ne parviens pas à comprendre pourquoi...

**bidi** · 30/10/2009, 15h47

se pourrait-il que ça ait un rapport avec ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
Index_name   |   clustering_factor
FILE_CONTRACT_M1_CONTRACTOR_ID   |   313679
FILE_CONTRACT_M2_COMPANY_ID   |   1780
FILE_CONTRACT_M3_CONTACT_ID   |   21874
FILE_CONTRACT_PK   |   1746

Le clustering factor (merci le blog de pacman ;p) est super élevé pour FILE_CONTRACT_M1_CONTRACTOR_ID.

Puis-je y faire quelque chose? j'ai bien peur que non...

**mnitu** · 02/11/2009, 10h40

Le clustering factor n’est qu’une mesure de l’écart entre la façon dans laquelle les données sont stockées dans l’index et sur le disque. C’est un attribut de l’index qui exprime que l’index est plus ou moins intéressant comme choix pour la recherche des données par rapport à un autre index ou un full table scan. Une réorganisation des données peut changer cette valeur.
Le consistent gets c’est la lecture des données en mode consistent : par rapport au début de l’interrogation.
[EDIT] Un nombre important de consistent gets indique d’habitude que les données lues sont en train d’être modifiés par un autre traitement.
Ce n’est pas le tri qui implique un nombre élevé des consistent gets, d’où ma question.
[/EDIT]
Le paragraphe édité est incorrect.

**bidi** · 02/11/2009, 14h21

Bon, j'ai reconstruit la table avec un order by contractor_id et là je n'ai plus de physical reads pour ma requête et ça va beaucoup plus vite. Par contre c'est juste pour le fun car j'imagine qu'une telle pratique n'est pas viable en production ...

Pour le nombre de consistents gets, désolé mais je ne vois pas d'où ça vient...Il n'y a rien d'autre qui modifie les données quand j'y accède. Et, encore une fois, si l'on regarde la trace, ce nombre est ridiculement bas quand on ne fait pas le order by...(de +-60 sans à +-6000 avec...)

**pacmann** · 02/11/2009, 14h56

Le nombre de consistent gets est comme le dit Mnitu le nombre de lectures consistentes... et là dedans, il y a le nombre de lectures !

S'il faut que tu tries les lignes avant de prendre les 25 premières, deux solutions :
- tu les récupères dans le bon ordre parce tu arrives à avoir le bon chemin d'accès
- tu les récupères toutes et tu tries

Et c'est pour ça que sans l'order by, il y a beaucoup moins à lire, puisque tu récupères juste les 25 premières.

Et c'est pour cela que si tu pouvais garantir l'unicité du déclarant, l'index sur INTEGRATED_FILE_ID suffisait pour faire le tri (avec un COUNT STOP KEY)
Maintenant, comme tu ne peux pas, tu peux envisager de gruger... en forçant la première partie de la sélection à ne renvoyer qu'une ligne ?

[EDIT]
Selon ta version d'Oracle (en dessous de 10, il me semble qu'on n'arrive pas au WINDOW SORT STOP KEY par l'index...), tu peux tenter par exemple de prendre les 25 premières lignes de chaque déclarant avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT * FROM (
SELECT contract.INTEGRATED_FILE_ID, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY INTEGRATED_FILE_ID) rk
          FROM FILE_CONTRACT file_contract,
               FILES contract,
               ACTOR declarant
          WHERE file_contract.CONTRACTOR_ID=declarant.ID
          AND file_contract.FILE_ID=contract.ID
          AND declarant.ACTOR_TYPE_CODE ='contractor'
          AND declarant.BCE_NUM=999999724
          )
WHERE rk between 10 AND 25

Si tu n'es pas sûr d'avoir un seul déclarant, tu refiltres le résultat avec ROWNUM ou ROW_NUMBER...

**bidi** · 03/11/2009, 12h09

re

Bon, j'ai essayé plein de trucs mais c'est pas très concluant...

1) j'ai rendu le déclarant unique comme tu l'avais suggéré en faisant un petit test et en lui fournissant directement l'id de celui-ci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
select * 
  from 
( select rownum rnum, a.*
    from (SELECT contract.INTEGRATED_FILE_ID
          FROM FILE_CONTRACT file_contract,
               FILES contract
          WHERE file_contract.CONTRACTOR_ID=40798
          AND file_contract.FILE_ID=contract.ID
              ORDER BY contract.INTEGRATED_FILE_ID desc
         ) a
   where rownum < 25 
)
where rnum >= 10;

Donc c'est bien simplifié.

Avec le order by ==> toujours pareil, toujours autant de consistents gets

Sans le order by ==> super rapide et une cinquantaine de consistents gets. Ca à l'air trié mais sur le file_id de file_contract (index contractor_id, file_id je suppose...) et non sur l'integrated_file_id de files et ça me semble logique... mais ce n'est pas ce que je veux

2) J'ai essayé ta requête mais elle n'a pas l'air correcte. Ca ne limite pas le nombre de lignes renvoyées, y'a des doublons,...(ptet % à ma version d'Oracle (9.2))

Si quelqu'un à une idée de comment faire un sort desc avec ma query en jouant sur les indexes et en me passant de "l'order by"...

J'ai l'impression que je ne peux pas me passer de l'order by vu qu'il n'y a aucune clause where qui porte sur integrated_file_id. Mais je me trompe peut-être...

Merci

**bidi** · 03/11/2009, 16h20

Bon j'arrête de me prendre la tête, je pense que le problème est plus au niveau fonctionnel, je vais discuter de ça avec les analystes

Merci pour votre aide.

**mnitu** · 06/11/2009, 17h33

Quelle est la valeur du paramètre workarea_size_policy

**pacmann** · 06/11/2009, 19h27

Si tu es sûr d'avoir un seul id, ajoute le à l'order by...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
ORDER BY contract.id, contract.INTEGRATED_FILE_ID DESC

Si tu n'es pas sûr, le problème est alors fonctionnel : tu ne pourrais jamais éviter ton tri avec cet index...

Optimisation d'un 'sort'

SQL Oracle

Discussions similaires

Partager

Partager