Saturday, March 21, 2026

Oracle SQL Tuning – Interview Questions & Answers

🎯 Oracle SQL Tuning – Interview Questions & Answers

1. What is SQL Tuning in Oracle?

Answer:
SQL Tuning is the process of improving the performance of SQL queries by optimizing execution plans, reducing resource consumption (CPU, I/O), and improving response time.

2. How do you identify a slow SQL query?

Answer:
We can identify slow SQL using:

V$SESSION (currently running SQL)
V$SQL (high resource SQL)
AWR reports
ASH reports

Example:

SELECT sql_id, elapsed_time, executions
FROM v$sql
ORDER BY elapsed_time DESC;

3. What is SQL_ID and why is it important?

Answer:
SQL_ID is a unique identifier for a SQL statement in Oracle.
It helps in:

Tracking SQL performance
Fetching execution plans
Monitoring historical execution

4. How do you check the execution plan of a query?

Answer:
Using:

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<SQL_ID>', NULL, 'ALLSTATS LAST'));

This shows:

Execution steps
Actual vs estimated rows
Cost and time

5. What is the difference between EXPLAIN PLAN and DBMS_XPLAN.DISPLAY_CURSOR?

Answer:

Feature	EXPLAIN PLAN	DISPLAY_CURSOR
Type	Estimated	Actual
Source	Optimizer	Cursor cache
Accuracy	Less	High

6. How do you check historical performance of a SQL?

Answer:
Using AWR views:

DBA_HIST_SQLSTAT
DBA_HIST_SQLTEXT

This helps analyze:

Plan changes
Execution trends
Performance degradation

7. What is PLAN_HASH_VALUE?

Answer:
PLAN_HASH_VALUE uniquely represents an execution plan.
If it changes, it means the execution plan has changed.

8. What is SQL Profile?

Answer:
A SQL Profile is a set of optimizer hints that helps Oracle generate a better execution plan without modifying the SQL.

9. How do you create a SQL Profile using COE script?

Answer:

Identify best plan:

START coe_xfr_sql_profile.sql <SQL_ID>

Generate profile:

START coe_xfr_sql_profile.sql <SQL_ID> <PLAN_HASH_VALUE>

Apply:

@coe_xfr_sql_profile_<SQL_ID>_<PLAN_HASH>.sql

10. How do you verify SQL Profile is being used?

Answer:

SELECT sql_id, plan_hash_value
FROM v$sql
WHERE sql_id = '<SQL_ID>';

Also:

SELECT * FROM dba_sql_profiles;

11. How do you drop a SQL Profile?

Answer:

EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('profile_name');

12. What is SQL Tuning Advisor?

Answer:
SQL Tuning Advisor is an Oracle tool that analyzes SQL and provides recommendations such as:

Index creation
SQL Profile
Statistics gathering
Alternative execution plans

13. How do you run SQL Tuning Advisor?

Answer:

BEGIN
    DBMS_SQLTUNE.create_tuning_task(
        task_name   => 'TASK1',
        sql_id      => '<SQL_ID>',
        time_limit  => 60
    );

    DBMS_SQLTUNE.execute_tuning_task('TASK1');
END;
/

14. What kind of recommendations does SQL Tuning Advisor give?

Answer:

Create index
Gather statistics
Accept SQL Profile
Rewrite query
Suggest better execution plan

15. What is the role of statistics in SQL tuning?

Answer:
Optimizer relies on statistics to estimate:

Cardinality
Cost
Execution plan

Stale statistics can lead to bad plans.

16. What is cardinality misestimation?

Answer:
When optimizer incorrectly estimates number of rows, leading to:

Wrong join method
Poor execution plan

17. When should you use SQL Profile vs Index?

Answer:

Scenario	Use
Temporary fix	SQL Profile
Long-term optimization	Index

18. What are common causes of slow SQL?

Answer:

Missing indexes
Full table scans
Stale statistics
Bad execution plans
High I/O operations

19. What is ALLSTATS LAST in DBMS_XPLAN?

Answer:
It shows actual execution statistics of the last run, including:

Actual rows processed
Actual time taken

20. Real Interview Scenario Question

Question:
A query suddenly became slow. What will you do?

Answer:

Capture SQL_ID
Check current execution plan
Compare with historical plans
Identify plan change
Use COE or SQL Profile
Run SQL Tuning Advisor
Validate performance

✅ Pro Tip (For Interviews)

Always say:

“First I check whether the issue is due to plan change or data growth, then I decide whether to fix using SQL Profile, Index, or statistics.”

🚀 Bonus: Quick One-Liners

SQL_ID → Unique identifier
PLAN_HASH_VALUE → Execution plan identifier
SQL Profile → Optimizer hint set
AWR → Historical performance data
ASH → Real-time session activity

Interview Question on Active Data Guard Configuration

Interview Questions

What is Active Data Guard?

Difference between Data Guard and Active Data Guard?

What is MRP process?

What is FAL_SERVER and FAL_CLIENT?

What is the use of Standby Redo Logs?

What is Switchover vs Failover?

Why FORCE LOGGING is required?

How do you check Data Guard lag?

What is DB_UNIQUE_NAME?

What is NOFILENAMECHECK in RMAN duplicate?

Friday, March 20, 2026

Configuring Active Data Guard in Oracle 26AI Multitenant Database

1. Introduction

Active Data Guard enables a standby database to be open in read-only mode while continuously applying redo logs from the primary database. This provides:

High availability
Disaster recovery
Real-time reporting

This document provides a complete step-by-step guide to configure Active Data Guard in an Oracle 26AI Multitenant environment.

2. Prerequisites

Source and Target servers with Oracle Linux 8/9
Primary server with Oracle 26AI Database installed
Standby server with Oracle 26AI Software installed
Network connectivity between servers (tnsping must work)

3. Environment Details

Database Name: AIDB
PDB Name: AIPDB
Primary Host: 192.168.226.129
Standby Host: 192.168.226.130

4. Network Configuration

4.1 Primary Database – TNS Configuration

File location:

$ORACLE_HOME/network/admin/tnsnames.ora

LISTENER_AIDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.129)(PORT = 1521))

PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.129)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRIMARY)
    )
  )

AIPDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.129)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = AIPDB)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.130)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = STANDBY)
    )
  )

4.2 Primary Listener Configuration

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.129)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = PRIMARY)
      (ORACLE_HOME = /u01/app/oracle/product/home)
      (SID_NAME = AIDB)
    )
  )

4.3 Standby Database – TNS Configuration

LISTENER_AIDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.130)(PORT = 1521))

STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.130)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = STANDBY)
    )
  )

PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.129)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = PRIMARY)
    )
  )

4.4 Standby Listener Configuration

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.130)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = STANDBY)
      (ORACLE_HOME = /u01/app/oracle/product/home)
      (SID_NAME = AIDB)
    )
  )

5. Primary Database Configuration

5.1 Enable Archivelog Mode

STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

5.2 Enable Force Logging

ALTER DATABASE FORCE LOGGING;

5.3 Set Data Guard Parameters

ALTER SYSTEM SET db_unique_name='PRIMARY' SCOPE=SPFILE;

ALTER SYSTEM SET log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)' SCOPE=BOTH;

ALTER SYSTEM SET log_archive_dest_1=
'LOCATION=/u01/app/oracle/product/home/dbs/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PRIMARY' SCOPE=BOTH;

ALTER SYSTEM SET log_archive_dest_2=
'SERVICE=STANDBY
 ASYNC
 NOAFFIRM
 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
 DB_UNIQUE_NAME=STANDBY' SCOPE=BOTH;

ALTER SYSTEM SET FAL_CLIENT=PRIMARY SCOPE=BOTH;
ALTER SYSTEM SET FAL_SERVER=STANDBY SCOPE=BOTH;

ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;

ALTER SYSTEM SET db_file_name_convert=
'/u01/.../AIDB','/u01/.../AIDB' SCOPE=BOTH;

ALTER SYSTEM SET log_file_name_convert=
'/u01/.../AIDB','/u01/.../AIDB' SCOPE=BOTH;

5.4 Create Standby Redo Logs

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/path/srl04.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/path/srl05.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/path/srl06.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/path/srl07.log') SIZE 200M;

5.5 Copy Required Files

scp initAIDB.ora orapwAIDB oracle@192.168.226.130:$ORACLE_HOME/dbs

6. Standby Database Configuration

6.1 Start Standby in NOMOUNT

STARTUP NOMOUNT PFILE='initAIDB.ora';

6.2 Duplicate Database Using RMAN

rman target sys/password@PRIMARY auxiliary sys/password@STANDBY

DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
NOFILENAMECHECK;

6.3 Verify Database State

SELECT name, open_mode FROM v$database;

7. Enable Active Data Guard

7.1 Open Standby in Read-Only Mode

ALTER DATABASE OPEN READ ONLY;

7.2 Start Managed Recovery Process (MRP)

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

8. Validation Checks

8.1 Check MRP Status

SELECT process, status FROM v$managed_standby;

8.2 Check Lag

SELECT name, value
FROM v$dataguard_stats
WHERE name IN ('transport lag','apply lag');

8.3 Archive Gap Check

SELECT * FROM v$archive_gap;

8.4 Synchronization Check

SELECT
  arch.sequence# AS received,
  appl.sequence# AS applied,
  (arch.sequence# - appl.sequence#) AS gap
FROM
  (SELECT MAX(sequence#) sequence# FROM v$archived_log) arch,
  (SELECT MAX(sequence#) sequence# FROM v$log_history) appl;

9. Switchover Process

9.1 On Primary

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;

9.2 On Standby

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE OPEN;

9.3 Restart MRP

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

10. Useful Monitoring Queries

MRP Status

SELECT process, status FROM v$managed_standby;

Lag Check

SELECT name, value FROM v$dataguard_stats;

Archive Gap

SELECT * FROM v$archive_gap;

11. Conclusion

Active Data Guard setup involves:

Network configuration
Primary database preparation
Standby database creation using RMAN
Enabling redo apply (MRP)
Validating synchronization and lag

A properly configured Active Data Guard environment ensures high availability, data protection, and real-time reporting capabilities.

SQL Tuning in Oracle Database – Step-by-Step Guide

1. Introduction

SQL tuning is a critical activity in Oracle databases to improve query performance and optimize resource usage. This document outlines a structured approach to identify, analyze, and optimize a slow-performing SQL query.

2. Prerequisites

Before performing SQL tuning, ensure the following:

Oracle Enterprise Database is available
Oracle Diagnostics Pack and Tuning Pack licenses are enabled
SQLTXPLAIN (SQLT) utility is installed
- COE scripts are located at:
  …/sqlt/install/coe

3. Scenario

A SELECT query is executed on a table with approximately 5 million rows, resulting in:

High memory consumption
Slow response time

4. Step-by-Step SQL Tuning Process

Step 1: Capture SQL ID

Identify the SQL ID of the running query:

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.sql_id,
    q.sql_fulltext,
    s.sql_child_number,
    s.status,
    s.event,
    s.wait_class,
    s.seconds_in_wait,
    s.machine,
    s.program
FROM
    v$session s
    JOIN v$sql q ON s.sql_id = q.sql_id
WHERE
    s.status = 'ACTIVE'
    AND s.sql_id IS NOT NULL
    AND s.username IS NOT NULL      
ORDER BY
    s.last_call_et DESC;

Step 2: Check Execution Plan

Analyze the current execution plan:

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<SQL_ID>', NULL, 'ALLSTATS LAST'));

Details:

Displays actual execution statistics
Helps identify bottlenecks like full table scans or high I/O

Step 3: Analyze SQL Execution History

Retrieve historical performance data:

SELECT
       ss.snap_id,
       ss.instance_number AS node,
       ss.begin_interval_time,
       s.sql_id,
       t.sql_text,
       s.plan_hash_value,
       NVL(s.executions_delta,0) AS execs,
       (s.elapsed_time_delta /
            DECODE(NVL(s.executions_delta,0),0,1,s.executions_delta)) / 1000000
           AS avg_etime,
       (s.buffer_gets_delta /
            DECODE(NVL(s.executions_delta,0),0,1,s.executions_delta))
           AS avg_lio
FROM   dba_hist_sqlstat s
       JOIN dba_hist_snapshot ss
           ON ss.snap_id = s.snap_id
          AND ss.instance_number = s.instance_number
       JOIN dba_hist_sqltext t
           ON t.sql_id = s.sql_id
WHERE  s.sql_id = '<SQL_ID>'
       AND s.executions_delta > 0
ORDER BY
       ss.begin_interval_time,
       ss.instance_number;

Purpose:

Detect plan changes over time
Compare performance across executions

Step 4: Identify and Apply Best Execution Plan (COE Script)

4.1 Identify Best Plan

START coe_xfr_sql_profile.sql <SQL_ID>

Sample output:

PLAN_HASH_VALUE      AVG_ET_SECS
------------------   ------------
4009583451            12.438
9123498812             0.210   <-- Best Plan
3009112234             5.901

4.2 Generate SQL Profile

START coe_xfr_sql_profile.sql <SQL_ID> <PLAN_HASH_VALUE>

This creates a script file containing:

SQL text
Execution hints
SQL profile creation command

4.3 Apply SQL Profile

@coe_xfr_sql_profile_<SQL_ID>_<PLAN_HASH_VALUE>.sql

4.4 Verify Plan Usage

SELECT sql_id, plan_hash_value
FROM   v$sql
WHERE  sql_id = '<SQL_ID>';

Drop SQL Profile (if required)

EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_<SQL_ID>_<PLAN_HASH_VALUE>');

Step 5: Use SQL Tuning Advisor

If no optimal plan is found:

BEGIN
    DBMS_SQLTUNE.create_tuning_task(
        task_name   => '<TASK_NAME>',
        sql_id      => '<SQL_ID>',
        time_limit  => <TIME_LIMIT>
    );

    DBMS_SQLTUNE.execute_tuning_task('<TASK_NAME>');
END;
/

View Tuning Report

SELECT DBMS_SQLTUNE.report_tuning_task('<TASK_NAME>')
FROM dual;

5. Common Recommendations

1. Create Index

CREATE INDEX EMP_DEPT_IDX 
ON EMPLOYEES(DEPARTMENT_ID, SALARY);

2. Accept SQL Profile

EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
     task_name => '<TASK_NAME>',
     name      => '<PROFILE_NAME>');

3. Gather Table Statistics

EXEC DBMS_STATS.GATHER_TABLE_STATS(
     ownname => 'HR',
     tabname => 'EMPLOYEES',
     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

4. Verify SQL Profile

SELECT * 
FROM   dba_sql_profiles
WHERE  name LIKE '%<SQL_ID>%';

6. Best Practices

Always validate performance after applying changes
Monitor execution plans regularly
Avoid overusing SQL Profiles
Keep optimizer statistics up to date
Prefer indexing and query rewriting for long-term solutions

7. Conclusion

SQL tuning in Oracle involves:

Identifying the problematic SQL
Analyzing execution plans
Reviewing historical performance
Applying optimal execution plans
Using SQL Tuning Advisor recommendations

A systematic approach ensures improved performance and efficient resource utilization.

Tuesday, March 17, 2026

ORA-01555: snapshot too old

ORA-01555: snapshot too old in Oracle Database is a very common and critical performance issue faced by DBAs in production.

🔴 What ORA-01555 Means

👉 Oracle cannot reconstruct the older version of data needed by a query.

In simple terms:

Your query is running too long, and the required undo data has already been overwritten.

📌 Why This Happens

Oracle uses UNDO tablespace to maintain read consistency.

When a long-running query needs old data:

Oracle reads from UNDO
If UNDO is overwritten → ❌ ORA-01555

🔎 Common Causes

1️⃣ Long Running Queries

Query runs for a long time
Needs older data snapshot

2️⃣ Small UNDO Tablespace

Not enough space to retain old data

3️⃣ High DML Activity

Frequent INSERT / UPDATE / DELETE
Overwrites undo quickly

4️⃣ Commit Inside Loop (Very Common)

Bad code example:


FOR i IN 1..10000 LOOP
   UPDATE emp SET sal = sal + 100 WHERE empno = i;
   COMMIT;
END LOOP;

👉 This causes undo to be overwritten quickly.

5️⃣ Undo Retention Too Low

Check:


SHOW PARAMETER undo_retention;

🛠 Solutions

✅ 1️⃣ Increase UNDO Tablespace


ALTER DATABASE DATAFILE '/u01/undo01.dbf'
RESIZE 5G;

OR add new file:


ALTER TABLESPACE undotbs1
ADD DATAFILE '/u01/undo02.dbf' SIZE 2G;

✅ 2️⃣ Increase UNDO_RETENTION


ALTER SYSTEM SET undo_retention = 1800;

👉 (Value in seconds = 30 minutes)

✅ 3️⃣ Avoid Frequent Commits

❌ Wrong:


COMMIT inside loop

✅ Correct:


COMMIT after batch processing

✅ 4️⃣ Optimize Long Queries

Add indexes
Avoid full table scans
Break large queries into smaller chunks

✅ 5️⃣ Use RETENTION GUARANTEE (Advanced)


ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

⚠️ Use carefully → may block DML if space runs out.

🔎 Useful Monitoring Queries

Check undo usage


SELECT * FROM v$undostat;

Check undo tablespace size


SELECT tablespace_name, sum(bytes)/1024/1024 MB
FROM dba_data_files
WHERE tablespace_name LIKE 'UNDO%'
GROUP BY tablespace_name;

Check active long queries


SELECT sid, serial#, sql_id, elapsed_time
FROM v$session_longops
WHERE sofar != totalwork;

📊 Real Production Scenario

A report query runs for 1 hour
Meanwhile heavy transactions run
Undo gets overwritten
Query fails with:


ORA-01555: snapshot too old

✅ Real Fix Implemented

1️⃣ Increased UNDO tablespace
2️⃣ Increased undo_retention
3️⃣ Optimized report query
4️⃣ Scheduled job during low load

📘 Key DBA Learning

👉 ORA-01555 is usually a combination of:

Long query +
Insufficient undo +
High DML activity

⭐ Best Practices

✔ Size UNDO properly
✔ Avoid commit inside loops
✔ Monitor long queries
✔ Tune SQL
✔ Schedule heavy jobs off-peak

Reference - ChatGpt

ORA-01109: database not open

🔴 What ORA-01109 Means

Oracle database has 3 main states:

NOMOUNT – Instance started (memory + background processes)
MOUNT – Control file opened
OPEN – Datafiles opened → database ready for use ✅

👉 ORA-01109 occurs when you try to run queries while DB is in:

NOMOUNT or
MOUNT state

🔎 How to Check Current Status


SELECT status FROM v$instance;


SELECT open_mode FROM v$database;

Example output:


STATUS = MOUNTED
OPEN_MODE = MOUNTED

🛠 Solution

✅ Open the Database


ALTER DATABASE OPEN;

✅ Or Start Database Fully


STARTUP;

This will:

Start instance
Mount database
Open database

🛠 Quick Troubleshooting Checklist

✔ Check instance status
✔ Check database open mode
✔ Open database if mounted
✔ Check recovery requirement
✔ Check if standby DB
✔ Check PDB status (if applicable)

⭐ DBA Tip

Always follow:


STARTUP;

instead of partial startup unless needed.

🔥 Summary

State	Meaning	Action
NOMOUNT	Instance started	Mount DB
MOUNT	Control file open	Open DB
OPEN	Fully available	✅

💡 Important

👉 If database is not opening, always check:

Alert log
Recovery requirement
Datafile status

ORA-01031: insufficient privileges

🔴 What ORA-01031 Means

Oracle is saying:

“You don’t have the privilege to execute this SQL statement.”

🛠 Quick Troubleshooting Checklist

✔ Check what operation you are performing
✔ Verify required privilege
✔ Check if privilege is direct or via role
✔ Confirm schema access
✔ Check DB link permissions

⭐ DBA Tip

Check your privileges:


SELECT * FROM user_sys_privs;


SELECT * FROM user_tab_privs;

🔥 Quick Summary

Operation	Required Privilege
SELECT	SELECT
INSERT	INSERT
CREATE TABLE	CREATE TABLE
ALTER SYSTEM	SYSDBA