Discussion :

[theclear]

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.