Need help: wio rease from 1% to more than 50% after increasing SGA size
Date: Thu, 26 Jun 2008 22:48:45 -0700 (PDT)
Message-ID: <ec85bcca-d460-4907-a1ba-daef4a22202e@z16g2000prn.googlegroups.com>
hi,
we met problem with oracle8.1.7(on HP-UX) SGA tuning.
The Oracle server runs on HP-UX11i. Previously, the server has 16GB RAM and 20CPU installed.
The business data keeps increasing in past year which make us decide to install more RAM into server
and more Cache into disk array.
the init.ora looks like :
open_cursors = 500
max_enabled_roles = 30
db_block_buffers = 1957760
shared_pool_size = 800000000
large_pool_size = 30M
java_pool_size = 0
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 1800
log_buffer = 5242880
db_block_size = 4096
remote_login_passwordfile = exclusive
os_authent_prefix = ""
compatible = "8.1.0"
sort_area_size = 262144
sort_area_retained_size = 262144
disk_asynch_io = TRUE
db_files=1024
db_block_lru_latches=12
audit_trail = false
transaction_auditing = false
optimizer_mode = rule
db_file_multiblock_read_count = 32
db_writer_processes=4
parallel_execution_message_size=8192
sort_multiblock_read_count = 8
UTL_FILE_DIR=/oracle/migrate
We add 20GB DRAM to DB server. At that time "sar -S " shows wio is always lower than 5%, while "sar -d " shows %busy on each disk is lower than 48%, %await is less than 40%.
In order to make use of new memory, we tuned SGA parameter and restart the DB server. Now, it shows
"sar -S " always shows high wio ( > 40% ,), while "sar -d " shows %busy on each disk is lower than 40%, await is higher than 45%.
now , the init.ora contains entrie comparable with above as:
#updated on 2008.6.23
open_cursors = 1000
max_enabled_roles = 30
#db_block_buffers = 1200000
#db_block_buffers = 1855360
# quotaed out 20080617
#db_block_buffers = 1957760
#added 20080623,total 21.35G, 24GB
#db_block_buffers = 5597056
db_block_buffers = 6291456
#shared_pool_size = 500000000
#shared_pool_size = 600000000
#quotaed out 20080617
#shared_pool_size = 800000000
# added 20080623 to 2570MB
#shared_pool_size = 1600000000
shared_pool_size = 838860800
#for rman backup, updated on 2008.06.23
large_pool_size = 100M
java_pool_size = 0
log_checkpoint_interval = 10240
log_checkpoint_timeout = 1800
processes = 1800
# added 20080623
log_buffer = 5836800
# increased from 262144 to 462848, 20080623
sort_area_size = 462848
sort_area_retained_size = 462848
disk_asynch_io = TRUE
db_files=1024
db_block_lru_latches=12
audit_trail = false
transaction_auditing = false
optimizer_mode = rule
db_file_multiblock_read_count = 32
db_writer_processes=4
parallel_execution_message_size=8192
sort_multiblock_read_count = 8
UTL_FILE_DIR=/oracle/migrate
event="logon trace name ksfqp_limit level 1"
HPUX_SCHED_NOAGE = 178
what's the possible reason for wio increasing ? how could we deal with it?
thanks in advance!
Joe Received on Fri Jun 27 2008 - 00:48:45 CDT