Comportement optimizer entre 9.2.0.1 et 9.2.0.8

[pholos]

hello,

nous avons migrés recement un datawarehouse de 600Go depuis une 9.2.0.1 vers une 9.2.0.8.

j'ai depuis de bonnes surprises sur les perfs generales de la base sauf pour un cas particulier que je ne comprend pas. la requete en question prend 4 secondes sur la 9.2.0.1 et ne retourne toujours rien au bout de 10mn sur l'autre, je fini par l'anuler.

je vois bien qu'il y a un produit cartesien sur 131 millions de lignes qui est fait sur la 9.2.0.8 qui n'est pas fait sur l'autre.

j'ai tenté les hints pour tester d'autres plans d'executions mais sans succès.

Code :

1
2
3
 
SELECT DISTINCT fam."Code famille", fam."Famille", fam."Code sous-famille", fam."Sous-famille" 
FROM V_EXT_PROCTER_SS_FAMILLE_FAM fam

voici les tkprof des traces entre les 2 serveurs :

9.2.0.1 :

Code :

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
 
SELECT DISTINCT fam."Code famille"--, fam."Famille", fam."Code sous-famille", fam."Sous-famille" 
FROM V_EXT_PROCTER_SS_FAMILLE_FAM fam 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.14       0.14          0          3          1           0
Execute      1      0.01       0.01          0          0          3           0
Fetch        2      0.32       1.72       7247      36305          0          35
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.48       1.88       7247      36308          4          35
 
Misses IN library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 22  
 
Rows     Row Source Operation
-------  ---------------------------------------------------
     35    
     35  SORT UNIQUE 
   3209   NESTED LOOPS  
   3209   NESTED LOOPS  
   3209    NESTED LOOPS  
   3209    NESTED LOOPS  
   3209     NESTED LOOPS  
   3209     HASH JOIN  
     56      NESTED LOOPS  
     56      INDEX FAST FULL SCAN I_DA_U_1 (object id 809379)
     56      INDEX UNIQUE SCAN PK_FOURNISSEURS (object id 1181469)
 543523      TABLE ACCESS FULL HISTO_EAN_DA 
   3209     TABLE ACCESS BY LOCAL INDEX ROWID LIEN_EAN_NOMENCLATURE PARTITION: 3 3 
   3209       INDEX UNIQUE SCAN PK_LIEN_EAN_NOMENCLATURE PARTITION: 3 3 (object id 9159904)
   3209     TABLE ACCESS BY INDEX ROWID NOMENCLATURE 
   3209        INDEX UNIQUE SCAN PK_NEW_NOMENCLATURE (object id 114032)
   3209    INDEX UNIQUE SCAN PK_NCL_NIVEAU_4 (object id 114042)
   3209    INDEX UNIQUE SCAN PK_NCL_NIVEAU_3 (object id 114040)
   3209   INDEX UNIQUE SCAN PK_EAN_VENTE (object id 111044)
 
********************************************************************************
 
 
********************************************************************************
 
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.14       0.14          0          3          1           0
Execute      3      0.01       0.01          0          0          3           0
Fetch        2      0.32       1.72       7247      36305          0          35
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.48       1.88       7247      36308          4          35
 
Misses IN library cache during parse: 2
Misses IN library cache during execute: 1
 
 
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       12      0.00       0.00          0          0          0           0
Execute     14      0.34       2.50       7347       7414          4        3209
Fetch       12      0.00       0.00          0         27          0           7
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       38      0.34       2.51       7347       7441          4        3216
 
Misses IN library cache during parse: 7
 
    4  user  SQL statements IN session.
   11  internal SQL statements IN session.
   15  SQL statements IN session.
********************************************************************************

9.2.0.8

Code :

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
 
 
SELECT DISTINCT fam."Code famille"--, fam."Famille", fam."Code sous-famille", fam."Sous-famille" 
FROM V_EXT_PROCTER_SS_FAMILLE_FAM fam 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.15       0.15         53          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1     45.87      53.43     394292    3829511          2           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     46.03      53.59     394345    3829513          2           0
 
Misses IN library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 84  
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT UNIQUE 
      0   NESTED LOOPS  
 764336    NESTED LOOPS  
 764336     NESTED LOOPS  
 764336      HASH JOIN  
     56       INDEX FAST FULL SCAN I_DA_U_1 (object id 1437785)
131337985       MERGE JOIN CARTESIAN 
    236        NESTED LOOPS  
   1294         HASH JOIN  
   1059          INDEX FAST FULL SCAN PK_NCL_NIVEAU_3 (object id 1438121)
   1481          INDEX FAST FULL SCAN I_NOMENCLATURE_U_1 (object id 1438136)
    236         INDEX UNIQUE SCAN PK_NCL_NIVEAU_4 (object id 1438123)
131337985        BUFFER SORT 
 556689         TABLE ACCESS FULL HISTO_EAN_DA 
 764336      INDEX UNIQUE SCAN PK_FOURNISSEURS (object id 1437963)
 764336     INDEX UNIQUE SCAN PK_EAN_VENTE (object id 1437811)
      0    INDEX UNIQUE SCAN IDX_LIEN_EAN_NOMENCLATURE (object id 1438099)
 
********************************************************************************
 
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       34      0.03       0.08          5         13          0           0
Execute     97      0.03       0.01          0          0          0           0
Fetch      277      0.01       0.36         53        455          0         218
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      408      0.07       0.46         58        468          0         218
 
Misses IN library cache during parse: 21
 
    3  user  SQL statements IN session.
   34  internal SQL statements IN session.
   37  SQL statements IN session.
********************************************************************************
Trace file: trace_9.2.0.8.trc
Trace file compatibility: 9.00.01
Sort options: DEFAULT
 
       1  session IN tracefile.
       3  user  SQL statements IN trace file.
      34  internal SQL statements IN trace file.
      37  SQL statements IN trace file.
      24  UNIQUE SQL statements IN trace file.
     664  LINES IN trace file.

qu'en pensez-vous?

que puis-je faire pour coller à l'ancien plan d'execution sachant que les volumetries n'ont pas changé entre temps?

merci!

[bibi92]

Bonjour,

Pour ma part, je sais qu'un certains nombres de bugs sont corrigés en 9.2.0.8.
Ci-joint :

Bonne journée

Brigitte

[bibi92]

Avez-vous penser à recalculer les stats exec dbms_stats.gather_schema_stats (ownname=> 'SCHEMA , estimate_percent=> 30 , cascade=> TRUE)

Bonne journée

Brigitte

[pholos]

j'ai oublié de preciser que ca requete sur une force view qui est elle meme d'un select distinct d'une autre vue...