Wednesday, December 14, 2005

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.


Blogger Rjamya said...


Maybe you should consider holding those Masters Classes in USA. Some of us will be have a better chance of attending one then.


4:21 AM  
Blogger shrek said...

yes, hold them over here. besides, i have a place here in Austin with 300 beers to sample.;-)

5:56 AM  
Blogger David Aldridge said...

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.


6:00 AM  
Blogger Moans Nogood said...

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?

3:10 PM  
Anonymous Raymond said...


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.

3:15 PM  
Blogger Noons said...

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.

4:45 PM  
Blogger Noons said...

Oh BTW: does anyone know what's going on with Haven't been able to get to the site for a while, everything alright with Steve Adams?

5:08 PM  
Blogger Roderick said...

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.

select empno from emp where rowid=...;
select deptno from emp where rowid = ...;
select hiredate ...
They were all in the top executed SQL section of statspack with the same number of executions.

6:53 PM  
Blogger Doug Burns said...


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

I wish someone had said something ;-)



1:12 PM  
Anonymous Ram said...

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


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'?


12:02 AM  
Blogger Moans Nogood said...

That would probably be an excellent example of just that, yes.


1:13 PM  
Blogger Roger Snowden said...

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.

7:15 PM  
Anonymous Anonymous said...

It takes strength to resist temptations and distractions Diablo iii Power Leveling, it takes strength to do what is right D3 PowerLeveling, it takes strength to do al these things. And all the while, these are the very things that build even more strength D3 Power Leveling.

Being happy doesn't mean that everything is perfect. It means that you've decided to look beyond the imperfections cheap c9 gold, don't go around saying the world owes you a living. The world owes you nothing. It was here first c9 money, I have a simple philosophy: Fill what's empty. Empty what's full. Scratch where it itches c9 gold.

1:32 AM  
Blogger 林磊 said...

celine bags
canada goose jackets
Coach Outlet Store Online
Canada Gooses Sale,Canada Gooses Jackets,Canada Gooses Coats,Canada Gooses Parka
Toms Outlet Shoes
Louis Vuitton Handbags Outlet Stores
coach outlet
oakley sunglasses cheap
Jordan Retro 13 Hot Sale
ugg boots
Abercrombie Store
Christian Louboutin Sale For Men And Women
Air Jordan 8
coach outlet online
michael kors uk
hollister uk sale
coach outlet online
Mont Blanc Pens
Louis Vuitton Outlet Online Shop
michael kors outlet online
louis vuitton handbags
cheap ugg boots
Coach Factory Outlet
timberland outlet
ralph lauren outlet
Coach Outlet Coach factory
Abercrombie kids - Official Site
michael kors outlet
Louis Vuitton Handbags Official Site
ralph lauren uk
cheap ugg boots
michael kors outlet clearance
true religion outlet
nike trainers
michael kors outlet

11:58 PM  
Blogger oakleyses said...

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

4:36 PM  
Blogger oakleyses said...

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

4:38 PM  
Blogger oakleyses said...

louboutin, nike roshe run, north face jackets, vans shoes, asics running shoes, new balance shoes, mcm handbags, iphone 6 cases, reebok outlet, air max, giuseppe zanotti, longchamp, oakley, nike trainers, soccer shoes, hollister, celine handbags, s6 case, insanity workout, baseball bats, wedding dresses, babyliss pro, north face outlet, hollister clothing store, soccer jerseys, p90x, beats by dre, ralph lauren, mac cosmetics, chi flat iron, iphone cases, ferragamo shoes, nike huaraches, valentino shoes, birkin bag, lululemon outlet, ghd, timberland boots, iphone 6s plus cases, abercrombie and fitch, instyler, nfl jerseys, bottega veneta, ipad cases, jimmy choo outlet, iphone 5s cases, mont blanc, herve leger, iphone 6s cases, iphone 6 plus cases

4:42 PM  
Blogger oakleyses said...

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

4:45 PM  
Blogger Clipping path Help Zone said...









10:14 PM  
Anonymous Obat Atasi Keputihan Yang Bau Dan Gatal said...

Very interesting information, may be useful for everything

Cara Alami Mengobati Hepatitis
Pengobatan Radang Selaput Otak (Meningitis)
Pengobatan Sirosis Hati
Pengobatan Tradisional Atasi Patah Tulang
Cara Melancarkan Haid Tidak Lancar/Tidak Teratur
Cara Ampuh Obati Vitiligo

5:47 PM  

Post a Comment

<< Home