EXPLAIN PLAN   «Prev  Next»

Lesson 5Detecting full-table scans
ObjectiveDetect a full-table scan in an Oracle 26ai execution plan, validate execution evidence, and decide whether the scan warrants investigation.

Detect and Diagnose Full-Table Scans in Oracle 26ai

Look for TABLE ACCESS FULL in the Operation column of an Oracle execution plan. Then identify the object being scanned and read the surrounding operators and predicates. Finding this operation answers an access-path question; it does not, by itself, establish that the SQL is inefficient.

In Oracle AI Database 26ai, a full scan can be appropriate for a small table, a broad result or an aggregate that needs much of its input. The useful tuning question is whether the scan is appropriate for the requested result and measured workload. This lesson develops that investigation from an estimated plan through collected execution evidence.

What a full-table scan reads

For the conventional heap-table case, a completed full scan reads formatted blocks below the segment's high-water mark. Oracle evaluates the relevant predicates and returns qualifying rows. Reading blocks is different from returning every row to the application, and it is not a separate scan of every column.

The high-water mark describes segment space, not the number of rows returned by a query. A formatted block below it may contain data or may have become empty. The scan does not simply count the currently matching rows and stop after finding that number.

Also distinguish the logical operation from physical I/O. Data may be cached, and scan behavior can involve direct reads, multiblock I/O or parallel execution. The plan's operation name alone does not tell you exactly how many disk reads occurred. Measurements are needed to assess that work.

How Oracle chooses the access path

During hard parsing, the cost-based optimizer compares available paths using statistics and estimated cost. It may choose a full scan even when an index exists. A compatible cached cursor can reuse its plan, so optimization is not necessarily repeated for every execution.

Oracle 26ai compares access paths, scans formatted heap-table blocks below the high-water mark, and returns rows matching the predicate.
The optimizer compares available paths during hard parsing; a compatible cached cursor may reuse its plan. A full scan reads formatted blocks and filters rows. A selective predicate and usable index may favor index access, but neither guarantees that choice.

The diagram preserves an illustrative POPULATION query from the original teaching example. POPULATION is not assumed to be an installed Oracle sample table. The following practical examples use a separate illustrative application table, CUSTOMERS, to demonstrate how to recognize and investigate the same operation.

Step 1: Generate an estimated plan

Use a training session with access to a CUSTOMERS table containing CUSTOMER_ID and LAST_NAME columns. This lesson does not create that table or assume that it belongs to HR. If your environment uses a different training table, adapt the names and predicates consistently.

EXPLAIN PLAN
  SET STATEMENT_ID = 'L5_CUSTOMERS_01'
FOR
  SELECT customer_id, last_name
  FROM customers
  WHERE last_name = 'Smith';

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'L5_CUSTOMERS_01', 'TYPICAL')
);

The first statement records estimated plan steps. It does not execute the customer query. The second statement displays the records with the supplied label. TYPICAL is useful here because the lesson examines estimates and predicates; BASIC provides a smaller display and should not be presented as producing all those details.

Oracle normally provides the default PLAN_TABLE infrastructure. Keep explain and display in the same session because its temporary plan rows are session-private. Use a fresh label for a new experiment. There is no need to begin with an unrestricted deletion of plan records or a casual COMMIT that could affect unrelated work.

The result may contain a full scan, an index path or another suitable choice. The value Smith is not inherently selective: its frequency depends on the actual data. A demonstration query cannot guarantee the same access path in every database.

Step 2: Locate the operation and its object

The following is a structural illustration of a plan containing a full scan, not captured output from a live Oracle 26ai execution:

Id  Operation           Name
0   SELECT STATEMENT
1     TABLE ACCESS FULL CUSTOMERS

The Operation column identifies the access method, and Name identifies the object. In a larger query, use aliases and predicate information to determine which reference to a table the operation represents. Do not search for the word FULL in isolation: INDEX FULL SCAN and INDEX FAST FULL SCAN describe index operations, not TABLE ACCESS FULL.

In an explained plan, this means the optimizer selected a full scan for the explain environment. It does not prove that the application completed that scan during a particular request. Schema resolution, bind information, statistics and optimizer settings can differ between explaining and executing.

Read Rows and Bytes as estimates. Cost is an optimizer comparison value, not elapsed seconds. A displayed Time value is also an estimate, not a measured response time. A large estimated cost alone does not identify the cause of a real slowdown.

Read predicates and partition boundaries

Predicate Information helps connect a condition to the operation that uses it. A full scan may inspect substantial input and return only a few qualifying rows. The operation's output cardinality is therefore different from the number of blocks inspected.

For example, returning three matching customers does not demonstrate that Oracle located them with three block reads. Conversely, returning many customers does not prove that a scan was wasteful. Compare the required result with the work needed to produce it.

For partitioned tables, inspect the parent partition operation and Pstart/Pstop information. Partition pruning can restrict the segments scanned. TABLE ACCESS FULL under a partition operator does not necessarily mean every partition of the logical table was scanned.

These details establish the scope of the operation. They do not provide a complete runtime timeline. Do not infer precise filtering chronology or physical I/O from a single operation or predicate label.

Step 3: Collect execution evidence

When investigating an actual performance issue, inspect the relevant executed cursor. DBMS_XPLAN.DISPLAY_CURSOR reads cursor information rather than PLAN_TABLE. For a controlled test, request row-source statistics during execution:

SELECT /*+ GATHER_PLAN_STATISTICS */
       customer_id, last_name
FROM customers
WHERE last_name = 'Smith';

Fetch all intended results when measuring a complete execution. Some clients initially fetch only the first screen. A partial fetch can leave you examining partial work and can make comparisons with completed runs misleading.

Identify the SQL_ID and child cursor corresponding to the test. In the following template, supply the two bind values through your client before executing the display query:

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    :target_sql_id,
    :target_child_number,
    'ALLSTATS LAST'
  )
);

The account needs the performance-view permissions documented for DISPLAY_CURSOR. Arrange suitable access with the DBA rather than assuming every application user has it. Using NULL arguments for the last statement can be convenient when that context is known, but intervening client SQL can change what you inspect.

ALLSTATS LAST requests a display of available statistics. It does not retroactively collect information that was never gathered. If runtime columns are missing, check the collection method, cursor identity and execution completeness before changing display formats repeatedly.

A cursor can age out of the cache. Running EXPLAIN PLAN again then produces a new estimate, not a reconstruction of the original execution. Keep the identity and conditions of a useful test with its results so that later comparisons remain meaningful.

Compare estimates with measured work

Where statistics are available, inspect E-Rows, A-Rows, Starts, buffers, reads and elapsed work together. Estimates and actual rows should be compared at the same operation, with attention to repeated starts. An operation invoked many times is not equivalent to one started once.

A substantial cardinality mismatch can justify investigating stale statistics, skew, correlated columns, expressions or bind context. It does not identify one of those causes automatically. Estimated rows and estimated cost alone cannot demonstrate a mismatch with actual execution.

Also distinguish database work from the complete application experience. Network transfer, client fetching and rendering can contribute to observed response time. Record what was measured instead of calling every slow screen a full-scan problem.

When a full scan can be appropriate

Questions to ask before changing an access path
SituationWhat to investigate
A small tableIndex navigation and table lookups may cost more than scanning the table.
A broad result or aggregateA scan may efficiently supply most of the input the statement needs.
A selective condition on a large tableCheck index suitability and measured work before proposing a change.
A partitioned tableDetermine which partitions the plan accesses and whether pruning is possible.
Estimates differ from collected rowsInvestigate statistics, distribution, expressions and execution context.

There is no universal percentage of matching rows at which an index becomes faster. Clustering, row width, caching, storage, parallelism and the columns required by the query affect the trade-off. An expensive scan can still be the best available path for a legitimately broad request.

Stale or missing statistics belong in the investigation of estimation quality. They are not evidence that a scan is correct. Similarly, a scan that is appropriate today may deserve review when the data distribution or application workload changes.

Investigate unexpected scans systematically

First check whether the result requested by the SQL matches the application's need. A missing condition or unexpectedly broad date range can increase work regardless of the chosen access path. Fixing the query's intent is different from forcing it to use an index.

Next inspect existing indexes and the predicates they might support. An index on one column does not prove it is suitable for every expression involving that column. Composite key order, available columns and the cost of subsequent rowid lookups can matter.

Functions and implicit datatype conversions can prevent an ordinary index range scan, but they do not categorically prohibit all index access. A matching function-based index may be suitable when an expression is intentional. Before rewriting a predicate, preserve its case, null and datatype semantics.

Finally, consider distribution and statistics. Histograms can help describe skew; extended statistics can address selected relationships such as correlated columns. They solve different estimation problems and should not be prescribed together as a universal remedy.

Test a specific correction

If evidence points to inadequate statistics, review the statistics policy and gather them in a controlled setting when appropriate. The example below assumes the connected user owns the illustrative CUSTOMERS table and has the required privileges:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => USER,
    tabname => 'CUSTOMERS'
  );
END;
/

USER identifies the connected database user; it is not a substitute for an independently changed CURRENT_SCHEMA. Use the actual owner when these differ. Gathering statistics can change optimizer choices, so it is not a harmless ritual to apply whenever a scan appears.

If a suitable index is genuinely missing and the workload benefits, a candidate test on the same owned training table might be:

CREATE INDEX idx_customers_last_name
ON customers(last_name);

Check existing definitions and object names before creation. Index DDL changes the schema and affects transaction boundaries. Include storage, insert, update and delete overhead in the decision, not only the duration of one SELECT.

Creating an index does not guarantee its use. After the change, inspect the relevant plan and compare representative executions. Do not assume an already running cursor switches paths midway through its scan, or that one faster test establishes a benefit for every value.

Hints can support a carefully controlled comparison, but they should not replace diagnosis. Do not revive RULE or force index access across a workload merely to remove TABLE ACCESS FULL from displays.

I/O and fair comparisons

Unexpected scans can consume substantial CPU and I/O, particularly when repeated for requests needing few rows. Oracle can use multiblock reads, but storage and caching affect the observed behavior. The presence of a full scan is not evidence that a system parameter needs adjustment.

Compare equivalent workloads: the same required result, representative values, comparable fetch behavior and known test conditions. A first execution reading cold data and a later execution using cached blocks can differ without any improvement in the SQL.

Keep the baseline, change one justified factor, and measure again. Confirm that returned data is still correct, inspect the access path actually used, and consider the effect on other statements. This provides a defensible reason to keep or reject the change.

Check your understanding

Locate TABLE ACCESS FULL in your plan and name the object it scans. Explain how the operation can return few rows while inspecting substantial input. If the table is partitioned, identify the surrounding partition operation before describing the scope of the scan.

Then state what additional evidence you would need before proposing an index. A plan estimate, collected runtime statistics and a workload comparison answer different questions. The goal is to improve the required workload, not to eliminate an operation name.

The next lesson examines table joins and combined access paths, where repeated operations and parent-child relationships make this distinction even more important.


Oracle 26ai references


SEMrush Software 5 SEMrush Banner 5