Problème de performances

**theclear** · 09/04/2014, 14h45

Bonjour à tous,

J'ai un problème sur une base de données ORACLE 11g, les performances ont chuté d'un coup sans comprendre pourquoi.
Je sais qu'on en peut pas s'improviser DBA Oracle et que les paramètres doivent être réglés en fonction du type de données et requêtes passées mais je suis un peu dans l'urgence et faire appel à ce forum est pour l'instant ma seule solution.

Pourriez vous me dire si l'un des paramètres de la base de données pourrait être à l'origine de ces problèmes ou m'orienter vers d'autres pistes de recherche?
J'ai suivi une session DBA Oracle I mais sans la session de Tuning j'ai l'impression qu'il m'est impossible d'optimiser ces performances seul...
Je n'ai récupéré que les paramètres différents de null.

processes 300
sessions 472
timed_statistics TRUE
timed_os_statistics 0
resource_limit FALSE
license_max_sessions 0
license_sessions_warning 0
cpu_count 4
sga_max_size 3238002688
pre_page_sga FALSE
shared_memory_address 0
hi_shared_memory_address 0
use_indirect_data_buffers FALSE
lock_sga FALSE
shared_pool_size 0
large_pool_size 0
java_pool_size 0
streams_pool_size 0
shared_pool_reserved_size 20971520
java_soft_sessionspace_limit 0
java_max_sessionspace_size 0
instance_type RDBMS
nls_language FRENCH
nls_territory FRANCE
nls_comp BINARY
nls_length_semantics BYTE
nls_nchar_conv_excp FALSE
disk_asynch_io TRUE
tape_asynch_io TRUE
dbwr_io_slaves 0
backup_tape_io_slaves FALSE
resource_manager_cpu_allocation 4
file_mapping FALSE
gcs_server_processes 0
sga_target 0
memory_target 3238002688
memory_max_target 3238002688
control_files D:\APP\ORADATA\CONTROL01.CTL, D:\APP\ORADATA\CONTROL02.CTL
control_file_record_keep_time 7
db_block_buffers 0
db_block_checksum TYPICAL
db_ultra_safe OFF
db_block_size 8192
db_cache_size 0
db_2k_cache_size 0
db_4k_cache_size 0
db_8k_cache_size 0
db_16k_cache_size 0
db_32k_cache_size 0
db_keep_cache_size 0
db_recycle_cache_size 0
db_writer_processes 1
db_flash_cache_size 0
db_cache_advice ON
compatible 11.2.0.0.0
log_archive_dest_state_1 enable
log_archive_dest_state_2 enable
log_archive_dest_state_3 enable
log_archive_dest_state_4 enable
log_archive_dest_state_5 enable
log_archive_dest_state_6 enable
log_archive_dest_state_7 enable
log_archive_dest_state_8 enable
log_archive_dest_state_9 enable
log_archive_dest_state_10 enable
log_archive_dest_state_11 enable
log_archive_dest_state_12 enable
log_archive_dest_state_13 enable
log_archive_dest_state_14 enable
log_archive_dest_state_15 enable
log_archive_dest_state_16 enable
log_archive_dest_state_17 enable
log_archive_dest_state_18 enable
log_archive_dest_state_19 enable
log_archive_dest_state_20 enable
log_archive_dest_state_21 enable
log_archive_dest_state_22 enable
log_archive_dest_state_23 enable
log_archive_dest_state_24 enable
log_archive_dest_state_25 enable
log_archive_dest_state_26 enable
log_archive_dest_state_27 enable
log_archive_dest_state_28 enable
log_archive_dest_state_29 enable
log_archive_dest_state_30 enable
log_archive_dest_state_31 enable
log_archive_start FALSE
log_archive_min_succeed_dest 1
standby_archive_dest %ORACLE_HOME%\RDBMS
log_archive_trace 0
log_archive_local_first TRUE
log_archive_format ARC%S_%R.%T
log_archive_max_processes 4
log_buffer 16285696
log_checkpoint_interval 0
log_checkpoint_timeout 1800
archive_lag_target 0
db_files 200
db_file_multiblock_read_count 128
read_only_open_delayed FALSE
cluster_database FALSE
parallel_server FALSE
parallel_server_instances 1
cluster_database_instances 1
db_recovery_file_dest_size 0
standby_file_management MANUAL
thread 0
fast_start_io_target 0
fast_start_mttr_target 0
log_checkpoints_to_alert FALSE
db_lost_write_protect NONE
recovery_parallelism 0
db_flashback_retention_target 1440
dml_locks 2076
replication_dependency_tracking TRUE
transactions 519
transactions_per_rollback_segment 5
undo_management AUTO
undo_tablespace UNDOTBS
undo_retention 900
fast_start_parallel_rollback LOW
resumable_timeout 0
cell_partition_large_extents DEFAULT
instance_number 0
db_block_checking FALSE
recyclebin on
db_securefile PERMITTED
serial_reuse disable
ldap_directory_access NONE
ldap_directory_sysauth no
os_roles FALSE
max_enabled_roles 150
remote_os_authent FALSE
remote_os_roles FALSE
sec_case_sensitive_logon TRUE
O7_DICTIONARY_ACCESSIBILITY FALSE
remote_login_passwordfile EXCLUSIVE
license_max_users 0
audit_sys_operations FALSE
global_names FALSE
distributed_lock_timeout 60
commit_point_strength 1
global_txn_processes 1
instance_name orcl
service_names orcl
dispatchers (PROTOCOL=TCP) (SERVICE=orclXDB)
shared_servers 1
cursor_space_for_time FALSE
session_cached_cursors 50
remote_dependencies_mode TIMESTAMP
plsql_v2_compatibility FALSE
plsql_warnings DISABLE:ALL
plsql_code_type INTERPRETED
plsql_debug TRUE
plsql_optimize_level 0
plscope_settings identifiers:all
permit_92_wrap_format TRUE
java_jit_enabled TRUE
job_queue_processes 1000
parallel_min_percent 0
create_bitmap_area_size 8388608
bitmap_merge_area_size 1048576
cursor_sharing EXACT
result_cache_mode MANUAL
parallel_min_servers 0
parallel_max_servers 0
parallel_execution_message_size 16384
hash_area_size 131072
result_cache_max_size 0
result_cache_max_result 5
result_cache_remote_expiration 0
audit_file_dest D:\APP\ADMIN\ORCL\ADUMP
shadow_core_dump none
background_core_dump partial
background_dump_dest d:\app\diag\rdbms\ORCL\ORCL\trace
user_dump_dest d:\app\diag\rdbms\ORCL\ORCL\trace
core_dump_dest d:\app\diag\rdbms\ORCL\ORCL\cdump
object_cache_optimal_size 102400
object_cache_max_size_percent 10
session_max_open_files 10
open_links 4
open_links_per_instance 4
optimizer_features_enable 11.2.0.1
audit_trail DB
sort_area_size 65536
sort_area_retained_size 0
cell_offload_processing TRUE
cell_offload_decryption TRUE
cell_offload_compaction ADAPTIVE
cell_offload_plan_display AUTO
db_name orcl
db_unique_name orcl
open_cursors 300
sql_trace FALSE
os_authent_prefix OPS$
optimizer_mode ALL_ROWS
sql92_security FALSE
blank_trimming FALSE
star_transformation_enabled FALSE
parallel_degree_policy MANUAL
parallel_adaptive_multi_user TRUE
parallel_threads_per_cpu 2
parallel_automatic_tuning FALSE
parallel_io_cap_enabled FALSE
optimizer_index_cost_adj 100
optimizer_index_caching 0
query_rewrite_enabled TRUE
query_rewrite_integrity enforced
pga_aggregate_target 0
workarea_size_policy AUTO
optimizer_dynamic_sampling 2
statistics_level TYPICAL
skip_unusable_indexes TRUE
optimizer_secure_view_merging TRUE
ddl_lock_timeout 0
deferred_segment_creation TRUE
optimizer_use_pending_statistics FALSE
optimizer_capture_sql_plan_baselines FALSE
optimizer_use_sql_plan_baselines TRUE
parallel_min_time_threshold AUTO
parallel_degree_limit CPU
parallel_force_local FALSE
optimizer_use_invisible_indexes FALSE
dst_upgrade_insert_conv TRUE
parallel_servers_target 16
sec_protocol_error_trace_action TRACE
sec_protocol_error_further_action CONTINUE
sec_max_failed_login_attempts 10
sec_return_server_release_banner FALSE
enable_ddl_logging FALSE
client_result_cache_size 0
client_result_cache_lag 3000
aq_tm_processes 0
hs_autoregister TRUE
xml_db_events enable
dg_broker_start FALSE
dg_broker_config_file1 D:\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\DR1ORCL.DAT
dg_broker_config_file2 D:\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\DR2ORCL.DAT
olap_page_pool_size 0
asm_power_limit 1
control_management_pack_access NONE
sqltune_category DEFAULT
diagnostic_dest D:\APP
max_dump_file_size unlimited
trace_enabled TRUE

Merci par avance.

**mnitu** · 09/04/2014, 15h34

Ni dieu dans sa grande sagesse, ne peut optimiser une base des données en regardant simplement les paramètres de la base!
Tracer une session qui rencontre des difficultés (trace sql étendue) ou utilisez statspack/awr(si vous avez la licence) pour collecter les informations nécessaires à la compréhension de votre problème.

**theclear** · 09/04/2014, 17h12

Je me doutais de cette réponse

, je voulais être sûr qu'il n'y avais pas quelques chose d'aberrant dans la configuration à corriger.

Merci pour les conseils je vais essayer. Mais il me semble avoir déjà trouvé une piste avec le plan d'exécution qui n'utilise pas l'index essentiel sur une table de 500 millions d'enregistrement.

**theclear** · 09/04/2014, 18h20

Je pense avoir identifié la raison des lenteurs mais j'avoue ne pas comprendre le comportement sur ce serveur car il est spécifique à ce serveur: Les mêmes requêtes utilisent un index sur un autre serveur ou j'ai récupéré le même schéma et où les temps de réponse sont satisfaisants.
Ma requête:

Code :

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

1
2
3
select * from mesure_ana m
    inner join vue_varpluvio_temps v on m.id_variable(+)=v.id  AND m.datem(+) >= v.date_curr  AND m.datem(+) < date_curr + 1
    where v.annee=2013 and v.mois=1 and v.id=m.id_variable

La table mesure [id_variable, date, ...] possède un index unique sur les colonnes id_variable et date. Celui ci est utilisé sur le serveur avec les bons temps de réponse mais "ignoré" sur le serveur qui pose problème!

Une idée pour que je puisse avancer sur ce problème?

J'ai ajouté 2 images reprenant le plan d'exécution : l'un utilisant l'index (serveur 1), l'autre l'ignorant (serveur 2)

**pachot** · 09/04/2014, 20h04

Bonjour,
les stats objet sont elles à jour ?
Une requête qui compare avec la date courante va faire de moins bonnes estimations si les stats (valeurs min et valeur max de la colonne) ne reflètent plsu la réalité.
Cordialement,
Franck.

**mnitu** · 10/04/2014, 09h06

Votre requête est un coup de maître d'un esprit maléfique qui en veut à l'optimiseur d'Oracle. Je ne jamais vue un si jolie mélange d'écriture AINSI et spécifique Oracle pour une jointure externe (?)

Code :

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

1
2
3
4
5
 INNER JOIN        vue_varpluvio_temps v 
    ON m.id_variable(+)=v.id  
       AND m.datem(+) >= v.date_curr  
       AND m.datem(+) < date_curr + 1

Remarquez la subtile combinaison du INNER JOIN avec les (+) ainsi que la condition de jointure normale dans la clause where

Code :

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

1
2
3
4
 WHERE v.annee=2013 
   AND v.mois=1 
   AND v.id=m.id_variable

Première chose à faire réécrivez cette requête d'une manière décente soit en écriture AINSI soit en écriture spécifique d'Oracle me ne le mélangez plus de cette manière.

**theclear** · 10/04/2014, 16h03

Oui désolé pour le mélange c'est une erreur d'inattention de ma part pendant mes tests.

La piste donnée sur les stats me semble tout à fait judicieuse. Ca doit être la raison de la non utilisation de l'index. Je vais faire une petite recherche dans ce sens. Merci beaucoup pour ce conseil et les autres aussi

**theclear** · 10/04/2014, 17h21

J'ai relancé les stats sur la table et l'index à utiliser mais ce n'est pas probant. J'ai également reconstruit l'index en question.

Le plan d'exécution a changé, l'index est maintenant bien utilisé mais j'ai toujours 2 minutes d'exécutions alors que sur l'autre serveur j'ai moins d'une seconde...

D'autre idées à me suggérer ou besoin d'autres informations?

Voici le nouveau plan d'exécution
Nom : 2014-04-09_3.jpg
Affichages : 586
Taille : 37,3 Ko

Nom : 2014-04-09_3.jpg
Affichages : 586
Taille : 37,3 Ko

Merci encore

**pachot** · 10/04/2014, 21h15

Bonjour,
J'ai décidé récemment de ne plus lire les plans d'exécution au format jpg

donc j'ai posté ici comment faire un plan propre avec toutes les infos nécessaires: http://www.dbi-services.com/index.ph...execution-plan

Code :

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

1
2
set pagesize 10000 linesize 300 trimspool on serveroutput off
alter session set statistics_level=all;

Code :

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

1
2
3
spool plan.txt
select * from table(dbms_xplan.display_cursor(format=>'allstats last +outline +peeked_binds'));
spool off

Cordialement,
Franck.

**theclear** · 11/04/2014, 14h30

Voici le plan d'exécution. Je n'ai pas utilisé votre technique car ej trouve le rendu moins lisible. Dites moi si vous préférez que je vous envoie celui ci.
Requête traitée:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
  SELECT v.annee,
    v.mois,
    v.date_curr,
   sum(m.valeur)
  FROM vue_varpluvio_temps v left join
    mesure_ana m on m.id_variable=v.id
  AND datem       >=v.date_curr
  AND datem        <date_curr+1
  WHERE mois=1 and annee=2013  
  GROUP BY v.annee,
    v.mois,
    v.date_curr

Plan d'exécution utilisant anormalement FULL ACCESS sur mesure_ana:

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
OPERATION   OBJECT_NAME   OPTIONS   COST  
 
 
  SELECT STATEMENT  
          619235  
 
<div style="margin-left:40px">
  HASH  
      GROUP BY   619235  
 
 
  HASH JOIN  
      OUTER   618958  
 
 
  VIEW  
  VUE_VARPLUVIO_TEMPS       178  
 
 
  MERGE JOIN  
      CARTESIAN   178  
 
 
  VIEW  
  VUE_JOUR_RES_DEV       103  
 
 
  HASH  
      UNIQUE   103  
 
 
  INDEX  
  PK_RES_DEV   FAST FULL SCAN   102  
 
 
  BUFFER  
      SORT   178  
 
 
  INDEX  
  PK_VARPLUVIO   FAST FULL SCAN   0  
 
 
  TABLE ACCESS  
  MESURE_ANA   FULL   607587</div>

**mnitu** · 11/04/2014, 16h29

C'est illisible comme plan d'exécution et ça ne montres pas les informations demandées.
Essayez de suivre le démarche indiqué sur le lien ou dans des autres fils de discussion concernant les problèmes de performance sur ce site.

**theclear** · 11/04/2014, 18h10

Voici le plan que j'avais obtenu sachant que la requête est ici avec les jointures dans le where et non en inner join

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
 
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last +outline +peeked_binds'));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bds1ryumrcjxp, child number 1
-------------------------------------
SELECT v.annee,     v.mois,     v.date_curr,
NVL(SUM(m.valeur)/COUNT(DISTINCT v.id),0) lame_moyenne   FROM
vue_varpluvio_temps v,     mesure_ana m   WHERE   m.id_variable(+)=v.id
  AND datem(+)       >=v.date_curr   AND datem(+)        <date_curr+1
and mois=1 and annee=2014   GROUP BY v.annee,     v.mois,
v.date_curr
 
Plan hash value: 3035705700
 
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |      1 |        |     31 |00:03:53.75 |    2206K|   2206K|       |       |          |
|   1 |  HASH GROUP BY               |                     |      1 |     12M|     31 |00:03:53.75 |    2206K|   2206K|   846K|   846K| 1333K (0)|
|   2 |   VIEW                       | VW_DAG_0            |      1 |     12M|    930 |00:03:53.75 |    2206K|   2206K|       |       |          |
|   3 |    HASH GROUP BY             |                     |      1 |     12M|    930 |00:03:53.75 |    2206K|   2206K|   855K|   855K| 1317K (0)|
|*  4 |     HASH JOIN OUTER          |                     |      1 |     12M|  36901 |00:03:53.73 |    2206K|   2206K|  1011K|  1011K| 1296K (0)|
|   5 |      VIEW                    | VUE_VARPLUVIO_TEMPS |      1 |  25110 |    930 |00:00:00.03 |     279 |      0 |       |       |          |
|   6 |       MERGE JOIN CARTESIAN   |                     |      1 |  25110 |    930 |00:00:00.03 |     279 |      0 |       |       |          |
|   7 |        VIEW                  | VUE_JOUR_RES_DEV    |      1 |    837 |     31 |00:00:00.03 |     275 |      0 |       |       |          |
|   8 |         HASH UNIQUE          |                     |      1 |    837 |     31 |00:00:00.03 |     275 |      0 |  1064K|  1064K| 1327K (0)|
|*  9 |          INDEX FAST FULL SCAN| PK_RES_DEV          |      1 |    837 |    341 |00:00:00.03 |     275 |      0 |       |       |          |
|  10 |        BUFFER SORT           |                     |     31 |     30 |    930 |00:00:00.01 |       4 |      0 |  2048 |  2048 | 2048  (0)|
|  11 |         INDEX FAST FULL SCAN | PK_VARPLUVIO        |      1 |     30 |     30 |00:00:00.01 |       4 |      0 |       |       |          |
|  12 |      TABLE ACCESS FULL       | MESURE_ANA          |      1 |    565M|    566M|00:01:02.30 |    2206K|   2206K|       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$5771D262")
      TRANSFORM_DISTINCT_AGG(@"SEL$1")
      OUTLINE_LEAF(@"SEL$C33C846D")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$5771D262")
      TRANSFORM_DISTINCT_AGG(@"SEL$1")
      NO_ACCESS(@"SEL$C33C846D" "VW_DAG_0"@"SEL$C33C846D")
      USE_HASH_AGGREGATION(@"SEL$C33C846D")
      NO_ACCESS(@"SEL$5771D262" "V"@"SEL$1")
      FULL(@"SEL$5771D262" "M"@"SEL$1")
      LEADING(@"SEL$5771D262" "V"@"SEL$1" "M"@"SEL$1")
      USE_HASH(@"SEL$5771D262" "M"@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$5771D262")
      NO_ACCESS(@"SEL$2" "R"@"SEL$2")
      INDEX_FFS(@"SEL$2" "V"@"SEL$2" ("VAR_PLUVIO"."ID"))
      LEADING(@"SEL$2" "R"@"SEL$2" "V"@"SEL$2")
      USE_MERGE_CARTESIAN(@"SEL$2" "V"@"SEL$2")
      INDEX_FFS(@"SEL$3" "R"@"SEL$3" ("RES_DEV"."ID_DO" "RES_DEV"."JOUR"))
      USE_HASH_AGGREGATION(@"SEL$3")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("M"."ID_VARIABLE"="V"."ID")
       filter(("DATEM">="V"."DATE_CURR" AND "DATEM"<INTERNAL_FUNCTION("V"."DATE_CURR")+1))
   9 - filter((TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("R"."JOUR"),'MM'))=1 AND
              TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("R"."JOUR"),'YYYY'))=2014))
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
 
74 ligne(s) sélectionnée(s).
 
SQL> spool off

**pachot** · 11/04/2014, 21h02

Bonjour,
C'est donc une jointure entre 500 million de lignes de MESURE_ANA et 1000 lignes de VUE_VARPLUVIO_TEMPS. Aucune methode de jointure n'est vraiment optimale: le full scan de 500 million de lignes est long, mais un accès par index à faire 1000 fois est long aussi. C'est quand même étonnant que l'accès par index ne soit plus choisi.

Il y a bien un index qui commence par "M"."ID_VARIABLE" ? Il faudrait voir cet index, il peut se faire qu'il soit moins attrayant pour l'optimiseur.

Si oui, est-ce que tu peux faire la même chose pour avoir le plan d'exécution en rajoutant les hints LEADING(V M) USE_NL(M) INDEX(M (ID_VARIABLE)) pour forcer le plan à utiliser cet index:

SELECT /*+ LEADING(V M) USE_NL(M) INDEX(M (ID_VARIABLE) */ v.annee, v.mois, v.date_curr,NVL(SUM(m.valeur)/COUNT(DISTINCT v.id),0) lame_moyenne FROM
vue_varpluvio_temps v, mesure_ana m WHERE m.id_variable(+)=v.id
AND datem(+) >=v.date_curr AND datem(+) <date_curr+1
AND mois=1 AND annee=2014 GROUP BY v.annee, v.mois,
v.date_curr

Le but est de comprendre pourquoi cet index n'est pas choisi.

Cordialement,
Franck.

**Waldar** · 14/04/2014, 11h52

Pouvez-vous également publier le code de la vue et le code de création de l'index ?

**theclear** · 14/04/2014, 12h01

Bonjour,

En forçant l'utilisation de l'index, la requête a un temps de réponse nettement plus satisfaisant:

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
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID  6fm8n9p8ah8ds, child number 1
-------------------------------------
SELECT /*+ LEADING(V M) USE_NL(M) INDEX(M (ID_VARIABLE) */ v.annee,
v.mois, v.date_curr,NVL(SUM(m.valeur)/COUNT(DISTINCT v.id),0)
lame_moyenne FROM vue_varpluvio_temps v, mesure_ana m WHERE
m.id_variable(+)=v.id AND datem(+) >=v.date_curr AND datem(+)
<date_curr+1 AND mois=1 AND annee=2014 GROUP BY v.annee, v.mois,
v.date_curr
 
Plan hash value: 2054700827
 
--------------------------------------------------------------------------------
--------------------------------------------------------------
| Id  | Operation                       | Name                  | Starts | E-Row
s | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------
--------------------------------------------------------------
|   0 | SELECT STATEMENT                |                       |      1 |
  |     31 |00:00:00.10 |    5089 |       |       |          |
|   1 |  HASH GROUP BY                  |                       |      1 |     1
2M|     31 |00:00:00.10 |    5089 |   846K|   846K|   14M (0)|
|   2 |   VIEW                          | VW_DAG_0              |      1 |     1
2M|    930 |00:00:00.10 |    5089 |       |       |          |
|   3 |    HASH GROUP BY                |                       |      1 |     1
2M|    930 |00:00:00.10 |    5089 |   855K|   855K|   49M (0)|
|   4 |     NESTED LOOPS OUTER          |                       |      1 |     1
2M|  36901 |00:00:00.06 |    5089 |       |       |          |
|   5 |      VIEW                       | VUE_VARPLUVIO_TEMPS   |      1 |  2511
0 |    930 |00:00:00.03 |     279 |       |       |          |
|   6 |       MERGE JOIN CARTESIAN      |                       |      1 |  2511
0 |    930 |00:00:00.03 |     279 |       |       |          |
|   7 |        VIEW                     | VUE_JOUR_RES_DEV      |      1 |    83
7 |     31 |00:00:00.03 |     275 |       |       |          |
|   8 |         HASH UNIQUE             |                       |      1 |    83
7 |     31 |00:00:00.03 |     275 |  1064K|  1064K| 1299K (0)|
|*  9 |          INDEX FAST FULL SCAN   | PK_RES_DEV            |      1 |    83
7 |    341 |00:00:00.03 |     275 |       |       |          |
|  10 |        BUFFER SORT              |                       |     31 |     3
0 |    930 |00:00:00.01 |       4 |  2048 |  2048 | 2048  (0)|
|  11 |         INDEX FAST FULL SCAN    | PK_VARPLUVIO          |      1 |     3
0 |     30 |00:00:00.01 |       4 |       |       |          |
|  12 |      TABLE ACCESS BY INDEX ROWID| MESURE_ANA            |    930 |    49
3 |  36827 |00:00:00.03 |    4810 |       |       |          |
|* 13 |       INDEX RANGE SCAN          | IU_MESUREANA_ID_DATEM |    930 |   985
2 |  36827 |00:00:00.01 |    2905 |       |       |          |
--------------------------------------------------------------------------------
--------------------------------------------------------------
 
Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$5771D262")
      TRANSFORM_DISTINCT_AGG(@"SEL$1")
      OUTLINE_LEAF(@"SEL$C33C846D")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$5771D262")
      TRANSFORM_DISTINCT_AGG(@"SEL$1")
      NO_ACCESS(@"SEL$C33C846D" "VW_DAG_0"@"SEL$C33C846D")
      USE_HASH_AGGREGATION(@"SEL$C33C846D")
      NO_ACCESS(@"SEL$5771D262" "V"@"SEL$1")
      INDEX_RS_ASC(@"SEL$5771D262" "M"@"SEL$1" ("MESURE_ANA"."ID_VARIABLE" "MESU
RE_ANA"."DATEM"))
      LEADING(@"SEL$5771D262" "V"@"SEL$1" "M"@"SEL$1")
      USE_NL(@"SEL$5771D262" "M"@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$5771D262")
      NO_ACCESS(@"SEL$2" "R"@"SEL$2")
      INDEX_FFS(@"SEL$2" "V"@"SEL$2" ("VAR_PLUVIO"."ID"))
      LEADING(@"SEL$2" "R"@"SEL$2" "V"@"SEL$2")
      USE_MERGE_CARTESIAN(@"SEL$2" "V"@"SEL$2")
      INDEX_FFS(@"SEL$3" "R"@"SEL$3" ("RES_DEV"."ID_DO" "RES_DEV"."JOUR"))
      USE_HASH_AGGREGATION(@"SEL$3")
      END_OUTLINE_DATA
  */
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   9 - filter((TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("R"."JOUR"),'MM'))=1 AND
              TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("R"."JOUR"),'YYYY'))=2014))
  13 - access("M"."ID_VARIABLE"="V"."ID" AND "DATEM">="V"."DATE_CURR" AND "DATEM
"<INTERNAL_FUNCTION("V"."DATE_CURR")+1)
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 
 
74 ligne(s) sÚlectionnÚe(s).

**theclear** · 14/04/2014, 12h08

Il faut savoir que l'utilisateur qui lance la requête posant problème est BA et que j'utilise des synonymes pour attaquer le schéma EVE sur la même instance ORACLE.
Voici le codes des index de la table mesure_ana

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
  CREATE UNIQUE INDEX "EVE"."IU_MESUREANA_ID_DATEM" ON "EVE"."MESURE_ANA" ("ID_VARIABLE", "DATEM") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 2147483648 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "EVE_INDEX" ;
 
  CREATE INDEX "EVE"."I_MESUREANA_LABEL" ON "EVE"."MESURE_ANA" ("LABELM") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "EVE_INDEX" ;

**pachot** · 14/04/2014, 14h11

Dans l'opération suivante:

    |*  9 |          INDEX FAST FULL SCAN   | PK_RES_DEV            |      1 |    837 |    341 |

le nombre de lignes est surestimé (837 au lieu de 314)
Il y a une vue qui transforme une date et qui aboutit au prédicat suivante:

Code :

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

1
2
((TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("R"."JOUR"),'MM'))=1 
AND TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("R"."JOUR"),'YYYY'))=2014))

toutes ces conversions faussent les estimations de l'optimiseur.

A cause de cela (et de toutes ces vues imbriquées) l'optimiseur pense avoir 25110 nested loop à faire et du coup préfère faire un full table scan.

L'idéal serait de revoir ces vues. Ce n'est pas optimal de faire toutes ces opérations d'extraction de mois, jour année, et toutes ces conversions date - char - number alors que le but est simplement de comparer des dates.

**theclear** · 14/04/2014, 15h12

Bien vu, en mettant "and datem between '01/01/2013' and '01/02/2013'" au lieu de mois=1 and annee=2013, ca fonctionne beaucoup plus vite...

Je vais voir si c'est viable à long terme.

Merci beaucoup pour la longue recherche!

A bientot

**skuatamad** · 14/04/2014, 15h54

J'espère que vous l'avez écrit correctement avec une convertion explicite, sinon même à court terme ça n'est pas viable.
Par ailleurs pour sélectionner les données d'un mois on utilise plutôt :

Code :

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

datem >= to_date('01/01/2013','dd/mm/yyyy') and datem < to_date('01/02/2013','dd/mm/yyyy')

**theclear** · 14/04/2014, 16h11

Envoyé par skuatamad

J'espère que vous l'avez écrit correctement avec une convertion explicite, sinon même à court terme ça n'est pas viable.
Par ailleurs pour sélectionner les données d'un mois on utilise plutôt :

Code :

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

datem >= to_date('01/01/2013','dd/mm/yyyy') and datem < to_date('01/02/2013','dd/mm/yyyy')

Oui c'est ce que j'utilise en général là c'était pour le test

Problème de performances [11gR2]

Administration Oracle

Discussions similaires

Partager

Partager