Discussion :

[farenheiit]

bonjour,

j'aurais voulu savoir quels étaient les paramètres d'une base à regarder en priorité sur une base 10G en cas de problème de perfs ou pour prévenir d'éventuels pb de perf ?

Par exemple, pour la liste des paramètres ci dessous, voyez vous des valeurs qui vous interpellent ?

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
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
 
active_instance_count	
aq_tm_processes	0
archive_lag_target	0
asm_diskgroups	
asm_diskstring	
asm_power_limit	1
audit_file_dest	/bases/oracle/psicpsic/var/audit
audit_syslog_level	
audit_sys_operations	FALSE
audit_trail	TRUE
background_core_dump	partial
background_dump_dest	/bases/oracle/psicpsic/var/bdump
backup_tape_io_slaves	FALSE
bitmap_merge_area_size	1048576
blank_trimming	FALSE
buffer_pool_keep	
buffer_pool_recycle	
circuits	
cluster_database	FALSE
cluster_database_instances	1
cluster_interconnects	
commit_point_strength	1
commit_write	
compatible	10.2.0.3.0
control_file_record_keep_time	7
control_files	/bases/oracle/psicpsic/sys/d01/control01.ctl, /bases/oracle/psicpsic/sys/d02/control02.ctl
core_dump_dest	/bases/oracle/psicpsic/var/cdump
cpu_count	4
create_bitmap_area_size	8388608
create_stored_outlines	
cursor_sharing	EXACT
cursor_space_for_time	FALSE
db_block_buffers	0
db_block_checking	FALSE
db_block_checksum	TRUE
db_block_size	8192
db_cache_advice	ON
db_cache_size	83886080
db_create_file_dest	
db_create_online_log_dest_1	
db_create_online_log_dest_2	
db_create_online_log_dest_3	
db_create_online_log_dest_4	
db_create_online_log_dest_5	
db_domain	
db_file_multiblock_read_count	16
db_file_name_convert	
db_files	200
db_flashback_retention_target	1440
db_keep_cache_size	0
db_name	psicpsic
db_recovery_file_dest	
db_recovery_file_dest_size	0
db_recycle_cache_size	0
db_unique_name	psicpsic
dbwr_io_slaves	0
db_writer_processes	1
db_16k_cache_size	0
db_2k_cache_size	0
db_32k_cache_size	0
db_4k_cache_size	0
db_8k_cache_size	0
ddl_wait_for_locks	FALSE
dg_broker_config_file1	/produits/oracle/ora1023/dbs/dr1psicpsic.dat
dg_broker_config_file2	/produits/oracle/ora1023/dbs/dr2psicpsic.dat
dg_broker_start	FALSE
disk_asynch_io	TRUE
dispatchers	(PROTOCOL=TCP) (SERVICE=psicpsic)
distributed_lock_timeout	60
dml_locks	504
drs_start	FALSE
event	
fal_client	
fal_server	
fast_start_io_target	0
fast_start_mttr_target	0
fast_start_parallel_rollback	LOW
fileio_network_adapters	
file_mapping	FALSE
filesystemio_options	asynch
fixed_date	
gc_files_to_locks	
gcs_server_processes	0
global_context_pool_size	
global_names	FALSE
hash_area_size	131072
hi_shared_memory_address	0
hs_autoregister	TRUE
ifile	
instance_groups	
instance_name	psicpsic
instance_number	0
instance_type	RDBMS
java_max_sessionspace_size	0
java_pool_size	0
java_soft_sessionspace_limit	0
job_queue_processes	10
large_pool_size	0
ldap_directory_access	NONE
license_max_sessions	0
license_max_users	0
license_sessions_warning	0
local_listener	PSICPSIC
lock_name_space	
lock_sga	FALSE
log_archive_config	
log_archive_dest	/bases/oracle/psicpsic/arch
log_archive_dest_state_1	enable
log_archive_dest_state_10	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_1	
log_archive_dest_10	
log_archive_dest_2	
log_archive_dest_3	
log_archive_dest_4	
log_archive_dest_5	
log_archive_dest_6	
log_archive_dest_7	
log_archive_dest_8	
log_archive_dest_9	
log_archive_duplex_dest	
log_archive_format	arch_%r_%t_%s.arc
log_archive_local_first	TRUE
log_archive_max_processes	2
log_archive_min_succeed_dest	1
log_archive_start	FALSE
log_archive_trace	0
log_buffer	14298112
log_checkpoint_interval	0
log_checkpoints_to_alert	FALSE
log_checkpoint_timeout	1800
log_file_name_convert	
logmnr_max_persistent_sessions	1
max_commit_propagation_delay	0
max_dispatchers	
max_dump_file_size	UNLIMITED
max_enabled_roles	150
max_shared_servers	
nls_calendar	
nls_comp	
nls_currency	
nls_date_format	
nls_date_language	
nls_dual_currency	
nls_iso_currency	
nls_language	AMERICAN
nls_length_semantics	BYTE
nls_nchar_conv_excp	FALSE
nls_numeric_characters	
nls_sort	
nls_territory	AMERICA
nls_time_format	
nls_timestamp_format	
nls_timestamp_tz_format	
nls_time_tz_format	
object_cache_max_size_percent	10
object_cache_optimal_size	102400
olap_page_pool_size	0
open_cursors	300
open_links	4
open_links_per_instance	4
optimizer_dynamic_sampling	2
optimizer_features_enable	10.2.0.3
optimizer_index_caching	0
optimizer_index_cost_adj	100
optimizer_mode	CHOOSE
optimizer_secure_view_merging	TRUE
os_authent_prefix	ops$
os_roles	FALSE
O7_DICTIONARY_ACCESSIBILITY	FALSE
parallel_adaptive_multi_user	TRUE
parallel_automatic_tuning	FALSE
parallel_execution_message_size	2152
parallel_instance_group	
parallel_max_servers	8
parallel_min_percent	0
parallel_min_servers	0
parallel_server	FALSE
parallel_server_instances	1
parallel_threads_per_cpu	2
pga_aggregate_target	67108864
plsql_ccflags	
plsql_code_type	INTERPRETED
plsql_compiler_flags	INTERPRETED, NON_DEBUG
plsql_debug	FALSE
plsql_native_library_dir	
plsql_native_library_subdir_count	0
plsql_optimize_level	2
plsql_v2_compatibility	FALSE
plsql_warnings	DISABLE:ALL
pre_page_sga	FALSE
processes	100
query_rewrite_enabled	TRUE
query_rewrite_integrity	enforced
rdbms_server_dn	
read_only_open_delayed	FALSE
recovery_parallelism	0
recyclebin	on
remote_archive_enable	true
remote_dependencies_mode	TIMESTAMP
remote_listener	
remote_login_passwordfile	EXCLUSIVE
remote_os_authent	FALSE
remote_os_roles	FALSE
replication_dependency_tracking	TRUE
resource_limit	FALSE
resource_manager_plan	
resumable_timeout	0
rollback_segments	
serial_reuse	disable
service_names	psicpsic
session_cached_cursors	20
session_max_open_files	10
sessions	115
sga_max_size	1677721600
sga_target	536870912
shadow_core_dump	PARTIAL
shared_memory_address	0
shared_pool_reserved_size	15099494
shared_pool_size	218103808
shared_servers	1
shared_server_sessions	
skip_unusable_indexes	TRUE
smtp_out_server	
sort_area_retained_size	0
sort_area_size	65536
spfile	/bases/oracle/psicpsic/admin/pfile/spfilepsicpsic.ora
sql_trace	FALSE
sqltune_category	DEFAULT
sql_version	NATIVE
sql92_security	FALSE
standby_archive_dest	?/dbs/arch
standby_file_management	MANUAL
star_transformation_enabled	FALSE
statistics_level	TYPICAL
streams_pool_size	33554432
tape_asynch_io	TRUE
thread	0
timed_os_statistics	0
timed_statistics	TRUE
trace_enabled	TRUE
tracefile_identifier	
transactions	126
transactions_per_rollback_segment	5
undo_management	AUTO
undo_retention	900
undo_tablespace	UNDOTBS1
use_indirect_data_buffers	FALSE
user_dump_dest	/bases/oracle/psicpsic/var/udump
utl_file_dir	
workarea_size_policy	AUTO

[orafrance]

la SGA par exemple MAIS :
- pourquoi risquer le diable en touchant les paramètres si t'as pas de problème ?
- si tu as des problèmes de perf, faudra d'abord voir d'où ça vient et si paramètre il faut changer, cette modif coulera de source suite aux observations.

[farenheiit]

Je ne veux pas toucher aux paramètres
j'essaie juste de comprendre quels sont les paramètres critiques

[orafrance]

le mieux c'est encore de voir dans la doc la signification de chacun d'eux

[farenheiit]

T'es devenu un fainéant orafrance

La doc ne remplacera pas ton expérience et ne me dira lesquels peuvent être critiques

[orafrance]

Mais ils sont tous critiques, si je te demande ce qui est critique dans un moteur de voiture tu me réponds quoi ?

[farenheiit]

Ba pas tout justement
si par exemple t'as 2 bougies de préchauffage qui ne marchent plus tu pourras quand même démarrer

[orafrance]

Bon OK, alors les bdump, adump, udump, ne sont pas important... pour le reste, je ne sais pas quoi te dire sans paraphraser la doc

Essaye ça : http://www.dba-oracle.com/t_oracle10...tion_parms.htm

[farenheiit]

C'est mieux que la doc oracle
en fait j'ai une base qui connait des pb de perf lors de l'exécution d'un traitement batch depuis le passage de la 8i vers la 10g.
La durée du traitement est passé de 2h à 30h.
Je me doute qu'il doit y avoir un pb de CBO mais comme pour l'instant l'exploitant ne peut pas relancer le traitement avant la semaine prochaine je voulais voir dans les paramètres s'il n'y avait pas un soucis c'est pour ça que j'ai fourni la liste des paramètres de la base.
C'est une base que je découvre

[orafrance]

Il ne faut préjuger de rien, le problème vient plus probablement du batch que des paramètres. Commence par vérifier les explain plan et faire une trace... changer les paramètres au petit bonheur la chance ça n'a jamais rien donné de bon

[sweeton]

Si ton batch fait essentiellement du full scan, tu peut modifier les paramètres suivant :
- optimizer_mode : ALL_ROWS
- _optimizer_cost_based_transformation : OFF (attention c'est un undocumented parameter mais ça marche plutôt bien sur les problèmes de dégradation de perfs suite au passage en 10G).
- _optimizer_cost_model : io (ça c'est pour le cas ou ta base passe beaucoup de temps en "db file sequential read", si elle passe plus de temps en "CPU Time" ne touche pas à ce paramètre).

Si les requêtes lancée par ton batch sont censées passer par des indexes, modifie les paramètres suivants :
- optimizer_index_caching : 50, 90 voire 100 (il faut essayer plusieurs valeurs ...)
- optimizer_index_cost_adj : 1


Celà dit, commence d'abord par les actions suivantes :
1. Augmenter ton sga_target et ton pga_aggregate_target.
2. Vérifier les stats Oracle et si besoin les recalculer avec un taux d'échantillonage et une méthode histogramme plus complète
3. Calculer les statistisques système (dbms_stats.gather_system_stats)

[orafrance]

alors là c'est assez cavalier comme conseils

Toucher les optimizer_index_* c'est pas très judicieux, vaut mieux calculer les stats systèmes.

Pour le reste... autant mettre le optimizer_features_enable sur 9.2.0.1

[farenheiit]

- _optimizer_cost_based_transformation : OFF (attention c'est un undocumented parameter mais ça marche plutôt bien sur les problèmes de dégradation de perfs suite au passage en 10G).

Pourquoi ce paramètre n'est pas documenté?

Calculer les statistisques système (dbms_stats.gather_system_stats)

c'est quoi le principe?

[orafrance]

Pourquoi ce paramètre n'est pas documenté?

Ca fait partie des paramètres qui ne doivent, en principe, être modifié que sur recommandation du support

c'est quoi le principe?

Tu démarres l'observation de la base pendant une activité normale et après quelques minutes tu l'arrêtes. Sur l'intervalle, Oracle collecte des infos sur le CPU, les IO, etc... et stocke ça dans la base. Ca lui permet ensuite de privilégier tel ou tel composant selon la charge (si le disque des datas est plus rapide que les indexes il fera plus de FTS par exemple).

[sweeton]

Il faudrait que vous m'expliquiez en quoi les statistiques systèmes (que j'ai d'ailleurs proposé de calculer avant de faire quoi que ce soit) remplacent le positionnement des paramètre optimizer_index_* ???
A ma connaissance (mais je peux me tromper) les statistiques systèmes influent sur les plans d'exécution en comparant le coût (temps accès disque/Temps CPU).
Par contre, forcer optimizer_features_enable à 9.2.0 invalidera à coup sur les paramètre optimizer_index_*, ce qui n'est pas le cas du _optimizer_cost_based_transformation.

[orafrance]

pour info : http://www.dba-oracle.com/t_dbms_sta...stem_stats.htm

De toute façon, il faut être très prudent avec les paramètres cachés et c'est trop dangereux d'en conseiller certains sans une vue détaillée de la base

[orafrance]

Et très intéressant à propos des optimizer_index_* : http://asktom.oracle.com/pls/asktom/...61100346113797

Ca oriente le CBO de la base dans sa totalité alors que la chasse à tout prix au FTS n'a jamais rien donné de bon

[sweeton]

S'il y a du Don Burleson là dessous ... je m'incline.
Celà dit :

As of 10g, the use of dbms_stats.gather_system_stats and improved sampling within dbms_stats had made adjustments to these parameters far less important.

C'est bien pour ça que je conseille de d'abord calculer les statistiques systèmes ainsi que d'effectuer un échantillonage des stats plus complet avant de toucher aux paramètre optimizer_index_*

Enfin le calcul des statistiques ne fait pas tout ... Les cas classiques de dégradation des perfs suite à la migration vers la 10G se résolvent assez rarement juste en calculant les stats, surtout si il y a eu un travail de tuning effectué en 8 ou 9i. J'ai même vu pas mal de cas où un calcul de stats complet aggravait la situation.

[orafrance]

S'il y a du Don Burleson là dessous ... je m'incline.

surtout du Tom Kyte en fait

Enfin le calcul des statistiques ne fait pas tout ... Les cas classiques de dégradation des perfs suite à la migration vers la 10G se résolvent assez rarement juste en calculant les stats, surtout si il y a eu un travail de tuning effectué en 8 ou 9i. J'ai même vu pas mal de cas où un calcul de stats complet aggravait la situation.

Je suis bien d'accord... mais comme tout dans la base, une action qui modifie le CBO est dangereuse en général... les stats tout autant que les paramètres

En général, on s'attache à améliorer les requêtes qui posent problèmes plutôt que de changer le comportement du CBO surtout quand c'est pour revenir à des fonctionnalités de la 9i. En cela, les conseils de la console peuvent être utiles... pas pour les suivre à l'aveugle mais au moins pour détecter les soucis.

[sweeton]

C'est sur, quand les requêtes posant problèmes sont clairement identifiées il vaut mieux les optimiser une par une. Par contre quand c'est une perte de perfs globales mettant en jeu un nombre important de requêtes différentes il faut bien agir de manière plus globales et donc de toucher aux paramètres influençant l'optimizer
Enfin, d'une manière générale ça coûte toujours beaucoup moins cher de modifier un paramètre de l'instance ou de créer un outline que de modifier le code-source pour optimiser la requête.