Performance HP-UX

[Tal Rosenberger]

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

[Lethuillier · Accepted solution]

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

[Lethuillier]

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

[Lethuillier]

Forgotten links ;

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


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

[SteveKirby]

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

[Ted Buis]

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

[Steven E. Protter]

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

[Tim Sanko]

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

[A. Clay Stephenson]

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.

[Michael Roeder]

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