Show TOC

 Table Scans: Problem Analysis (Oracle)Locate this document in the navigation structure

Use

The Table Scans entry that appears in the Database Alert Monitor and the Database Monitor shows the number of sequential read operations on tables per day. If the number of sequential read operations per day is very high, you should perform further analyses. Sequential data access is generally not very efficient, which is why you should try to minimize the number of full table scans.

Causes

  • Full table scans are often caused by missing table indexes. You can display tables with missing indexes by choosing Database indexes in the Database Alert Monitor.

    See also: Missing Indexes

  • Incorrect coding of SELECT SQL statements may also result in too many full table scans.
Procedure

To identify tables affected by sequential read operations, do the following:

  1. Choose Tools →Administration →Computing Center →Management System →Control →Performance Menu →Database →Activity.

    Alternatively, use transaction code ST04.

  2. Choose Goto → Exceptions → Alert Monitor.

    You reach the Database Alert Monitor.

  3. Choose Table Scans long tables to display the applications servers and processes responsible for the table scans.

    If the processes belong to an Oracle user (for example, SYS), the table scans are actually caused by the database. Processes belonging to the SAP user SAPR3 are important for further analysis.

  4. Log on to the application server that is causing the table scans.
  5. Use the Process Monitor to identify the user and report causing the table scans.

    Choose Tools → Administration → Monitor → System monitoring → Process overview.

    Alternatively, use transaction code SM50 (Work Process Load Monitor: Overview).

  6. Find out which tables are used by this report.

    There are two ways of doing this:

    • Start the report with an activated SQL trace
    • Analyze the program.
  7. Compare theses tables with those that appear in the list of tables with missing indexes. Choose Database indexes in the Alert Monitor.

    See also: Missing Indexes

    If none of these tables have indexes missing, the table scans are probably caused by an SQL statement in the report that has not been optimized.

See also:

Table Scans/Table Fetch (Oracle)

Monitoring Table Access Methods (Oracle)

Monitoring the Shared SQL Area (Oracle)