开滦项目性能调优总结 本文关键词：开滦，性能，项目
开滦项目性能调优总结 本文简介：开滦项目性能调优总结一、调整前1.数据库服务器1.1telnet至数据库服务器1.2查看资源占用（2台数据库集群）通过资源占用可以看出：当前数据库服务器的CPU（16颗）有瞬时的等待，整体在>80%区间内，运行正常。页交换方面是0，没有页交换出现。内存方面，10.20.10.12这台数据库服务器可用
开滦项目性能调优总结 本文内容：
开滦项目性能调优总结
一、
调整前
1.
数据库服务器
1.1
telnet
至数据库服务器
1.2
查看资源占用（2台数据库集群）
通过资源占用可以看出：当前数据库服务器的CPU（16颗）有瞬时的等待，整体在>80%区间内，运行正常。页交换方面是0，没有页交换出现。内存方面，10.20.10.12这台数据库服务器可用内存为：1.9G左右，10.20.10.11这台在0.8G左右。
1.3
Oracle当前参数配置
SQL>
show
parameter
NAME
TYPE
VALUE
------------------------------------
-----------
------------------------------
O7_DICTIONARY_ACCESSIBILITY
boolean
FALSE
active_instance_count
integer
aq_tm_processes
integer
0
archive_lag_target
integer
0
asm_diskgroups
string
asm_diskstring
string
asm_power_limit
integer
1
audit_file_dest
string
/oracle/app/oracle/admin/KLWL/adump
audit_sys_operations
boolean
FALSE
audit_syslog_level
string
audit_trail
string
NONE
background_core_dump
string
partial
background_dump_dest
string
/oracle/app/oracle/admin/KLWL/bdump
backup_tape_io_slaves
boolean
FALSE
bitmap_merge_area_size
integer
1048576
blank_trimming
boolean
FALSE
buffer_pool_keep
string
buffer_pool_recycle
string
circuits
integer
cluster_database
boolean
TRUE
cluster_database_instances
integer
2
cluster_interconnects
string
commit_point_strength
integer
1
commit_write
string
compatible
string
10.2.0.3.0
control_file_record_keep_time
integer
7
control_files
string
+DATAGRP/klwl/control01.ctl,+DATAGRP/klwl/control02.ctl,+DATAGRP/klwl/control03.ctl
core_dump_dest
string
/oracle/app/oracle/admin/KLWL/cdump
cpu_count
integer
16
create_bitmap_area_size
integer
8388608
create_stored_outlines
string
cursor_sharing
string
EXACT
cursor_space_for_time
boolean
FALSE
db_16k_cache_size
big
integer
0
db_2k_cache_size
big
integer
0
db_32k_cache_size
big
integer
0
db_4k_cache_size
big
integer
0
db_8k_cache_size
big
integer
0
db_block_buffers
integer
0
db_block_checking
string
FALSE
db_block_checksum
string
TRUE
db_block_size
integer
8192
db_cache_advice
string
ON
db_cache_size
big
integer
0
db_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_online_log_dest_5
string
db_domain
string
db_file_multiblock_read_count
integer
8
db_file_name_convert
string
db_files
integer
200
db_flashback_retention_target
integer
1440
db_keep_cache_size
big
integer
0
db_name
string
KLWL
db_recovery_file_dest
string
db_recovery_file_dest_size
big
integer
0
db_recycle_cache_size
big
integer
0
db_unique_name
string
KLWL
db_writer_processes
integer
2
dbwr_io_slaves
integer
0
ddl_wait_for_locks
boolean
FALSE
dg_broker_config_file1
string
/oracle/app/oracle/product/10.2/db_1/dbs/dr1KLWL.dat
dg_broker_config_file2
string
/oracle/app/oracle/product/10.2/db_1/dbs/dr2KLWL.dat
dg_broker_start
boolean
FALSE
disk_asynch_io
boolean
TRUE
dispatchers
string
(PROTOCOL=TCP)
(SERVICE=KLWLXDB)
distributed_lock_timeout
integer
60
dml_locks
integer
4860
drs_start
boolean
FALSE
event
string
fal_client
string
fal_server
string
fast_start_io_target
integer
0
fast_start_mttr_target
integer
0
fast_start_parallel_rollback
string
LOW
file_mapping
boolean
FALSE
fileio_network_adapters
string
filesystemio_options
string
asynch
fixed_date
string
gc_files_to_locks
string
gcs_server_processes
integer
4
global_context_pool_size
string
global_names
boolean
FALSE
hash_area_size
integer
131072
hi_shared_memory_address
integer
0
hs_autoregister
boolean
TRUE
ifile
file
instance_groups
string
instance_name
string
KLWL1
instance_number
integer
1
instance_type
string
RDBMS
java_max_sessionspace_size
integer
0
java_pool_size
big
integer
0
java_soft_sessionspace_limit
integer
0
job_queue_processes
integer
10
large_pool_size
big
integer
0
ldap_directory_access
string
NONE
license_max_sessions
integer
0
license_max_users
integer
0
license_sessions_warning
integer
0
local_listener
string
lock_name_space
string
lock_sga
boolean
FALSE
log_archive_config
string
log_archive_dest
string
log_archive_dest_1
string
LOCATION=+DATAGRP/arch
log_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
enable
log_archive_dest_state_10
string
enable
log_archive_dest_state_2
string
enable
log_archive_dest_state_3
string
enable
log_archive_dest_state_4
string
enable
log_archive_dest_state_5
string
enable
log_archive_dest_state_6
string
enable
log_archive_dest_state_7
string
enable
log_archive_dest_state_8
string
enable
log_archive_dest_state_9
string
enable
log_archive_duplex_dest
string
log_archive_format
string
%t_%s_%r.arc
log_archive_local_first
boolean
TRUE
log_archive_max_processes
integer
2
log_archive_min_succeed_dest
integer
1
log_archive_start
boolean
FALSE
log_archive_trace
integer
0
log_buffer
integer
14206976
log_checkpoint_interval
integer
0
log_checkpoint_timeout
integer
1800
log_checkpoints_to_alert
boolean
FALSE
log_file_name_convert
string
logmnr_max_persistent_sessions
integer
1
max_commit_propagation_delay
integer
0
max_dispatchers
integer
max_dump_file_size
string
UNLIMITED
max_enabled_roles
integer
150
max_shared_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
AMERICAN
nls_length_semantics
string
BYTE
nls_nchar_conv_excp
string
FALSE
nls_numeric_characters
string
nls_sort
string
nls_territory
string
AMERICA
nls_time_format
string
nls_time_tz_format
string
nls_timestamp_format
string
nls_timestamp_tz_format
string
object_cache_max_size_percent
integer
10
object_cache_optimal_size
integer
102400
olap_page_pool_size
big
integer
0
open_cursors
integer
2000
open_links
integer
4
open_links_per_instance
integer
4
optimizer_dynamic_sampling
integer
2
optimizer_features_enable
string
10.2.0.4
optimizer_index_caching
integer
0
optimizer_index_cost_adj
integer
100
optimizer_mode
string
ALL_ROWS
optimizer_secure_view_merging
boolean
TRUE
os_authent_prefix
string
ops$
os_roles
boolean
FALSE
parallel_adaptive_multi_user
boolean
TRUE
parallel_automatic_tuning
boolean
FALSE
parallel_execution_message_size
integer
2152
parallel_instance_group
string
parallel_max_servers
integer
320
parallel_min_percent
integer
0
parallel_min_servers
integer
0
parallel_server
boolean
TRUE
parallel_server_instances
integer
2
parallel_threads_per_cpu
integer
2
pga_aggregate_target
big
integer
4G
plsql_ccflags
string
plsql_code_type
string
INTERPRETED
plsql_compiler_flags
string
INTERPRETED,NON_DEBUG
plsql_debug
boolean
FALSE
plsql_native_library_dir
string
plsql_native_library_subdir_count
integer
0
plsql_optimize_level
integer
2
plsql_v2_compatibility
boolean
FALSE
plsql_warnings
string
DISABLE:ALL
pre_11g_enable_capture
boolean
FALSE
pre_page_sga
boolean
FALSE
processes
integer
1000
query_rewrite_enabled
string
TRUE
query_rewrite_integrity
string
enforced
rdbms_server_dn
string
read_only_open_delayed
boolean
FALSE
recovery_parallelism
integer
0
recyclebin
string
OFF
remote_archive_enable
string
true
remote_dependencies_mode
string
TIMESTAMP
remote_listener
string
LISTENERS_KLWL
remote_login_passwordfile
string
EXCLUSIVE
remote_os_authent
boolean
FALSE
remote_os_roles
boolean
FALSE
replication_dependency_tracking
boolean
TRUE
resource_limit
boolean
FALSE
resource_manager_plan
string
resumable_timeout
integer
0
rollback_segments
string
serial_reuse
string
disable
service_names
string
KLWLTAF,KLWL
session_cached_cursors
integer
20
session_max_open_files
integer
10
sessions
integer
1105
sga_max_size
big
integer
20G
sga_target
big
integer
20G
shadow_core_dump
string
partial
shared_memory_address
integer
0
shared_pool_reserved_size
big
integer
644245094
shared_pool_size
big
integer
0
shared_server_sessions
integer
shared_servers
integer
1
skip_unusable_indexes
boolean
TRUE
smtp_out_server
string
sort_area_retained_size
integer
0
sort_area_size
integer
65536
spfile
string
+DATAGRP/klwl/spfileklwl.ora
sql92_security
boolean
FALSE
sql_trace
boolean
FALSE
sql_version
string
NATIVE
sqltune_category
string
DEFAULT
standby_archive_dest
string
?/dbs/arch
standby_file_management
string
MANUAL
star_transformation_enabled
string
FALSE
statistics_level
string
TYPICAL
streams_pool_size
big
integer
0
tape_asynch_io
boolean
TRUE
thread
integer
1
timed_os_statistics
integer
0
timed_statistics
boolean
TRUE
trace_enabled
boolean
TRUE
tracefile_identifier
string
transactions
integer
1215
transactions_per_rollback_segment
integer
5
undo_management
string
AUTO
undo_retention
integer
900
undo_tablespace
string
UNDOTBS1
use_indirect_data_buffers
boolean
FALSE
use_sigio
boolean
TRUE
user_dump_dest
string
/oracle/app/oracle/admin/KLWL/udump
utl_file_dir
string
workarea_size_policy
string
AUTO
2.
财务应用服务器
系统资源占用在正常范围内。
3.
物流应用服务器
二、
调整后
1.
数据库服务器
1.1
查看资源占用
数据库服务器在SGA参数调整后，两台数据库服务器的CPU占用<80%,页交换都是0，内存方面，10.20.10.11可用内存在3.8G左右，10.20.10.12可用内存在2.6G左右，整体运行正常。
1.2
修改后的Oracle参数
NAME
TYPE
VALUE
------------------------------------
-----------
------------------------------
O7_DICTIONARY_ACCESSIBILITY
boolean
FALSE
active_instance_count
integer
aq_tm_processes
integer
0
archive_lag_target
integer
0
asm_diskgroups
string
asm_diskstring
string
asm_power_limit
integer
1
audit_file_dest
string
/oracle/app/oracle/admin/KLWL/adump
audit_sys_operations
boolean
FALSE
audit_syslog_level
string
audit_trail
string
NONE
background_core_dump
string
partial
background_dump_dest
string
/oracle/app/oracle/admin/KLWL/bdump
backup_tape_io_slaves
boolean
FALSE
bitmap_merge_area_size
integer
1048576
blank_trimming
boolean
FALSE
buffer_pool_keep
string
buffer_pool_recycle
string
circuits
integer
cluster_database
boolean
TRUE
cluster_database_instances
integer
2
cluster_interconnects
string
commit_point_strength
integer
1
commit_write
string
compatible
string
10.2.0.3.0
control_file_record_keep_time
integer
7
control_files
string
+DATAGRP/klwl/control01.ctl,+DATAGRP/klwl/control02.ctl,+DATAGRP/klwl/control03.ctl
core_dump_dest
string
/oracle/app/oracle/admin/KLWL/cdump
cpu_count
integer
16
create_bitmap_area_size
integer
8388608
create_stored_outlines
string
cursor_sharing
string
EXACT
cursor_space_for_time
boolean
FALSE
db_16k_cache_size
big
integer
0
db_2k_cache_size
big
integer
0
db_32k_cache_size
big
integer
0
db_4k_cache_size
big
integer
0
db_8k_cache_size
big
integer
0
db_block_buffers
integer
0
db_block_checking
string
FALSE
db_block_checksum
string
TRUE
db_block_size
integer
8192
db_cache_advice
string
ON
db_cache_size
big
integer
11456M
db_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_online_log_dest_5
string
db_domain
string
db_file_multiblock_read_count
integer
8
db_file_name_convert
string
db_files
integer
200
db_flashback_retention_target
integer
1440
db_keep_cache_size
big
integer
0
db_name
string
KLWL
db_recovery_file_dest
string
db_recovery_file_dest_size
big
integer
0
db_recycle_cache_size
big
integer
0
db_unique_name
string
KLWL
db_writer_processes
integer
2
dbwr_io_slaves
integer
0
ddl_wait_for_locks
boolean
FALSE
dg_broker_config_file1
string
/oracle/app/oracle/product/10.2/db_1/dbs/dr1KLWL.dat
dg_broker_config_file2
string
/oracle/app/oracle/product/10.2/db_1/dbs/dr2KLWL.dat
dg_broker_start
boolean
FALSE
disk_asynch_io
boolean
TRUE
dispatchers
string
(PROTOCOL=TCP)
(SERVICE=KLWLXDB)
distributed_lock_timeout
integer
60
dml_locks
integer
4860
drs_start
boolean
FALSE
event
string
fal_client
string
fal_server
string
fast_start_io_target
integer
0
fast_start_mttr_target
integer
0
fast_start_parallel_rollback
string
LOW
file_mapping
boolean
FALSE
fileio_network_adapters
string
filesystemio_options
string
asynch
fixed_date
string
gc_files_to_locks
string
gcs_server_processes
integer
4
global_context_pool_size
string
global_names