hp9000/800 with oracle 8.0.6 slow performance

[Oskar Teran]

hello people,
I just started working at a place that has an hp-ux 9000. It has 1G of RAM, one 120MHZ processor and I believe three 5G drives. We have a 3Gig oracle database on it running Lawson software. The server is really slow! From what I could tell, they get consultants to come and look at the machine only when the problem is very bad. (work can't get done) I think no one has ever fine tuned this machine for the environment it is in. So basically, I've really got my work cut out as the new guy.
Where is a good place to start as far as doing research for performance tuning this machine for the database. I see oracle or lawson processes on the machine that take 50 to 60% of the cpu cycles....regularly!
For now I'm doing research on the oracle side of things but I'd also like to know what I should start reading for the unix side as well...
thank you,
Oskar

[James R. Ferguson]

Hi:

Here are some documents to help guide you in measuring and determining where (CPU, memory, I/O) performance is poor.

Document #KBRC00000947 (System Performance Tuning Guide)

Document #S3100002312A, S3100002312B, and S3100002312 (Sys Adm: determining the cause of system performance problems).

To understand the tunable kernel parameters you can change to alter performance, see:

http://docs.hp.com/hpux/onlinedocs/os/KCparams.OverviewAll.html

And, for some some tips for using individual kernel parameters, see document #KNC061098001 (SYS ADM: HP-UX kernel variables and performance tuning).

The 'glance' performance monitor is a superb realtime tool to use for performance monitoring. It is available on a trial basis from the Application CDROM(s) and is worth its permanent license fee.

...JRF...

[Bill Hassell]

I think you won't find much to fix in HP-UX. The CPU is busy because the applications are consuming huge numbers of instructions. With such a slow single processor, there is little to do but to reduce the amount of work being done at the same time. The design of the database and the SQL code used write/read data elements has a massive effect on performance.

Oracle and Lawson are high end applications and can be tuned to take advantage of huge amounts of RAM (gigabytes) and multiple processors. I think you'll see all your performance problems disappear if you switch your computer to an L-class machine with 2 or 3 processors.

[Volker Borowski]

Hi oscar,
how is the IO usage ? If there is really nothing on IO, it has to be application.
If it is a mixture of high io and high cpu, I would try to take a look at the distribution of the tablespaces. data and index on separate disks, as well as TEMP if you do sorts and ROLLBACK if you do updates.
How is your redo logging doing. Do you get "checkpoint incomplete" messages in the alert file ? (-> increase redolog size)
This is all general, but I would try to find out, if you start the system, who starts the task that eats the cpu, and then sqltrace it.
May be you find an index to speed it up.
Good hunting.
Volker

[Dave Chamberlin]

With only a few disk drives, you will probable have disk contention running Oracle. Things you can do to help are to add more disks to spread the IO out; many small disks will work better than a couple of large ones. Also make sure that indexes have thier own tablespaces and that those are on separate disks than the tables.

[Rita C Workman]

Well, I'm just gonna throw out what is the first couple things I look at.
I turn on my Glance and start watching....next I look for just how many lookups do a full table scan or on an index??? The more full table (and I have learned the hard way..vendors do alot of this so you call them back "to tune".) lookups the more your systems slows as resources are hit. Even on a small system as yours....if your lookups are hitting every line of the database, then your going to feel the slowdown while the request reads EVERY record.
If Glance (or sar) is showing that my kernel is set correctly, then I make myself 'very visible' to DBA's or Vendors and I start asking for stats on the code, especially the querries of the db.

Just my 2cents,
/rcw