One server is fast, the other slow...kernel parameters?

[Daniel D.B. Shafer Sr.]

We have two servers - both running Oracle 10G.
One server (rp4440) runs great, the other (rp5470) runs horribly.

Kernel parameters are the suspect according to DBAs. Oracle tuning / SQL queries are the suspect according to sysadmins.

Probably, this same war has been waged many times before. So, we are looking for any help if it is possible.

Below is a list of kernel parameters that are different between the two boxes.

kernel parameter rp4440-20G rp5470-16G
STRCTLSZ 1024 0
dbc_max_pct 10 8
dbc_min_pct 5 4
ksi_alloc_max 32768 75264
max_thread_proc 1024 3000
maxdsiz 1073741824 3221225472
maxdsiz_64bit 2147483648 3221225472
maxfiles 4096 2663
maxfiles_lim 8192 2663
maxssiz 134217728 268435456
maxtsiz 67108864 1073741824
maxtsiz_64bit 1073741824 3221225472
maxusers 32 1080
maxvgs 10 64
ncallout 7200 16496
nclist 612 17380
ncsize 39936 35840
nkthread 7184 16480
nproc 4096 9408
nsysmap 8192 18816
nsysmap64 8192 18816
semume 10 64
semvmx 32768 32767
shmmax 14680064000 3221225472

Hopefully, this is enough info to get us started.

Thanks in advance for your help.

[Patrick Wallek]

>>One server (rp4440) runs great, the other (rp5470) runs horribly.

What exactly do you mean by this? The same task on one completes faster than on the other?

What is the configuration of the servers? # or processors in each? Amount of RAM? Type of disk?

The processors on the rp4440 are significantly faster than the rp5470. If the rp4440 has more RAM then this could make a difference as well.

Have you checked your 'swapinfo -tam' output on the rp5470 to make sure you aren't paging out?

[Daniel D.B. Shafer Sr.]

swapinfo -tam output...
rp4440
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 10240 0 10240 0% 0 - 1 /dev/vg00/lvol2
reserve - 5358 -5358
memory 15697 2133 13564 14%
total 25937 7491 18446 29% - 0 -

rp5470
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 2048 0 2048 0% 0 - 1 /dev/vgroot/lvswapA
dev 4000 0 4000 0% 0 - 0 /dev/vgroot/lvswapB
reserve - 6048 -6048
memory 12866 4338 8528 34%
total 18914 10386 8528 55% - 0 -

[Daniel D.B. Shafer Sr.]

Configuration:
rp4440
20G RAM
4 processors

rp5470
16G RAM
4 processors

[Court Campbell]

This is pretty vague. You might look into other things like bad disks, lan connection, san zoning, etc. I once had a server that responded very slowly and the issue ended up being that the server was plugged into a port that was statically defined as 10Mb. Other things to consider are your vxfs mount options for data files. If you don't set the options you will be double buffering which could cause delays. Good luck.

[Court Campbell]

Also, is the installation of 10g and datafiles on the server or an external disk array?

[Daniel D.B. Shafer Sr.]

Oracle installation
rp4440
Oracle (binaries and data) is on EVA5000

rp5470
Oracle binaries on root disk
Oracle data on EMC storage

[Tim Killinger]

I work with Daniel on these systems and here is a clarification.... the 5470 runs a same piece of code much more slowly today on 10g than it did on oracle 8i last week. The 4440 runs much faster today on 10g than it did on 8i last week. Since the app is the same and DB size is fairly close, can anyone link any of the different kernel params between the two boxes with the performance differences between the two boxes?

[Tim Nelson]

Typical kernel tunes for DB systems

Set dbc_max_pct to low value 5%, e.g. 500mb or so, give the RAM to Oracle SGA.

set shmmax to larger value to accomodate larger SGAs.

Your rp5470 is set to about 3GB ? What are the sga for your oracle instances ?

Also, are all the system patches required by oracle installed ?

As Court is eluding to there are a couple dozen reasons here. Start posting some performance data (cpu, mem, disk), and/or review Oracle stats so we can get an idea of what haystack the needle is in...

[Court Campbell]

As Tim noted you should probably make your shmmax higher on the rp5470. If you have shmmax relatively small compared to the sga size you can run into issues. shmmax puts a cap on how large any shareed memory segment can get. You could get into a scenario where you have to allocate quite a few segments. If I remember correctly oracle has a limit,or should I say recommendation, to the number of segments you should have. I would say either set it to the size of your sga/pga or to the max amount of physical ram you have in the box.

[Court Campbell]

>> the 5470 runs a same piece of code much more slowly today on 10g than it did on oracle 8i last week.

I would expect that. Nearly every time you do an oracle upgrade you end up tuning sql. You shouldn't expect the execution plans to get better just because you move to 10g. In some cases queries actually get a lot worse. The oracle optimizer has a nice ring to it, but it does not always optimize.

[Bill Hassell · Accepted solution]

You may want to check your indexes for balance. You can use EXPLAIN in a very slow SQL procedure to see if the rows require a search partial, probably indicating highly unbalanced search trees. The number of disk records (for the same query) is a good indication that the databases are not the same internally.

It is hard to convince DBAs that kernel parameters do not make Oracle run fast. Most kernel parameters are limits which processes will hit and report that they need more. Poorly written processes will just crash. But there is no kernel parameter called GOFAST.

There are several parameters that do not make much sense. maxtsiz (and maxtsiz_64) relate to the largest size of the text area, the unchanging instructions in a program. Setting to a very large value (1073741824) means that you have programs with millions of lines of code (unlikely). Change maxtsiz and maxtsiz_64 to the same as the rp4440.

Parameters nproc, nkthread, nclist, ncallout, nsysmap/64, max_thread_proc are all significantly larger in the slow machine. This is very likely due to the pseudo parameter maxusers. maxusers has nothing to do with the number of users that can connect. Instead, it is a formula adjuster that causes more problems than it is worth. You can simply change maxusers back to 64 and the numbers should match up more closely. Or you can check each of the parameters using SAM to see if there is a number or a formula. Then replace the formula with a more reasonable number.

Getting the parameters to a more normal state will comfort the DBAs, and then they can go back to running Oracle stats to see what is really happening between the machines.