4 Replies Latest reply on Mar 21, 2011 4:50 PM by jgarry

    Full table scan

      Dear ALl,

      Please answer my below query

      How to find which session is making full table scan ?

        • 1. Re: Full table scan
          How to find which session is making full table scan ?
          EXPLAIN PLAN shows when FTS is used.

          For some queries FTS is optimal solution.
          • 2. Re: Full table scan
            You should find a possible answer searching in Google for "awr_full_table_scans.sql'.
            • 3. Re: Full table scan
              Hi, I use this query and as you can see, you can exclude some users.
              SELECT username "User Name", sql_id "Sql Id", operation "Operation",
                     optimizer "Optimizer", sql_fulltext "Sql"
                FROM (SELECT   a.parsing_schema_name AS username, a.sql_id,
                               p.operation, p.optimizer, a.sql_fulltext
                             FROM v$sqlarea a JOIN v$sql_plan p ON (p.sql_id = a.sql_id)
                           WHERE a.parsing_schema_name NOT IN ('ANONYMOUS','APEX_PUBLIC_USER','CSMIG','CTXSYS','DBSNMP','DEMO','DIP',
                            --AND p.optimizer IN ('RULE', 'HINT: RULE')
                            AND p.depth = 0
                            AND EXISTS (SELECT 1
                                           FROM v$sql_plan q
                                              WHERE q.sql_id = p.sql_id
                                                AND q.operation = 'TABLE ACCESS'
                                               AND q.options = 'FULL'))
               WHERE ROWNUM <= 50
              Hope this help.

              • 4. Re: Full table scan
                If it is taking enough time to be noticeable by users, it's easy to drill down in dbconsole.