Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: d/b health check

RE: d/b health check

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Wed, 25 Aug 2004 10:09:20 -0500
Message-ID: <004401c48ab5$825775a0$6701a8c0@CVMLAP02>


Despite the title, the focus of the book is really "diagnosing Oracle performance problems." From this, you can see why the focus of the book = is
on user complaints. One of the prime motives for writing the book is the repeated scenario 1) user complains, 2) technician springs into action "fixing" things, 3) technician announces success, 4) user doesn't = perceive
any improvement.

But responding to complaints is of course only part of the picture. A responsible performance MANAGER (a person who works with applications = even
when they're healthy, as opposed to a guy like me who tends to see them = only
when they're sick) needs to execute the same profiling steps in an environment where nobody's complaining. Even in a healthy, no-complaints environment, targeting of specific user actions is still /extremely/ important.

In a no-complaints environment, the targeting is not driven by = complaints
(there aren't any), but it /is/ still driven by business priority. For example, if your company sells widgets, then the part of the application that takes orders, ships orders, and bills for orders had #$%^ well be efficient. You don't even have to know that much about the business to figure these things out: for example, if some program runs 10,000 times = a
day, then you /know/ that improving it--even just a little bit--can mean = a
big reduction in overall workload.

How can you tell is a system is efficient? ...By ensuring that your business's Top=A0N most important user actions are efficient. So, step 1 = is to
target your Top=A0N most important user actions. Without this step = (Chapter 2,
especially p40), you can't go on. Of course, the bigger you can make the value of N, the more effective you are. For many shops, focusing = correctly
on N=3D5 would result in spectacular progress.

Once you've done your targeting step, then how can you tell if a user = action
is efficient? Profile it: collect carefully scoped timing data, and summarize it into a form with which you can tell by looking whether = there's
anything unnecessary going on--that is, whether there are any = unnecessary
steps contributing to response time.

Here's an example. Just this week, my team and visited an airline where = we
looked at a user action that nobody was really complaining about (yet). Collecting the right trace data was a challenge because of the = multi-tier
application architecture. But the guys stuck with it and got the right = data
(the stuff shown in figure 3-4 on p50 of the book). The reward was = pretty
cool: we got a 5x throughput improvement by making a trivial = modification to
the application. Once we had the right data, seeing the opportunity and getting the 5x took less than half an hour of analysis time. Because the response time problem showed up as an excessive number of calls to the 'SQL*Net message from client' timed event, we would have never found = this
opportunity with statspack or the "let's find bad SQL" approach.

Back to the original point, the reason we were looking at this = particular
user action is not that someone was complaining about it. It was because = the
business regarded this user action as important enough to warrant an efficiency evaluation. When you take this approach of looking at = individual
user actions, prioritized by business importance, you'll always get one = of
two results: either (1) you'll find an inefficiency and deliver a nice (maybe even spectacular) result to a place in the business that really = needs
it; or (2) you'll prove that the user action is as fast as it's going to = get
on the current technology architecture (and you'll know exactly what = part of
that architecture needs upgrading to make it better). As a bonus, you'll frequently eliminate significant percentages of overall system workload, which kind of accidentally makes life better for every user on the = system.

You just can't get these results by looking at system-wide metrics.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 9/14 San Francisco, 10/5 Charlotte, 10/26 Toronto
- SQL Optimization 101: 8/16 Minneapolis, 9/20 Hartford, 10/18 New = Orleans
- Hotsos Symposium 2005: March 6-10 Dallas - Visit www.hotsos.com for schedule details...

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Freeman, Donald
Sent: Wednesday, August 25, 2004 7:59 AM To: oracle-l_at_freelists.org
Subject: RE: d/b health check

I may be wrong but the first thing I got out of Carey and Jeff's book is =
=3D

to ask the question, "Is anybody complaining?" I have long thought that =
=3D

should be the primary indicator that something needs checked<g>. When I =
=3D

look long enough, and hard enough, I will undoubtably find something =3D that needs to be messed with, often to my detriment.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Lord David Sent: Wednesday, August 25, 2004 8:33 AM To: 'oracle-l_at_freelists.org'
Subject: d/b health check

All,

Can anyone point me to a good outline for a 'database health-check'. =
=3D

I.e.
is it best to base it on statspack/bstat/estat, some form of response =
=3D

time
breakdown (I'm reading Cary's book at the moment) or something else entirely.

Regards

--
David Lord
Senior DBA
Iron Mountain (UK) Ltd
This email and its attachments are confidential under applicable law and =

=3D
are intended for use of the sender's addressee only, unless the sender =
=3D
expressly agrees otherwise, or unless a separate written agreement =3D exists between Iron Mountain and a recipient company governing =3D communications between the parties and any data that may be so =3D transmitted. Transmission of email over the Internet is not a secure =
=3D
communications medium. If you are requesting or have requested the =3D transmittal of personal data, as defined in applicable privacy laws, by =
=3D
means of email or in an attachment to email, you may wish to select a =
=3D
more secure alternate means of transmittal that better supports your =3D obligations to protect such personal data. If the recipient of this message is not the recipient named above, =3D and/or you have received this email in error, you must take no action =
=3D
based on the information in this email. You are hereby notified that =
=3D
any dissemination, misuse or copying or disclosure of this communication =
=3D
by a recipient who has received this message in error is strictly =3D prohibited. If this message is received in error, please return this =3D email to the sender and immediately highlight any error in transmittal. =
=3D
Thank you. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------
Received on Wed Aug 25 2004 - 10:15:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US