Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Training for Oracle Performance tuning - Method-R is easy

RE: Training for Oracle Performance tuning - Method-R is easy

From: Ted Coyle <>
Date: Fri, 28 Sep 2007 09:35:50 -0400
Message-ID: <001e01c801d4$7c457380$>

"This approach refuses to give any attention to instance level tuning. On some occasions, they won't even read a statspack report. "

I just have to jump back in here. I never read statspack reports.

I use Method-R to fix problems large and small every day. I've posted a real life example inline with the method. In this case, I did make a system level change and I had no prior involvement with this client's system.

  1. Select the top user actions for which the business needs improved performance.

----> System response time had grown worse over time. One report in particular used to take 15 minutes, but now took 2hrs. This report can't be run in batch. They really needed this particular report to run faster since it had to be kicked of by end users.

   2. Collect properly scoped diagnostic data that will allow you to identify the causes of response time consumption for each selected user action while it is performing sub-optimally.

-----> In this case, we examined the xplans and key init settings first.
It turns out they had skewed data and were biased toward table scans. I didn't trace, it wasn't necessary and as quoted from Jonathan Lewis, "The first (and only) rule of optimization is: Avoid unnecessary effort." We tested based on changes to one setting to determine scope.

   3. Execute the candidate optimization activity that will have the greatest net payoff to the business. If even the best net-payoff activity produces insufficient net payoff, then suspend your performance improvement activities until something changes.

-----> the solution was to adjust optimizer_index_cost_adj. The net payoff
is that the report ran faster. The subsequent test will be to see if anything goes slower. If so go to #4 otherwise were done.

Other options were suggested buy client resources, but all involved substantial effort or code changes, none of which was going to happen.

   4. Go to step 1. ----> So far we're monitoring and all is well.

I didn't guess about the solution, but I did take a risk adjusting a system setting. This risk turns out to be acceptable to the business because it solved their immediate problem and any subsequent issue can be resolved quickly using this approach. The problem was 4 months old by the time it landed in my cube. I was able to determine the issue and suggest a solution in 4hrs. It took 4 days to review and implement.

Again, this was business focused. I didn't need tracing to figure it out, this would be the next step if necessary, but I didn't need to go that far to make the report run faster.

I don't know what's so hard about implementing Method-R; knowing what to fix once you know what is wrong is the hard part. I provide the options and let the business decide. I have a 100% success rate with Method-R and I still haven't made it 100% through any of my 100+ books.


-----Original Message-----
From: [] On Behalf Of fmhabash
Sent: Wednesday, September 26, 2007 2:14 PM To:
Cc: oracle-l
Subject: Re: Training for Oracle Performance tuning

I read the OraPub class description here s
. My advice, take this class first then if you still need to, use the HotSos one. If you care to know why, keep reading ....

I have read and attended many Oracle perf approaches. Last one was HotSos Metho-R one. One thing that distinguishes HS from others is that they have a science-evidence-based approach. Compare this to what others keep calling 'art' or at best the 'bed-time' stories that some publish on their web sites as 'gurus'. I think they are, but their approach (if you we can call as such) is nothing more that a collection of experiences they have collected over the years. IMHO, any perf turning approach must yield accurate and reproachable results using a clearly defined tools and steps. And it should be something that can be taught and used by DBAs without necessarily having 20 years of experience.

Having said that, I have 4 reservations on HS M-R approach ...

IMHO, the HS M-R class, is an advanced class that I will not recommend as an initial step. I think this method worked great for HS group due to the nature and magnitude of problems they get consulted for. At that level of consulting and visibility, all the necessary pre-requisite work is probably have been done for them. But for everyday DBA performance issues, it will not help as much.

I have been involved in so many performance issues since I attended this class, all of them have been diagnosed and resolved. However, only in a few of them I had to get to the level where I really needed to use M-R.


Received on Fri Sep 28 2007 - 08:35:50 CDT

Original text of this message