Analyse des paramètres d'une base

**farenheiit** · 26/03/2009, 11h55

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** · 26/03/2009, 12h17

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.