首先说一下要调整的数据库所在服务器情况.
2个3.0G 至强 CPU ,4G内存
oracle8.1.7
windows2000+sp4
除了oracle以外,还装有另外的应用系统.oracle为此应用系统服务.
从TOP5来看
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file sequential read 21,160 4,662 38.36
log file sync 3,142 3,840 31.60
enqueue 13,052 667 5.49
log buffer space 15 488 4.02
direct path write 12,499 395 3.25
这是因为 数据库的 redolog size太小,原来默认的是1M.引起的.
db_files = 1024 # INITIAL
control_files = ("D:\oracle\oradata\zdspdm\control01.ctl", "D:\oracle\oradata\zdspdm\control02.ctl", "D:\oracle\oradata\zdspdm\control03.ctl"
open_cursors = 300
max_enabled_roles = 30
db_file_multiblock_read_count = 8 # INITIAL
db_block_buffers = 102400 # INITIAL
shared_pool_size = 104857600 # INITIAL
large_pool_size = 8388608
java_pool_size = 20971520
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 150 # INITIAL
parallel_max_servers = 5 # SMALL
log_buffer = 32768 # INITIAL
max_dump_file_size = 10240 # limit trace file size to 5M each
global_names = false
oracle_trace_collection_name = ""
background_dump_dest = D:\oracle\admin\zdspdm\bdump
user_dump_dest = D:\oracle\admin\zdspdm\udump
db_block_size = 8192
log_archive_start=true
log_archive_dest_1 = "location=D:\oracle\oradata\zdspdm\archive"
log_archive_format = %%ORACLE_SID%%T%TS%S.ARC
remote_login_passwordfile = exclusive
os_authent_prefix = ""
job_queue_processes = 4
job_queue_interval = 10
open_links = 255
distributed_transactions = 500
mts_dispatchers = "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"
compatible = 8.1.0
sort_area_size = 65536
sort_area_retained_size = 65536
根据 wsny和rollingpig版主 的指导现在给出详细的调整方案.
1.把redolog的大小由 1M调整为 100M
2.把sort_area_size由 65536 调整为 1024000
3.把shared_pool_size = 104857600调整为 150M
另外 ,出于对数据库运行安全方面的考虑,在C盘建立第4个controlfile的镜像, 在C盘建立一组redo file的镜像.
不知道大家对这个方案有什么意见?谢谢
|
