Re: Oracle - solving performance problem

mark_sensfan_at_hotmail.com wrote:
>
> Problem:

> Certain queries are taking a dogs age to run. One is a mass load
> of data each day, the other a more critical one is a client query that
> takes about 2 minutes. We want to bring this value down to something
> like 5-10 seconds, if thats not possible at least 30-45 seconds or so.

So you want an improvement of 12 to 24 fold. How feasible do you think it is that you can improve IO by a factor of 24? Are you currently using less than 5% of the CPU? When you try to improve something by a factor of 20, you have assume the bottleneck is going to change in the course of your improvements.

> We know our bottle necks are I/O in both reads and writes.

*How* do you know that? I assume it is read during the client query, and write during the mass load?

> - Other minor DB/query changes

You probably need major query changes. An improved execution plan can increase performance by orders of magnitude. Can you provide the execution plan and various stats for that query? (Of course, there is always the chance that it is already about as good as it can get, but I think this is the best shot.) Better yet, trace it, too.

>
> Consultant was hired to look at our environment they recommended:
> - We need 5-10% of db size in SGA, therefore 23 gigs of SGA for today's
> size.

Can they justify this recommendation by refering to specific analyses/experiments they did on your specific system? Or did they just pull it out of their asses with a Rule of Thumb? If the former, what was it? If the latter, how much do you pay people to pull things out of their ass?

> - 64 bit architecture, to allow for huge SGA

Is there evidence this will help, or is it just a hunch?

> - Scrap the RAID 5 arrays and build RAID 10 arrays instead, more
> platters the better.

Might make a big difference for the bulk load. Probably not so much with your customer's query. (Well, unless we are talking about a heck of a lot more platters.)

> - Recommended some query and index changes, the fixes will help, but
> won't be overly significant.

Is there anyone who thoroughly understands the data? Does the query just fundamentally demand a massive amount of data, either because it returns a massive number of rows or because it aggregates a massive number or rows? If not, then there is a very good chance it could still be improved a lot.

>
> I need to figure out what we can do to achieve our goals and have it
> last at least 2 years of increasing data. I've been researching the
> following.
>
> - Change hardware platform to a large Sun box that can take more than
> 32 gigs of RAM running Solaris. (v890 or something)

That sounds expensive. How confident are you that it will give the desired result? I'm not very confident it will.

> - Some type of high performance platform from IBM with AIX

Same thing.

> - Intel/Sun Oracle grid with several nodes, blade server perhaps

Since you are a sysadmin looking for advice on usenet, I wonder if you guys have sufficient DBA talent in house to do a good job of that. Or would it be consultants?

> - Scrap SCSI and use a fiber channel SAN instead, in standalone or Grid
> setup.

I don't know enough to comment.

>
> Anyone have any opinions on the best course of action? How should one
> handle this amount of data and maintain performance?

Tune the application.

It ain't the size, it is the motion. Compare a 10 GB database that needs to inspect every row to answer every query to a 1TB database than can answer 99% of it's queries based on 3MB of hot data and can answer the other 1% by looking up 20 cold rows via primary key.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB