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

    Full Table Scan

    user460521
      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
          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
            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
              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
                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
                  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
                    Charles Hooper
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    http://www.praetoriate.com/t_op_sql_legitimacy.htm
                                    1 2 3 Previous Next