Need help: wio rease from 1% to more than 50% after increasing SGA size
in [Oracle]
|
Prev: Louisiana Will Face Lawsuit If New Law Brings Religion Into
Next: Oracle ODBC scalability with increased user volumes
From: jshen.cad on 27 Jun 2008 01:48 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
From: gym dot scuba dot kennedy at gmail on 27 Jun 2008 02:06 <jshen.cad(a)gmail.com> wrote in message news:ec85bcca-d460-4907-a1ba-daef4a22202e(a)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 > > I don't think 8.1.7 is even supported any more. Upgrade to something a little more recent. To find out why trace what the queries are doing. What is taking so long? Jim
From: jshen.cad on 27 Jun 2008 10:36 > > I don't think 8.1.7 is even supported any more. Upgrade to something a > little more recent. > To find out why trace what the queries are doing. What is taking so long? maybe that's a solution, but currently it could not help me out of trouble. To my understanding, enlarge data buffer will do help with oracle performance, but whil wio increase so fast?
From: machao on 27 Jun 2008 10:41 i suggest, Comparing the current statspack report and previous staspack reports, What is the difference between you can use OSW(os watcher) monitoring system to provide other details.
From: joel garry on 27 Jun 2008 14:24
On Jun 27, 7:36 am, "jshen....(a)gmail.com" <jshen....(a)gmail.com> wrote: > > I don't think 8.1.7 is even supported any more. Upgrade to something a > > little more recent. > > To find out why trace what the queries are doing. What is taking so long? > > maybe that's a solution, but currently it could not help me out of > trouble. > > To my understanding, enlarge data buffer will do help with oracle > performance, but whil wio increase so fast? Look on metalink for notes about async i/o and hp-ux. If you aren't using raw devices, you may be seeing the effects of Oracle trying to be cute and using async i/o when you don't have it. If you are using raw devices, you may be seeing the effect of moving bottlenecks around, like squeezing a balloon animal. You can see odd paradoxical effects when you just throw memory at Oracle. Sometimes it's things like, you do more stuff in memory, therefore you generate more redo, therefore you create an I/O problem. jg -- @home.com is bogus. 'Indeed, FYO point may ultimately be renamed the "FYRH point."' - Bryan Cantrill, August 2004 |