Performance issue on oracle/app server

[joe clark]

We are constantly seeing a high load average(3-6.x) on an N4000,(HP-UX11i) running egate application with oracle, we have the /oracle on the same drive as 3-u directories, that is the most used disk(c7t0d5)
09:20:01 c1t6d0 1.26 1.31 2 10 32.56 18.41
c2t6d0 1.23 1.24 2 9 33.12 22.64
c7t0d5 6.06 0.55 173 1486 4.72 0.59
c5t8d0 1.01 0.50 1 21 4.20 19.95
c5t9d0 0.55 0.50 1 10 5.03 9.42
09:40:01 c1t6d0 0.96 1.55 2 9 7.71 14.03
c2t6d0 0.88 1.53 2 8 7.51 13.59
c7t0d5 11.84 2.68 252 2822 6.17 0.85
c5t8d0 0.77 0.52 2 22 3.12 9.42
c5t9d0 0.51 0.50 1 11 4.93 9.46
10:00:00 c1t6d0 0.43 0.50 1 5 2.30 9.30
c2t6d0 0.46 0.50 1 4 2.33 9.76
c7t0d5 14.46 0.90 280 3341 5.24 0.91
c5t8d0 0.77 0.50 2 22 2.95 9.03
c5t9d0 0.51 0.50 1 11 4.85 9.37
10:20:00 c1t6d0 1.72 1.01 3 21 12.64 19.51

I plan to move oracle to a seperate disk, also
looking at nproc and nfile, they may be set to high, maxdsiz is set to 4 gig, and we are running with swapmem_on. We are also running a couple of general bundles behind, didn't know if there were major performance patch fixes recently. Any ideas would be appreciated.
Thanks in Advance

[Paula J Frazer-Campbell]

Joe

If you have glance run it and monitor what is going on.

Sar is another good monitoring tool.
See man sar


Paula

[Bill Hassell]

If you have a patch bundle from the last 6 months, you should be OK. Now for the bad news:

Tweaking the kernel doesn't help applications very much at all. The vast majority of performance enhancements for database apps revolves around better SQL code and better use of RAM by the database processes. For Oracle on 64bit HP-UX, start by expanding your SGA to a few gigabytes. Use this area wisely to provide sort work areas as well as buffering for data. This means that a system with only a couple of gigabytes of RAM won't work well. You are trading RAM for massive disk activity, which means: slow. Think 4-8 Gbytes for RAM.

>> looking at nproc and nfile, they may be set to high <<

Setting them to 2-3 times large will do nothing for performance (just uses a bit more RAM for the kernel) and reducing them by 1/2 will probably prevent some of your users from running anything. If your application needs 19,000 files open at the same time, then nfile will have to be 20,000 or so. This is a go/nogo parameter. Same for nproc. Now you can reduce nproc to 20 or 30 and your system will run like a champ (because no one can login). 8-)

>> maxdsiz is set to 4 gig <<

This is simply a fence that you could set to 8 gig if you want...it just kills programs that grow too large. However, maxdsiz is for 32bit programs only so an ordinary program can only malloc 940 megs--maxdsiz will not improve that. With chatr and a quick read through the proc and mem management documents in /usr/share/doc, you can allow programs to address 1750 megs, and with restrictions, close to 3 gigs--but no more. That is an inherent limitation of 32bit programs.

Shared memory is also limited in a 32bit application, but also limited to the largest segment due to memory fragmentation. (this isn't an issue with 64bit programs)

>> and we are running with swapmem_on >>

All this does is to reduce the amount of swap space you need.

[Tom Geudens]

Hi,
If you have the time to spare, look through the following document http://h21007.www2.hp.com/dspp/files/unprotected/devresource/Docs/TechPapers/UXPerfCookBook.pdf

It took care of most of my questions, it might help you. Great reading too by the way :-).

Hope this helps,
Tom Geudens

[Wodisch]

Hello Joe,

if Oracle runs slow on *good* hardware, it is usually configured/adminstrated "sub-optimally", i.e. the SGA is way too small, and the data-files and redolog-files are located on the same physical diskdrives...
Have a look for your "init*ora" file, and check for parameters like:
- db_block_buffers (as many as possible)
- db_block_size (less then 8192 is silly)
- shared_pool_size (less then 100MB is useless)
- sort_area_size and retained_sort_area_size (more than 4MB helps)
- log_buffer_size (1MB or so - never 32K!)

Just my $0.02,
Wodisch

[Patrick Wallek]

Something to keep in mind as well is if you applied the HP-UX tuned kernel parameter set for databases, then you MUST check your timeslice setting. The tuned parameter set sets it to 1. You DON'T want that. You should set it back to 10, recompile the kernel and reboot the machine.

[Lionel MAZE]

Hi Joe,

With Oracle 8i and higher, if your optimizer_mode is choose, you must keep your database statistics current.
Use the DBMS_STATS package to gather statistics.
Becarefull with the estimate_percent argument, the execution plan could be completely wacky after calculation.

Hope this helps,
Lionel

[Dennis J Robinson]

Your main problem lies in the high load avg of 3.0-6.0.

This number indicates the amount of runnable processes waiting in the run queue. Waiting means they are not running, when they would ideally like to. The wait time can be UP to the value indicated in the /stand/system "timeslice" parameter or 1/10 sec for most HP installations.

Increase number of CPU's on the box, the load avg will go down and the work will get done faster.

Software solution which needs to be considered is have a real SQL tuner come out there and look at the SQL's. First of all what is wasting most of the CPU%, Oracle or the application? Top or glance can tell you this.

If it is Oracle, then you have the sql tuner (someone like a Johnathan Lewis, one of the TUSC guys, or one of my guys), tune SQL calls to reduce LOGICAL I/O's, hard parsing, unnecessary casting of parameters, unnecessary indexes, etc.

Also if database and app are on same box ( which is good move if you have large server ), it is best to not use UNIX sockets but UNIX pipes (BEQ local connection in tnsnames.ora).

Here goes example of local BEQ connection tnsnames.ora entry:

HPGURUDB =
(Description =
(Address =
(Protocol = BEQ)
(Program = /dba/u01/app/oracle/8.1.7/bin/oracle)
(ARGS = '(Description=(local=YES)(Address=(Protocol=BEQ)))')
(ENVS = 'ORACLE_HOME=/oravl01/oracle/8.1.7,ORACLE_SID=HPGURUDB')
)
(Connect_data = (SID = HPGURUDB)
)
)

vs sockets

HPGURUDB=
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host= superdome)(Port= 1521))
(CONNECT_DATA = (SID = HPGURUDB))
)

Even if database is compeletly contained within physical memory it is possible to increase database performance over 10x.

Good luck: hpguru@optimalss.com