開(kāi)灤項(xiàng)目性能調(diào)優(yōu)總結(jié)

1、開(kāi)灤項(xiàng)目性能調(diào)優(yōu)總結(jié)調(diào)整前數(shù)據(jù)庫(kù)服務(wù)器telnet 至數(shù)據(jù)庫(kù)服務(wù)器查看資源占用（2臺(tái)數(shù)據(jù)庫(kù)集群）通過(guò)資源占用可以看出：當(dāng)前數(shù)據(jù)庫(kù)服務(wù)器的CPU（16顆）有瞬時(shí)的等待，整體在80%區(qū)間內(nèi)，運(yùn)行正常。頁(yè)交換方面是0，沒(méi)有頁(yè)交換出現(xiàn)。內(nèi)存方面，2這臺(tái)數(shù)據(jù)庫(kù)服務(wù)器可用內(nèi)存為：1.9G左右，10.20.10.11這臺(tái)在0.8G左右。Oracle當(dāng)前參數(shù)配置SQL show parameterNAME TYPE VALUE O7_DICTIONARY_ACCESSIBILITY boolean FALSEactive_instance_count integer aq_tm_processes integ

2、er 0archive_lag_target integer 0asm_diskgroups string asm_diskstring string asm_power_limit integer 1audit_file_dest string /oracle/app/oracle/admin/KLWL/adumpaudit_sys_operations boolean FALSEaudit_syslog_level string audit_trail string NONEbackground_core_dump string partialbackground_dump_dest st

3、ring /oracle/app/oracle/admin/KLWL/bdumpbackup_tape_io_slaves boolean FALSEbitmap_merge_area_size integer 1048576blank_trimming boolean FALSEbuffer_pool_keep string buffer_pool_recycle string circuits integer cluster_database boolean TRUEcluster_database_instances integer 2cluster_interconnects stri

4、ng commit_point_strength integer 1commit_write string control_file_record_keep_time integer 7control_files string +DATAGRP/klwl/control01.ctl, +DATAGRP/klwl/control02.ctl, +DATAGRP/klwl/control03.ctlcore_dump_dest string /oracle/app/oracle/admin/KLWL/cdumpcpu_count integer 16create_bitmap_area_size

5、integer 8388608create_stored_outlines string cursor_sharing string EXACTcursor_space_for_time boolean FALSEdb_16k_cache_size big integer 0db_2k_cache_size big integer 0db_32k_cache_size big integer 0db_4k_cache_size big integer 0db_8k_cache_size big integer 0db_block_buffers integer 0db_block_checki

6、ng string FALSEdb_block_checksum string TRUEdb_block_size integer 8192db_cache_advice string ONdb_cache_size big integer 0db_create_file_dest string db_create_online_log_dest_1 string db_create_online_log_dest_2 string db_create_online_log_dest_3 string db_create_online_log_dest_4 string db_create_o

7、nline_log_dest_5 string db_domain string db_file_multiblock_read_count integer 8db_file_name_convert string db_files integer 200db_flashback_retention_target integer 1440db_keep_cache_size big integer 0db_name string KLWLdb_recovery_file_dest string db_recovery_file_dest_size big integer 0db_recycle

8、_cache_size big integer 0db_unique_name string KLWLdb_writer_processes integer 2dbwr_io_slaves integer 0ddl_wait_for_locks boolean FALSEdg_broker_config_file1 string /oracle/app/oracle/product/10.2/db_1/dbs/dr1KLWL.datdg_broker_config_file2 string /oracle/app/oracle/product/10.2/db_1/dbs/dr2KLWL.dat

9、dg_broker_start boolean FALSEdisk_asynch_io boolean TRUEdispatchers string (PROTOCOL=TCP) (SERVICE=KLWLXDB)distributed_lock_timeout integer 60dml_locks integer 4860drs_start boolean FALSEevent string fal_client string fal_server string fast_start_io_target integer 0fast_start_mttr_target integer 0fa

10、st_start_parallel_rollback string LOWfile_mapping boolean FALSEfileio_network_adapters string filesystemio_options string asynchfixed_date string gc_files_to_locks string gcs_server_processes integer 4global_context_pool_size string global_names boolean FALSEhash_area_size integer 131072hi_shared_me

11、mory_address integer 0hs_autoregister boolean TRUEifile file instance_groups string instance_name string KLWL1instance_number integer 1instance_type string RDBMSjava_max_sessionspace_size integer 0java_pool_size big integer 0java_soft_sessionspace_limit integer 0job_queue_processes integer 10large_p

12、ool_size big integer 0ldap_directory_access string NONElicense_max_sessions integer 0license_max_users integer 0license_sessions_warning integer 0local_listener string lock_name_space string lock_sga boolean FALSElog_archive_config string log_archive_dest string log_archive_dest_1 string LOCATION=+D

13、ATAGRP/archlog_archive_dest_10 string log_archive_dest_2 string log_archive_dest_3 string log_archive_dest_4 string log_archive_dest_5 string log_archive_dest_6 string log_archive_dest_7 string log_archive_dest_8 string log_archive_dest_9 string log_archive_dest_state_1 string enablelog_archive_dest

14、_state_10 string enablelog_archive_dest_state_2 string enablelog_archive_dest_state_3 string enablelog_archive_dest_state_4 string enablelog_archive_dest_state_5 string enablelog_archive_dest_state_6 string enablelog_archive_dest_state_7 string enablelog_archive_dest_state_8 string enablelog_archive

15、_dest_state_9 string enablelog_archive_duplex_dest string log_archive_format string %t_%s_%r.arclog_archive_local_first boolean TRUElog_archive_max_processes integer 2log_archive_min_succeed_dest integer 1log_archive_start boolean FALSElog_archive_trace integer 0log_buffer integer 14206976log_checkp

16、oint_interval integer 0log_checkpoint_timeout integer 1800log_checkpoints_to_alert boolean FALSElog_file_name_convert string logmnr_max_persistent_sessions integer 1max_commit_propagation_delay integer 0max_dispatchers integer max_dump_file_size string UNLIMITEDmax_enabled_roles integer 150max_share

17、d_servers integer nls_calendar string nls_comp string nls_currency string nls_date_format string nls_date_language string nls_dual_currency string nls_iso_currency string nls_language string AMERICANnls_length_semantics string BYTEnls_nchar_conv_excp string FALSEnls_numeric_characters string nls_sor

18、t string nls_territory string AMERICAnls_time_format string nls_time_tz_format string nls_timestamp_format string nls_timestamp_tz_format string object_cache_max_size_percent integer 10object_cache_optimal_size integer 102400olap_page_pool_size big integer 0open_cursors integer 2000open_links intege

19、r 4open_links_per_instance integer 4optimizer_dynamic_sampling integer 2optimizer_index_caching integer 0optimizer_index_cost_adj integer 100optimizer_mode string ALL_ROWSoptimizer_secure_view_merging boolean TRUEos_authent_prefix string ops$os_roles boolean FALSEparallel_adaptive_multi_user boolean

20、 TRUEparallel_automatic_tuning boolean FALSEparallel_execution_message_size integer 2152parallel_instance_group string parallel_max_servers integer 320parallel_min_percent integer 0parallel_min_servers integer 0parallel_server boolean TRUEparallel_server_instances integer 2parallel_threads_per_cpu i

21、nteger 2pga_aggregate_target big integer 4Gplsql_ccflags string plsql_code_type string INTERPRETEDplsql_compiler_flags string INTERPRETED, NON_DEBUGplsql_debug boolean FALSEplsql_native_library_dir string plsql_native_library_subdir_count integer 0plsql_optimize_level integer 2plsql_v2_compatibility

22、 boolean FALSEplsql_warnings string DISABLE:ALLpre_11g_enable_capture boolean FALSEpre_page_sga boolean FALSEprocesses integer 1000query_rewrite_enabled string TRUEquery_rewrite_integrity string enforcedrdbms_server_dn string read_only_open_delayed boolean FALSErecovery_parallelism integer 0recycleb

23、in string OFFremote_archive_enable string trueremote_dependencies_mode string TIMESTAMPremote_listener string LISTENERS_KLWLremote_login_passwordfile string EXCLUSIVEremote_os_authent boolean FALSEremote_os_roles boolean FALSEreplication_dependency_tracking boolean TRUEresource_limit boolean FALSEre

24、source_manager_plan string resumable_timeout integer 0rollback_segments string serial_reuse string disableservice_names string KLWLTAF, KLWLsession_cached_cursors integer 20session_max_open_files integer 10sessions integer 1105sga_max_size big integer 20Gsga_target big integer 20Gshadow_core_dump st

25、ring partialshared_memory_address integer 0shared_pool_reserved_size big integer 644245094shared_pool_size big integer 0shared_server_sessions integer shared_servers integer 1skip_unusable_indexes boolean TRUEsmtp_out_server string sort_area_retained_size integer 0sort_area_size integer 65536spfile

26、string +DATAGRP/klwl/spfileklwl.orasql92_security boolean FALSEsql_trace boolean FALSEsql_version string NATIVEsqltune_category string DEFAULTstandby_archive_dest string ?/dbs/archstandby_file_management string MANUALstar_transformation_enabled string FALSEstatistics_level string TYPICALstreams_pool

27、_size big integer 0tape_asynch_io boolean TRUEthread integer 1timed_os_statistics integer 0timed_statistics boolean TRUEtrace_enabled boolean TRUEtracefile_identifier string transactions integer 1215transactions_per_rollback_segment integer 5undo_management string AUTOundo_retention integer 900undo_

28、tablespace string UNDOTBS1use_indirect_data_buffers boolean FALSEuse_sigio boolean TRUEuser_dump_dest string /oracle/app/oracle/admin/KLWL/udumputl_file_dir string workarea_size_policy string AUTO財(cái)務(wù)應(yīng)用服務(wù)器系統(tǒng)資源占用在正常范圍內(nèi)。物流應(yīng)用服務(wù)器調(diào)整后數(shù)據(jù)庫(kù)服務(wù)器查看資源占用數(shù)據(jù)庫(kù)服務(wù)器在SGA參數(shù)調(diào)整后，兩臺(tái)數(shù)據(jù)庫(kù)服務(wù)器的CPU占用80%,頁(yè)交換都是0，內(nèi)存方面，10.20.10.11可用內(nèi)存在

29、3.8G左右，10.20.10.12可用內(nèi)存在2.6G左右，整體運(yùn)行正常。修改后的Oracle參數(shù)NAME TYPE VALUE O7_DICTIONARY_ACCESSIBILITY boolean FALSEactive_instance_count integer aq_tm_processes integer 0archive_lag_target integer 0asm_diskgroups string asm_diskstring string asm_power_limit integer 1audit_file_dest string /oracle/app/oracle/

30、admin/KLWL/adumpaudit_sys_operations boolean FALSEaudit_syslog_level string audit_trail string NONEbackground_core_dump string partialbackground_dump_dest string /oracle/app/oracle/admin/KLWL/bdumpbackup_tape_io_slaves boolean FALSEbitmap_merge_area_size integer 1048576blank_trimming boolean FALSEbu

31、ffer_pool_keep string buffer_pool_recycle string circuits integer cluster_database boolean TRUEcluster_database_instances integer 2cluster_interconnects string commit_point_strength integer 1commit_write string control_file_record_keep_time integer 7control_files string +DATAGRP/klwl/control01.ctl,

32、+DATAGRP/klwl/control02.ctl, +DATAGRP/klwl/control03.ctlcore_dump_dest string /oracle/app/oracle/admin/KLWL/cdumpcpu_count integer 16create_bitmap_area_size integer 8388608create_stored_outlines string cursor_sharing string EXACTcursor_space_for_time boolean FALSEdb_16k_cache_size big integer 0db_2k

33、_cache_size big integer 0db_32k_cache_size big integer 0db_4k_cache_size big integer 0db_8k_cache_size big integer 0db_block_buffers integer 0db_block_checking string FALSEdb_block_checksum string TRUEdb_block_size integer 8192db_cache_advice string ONdb_cache_size big integer 11456Mdb_create_file_d

34、est string db_create_online_log_dest_1 string db_create_online_log_dest_2 string db_create_online_log_dest_3 string db_create_online_log_dest_4 string db_create_online_log_dest_5 string db_domain string db_file_multiblock_read_count integer 8db_file_name_convert string db_files integer 200db_flashba

35、ck_retention_target integer 1440db_keep_cache_size big integer 0db_name string KLWLdb_recovery_file_dest string db_recovery_file_dest_size big integer 0db_recycle_cache_size big integer 0db_unique_name string KLWLdb_writer_processes integer 2dbwr_io_slaves integer 0ddl_wait_for_locks boolean FALSEdg

36、_broker_config_file1 string /oracle/app/oracle/product/10.2/db_1/dbs/dr1KLWL.datdg_broker_config_file2 string /oracle/app/oracle/product/10.2/db_1/dbs/dr2KLWL.datdg_broker_start boolean FALSEdisk_asynch_io boolean TRUEdispatchers string (PROTOCOL=TCP) (SERVICE=KLWLXDB)distributed_lock_timeout intege

37、r 60dml_locks integer 4860drs_start boolean FALSEevent string fal_client string fal_server string fast_start_io_target integer 0fast_start_mttr_target integer 0fast_start_parallel_rollback string LOWfile_mapping boolean FALSEfileio_network_adapters string filesystemio_options string asynchfixed_date

38、 string gc_files_to_locks string gcs_server_processes integer 4global_context_pool_size string global_names boolean FALSEhash_area_size integer 131072hi_shared_memory_address integer 0hs_autoregister boolean TRUEifile file instance_groups string instance_name string KLWL2instance_number integer 2ins

39、tance_type string RDBMSjava_max_sessionspace_size integer 0java_pool_size big integer 16Mjava_soft_sessionspace_limit integer 0job_queue_processes integer 10large_pool_size big integer 16Mldap_directory_access string NONElicense_max_sessions integer 0license_max_users integer 0license_sessions_warni

40、ng integer 0local_listener string lock_name_space string lock_sga boolean FALSElog_archive_config string log_archive_dest string log_archive_dest_1 string LOCATION=+DATAGRP/archlog_archive_dest_10 string log_archive_dest_2 string log_archive_dest_3 string log_archive_dest_4 string log_archive_dest_5

41、 string log_archive_dest_6 string log_archive_dest_7 string log_archive_dest_8 string log_archive_dest_9 string log_archive_dest_state_1 string enablelog_archive_dest_state_10 string enablelog_archive_dest_state_2 string enablelog_archive_dest_state_3 string enablelog_archive_dest_state_4 string ena

42、blelog_archive_dest_state_5 string enablelog_archive_dest_state_6 string enablelog_archive_dest_state_7 string enablelog_archive_dest_state_8 string enablelog_archive_dest_state_9 string enablelog_archive_duplex_dest string log_archive_format string %t_%s_%r.arclog_archive_local_first boolean TRUElo

43、g_archive_max_processes integer 2log_archive_min_succeed_dest integer 1log_archive_start boolean FALSElog_archive_trace integer 0log_buffer integer 14222336log_checkpoint_interval integer 0log_checkpoint_timeout integer 1800log_checkpoints_to_alert boolean FALSElog_file_name_convert string logmnr_ma

44、x_persistent_sessions integer 1max_commit_propagation_delay integer 0max_dispatchers integer max_dump_file_size string UNLIMITEDmax_enabled_roles integer 150max_shared_servers integer nls_calendar string nls_comp string nls_currency string nls_date_format string nls_date_language string nls_dual_cur

45、rency string nls_iso_currency string nls_language string AMERICANnls_length_semantics string BYTEnls_nchar_conv_excp string FALSEnls_numeric_characters string nls_sort string nls_territory string AMERICAnls_time_format string nls_time_tz_format string nls_timestamp_format string nls_timestamp_tz_for

46、mat string object_cache_max_size_percent integer 10object_cache_optimal_size integer 102400olap_page_pool_size big integer 0open_cursors integer 2000open_links integer 4open_links_per_instance integer 4optimizer_dynamic_sampling integer 2optimizer_index_caching integer 0optimizer_index_cost_adj inte

47、ger 100optimizer_mode string ALL_ROWSoptimizer_secure_view_merging boolean TRUEos_authent_prefix string ops$os_roles boolean FALSEparallel_adaptive_multi_user boolean TRUEparallel_automatic_tuning boolean FALSEparallel_execution_message_size integer 2152parallel_instance_group string parallel_max_se

48、rvers integer 320parallel_min_percent integer 0parallel_min_servers integer 0parallel_server boolean TRUEparallel_server_instances integer 2parallel_threads_per_cpu integer 2pga_aggregate_target big integer 4Gplsql_ccflags string plsql_code_type string INTERPRETEDplsql_compiler_flags string INTERPRE

49、TED, NON_DEBUGplsql_debug boolean FALSEplsql_native_library_dir string plsql_native_library_subdir_count integer 0plsql_optimize_level integer 2plsql_v2_compatibility boolean FALSEplsql_warnings string DISABLE:ALLpre_11g_enable_capture boolean FALSEpre_page_sga boolean FALSEprocesses integer 1000que

50、ry_rewrite_enabled string TRUEquery_rewrite_integrity string enforcedrdbms_server_dn string read_only_open_delayed boolean FALSErecovery_parallelism integer 0recyclebin string OFFremote_archive_enable string trueremote_dependencies_mode string TIMESTAMPremote_listener string LISTENERS_KLWLremote_log

51、in_passwordfile string EXCLUSIVEremote_os_authent boolean FALSEremote_os_roles boolean FALSEreplication_dependency_tracking boolean TRUEresource_limit boolean FALSEresource_manager_plan string resumable_timeout integer 0rollback_segments string serial_reuse string disableservice_names string KLWLTAF, KLWLsession_cached_cursors integer 20session_max_open_files integer 10sessions integer 1105sga_max_size big integer 12496Msga_target big integer 0shadow_core_dump string partialshared_memory_address integer 0shared_pool_reserved_size big integer 52009369shared_poo