topic Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct) in Operating System - HP-UX

Perforamnce issue... (dbc_min_pct and dbc_max_pct)

unimedriopreto — Fri, 06 Jan 2006 06:22:04 GMT

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

Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct)

Keith Bryson — Fri, 06 Jan 2006 06:30:22 GMT

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

Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct)

unimedriopreto — Fri, 06 Jan 2006 06:46:47 GMT

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.

Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct)

Keith Bryson — Fri, 06 Jan 2006 06:50:15 GMT

You can leave MIN_PCT as it is.

Thanks - K

Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct)

Eric Antunes — Fri, 06 Jan 2006 06:53:12 GMT

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

Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct)

unimedriopreto — Fri, 06 Jan 2006 07:13:29 GMT

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).

Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct)

Victor BERRIDGE — Fri, 06 Jan 2006 07:14:32 GMT

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

Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct)

Yogeeraj_1 — Fri, 06 Jan 2006 07:31:04 GMT

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

Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct)

unimedriopreto — Fri, 06 Jan 2006 07:49:37 GMT

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.

Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct)

Hein van den Heuvel — Fri, 06 Jan 2006 08:20:46 GMT

>>> 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.

Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct)

Eric Antunes — Fri, 06 Jan 2006 09:02:48 GMT

Hi again,

nproc and other parameters are better controlled has a formula because if you change the basic parameters, the depending ones will also get changed. For nproc the formula (20+8*MAXUSERS) is suitable for Oracle OLTP environement. But your value seems enough.

Your db_cache_size is very very big, are you sure you need it this huge?? Try to reduce it to the half and see if you get better performance...

PS: Online JFS enables you to extend online (without rebooting and entering single mode) the volume sizes. It also allows you to use advanced tunning options for file systems such as mincache, etc...

Best Regards,

Eric Antunes

Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct)

Eric Antunes — Fri, 06 Jan 2006 09:25:29 GMT

About your db_cache_size, execute the following SQL statements and check the ratios:

select (bbc.total_waits*100/(cg.value+dbg.value)) "Buff busy ratio ind. <= 0,007"
from v$system_event bbc,
v$sysstat cg, v$sysstat dbg
where bbc.event='buffer busy waits'
and cg.name ='consistent gets'
and dbg.name='db block gets';

select round(((1-(sum(decode(name, 'physical reads', value,0)) /
(sum(decode(name,'db block gets', value,0))+
(sum(decode(name, 'consistent gets', value, 0))))))*100),2)
|| '%' "Buffer Cache Hit Ratio > 95%"
from v$sysstat;

select round(100 * sum(getmisses) / sum(gets), 2) "Misses Pct < 5%"
from v$rowcache
having sum(gets) > 1000;

select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio"
from v$rowcache;

Also this one in sqlplus (in Oracle 8 this was a svrmgrl command only) and post the results here:

SQL> connect / as sysdba;
Connected.
SQL> SHOW SGA;

Best Regards,

Eric Antunes

Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct)

unimedriopreto — Fri, 06 Jan 2006 10:35:21 GMT

Hi Eric.
I'm sending values from sqls that you told me to execute.
I think that returned values are ok.
I'm curious about what you say about db_cache_size. A bigger value will not improve performance?
My buffer cache hit is higth, if I increase db_cache_size, Will not I increase a little more this number? Isn't It better?

Thanks.

Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct)

Eric Antunes — Fri, 06 Jan 2006 11:00:52 GMT

Hi again,

"I'm curious about what you say about db_cache_size. A bigger value will not improve performance?
My buffer cache hit is higth, if I increase db_cache_size, Will not I increase a little more this number? Isn't It better?"

No. There is a point at which you don't get any performance value just increasing the SGA. There is much more things to check than SGA:

- sort_area_size=4194304. You must be aware that this is a EACH SESSION value (that's why you need that huge SGA...)

- Log swichs/checkpoint (what is the rate? Set "log_checkpoint_alert = true" to analyse this in the alert_.log)

- Tablespace Fragmentation (How is it? OEM Tablespace Manager)

- Who are the eavy guys and what are they doing?

"select substr(s.username,1,20) "User Name",
s.osuser "OS User",
s.status "Status",
lockwait "Lock Wait",
substr(s.program,1,30) "Program",
substr(s.machine,1,15) "Machine",
substr(du.user_name,1,20) "Apps User Name",
s.logon_time "Connect Time",
p.program "P Program",
si.physical_reads "Physical Reads",
si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets",
si.block_changes "Block Changes",
si.consistent_changes "Consistent Changes",
s.process "Process PID",
p.spid, p.pid, s.serial#, si.sid
from sys.v_$sess_io si, sys.v_$session s, sys.v_$process p, system.dba_usersess du
where s.username is not null and
si.sid(+)=s.sid
and p.addr(+)=s.paddr
and (si.consistent_gets > 10000 or si.block_gets > 10000)
and du.sid=s.sid
order by si.consistent_gets+si.block_gets desc"

- Chainning? What is the ratio?

- Etc...

Best Regards,

Eric Antunes

Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct)

Eric Antunes — Fri, 06 Jan 2006 11:07:10 GMT

Hi,

From the last query remove the du columns since system.dba_usersess it is one of the customized views I have...

Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct)

Hein van den Heuvel — Fri, 06 Jan 2006 11:20:28 GMT

Eric,

I beg to differ with many, if not most, of your points and approaches. Send me an Email if you want to discuss a little bit,

>> "I'm curious about what you say about db_cache_size. A bigger value will not improve performance?
My buffer cache hit is higth, if I increase db_cache_size, Will not I increase a little more this number? Isn't It better?"

I believe so. Even if your hit rate is 99,9%, but there are still hundreds of reads per second, then possibly more cache is needed and/or better query plans.

For further reading check out documents like: http://www.hotsos.com/e-library/abstract.php?id=6

Eric> No. There is a point at which you don't get any performance value just increasing the SGA.

Hmmm, that's hard to imagine, unless the memory can be used better elsewhere (processes, dbc)

Eric> There is much more things to check than SGA:

Absolutely

Eric> - sort_area_size=4194304. You must be aware that this is a EACH SESSION value (that's why you need that huge SGA...)

Per process yes, from the SGA... NO. From the PGA_aggregates.
Please be sure to read up on the interaction of the individual setting line sort_area_size vs the global pga_aggregate-target.

Eric> - Log swichs/checkpoint (what is the rate? Set "log_checkpoint_alert = true" to analyse this in the alert_.log)

Right. Too often we see very small redo log forcing early checkpoint. But here MTTR is used which does put a slightly different spin on things.

Statspack data would be the best to understand where to focus next, but that quickly turns in to 'real work' vs 'trying to help a little'.

Hope this helps someone somewhere,
Regards,

Hein.

Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct)

unimedriopreto — Fri, 06 Jan 2006 11:35:45 GMT

Hi Hein, before all, I'm very thankfull for your help.

I'd altered dbc_min and max_pct yesterday, before that, vmstat shows that I had 50 Mb of free memory, and swapinfo -tam, shows that I was not using swap, I did not change oracle memory parameters since that.

So, should I increase dbc_min and max_pct, lets talk... to 5 and 10... And increase sga max size to 3Gb? I did that another time and saw that system become using swap. (It's better that oracle's process uses swap than they have a little memory configuration?).

Back to dbc_max and min_pct...
Excuse me for that I wrote... When I openned this thread, sar command shows that %iddle was 10% (90% work). But 2 hours agor I discovered that one bad sql instruction was executing and consuming my server (an update of an application that was bad wrote).
Now, after application's correction, %iddle comes to 65% (sar -ud 3 30), so, I think that dbc_min and max_pct did not influence (too much) in system degradation. I'll increase to 5 and 10% and still looking system performance.

I could increase db_cache_size, but I'm still confusing about swap use for oracle processes.
I could increase pga_aggregate_target too, but I'll need to tune memory between db_cache_size and pga_aggregate_target.

About >> max_dispatchers=100.
My system always use 5 dispatchers, the max_dispatchers parameters will not influence in system performance... Will it??? (ok... if a huge number of connections are made at same tame.... It probably will not occur...).

>> max_shared_servers=50
It is setted to 50 becouse one application has an bug, that causes enqueue... I asked for correction but It not comes until now... so I increase this number to free another applications to run. (it was setted to 20 and my database crashes)...

About add and delete indexes, I'd passed an explanation to applications providers, showing what indexes are needed and what are not used... I'm waiting theyer response...

I'll take a closer look at executions plans.

My biggest doubt is about swap...

Thanks for response.
And excuse me for my long response time.

Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct)

unimedriopreto — Fri, 06 Jan 2006 11:46:58 GMT

Eric this is executions results...
SQL>
SQL> select (bbc.total_waits*100/(cg.value+dbg.value)) "Buff busy ratio ind. <= 0,007"
2 from v$system_event bbc,
3 v$sysstat cg, v$sysstat dbg
4 where bbc.event='buffer busy waits'
5 and cg.name ='consistent gets'
6 and dbg.name='db block gets';

Buff busy ratio ind. <= 0,007
-----------------------------
0,000457823537197533

SQL> ;
SQL>
SQL> select round(((1-(sum(decode(name, 'physical reads', value,0)) /
2 (sum(decode(name,'db block gets', value,0))+
3 (sum(decode(name, 'consistent gets', value, 0))))))*100),2)
4 || '%' "Buffer Cache Hit Ratio > 95%"
5 from v$sysstat;

Buffer Cache Hit Ratio > 95%
-----------------------------------------
96,66%

SQL>
SQL> select round(100 * sum(getmisses) / sum(gets), 2) "Misses Pct < 5%"
2 from v$rowcache
3 having sum(gets) > 1000;

Misses Pct < 5%
---------------
0,75

SQL>
SQL> select (1-(sum(getmisses)/sum(gets))) * 100 "Hit Ratio"
2 from v$rowcache;

Hit Ratio
----------
99,2453743

Attached will be your last requisition about who are executing what....
but I need to do some modifications, becouse I do not have one table.

Tanks...
Jefferson.

Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct)

LiPEnS — Fri, 06 Jan 2006 11:47:58 GMT

Hi
Pleass post result of:
#swlist |grep OnLineJFS
and
#mount -v

Regards

LiPEnS

Re: Perforamnce issue... (dbc_min_pct and dbc_max_pct)

Eric Antunes — Fri, 06 Jan 2006 11:55:05 GMT

Hein,

Be confortable to desagree but then Oracle also desagrees with you until Oracle 8i: read Metalink Note 100709.1 (Top 8 init.ora Parameters Affecting Performance) and tell me something about the recomendations. They are wrong or 9i is a revolution??

About Buffer Cache, better query plans is definitively the way to go. Try to solve with more buffer cache a full scan in a millions records table!

From the above refered note:

"...If the value is set too high, swapping will began to occur on the OS and the
system may come to a halt."

About SGA:

"...We have discussed having the Shared_Pool_Size set too low, but adverse effects
may comes from setting the Shared_Pool_Size too high as well. Having the
shared pool set too large increase the probability of latch contention when the
database needs to find a free peace of memory to load a new statement.
You can query v$sgastat to show the available free memory. This will tell you
memory is being wasted. As an example, let's look at the following problem:

select name, bytes/1024/1024 "Size in MB"
from v$sgastat
where name='free memory';

You should see output similar to the following:

NAME Size in MB
Free memory 39.6002884

What this return would tell you is that there is 39M of free memory in the
shared pool, which would mean that the shared pool is being under utilized.
If the shared pool was 70M, over half of it would be under utilized. This
memory could be allocated elsewhere..."

Elsewhere is of course OS, applications, etc...

About sort_area_size:

Yes, it is outside the SGA but it is memory used by each user process for sorting...

From Note:

"...The SORT_AREA_SIZE is an oft-misunderstood init.ora parameter. Many believe
that this parameter is an amount allocated for the users as a whole in terms
of sorting. Not true, the SORT_AREA_SIZE is what Oracle allocates per
user process for sorting data. Unlike the two previous parameters, the
SORT_AREA_SIZE is outside of the SGA..."

Best Regards,

Eric Antunes