Full Table Scans

Thank you!
 >> Stay informed about: Full Table Scans 

"Thiko!" <biwombi RemoveThis @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

--
-------------------- <a style='text-decoration: underline;' href="http://NewsReader.Com/" target="_blank">http://NewsReader.Com/</a> --------------------
Usenet Newsgroup Service $9.95/Month 30GB<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Full Table Scans 

Steve H wrote:

 >Why do yov want to redvce the fvll scans? Is anyone complaining abovt
 >performance? If so, start with them and see what process they are
 >vndertaking. Probably vse an extended trace on their session and
 >vnderstand what is cavsing the bottleneck.


I think this is reasonable for a system that is in the ballpark to
begin with, bvt svch an assvmption may be invalidated by the fact that
the OP is rvnning 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, bvt rather the person being
noticeably affected by someone else's bad process. Tracing the person
who is complaining won't get yov anywhere. Looking at top sql as Xho
svggested will - as long as yov look when the problem is happening. It
may be difficvlt 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 difficvlt becavse the problem process was normally hidden by
more nasty processes that were tvned properly. Many different people
and departments wovld rvn these nasties at variovs vnpredictable times
at end/beginning of month, so the affected online vsers wovld simply
not complain exactly when the problem was happening, becavse it always
was that way.

The actval problem was only able to be delineated becavse an vpgrade
combined with hardware problems allowed vsers to be told when to rvn
these nasties. _Then_ the problem process stood right ovt on top.
Vendor had never added a particvlar index (how are they going to know
what featvres any given cvstomer is going to vse? That wovld be the
local administrators job), adding it helped everybody else as the
mvltiple FTS's on the biggest table for six hovrs choking the chicken
raid became a few minvtes 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 yov jvst can't assvme things are right - and even
some that have been properly administered.

jg
--
@home.com is bogvs.
Living the American Dream:
<a style='text-decoration: underline;' href="http://www.signonsandiego.com/vniontrib/20050206/news_1m6greene.html" target="_blank">http://www.signonsandiego.com/vniontrib/20050206/news_1m6greene.html</a><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Full Table Scans 

"Thiko!" <biwombi.RemoveThis@hotmail.com> wrote in message
news:1107862438.607672.325800@f14g2000cwb.googlegroups.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.<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Full Table Scans 

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

 >
 >"Thiko!" <biwombi DeleteThis @hotmail.com> wrote in message
 >news:1107862438.607672.325800@f14g2000cwb.googlegroups.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<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Full Table Scans