Re: Oracle - solving performance problem

On Fri, 07 Oct 2005 14:42:52 -0700, mark_sensfan interested us by writing:

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

While it MAY be required, you might want to find out WHY they made this recommendation. It could be right, BUT ...

often consultants who learned their trade from the internet (or in the 90s and never upgraded their skills) will make such a recommendation because the want a huge increase in DB Cache. This is on the assumption that keeping everything in memory will speed things up. They miss a few minor things like how long it takes to find stuff to flush back to disk when the cache is that big. Unless proven otherwise such a large cache could easily slow the entire system down.

Your cheapest route is to get the 10g Diagnostic Pack, Tuning Pack and Performance Pack officially turned on (they are included in Enterprise Manager but are extra cost options), start collecting stats with the AWR and use the Advisors to help you find out what is going on. To get started, go to http://otn.oracle.com, click on Enterprise Manager (left side) and look at the demos.

There are dozens of things to look at first. Things as simple as 'are you using undo vs redo', 'are you using locally managed tablespaces' and 'could you benefit from partitioning' should be reviewed first.

Depending on your SAN technology, you MIGHT benefit from switching Raid 5 to Raid 1+0 (10), but some of the newer SANs compensate for Raid 5 losses in performance reasonably well.

Using Real Application Clusters (RAC), which is effectively the database's contribution to Oracle's GRID vision, might be helpful. I'd study other things first, as there are some classes of problems that are not suited for RAC.

A lot of what Jonathan Lewis writes in Practical Oracle8i is still valid as well.

If you are serious about using intelligent consulting, wander over to http://www.oaktable.net and get the consultants listed there involved. I'm sure there is one close to you. (Or contact any of the regulars in this group offline.)

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** I no longer assist with top-posted newsgroup queries ***