Perforamnce issue... (dbc_min_pct and dbc_max_pct)

[unimedriopreto]

Hi all.
I'm having performance problems in my production system.
My server configuration is:
rp3410 2x800 Mhz Processor
4096 MB of RAM
6x73Gb 15000RPM Hd.
S.O. HP-UX 11.11
Oracle 9.2.0.7.
All the day overall consume of server is very high(about 90%). I have 230 users connected and about 3 active users at same time.
I read about dbc_min_pct and dbc_max_pct, that if result of "sar -b" shows low %rcache and %wcache, I shoud lower dbc_min and max_pct to increase this results, and so, gain performance.
Yesterday I setted this tow kernel parameters to 3%, and now I'd verified that system performance slow down.
When I execute "sar -b 3 25" the avagere result is:
342 585 41 316 317 0 0 0.
%rcache is 41 and %wcache is 0....
Is this a problem?
How can I increase my server capacity?
I use only Oracle in this server.
I'd changed the following kerner parameters too:
DBC_MAX_PCT 3
DBC_MIN_PCT 3
KSI_ALLOC_MAX 32768
MAX_THREAD_PROC 1024
MAXDSIZ 1073741824 bytes
MAXDSIZ_64BIT 2147483648 bytes
MAXSSIZ 134217728 bytes
MAXSSIZ_64BIT 1073741824 bytes
MAXSWAPCHUNKS 16384*
MAXUPRC 3686
MSGMAP 4098
MSGMNI 4096
MSGSEG 32767
MSGTQL 4096
NCALLOUT 7200
NCSIZE 35840
NFILE 63488
NFLOCKS 4096
NINODE 34816
NKTHREAD 7184
NPROC 4096
SEMMAP 4098
SEMMNI 4096
SEMMNS 8192
SEMMNU 4092
SEMVMX 32767
SHMMAX 3221225472
SHMMIN
SHMMNI 512
SHMSEG 32
VPS_CEILING 64

Are they correct?
How can I better my system performance?

Thank for all help

[Keith Bryson]

Hi

I'd normally suggest that you balance buffer cache at around 400-600Mb, but you only have a small amount of RAM. How much physical memory do you have free (ie. can you afford to set DBC_MAX_PCT to 10 - if so, this would be more desirable).

Keith

[unimedriopreto]

Hi Keith.
Thanks for response.
I have about 900 Mb of free memory (RAM). I'll try to set dbc_max_pct to 10 (it was setted to 15 yesterday, so I lowered it, and performance come down).
And about dbc_min_pct, it's recommended to set it to 5% or more? Will I have any problem if I let it to 3%?

Thanks.

[Keith Bryson]

You can leave MIN_PCT as it is.

Thanks - K

[Eric Antunes · Accepted solution]

Hi,

Increase your dbc_max_pct and dbc_min_pct until you get a "sar -b 3 33" %rcache Average equal or above 90%. Note that those 2 parameters are percentages of Physical memory. In your situation, you need something between 5 and 10 for dbc_max_pct. Also, dbc_min_pct must be less (not equal) than dbc_max_pct: you can begin with dbc_min_pct = 5 and dbc_max_pct = 8 and execute "sar -b 3 33" to see what %rcache average you get.

Finally, you ninode seems oversized: start with 2048 and "sar -v 3 33" and check if you have overflow (ov column next to inod-sz column).

Best Regards,

Eric Antunes

[unimedriopreto]

Thanks for all explanations.
I'll try to set dbc_max_pct to 10 and dbc_min_pct to 5.
I'll set ninode to 2048 (when I execute sar -v, I see 856/4464 so, 2048 may be a good number).
And about set nproc to 1024? (sar -v result is 143/4096).

[Victor BERRIDGE]

Hi,

On what criteria you noticed performance degradation?
When you say 900 MB free memory, should I understand most of the rest is used by oracle (great SGA size)? or is it used by pseudoswap...
with 4 GB RAM, 500MB->12 for DBC_MAX_PCT should be plenty enough...
What is the output of swapinfo -tam?

What size SGA do you have?

Have you tried fine tuning the filesystems?
(Do you have OnlineJFS?)

All the best
Victor

[Yogeeraj_1]

hi,

maybe you can also extract a Statspack report of your database during the peek period and get some hints on performance bottlenecks also...

hope this helps too!

kind regards
yogeeraj

[unimedriopreto]

Hi,
I'd saw sar -ud, for a long time, that was showing %idle about 40% all the time, and sometime disk consume(%busy) very hight
Oracle Enterprise Manager Performance Overview shows about 50% of server consume.
Top command shows that cpu consume is about 40%.
Analysing Oracle Perfstat report, I saw that bad SQL instructions was responsable for 70% of server workload, but I'm using and third-part application, and I can't change sql instructions; So I need to "harvest" my server, and let it works as best as it could.
Oracle SGA configuration is the following:

sga_max_size=2281701376
large_pool_size=268435456
shared_pool_size=536870912

log_archive_max_processes=1
db_block_size=8192
db_cache_size=1073741824
db_file_multiblock_read_count=16
log_buffer=4194304

db_writer_processes=1
disk_asynch_io=false

open_cursors=300
job_queue_processes=10

hash_join_enabled=TRUE
query_rewrite_enabled=TRUE
star_transformation_enabled=FALSE

processes=150
sessions=1000

fast_start_mttr_target=300
log_checkpoint_timeout=0

pga_aggregate_target=268435456
sort_area_size=4194304

dispatchers="(protocol=TCP)(disp=5)"
shared_servers=5
max_dispatchers=100
max_shared_servers=50

swapinfo -tam shows:
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 8192 0 8192 0% 0 - 1 /dev/vg00/lvol2
reserve - 2641 -2641
memory 3142 31 3111 1%
total 11334 2672 8662 24% - 0 -
...
I'm not using swap (paging out). I think that It's very good!

About Online JFS, I don't know nothing about that...
My disks configuration is:
2x 73gb hd as VG00 (with lvol level mirror)
2x 73gb hd as VG01 (with lvol level mirror)
2x 73gb hd as VG02 (with lvol level mirror)
lvols created in this VGs, with vxfs filesystem.
Mirror copies of all lvols.
S.O., Oracle binaries, redologs, system, temporary and undo datafiles on VG00.
Data datafiles on VG01.
Index datafiles on VG02.

How could I get better performance (without and with stripping)?
Online JFS is a different filesystem than vxfs? Is it better?

Thank all.
Jefferson.

[Hein van den Heuvel]

>>> I read about dbc_min_pct and dbc_max_pct, that if result of "sar -b" shows low %rcache and %wcache, I shoud lower dbc_min and max_pct to increase this results, and so, gain performance.

Only if that memory then gets re-used for more useful purposes. So what other knob did you turn to use the newly availabel memory? That knob should probably be in oracle. If, as you say, this system just runs oracle then any of the kernel tuning knobs, unless completely wrong, will only have a minor effect.
As you increase dbc_xxx you can and should increase the Oracle SGA cache size to compensate otherwise you will increase the read IO load (as seems to happen)

In a sense the dbc cache is an 'easy', forgiving cache, lowering it shuld only be done if you fully understand the oracle tuning.

>> I'd saw sar -ud, for a long time, that was showing %idle about 40% all the time, and sometime disk consume(%busy) very high

Hmm, initially you wrote high usage: 90%.
Is this 40% the result of lowering

>> I saw that bad SQL instructions was responsable for 70% of server workload

Ah! Now that is good work. That's where you should focus the energy.

>> but I'm using and third-part application, and I can't change sql instructions; So I need to "harvest" my server, and let it works as best as it could.

I beg to differ.
1) A good oracle resource can still seriously influence the SQL execution, for the better hopefully, without touching the actual SQL code.
1A) add or deleted indexes
1B) add execution plan templates
1C) setting oracle params like CURSOR_SHARING
1D) setting oracle params to influence the pga / sort spaces

2) a good 3rd party application will appreciate your observation and work with you to improve the situation. Some have ways/repositories to dynamically change the query.

> Oracle SGA configuration is the following:
> sga_max_size=2281701376

Could be ok, but may need 3GB instead of 2 on this 4GB box.

>> db_cache_size=1073741824

That's the knwo to increase when decreasing hpux dbc params

>> db_writer_processes=1
>> disk_asynch_io=false

Thagt seems restrictive and could create a bottleneck


>> pga_aggregate_target=268435456

That may be low for the application.

>> max_dispatchers=100

That's just plain wrong!
It should be 5 or 10, or not be used at all.
CHeck with your dba! (oh, you are the dba? :-)

>> max_shared_servers=50

Could be ok, but verify.

>> I'm not using swap (paging out). I think that It's very good!

Yes and no... there could/should be a little pressure otherwise you are not allowing the system to exploit the physical memory. Lowering dbc restricted physical memory usage for a good (cahing) purpose. Now find something else (in oracle0 to use it for a better purpose. The memory is there to be used, to help you get performance and load up the CPU instead of waiting for the disks.


Good luck,
Hein.