topic Re: more kernel tuning in Operating System - HP-UX

more kernel tuning

doug mielke — Tue, 26 Aug 2003 19:07:55 GMT

I have an N class, 11.0, 24 gig ram. running 32 bit Oracle and Financials. Will be going to 64 bit, and much larger SGA in October.

I've changed some of the basic params as recommended on this forum, but am looking for something to change to address a specific problem.

We have one customer with 16,000 locations. Our next biggest is around 2,000.

When we went past the 10K mark, performance for anyone that accesses that account sees a huge perfomance hit.

Oracle tells us to split the account into 2, but Finace folk are balking at that.

Is there a kernel limit I might be hitting once I start using such large tables? Are there other params. I need to address? ( attached is stand/system)

Re: more kernel tuning

Steven E. Protter — Tue, 26 Aug 2003 19:12:09 GMT

This US doc might help.
http://www2.itrc.hp.com/service/cki/search.do?category=c0&docType=Security&docType=Patch&docType=EngineerNotes&docType=BugReports&docType=Hardware&docType=ReferenceMaterials&docType=ThirdParty&searchString=UPERFKBAN00000726&search.y=8&search.x=28&mode=id&admit=-1335382922+1061928645238+28353475&searchCrit=allwords
Written by one of HP's crack oracle tuners.

You also might want to look at the glance system tables graph to see if you are hitting any limits.

SEP

Re: more kernel tuning

A. Clay Stephenson — Tue, 26 Aug 2003 19:33:06 GMT

When you begin to hit walls like this, tuning and hardware seldom solve the problem, especially when 'huge' means that even a 2X performance boost (which is VERY rare in tuning and hardware) still leaves you with a dog.

Your real answer is to analyze the SQL code and find where the queries are killing you. Many, many times I have found that adding just one index exceeded by an order of magnitude all the improvents made with new hardware and tuning.

The real tipoff is that you seem to be fine as long as you are under 2K rows.

This would be my approach to solving this (past displaying the database query plan):

Gather some data that correlates number of location vs. query times and plot the logarithm of the number of locations vs. query time. The slope of that line gives the order of the equation.
e.g slope ~ -1 --> linear, not bad
slope -2; quadratic, bad usually indicates an outer join using sequential access
slope worse that that -3,-4, disaster; serious improvements are needed. (e.g you might have an outer join inside an outer join and all the access is sequential.)

Re: more kernel tuning

doug mielke — Tue, 26 Aug 2003 21:09:05 GMT

Thanks for the link, good stuff there.

The params that Glance reports show that I'm not even close on any of them ( except buff pages)

Doing explain plans and repairing queries has become the story of my life, a constant battle.

I posted this looking to see if there were more obscure params that I was skipping. I remember in a previous life with System V params forgetting to raise callout table if I increased nproc, or forgetting to make file hash table a non prime value. It seems so simple today, that I thought I might be missing some dependency.

Thanks for the help.

Re: more kernel tuning

Indira Aramandla — Wed, 27 Aug 2003 03:00:56 GMT

Hi,

Refer to the attached document for some Unix kernel parameters that should be monitored with changes to the oracle parameters in the init.ora file.