9 Replies Latest reply: Jul 6, 2009 12:40 AM by Billy~Verreynne RSS

    how to find Full Table Scans ?

    627359
      Hi,

      plz how to find fulll table scans ?


      Thanks,
        • 1. Re: how to find Full Table Scans ?
          sybrand_b
          Typical OTN question:
          How can I get as much as possible for nothing, while doing as little as possible myself.
          In this case
          - can not be bothered to abstain from MSN lingo
          - can not be bothered to include a version (4 digits), and the answer is version dependent.

          Plz read the performance tuning manual for ur unmentioned version.

          --------------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: how to find Full Table Scans ?
            Charles Hooper
            Are you trying to find full table scans under the assumption that full table scans are always bad? Full table scans do not necessarily indicate a problem.

            The answer will be differ depending on Oracle version - the answer will be different for Oracle 8.1.7.1 compared to Oracle 11.1.0.7.

            Try this experiment in SQL*Plus:
            DESC V$SQL_PLAN
            The output of the above includes lots of interesting columns, including the columns needed to join to V$SQL so that the SQL statement may be retrieved.

            Warning, the following is a potentially expensive operation in a production database:
            SELECT DISTINCT
              OPTIONS,
              OBJECT_TYPE
            FROM
              V$SQL_PLAN
            ORDER BY
              OPTIONS,
              OBJECT_TYPE;
            
            OPTIONS                        OBJECT_TYPE
            ------------------------------ ----------------
            AGGREGATE
            ANTI
            ANTI NA
            BUFFER
            BY INDEX ROWID                 CLUSTER
            BY INDEX ROWID                 TABLE
            BY USER ROWID                  CLUSTER
            BY USER ROWID                  TABLE
            CARTESIAN
            CLUSTER                        CLUSTER
            FAST FULL SCAN                 INDEX
            FAST FULL SCAN                 INDEX (UNIQUE)
            FIXED INDEX                    TABLE (FIXED)
            FULL                           CLUSTER
            FULL                           TABLE
            FULL                           TABLE (FIXED)
            FULL                           TABLE (TEMP)
            FULL SCAN                      INDEX
            FULL SCAN                      INDEX (UNIQUE)
            FULL SCAN (MIN/MAX)            INDEX
            FULL SCAN (MIN/MAX)            INDEX (UNIQUE)
            GROUP BY
            GROUP BY NOSORT
            GROUP BY ROLLUP
            JOIN
            ORDER BY
            ORDER BY STOPKEY
            OUTER
            PARTITION
            PICKLER FETCH
            RANGE SCAN                     INDEX
            RANGE SCAN                     INDEX (CLUSTER)
            RANGE SCAN                     INDEX (UNIQUE)
            RANGE SCAN (MIN/MAX)           INDEX (UNIQUE)
            RANGE SCAN DESCENDING          INDEX
            RANGE SCAN DESCENDING          INDEX (UNIQUE)
            RIGHT OUTER
            RIGHT SEMI
            SEMI
            SKIP SCAN                      INDEX
            SKIP SCAN                      INDEX (UNIQUE)
            SORT
            STOPKEY
            UNIQUE
            UNIQUE SCAN                    INDEX (CLUSTER)
            UNIQUE SCAN                    INDEX (UNIQUE)
            WITH FILTERING
            WITHOUT FILTERING
                                           SEQUENCE
                                           VIEW
                                           VIEW (FIXED)
            With a little more experimentation, you should be able to determine which SQL statements have full tablescans in their plan. Note that there may be multiple plans for a single SQL statement (CHILD_NUMBER will differ).

            Charles Hooper
            IT Manager/Oracle DBA
            K&M Machine-Fabricating, Inc.
            • 3. Re: how to find Full Table Scans ?
              Oded Raz
              There are 2 ways I can think of,

              - Get an AWR report and look for event - "scattered read", go to the SQL ordered by READ, there you can find queries with Full Table Scan (Some times).

              - Query V$SQL, V$SQL_PLAN
              -- Find SQL_ID with FULL table scan
              SELECT SQL_ID,OPERATION,OPTION,OBJECT_NAME,OBJECT_OWNER
              FROM V$SQL_PLAN
              WHERE OPTION like '%FULL%' AND OPERATION like '%TABLE%';
              
              --Then find the Query Text :
              SELECT SQL_ID,SQL_TEXT 
              FROM V$SQL
              WHERE SQL_ID in (
                                         SELECT SQL_ID
                                         FROM V$SQL_PLAN
                                         WHERE OPTION like '%FULL%' AND OPERATION like '%TABLE%'
                                        );
              Oded
              [www.dbsnaps.com]
              [www.orbiumsoftware.com]

              Edited by: Oded Raz on Jul 4, 2009 1:43 AM
              • 4. Re: how to find Full Table Scans ?
                Toni Lazarin
                Mohammed_82 wrote:
                Hi,
                plz how to find fulll table scans ?
                :)
                why are you disabled to search at least this forum for the answer to question you asked?
                Thanks,
                ok
                • 5. Re: how to find Full Table Scans ?
                  Girish Sharma
                  Here is a single select command by Shyam Prabhudesai at following link:

                  http://www.oracle.com/technology/oramag/code/tips2005/032105.html

                  Hth

                  Girish Sharma
                  • 6. Re: how to find Full Table Scans ?
                    701909
                    "under the assumption that full table scans are always bad?"

                    Why would anybody do that?

                    Full scans against large tables are a well known indicator of many problems like missing indexes and obtimizer issues.

                    To find full table scans and see how they change over time use a script like this one http://www.dba-oracle.com/t_awr_fts_plan10g_sql.htm
                    • 7. Re: how to find Full Table Scans ?
                      26741
                      FullTableScans on large tables -- if very large, then on Partitions -- are meaningful in many cases.

                      Why else do we have things like
                      HASH JOIN
                      PARALLEL QUERY

                      if not to support requirements where a FullTableScan will yield results better than an IndexRangeScan ?
                      • 8. Re: how to find Full Table Scans ?
                        Charles Hooper
                        Absorbine Jr. wrote:
                        "under the assumption that full table scans are always bad?"

                        Why would anybody do that?

                        Full scans against large tables are a well known indicator of many problems like missing indexes and obtimizer issues.

                        To find full table scans and see how they change over time use a script like this one www.dba-oracle.com/t_awr_fts_plan10g_sql.htm
                        Mr Burleson,

                        It appears that you have quoted me out of context. I stated: +"Are you trying to find full table scans under the assumption that full table scans are always bad? Full table scans do not necessarily indicate a problem."+

                        It is unfortunately beat into the education (or lack there of) of some DBAs and developers that any full table scan is bad, much worse than any index type access.

                        An example of this is seen in the otherwise decent book "Beginning PL/SQL: From Novice to Professional". On pages 266-268 the author attempts to force the Oracle optimizer to use an index access for a SQL statement containing "WHERE NAME LIKE '%DOE%'. The author stated that "the Optimizer is wrong" for selecting to use a full tablescan rather than an index type access, citing that the index type access would require the retrieval of about 1,080 4KB blocks to determine which table blocks to access, compared to the 5,500 4KB blocks during a full tablescan. On page 266 the author stated "Instead, it resorted to the worst of all options: a full table scan. Ouch!". On page 268 the author stated "On the other hand, you're an intelligent programmer who is much more knowledgeable and can therefore consider things like physics." The author fails to recognize that if the table's extent size were set at 1MB, with a 4KB block size, Oracle 10g R2 would have auto-set the db_file_multiblock_read_count to 256 (on earlier versions of Oracle the DBA could have set the same value). While the author's forced INDEX FULL SCAN was reading the 4KB blocks one at a time, the full tablescan would have been reading up to 256 blocks at a time, in roughly the same amount of time that it would have taken to fetch a couple blocks of the index from disk.

                        As Hemant pointed out with clear examples, even full table scans on very large tables are not necessarily bad - or not as bad as the equivalent access method through an index.

                        My reply to the OP was intended to let the OP know that the primary goal of a DBA is not to remove all full table scans. That full table scans do not necessarily indicate a problem.

                        It would be helpful if you fix the page that you referenced, thanks for bringing it to my attention. The page apparently has errors.

                        Charles Hooper
                        IT Manager/Oracle DBA
                        K&M Machine-Fabricating, Inc.
                        • 9. Re: how to find Full Table Scans ?
                          Billy~Verreynne
                          Absorbine Jr. wrote:
                          "under the assumption that full table scans are always bad?"

                          Why would anybody do that?
                          Because they would have run into web sites of so-called Oracle consultants that presents a different reality? A reality where there are silver bullets to slay magical performance monsters. A reality where there are different classes of DBA, each with their own unique set of hit points and manna levels. And of course, where there are only a few Real Knights (riding around on miniature ponies) that alone are The Apostles, The Chosen Ones, of The One And True Church Of Oracle.
                          Full scans against large tables are a well known indicator of many problems like missing indexes and obtimizer issues.
                          So is I/O. In fact, I/O would also show the reverse - where a huge index range scan, followed by a huge table range scan, could have been better serviced by a full table scan instead.

                          So surely, it is better to look at something like I/O (a true reflection of the amount of work a query is doing), than going inky-pinky-ponky-full-table-scans-are-bad ?
                          To find full table scans and see how they change over time use a script like this one http://www.dba-oracle.com/t_awr_fts_plan10g_sql.htm
                          Personally, I would not trust anything on that site - including their quotes of what I said to back up their views, and using my source code examples to illustrate their point.