This discussion is archived
1 2 3 Previous Next 31 Replies Latest reply: May 5, 2008 1:33 AM by Jonathan Lewis RSS

Full Table Scan

user460521 Newbie
Currently Being Moderated
Hi,

We are runing in 10g (10.2.0.3), when using full table scan, the perfromance is very slow. Is a bug in ASM or SQL program problem? How can I vertify the problem come from? I have runing health check in oracle but found nothing.

Many Thanks
  • 1. Re: Full Table Scan
    585319 Newbie
    Currently Being Moderated
    Hi,

    By definition the Full Table Scan access is the most feared enemy, you should avoid that monster when your tables are huge (many rows &/or long rows).

    Create an index to the specific column(s) you want to search or join...

    Regards
    Ignacio

    http://oracledisect.blogspot.com
  • 2. Re: Full Table Scan
    SomeoneElse Guru
    Currently Being Moderated
    By definition the Full Table Scan access is the most feared enemy
    Unless it's faster than using an index for a particular query.

    "Not all full table scans are bad, not all indexes are good" - Tom Kyte.
  • 3. Re: Full Table Scan
    585319 Newbie
    Currently Being Moderated
    yes that's correct, I had that in mind when finished the sentence with "many rows &/or huge rows"...

    Regards
    Ignacio

    http://oracledisect.blogspot.com
  • 4. Re: Full Table Scan
    Madrid Oracle ACE
    Currently Being Moderated
    A full table scan doesn't say any thing by itself, you could be performing a full table scan against a table that sizes just a couple of blocks and it would even be more efficient than performing the access by means of the index. Or you could be accessing a table against thousands, tens of thousands or more blocks and performance would be severely degraded. This fact doesn't have anything to do with the oracle version, it has always been the same. It is not a feature or a 10g bug, I would rather say it is an application programming bug between the chair and the keyboard.

    Seriously, you will have to analyze the execution plan of the problematic query and use realistic metrics to evaluate the workload generated by the query execution, and have this query optimized.


    ~ Madrid

    http://hrivera99.blogspot.com/
  • 5. Re: Full Table Scan
    585319 Newbie
    Currently Being Moderated
    Please may you post the table stats, please query DBA_SEGMENTS for that table, sum by BLOCKS and BYTES... and if you have the explain plan to share with use will be great.

    How many rows are we talking?

    Regards
    Ignacio

    http://oracledisect.blogspot.com
  • 6. Re: Full Table Scan
    CharlesHooper Expert
    Currently Being Moderated
    We are runing in 10g (10.2.0.3), when using full
    table scan, the perfromance is very slow. Is a bug in
    ASM or SQL program problem? How can I vertify the
    problem come from? I have runing health check in
    oracle but found nothing.

    Many Thanks
    user460521,

    Take a look at generating a 10046 trace at level 8 or 12 for the SQL program running the query. It takes a little practice to learn how to manually read a 10046 trace file, but the wait events and CPU/elapsed time present in the trace files provide a significant amount of detail to determine the source of the performance problem.

    Enabling a 10046 trace file for a session:
    Re: SQL_TRACE help to newbie in oracle

    Sample of interpretting a 10046 trace file:
    http://groups.google.com/group/comp.databases.oracle.server/msg/2701e2b49ef1e77a?

    For more information on using a 10046 trace, read the book "Optimizing Oracle Performance".

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 7. Re: Full Table Scan
    311441 Employee ACE
    Currently Being Moderated
    Hi Ignacio

    What if you need to access a "large" proportion of the very very big table ?

    A FTS can very well be the best available option even for such large beasts.

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 8. Re: Full Table Scan
    636947 Newbie
    Currently Being Moderated
    Hello Richard,

    Usually you don't want your applications to access a very large part of a large table. This will be very slow and could deteriorate the performance of your application severely.

    Will a user wait for several minutes/hours while the query is running?

    You can usually do this smarter by creating MVs or summery tables which refresh every X intervals or have you application perform multiple inserts to keep the summery tables updated.

    Of course unless you are talking about very specific reports running on DSS systems here.

    Best Regards,
    David Yahalom
    http://www.authoritybase.com
  • 9. Re: Full Table Scan
    311441 Employee ACE
    Currently Being Moderated
    Hi David

    Keyword here of course is usually. However, this could be a DSS or reporting system, could be an overnight batch process, could be an infrequent requirement not worthy of the general overheads in maintaining a MV or index, etc. etc.

    Point being a FTS is not necessarily a bad thing and might be "cheaper" than using an index or maintaining a MV...

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 10. Re: Full Table Scan
    119642 Explorer
    Currently Being Moderated
    We had the same issues with full table scans in 10.2.0.3 where we had gathered system stats (have you gathered system stats?) and the optimizer seemed to think the disks could return data faster than actual. After modifications to weight the disks more expensive; index access path plans returned.

    Loads of patches for 10.2.0.3 look through the patch database and you may find a description that is spookily similar to your issue.
  • 11. Re: Full Table Scan
    636947 Newbie
    Currently Being Moderated
    Yep, many times FTS is cheaper then using an index. More than most people think. If I'm not mistaken, Oracle says that if you query 7.5% of the table rows and above you are usually better off with an FTS.

    Its true that you shouldn't avoid an FTS just because it is an FTS, I was just commenting that you CAN almost always avoid an FTS if you need too. But hack, this goes for large index scans as well.

    David Yahalom
    http://www.authoritybase.com
  • 12. Re: Full Table Scan
    SomeoneElse Guru
    Currently Being Moderated
    Oracle says that if you query 7.5% of the table rows
    I would be surprised if Oracle actually said that.
  • 13. Re: Full Table Scan
    636947 Newbie
    Currently Being Moderated
    I would be surprised if Oracle actually said that.
    Life is surprising :)


    "Queries that retrieve less than 7 percent of the table data blocks
    should use an index range scan. Conversely, queries that read more than 7 percent of the table data blocks will probably be faster with a full-table scan. "


    Not true for IOTs, of course.

    I've read this in an Oracle University Press book seven or eight years ago and it has stuck with me ever since.


    David Yahalom
    http://www.authoritybase.com
  • 14. Re: Full Table Scan
    424150 Newbie
    Currently Being Moderated
    http://www.praetoriate.com/t_op_sql_legitimacy.htm
1 2 3 Previous Next