Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Full Table Scans

Re: Full Table Scans

From: Joel Garry <>
Date: 8 Feb 2005 14:21:31 -0800
Message-ID: <>

Steve H wrote:

>Why do you want to reduce the full scans? Is anyone complaining about
>performance? If so, start with them and see what process they are
>undertaking. Probably use an extended trace on their session and
>understand what is causing the bottleneck.

I think this is reasonable for a system that is in the ballpark to begin with, but such an assumption may be invalidated by the fact that the OP is running monitoring software that doesn't tell the top sessions (or perhaps he doesn't know how). The person complaining may not be the person with the bad process, but rather the person being noticeably affected by someone else's bad process. Tracing the person who is complaining won't get you anywhere. Looking at top sql as Xho suggested will - as long as you look when the problem is happening. It may be difficult to be notified when the problem is happening.

I recently solved one of these that had been going on for a long time. It was difficult because the problem process was normally hidden by more nasty processes that were tuned properly. Many different people and departments would run these nasties at various unpredictable times at end/beginning of month, so the affected online users would simply not complain exactly when the problem was happening, because it always was that way.

The actual problem was only able to be delineated because an upgrade combined with hardware problems allowed users to be told when to run these nasties. _Then_ the problem process stood right out on top. Vendor had never added a particular index (how are they going to know what features any given customer is going to use? That would be the local administrators job), adding it helped everybody else as the multiple FTS's on the biggest table for six hours choking the chicken raid became a few minutes of index thrashing.

So while Tom's mantra is correct, I've seen over and over again where a little bit of "making things right" is really worthwhile, and there are many places where you just can't assume things are right - and even some that have been properly administered.


-- is bogus.
Living the American Dream:
Received on Tue Feb 08 2005 - 16:21:31 CST

Original text of this message