4 Replies Latest reply: Mar 21, 2011 11:50 AM by jgarry RSS

    Full table scan

    783294
      Dear ALl,

      Please answer my below query

      How to find which session is making full table scan ?

      Regards
        • 1. Re: Full table scan
          sb92075
          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
            Catch-22
            You should find a possible answer searching in Google for "awr_full_table_scans.sql'.
            • 3. Re: Full table scan
              JuanM
              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',
                                        'DMSYS','EXFSYS','FLOWS_030000','FLOWS_FILES','HR','IX','LBACSYS','MDDATA',
                                        'MDSYS','MGMT_VIEW','ODM','ODM_MTR','OE','OLAPSYS','ORACLE_OCM','ORDPLUGINS',
                                        'ORDSYS','OUTLN','OWBSYS','PERFSTAT','PM','QS','QS_CBADM','QS_CS','QS_ES',
                                        'QS_OS','QS_WS','RMAN','SCOTT','SH','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR',
                                        'SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SYSTEM','TSMSYS','WEBSYS','WK_TEST',
                                        'WKPROXY','WKSYS','WMSYS','WS_OWNER','XDB','ICHUB','XS$NULL'
                                       )
                            --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.

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