CPU memory requirements

[Dave Forber]

We have an RP8400 running our Oracle payroll app on HP-UX 11i. The box is running at 100% CPU and memory, going well into swap.

I'm in the process of getting the license key to enable more icod CPU's (8 in the box, 4 currently active/licensed) to deal with this side, but I have memory questions if someone could advise please?

As said, memory is already used up so obviously we need more anyway, but.... When I enable the CPU's, standard practise is to increase memory too as they can go hand in hand. I've got to recommend how much CPU power we need, but I don't know how much memory to allocate per new CPU. Any ideas? On top of this I can then estimate how much more Oracle itself needs. I'll then obviously need to expand swap space too!

Thanks.

[Fred Ruffet]

Please have a look at my answer in this post :
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=750342

Regards,

Fred

[Eric Antunes]

Hi Dave,

You are doing lot of swaping just with Oracle Payroll or do you have more modules? You must have a tuning issue... Maybe SQL, maybe parameters, ...?

How much physical memory do you have?

What values do you have for those kernel parameters:

dbc_max_pct = ?
dbc_min_pct = ?
ninode = ?
shmmax = ?
swchunk = ?

What values for init.ora parameters:

shared_pool_size = ?
log_buffer = ?
db_block_size = ?
db_block_buffers = ?
sort_area_size = ?

Best Regards,

Eric Antunes

[Patrick Wallek]

How much RAM do you currently have? How much are you swapping? The output of 'swapinfo -tam' would be helpful.

[Bill Hassell]

Oracle runs best with lost of RAM...minimum 4Gb but DBAs will be very happy if they can bump their SGA up to several Gb, 4-6 Gb if possible. Since your using swap space, check how often with vmstat. The po (page out) field is the most important. If the number is 2 or 3 digits, then you definitely 2x to 4x more RAM. Although RAM is seen as a costly option, it has to be weighed with response time costs. If users are willing to wait for several minutes for each transaction then you can run with small RAM (4Gb or less).

[Eric Antunes]

Bill,

Only bad DBA's ask this kind of memory for SGA... Good ones read the Metalink notes about "Top init.ora Parameters Affecting performance" and don't ask the server just for them...! :)

[TwoProc]

Review the dbc_max_pct parameter in the kernel.
Is this still at the default of 50%?

How much ram do you have?

How big is your SGA?

How big a payroll (that is, number of people you're processing for) ?

One more thing, if you're allocation more ICOD processors - I don't think you're going to get more ram from what I was told when I was investigating the possibility of using ICOD as a solution. I was told that all the ram from all cells are available even when the CPU's are not in use - providing they are in the partition already.

An fyi - you really should be able to run Oracle Payroll (alone I gather from your email) with four processors just fine. I did it for years with a box slower (in class and processing power) than yours. But, that depends on how many people you're processing payroll for are you doing it for 1,000 or less? I'd think you could do easily 1,000 or so with what you're talking about.

Have you run a statspack to see what's holding you up?

I'd be willing to bet that what's killing you is just a few things - and that you could tune 6 or 7 things (kernel, SGA, and five or six queries ) and you'd probably get a lot closer to your solution.

Also, one last thought. Do you have both the forms server and the database server on the same box? If so, you might have some runaways f60webmx processes that need to be killed off...

[Dave Forber]

Cheers for the replies so far. Answers to your questions (our dba informs as we are using Oracle 9i db_block_buffers and sort_area_size are no longer used options, and are replace with the two provided):

Current physical memory = 8Gb
dbc_max_pct = 10
dbc_min_pct = 5
ninode = 18188
shmmax = 8589934592
swchunk = 2048
shared_pool_size = 524288000
log_buffer = 1048576
db_block_size = 8192
db_cache_size = 1258291200
pga_aggregrate_target = 524288000

I'll provide the swapinfo details next time the payroll is run (Tuesdays).

The payroll run caters for appx 80,000 employees each week.

Forms and db are seperate servers.

[Eric Antunes]

ok,

About ninode, did you checked if you are using those 18188?

#sar -v 15 1000

I'll start with 2048 and see if it overfloes...

About swchunk, increase it to 4096

Oracle:

shared_pool_size does not seems exagerated.

db_cache_size seems huge to me: I'll ask DBA's to try to decrease it to half the value and see if there are improvements.

Finaly, ask DBAs to execute the following query:

select pc.value*100/decode(ec.value,0,1,ec.value) "Parse/execute ratio < 20"
from v$sysstat ec, v$sysstat pc
where ec.name='execute count'
and pc.name='parse count (total)'

Eric Antunes

[Eric Antunes]

One more thing:

When the Payroll process will be running ask DBA's to run the following queries and attach here the results:

-- Top Sessions:

select s.username "User Name",
s.osuser "OS User",
s.status "Status",
lockwait "Lock Wait",
s.program "Program",
s.machine "Machine",
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",
p.spid, p.pid, s.serial#, si.sid
from sys.v_$sess_io si, sys.v_$session s, sys.v_$process p
where s.username is not null and
si.sid(+)=s.sid
and p.addr(+)=s.paddr
order by status,si.consistent_gets+si.block_gets desc

Here identify sessions executing for more than 1 minute and with huge values at "Consistent Gets" column. Those are the bad performers...

-- Top SQL:

select sql_text, executions, disk_reads, loads, rows_processed, optimizer_mode, module
from v$sqlarea
where disk_reads >= 1000
order by disk_reads desc

Here identify modules (programs) with huge values (more than 100,000) at disk_reads column and few executions: those are typically bad SQL programs...

Best Regards,

Eric Antunes