Understand Execution Plan

Information

Execution Plan Columns

Column	Detailed Explanation
Id	A numeric identifier assigned to each step in the execution plan. The steps form a hierarchical tree, where higher-level steps (lower IDs) depend on lower-level operations. Oracle begins execution from the innermost child node (highest ID) and proceeds upwards. Indentation or tree structure (in some tools) reflects parent-child relationships between operations.
Operation	Describes the type of action Oracle performs at that step. Examples include: • TABLE ACCESS FULL – Full table scan • INDEX RANGE SCAN – Accessing index via range • HASH JOIN – Performing a join using hashing technique • SORT AGGREGATE – Aggregate function with sorting This is the most crucial column to understand how Oracle is accessing data and combining it.
Name	Indicates the name of the object (table, index, or view) used in that step. This helps confirm whether Oracle is using the correct index, or if it's accessing the expected table (especially in views or synonyms). A blank value usually indicates that the operation does not directly access a named object (e.g., a scalar function or derived row).
Rows	Estimated number of rows that the step will output to its parent. This estimate is based on optimizer statistics (e.g., table size, data distribution, histograms). Accurate row estimates are crucial because they determine join methods, access paths, and overall cost. If the estimate is inaccurate, the optimizer may choose a suboptimal plan.
Bytes	Estimated size (in bytes) of the data output by this step. Calculated as: Estimated Rows × Average Row Size. This helps the optimizer understand memory and I/O implications, especially for operations like sorting, joins, or aggregation. High byte estimates may trigger use of TEMP tablespace or reduce parallelism.
Cost (%CPU)	A relative measure of resource usage, used by Oracle to compare alternative execution plans. The value is derived from a combination of factors like I/O cost, CPU usage, and memory. The number in parentheses is the percentage of that cost attributed to CPU. This is not an actual runtime metric but a modeling approximation. Lower cost generally implies better efficiency relative to other paths, not absolute performance.
Time	Estimated execution time for the step in HH:MM:SS format. This is computed by Oracle using I/O and CPU cost formulas, assuming ideal conditions. This is not the actual time the step took in real execution—it is merely a projection. For real-time metrics, use DBMS_XPLAN.DISPLAY_CURSOR with actual execution statistics.
Predicate Info (e.g., * asterisk)	A * next to the ID or operation indicates that a filter or access predicate is applied at that step. These predicates can be seen in detail when using DBMS_XPLAN.DISPLAY with the +PREDICATE or ALL options. Predicates are key for understanding how efficiently Oracle is filtering data—either during access or after data is fetched.

Example 1

| Id  | Operation                              | Name                    | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                         |    1 |    63 |    32   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID           | USER_ENTITY             |    1 |    63 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN                    | CONSTRAINT_FB           |    1 |       |     1   (0)| 00:00:01 |
|   3 |  TABLE ACCESS BY INDEX ROWID BATCHED   | USER_ATTRIBUTE          |    1 |   102 |    30   (0)| 00:00:01 |
|*  4 |   INDEX SKIP SCAN                      | IDX_USER_ATTRIBUTE_NAME |    2 |       |    28   (0)| 00:00:01 |

Oracle uses cost-based optimization (CBO) to estimate query execution costs and choose the most efficient plan.

Explanation

Step 0 (Top Level) Oracle is executing a SELECT statement. The total estimated cost of the query is 32. That means Oracle expects this query to be relatively cheap, but not negligible.

Step 1 and 2 Oracle is accessing the USER_ENTITY table using an index called CONSTRAINT_FB. The scan type is INDEX UNIQUE SCAN, which is very efficient. It means Oracle expects only one row to match. This is a good sign.

Step 3 and 4 Oracle then accesses the USER_ATTRIBUTE table. It is using Batched Rowid Access, which is more efficient than individual row-by-row lookups. However, the index being used is IDX_USER_ATTRIBUTE_NAME with an Index Skip Scan.

An index skip scan is used when the leading column of a composite index is not part of the filter condition. Oracle skips through index blocks, which is more expensive than a range scan. This is where most of the cost lies — 28 for the index operation and 30 for the table access.

Observations

1. Overall Cost is 32

   • This is the estimated total cost for Oracle to execute this query.

   • Lower is better, but it depends on the dataset size and indexing.

2. INDEX UNIQUE SCAN (CONSTRAINT_FB) has Cost = 1

   • Since it's a primary key lookup, it's extremely efficient.

   • It retrieves userentity0_.id using the primary key.

3. INDEX SKIP SCAN (IDX_USER_ATTRIBUTE_NAME) has Cost = 28

   • Oracle is skipping over indexed values instead of performing a full scan.

   • Problem? If user_attribute.value has high cardinality (many distinct values), an INDEX RANGE SCAN would be better.

4. TABLE ACCESS BY INDEX ROWID (USER_ATTRIBUTE) has Cost = 30

   • Fetching rows from USER_ATTRIBUTE based on the INDEX SKIP SCAN result.

   • The cost is high because many rows may match the filter on value.

How to Improve This Query?

1. Improve Indexing Strategy

• Use a Composite Index (value, user_id)

  CREATE INDEX idx_user_attribute_value_userid 
  ON user_attribute(value, user_id);

  • This helps Oracle directly locate user_id, avoiding the skip scan.

  • Expected result: The optimizer should switch to INDEX RANGE SCAN instead.

2. Force an Index Hint

If Oracle keeps using INDEX SKIP SCAN, you can force it to use a better index:

SELECT /*+ INDEX(userattrib1_ idx_user_attribute_value_userid) */
    userentity0_.username
FROM user_entity userentity0_
INNER JOIN user_attribute userattrib1_ 
    ON userentity0_.id = userattrib1_.user_id
WHERE userattrib1_.value = '1311310003';

3. Check Index Statistics

Run:

SELECT table_name, num_rows FROM user_tables WHERE table_name IN ('USER_ENTITY', 'USER_ATTRIBUTE');
SELECT index_name, num_rows, distinct_keys FROM user_indexes WHERE table_name = 'USER_ATTRIBUTE';

• If num_rows is large but distinct_keys for IDX_USER_ATTRIBUTE_NAME is low, the INDEX SKIP SCAN is inefficient.

• In this case, a new composite index (value, user_id) will help.