Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
Showing results for 
Search instead for 
Did you mean: 

Performance issue on oracle/app server

joe clark

Performance issue on oracle/app server

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
Honored Contributor

Re: Performance issue on oracle/app server


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

Sar is another good monitoring tool.
See man sar

If you can spell SysAdmin then you is one - anon
Bill Hassell
Honored Contributor

Re: Performance issue on oracle/app server

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

Bill Hassell, sysadmin
Tom Geudens
Honored Contributor

Re: Performance issue on oracle/app server

If you have the time to spare, look through the following document

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

Hope this helps,
Tom Geudens
A life ? Cool ! Where can I download one of those from ?
Honored Contributor

Re: Performance issue on oracle/app server

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,
Patrick Wallek
Honored Contributor

Re: Performance issue on oracle/app server

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
Occasional Advisor

Re: Performance issue on oracle/app server

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,
Dennis J Robinson
Frequent Advisor

Re: Performance issue on oracle/app server

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:

(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

(ADDRESS = (PROTOCOL= TCP)(Host= superdome)(Port= 1521))

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

Good luck:
You know the drill