SEO

Hi

We have monitoring software which is telling us that many full table
scans are happening in the database.

We want to reduce this if possible.

Is there any SQL to use to tell what is doing the full tablescans?
e.g. the database user and the select statement? It is becoming
tedious checking the top sql and seeing if it doing full table scans.

Many thanks.

Thiko!

Tom Kyte has a good mantra for this, that goes something like this;
"not all full table scans are bad, not all index scans are good"

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.

If you are just trying to be proactive, perhaps start with Statspack
and examine SQLs that are performing a lot of buffer accesses but are
only returning a small number of rows. These SQLs may perform FULL
table scans, or they may not. Some of the worst performance problems
I've seen have been from forced index scans.

Thank you!

"Thiko!" <biwombi@hotmail.com> wrote:
> Hi
>
> We have monitoring software which is telling us that many full table
> scans are happening in the database.

Throw away the software.

>
> We want to reduce this if possible.
>
> Is there any SQL to use to tell what is doing the full tablescans?

Isn't that what you buy monitoring software for? Throw away the software.

> e.g. the database user and the select statement? It is becoming
> tedious checking the top sql and seeing if it doing full table scans.

If the full table scans aren't showing up among the top sql the first
couple times you check them, then it is very likely that they aren't
actually a problem, no matter what some monitoring software says.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB

"Thiko!" <biwombi@hotmail.com> wrote in message
news:1107862438.607672.325800@f14g2000cwb.googlegr oups.com...
> Hi
>
> We have monitoring software which is telling us that many full table
> scans are happening in the database.
>
> We want to reduce this if possible.
>
> Is there any SQL to use to tell what is doing the full tablescans?
> e.g. the database user and the select statement? It is becoming
> tedious checking the top sql and seeing if it doing full table scans.
>
> Many thanks.
>
> Thiko!
>

If you are oracle 9i then use v$sql_plan to find out which tables are being
full scanned.
But as others answered it might help to find the offending SQL and go from
there.

On Tue, 8 Feb 2005 14:29:04 -0500, "Oradba Linux"
<techiey2k3@comcast.net> wrote:

>
>"Thiko!" <biwombi@hotmail.com> wrote in message
>news:1107862438.607672.325800@f14g2000cwb.googleg roups.com...
>> Hi
>>
>> We have monitoring software which is telling us that many full table
>> scans are happening in the database.
>>
>> We want to reduce this if possible.
>>
>> Is there any SQL to use to tell what is doing the full tablescans?
>> e.g. the database user and the select statement? It is becoming
>> tedious checking the top sql and seeing if it doing full table scans.
>>
>> Many thanks.
>>
>> Thiko!
>>
>
>If you are oracle 9i then use v$sql_plan to find out which tables are being
>full scanned.
>But as others answered it might help to find the offending SQL and go from
>there.
>
v$session_longops should also do.


--
Sybrand Bakker, Senior Oracle DBA

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.

jg
--
@home.com is bogus.
Living the American Dream:
http://www.signonsandiego.com/uniont...1m6greene.html