开滦项目性能调优总结

时间：2021-03-19 12:06:14 来源：蒲公英阅读网 本文已影响人

相关热词搜索：开滦 性能 项目

开滦项目性能调优总结 本文关键词：开滦，性能，项目

开滦项目性能调优总结 本文简介：开滦项目性能调优总结一、调整前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