Re: Need help: wio rease from 1% to more than 50% after increasing SGA size

<jshen.cad_at_gmail.com> wrote in message
news:ec85bcca-d460-4907-a1ba-daef4a22202e_at_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 Received on Fri Jun 27 2008 - 01:06:10 CDT