[O8i]update et performances

**lalystar** · 22/11/2004, 16h20

Orafrance t'as déjà posé la question, tu as combien d'index sur la table ?

Sinon quand tu lances ton update tu vois quoi comme attente dans V$SESSION_WAIT qui correspond à ta session ? (lance plusieurs fois, c'est l'attente en instantanée...)

Sinon je vois plus que Statspack...

Laly.

**orafrance** · 22/11/2004, 16h22

la trace de la session en level 12 pourrait fournir pas mal d'info aussi

**lalystar** · 22/11/2004, 16h32

C'est du 8i ou du 9i ?

En trace level = 12, dans TkProf on verra les attentes, sinon sous 8i, il y a le petit script perl que j'ai mis dans les sources.

Mais il faudrait mettre rownum <= 100 si l'update rend pas la main.

Laly.

**Fabien Celaia** · 22/11/2004, 16h37

Envoyé par lalystar

Orafrance t'as déjà posé la question, tu as combien d'index sur la table ?

Sinon quand tu lances ton update tu vois quoi comme attente dans V$SESSION_WAIT qui correspond à ta session ? (lance plusieurs fois, c'est l'attente en instantanée...)

Sinon je vois plus que Statspack...

Laly.

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
SQL> select * from v$session_wait where sid=17 ;
 
SID = 17
SEQ# = 61
EVENT =direct path write file
P1TEXT = file number                   
P1 = 23
P1RAW  = 00000017  
P2TEXT = first dba
P2 = 23612 
P2RAW = 00005C3C
P3TEXT = block cnt  
P3 = 32
P3RAW   =  00000020
WAIT_TIME  = -2
SECONDS_IN_WAIT =  1595
STATE = WAITED UNKNOWN TIME

En ce qui concerne les indexes : un index d'une colonne sur chacune des tables

**rouardg** · 22/11/2004, 16h42

Bonjour Fadace,

Essayons de résumer un peu : tu as un pb de perf sur un Update (les Américains appellent cela un Slow Update).

D'après ma propre expérience, il y a deux cas de figure :

1 ) pb de lignes chaînées. Lalystar t'a déjà posé cette question : as tu des lignes chaînées sur ta table FUSASS ???
(pour le savoir, comme tu es en 8i, fais un ANALYZE TABLE FUSASS COMPUTE STATISTICS ; et regarde le résultat par un SELECT NUM_ROWS, CHAIN_CNT FROM USER_TABLES WHERE TABLE_NAME = 'FUSASS' ;
Normalement, ton CHAIN_CNT doit être à 0 (ou bien une valeur très faible, du genre 1 % des lignes de la table doivent être chaînées),

2 ) ton plan d'exécution a l'air pas mal. Tu as un Full Scan sur FUSASS à cause de l'Update, et dans la requête imbriquée, tu as un Range Scan sur GFUCVTP via l'index FC_GFUCVTP.

Ce qu'il faudrait nous dire, c'est que quelle(s) colonne(s) porte cet index ?
Est-ce sur la colonne cdoldcai, ou sur txcptnom, ou les 2 ?

A ce niveau, tu peux essayer de créer sur GFUCVTP un index encore plus sélectif, pour diminuer le nombre de blocs d'index et de tables remontés par le Range Scan.

Et puis pour finir, je vois que tu as répondu :

En ce qui concerne les indexes : un index d'une colonne sur chacune des tables

Ca c'est pas terrible du tout. En tout cas, ta colonne noAssNew est indexée, et cela ralentit l'Update. Es tu sur d'avoir besoin de tous ces index ???

**orafrance** · 22/11/2004, 16h43

tu pourrais faire ceci :

Code :

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

ALTER SESSION SET EVENTS '10046 trace name context forever, LEVEL 12';

Ensuite tu fais ton UPDATE et enfin :

Code :

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

ALTER SESSION SET EVENTS '10046 trace name context off';

et nous envoyer le fichier généré dans le répertoire udump

Code :

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

show parameter user_dump_dest

Cette commande permet d'avoir le répertoire de udump

**Fabien Celaia** · 22/11/2004, 17h24

Envoyé par rouardg

1 ) pb de lignes chaînées. Lalystar t'a déjà posé cette question : as tu des lignes chaînées sur ta table FUSASS ???
(pour le savoir, comme tu es en 8i, fais un ANALYZE TABLE FUSASS COMPUTE STATISTICS ; et regarde le résultat par un SELECT NUM_ROWS, CHAIN_CNT FROM USER_TABLES WHERE TABLE_NAME = 'FUSASS' ;
Normalement, ton CHAIN_CNT doit être à 0 (ou bien une valeur très faible, du genre 1 % des lignes de la table doivent être chaînées),

Ca peut venir de la : malgré le recalcul des stats, j'ai NUM_ROWS = 71504 et CHAIN_CNT = 1411, ce qui veut dire, si je ne m'abuse, que la table est fortement fragmentée ?

Envoyé par rouardg

2 ) ton plan d'exécution a l'air pas mal. Tu as un Full Scan sur FUSASS à cause de l'Update, et dans la requête imbriquée, tu as un Range Scan sur GFUCVTP via l'index FC_GFUCVTP.

Ce qu'il faudrait nous dire, c'est que quelle(s) colonne(s) porte cet index ?
Est-ce sur la colonne cdoldcai, ou sur txcptnom, ou les 2 ?

les 2 indexes portent sur
1) GFUCVTP(TXCPTNOM )
2) FUSASS(CDCAISSE, NOASS)

Envoyé par rouardg

A ce niveau, tu peux essayer de créer sur GFUCVTP un index encore plus sélectif, pour diminuer le nombre de blocs d'index et de tables remontés par le Range Scan.

Et puis pour finir, je vois que tu as répondu :

En ce qui concerne les indexes : un index d'une colonne sur chacune des tables

Ca c'est pas terrible du tout. En tout cas, ta colonne noAssNew est indexée, et cela ralentit l'Update. Es tu sur d'avoir besoin de tous ces index ???

Qu'est ce qui n'est pas terrible du tout ? Je n'ai somme tout que un index par table, ce qui me semble assez correct pour un environnement OLTP. Je ne crois pas avoir dit que NoAssNew soit indexé.

**orafrance** · 22/11/2004, 17h31

je crains que Rouarg a cru que tu avais un index sur chacune de tes colonnes

dans ton cas, pas de problème a priori

**orafrance** · 22/11/2004, 17h41

tu pourrais faire la même chose en ajoutant un ROWNUM < 101 pour voir une trace compléte ?

**rouardg** · 22/11/2004, 17h55

je crains que Rouarg a cru que tu avais un index sur chacune de tes colonnes dans ton cas, pas de problème a priori

Milles excuses à tous : j'ai effectivement cru que chaque colonne de ces 2 tables était indexée. C'est moi qui ai mal compris.

Ca peut venir de la : malgré le recalcul des stats, j'ai NUM_ROWS = 71504 et CHAIN_CNT = 1411, ce qui veut dire, si je ne m'abuse, que la table est fortement fragmentée ?

Fadace, tu as effectivement 100 * 1411 / 71504 = 2 % des lignes de la table qui sont chaînées, ce qui contribue à ralentir ton Update.

les 2 indexes portent sur
1) GFUCVTP(TXCPTNOM )
2) FUSASS(CDCAISSE, NOASS)

Ok. Une solution serait de poser un index encore plus sélectif sur la table GFUCVTP. Je pense notamment a un index composite, c'est-à-dire portant sur plusieurs colonnes. Difficile de travailler sur la colonne NBOLDVAL, à cause de l'appel à TO_CHAR (à moins de travailler sur un index basé sur une fonction). Par contre, tu peux poser un index sur les colonnes txcptnom et cdoldcai. A toi de choisir le bon ordre entre ces 2 colonnes, de manière à avoir l'index le plus sélectif possible dès la première colonne.
Pour finir, lorsque tu auras choisi ton index, sachant que tu as des lignes chaînées dans ta table, tu pourras réorganiser ta table en réinsérant les données suivant ton index, ceci afin de favoriser le clustering factor et donc de limiter le nombre de blocs lus de la table.

Fadace, comprends-tu mes propos quand je parle de clustering factor et de réorganisation de table ???

**Fabien Celaia** · 22/11/2004, 17h59

avec un ROWNUM < 101

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
TKPROF: Release 8.1.7.0.0 - Production on Mon Nov 22 17:55:06 2004
 
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
 
Trace file: w4_ora_23828.trc
Sort options: default
 
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************
 
ALTER SESSION SET EVENTS '10046 trace name context forever, LEVEL 12'
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0
 
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 27
********************************************************************************
 
update FUSASS a set a.noAssNew =
        (select b.nbnewval
         from GFUCVTP b
         where a.cdCaisse = b.cdoldcai
    and  a.noAss = to_char(b.nboldval)
    and  b.txcptnom = :"SYS_B_0"
        )
where ROWNUM < :"SYS_B_1"
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0     307602        112         100
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0     307602        112         100
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 27
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  UPDATE FUSASS
    101   COUNT STOPKEY
    100    TABLE ACCESS FULL FUSASS
 
********************************************************************************
 
ALTER SESSION SET EVENTS '10046 trace name context off'
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 27
 
 
 
********************************************************************************
 
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          0     307602        112         100
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.00       0.00          0     307602        112         100
 
Misses in library cache during parse: 2
 
 
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0
 
Misses in library cache during parse: 0
 
    3  user  SQL statements in session.
    0  internal SQL statements in session.
    3  SQL statements in session.
********************************************************************************
Trace file: w4_ora_23828.trc
Trace file compatibility: 8.00.04
Sort options: default
 
       1  session in tracefile.
       3  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       3  SQL statements in trace file.
       3  unique SQL statements in trace file.
      63  lines in trace file.

**Fabien Celaia** · 22/11/2004, 18h02

Envoyé par rouardg

Fadace, comprends-tu mes propos quand je parle de clustering factor et de réorganisation de table ???

Oui, merci

**orafrance** · 22/11/2004, 18h05

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 307602 112 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 307602 112 100

bigre... ça en fait de l'accés disque pour 100 lignes

**lalystar** · 23/11/2004, 05h11

direct path write file

Execute 1 0.00 0.00 0 307602 112 100

ca fait plus de 3000 blocs par lignes

Tu aurais pas un gros long/lob dans ta table ?

Laly.

**lalystar** · 23/11/2004, 05h50

Entièrement d'accord avec Rouarg :

dans la table FUSASS qui fait 630K pour un (cdCaisse, noAss) tu n'as qu'une seule ligne avec txtCptNom = 'MACLE'
il serait intéressant d'étendre l'index à (txtCptNom, cdCaisse, noAss)

Quel est le résultat de cette requête :

Code :

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

1
2
3
4
 
select count(*)
from   FUSASS
where txtCptNom = 'MACLE'

Vu le nb de blocs lu, je me demande si l'index sur txtCptNom est réellement utilisé dans l'update.

Laly.

**Fabien Celaia** · 23/11/2004, 07h49

Ca me chagrine, mais pas de lob en vue, comme vous pouvez le voir dans le post initial.

Je vais creuser du côté des indexes composites

**lalystar** · 23/11/2004, 08h19

Je suis persuadé que c'est ca le pb :
pour chaque ligne de FUSASS à updater, Oracle parcourt toute la table GFUCVTP pour trouver l'unique ligne correspondante sans utiliser l'index.

Est-ce-que tu as collecté des stats sur GFUCVTP avec des histogrammes sur les colonnes indexés et sur les indexes ?Qqchose comme :

Code :

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

1
2
3
4
analyze table GFUCVTP estimate statistics sample 20 percent 
for table
for all columns size 1
for all indexes

Est-ce-que ca change qqchose ?

Laly.

**Fabien Celaia** · 23/11/2004, 08h35

Code :

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

1
2
3
4
5
6
7
select count(*)
from   GFUCVTP
where txCptNom ='MACLE'
 
 COUNT(*)
---------
   397040

**lalystar** · 23/11/2004, 08h40

J'en suis de plus en plus persuadé : cet index sur txCptNom n'est pas utile car il rammène plus de 80% de la table. Il faut l'étendre aux deux autres colonnes pour qu'il soit utile au moins pour cette requête.

Laly.

**Fabien Celaia** · 23/11/2004, 08h48

Avec l'index composite sur 3 colonnes, et après stats

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
update FUSASS a set a.noAssNew =
        (select b.nbnewval
         from GFUCVTP b
         where a.cdCaisse = b.cdoldcai
    and  a.noAss = to_char(b.nboldval)
    and  b.txcptnom = :"SYS_B_0"
        )
where rownum < :"SYS_B_1"          /* < 101*/
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0      55010        108         100
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0      55010        108         100

En enlevant txcptnom de la clé, ça ne s'améliore de loin pas (*2 !)

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
update FUSASS a set a.noAssNew =
        (select b.nbnewval
         from GFUCVTP b
         where a.cdCaisse = b.cdoldcai
    and  a.noAss = to_char(b.nboldval)
    and  b.txcptnom = :"SYS_B_0"
        )
where rownum < :"SYS_B_1"
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          2     118134        214         200
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          2     118134        214         200

[O8i]update et performances

Oracle

Discussions similaires

Partager

Partager