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

[Donny Jekels]

an exelent alternate to the pricy OpenView.

http://www.lund.com/

also try the demo from quest software

spotlight

for service gaurd, understand your configuration thoroughly, even the best tools will not be able to help you if you don't know your layout and configs.

[Sridhar Bhaskarla · Accepted solution]

Hi,

There are many components in Openview. Which openview are you using and for what?.

1. Since this is RAC, the database is already there on raw devices. I suggest to work with your DBA and understand how they laid their tablespaces and datafiles. In many cases, the culprit for bad performance would be disk IO. Measureware and glance are great tools for historical and online performance monitoring. You can find out the busiest disks and Qlens on them using glance and see what is configured on them. Try to spread that data over disks that are not quite used.
2. You will need to take the help of your DBAs and finetune the SQl queries done by your application. Queries that do full table scans can be lethal.
3. If the measureware is collecting the data, you should do trend analysis of your system. Perfview 'pv' can help you without much trouble. If you don't have it you can use 'extract' a measureware utility to pull the stats.

ServiceGuard perse doesn't affect the performance. But badly written monitoring scripts can.

Measureware/Glance+ is definitely to have a tool.

-Sri

[Greg OBarr]

I got some good information about what Oracle queries were taking the longest and why, using the SQL Analyzer in OEM (Enterprise Manager) GUI that comes with 9i. Using the "TOP SQL", you can see the top "x" sql statements based on CPU time, disk physreads, buffer gets, etc.

We're still running 8i on the production database, but I was able to analyze with the 9i OEM tools and discovered some things that helped me to improve performance:

Background:

Ours is an approx 60Gb OLTP database. We run with optimizer in CHOOSE mode. We had not been analyzing tables regularly. Some tables that were created in past year had never been analyzed. In Dec, I reorganized the database, converted dictionary managed tablespaces to locally managed and did some other stuff that I thought would IMPROVE performance. Instead, performance DECREASED noticeably.

The problem turned out to be related to the fact that I had analyzed all the tables and indexes. Since we are running the optimizer in CHOOSE mode, it was running many of the queries using RULE rather than COST based optimization. RULE based optimization basically says: if there's an index that can be used, then use it. Even on the tables that had been analyzed, in most cases the analysis data was 2-3 years old and the number of records has grown greatly since then. So, when I analyzed all tables and indexes, the optimizer changed the execution plan for many of the queries in a negative way. Many indexes were no longer being used and this was slowing everything down.

I was able to see what was happening by using the SQL Analyzer in OEM for 9i. YOu could also user Oracle queries to get the information, of course, but the GUI makes it much faster and more intuitive.

One more thing. I'm sure you know this, but in 9i, the only choice you have in COST based optimizer. There is no RULE based optimizer in 9i. Therefore, table and index analysis data will be critical to the development of the explain plans.

In our case, we added some hints in some queries to force them to use the index. I also tweaked some init.ora parameters (Many of them are dynamic in 9i) that affect the optimizer. Some of them are:

optimizer_index_cost_adj = 5 (default is 100 and tells oracle that an index is just as costly as a table scan)

optimizer_index_caching = 95 (should be set to around the same as your buffer cache hit ratiio. The default of 0 tells Oracle NOT to keep indexes in the buffer cache!)

There are many additional ones in 9i. See Oracle Metalink http://metalink.oracle.com for more info.

-greg

[Greg OBarr]

One more thing:

Check your kernel parameters, especiallyl the "TIMESLICE" parameter. If you configured the kernel with one of the database models in SAM, it will set TIMESLICE to 1 . In most every case, this should be set back to the default of 10. Bill Hassel has a good doc somewhere here in ITRC about HP-UX and performance tuning for Oracle.

-greg