1750503 Members
5764 Online
108777 Solutions
New Discussion юеВ

more kernel tuning

 
doug mielke
Respected Contributor

more kernel tuning

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)
4 REPLIES 4
Steven E. Protter
Exalted Contributor

Re: more kernel tuning

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
A. Clay Stephenson
Acclaimed Contributor

Re: more kernel tuning

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.)


If it ain't broke, I can fix that.
doug mielke
Respected Contributor

Re: more kernel tuning

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.
Indira Aramandla
Honored Contributor

Re: more kernel tuning

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.
Never give up, Keep Trying