oracle9i性能调整问题

显示全部楼层 · 2010-5-13 10:04:27

我在RED HAT上装了ORACLE9I，建库的时候所有的内存参数都是用的默认的，现在数据库有四个用户，发现在其中一个用户进行大批量数据处理时，其他用户访问就变的很慢。请问这是该调整什么参数呢，我发现调整了dbs/initoracle.ora文件里的诸如large_pool_size, shared_pool_size等内存参数后，并没有生效。

千问 · 2010-5-13 10:04:27

做大批量数据处理慢很正常啊，你机器什么配置，参数文件怎么配置的？是不是本身来说磁盘的IO是瓶颈啊？

千问 · 2010-5-13 10:04:27

4CPU,4G内存, 数据库以裸设备的方式建在盘阵上。
修改内存参数应该是改哪个配置文件啊，我修改了dbs/initoracle.ora文件后重新启动数据库,发现没有生效.

千问 · 2010-5-13 10:04:27

9i启动参数的顺序是：检查spfileSID.ORA,找不到再找spfile.ora,找不到再找initSID.ora。
你可以强制使用：startup pfile='...'
估计是io问题，你也可以取statspack报告来看一下，哪部分等待事件比较严重，再能决定调整哪个参数。

千问 · 2010-5-13 10:04:27

最初由 afei0727 发布
[B]4CPU,4G内存, 数据库以裸设备的方式建在盘阵上。
修改内存参数应该是改哪个配置文件啊，我修改了dbs/initoracle.ora文件后重新启动数据库,发现没有生效. [/B]
show parameter 贴出结果看看

千问 · 2010-5-13 10:04:27

SQL> show parameter
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY
boolean FALSE
active_instance_count
integer
aq_tm_processes
integer 1
archive_lag_target
integer 0
audit_file_dest
string?/rdbms/audit
audit_sys_operations
boolean FALSE
audit_trail
stringNONE
background_core_dump
stringpartial
background_dump_dest
string/oracle/Orahome9/admin/bswl/bd

ump
backup_tape_io_slaves
boolean FALSE
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size
integer 1048576
blank_trimming
boolean FALSE
buffer_pool_keep
string
buffer_pool_recycle
string
circuits
integer 170
cluster_database
boolean FALSE
cluster_database_instances integer 1
cluster_interconnects
string
commit_point_strength
integer 1
compatible
string9.2.0.0.0
control_file_record_keep_timeinteger 7
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
control_files
string/dev/raw/raw3, /dev/raw/raw4,

/dev/raw/raw5
core_dump_dest
string/oracle/Orahome9/admin/bswl/cd

ump
cpu_count
integer 8
create_bitmap_area_size
integer 8388608
cursor_sharing
stringEXACT
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
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
db_4k_cache_size
big integer 0
db_8k_cache_size
big integer 0
db_block_buffers
integer 0
db_block_checking
boolean FALSE
db_block_checksum
boolean TRUE
db_block_size
integer 8192
db_cache_advice
stringON
db_cache_size
big integer 33554432
db_create_file_dest
string
db_create_online_log_dest_1
string
db_create_online_log_dest_2
string
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
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_countinteger 16
db_file_name_convert
string
db_files
integer 200
db_keep_cache_size
big integer 0
db_name
stringbswl
db_recycle_cache_size
big integer 0
db_writer_processes
integer 1
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
dblink_encrypt_login
boolean FALSE
dbwr_io_slaves
integer 0
dg_broker_config_file1
string?/dbs/[email protected]
dg_broker_config_file2
string?/dbs/[email protected]
dg_broker_start
boolean FALSE
disk_asynch_io
boolean TRUE
dispatchers
string(PROTOCOL=TCP)
distributed_lock_timeout
integer 60
dml_locks
integer 748
drs_start
boolean FALSE
enqueue_resources
integer 968
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
event
string
fal_client
string
fal_server
string
fast_start_io_target
integer 0
fast_start_mttr_target
integer 300
fast_start_parallel_rollback stringLOW
file_mapping
boolean FALSE
filesystemio_options
stringnone
fixed_date
string
gc_files_to_locks
string
global_context_pool_size
string
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
global_names
boolean FALSE
hash_area_size
integer 1048576
hash_join_enabled
boolean TRUE
hi_shared_memory_address
integer 0
hs_autoregister
boolean TRUE
ifile
file
instance_groups
string
instance_name
stringbswl
instance_number
integer 0
java_max_sessionspace_size integer 0
java_pool_size
big integer 83886080
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
java_soft_sessionspace_limit integer 0
job_queue_processes
integer 10
large_pool_size
big integer 16777216
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_dest
string
log_archive_dest_1
string
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
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
stringenable
log_archive_dest_state_10
stringenable
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2
stringenable
log_archive_dest_state_3
stringenable
log_archive_dest_state_4
stringenable
log_archive_dest_state_5
stringenable
log_archive_dest_state_6
stringenable
log_archive_dest_state_7
stringenable
log_archive_dest_state_8
stringenable
log_archive_dest_state_9
stringenable
log_archive_duplex_dest
string
log_archive_format
string%t_%s.dbf
log_archive_max_processes
integer 2
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
log_archive_min_succeed_dest integer 1
log_archive_start
boolean FALSE
log_archive_trace
integer 0
log_buffer
integer 1048576
log_checkpoint_interval
integer 0
log_checkpoint_timeout
integer 1800
log_checkpoints_to_alert
boolean FALSE
log_file_name_convert
string
log_parallelism
integer 1
logmnr_max_persistent_sessions integer 1
max_commit_propagation_delay integer 700
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
max_dispatchers
integer 5
max_dump_file_size
stringUNLIMITED
max_enabled_roles
integer 30
max_rollback_segments
integer 37
max_shared_servers
integer 20
mts_circuits
integer 170
mts_dispatchers
string(PROTOCOL=TCP)
mts_listener_address
string
mts_max_dispatchers
integer 5
mts_max_servers
integer 20
mts_multiple_listeners
boolean FALSE
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
mts_servers
integer 1
mts_service
stringbswl
mts_sessions
integer 165
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
stringAMERICAN
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics
stringBYTE
nls_nchar_conv_excp
stringFALSE
nls_numeric_characters
string
nls_sort
string
nls_territory
stringAMERICA
nls_time_format
string
nls_time_tz_format
string
nls_timestamp_format
string
nls_timestamp_tz_format
string
object_cache_max_size_percentinteger 10
object_cache_optimal_size
integer 102400
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
olap_page_pool_size
integer 33554432
open_cursors
integer 300
open_links
integer 4
open_links_per_instance
integer 4
optimizer_dynamic_sampling integer 1
optimizer_features_enable
string9.2.0
optimizer_index_caching
integer 0
optimizer_index_cost_adj
integer 100
optimizer_max_permutations integer 2000
optimizer_mode
stringCHOOSE
oracle_trace_collection_name string
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
oracle_trace_collection_path string?/otrace/admin/cdf
oracle_trace_collection_size integer 5242880
oracle_trace_enable
boolean FALSE
oracle_trace_facility_name stringoracled
oracle_trace_facility_path string?/otrace/admin/fdf
os_authent_prefix
stringops$
os_roles
boolean FALSE
parallel_adaptive_multi_user boolean FALSE
parallel_automatic_tuning
boolean FALSE
parallel_execution_message_sizeinteger 2148
parallel_instance_group
string
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers
integer 5
parallel_min_percent
integer 0
parallel_min_servers
integer 0
parallel_server
boolean FALSE
parallel_server_instances
integer 1
parallel_threads_per_cpu
integer 2
partition_view_enabled
boolean FALSE
pga_aggregate_target
big integer 25165824
plsql_compiler_flags
stringINTERPRETED
plsql_native_c_compiler
string
plsql_native_library_dir
string
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
plsql_native_library_subdir_countinteger 0
plsql_native_linker
string
plsql_native_make_file_name
string
plsql_native_make_utility
string
plsql_v2_compatibility
boolean FALSE
pre_page_sga
boolean FALSE
processes
integer 150
query_rewrite_enabled
stringFALSE
query_rewrite_integrity
stringenforced
rdbms_server_dn
string
read_only_open_delayed
boolean FALSE
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
recovery_parallelism
integer 0
remote_archive_enable
stringtrue
remote_dependencies_mode
stringTIMESTAMP
remote_listener
string
remote_login_passwordfile
stringEXCLUSIVE
remote_os_authent
boolean FALSE
remote_os_roles
boolean FALSE
replication_dependency_trackingboolean TRUE
resource_limit
boolean FALSE
resource_manager_plan
string
rollback_segments
string
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
row_locking
stringalways
serial_reuse
stringDISABLE
serializable
boolean FALSE
service_names
stringbswl
session_cached_cursors
integer 0
session_max_open_files
integer 10
sessions
integer 170
sga_max_size
big integer 236523640
shadow_core_dump
stringpartial
shared_memory_address
integer 0
shared_pool_reserved_size
big integer 4194304
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
shared_pool_size
big integer 83886080
shared_server_sessions
integer 165
shared_servers
integer 1
sort_area_retained_size
integer 0
sort_area_size
integer 524288
spfile
string?/dbs/[email protected]
sql92_security
boolean FALSE
sql_trace
boolean FALSE
sql_version
stringNATIVE
standby_archive_dest
string?/dbs/arch
standby_file_management
stringMANUAL
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
star_transformation_enabled
stringFALSE
statistics_level
stringTYPICAL
tape_asynch_io
boolean TRUE
thread
integer 0
timed_os_statistics
integer 0
timed_statistics
boolean TRUE
trace_enabled
boolean TRUE
tracefile_identifier
string
transaction_auditing
boolean TRUE
transactions
integer 187
transactions_per_rollback_segmentinteger 5
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
undo_management
stringAUTO
undo_retention
integer 10800
undo_suppress_errors
boolean FALSE
undo_tablespace
stringUNDOTBS1
use_indirect_data_buffers
boolean FALSE
user_dump_dest
string/oracle/Orahome9/admin/bswl/ud

ump
utl_file_dir
string
workarea_size_policy
stringAUTO
SQL>
我具体应该修改哪个配置文件,是Orahome/dbs/initSID.ora呢，还是Orahome/admin/SID/pfile/init.ora??

千问 · 2010-5-13 10:04:27

db_cache_size big integer 33554432 太小，修改
alter system set db_cache_size＝1000000000 scope＝spfile;
shared_pool_size big integer 83886080太小，修改
alter system set shared_pool_size＝200000000 scope＝spfile;
pga_aggregate_target big integer 25165824，修改
alter system set pga_aggregate_target＝300000000 scope＝spfile;
屏蔽所有关于MTS的参数。

千问 · 2010-5-13 10:04:27

还有一个就是sga_max_size big至少应该为1G－1.3G,还有一个就是回滚段的大小！

千问 · 2010-5-13 10:04:27

这是我修改后的dbs目录下的initSID.ora文件, 还有哪几句话是需要屏蔽和修改的呢..还有,我现在是强制用这个文件来启动数据库，若我不带上这个文件，只是startup的话，系统是找的哪个配置文件来启动啊? 我试了一下，也不是admin 下的pfile里的init.ora文件。前面那位所说的回滚段是否是undo_retention, 应该为多大合适呢? 我建库的时候选择的是共享服务器模式,MTS必须屏蔽么? 具体哪几句话是关于MTS的呢? 谢谢各位.
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=1016582400
db_file_multiblock_read_count=16
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Database Identification
###########################################
db_domain=""
db_name=bswl

bash-2.05$ more initbswl.ora
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=1016582400
db_file_multiblock_read_count=16
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Database Identification
###########################################
db_domain=""
db_name=bswl
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/oracle/Orahome9/admin/bswl/bdump
core_dump_dest=/oracle/Orahome9/admin/bswl/cdump
timed_statistics=TRUE
user_dump_dest=/oracle/Orahome9/admin/bswl/udump
###########################################
# File Configuration
###########################################
control_files=("/dev/raw/raw3", "/dev/raw/raw4", "/dev/raw/raw5&quot

###########################################
# Instance Identification
###########################################
instance_name=bswl
###########################################
# Job Queues
###########################################
job_queue_processes=10
###########################################
# MTS
###########################################
dispatchers="(PROTOCOL=TCP)"
###########################################
# Miscellaneous
###########################################
aq_tm_processes=1
compatible=9.2.0.0.0
###########################################
# Optimizer
###########################################
hash_join_enabled=TRUE
query_rewrite_enabled=FALSE
star_transformation_enabled=FALSE
###########################################
# Pools
###########################################
java_pool_size=63886080
large_pool_size=60886080
shared_pool_size=210860000
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300
###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=251658000
sort_area_size=52428
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1

千问 · 2010-5-13 10:04:27

1、9i默认启动的是spfile
你的参数里面已经指明了是spfile
spfile string ?/dbs/[email protected]
2、MTS只要注释
dispatchers="(PROTOCOL=TCP)"
即可
3、undo_retention=10800并不影响性能，但是大了会浪费空间
这个参数可以动态修改