Check the performance of Index Skip Scan

Oracle Performance Series

Index Skip ScanHow Oracle can use a composite index without the leading column, when that helps, and when it does not

Index skip scan is one of those optimizer features that is often summarized too loosely. It does not make column order unimportant, and it does not mean every predicate on a nonleading column will suddenly benefit from a composite index. What it does mean is that Oracle has an extra access path in specific cases: when the leading column is absent, the later column is selective enough, and the leading column has relatively few distinct values.

Leading key absentCore prerequisite

Few distinct prefixesWhy it can work

Later key selectiveWhy it can be worth it

Still cost-basedNot a guarantee

Table of contents

01. Mental modelWhat skip scan actually is02. Optimizer criteriaWhen Oracle considers it03. How it worksLogical subindexes and why distinctness matters04. When it helpsGood fits, weak fits, and design implications05. Reading plansHints, DBMS_XPLAN, and safe testing06. Design guidanceHow skip scan should influence index design07. MisconceptionsWhat skip scan does not mean08. Hands-on labReproducible SQL to observe the access path09. Quiz and takeawaysCheck your instincts

Section 01

Start with the right mental model: skip scan is still one composite index

Oracle documentation describes index skip scan as a way to use a composite index even when the initial column is not specified in the predicate. The optimizer does this by treating the index as a set of logical subindexes, one for each distinct leading-key value. That is why skip scan is closely tied to the number of distinct values in the leading column.

Composite index stays compositeLeading column is absentOracle probes logical slices

Key point

Skip scan is not a normal leading-prefix lookup. It is a fallback access path that becomes interesting when the leading column has relatively few distinct values and the later column is selective enough to justify probing those logical slices.

Bottom line: Skip scan works by probing a composite index through a small number of logical leading-key slices.

Section 02

When the optimizer considers a skip scan

Oracle’s SQL Tuning Guide gives two central conditions. First, the leading column of the composite index is not specified in the query predicate. Second, the leading key has relatively few distinct values while the nonleading key has many. Those two conditions explain both the appeal and the limits of skip scan.

1. Leading key missing

The predicate does not reference the first indexed column.

2. Few leading-key values

A small number of logical subindexes keeps the probe count manageable.

3. Many later-key values

The later column needs enough distinctness to make the probes worthwhile.

4. Cost still wins

Oracle still compares skip scan against other access paths, including full table scan.

Condition	Why it matters
Leading column is absent from the predicate	Without that, Oracle would normally prefer a standard leading-prefix access path instead of skip scan.
Few distinct values in the leading column	This limits the number of logical slices the optimizer must probe.
Many distinct values in the nonleading key	This makes the later-column predicate more selective and therefore more likely to justify skip scan.
Favorable cost estimate	Skip scan is optional. Oracle may still choose a full table scan or another index path.

What matters

A skip scan is usually easiest to picture on an index such as (gender, email), where gender has two values and email is much more selective.

Section 03

How skip scan works and why distinctness changes everything

Oracle documentation says the number of logical subindexes is determined by the number of distinct values in the leading column. That is the performance hinge. Two logical slices can be manageable. Hundreds or thousands can make skip scan unattractive very quickly.

Good fit

Index (cust_gender, cust_email) with two gender values and a highly selective email predicate.

Weak fit

Index (region_code, account_id) where the leading column has many distinct values, multiplying the slice probes.

Bottom line: The leading column’s distinctness drives how many logical slices Oracle has to examine.

Section 04

When skip scan helps, and when it should make you rethink the index

Skip scan is useful when it saves you from discarding a composite index entirely for a later-column predicate. It is much less useful as an excuse to ignore column order. If a query pattern on the nonleading column is common and important, that may still point to a different index design rather than hoping skip scan always carries the workload.

Workload pattern	How skip scan fits	Design instinct
Occasional nonleading-column lookup on an index whose leading column has very few values	Often reasonable	Skip scan may be enough if the pattern is occasional and selective.
Frequent business-critical lookup on the nonleading column	Possible, but not necessarily ideal	Consider whether a dedicated index or a different column order better matches the workload.
Leading column has many distinct values	Usually weak	Do not assume skip scan will rescue the design.
Broad result set where full table scan is cheap	Often unnecessary	Oracle may rightly prefer a table scan.

Design caution

Skip scan is a useful exception, not a replacement for workload-driven index design. A composite index should still be ordered primarily for the predicates you care about most.

Good use

A low-distinctness leading column and a highly selective later-column predicate.

Borderline use

A later-column predicate that matters often enough to deserve its own design discussion.

Bad assumption

Treating skip scan as proof that column order no longer matters.

Section 05

How to read and test skip scans safely

The safest way to reason about skip scan is to observe real plans and compare alternatives. Oracle gives you both plan-display tools and explicit hints for skip scans, including INDEX_SS, INDEX_SS_ASC, INDEX_SS_DESC, and NO_INDEX_SS. Use those hints for controlled testing, not as a substitute for understanding why the plan changed.

1. Gather stats

Give the optimizer current table and index metadata.

2. Run the real statement

Prefer representative predicates and runtime stats.

3. Inspect DBMS_XPLAN

Look for INDEX SKIP SCAN and compare actual row-source behavior.

4. Compare responsibly

Use hints to test alternatives, then remove them and decide from evidence.

SQL - Runtime plan verification

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

ALTER SESSION SET statistics_level = ALL;

SELECT /*+ gather_plan_statistics */ customer_id, cust_email
FROM   customer_demo
WHERE  cust_email = 'user50000@example.com';

SELECT *
FROM   TABLE(
         DBMS_XPLAN.DISPLAY_CURSOR(
           NULL,
           NULL,
           'ALLSTATS LAST +PREDICATE +BYTES +COST'
         )
       );

SQL - Hints for controlled testing

-- Encourage a skip scan on the composite index.
SELECT /*+ INDEX_SS(c customer_demo_gdr_email_ix) */ customer_id
FROM   customer_demo c
WHERE  cust_email = :email_value;

-- Exclude skip scan for the same index and compare the chosen path.
SELECT /*+ NO_INDEX_SS(c customer_demo_gdr_email_ix) */ customer_id
FROM   customer_demo c
WHERE  cust_email = :email_value;

Testing tip

INDEX_SS_ASC and INDEX_SS_DESC exist for skip-scan variants too, but the main design question is usually whether skip scan is appropriate at all.

Section 06

Design guidance: let skip scan inform decisions, not replace them

A composite index that occasionally benefits from skip scan can still be a good design. But if skip-scan-driven queries are frequent enough to matter, you should examine whether column order, overlap with other indexes, or a dedicated index on the later column would better fit the workload.

Healthy interpretation

Skip scan is one more reason not to oversimplify how Oracle uses composite indexes.

Unhealthy interpretation

Skip scan means the leading column does not matter, so any column order will do.

SQL - Test an alternative safely with an invisible index

CREATE INDEX customer_demo_email_ix_inv
  ON customer_demo(cust_email)
  INVISIBLE;

ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

-- Now compare plans carefully in a controlled session.

Why this is useful

Invisible indexes are maintained by DML but ignored by the optimizer unless you enable them explicitly. That makes them useful for comparing a dedicated alternative against a skip-scan path without changing the rest of the workload immediately.

SQL - Monitor whether the existing index is actually used

ALTER INDEX customer_demo_gdr_email_ix MONITORING USAGE;

SELECT index_name, used, monitoring, start_monitoring, end_monitoring
FROM   user_object_usage
WHERE  index_name = 'CUSTOMER_DEMO_GDR_EMAIL_IX';

Section 07

Common misconceptions about skip scan

Misconception	Better reading
“A predicate on the second column can never use the composite index.”	Oracle may consider skip scan when the leading column is absent and the distinctness pattern is favorable.
“If skip scan exists, column order is no longer important.”	Column order is still fundamental. Skip scan is an exception, not the main design rule.
“If skip scan is possible, Oracle should always prefer it.”	Skip scan is cost-based. A full table scan or another access path may still be cheaper.
“Few leading-key values are enough by themselves.”	The later key also needs to be selective enough to justify the probes.
“Hints prove the right production design.”	Hints are useful for testing, but the durable decision comes from plan evidence and workload fit.

Predicate nuance

Skip scan is about missing leading keys, not about bypassing every normal rule of composite-index design.

Cost nuance

Even a real skip-scan candidate can lose if the overall cost favors a table scan.

Design nuance

A frequent nonleading-column query may still justify a different index.

Bottom line: Skip scan expands Oracle’s options, but it does not erase the underlying importance of leading-column design.

Section 08

Hands-on lab: observe skip scan on a realistic composite index

This lab gives you a repeatable setup for testing when a later-column predicate may trigger skip scan and how that compares with a dedicated alternative.

Step 1: create a table with a low-distinctness leading column

SQL - Build demo data

DROP TABLE customer_demo PURGE;

CREATE TABLE customer_demo AS
SELECT level AS customer_id,
       CASE
         WHEN MOD(level, 2) = 0 THEN 'M'
         ELSE 'F'
       END AS cust_gender,
       'user' || level || '@example.com' AS cust_email,
       MOD(level, 1000) AS dept_id
FROM   dual
CONNECT BY level <= 100000;

CREATE INDEX customer_demo_gdr_email_ix
  ON customer_demo(cust_gender, cust_email);

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

Step 2: query on the nonleading column

SQL - Candidate skip-scan query

ALTER SESSION SET statistics_level = ALL;

SELECT /*+ gather_plan_statistics */ customer_id, cust_email
FROM   customer_demo
WHERE  cust_email = 'user50000@example.com';

SELECT *
FROM   TABLE(
         DBMS_XPLAN.DISPLAY_CURSOR(
           NULL,
           NULL,
           'ALLSTATS LAST +PREDICATE +BYTES +COST'
         )
       );

Step 3: compare against a dedicated invisible alternative

SQL - Controlled comparison

CREATE INDEX customer_demo_email_ix_inv
  ON customer_demo(cust_email)
  INVISIBLE;

ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

SELECT /*+ gather_plan_statistics */ customer_id, cust_email
FROM   customer_demo
WHERE  cust_email = 'user50000@example.com';

What to inspect

Look for whether the runtime plan shows INDEX SKIP SCAN and how that compares with the dedicated alternative.

What not to assume

Do not assume one plan will win everywhere. Data volume, caching, and statistics still matter.

Section 09

Quiz: do you have the right skip-scan instinct?

Answer from documented Oracle behavior and careful design reasoning

Q1. What has to be absent for a classic skip-scan case to arise?

The table itself.

The leading column of the composite index from the predicate.

All nonleading columns from the predicate.

The index itself.

Correct answer: the leading indexed column is absent. That is the prerequisite that makes Oracle consider skip scan rather than a normal leading-prefix lookup.

Q2. Why does a low-distinctness leading column matter so much?

Because Oracle can then ignore the rest of the index.

Because low distinctness makes every query index-only.

Because it limits how many logical leading-key slices Oracle has to probe.

Because the optimizer requires exactly two values.

Correct answer: fewer distinct leading-key values mean fewer logical probes.

Q3. Which statement best reflects healthy index-design judgment?

Use skip scan as one documented option, but still design indexes around the workload’s main predicate patterns.

Once skip scan exists, column order stops mattering.

A dedicated later-column index is never worth testing if skip scan appears once.

Hints are enough to prove the right production design.

Correct answer: skip scan expands Oracle’s options; it does not replace workload-driven design.

Q4. Why might Oracle still choose a full table scan for a real skip-scan candidate?

Because skip scan is illegal on composite indexes.

Because the optimizer never compares access paths once an index exists.

Because low leading-key distinctness automatically disables indexes.

Because skip scan is still cost-based and another path may be cheaper.

Correct answer: skip scan is optional. Oracle still chooses the access path it estimates as cheapest.

Q5. Which hint excludes skip scan for a specified index during a controlled test?

NO_FULL

NO_INDEX_SS

LEADING

NO_SORT

Correct answer: NO_INDEX_SS.

Q6. What is the safest way to compare a skip-scan path with a dedicated alternative?

Drop the composite index immediately.

Assume skip scan wins because the leading column has two values.

Test in a controlled session with current stats, runtime plans, and if needed an invisible alternative index.

Use only an EXPLAIN PLAN screenshot and stop there.

Correct answer: controlled comparison wins. Use current stats, runtime plan evidence, and safe testing techniques.

Takeaways

Skip scan is useful when a composite index has a missing leading key, a low-distinctness prefix, and a later predicate selective enough to justify probing logical slices. It broadens Oracle’s options, but it does not remove the need to design indexes around real workload patterns and to validate plans carefully.

PLAN TABLE in Oracle

Oracle Performance Series

PLAN_TABLEWhat EXPLAIN PLAN stores, how DBMS_XPLAN reads it, and where plan analysis goes wrong

PLAN_TABLE is the staging area Oracle uses for EXPLAIN PLAN output. That makes it foundational, but also easy to misuse. A plan stored in PLAN_TABLE shows what the optimizer estimated for the explained statement. It does not prove what a live cursor actually did at runtime. To use it well, you need to understand the table’s role, how DBMS_XPLAN formats it, how to read the tree, and when to move from explained plans to cursor-based evidence.

EXPLAIN PLANWrites plan rows here

DBMS_XPLANFormats and displays

Estimated, not executedCritical distinction

Cursor stats matterFor runtime truth

Table of contents

01. Mental modelWhat PLAN_TABLE is and is not02. AnatomyThe columns that matter most03. Explain-plan workflowHow statements land in PLAN_TABLE04. DBMS_XPLAN formatsDISPLAY, DISPLAY_CURSOR, and output choices05. Reading the treeHow to interpret parent-child plan steps06. Direct queries and maintenanceQuerying rows and keeping plans organized07. Limits and pitfallsWhere PLAN_TABLE can mislead you08. Lab and quizPractical workflow for plan analysis

Section 01

Start with the right mental model: PLAN_TABLE is an explain-plan repository

PLAN_TABLE is the default output table for the EXPLAIN PLAN statement, and DBMS_XPLAN.DISPLAY reads from it by default. In modern Oracle releases, Oracle supplies the infrastructure automatically, and scripts such as utlxplan.sql and catplan.sql still matter when creating plan tables or public synonyms manually.

EXPLAIN PLAN writes rowsDISPLAY reads them backNot a runtime proof

Critical distinction

An explained plan is not the same thing as an executed cursor plan. Oracle’s own documentation warns that explained plans can differ from the plan actually used at runtime, especially when bind peeking, adaptive behavior, or other runtime conditions matter.

Bottom line: PLAN_TABLE is a useful plan repository, but it is only one part of Oracle plan analysis.

Section 02

PLAN_TABLE anatomy: which columns matter most

PLAN_TABLE contains many columns, but only a subset drives most tuning conversations. The table includes identifiers for plan hierarchy, estimated row counts, bytes, costs, predicates, and extended metadata stored in XML.

Column	What it helps you understand
ID	The step number inside the plan tree.
PARENT_ID	The parent-child relationship between operations.
POSITION	Useful for ordering and reading siblings in the tree.
OPERATION	The main action, such as TABLE ACCESS, INDEX RANGE SCAN, or HASH JOIN.
OPTIONS	The subtype, such as FULL, UNIQUE SCAN, or BY INDEX ROWID.
OBJECT_NAME	The table, index, or other object involved in the step.
COST	The optimizer’s relative cost estimate for the operation or statement.
CARDINALITY	The estimated number of rows for the step.
BYTES	The estimated number of bytes produced.
OTHER_XML	Extra structured metadata used by Oracle tools and richer plan displays.

What matters first

For most tuning work, start with ID, PARENT_ID, OPERATION, OPTIONS, OBJECT_NAME, CARDINALITY, and COST. Then add predicates, notes, and runtime row-source stats when needed.

SQL - Inspect PLAN_TABLE structure

DESC plan_table;

-- Or inspect selected columns explicitly
SELECT column_name, data_type, data_length
FROM   user_tab_columns
WHERE  table_name = 'PLAN_TABLE'
ORDER BY column_id;

Section 03

How rows get into PLAN_TABLE

The normal workflow is straightforward: you issue EXPLAIN PLAN FOR ..., Oracle inserts plan rows into PLAN_TABLE, and then you display them with DBMS_XPLAN.DISPLAY. If you are comparing multiple statements, STATEMENT_ID helps you keep the plan rows separated.

1. Explain the statement

Oracle estimates a plan and writes rows into PLAN_TABLE.

2. Tag if needed

Use STATEMENT_ID when comparing more than one explained statement.

3. Display the rows

DBMS_XPLAN.DISPLAY formats the stored rows into readable output.

4. Clean up

Delete old rows when you no longer need them for that analysis cycle.

SQL - Basic explain-plan workflow

EXPLAIN PLAN FOR
SELECT e.last_name, d.department_name, e.salary
FROM   employees e
       JOIN departments d
         ON e.department_id = d.department_id
WHERE  e.salary > 10000
ORDER BY e.salary DESC;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

SQL - Use STATEMENT_ID for cleaner comparisons

EXPLAIN PLAN SET statement_id = 'EMP50' FOR
SELECT *
FROM   employees
WHERE  department_id = 50;

SELECT *
FROM   TABLE(
         DBMS_XPLAN.DISPLAY(
           table_name   => 'PLAN_TABLE',
           statement_id => 'EMP50',
           format       => 'TYPICAL'
         )
       );

Section 04

DBMS_XPLAN formats: what DISPLAY tells you, and what DISPLAY_CURSOR adds

DBMS_XPLAN.DISPLAY reads plan rows from PLAN_TABLE. DBMS_XPLAN.DISPLAY_CURSOR reads execution-plan data for statements already in the library cache. That distinction is one of the most important in Oracle plan analysis because cursor-based displays can include runtime row-source statistics, while explained plans cannot.

DISPLAY

Formats rows already stored in PLAN_TABLE, typically after EXPLAIN PLAN.

DISPLAY_CURSOR

Formats the cursor plan from the shared SQL area and can show runtime statistics such as ALLSTATS LAST.

SQL - Common DBMS_XPLAN format patterns

-- Minimal output
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY(format => 'BASIC'));

-- Default style
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY(format => 'TYPICAL'));

-- Richer output with predicates and notes
SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));

-- Runtime stats from an executed cursor
SELECT *
FROM   TABLE(
         DBMS_XPLAN.DISPLAY_CURSOR(
           format => 'ALLSTATS LAST'
         )
       );

Reading tip

When estimated rows and actual rows diverge sharply in a cursor-based display, that is often a signal to review statistics, predicates, skew, or cardinality assumptions rather than merely staring at the plan shape.

Section 05

How to read the plan tree without getting lost

A plan is a tree, not a flat checklist. ID, PARENT_ID, indentation, and sibling order tell you which steps feed which others. The safest habit is to see each row as a row source that produces data for its parent. A scan or access operation produces rows, a join combines them, and a sort or aggregate consumes the incoming stream higher up the tree.

Sample plan shape

-- Id  Operation                          Name              Rows  Cost
--  0  SELECT STATEMENT                                      5    12
--  1   SORT ORDER BY                                        5    12
--  2    NESTED LOOPS                                        5    11
--  3     TABLE ACCESS BY INDEX ROWID EMPLOYEES              5     4
--  4      INDEX RANGE SCAN          IDX_EMP_DEPT            5     2
--  5     TABLE ACCESS BY INDEX ROWID DEPARTMENTS            1     1
--  6      INDEX UNIQUE SCAN         DEPT_ID_PK              1     0

1. Find the statement root

The top SELECT STATEMENT is the overall consumer of the plan tree.

2. Trace row sources upward

Step 4 feeds step 3, and step 6 feeds step 5. Those outputs then feed the join at step 2.

3. Read operations with context

An index scan by itself is not the whole story. Ask what table access, join, or sort it feeds.

4. Compare estimates carefully

Cost and cardinality estimates are more useful when read together than in isolation.

Bottom line: Read a plan as a flow of row sources feeding parent operations, not as unrelated lines of text.

Section 06

Querying PLAN_TABLE directly and keeping it organized

DBMS_XPLAN is the preferred display layer, but direct queries against PLAN_TABLE are still useful when you want custom reporting, hierarchical views, or cleanup by STATEMENT_ID. This is especially handy during iterative tuning sessions.

SQL - Simple hierarchical query against PLAN_TABLE

SELECT LPAD(' ', 2 * LEVEL) ||
       operation || ' ' ||
       NVL(options, '') || ' ' ||
       NVL(object_name, '') AS plan_step,
       cost,
       cardinality AS est_rows
FROM   plan_table
WHERE  statement_id = 'EMP50'
CONNECT BY PRIOR id = parent_id
START WITH id = 0
ORDER SIBLINGS BY id;

SQL - Remove old explained rows for a statement

DELETE FROM plan_table
WHERE  statement_id = 'EMP50';

COMMIT;

Operational habit

If you explain many similar statements during tuning, disciplined use of STATEMENT_ID makes both cleanup and comparison much easier.

Section 07

Where PLAN_TABLE can mislead you

PLAN_TABLE is useful, but it has limits. Oracle’s own documentation distinguishes explained plans from plans already associated with executed cursors. That distinction matters because runtime decisions, bind handling, adaptive behavior, and actual row-source statistics are not fully represented in a basic explained plan.

Potential mistake	Better interpretation
Treating EXPLAIN PLAN as proof of runtime behavior	Use it as an estimate. Move to DISPLAY_CURSOR or other runtime evidence when execution truth matters.
Reading cost as elapsed time	Cost is a relative optimizer estimate, not a wall-clock measurement.
Ignoring predicates, notes, and cardinality	The operation name alone rarely tells the whole story.
Comparing explained plans without isolating STATEMENT_ID	Mixed rows in PLAN_TABLE can make analysis messy or misleading.
Stopping at plan shape	Always ask whether estimates aligned with actual row-source behavior on the executed cursor.

Estimate nuance

An explained plan is often a useful first pass, but it is still an estimate.

Runtime nuance

Cursor-based plans let you compare estimated and actual behavior more responsibly.

Workflow nuance

The healthiest tuning workflow moves from explained plan to runtime validation when needed.

Common trap

It is easy to become too confident because DBMS_XPLAN.DISPLAY looks polished. The formatting is polished; the underlying rows are still only as reliable as the explained estimate behind them.

Section 08

Hands-on workflow: explain, display, validate, then quiz yourself

This workflow is a practical way to keep explained plans and runtime plans in the right order. Start with EXPLAIN PLAN when you want a quick optimizer estimate. Then move to cursor-based evidence if the statement is worth deeper tuning.

SQL - A compact plan-analysis workflow

-- 1. Explain and tag the statement
EXPLAIN PLAN SET statement_id = 'EMP_SAL' FOR
SELECT employee_id, salary
FROM   employees
WHERE  salary > 10000
ORDER BY salary DESC;

-- 2. Display the explained plan
SELECT *
FROM   TABLE(
         DBMS_XPLAN.DISPLAY(
           statement_id => 'EMP_SAL',
           format       => 'ALL'
         )
       );

-- 3. Run the statement with runtime stats if deeper validation is needed
ALTER SESSION SET statistics_level = ALL;

SELECT /*+ gather_plan_statistics */ employee_id, salary
FROM   employees
WHERE  salary > 10000
ORDER BY salary DESC;

-- 4. Compare runtime evidence
SELECT *
FROM   TABLE(
         DBMS_XPLAN.DISPLAY_CURSOR(
           NULL,
           NULL,
           'ALLSTATS LAST +PREDICATE +BYTES +COST'
         )
       );

Good workflow

Use explained plans for a quick estimate, then step up to cursor evidence when the statement matters.

Weak workflow

Treat a clean-looking explained plan as final proof and stop there.

Answer from sound Oracle plan-analysis practice

Q1. What does DBMS_XPLAN.DISPLAY read by default?

The currently executing cursor only.

PLAN_TABLE rows.

Only SQL Monitor output.

The alert log.

Correct answer: PLAN_TABLE.

Q2. What is the safest summary of an explained plan?

It proves exactly what happened at runtime.

It is identical to DISPLAY_CURSOR.

It shows an optimizer estimate for the explained statement, not guaranteed runtime truth.

It is useful only for historical plans.

Correct answer: an explained plan is an estimate.

Q3. Why is STATEMENT_ID useful?

It keeps explained plan rows organized when multiple statements are being compared.

It forces Oracle to use a different optimizer.

It changes runtime row counts.

It removes the need for DBMS_XPLAN.

Correct answer: organization and isolation.

Q4. When do you usually move from DISPLAY to DISPLAY_CURSOR?

Never. DISPLAY is enough for all tuning.

Only when the SQL text is short.

Only after deleting PLAN_TABLE.

When you need runtime evidence, such as estimated versus actual rows.

Correct answer: move to cursor evidence when runtime truth matters.

Takeaways

PLAN_TABLE matters because it supports EXPLAIN PLAN workflows and quick plan inspection. But the strongest plan-analysis habit is to treat explained plans as estimates, use DBMS_XPLAN deliberately, and move to cursor-based evidence when a tuning decision depends on runtime truth.

Using Parallelism in Oracle Database 12C

By Gowthami | apps-dba.com | Oracle Performance Series

Oracle Parallel Query (PQ) allows a single SQL statement to be executed by multiple parallel execution servers simultaneously, dramatically reducing elapsed time for large data operations. It is the primary tool for accelerating full table scans, large sorts, and bulk DML on multi-CPU systems.

Key Insight: Parallel query trades throughput for response time. A query using DOP 8 uses 8x the CPU and I/O resources but may run 6-7x faster. Use parallelism for batch jobs and large reports — not for OLTP queries where resource contention hurts all users.

Parallel Query Architecture

A parallel query uses a Query Coordinator (QC) — the session that submits the query — and multiple Parallel Execution Servers (PX servers) that perform the actual work. The QC distributes work, collects results, and returns them to the user.

-- Check current parallel configuration
SHOW PARAMETER parallel_max_servers;     -- max PX servers in pool
SHOW PARAMETER parallel_min_servers;     -- pre-started PX servers  
SHOW PARAMETER parallel_degree_policy;   -- MANUAL, AUTO, or ADAPTIVE

-- Check active parallel queries
SELECT qcsid, server#, degree, req_degree, sql_text
FROM v$px_session ps
JOIN v$sql s ON ps.sql_id = s.sql_id;

Enabling Parallelism

-- Method 1: Table-level parallel degree (persistent)
ALTER TABLE large_sales_table PARALLEL 8;

-- Method 2: Query-level hint (preferred for control)
SELECT /*+ PARALLEL(s, 8) */ 
       region, SUM(amount) 
FROM large_sales_table s
GROUP BY region;

-- Method 3: Session-level (affects all queries in session)
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;
ALTER SESSION FORCE PARALLEL DML PARALLEL 4;
ALTER SESSION FORCE PARALLEL DDL PARALLEL 4;

-- Reset session-level parallelism
ALTER SESSION DISABLE PARALLEL QUERY;

Parallel DML Operations

-- Parallel DML must be explicitly enabled
ALTER SESSION ENABLE PARALLEL DML;

-- Parallel INSERT
INSERT /*+ PARALLEL(t, 8) APPEND */ INTO target_table t
SELECT /*+ PARALLEL(s, 8) */ * FROM source_table s
WHERE created_date >= DATE '2024-01-01';
COMMIT;

-- Parallel UPDATE (12c+)
UPDATE /*+ PARALLEL(e, 4) */ large_emp_table e
SET salary = salary * 1.05
WHERE department_id IN (10, 20, 30);
COMMIT;

-- Parallel CREATE TABLE AS SELECT (CTAS)
CREATE /*+ PARALLEL(8) */ TABLE archive_sales
PARALLEL 8 NOLOGGING AS
SELECT /*+ PARALLEL(s, 8) */ * FROM sales_history s
WHERE sale_year = 2023;

Monitoring Parallel Query Execution

-- Monitor active parallel queries (12c+)
SELECT sql_id, sql_text, px_servers_requested, px_servers_allocated,
       elapsed_time/1000000 elapsed_sec, cpu_time/1000000 cpu_sec
FROM v$sql_monitor
WHERE px_servers_requested > 0
  AND status = 'EXECUTING'
ORDER BY elapsed_time DESC;

-- Check parallel operations in execution plan
EXPLAIN PLAN FOR
SELECT /*+ PARALLEL(s, 4) */ region, SUM(amount)
FROM sales s GROUP BY region;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- Look for: PX COORDINATOR, PX SEND/RECEIVE, PX BLOCK ITERATOR

Choosing the Right Degree of Parallelism

Operation Type	Recommended DOP	Notes
Ad-hoc large reports	4-8	Balance speed vs resource use
Nightly ETL/batch	CPUs/2 to CPUs	Off-peak, maximize throughput
CTAS / index rebuild	CPUs or CPUs/2	DDL benefits most from high DOP
OLTP queries	1 (no parallel)	Parallelism hurts OLTP concurrency
Exadata queries	AUTO or ADAPTIVE	Exadata manages DOP automatically

Summary

Oracle Parallel Query is a powerful weapon for large data operations. The key is using it judiciously — enable it for batch jobs, large reports, and maintenance operations on large tables, but avoid it for OLTP workloads. Always monitor with V$SQL_MONITOR to ensure parallel queries are actually running at the requested DOP.

Oracle Exadata - The Complete Guide

Learn how Exadata Smart Scan supercharges parallel query performance and master all Exadata-specific optimization techniques with Gowthami's guide.

Get the Book