Optimiser un INSERT global

**niiccoo** · 22/05/2006, 12h19

Bonjour à tous,

j'aimerais optimiser une requête qui prend énormément de temps, environ 30 minutes alors qu'elle n'insère que 120 000 enregistrements. Voilà la requête en question :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
insert into int_session (id_session, dat_deb, heure_deb, minute_deb, dat_crea,
                         origine_session, id_interne_individu, IP, ORI_IP)
   (select Id_session,trunc(min(date_hit),'dd'),to_char(min(date_hit), 'HH24'),
           to_char(min(date_hit),'MI'), max(sysdate),max(origine_session),
           max(nvl(id_interne_individu,0)),max(a.IP),999
      from imp_navigation a
        where status='N'
         and exists (select '*' from int_individu e
                     where nvl(a.id_interne_individu,0) = e.id_interne_individu and e.flag_supp = 0)
         and not exists (select '*' from int_session b where a.id_session = b.id_session)
         and not exists (select '*' from INT_ORI_IP b where a.IP = b.IP )
       group by id_session);

Je pense donc que le temps perdu se situe au niveau de l'insert. Pourtant sémantiquement c'est mieux de faire un insert de 1000 lignes que 1000 inserts d'une ligne.

Au niveau des volumétries :
* imp_navigation :~1Million
* int_session : ~5Millions
* int_individu : ~220 000
* INT_ORI_IP : 7

Au niveau des index j'ai :
* imp_navigation (status)
* - int_session(id_session)
- int_session(origine_session, id_session)
- int_session(date_deb, id_interne_individu, id_session)
* INT_ORI_IP(ip)
* int_individu(id_interne_individu, flag_supp)

J'ai également essayer de changer ma requête sans faire de sous requetes mais des left outer join, mais le gain est nul :'(. Voici la requête modifiée :

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
 
select
   a.Id_session,
   trunc(min(a.date_hit),'dd'),
   to_char(min(a.date_hit), 'HH24'),
   to_char(min(a.date_hit),'MI'),
   max(sysdate),
   max(a.origine_session),
   max(nvl(id_interne_individu,0)),
   max(a.ip),
   999
  from
   imp_navigation a
  left outer join int_individu e on
   e.id_interne_individu = nvl(a.id_interne_individu, 0) and
   e.flag_supp = 0
  left outer join int_session b on
   b.id_session = a.id_session
  left outer join int_ori_ip c on
   c.ip = a.ip
  where
   a.status='N' and
   e.id_interne_individu is not null and
   b.id_session is null and
   c.ip is null
  group by
    a.id_session

Bref, je sèche complètement et je pense qu'une requête de 30 minutes doit pouvoir être optimisée (surtout pour n'insérer que 120000 lignes). Existe t'il une méthode afin d'améliorer la vitesse d'insertion ?

Merci d'avance à tous

EDIT: M -> Million

**Jaouad** · 22/05/2006, 12h28

Bonjour ,
Il nous faudrait l'explain plan de la requête sur le select

**orafrance** · 22/05/2006, 12h34

essaye INSERT /*+ APPEND */ et envoie l'explain plan ainsi que les indexes STP

**niiccoo** · 22/05/2006, 13h07

Envoyé par Fred_D

essaye INSERT /*+ APPEND */ et envoie l'explain plan ainsi que les indexes STP

à quoi sert exactement le APPEND ? je pense avoir compris que cela sert à se passer du RBS afin de gagner du temps, ai-je bon ?

**orafrance** · 22/05/2006, 13h16

Envoyé par niiccoo

à quoi sert exactement le APPEND ? je pense avoir compris que cela sert à se passer du RBS afin de gagner du temps, ai-je bon ?

http://download-west.oracle.com/docs...tsref.htm#7834

Il fait du DIRECT_PATH et ne s'occupe pas de l'allocation de bloc

Mais ton SELECT doit être perfectible. Si status contient peut de valeur distinct et qu'il est rarement mis à jour tu peux essayer de mettre un index BITMAP. En remplaçant le EXISTS par IN tu ne devrait plus faire qu'un accés sur cet index

**niiccoo** · 22/05/2006, 12h37

Voici le plan pour la première requête :

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
 
"-----------------------------------------------------------------------------------------------"
"| Id  | Operation                       | Name                        | Rows  | Bytes | Cost  |"
"-----------------------------------------------------------------------------------------------"
"|   0 | SELECT STATEMENT                |                             |     1 |   118 |   191 |"
"|   1 |  HASH GROUP BY                  |                             |     1 |   118 |   191 |"
"|   2 |   NESTED LOOPS ANTI             |                             |     1 |   118 |   190 |"
"|   3 |    NESTED LOOPS ANTI            |                             |     9 |   765 |   181 |"
"|   4 |     HASH JOIN SEMI              |                             |    15 |  1065 |   181 |"
"|   5 |      TABLE ACCESS BY INDEX ROWID| IMP_NAVIGATION              |  1197 | 74214 |    28 |"
"|   6 |       INDEX RANGE SCAN          | INDEX_IMP_NAVIGATION_STATUS |  1197 |       |     6 |"
"|   7 |      INDEX FAST FULL SCAN       | INT_INDIVIDU_INDX           |   117K|  1037K|   151 |"
"|   8 |     INDEX UNIQUE SCAN           | PK_INT_ORI_IP               |     3 |    42 |     0 |"
"|   9 |    INDEX UNIQUE SCAN            | PK_INT_SESSIONS             |  4347K|   136M|     1 |"
"-----------------------------------------------------------------------------------------------"

et le plan d'exe pour la requête que j'ai modifié en supprimant les requêtes imbriquées :

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
 
"------------------------------------------------------------------------------------------------"
"| Id  | Operation                        | Name                        | Rows  | Bytes | Cost  |"
"------------------------------------------------------------------------------------------------"
"|   0 | SELECT STATEMENT                 |                             |    15 |  1815 |   212 |"
"|   1 |  HASH GROUP BY                   |                             |    15 |  1815 |   212 |"
"|   2 |   NESTED LOOPS ANTI              |                             |    15 |  1815 |   211 |"
"|   3 |    FILTER                        |                             |       |       |       |"
"|   4 |     NESTED LOOPS OUTER           |                             |    15 |  1605 |   211 |"
"|   5 |      HASH JOIN                   |                             |    15 |  1065 |   181 |"
"|   6 |       TABLE ACCESS BY INDEX ROWID| IMP_NAVIGATION              |  1197 | 74214 |    28 |"
"|   7 |        INDEX RANGE SCAN          | INDEX_IMP_NAVIGATION_STATUS |  1197 |       |     6 |"
"|   8 |       INDEX FAST FULL SCAN       | INT_INDIVIDU_INDX           |   117K|  1037K|   151 |"
"|   9 |      TABLE ACCESS BY INDEX ROWID | INT_SESSION                 |     1 |    36 |     2 |"
"|  10 |       INDEX UNIQUE SCAN          | PK_INT_SESSIONS             |     1 |       |     1 |"
"|  11 |    INDEX UNIQUE SCAN             | PK_INT_ORI_IP               |     1 |    14 |     0 |"
"------------------------------------------------------------------------------------------------"

**Jaouad** · 22/05/2006, 12h41

Peux t'on avoir les colognes qui portent sur cet index : INT_INDIVIDU_INDX

Lorsque tu écrit :

Au niveau des volumétries :
* imp_navigation :~1M
* int_session : ~5M

M représente mille ou millions

**niiccoo** · 22/05/2006, 12h44

M comme million

méa culpa, il faut toujours préciser ses abréviations

**Jaouad** · 22/05/2006, 12h55

Envoyé par niiccoo

M comme million

méa culpa, il faut toujours préciser ses abréviations

et les colonnes indéxés

**orafrance** · 22/05/2006, 12h45

si j'ai bien compris int_ori_ip c'est la liste des IP... donc s'il n'y a pas de ligne dans int_session il ne devrait pas y en avoir dans int_ori_ip non ? Du coup tu peux supprimer un NOT EXISTS.

Essaye :

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
INSERT INTO int_session
            (id_session, dat_deb, heure_deb, minute_deb, dat_crea,
             origine_session, id_interne_individu, ip, ori_ip)
   (SELECT   id_session, TRUNC (MIN (date_hit), 'dd'),
             TO_CHAR (MIN (date_hit), 'HH24'), 
             TO_CHAR (MIN (date_hit), 'MI'),
             MAX (SYSDATE), 
             MAX (origine_session),
             MAX (NVL (id_interne_individu, 0)), 
             MAX (a.ip), 999
        FROM imp_navigation a,int_individu e
       WHERE status = 'N'
         AND NVL (a.id_interne_individu, 0) = e.id_interne_individu
         AND e.flag_supp = 0
         AND NOT EXISTS (SELECT '*'
                           FROM int_session b
                          WHERE a.id_session = b.id_session)
    GROUP BY id_session);

NVL (a.id_interne_individu, 0) = e.id_interne_individu

c'est pas top... tu peux pas faire un OR a.id_interne_individu IS NULL ?

**niiccoo** · 22/05/2006, 12h58

int_ori_ip contient seulement 7ip. Ce sont des ip à exclure pour les statistiques afin de ne pas les insérer dans int_session.

juste en dessous dans ma procédure pl:sql j'ai la requête qui insère dans int_session_bis