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

  • From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
  • To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 28 Sep 2007 12:18:43 -0400

I agree with Ted.  To my mind, the phrase "getting the whole instance
running well" means making sure that each of the important processes
runs in an acceptable period of time, and the way to do that is to apply
Method R.  


Paul Baumgartel
CREDIT SUISSE
Information Technology
Securities Processing Databases Americas
One Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel@xxxxxxxxxxxxxxxxx
www.credit-suisse.com


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Kerber, Andrew W.
Sent: Friday, September 28, 2007 10:45 AM
To: oracle-l@xxxxxxxxxxxx; fmhabash@xxxxxxxxx; sunil.kanderi@xxxxxxxxx
Cc: oracle-l
Subject: RE: Training for Oracle Performance tuning - Method-R is easy

This is a useful method for a consultant.  However, if you are a full
time DBA for a single company, and are really interested in getting the
whole instance running well, tuning is an ongoing task that you will
never actually finish.

And I'm not saying it's not a good way to do things.  It's a highly
effective method for solving immediate problems, which is the reason
someone would hire a consultant.  Its just that different jobs have
different requirements.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ted Coyle
Sent: Friday, September 28, 2007 8:36 AM
To: fmhabash@xxxxxxxxx; sunil.kanderi@xxxxxxxxx
Cc: 'oracle-l'
Subject: RE: Training for Oracle Performance tuning - Method-R is easy

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

-Ted

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of fmhabash
Sent: Wednesday, September 26, 2007 2:14 PM
To: sunil.kanderi@xxxxxxxxx
Cc: oracle-l
Subject: Re: Training for Oracle Performance tuning

I read the OraPub class description here 
http://resources.orapub.com/ProductDetails.asp?ProductCode=RPM&Show=Tech
Spec
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 ...

- This approach refuses to give any attention to instance level tuning. 
On some occasions, they won't even read a statspack report. Its entire 
focus is on identifying most important tasks for the business and 
collecting diagnostic data on it. This sounds great in concept, but in 
real life, it does not work this way. First, most often than not (at 
least in my experience), users and their managers, have no time (and 
sometimes knowledge) even to agree on a list. System is slow and we need

to know why NOW. You will find out when such issue strikes, you won't 
have the time or initiative to ask people on the call to go back and a 
gather a list so you can have time to instrument your database to 
collect stats. Specially, if your application users MTS, connection 
pools, or in large RAC environment.

- Has no focus on real-time performance monitoring and troubleshooting. 
I was disappointed when the class had not even addressed it. In real 
life, you must be able to decide on some tools that allows you to look 
at a DB real-time and be able to spot an issue immediately. This is the 
most critical of all. This class will not prepare you for this. This is 
what I do almost every day.

- This class will not prepare you for necessary tools and knowledge to 
able to research and report on a DB performance historically. This is 
again very critical for me. What tools can I use to be able to do this. 
Furthermore, I'm asked repeatedly by upper management to provide such 
reports on periodically basis. This class will not help you in this
regard.

- Class focuses at least by 80% of its time on the method itself. Little

emphasis is put on Oracle internals which are mandatory to understand 
performance. Also, I did not think enough emphasis was put on the 
resolution part and how these measure will work to resolve the issue.

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.


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l



------------------------------------------------------------------------
------
NOTICE:  This electronic mail message and any attached files are
confidential.  The information is exclusively for the use of the
individual or entity intended as the recipient.  If you are not the
intended recipient, any use, copying, printing, reviewing, retention,
disclosure, distribution or forwarding of the message or any attached
file is not authorized and is strictly prohibited.  If you have received
this electronic mail message in error, please advise the sender by reply
electronic mail immediately and permanently delete the original
transmission, any attachments and any copies of this message from your
computer system. Thank you.

========================================================================
======

--
http://www.freelists.org/webpage/oracle-l



==============================================================================
Please access the attached hyperlink for an important electronic communications 
disclaimer: 

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================

--
http://www.freelists.org/webpage/oracle-l


Other related posts: