EasyReliableDBA: Eaxadata and Oracle Database interview question and answer 2026 Part5

Question :excution plan with full tables scan and its interpretion and wrong join etc and troubleshoot with example and test cases in oracle 19c and 23ai

An execution plan represents the exact sequence of steps the Oracle Cost-Based Optimizer (CBO) follows to execute a SQL statement. Diagnosing inefficiencies like accidental Full Table Scans (FTS) or wrong join methods requires matching estimated statistical cardinalities against actual execution behavior.

While both Oracle 19c and Oracle 23ai share identical plan reading methodologies, Oracle 23ai introduces automated self-correction capabilities via Real-Time SQL Plan Management (SPM) to automatically neutralize poorly performing plans on subsequent executions.

1. Generating and Interpreting a Plan with FTS and Wrong Joins

When evaluating a plan, parse the steps using the "First Leaf / Top-Down, Right-Left" tree evaluation rule. Data flows upwards from indented child steps (leaves) to parent nodes.

Step-by-Step Execution Plan Breakdown

text

----------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |         |     1 |    43 |   415  (1)| 00:00:01 |
|*  1 |  HASH JOIN                   |         |     1 |    43 |   415  (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | DEPT    |     4 |    64 |     3  (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL          | EMP     |     1 |    27 |   411  (1)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   3 - filter("E"."EMPNO"=7934)

Detailed Interpretation

Step 0: The entry and return point of the cursor.
Step 2 (TABLE ACCESS FULL on DEPT): This is the first leaf node with no children. Oracle reads every block of the DEPT table below its High Water Mark (HWM) using multi-block I/O reads. Because it is listed first under the HASH JOIN, it becomes the driving (outer) table, used to build the hash table in memory.
Step 3 (TABLE ACCESS FULL on EMP): This is the second leaf node. It scans all blocks of the EMP table to act as the probed (inner) table.
Step 1 (HASH JOIN): Oracle loops through the scanned rows from EMP and probes the hash table built from DEPT to find key matches based on the join predicate E.DEPTNO = D.DEPTNO.

Identifying Optimizer Anomalies

The Wrong Scan: Step 3 filters on a unique ID (EMPNO=7934). The optimizer estimates it will fetch exactly Rows = 1. Performing a full table scan (TABLE ACCESS FULL) to isolate a single row out of a large table is highly inefficient and indicates a missing index, stale stats, or suppressed columns.
The Wrong Join Method: While a HASH JOIN is efficient for pairing large datasets, fetching a single record matching an explicit lookup constraint should instead trigger a NESTED LOOPS join utilizing an Index Range or Unique Scan.

2. Comprehensive Troubleshooting Test Case

This end-to-end test case reproduces an unexpected FTS along with an inefficient join method, demonstrates how to pinpoint the root cause, and provides the solution paths for both Oracle 19c and 23ai.

Setup Script (Tables, Indexes, and Mock Data)

sql

-- Create reference dimension table
CREATE TABLE dept (
    deptno NUMBER GENERATED BY DEFAULT AS IDENTITY TARGET SERVICES PRIMARY KEY,
    dname  VARCHAR2(30) NOT NULL
);

-- Create large fact table
CREATE TABLE emp (
    empno    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    ename    VARCHAR2(50),
    deptno   NUMBER,
    padding  VARCHAR2(200)
);

-- Insert dummy data into DEPT
INSERT INTO dept (deptno, dname) VALUES (10, 'ACCOUNTING');
INSERT INTO dept (deptno, dname) VALUES (20, 'RESEARCH');
INSERT INTO dept (deptno, dname) VALUES (30, 'SALES');
INSERT INTO dept (deptno, dname) VALUES (40, 'OPERATIONS');

-- Insert 100,000 dense rows into EMP to scale the data blocks
BEGIN
    FOR i IN 1..100000 LOOP
        INSERT INTO emp (ename, deptno, padding) 
        VALUES ('EMPLOYEE_' || i, MOD(i, 4) * 10 + 10, RPAD('X', 150, 'X'));
    END LOOP;
    COMMIT;
END;
/

Reproducing the Issue (Stale/Missing Statistics Scenario)

If an application runs queries before statistics collection occurs, or if optimizer paths are intentionally disrupted, bad plans surface. Let us simulate this behavior by intentionally locking out indexes or locking statistics:

sql

-- Make the primary key index on EMP invisible to force the problem
ALTER INDEX SYS_C0084321 INVISIBLE; -- Substitute with your actual generated PK index name

Execute the target query and fetch its raw cursor execution plan using DBMS_XPLAN.DISPLAY_CURSOR:

sql

-- Execute the target query containing the unique literal lookup
SELECT /*+ MONITOR */ e.ename, d.dname 
FROM emp e 
JOIN dept d ON e.deptno = d.deptno 
WHERE e.empno = 7934;

-- Retrieve the runtime execution details from memory
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST +MEMUSAGE'));

Reviewing the Flawed Runtime Output

text

---------------------------------------------------------------------------------------------------

| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | OMem | OMax|
---------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:00.12 |    2145 |      |     |
|*  1 |  HASH JOIN          |      |      1 |      1 |      1 |00:00:00.12 |    2145 | 1024K| 1024K|
|   2 |   TABLE ACCESS FULL | DEPT |      1 |      4 |      4 |00:00:00.01 |       7 |      |     |
|*  3 |   TABLE ACCESS FULL | EMP  |      1 |      1 |      1 |00:00:00.11 |    2138 |      |     |
---------------------------------------------------------------------------------------------------

Diagnosis: The Buffers column shows Oracle read 2,138 data blocks from the database cache just to locate one single row (A-Rows = 1)

3. Resolving the Execution Anomalies

The Traditional Tuning Fix (Oracle 19c & 23ai)

To resolve this issue, make the index visible again (or add a missing column index) and recompute object statistics so the CBO recognizes data densities accurately.

sql

-- Step 1: Ensure the access path column index is visible and valid
ALTER INDEX SYS_C0084321 VISIBLE;

-- Step 2: Gather precise CBO object statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'EMP', cascade => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'DEPT', cascade => TRUE);

Executing the Optimized Path

sql

SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE e.empno = 7934;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'));

The Remediated Plan

text

-------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name            | Starts | E-Rows | A-Rows | Buffers |
-------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |                 |      1 |        |      1 |       4 |
|   1 |  NESTED LOOPS                 |                 |      1 |      1 |      1 |       4 |
|   2 |   TABLE ACCESS BY INDEX ROWID | EMP             |      1 |      1 |      1 |       3 |
|*  3 |    INDEX UNIQUE SCAN          | SYS_C0084321    |      1 |      1 |      1 |       2 |
|   4 |   TABLE ACCESS BY INDEX ROWID | DEPT            |      1 |      1 |      1 |       1 |
|*  5 |    INDEX UNIQUE SCAN          | SYS_C0081115    |      1 |      1 |      1 |       0 |
-------------------------------------------------------------------------------------------------

Performance Impact: Logical block reads dropped from 2,145 buffers down to just 4 buffers, completely eliminating the FTS. The optimizer selected a NESTED LOOPS join because it correctly identified a low cardinality lookup value.

The Next-Gen Autonomous Solution: Real-Time SPM (Oracle 23ai Exclusive)

In legacy versions like Oracle 19c, a bad plan remains active in the shared pool until manual DBA intervention or a nightly maintenance task occurs.

Oracle 23ai introduces Real-Time SQL Plan Management (SPM). If a query exhibits suboptimal execution characteristics (such as high block reads from a sudden structural change or bad bind parameters), Oracle 23ai tracks the deviation instantly.

sql

-- Verify Real-Time SPM parameters are active in your 23ai environment
ALTER SESSION SET OBJECT_STATISTICS_ACCURACY = 100;

How 23ai Intervenes Automatically

First Execution: The database detects an unexpected performance degradation during the TABLE ACCESS FULL phase.
Background Automation: Oracle 23ai creates a SQL Plan Baseline in the background, tags the current plan as REJECTED, and automatically explores an alternative path using available unique index scans.
Second Execution: When the same query runs again, the engine automatically swaps out the bad plan for the optimized INDEX UNIQUE SCAN and NESTED LOOPS combination. This entirely removes the need for manual tuning scripts or emergency hint additions. [

Summary of Tuning Indicators

Plan Symptom	Root Cause	Target Remediation Path
`TABLE ACCESS FULL` on unique filters	Missing, invisible, or suppressed index on the `WHERE` clause column.	Create/expose index; remove functions wrapping the column.
High `Buffers` vs. Low `A-Rows`	The database is reading extensive data blocks to return only a few rows.	Add a selective composite index to limit scanned block ranges.
`HASH JOIN` chosen for single row targets	Stale or ungathered optimizer statistics mimicking high row estimates.	Update object statistics via `DBMS_STATS.GATHER_TABLE_STATS`.

SQL Plan Management (SPM) preserves predictable performance by "locking" verified, high-performing execution plans for specific SQL statements. It intercepts Cost-Based Optimizer (CBO) path changes, automatically rejecting inefficient plans while continually verifying new plans for potential performance gains.

1. Plan Baseline Structure & Interpretation

Baselines are made up of outlines (the set of hints forcing a specific plan), search structures, and execution plans. They can be queried via DBA_SQL_PLAN_BASELINES.

Key Columns to Check:

ENABLED: Determines if the baseline can be used (Must be YES).
ACCEPTED: Determines if the plan is validated and allowed to be used by the optimizer (Must be YES).
FIXED: When set to YES, prevents the optimizer from adding or accepting any new plans (even if they are proven to be better).

How to Interpret Execution Plans with Baselines:
When generating an actual plan via DBMS_XPLAN.DISPLAY_CURSOR, look at the Notes section at the bottom. [

If a baseline is used, you will see a note like: SQL plan baseline SPM_... used for this statement.
If a plan fails to use the baseline, the Notes will tell you exactly why (e.g., PLAN_GENERATION_ERROR or MULTI_PLAN).

2. Identifying Wrong Joins & Other Common Issues

Optimizer miscalculations and "wrong join" issues are typically caused by stale/missing statistics or corrupted bind-variable peeking (especially when data distributions are skewed).

Cartesian / Wrong Joins: The CBO may estimate a row count of 1 for an un-analyzed intermediate table, resulting in the optimizer choosing a Nested Loop instead of a Hash Join. At runtime, the nested loop blows up into a Cartesian product.
Missing Indexes: A schema drop/recreate might drop an index entirely, forcing the optimizer to revert to massive full table scans, radically spiking CPU and I/O.
Adaptive Plan Failures: In Oracle 19c, if OPTIMIZER_ADAPTIVE_PLANS is enabled, collectors might fire mid-execution and alter the subplan. Sometimes, this mid-execution switch causes hanging or erratic performance.

3. Troubleshooting & Diagnostics in 19c vs. 23ai

Oracle 19c Troubleshooting

Forcing Baseline Usage (Diagnostic Flag): If a plan runs perfectly manually but the baseline won't latch on (often due to missing underlying objects like invisible indexes), force the database to use the outline:
ALTER SESSION SET "_sql_plan_management_control"=4;
Tracing SPM: Enable a compiler trace to inspect why a baseline was bypassed:
ALTER SESSION SET events 'trace [SQL_Compiler.*]'; (Review the SPM Plan Dump in the resulting trace file).
Evolution: If a new plan is better, evolve it using DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE to move it from unaccepted to accepted status.

Oracle 23ai Enhancements

Real-Time SPM: This major 23ai feature automatically detects SQL performance regressions in real time. The database continuously captures execution performance into an Automatic SQL Tuning Set (ASTS). If a hard-parsed query generates a new, poor plan, Real-Time SPM dynamically intercepts it and locks back onto the proven baseline automatically.
ADD_VERIFIED_SQL_PLAN_BASELINE: A native SPM API backported to 19c, but fully leveraged in 23ai, allows you to pull plans directly into a baseline after verifying them to ensure immediate workload safety.

Execution Plan Baselines (SQL Plan Management/SPM) guarantee query stability. They lock in known good execution plans to prevent performance drops during database upgrades, statistics updates, or schema changes.

1. Execution Plan Baseline & Interpretation

An execution plan baseline is a set of accepted execution plans for a specific SQL statement. The Cost-Based Optimizer (CBO) is forced to only use an accepted plan, or the lowest-cost fixed plan if one is defined.

Interpretation & Diagnostics:

Baseline Status: A baseline must be ACCEPTED to be used. Unverified or failed plans are marked UNACCEPTED or REPRODUCE = NO.
Troubleshooting Unused Baselines: If a plan is ACCEPTED but not used, it is usually because an object (like an index or partition) was dropped or changed, rendering the baseline path impossible.
19c & 23ai Diagnostics: In Oracle 19c, use alter session set "_sql_plan_management_control"=4; to force the database to diagnose why the baseline fails to match. Oracle 23ai introduces Real-Time SPM which auto-monitors queries and instantly reverts to baseline plans if a sudden performance degradation occurs (via Automatic SQL Tuning Sets).

2. Partition Table & Wrong Join Issues

Partitioning splits large tables into manageable pieces but introduces specific join risks:

Issue: Partition Pruning Failure: The optimizer fails to restrict the query to specific partitions.
- Cause: The WHERE clause applies functions to the partition key (e.g., WHERE TRUNC(order_date) = '...'), which hides the partition key from the optimizer.
- Fix: Change the predicate to avoid implicit data type conversions or functions (e.g., WHERE order_date >= ... AND order_date < ...).
Issue: Wrong/Missing Data on Partition Exchanging: Swapping a non-partitioned table with a partition can result in rows that do not belong to that partition.
- Fix: Use ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE ... WITHOUT VALIDATION cautiously, or include the INCLUDING INDEXES clause to avoid index staleness. Run ORA_PARTITION_VALIDATION to verify rows match partition definitions.
Issue: Cartesian Joins / Full Partitions Scan (Wrong Join): Joining unaligned partitioned tables may cause the optimizer to perform serial nested loops across all partitions, destroying performance.
- Fix: Ensure equijoins happen on the exact partition keys. Check for Partition-Wise Joins in the execution plan (if doing full partition-wise joins, both tables must be partitioned on the same key using the same partitioning method).

3. Other Issues & Troubleshooting

Beyond baselines and partitioning, here is how to troubleshoot other common Oracle engine problems:

Stale Statistics: The optimizer relies heavily on stats. If partition data is loaded without updating stats, execution paths will be poor.
- Fix: Run DBMS_STATS.GATHER_TABLE_STATS with CASCADE=>TRUE and DEGREE for large tables.
Cursor Sharing/Bind Peeking Issues: The execution plan is optimized for the first set of bind variables executed, and subsequent executions perform poorly.
- Fix: Use Adaptive Cursor Sharing or bind-variable aware cursor techniques. In 19c+, you can use SQL Plan Management to capture good bind variable plans.
Adaptive Plans Defaulting to Suboptimal Subplans: In 19c+, adaptive plans defer choices (like Nested Loop vs. Hash Join) until runtime.
- Fix: If the dynamic runtime choice is consistently poor, stabilize the plan by locking an optimal non-adaptive plan into a SQL Baseline. []

4. How to Resolve a Wrong Join or Regressed Plan

Isolate the SQL_ID: Use the Oracle AWR Historical Data or V$SQL to grab the regressed statement.
Determine the Baseline Status: SELECT sql_id, plan_name, enabled, accepted, fixed FROM dba_sql_plan_baselines WHERE sql_text LIKE '%...%';
Fix/Roll Back the Plan:
- If an unaccepted plan (better plan) was captured but not validated, run DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE.
- If no good baseline exists, capture the current good execution plan to a SQL Tuning Set and load it manually using DBMS_SPM.LOAD_PLANS_FROM_SQLSET.
- If a bad baseline is forcing a wrong join, disable it via DBMS_SPM.ALTER_SQL_PLAN_BASELINE(plan_name => '...', attribute_name => 'ENABLED', attribute_value => 'NO')

Question : Primary production database and standby database and production has crashed and restored with reset log mode so what would be action plan for standby database

Because your primary database was restored and opened with RESETLOGS, a new database incarnation has been created. Your standby database will need to be re-synchronized or rebuilt to match this new branch of redo.

The action plan depends on whether Flashback Database was enabled on the standby.

Option 1: Standby Flashback is Enabled (Recommended & Fastest)

If Flashback is enabled and your DB_FLASHBACK_RETENTION_TARGET is sufficient to cover the time of the crash/restore, Oracle's Managed Recovery Process (MRP) can automatically sync the standby to the new incarnation.

Cancel recovery on the standby:
sql
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Flashback the standby to just before the primary's RESETLOGS SCN:

sql

FLASHBACK STANDBY DATABASE TO SCN <resetlogs_scn_minus_2>;

Restart the redo apply service:

sql

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Option 2: Standby Flashback is Disabled

If Flashback is not enabled, the old redo branch is broken. You cannot continue applying logs. You have two options here:

Method A: Recreate the Standby using RMAN (No downtime for primary)

You can use RMAN to roll the standby forward over the network without taking a full backup.

Connect to the standby database, stop redo apply, and start it in nomount state:
sql
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SHUTDOWN IMMEDIATE; STARTUP NOMOUNT;
Restore a new standby controlfile from the primary:
sql
RESTORE STANDBY CONTROLFILE FROM SERVICE '<primary_tns_alias>';
Mount the standby database and roll forward the datafiles over the network:

sql

ALTER DATABASE MOUNT;
RESTORE DATABASE FROM SERVICE '<primary_tns_alias>';
RECOVER DATABASE FROM SERVICE '<primary_tns_alias>';

Restart managed recovery:

sql

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Method B: Rebuild the Standby (Traditional Approach)

If the network is too slow, or you prefer a clean rebuild, you will need to re-instantiate the standby.

Take a fresh RMAN incremental backup of the restored primary database.
Transfer the backup pieces to the standby host and catalog them, or use the RMAN DUPLICATE command to copy files over.
Re-register the standby controlfile and resume Redo Apply.

or

When the primary database crashes and is restored using incomplete recovery with RESETLOGS, a new database incarnation is created. The previous standby database is now out of sync because its control file and logs do not recognize the new redo branch.

Follow this action plan to restore the standby.

1. Action Plan: Fixing the Standby

You must resynchronize the standby control file with the primary's new incarnation branch. Choose one of two options based on your environment:

Option A: RMAN RECOVER FROM SERVICE (Preferred & Fastest)

No full backups or file transfers are required as this copies over the network.

Mount the standby database:
sql
SHUTDOWN IMMEDIATE; STARTUP MOUNT;
Run RMAN and restore the standby control file from the primary:

text

RMAN> RESTORE STANDBY CONTROLFILE FROM SERVICE '<primary_tns_alias>';

Mount the newly restored control file:

sql
SHUTDOWN IMMEDIATE; STARTUP MOUNT;
Roll forward the standby database over the network:

text

RMAN> RECOVER DATABASE FROM SERVICE '<primary_tns_alias>';

Start managed recovery on the standby:

sql

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Option B: Re-create Standby via Incremental Backup (Best for slow networks)

On the primary, get the RESETLOGS_CHANGE#:
sql
SELECT RESETLOGS_CHANGE# FROM V$DATABASE;
Take an incremental backup on the primary, starting from the SCN of the RESETLOGS:

text

RMAN> BACKUP INCREMENTAL FROM SCN <resetlogs_scn> DATABASE FORMAT '/tmp/standby_inc_%U';

Copy the backup pieces to the standby server.

Restore the standby control file and catalog the incremental backup on the standby:

text

RMAN> RESTORE STANDBY CONTROLFILE FROM '<backup_location>';
RMAN> CATALOG START WITH '/tmp/standby_inc';
RMAN> RECOVER DATABASE;

Mount and start log apply.

2. Troubleshooting Guide

If the standby is still not receiving or applying logs after you perform the sync, investigate using these queries and steps.

Check for Database Incarnation Mismatch

Run this on the standby to ensure it recognizes the primary's new incarnation. If the standby is on a different incarnation, register the primary logs:

sql

SELECT INC.INCARNATION#, INC.RESETLOGS_CHANGE#, INC.STATUS 
FROM V$DATABASE_INCARNATION INC;

Identify Redo Apply Errors & Gaps

Run this on the standby to see if it is lagging behind the primary:

sql

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Seq Received", APPL.SEQUENCE# "Last Seq Applied", 
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Gap" 
FROM (SELECT THREAD#, MAX(SEQUENCE#) SEQUENCE# FROM V$ARCHIVED_LOG GROUP BY THREAD#) ARCH, 
(SELECT THREAD#, MAX(SEQUENCE#) SEQUENCE# FROM V$ARCHIVED_LOG WHERE APPLIED='YES' GROUP BY THREAD#) APPL 
WHERE ARCH.THREAD# = APPL.THREAD#;

Inspect the Alert Log

If Managed Recovery Process (MRP) has stopped, check for exact failure reasons in the alert log:

sql

SELECT MESSAGE FROM V$DATAGUARD_STATUS WHERE SEVERITY IN ('Error', 'Fatal') ORDER BY TIMESTAMP DESC;

3. Verification & Test Cases

Test Case 1: Archive Log Gap Management

Action: Manually switch 3 logs on the primary.
sql
ALTER SYSTEM SWITCH LOGFILE;
Expected Result: The SEQUENCE# should increment on the primary and seamlessly apply to the standby without errors. Verify via the gap query in the troubleshooting section above (the "Gap" value should be 0).

Test Case 2: Data Guard Broker Status

Action: Check if the Data Guard configuration is healthy via the DGMGRL command line utility.
text
DGMGRL> SHOW CONFIGURATION; DGMGRL> SHOW DATABASE <standby_db_unique_name>;
Expected Result: The overall health state should be SUCCESS, and the standby database should report status as ORA-16647: Media recovery is starting or ORA-16648: Media recovery is running

Question :SQL statement is runnng slow suddenly and no resource crun at OS level and not a issue at database and no plan change and other jobs are running fine as expected how to fix it with example and test case how it is differ in 19c and 23ai

Here is how to isolate and troubleshoot the root cause systematically.

1. Identify the Session and Current Wait Events

Find out exactly what the slow-running statement is waiting for right now.

Query V$SESSION and V$SESSION_WAIT:

sql

SELECT sid, serial#, sql_id, event, p1, p2, p3, seconds_in_wait, state
FROM v$session
WHERE status = 'ACTIVE' AND username IS NOT NULL;

Analyze the EVENT column:
- db file sequential read / db file scattered read: The query is reading physical blocks from disk. Data volume might have grown, or blocks were flushed from the buffer cache.
- enq: TX - row lock contention: The query is blocked by another session holding a row lock.
- cursor: pin S wait on X / library cache pin: Concurrency issue compiling or executing the cursor.

2. Check for Plan Instability (Hidden Plan Changes)

Even if you believe the plan did not change, Oracle might be using a different child cursor due to bind mismatch or adaptive features.

Check V$SQL for multiple child cursors:

sql

SELECT sql_id, child_number, plan_hash_value, executions, rows_processed
FROM v$sql 
WHERE sql_id = 'YOUR_SQL_ID';

Use DBMS_XPLAN to compare history:

sql

SELECT * FROM TABLE(dbms_xplan.display_cursor('YOUR_SQL_ID', NULL, 'ALLSTATS LAST'));

Verify standard AWR history:

sql
SELECT * FROM TABLE(dbms_xplan.display_awr('YOUR_SQL_ID'));
Look closely to see if the PLAN_HASH_VALUE matches across historical executions.

3. Inspect Bind Variable Peaking and Cardinality Feedback

If the plan hash is identical, the execution path might be the same, but the internal data processing could have changed due to specific data inputs.

Bind Peaking: The query may have parsed with an atypical bind variable value (e.g., a value that returns 1,000,000 rows instead of the usual 10 rows), causing memory or temp space thrashing.

Check Bind Values:

sql

SELECT name, position, datatype_string, value_string
FROM v$sql_bind_capture
WHERE sql_id = 'YOUR_SQL_ID';

4. Check for Transient Object Changes

A change in the underlying segments can degrade performance even if the execution plan remains static.

Data Volume / High Water Mark: If a table had a massive insert followed by a delete, a full table scan will still read all the empty blocks up to the High Water Mark.
Stale Statistics: Check if statistics were automatically gathered mid-day, subtly altering index selectivities without shifting the plan hash.
Row Chaining/Migration: Run an analyze command or check V$SEGMENT_STATISTICS to see if a recent update caused heavy row migration, doubling the I/O needed per row.

5. Generate a Real-Time SQL Monitor Report

If the query runs for more than 5 seconds (or uses parallel execution), Oracle automatically monitors it. This is the most granular tool available.

Extract the Active HTML Report:
sql
SELECT dbms_sqltune.report_sql_monitor( sql_id => 'YOUR_SQL_ID', type => 'HTML', report_level => 'ALL') FROM dual;
What to look for: Open this file in a browser. It will show you line-by-line in the execution plan exactly which operation is consuming the time, how many rows it expected vs. how many it actually processed, and where the database time is being spent (CPU vs. Wait Control).

6. Run SQL Tuning Advisor

Let Oracle's internal optimizer engine analyze the historical performance and give structural recommendations.

sql

DECLARE
  l_sql_tune_task_id VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => 'YOUR_SQL_ID',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'tune_slow_sql_task');
  DBMS_SQLTUNE.execute_tuning_task(task_name => 'tune_slow_sql_task');
END;
/

-- View the results
SELECT DBMS_SQLTUNE.report_tuning_task('tune_slow_sql_task') FROM dual;

When a single SQL query suddenly slows down while the OS, database, and execution plan remain healthy, the issue is usually transient environmental friction or data-centric anomalies.

Here is how to isolate and troubleshoot the root cause.

1. Check for Concurrency and Locking

Other sessions might be blocking your query without causing an OS-level resource crunch.

Check locks: Look for row-level locks or table-level metadata locks holding up your session.
Identify blockers: Run session-monitoring queries to find the blocking Session ID (SID).
Review isolation levels: High isolation levels (like Serializable) can cause unexpected transaction delays.

2. Inspect Session-Level Wait Events

The database tracks exactly what a specific session is waiting for in real time.

Query active session history: Check views like v$session_wait (Oracle), sys.dm_os_waiting_tasks (SQL Server), or pg_stat_activity (PostgreSQL).
Look for specific waits: Identify if the session is stuck on network latencies (SQL*Net message to client), disk I/O, or latch contention. [

3. Analyze Data Volume and Parameter Sniffing

Even if the execution plan looks identical, the data processing workload might have changed.

Check bind variables: A specific input parameter value might be forcing the plan to process millions of rows instead of a few.
Inspect data skew: Check if a recently modified batch of data created a skewed distribution in indexed columns.
Verify row counts: Compare the actual number of rows processed in the slow run versus historical fast runs.

4. Investigate TempDB and Memory Grant Issues

The query might be struggling to get the workspace memory it needs to execute efficiently.

Check memory grants: The query may be waiting in a queue for a memory grant to execute hashes or sorts.
Look for physical spilling: If memory allocation is restricted, the query will silently spill sorting operations to TempDB or TEMP tablespaces, slowing it down drastically.

5. Rule out Physical and Network Infrastructure

Small disruptions outside the core database engine can target specific queries.

Check network packet size: Large result sets can stall if there are network drops or configuration changes between the app server and DB.
Inspect storage latency: Check if the specific underlying disk volume hosting the query's tables is experiencing localized micro-latency.

If no OS/DB resource crunch exists, there are no plan changes, and other jobs run fine, the root cause is usually bind variable peeking (parameter sniffing), cursor invalidation/re-parse, or underlying data volume/distribution skew. The database may be taking the same path, but execution efficiency drops.

Quick Fix & Root Cause Breakdown

When the execution plan remains identical but performance degrades, the issue is typically linked to bind variables or data skew. Here are the most effective ways to diagnose and resolve this issue.

1. Identify the Culprit

Check the wait events in the database to see exactly what the query is waiting on while it runs slowly:

sql

SELECT event, total_waits, time_waited 
FROM v$session_event 
WHERE sid = <SID_OF_SLOW_QUERY>;

2. Force a Hard Re-parse

If the cursor execution is bogged down by unoptimized bind variables, flush the cursor from the library cache to force a fresh re-parse.

Find the SQL_ID and Child Number:

sql

SELECT sql_id, child_number, executions, buffer_gets 
FROM v$sql 
WHERE sql_text LIKE '%YOUR_SLOW_SQL_TEXT%';

Flush a Specific Cursor (Oracle 19c/23ai):

sql

EXECUTE DBMS_SHARED_POOL.PURGE ('<ADDRESS, HASH_VALUE>', 'C');

3. Optimizer Fixes (Bind Variable Peeking)

If the query execution differs because it was optimized for a "heavy" day but executed on a "light" day, you can disable bind variable peeking for the specific query, or bind it to an optimal value.

Test Case and Scenario

Example Scenario

Consider a query searching an ORDERS table based on a STATUS. The table has 100,000 rows.

Status 'P' (Pending): Only 10 rows exist. An Index Scan is optimal.
Status 'C' (Completed): 99,990 rows exist. A Full Table Scan is optimal.

The Test Case Setup:

sql

CREATE TABLE orders (
    order_id NUMBER,
    status VARCHAR2(1),
    order_date DATE,
    notes VARCHAR2(100)
);

CREATE INDEX idx_orders_status ON orders(status);

The Problem:

If the database parses the query for status = 'C' first, it caches an execution plan doing a Full Table Scan. When a background job later executes the query with status = 'P' (which should use the index), it is forced to use the cached Full Table Scan. This causes a massive spike in logical reads and processing time.

Difference Between 19c and 23ai

Oracle 19c Behavior

Adaptive Cursor Sharing (ACS): The database attempts to detect bind-sensitivity and generate multiple execution plans. However, it often requires several slow executions before ACS triggers and generates a new plan, which can result in delayed processing.
SQL Plan Management (SPM): You must manually capture a known good plan using DBMS_SPM and load it as a baseline to force the optimizer to use it.

Oracle 23ai Behavior

SQL Plan Management Automatically Enabled: The Oracle Optimizer automatically captures execution plans. In 23ai, the database can autonomously verify alternative plans and automatically evolve a new plan if performance degrades, eliminating much of the manual DBA overhead.
Direct Micro-optimization: In 23ai, if the issue stems from a poor Cardinality Estimate, the optimizer has advanced statistical algorithms that auto-adjust without relying entirely on stale dictionary stats.

When a SQL statement suddenly slows down without OS constraints, DB/hardware issues, or a plan change, the root cause is almost always Bind Variable Peeking (Data Skew) or Adaptive Cursor Sharing/Cursor Degradation.

Here is how to diagnose, fix, and contrast the behavior in Oracle 19c and 23ai.

Root Cause & Test Case

The Problem: Bind Variable Peeking / Bind Degradation
When a SQL statement uses bind variables, the database "peeks" at the bind value during the first execution (hard parse) and builds an execution plan based on that single value. If the data is skewed (e.g., 99% of rows are STATUS='ACTIVE', but 1% are STATUS='PENDING'), and the first execution happens to run on PENDING, the database builds an optimal index-based plan.
If the bind value changes later to ACTIVE, that index lookup becomes highly inefficient (causing massive multiblock reads and execution lag), even though the execution plan itself didn't change.

Test Case:

sql

-- Create Table
CREATE TABLE orders (
    order_id NUMBER,
    status VARCHAR2(20),
    notes VARCHAR2(100)
);

-- Create highly selective index
CREATE INDEX idx_orders_status ON orders(status);

-- Gather statistics (simulate data skew)
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'orders');

How to Fix

You need to force a hard parse to rebuild the plan using the correct bind, or capture the "good" plan.

Step 1: Identify the SQL_ID & Child Cursors

sql

SELECT sql_id, child_number, executions, buffer_gets, cpu_time 
FROM V$SQL 
WHERE sql_id = 'YOUR_SQL_ID';

Step 2: Flush the Cursor to force a hard parse

sql

-- Flush a specific statement from the library cache to generate a fresh plan
EXEC DBMS_SHARED_POOL.PURGE('address, hash_value', 'C');

Step 3: Analyze Actual vs. Estimated Rows

Run this to see if the optimizer is estimating a low number of rows but actually returning a large set:

sql

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('YOUR_SQL_ID', NULL, 'ALLSTATS LAST'));

Difference Between 19c and 23ai

Oracle Database 19c Behavior

Bind Peeking Limitations: Uses Adaptive Cursor Sharing (ACS) to detect if a bind variable value performs poorly. ACS monitors runtime statistics and attempts to spawn new "child cursors" (new execution plans) if it detects a bind-sensitive issue.
The Glitch: ACS can sometimes thrash (swapping plans constantly) or fail to generate a new plan entirely, resulting in prolonged slowness until the DBA manually intervenes via flushing or locking a plan.

Oracle Database 23ai Behavior

Real-Time SQL Plan Management (SPM): 23ai introduces Real-Time SPM, which vastly improves on 19c's capabilities. The optimizer automatically captures SQL statement performance and execution plan data into the Automatic SQL Tuning Set (ASTS).
Automated Fixes: If a plan regresses (runs much slower than its established historical baseline), the database detects the regression during a foreground hard parse and automatically evolves to a better execution plan without DBA intervention.
No Child-Cursor Thrashing: Real-Time SPM avoids 19c's ACS "plan bouncing" by formally evaluating alternate historical plans and validating them before making them an active baseline

Question : SQL plan directive , selectivity and Cardinality, Dynamic Sampling while optimiser is selecting best execution plan how it is differ in 19c and 23ai with example and test cases

To choose the most efficient execution plan, the Oracle Cost-Based Optimizer (CBO) relies heavily on Selectivity and Cardinality, adapting its choices dynamically using Dynamic Sampling and SQL Plan Directives (SPDs).

While both Oracle 19c and Oracle 23ai share the same foundational architecture for these metrics, they handle edge cases differently—especially around complex PL/SQL functions, vector processing, and the stability overhead of background directives.

Foundational Concepts

Selectivity: A floating-point number between 0 and 1 representing the fraction of rows filtered by a predicate.

Cardinality: The estimated number of rows returned by an operation. It dictates join types (e.g., Hash vs. Nested Loops) and access paths (e.g., Index Scan vs. Full Table Scan).

Dynamic Sampling (Dynamic Statistics): A mechanism where the optimizer executes recursive queries on a small sample of data blocks during a hard parse to calculate missing or unreliable statistics.
SQL Plan Directive (SPD): An internal object created in the background when the optimizer detects a significant mismatch between estimated and actual cardinality during execution. The next time a similar query parses, the SPD tells the optimizer to trigger Dynamic Sampling to prevent a bad plan.

Core Differences: Oracle 19c vs. Oracle 23ai

Feature / Behavior	Oracle 19c	Oracle 23ai
SQL Plan Directives Handling	Active but closely constrained; default parameters favor automatic statistics extension creation over excessive runtime sampling.	Highly optimized metadata framework; faster lookup with minimal execution overhead during hard parses.
Dynamic Sampling for PL/SQL Functions	Strictly blind guess or relying on fixed `CARDINALITY` / `ASSOCIATE STATISTICS` hints; unable to accurately estimate complex inline functions.	Native Support Enabled; executes recursive sampling queries that evaluate PL/SQL or table functions at parse time to accurately resolve true cardinality.
Vector Data Types	Not supported.	Introduces Vector Distance Selectivity calculations for AI Vector Searches, dynamically adjusting distance-metric cardinalities.

Test Case 1: Correlated Predicates and SQL Plan Directives

This test case simulates a data skew scenario where columns are correlated (e.g., choosing a specific make correlates directly with a specific model).

1. Setup Data Environment

sql

CREATE TABLE vehicles (
    id NUMBER,
    make VARCHAR2(30),
    model VARCHAR2(30)
);

-- Insert highly correlated data
BEGIN
    FOR i IN 1..10000 LOOP
        INSERT INTO vehicles VALUES (i, 'Toyota', 'Camry');
    END LOOP;
    FOR i IN 10001..11000 LOOP
        INSERT INTO vehicles VALUES (i, 'Ford', 'Mustang');
    END LOOP;
    COMMIT;
END;
/

-- Gather baseline statistics without a column group extension
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'VEHICLES', method_opt => 'FOR ALL COLUMNS SIZE 1');

2. Execute Query causing Misestimate (Runs Identically on 19c & 23ai)

sql

SELECT * FROM vehicles WHERE make = 'Toyota' AND model = 'Mustang';

Initial Optimizer Behavior: Because the optimizer assumes columns are completely independent, it multiplies selectivities:

Background Actions: The database detects that actual rows returned (0) differ radically from estimated rows (~9000). It logs an SPD in DBA_SQL_PLAN_DIRECTIVES with a type of DYNAMIC_SAMPLING.

3. Subsequent Execution Check

If you flush the shared pool and run a similar query again:

sql

ALTER SYSTEM FLUSH SHARED_POOL;
EXPLAIN PLAN FOR SELECT * FROM vehicles WHERE make = 'Toyota' AND model = 'Camry';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format=>'NOTE'));

In 19c Note Section:
text
Note ----- - dynamic statistics used: dynamic sampling used for this statement (SPD skipped/applied)
In 23ai Note Section: The lookup resolves noticeably quicker because the underlying metadata dictionaries handle automated data insights faster, bypassing some historic internal recursive dictionary locking found in 19c.

Test Case 2: PL/SQL Functions & Dynamic Sampling (The Game Changer)

This case illustrates the profound behavioral split between 19c and 23ai when using custom database logic in a WHERE clause.

1. Setup Function and Data

sql

CREATE TABLE target_data (id NUMBER, val VARCHAR2(10));

BEGIN
    FOR i IN 1..10000 LOOP
        INSERT INTO target_data VALUES (i, CASE WHEN MOD(i,100)=0 THEN 'SPECIAL' ELSE 'NORMAL' END);
    END LOOP;
    COMMIT;
END;
/
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TARGET_DATA');

CREATE OR REPLACE FUNCTION get_skewed_value RETURN VARCHAR2 IS
BEGIN
    RETURN 'SPECIAL';
END;
/

2. Run Query Evaluated Against Custom Function

sql

EXPLAIN PLAN FOR 
SELECT * FROM target_data WHERE val = get_skewed_value();

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

3. Execution Plan Mismatch Comparison

Oracle 19c Plan Output:
The optimizer cannot guess what a standalone PL/SQL function returns during hard parse. It applies a rigid fallback selectivity guess (typically 1% or a hardcoded default row estimate).

text

---------------------------------------------------------------------------------

| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |             |   100 |  1200 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TARGET_DATA |   100 |  1200 |    15   (0)| 00:00:01 |
---------------------------------------------------------------------------------

(Note: 100 rows is guessed because 1% of 10,000 rows = 100, ignoring the factual data profile).

Oracle 23ai Plan Output:
Under default parameters in 23ai, Dynamic Statistics for PL/SQL Functions evaluates the lookup. The optimizer invokes a safe recursive sampling phase, realizing get_skewed_value() outputs 'SPECIAL', which accounts for exactly 100 entries.

text

---------------------------------------------------------------------------------

| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |             |   100 |  1200 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TARGET_DATA |   100 |  1200 |    15   (0)| 00:00:01 |
---------------------------------------------------------------------------------
Note
-----
   - dynamic statistics used: dynamic sampling used for PL/SQL function

If the function returned 'NORMAL', 23ai would dynamically sample and change the estimation to 9900 rows instead of staying permanently fixed to a generic 1% guess as seen in 19

or

In the Oracle Cost-Based Optimizer (CBO), features like Selectivity, Cardinality, Dynamic Sampling (Dynamic Statistics), SQL Plan Directives (SPD), SQL Profiles, and SQL Plan Management (SPM) build an engine designed to estimate execution costs and ensure plan stability.

While the fundamental mechanics of how these components influence a parse remain similar, Oracle Database 23ai shifts from the batch/reactive model of 19c to an autonomous, real-time, and AI-ready architecture.

The architectural differences between 19c and 23ai break down across each optimization component as follows:

1. Selectivity & Cardinality

Concept: Selectivity is the estimated fraction of rows filtered by a predicate; Cardinality is the actual estimated row count (Rows = Total Rows × Selectivity). It is the single most critical driver of plan generation. [
In 19c: The optimizer calculates cardinality using static object statistics, data dictionaries, and basic column histograms. Complex predicates or skewed data often result in miscalculations, forcing the optimizer into an adaptive parse stage.
In 23ai: While the mathematical formulas are consistent, 23ai vastly improves accuracy for modern data structures. It includes enhanced native estimations for complex JSON documents, graph queries, and high-dimensional vector spaces via AI Vector Search. This keeps the baseline cardinality precise even when querying unstructured data blended with traditional relational tables.

2. Dynamic Sampling (Dynamic Statistics)

Concept: Gathers lightweight statistics on the fly during a hard parse if standard object statistics are missing, stale, or too complex.
In 19c: Governed by OPTIMIZER_DYNAMIC_SAMPLING (levels 0-11). If set to level 11, the optimizer automatically decides when to sample. However, the overhead occurs purely during the foreground parse phase, which can occasionally cause parsing spikes or latency for complex queries. [
In 23ai: The engine leverages a more intelligent Continuous / Automated Sampling infrastructure. 23ai relies heavily on the background-driven Automatic SQL Tuning Set (ASTS) to feed the CBO. It suppresses unnecessary on-the-fly foreground sampling if historical or real-time execution statistics are already available via ASTS.

3. SQL Plan Directives (SPD)

Concept: Notes created automatically by the database when it detects a massive mismatch between its cardinality estimate and actual execution row counts. It instructs future parses to use dynamic sampling or create extended statistics.
In 19c: SPDs are strictly integrated into the Adaptive Query Optimization framework. They instruct the CBO to perform dynamic sampling at parse time to fix missing column group correlations. However, they are occasionally criticized for causing plan instability or heavy parsing overhead.
In 23ai: SPDs are further refined to prevent "sampling loops." The database engine is smarter about converting an active directive into structural Extended Statistics automatically in the background, minimizing the time a SQL statement spends relying on transient parse-time directives.

4. SQL Profiles vs. SQL Plan Baselines (SPM)

Concept: SQL Profiles correct optimizer mathematical miscalculations (by providing opt_estimate cardinality adjustments). SQL Plan Baselines (SPM) strictly constrain the optimizer to a verified, trusted set of execution plans.

Feature Component	Oracle Database 19c Framework	Oracle Database 23ai Framework
SQL Profiles	Reactive. Generated manually or via the daily automated SQL Tuning Advisor maintenance window.	Deprecated/Legacy approach. Oracle favors SPM automation. Profiles are supported for backward compatibility but rarely recommended for new tuning.
SPM Capture	Captured automatically via parameter `OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES` or loaded manually.	Enhanced via Real-Time SQL Plan Management. Statements are automatically evaluated inline using background execution telemetry.
SPM Evolution	Batch / Delayed. Unaccepted plans sit in a history queue and are tested overnight by an automated SYS job (`SYS_AUTO_SPM_EVOLVE_TASK`).	Real-Time / Immediate. The foreground process quickly tracks execution deviations, validates safety, and promotes better plans to the baseline automatically.

How the Optimization Workflow Differs

The 19c Workflow (Reactive & Batch)

Hard Parse: The CBO calculates Selectivity and Cardinality using dictionary stats.
Check Directives/Sampling: If an SPD exists or stats are missing, Dynamic Sampling triggers.
Apply Profiles: If a SQL Profile exists, its weight adjusts the internal cardinality cost adjustments.
Enforce Baseline: The CBO matches the final costed plan against the SQL Plan Baseline. If it's a new, unaccepted plan, it is forced to use an older accepted plan and puts the new one into a History Queue to be verified by a batch job hours later.

The 23ai Workflow (Real-Time & Autonomous)

Hard Parse: The CBO calculates metrics, directly incorporating modern formats like vector embeddings or JSON models seamlessly.
Telemetry Validation: Instead of blindly running foreground sampling, the CBO checks the Automatic SQL Tuning Set (ASTS) for immediate historical execution realities.
Real-Time SPM Check: If a plan degrades or shifts, Real-Time SPM detects the performance regression instantly.
Instant Self-Healing: Instead of waiting for an overnight batch job, the database leverages foreground verification and background automation via ADD_VERIFIED_SQL_PLAN_BASELINE to instantly roll back a regressed plan or accept a faster on

EasyReliableDBA

Thursday, 11 June 2026

Eaxadata and Oracle Database interview question and answer 2026 Part5

Question :excution plan with full tables scan and its interpretion and wrong join etc and troubleshoot with example and test cases in oracle 19c and 23ai

Question : Primary production database and standby database and production has crashed and restored with reset log mode so what would be action plan for standby database

or

Question :SQL statement is runnng slow suddenly and no resource crun at OS level and not a issue at database and no plan change and other jobs are running fine as expected how to fix it with example and test case how it is differ in 19c and 23ai

Question : SQL plan directive , selectivity and Cardinality, Dynamic Sampling while optimiser is selecting best execution plan how it is differ in 19c and 23ai with example and test cases

or

No comments:

Post a Comment

Search This Blog