System-wide slowdowns
When I talk to customers with performance problems, I ask these two questions:
1. Is everything slow, or is it just specific stuff like a form, a report or a job (Cary Millsap would call it a Business Function, I think :) )?
2. Has it always been slow or did it just start?
3. Do you know what changed?
If everything really IS slow, ie. what I call a system-wide slowdown, then I still don't think you can find the answer fast and reliably (and certainly not without pure luck combined with decades of experience) by looking at system-wide data such as 'sar', StatsPack, v$sysstat, v$system_event and such.
[With one exception, though: Most of the times, this situation is caused by running too many reports or other heavy jobs at the same time, ie saturating the CPU's with CPU-intensive, long-running jobs. Cary wrote about 'The Magic of 2' some years ago, and he'll 'kill' that topic during his Master Class in Copenhagen in January, so that'll be fun. Anyway, when you can establish that CPU usage is 100% (or 0% idle) it's mostly just because of Month's End or similar situations where many reports have to be run.]
Back to the main topic: In the relatively few cases with system-wide slowdowns I've been involved in where it wasn't just because of too many 'batch' or 'CPU-intensive' jobs running simultaneously, it has been possible to find the reason for the general slowdown by 10046'ing something that was used widely, because whatever slows the system down will also impact this fellow.
To be safe, you might want to 10046 (perhaps it should be called Deep-six when it's level 12....) two or three such typical thingies to be sure.
The symptom of whatever is bothering the whole system will show up in the trace files.
So, again, I don't think you can use the system-wide stuff for anything :-).
This should conclude my trilogy on this topic. 'Baselining' and 'StatsPack' were the first two.
1. Is everything slow, or is it just specific stuff like a form, a report or a job (Cary Millsap would call it a Business Function, I think :) )?
2. Has it always been slow or did it just start?
3. Do you know what changed?
If everything really IS slow, ie. what I call a system-wide slowdown, then I still don't think you can find the answer fast and reliably (and certainly not without pure luck combined with decades of experience) by looking at system-wide data such as 'sar', StatsPack, v$sysstat, v$system_event and such.
[With one exception, though: Most of the times, this situation is caused by running too many reports or other heavy jobs at the same time, ie saturating the CPU's with CPU-intensive, long-running jobs. Cary wrote about 'The Magic of 2' some years ago, and he'll 'kill' that topic during his Master Class in Copenhagen in January, so that'll be fun. Anyway, when you can establish that CPU usage is 100% (or 0% idle) it's mostly just because of Month's End or similar situations where many reports have to be run.]
Back to the main topic: In the relatively few cases with system-wide slowdowns I've been involved in where it wasn't just because of too many 'batch' or 'CPU-intensive' jobs running simultaneously, it has been possible to find the reason for the general slowdown by 10046'ing something that was used widely, because whatever slows the system down will also impact this fellow.
To be safe, you might want to 10046 (perhaps it should be called Deep-six when it's level 12....) two or three such typical thingies to be sure.
The symptom of whatever is bothering the whole system will show up in the trace files.
So, again, I don't think you can use the system-wide stuff for anything :-).
This should conclude my trilogy on this topic. 'Baselining' and 'StatsPack' were the first two.
posted by Moans Nogood at 10:09 PM
15 Comments:
Mogens,
Maybe you should consider holding those Masters Classes in USA. Some of us will be have a better chance of attending one then.
Raj
yes, hold them over here. besides, i have a place here in Austin with 300 beers to sample.;-)
Earlier in the week we found that the cause of our own system-wide slowdown was that an administrator had incorrectly exited "sam" and it was consuming 99% of the CPU. It had notched up 31 hours of CPU time.
Nice.
Y'all (as they say down South) might be right there: We should do Master Classes in the USA instead of here. I also - again and again - have to listen to persistent rumors about the USA having many more people than Denmark. So where should we base Miracle US? My old hometown of Baton Rouge, perhaps?
Mogens,
I am sick an tired flying to the US and Europe for these classes. It is time that you bring the road show to Australia.
I'll second that, Raymondo! Been too long since the last one all the way back in 2001 - or whatever it was: can't remember what I had for breakfast let alone what happened a few years ago!
Besides, this place is known for having a few beers as well...
On the overall system thing: a clasic situation I still get from time to time is just the plain old "too much SQL being executed". A few years ago it was common to see apps designed with a discrete SQL behind every single field on the screen. With the obvious result: parsing madness, SQL overload.
It STILL is common to see this now, except it's behind every single attribute of an object in a bean - or something else, java is not the sole offender here.
And the worst offender: one discrete SQL execution for every single row needed!
Set-at-a-time processing, wazzat?
I won't name products and architectures as it may offend the listeners but it's obvious which I'm talking about.
Solutions? Not many, I'm afraid. But in these situations the statspack may help: check out the most used SQL, if you got horrendous number of executions of very simple row-by-row statements, it's a good tip to start chasing the "architect" with a baseball bat.
Oh BTW: does anyone know what's going on with ixora.com.au? Haven't been able to get to the site for a while, everything alright with Steve Adams?
I just like how you say you ask 2 questions and then list 3 :-). Of course, question #3 does get fun to ask because sometimes in the beginning the answer is, "Nothing's changed." And after it's solved, someone will say, "Oh? But, I didn't think that change would affect anything, so I didn't think it was worth mentioning."
There may be more exceptions than you think. There is still a growing "newbie" population out there repeating history. Usually takes a few minutes for an experienced person to look at a statspack and calculate some probability that the problem will likely be in the area of SQL optimization, not using binds, short lived connections, bad design, etc. Then you can decide what direction to investigate first - e.g. 10046 or tkprof or looking outside the database altogether. And you need tons of experience to interpret that stuff too...
Isn't CPU usage a system-wide stat? :-)
Similar to Nuno, I actually saw one Web based application issuing one query for each field and radio button on the page and all the queries were against one column of the same table.
[e.g.
login
select empno from emp where rowid=...;
select deptno from emp where rowid = ...;
select hiredate ...
logout
]
They were all in the top executed SQL section of statspack with the same number of executions.
Mogens,
"Cary wrote about 'The Magic of 2' some years ago, and he'll 'kill' that topic during his Master Class in Copenhagen in January, so that'll be fun."
That's fantastic news! So I'll be discussing a dead topic - http://tinyurl.com/b7mgl
I wish someone had said something ;-)
Cheers,
Doug
"it has been possible to find the reason for the general slowdown by 10046'ing something that was used widely, because whatever slows the system down will also impact this fellow."
Mogens,
Can a form that is used often, for eg, a ticket availability form in an airline reservation system be a good example of 'something that is used widely'?
Ram.
That would probably be an excellent example of just that, yes.
Mogens
Umm... aren't you supposed to check the hit ratios and throw more memory at the db until the ratios get better?
But seriously, do a Masters Class here in the states and I will do one of my famous standup routines. Might even discuss diagnostics or some such.
Maybe I'll talk Ricky Sanchez into showing up and ranting about something. You never know.
oakley sunglasses, michael kors outlet, louboutin outlet, prada handbags, air max, burberry outlet, louis vuitton outlet, ray ban sunglasses, tory burch outlet, cheap uggs, michael kors outlet, michael kors outlet, chanel handbags, burberry outlet, oakley sunglasses, kate spade outlet, uggs outlet, oakley sunglasses cheap, prada outlet, longchamp handbags, gucci outlet, air max, louis vuitton, ray ban sunglasses, nike shoes, jordan shoes, nike free, polo ralph lauren, michael kors outlet, polo ralph lauren outlet, ray ban sunglasses, oakley sunglasses, michael kors outlet, christian louboutin, tiffany and co, louboutin, tiffany and co, rolex watches, longchamp outlet, louis vuitton outlet, longchamp handbags, ugg boots clearance, michael kors outlet online sale, uggs, ugg outlet, louis vuitton outlet stores, louboutin shoes, replica watches, louis vuitton handbags
vanessa bruno, air max, michael kors uk, coach outlet, michael kors, sac guess, true religion outlet, nike free pas cher, nike free, kate spade handbags, timberland, lululemon outlet online, michael kors outlet, north face, abercrombie and fitch, coach purses, ralph lauren, polo lacoste, oakley pas cher, north face, new balance pas cher, sac hermes, coach outlet store online, converse pas cher, longchamp, nike tn, air max pas cher, hollister, sac burberry, air max, michael kors pas cher, nike air force, hogan outlet, ray ban pas cher, louboutin, polo ralph lauren, true religion jeans, mulberry, sac longchamp pas cher, replica handbags, true religion outlet, nike blazer, vans pas cher, ray ban sunglasses, nike roshe, true religion jeans, nike roshe run pas cher, hollister, nike air max, air jordan
converse, pandora charms, toms shoes, barbour, moncler outlet, sac louis vuitton, swarovski, links of london, louis vuitton, barbour, moncler, canada goose, moncler, canada goose jackets, pandora charms, converse shoes, rolex watches, vans, supra shoes, moncler, ugg boots, abercrombie, ugg, marc jacobs, ugg pas cher, ugg, wedding dresses, pandora jewelry, sac lancel, air max, louis vuitton, sac louis vuitton, coach outlet store online, juicy couture, hollister, ugg, canada goose, canada goose, moncler, juicy couture outlet, thomas sabo, moncler, ray ban, louis vuitton uk, montre homme, moncler, gucci, canada goose outlet, pandora jewelry, canada goose, canada goose uk
Post a Comment
<< Home