ACCESS FULL apres un compute statistics sous Oracle 8.1.7

**othon_oda** · 27/04/2006, 15h30

Bonjour,
j'ai un ordre SQL avec des jointures externes. Si je fait un tkrpof je m'apercoie que cet ordre fait bcp d'access full. Pourtant les indexes ont l'air bien.
Je decide d'effacer les stat et la mon ordre est nickel avec aucin access full. Je refait un compute des stats avec dbms et j'ai a nouveau des access full.
Que faut il faire ?

Merci pour votre aide.

**orafrance** · 27/04/2006, 15h33

zut... j'ai oublié ma boule de crystal et ne peut donc pas vous aider désolé

Plus sérieusement, la requête SQL et les indexes nous seraient d'un grand secours pour être efficace

**othon_oda** · 27/04/2006, 15h45

Bon, pour ceux qui ne sont pas devins.
voila la requete complexes faite par hibernet.

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
SELECT DISTINCT pms_evenem0_.even_id_even AS x0_0_,
                pms_evenem0_.even_dat_eve AS x1_0_,
                pms_evenem0_.even_dat_eve AS x2_0_,
                pms_evenem0_.even_libelle AS x3_0_,
                pms_evenem0_.even_ind_hyp_ini AS x4_0_,
                ide_medeci1_.mede_nom_usu_maj AS x5_0_,
                ide_medeci1_.mede_prenom AS x6_0_,
                bas_uf2_.unfo_code || ' - ' || bas_uf2_.unfo_libelle AS x7_0_,
                ide_sejour3_.sejo_num_sej AS x8_0_,
                bas_type_i12_.tyin_id_tyin AS x9_0_,
                pms_evenem0_.even_id_obj AS x10_0_,
                pms_rhs7_.trhs_id_trhs AS x11_0_,
                pms_rhs9_.trhs_id_trhs AS x12_0_,
                pms_rhs10_.trhs_id_trhs AS x13_0_,
                pms_rums8_.trum_id_trum AS x14_0_,
                sad_diag_s11_.dsai_code_d AS x15_0_,
                sad_diag_s11_.dsai_libelle_d AS x16_0_
           FROM pms_evenement pms_evenem0_,
                ide_medecin ide_medeci1_,
                bas_uf bas_uf2_,
                ide_sejour ide_sejour3_,
                pms_acte_reeduc pms_acte_r4_,
                pms_dependance pms_depend5_,
                sad_recueil sad_recuei6_,
                pms_rhs pms_rhs7_,
                pms_rum pms_rums8_,
                pms_rhs pms_rhs9_,
                pms_rhs pms_rhs10_,
                sad_diag_saisis sad_diag_s11_,
                bas_type_info bas_type_i12_
          WHERE pms_evenem0_.even_id_mede = ide_medeci1_.mede_id_mede(+)
            AND pms_evenem0_.even_id_unfo = bas_uf2_.unfo_id_unfo(+)
            AND pms_evenem0_.even_id_sejo = ide_sejour3_.sejo_id_sejo(+)
            AND sad_recuei6_.recu_id_recu = pms_rhs7_.trhs_id_recu(+)
            AND sad_recuei6_.recu_id_recu = pms_rums8_.trum_id_recu(+)
            AND pms_acte_r4_.acre_id_trhs = pms_rhs9_.trhs_id_trhs(+)
            AND pms_depend5_.rdep_id_trhs = pms_rhs10_.trhs_id_trhs(+)
            AND sad_recuei6_.recu_id_recu = sad_diag_s11_.dsai_id_recu(+)
            AND pms_evenem0_.even_id_tyin = bas_type_i12_.tyin_id_tyin
            AND (    (    bas_type_i12_.tyin_code IN
                             ('REC_ACT_DIAG',
                              'REC_ACT_PRE',
                              'REC_ACT_REED',
                              'REC_DEP',
                              'REC_GCAP',
                              'REC_PRES'
                             )
                      AND pms_evenem0_.even_id_tyin =
                                                    bas_type_i12_.tyin_id_tyin
                     )
                 AND (   (ide_sejour3_.sejo_id_sejo = 2327815)
                      OR (    (ide_sejour3_.sejo_id_sejo IS NULL)
                          AND (    bas_type_i12_.tyin_code = 'REC_PRES'
                               AND pms_evenem0_.even_id_tyin =
                                                    bas_type_i12_.tyin_id_tyin
                              )
                          AND (pms_evenem0_.even_dat_eve >=
                                          TO_DATE ('06/03/2006', 'dd/mm/yyyy')
                              )
                          AND (pms_evenem0_.even_id_pati = 1752653)
                         )
                     )
                 AND (pms_evenem0_.even_ind_rec = '1')
                 AND (pms_evenem0_.even_id_obj = sad_recuei6_.recu_id_recu(+))
                 AND (pms_evenem0_.even_id_obj = pms_acte_r4_.acre_id_rrea(+))
                 AND (pms_evenem0_.even_id_obj = pms_depend5_.rdep_id_rdep(+))
                 AND (sad_diag_s11_.dsai_typ_dia(+) = 'DP')
                 AND (pms_evenem0_.even_id_etab_d = 1059585)
                )
       ORDER BY ide_sejour3_.sejo_num_sej DESC,
                pms_evenem0_.even_dat_eve DESC

**othon_oda** · 27/04/2006, 15h47

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
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 54  (xxx)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY 
      0   SORT UNIQUE 
      0    NESTED LOOPS OUTER 
      1     HASH JOIN OUTER 
      0      HASH JOIN OUTER 
      0       NESTED LOOPS OUTER 
      1        HASH JOIN OUTER 
      0         HASH JOIN OUTER 
      0          NESTED LOOPS OUTER 
      1           HASH JOIN OUTER 
      0            FILTER 
  38487             HASH JOIN OUTER 
  38487              HASH JOIN OUTER 
  38487               HASH JOIN OUTER 
  38487                NESTED LOOPS 
      7                 TABLE ACCESS FULL BAS_TYPE_INFO 
  38487                 TABLE ACCESS BY INDEX ROWID PMS_EVENEMENT 
  39347                  INDEX RANGE SCAN (object id 24437)
     11                TABLE ACCESS FULL BAS_UF 
    129               TABLE ACCESS FULL IDE_MEDECIN 
   3418              TABLE ACCESS FULL IDE_SEJOUR 
      0            TABLE ACCESS FULL PMS_DEPENDANCE 
      0           INDEX UNIQUE SCAN (object id 24675)
      0          TABLE ACCESS FULL PMS_RUM 
      0         TABLE ACCESS FULL PMS_RHS 
      0        INDEX UNIQUE SCAN (object id 24476)
      0       TABLE ACCESS FULL PMS_ACTE_REEDUC 
      0      TABLE ACCESS FULL SAD_DIAG_SAISIS 
      0     INDEX UNIQUE SCAN (object id 24476)
 
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (ORDER BY)
      0    SORT (UNIQUE)
      0     NESTED LOOPS (OUTER)
      1      HASH JOIN (OUTER)
      0       HASH JOIN (OUTER)
      0        HASH JOIN (OUTER)
      1         HASH JOIN (OUTER)
      0          NESTED LOOPS (OUTER)
      0           NESTED LOOPS (OUTER)
      1            HASH JOIN (OUTER)
      0             FILTER
  38487              HASH JOIN (OUTER)
  38487               HASH JOIN (OUTER)
  38487                HASH JOIN (OUTER)
  38487                 HASH JOIN
      7                  TABLE ACCESS   GOAL: ANALYZED 
                             (FULL) OF 'BAS_TYPE_INFO'
  38487                  TABLE ACCESS   GOAL: ANALYZED 
                             (FULL) OF 'PMS_EVENEMENT'
  39347                 INDEX   GOAL: ANALYZED (FULL SCAN) 
                            OF 'BAS_UF_I_UNFO_ID_UNFO_UNFO_COD' (NON-UNIQUE)
     11                TABLE ACCESS   GOAL: ANALYZED (FULL) 
                           OF 'IDE_MEDECIN'
    129               INDEX   GOAL: ANALYZED (FULL SCAN) OF 
                          'I_SEJ_NDV' (NON-UNIQUE)
   3418             TABLE ACCESS   GOAL: ANALYZED (FULL) OF 
                        'PMS_DEPENDANCE'
      0            INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                       'PK_PMS_RHS' (UNIQUE)
      0           INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                      'PK_SAD_RECUEIL' (UNIQUE)
      0          TABLE ACCESS   GOAL: ANALYZED (FULL) OF 
                     'SAD_DIAG_SAISIS'
      0         TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'PMS_RHS'
      0        TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'PMS_RUM'
      0       TABLE ACCESS   GOAL: ANALYZED (FULL) OF 
                  'PMS_ACTE_REEDUC'
      0      INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_PMS_RHS' 
                 (UNIQUE)

**othon_oda** · 27/04/2006, 15h52

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
 
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        1      0.00       0.00       1328     359678          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00       1328     359678          0           0
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 54  (xxx)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY 
      0   SORT UNIQUE 
      0    NESTED LOOPS OUTER 
      1     NESTED LOOPS OUTER 
      1      NESTED LOOPS OUTER 
      1       NESTED LOOPS OUTER 
      1        NESTED LOOPS OUTER 
      1         NESTED LOOPS OUTER 
      1          NESTED LOOPS OUTER 
      1           FILTER 
  38487            NESTED LOOPS OUTER 
  38488             NESTED LOOPS OUTER 
  38488              NESTED LOOPS OUTER 
  38488               NESTED LOOPS OUTER 
  38488                NESTED LOOPS 
  44608                 TABLE ACCESS BY INDEX ROWID PMS_EVENEMENT 
  52867                  INDEX RANGE SCAN (object id 24433)
  83094                 TABLE ACCESS BY INDEX ROWID BAS_TYPE_INFO 
  89214                  INDEX UNIQUE SCAN (object id 24260)
  20107                INDEX UNIQUE SCAN (object id 24675)
   6125               TABLE ACCESS BY INDEX ROWID PMS_DEPENDANCE 
  44612                INDEX UNIQUE SCAN (object id 24407)
   6123              INDEX UNIQUE SCAN (object id 24476)
  32348             TABLE ACCESS BY INDEX ROWID IDE_SEJOUR 
  70835              INDEX UNIQUE SCAN (object id 24369)
      0           TABLE ACCESS BY INDEX ROWID BAS_UF 
      0            INDEX UNIQUE SCAN (object id 24266)
      0          TABLE ACCESS BY INDEX ROWID IDE_MEDECIN 
      0           INDEX UNIQUE SCAN (object id 24301)
      0         TABLE ACCESS BY INDEX ROWID SAD_DIAG_SAISIS 
      0          INDEX RANGE SCAN (object id 24640)
      0        TABLE ACCESS BY INDEX ROWID PMS_RUM 
      0         INDEX RANGE SCAN (object id 24491)
      0       TABLE ACCESS BY INDEX ROWID PMS_RHS 
      0        INDEX RANGE SCAN (object id 24463)
      0      TABLE ACCESS BY INDEX ROWID PMS_ACTE_REEDUC 
      0       INDEX RANGE SCAN (object id 24397)
      0     INDEX UNIQUE SCAN (object id 24476)
 
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (ORDER BY)
      0    SORT (UNIQUE)
      0     NESTED LOOPS (OUTER)
      1      NESTED LOOPS (OUTER)
      1       NESTED LOOPS (OUTER)
      1        NESTED LOOPS (OUTER)
      1         NESTED LOOPS (OUTER)
      1          NESTED LOOPS (OUTER)
      1           NESTED LOOPS (OUTER)
      1            FILTER
  38487             NESTED LOOPS (OUTER)
  38488              NESTED LOOPS (OUTER)
  38488               NESTED LOOPS (OUTER)
  38488                NESTED LOOPS (OUTER)
  38488                 NESTED LOOPS
  44608                  TABLE ACCESS (BY INDEX ROWID) OF 
                             'PMS_EVENEMENT'
  52867                   INDEX   GOAL: ANALYZED (RANGE 
                            SCAN) OF 'I_FK_PMS_EVENEMENT_BAS_ETABLIS' 
                              (NON-UNIQUE)
  83094                  TABLE ACCESS (BY INDEX ROWID) OF 
                             'BAS_TYPE_INFO'
  89214                   INDEX   GOAL: ANALYZED (UNIQUE 
                              SCAN) OF 'PK_BAS_TYPE_INFO' (UNIQUE)
  20107                 INDEX   GOAL: ANALYZED (UNIQUE SCAN) 
                            OF 'PK_SAD_RECUEIL' (UNIQUE)
   6125                TABLE ACCESS (BY INDEX ROWID) OF 
                           'PMS_DEPENDANCE'
  44612                 INDEX   GOAL: ANALYZED (UNIQUE SCAN) 
                            OF 'PK_PMS_DEPENDANCE' (UNIQUE)
   6123               INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                          'PK_PMS_RHS' (UNIQUE)
  32348              TABLE ACCESS (BY INDEX ROWID) OF 
                         'IDE_SEJOUR'
  70835               INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                          'PK_IDE_SEJOUR' (UNIQUE)
      0            TABLE ACCESS (BY INDEX ROWID) OF 'BAS_UF'
      0             INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                        'PK_BAS_UF' (UNIQUE)
      0           TABLE ACCESS (BY INDEX ROWID) OF 'IDE_MEDECIN'
      0            INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                       'PK_IDE_MEDECIN' (UNIQUE)
      0          TABLE ACCESS (BY INDEX ROWID) OF 
                     'SAD_DIAG_SAISIS'
      0           INDEX   GOAL: ANALYZED (RANGE SCAN) OF 
                      'I_FK_SAD_DIAG_SAISIS_SAD_RECUE' (NON-UNIQUE)
      0         TABLE ACCESS (BY INDEX ROWID) OF 'PMS_RUM'
      0          INDEX   GOAL: ANALYZED (RANGE SCAN) OF 
                     'I_FK_PMS_RUM_SAD_RECUEIL' (NON-UNIQUE)
      0        TABLE ACCESS (BY INDEX ROWID) OF 'PMS_RHS'
      0         INDEX   GOAL: ANALYZED (RANGE SCAN) OF 
                    'I_FK_PMS_RHS_SAD_RECUEIL' (NON-UNIQUE)
      0       TABLE ACCESS (BY INDEX ROWID) OF 'PMS_ACTE_REEDUC'
      0        INDEX   GOAL: ANALYZED (RANGE SCAN) OF 
                   'I_FK_PMS_ACTE_REEDUC_PMS_REEDU' (NON-UNIQUE)
      0      INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_PMS_RHS' 
                 (UNIQUE)

**othon_oda** · 27/04/2006, 15h53

La 1ere met 30 sec
La 2eme met 3 sec
Je compute les stats et je repasse a 30 sec.

Les stats seraient elles nefaste....

**orafrance** · 27/04/2006, 15h55

et la commande de calcul de stat ainsi que la version d'Oracle STP. Le tkprof avec stat donne quoi ?

**othon_oda** · 27/04/2006, 16h09

Code :

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

1
2
3
4
5
6
7
8
9
 
BEGIN
SYS.DBMS_STATS.GATHER_SCHEMA_STATS (OwnName => 'xxx'
    ,Degree      => NULL
    ,Cascade     => TRUE
    ,Granularity => 'ALL'
    ,Options     => 'GATHER');
END;
/

**othon_oda** · 27/04/2006, 16h18

La version d'Oracle : Une 8.1.7
et le tkprof avec stats, c'est le premier envoye......

**orafrance** · 27/04/2006, 16h25

c'est l'explain plan mais pas le tkprof complet... manque les temps

essaye ça tout simplement :

Code :

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

1
2
3
dbms_stats.delete_schema_stats('xxx'); 
 
dbms_stats.gather_schema_stats('xxx');

**othon_oda** · 27/04/2006, 16h29

Il manquait le debut

Code :

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

1
2
3
4
5
6
7
8
9
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00       5634      42645        296           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00       5634      42645        296           0
 
Misses in library cache during parse: 1

**othon_oda** · 27/04/2006, 17h05

Apres le compute des stats avec ta commande, ca n'a rien fait de mieux.
Toujours des access full et toujours 30 sec.

**orafrance** · 27/04/2006, 17h08

et si tu fais un ANALYZE ?

**othon_oda** · 28/04/2006, 09h13

avec l'analize, meme punition ;'(

**Wurlitzer** · 28/04/2006, 09h20

Cela rejoint ma discussion

http://www.developpez.net/forums/sho...d.php?t=137440

En CBO Oracle (8i en tout) semble aimer les HASH join qui imlpique des FULL alors qu'en RBO il prefere les Nested Loop qui sont a mon avis plus éfficace.

Mais comment faire pour rester en CBO et aider Oracle a retrouvre le bon chemin ?

(PS : Dans ton cas, tu es en CBO, Sans les stat Orcale se comporte en mode RULE alors qu'avec les stats il essaye de calculer le cout et il se plante)