topic Re: Performance HP-UX in Operating System - HP-UX

Performance HP-UX

Tal Rosenberger — Sun, 22 Feb 2004 00:59:54 GMT

Hi,

We have performance problem with our Border Control / Homeland Security application on three HP-UX/ Oracle RDBMS/ Oracle Reports Server.
Two of them are in cluster (service guard) and RAC 9i and the third is connected with Oracle Replication. We are using HPOV as well. We have both batch and online processing.

1. I'm looking for recommendations for performance tuning, what can affect the performance (HP-UX, Oracle RDBMS, Oracle Reports Server, Service Guard, and Oracle RAC 9i).
2. I'm looking for tools both shareware and others to measure and analyze performance / resources.

Regards,
TR

Re: Performance HP-UX

Lethuillier — Sun, 22 Feb 2004 17:35:14 GMT

Consider the following areas for tuning. The order in which steps are listed needs to be maintained to prevent tuning side effects. For example, it is no good increasing the buffer cache if you can reduce I/O by rewriting a SQL statement.
Database Design (if it's not too late):
Poor system performance usually results from a poor database design. One should generally normalize to the 3NF. Selective denormalization can provide valuable performance improvements. When designing, always keep the "data access path" in mind. Also look at proper data partitioning, data replication, aggregation tables for decision support systems, etc.

Application Tuning:
Experience showed that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours.

Memory Tuning:
Properly size your database buffers (shared_pool, buffer cache, log buffer, etc) by looking at your buffer hit ratios. Pin large objects into memory to prevent frequent reloads.

Disk I/O Tuning:
Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation, etc.

Eliminate Database Contention:
Study database locks, latches and wait events carefully and eliminate where possible.

Tune the Operating System:
Monitor and tune operating system CPU, I/O and memory utilization. For more information, read the related Oracle FAQ dealing with your specific operating system.

(exerpt from http://www.orafaq.com/faqdbapf.htm)

Hope this helps,
Thierry

Re: Performance HP-UX

Lethuillier — Sun, 22 Feb 2004 17:38:54 GMT

Here are some tools you can use and appropriate hints and tips ;

- To check your hp-ux kernel parameters ;

http://h21007.www2.hp.com/dspp/tech/tech_TechDocumentDetailPage_IDX/1,1701,1608,00.html

- Take a look at HP technical resources for tools and hints ;

http://www.hp.com/products1/unix/java/infolibrary/tools/index.html

Notice that a java program can harm your system performances.

- You can also use STATSPACK for 9i, the successor of Utlbstat/Utlestat

http://www.dba-oracle.com/art_builder_statspack_book.htm
http://www.geocities.com/alexdabr/

Hope this helps,
Thierry

Re: Performance HP-UX

Lethuillier — Sun, 22 Feb 2004 17:52:11 GMT

Forgotten links ;

http://www.rampant-books.com/book_2003_1_oracle9i_sga.htm

http://www.rampant-books.com/teaser_sga_bhr_statspack.htm;

Re: Performance HP-UX

SteveKirby — Mon, 23 Feb 2004 02:32:39 GMT

I have been able to hadle everyone performance problem I have encountered with 4 tools:
top
sar
iostat
Oracle

top gives me CPU,process usage
sar lets me look at the disk (Note: Sometimes, because the disks are abstracted to the OS you need Disk Array specific tools)
iostat gives a slightly different look at io
Oracle lets me look at the queries with 'explain plan' and Oracle Enterprise Manager (or STATPACK) can show me most of the problem code in the application.

Last steps, after looking at the code, may result in de-normalizing tables, using 'bind' variables, or adding indexes.

I have not found any difficult tuning situations. There have been a few where it was hard to convince people or to where the solution was unexpected (e.g. not running analyze frequently).

Take a look at the tools I list and see what you get. Typically I look at:
CPU first becasue it is easy
RAM next because it is really easy too
Disk, use sar and it is pretty easy too
Once I have a hint as to which one it is look at the database to see if it supports the conculsion (lots of waits? lot of parsing? lots of reads? buffer cahce not used well?.. whatever.

The one point I don't cover is it really help to know the application. Shoud you be getting many queries for the same thing? Is every one large reports gathering radically different data? Lots of updating? Some of the statitics will vary depending on exactly what you are doing with you database.

Best of luck. The last possibility, which are the hard to track performance issues, are bugs. Since you are using trusted Oracle (which I have NO familiarity with) you may be encounter overhead or ineffcient code Oracle that you can't avoid. In those cases just keep trying to understnad the problem and then code around the problem (or sit on hold with Oracle and make a Priority 1 TAR :-))

Steve

Re: Performance HP-UX

Ted Buis — Mon, 23 Feb 2004 10:20:26 GMT

Good book, HP-UX Tuning and Performance: Concepts, Tools and Methods
by Robert F. Sauers (Author), Peter S. Weygant (Author) which you can find a Amazon.com

Re: Performance HP-UX

Steven E. Protter — Mon, 23 Feb 2004 10:26:20 GMT

Here is a really good doc.

US

http://www1.itrc.hp.com/service/cki/search.do?category=c0&mode=id&searchString=UPERFKBAN00000726&search.x=28&search.y=8&searchCrit=allwords&docType=Security&docType=Patch&docType=EngineerNotes&docType=BugReports&docType=Hardware&docType=ReferenceMaterials&docType=ThirdParty

SEP

Re: Performance HP-UX

Tim Sanko — Mon, 23 Feb 2004 11:15:31 GMT

90% of tuning is finding the bottleneck.

I run SAR 24/7.

Then when problems arise as they do, I use glance/gpm to fine tune things to find the offending application.

I would suggest Openview performance monitoring tools as a retail option, and then I would look at SSPM as a finer toothed comb It can do everything including cancel offending jobs, page people and dial your home and wake you up.

Tim

Tim Sanko

Re: Performance HP-UX

A. Clay Stephenson — Mon, 23 Feb 2004 11:31:11 GMT

I would begin by asking myself "If I double the speed of the application, will that be enough?" If the answer to that question is no then the only place to look is in the application code itself. Moreover, OS tuning and better disk layouts seldom yield even 20% improvements. Very rarely, absolutely horribly tuned/configured boxes can be improved by 2X but that is about the absolute maximum that can be achieved -- and again, improvements that dramatic are rare.

You need to gather database statistics and do some sqlexplains on the most common queries. Often, the addition of a single index can exceed all the tuning efforts by a factor of 10.

Re: Performance HP-UX

Michael Roeder — Thu, 08 Apr 2004 02:29:56 GMT

Glance is a very nice tool. If you like it (you can find a lot of bottlenecks), you could buy measureware - this application logs many hours/days the data you see in glance at the moment - and your are able to set filter on your applications. You could log some (remote) machines and get the log data to a local machine to view them. Ask HP

Re: Performance HP-UX

Nicolas Dumeige — Thu, 08 Apr 2004 03:38:54 GMT

Hello,

For Oracle, take a look at :
Oracle9i Database Performance Tuning Guide and Reference
This is Oracle Corp. standard documentation.

Oracle Press provide large book on RAC, SQL tunning, ...

There is also abondante litterature on OLTP performance or Datawarehouse design.

For the Unix part - given that Oracle say that 90% of the performance depend on database design and SQL tunning, and 10% on system tunning, take a look at this amazing bookfind attached an O'reilly book :
http://www.oreilly.com/catalog/spt2/

Cheers

Nicolas

Re: Performance HP-UX

Tim Sanko — Thu, 05 May 2005 06:52:59 GMT

Just to throw a wrench at those who don't know this stuff. depending on the Unix and the Oracle release, you can get as much as
a 75% reduction by changing buffer sizes in Oracle and in the OS.

I did reduce a 45 second query to a 1.5 second return by changing buffers on an Oracle 6 database and by changing the OS buffers. That is the extreme.

I have often seen better than 50% improvement by addition of keys. Proper DB design can often help more than much else just remember Indexes on one drive Datafiles on another. That is drives not partitions.

Tim

Tim

Re: Performance HP-UX

Amit Agarwal_1 — Thu, 05 May 2005 20:27:08 GMT

Tal,

Whatever tuning you do, just make sure that you change one parameter at once, and benchmark the performance. Depending on the results you may decide to keep the change and proceed or revert the change. Changing more than one tunable at once is never recommended as one of the tunable may offset the performance gain by other, and you may never notice it.

Having said that, I would suggest you to first optimize your application to the fullest and then touch the tunables. Most of the time, slight changes in application gives you much better performance than what OS tuning would.

My 2 cents.

-Amit

Re: Performance HP-UX

Nguyen Anh Tien — Thu, 05 May 2005 21:42:18 GMT

I usually refer these docs for my case
It is really good for me
Let read and comment,
HTH
tienna