Monday, July 13, 2026

oracl

SR Title

APPLSYS.SYS_LOB0000033489C00004$$ Segment Consuming Approximately 2.9 TB in Oracle E-Business Suite 12.2 Database

Problem Description

Dear Oracle Support,

We are performing a housekeeping and storage optimization exercise on our Oracle E-Business Suite Production environment.

During our storage analysis we observed that the following LOB segment has grown significantly:

Owner : APPLSYS

LOB Segment: SYS_LOB0000033489C00004$$

Current Size: ~2.9 TB

Further investigation shows that this LOB contains millions of documents stored through Oracle EBS attachment functionality.

The major file types stored include:

PDF (~1.6 TB)

JPEG (~438 GB)

PJPEG (~249 GB)

application/octet-stream (~148 GB)

ZIP (~111 GB)

Microsoft Office documents

Our objective is to understand:

Which Oracle EBS table owns this LOB.

Whether Oracle provides any supported purge utility for this data.

Whether old attachments can be archived or deleted safely.

Whether SecureFile LOB migration, LOB shrink, or online reorganization is supported.

Any Oracle recommended housekeeping activity for this LOB.

Whether this growth is expected for Oracle EBS attachments.

Whether any Oracle patches or MOS notes address excessive growth of this LOB segment.

We would appreciate Oracle's guidance before taking any cleanup action in Production.

Information We Will Provide

Oracle EBS Version

Oracle Database Version

AWR Report (if required)

Segment Report

LOB Owner Details

Output of

SELECT owner,

table_name,

column_name,

segment_name

FROM dba_lobs

WHERE segment_name='SYS_LOB0000033489C00004$$';

and

SELECT owner,

segment_name,

bytes/1024/1024/1024 GB

FROM dba_segments

WHERE segment_name='SYS_LOB0000033489C00004$$';

Experienced Oracle Database Administrator and Oracle Applications DBA with over 17 years of experience in managing mission-critical Oracle Database and Oracle E-Business Suite environments. Skilled in Oracle Database 11g, 12c, 19c, RAC, ASM, RMAN, Data Guard, Performance Tuning, Backup and Recovery, Database Security, and High Availability solutions. Extensive expertise in Oracle E-Business Suite R12.2 administration, ADOP patching, cloning, upgrades, WebLogic, Oracle HTTP Server, Concurrent Processing, Workflow Mailer, and application performance tuning. Experienced in database migrations, cross-platform migrations, automation using Shell scripting, SQL, and PL/SQL, and production support for large enterprise environments. Strong knowledge of Linux, AIX, Solaris, OEM Cloud Control, ServiceNow, JIRA, and enterprise monitoring tools. Successfully delivered Oracle Database upgrades, EBS upgrades, disaster recovery implementations, and infrastructure modernization projects while ensuring maximum system availability, security, and performance. Experienced in troubleshooting complex production issues, implementing automation, capacity planning, and collaborating with global teams to deliver business-critical database solutions.

Roles & Responsibilities (Minimum 50 words)

Provide Oracle Database and Oracle E-Business Suite administration for the client's production environment. Perform database monitoring, performance tuning, backup and recovery, patching, cloning, upgrades, troubleshooting, and automation. Support high availability, disaster recovery, security compliance, and production incident resolution while collaborating with client and offshore teams to ensure stable, secure, and highly available database services.

Friday, June 19, 2026

PARAMETERES

COL con_name FOR A20

COL parameter_name FOR A40

COL value FOR A80

SELECT c.name con_name,

p.name parameter_name,

p.value

FROM v$parameter p,

v$containers c

WHERE p.con_id=c.con_id

AND p.name IN (

'db_name',

'db_unique_name',

'service_names',

'utl_file_dir',

'local_listener',

'remote_listener',

'db_create_file_dest',

'log_archive_dest_1'

)

ORDER BY c.name,p.name;

Sunday, May 17, 2026

Oracle EBS 12.2 – Patch File System Validation Checklist

1. Take Backup of Critical Files

Take backup of the following files from all RUN/PATCH nodes and keep them in a shared NAS mount location for easy recovery.

a) Context Files

Backup all context XML files.

Example:


$INST_TOP/appl/admin/*.xml

b) Environment Files


$APPL_TOP/*.env

c) WebLogic Domain Configuration


$FMW_HOME/user_projects/domains/EBS_domain_<SID>/config/config.xml

2. Copy Patch to Shared Mount Point

Copy the required patch files to the extranet/shared mount location.

Example:


/nas/ebs_patches/

3. Run ADOP Validation

Execute validation before starting the patching cycle.


adop phase=validate

Ensure validation completes successfully without errors.

4. Validate Admin Server and Node Manager

Connect to PATCH file system and validate startup/shutdown of:

Admin Server (Primary Node)
Node Manager (All Nodes)

Source Patch Environment


cd ~

. ./EBSapps.env patch

Admin Server Start/Stop Validation


$ADMIN_SCRIPTS_HOME/adadminsrvctl.sh start
$ADMIN_SCRIPTS_HOME/adadminsrvctl.sh stop

Node Manager Start/Stop Validation


$ADMIN_SCRIPTS_HOME/adnodemgrctl.sh start
$ADMIN_SCRIPTS_HOME/adnodemgrctl.sh stop

5. Ensure No Patch File System Processes Are Running

Verify no residual PATCH filesystem processes are active across application nodes.

Example:


ps -ef | grep fs2

If any unwanted processes exist, stop them cleanly before proceeding.

6. Validate Node Manager Port Configuration

Ensure:

RUN filesystem ports are identical across all RUN nodes.
PATCH filesystem uses a different Node Manager port.

Verify Node Manager Listen Port


grep -i listen-port \
$FMW_HOME/user_projects/domains/EBS_domain_<SID>/config/config.xml

Extract Port Number


grep -i listen-port \
$FMW_HOME/user_projects/domains/EBS_domain_<SID>/config/config.xml \
| sed -n 1p \
| cut -d'>' -f2 \
| cut -d'<' -f1

Recommended DBA Validation Checks

Verify Node Manager Process


ps -ef | grep NodeManager

Check Listening Ports


netstat -an | grep LISTEN | grep <PORT>

Verify WebLogic Admin Connectivity


curl -I http://hostname:port

Important Best Practices

Always validate PATCH filesystem before adop prepare.
Ensure no orphan Java/WLS processes exist.
Maintain backup of config.xml before changes.
Validate SSH equivalency between nodes.
Confirm shared filesystem mount availability on all nodes.
Check free space in:
- $PATCH_TOP
- $NE_BASE
- $COMMON_TOP

Typical Real-Time Use Cases

This checklist is commonly used before:

adop phase=prepare
adop fs_clone
WebLogic recovery
Node Manager troubleshooting
Dual filesystem synchronization issues
Patch filesystem corruption validation

Saturday, May 16, 2026

Oracle SQL Plan Migration Runbook

Objective

This runbook explains how to move a good execution plan from a source environment (Source Environment) to a Production environment using:

SQL Tuning Set (STS)
Data Pump (expdp/impdp)
SQL Plan Management (SPM)

This approach is commonly used by Oracle Apps DBAs during:

SQL Plan Regression
Month-End Performance Issues
Concurrent Program Slow Performance
Optimizer Plan Changes after Statistics Gathering
Oracle EBS 12.2 Performance Stabilization

High-Level Flow

Identify Good Plan

↓

Create SQL Tuning Set (STS)

↓

Load Good SQL into STS

↓

Create STS Staging Table

↓

Export STS Table using Data Pump

↓

Transfer Dump File to Production

↓

Import STS Table into Production

↓

Unpack STS

↓

Load Plan into SQL Plan Baseline

↓

Purge Old Cursor from Shared Pool

↓

Re-execute SQL / Concurrent Program

↓

Validate Improved Execution Plan

Source Environment Steps (Source Environment)

Step 1: Identify the Good SQL Plan

Find the SQL_ID and PLAN_HASH_VALUE of the good plan.

SELECT sql_id,

plan_hash_value

FROM v$sqlarea

WHERE sql_id IN ('f9k42ab71mn8q');

SELECT sql_id,

plan_hash_value

FROM v$sql

WHERE sql_id IN ('f9k42ab71mn8q');

Step 2: Create Empty SQL Tuning Set (STS)

BEGIN

DBMS_SQLTUNE.CREATE_SQLSET(

sqlset_name => 'f9k42ab71mn8q_STS',

description => 'STS to move better plan to Production');

END;

Step 3: Load SQL Information into STS

DECLARE

s_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR;

BEGIN

OPEN s_sqlarea_cursor FOR

SELECT VALUE(p)

FROM TABLE(

DBMS_SQLTUNE.SELECT_CURSOR_CACHE(

'sql_id = ''f9k42ab71mn8q''

AND plan_hash_value = 1847263512')) p;

DBMS_SQLTUNE.LOAD_SQLSET(

sqlset_name => 'f9k42ab71mn8q_STS',

populate_cursor => s_sqlarea_cursor);

END;

Step 4: Verify SQL Tuning Set Contents

SELECT name,

statement_count,

description

FROM dba_sqlset;

Formatting commands:

COLUMN sql_text FORMAT a30

COLUMN sch FORMAT a3

COLUMN elapsed FORMAT 999999999

Query STS contents:

SELECT sql_id,

parsing_schema_name AS "SCH",

sql_text,

elapsed_time AS "ELAPSED",

buffer_gets

FROM TABLE(

DBMS_SQLTUNE.SELECT_SQLSET('f9k42ab71mn8q_STS'));

Detailed verification:

SELECT *

FROM TABLE(

DBMS_SQLTUNE.SELECT_SQLSET('f9k42ab71mn8q_STS'));

Step 5: Create Staging Table

Note: Table names and parameters are case-sensitive.

EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(

table_name => 'TEST');

Step 6: Pack STS into Staging Table

BEGIN

DBMS_SQLTUNE.PACK_STGTAB_SQLSET(

sqlset_name => 'f9k42ab71mn8q_STS',

sqlset_owner => 'SYSTEM',

staging_table_name => 'TEST',

staging_schema_owner=> 'SYSTEM');

END;

Step 7: Create Oracle Directory Object

CREATE DIRECTORY TEST AS

'/opt/mis/backup_1/EBS_SQLSET_BACKUP';

Step 8: Export Staging Table using Data Pump

expdp system DIRECTORY=TEST \

DUMPFILE=f9k42ab71mn8q_STS.dmp \

TABLES=TEST

Step 9: Transfer Dump File to Production

Transfer the dump file using:

SCP
FTP
SFTP

Example:

scp f9k42ab71mn8q_STS.dmp oracle@targetserver:/backup

Target Environment Steps (Production)

Step 10: Import the Dump File

impdp system DIRECTORY=TEST \

DUMPFILE=f9k42ab71mn8q_STS.dmp \

TABLES=TEST

Step 11: Unpack SQL Tuning Set

BEGIN

DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(

sqlset_name => '%',

sqlset_owner => 'SYSTEM',

replace => TRUE,

staging_table_name => 'TEST',

staging_schema_owner => 'SYSTEM');

END;

Step 12: Load Plan from STS into SQL Plan Baseline

VARIABLE v_plan_cnt NUMBER

EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(

sqlset_name => 'f9k42ab71mn8q_STS',

sqlset_owner => 'SYSTEM',

basic_filter =>

'sql_id = ''f9k42ab71mn8q''

AND plan_hash_value = 1847263512');

Step 13: Purge Existing SQL from Shared Pool

This forces Oracle to re-parse and pick the new SQL Plan Baseline.

Generate purge command:

SELECT 'exec DBMS_SHARED_POOL.PURGE('''

|| ADDRESS || ',' || HASH_VALUE || ''',''C'');'

FROM v$sqlarea

WHERE sql_id IN ('f9k42ab71mn8q');

Execute generated command:

EXEC DBMS_SHARED_POOL.PURGE(

'0000001006B396C8,2113289046',

'C');

Step 14: Alternative Method - Load from Cursor Cache

DECLARE

i NATURAL;

BEGIN

i := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(

'f9k42ab71mn8q',

1847263512);

END;

Validation Queries

Check SQL Baselines

SELECT sql_handle,

plan_name,

enabled,

accepted,

fixed

FROM dba_sql_plan_baselines

WHERE signature IN (

SELECT exact_matching_signature

FROM v$sql

WHERE sql_id='f9k42ab71mn8q');

Verify Active Plan

SELECT sql_id,

child_number,

plan_hash_value,

executions,

elapsed_time

FROM v$sql

WHERE sql_id='f9k42ab71mn8q';

Check if Baseline is Used

SELECT sql_id,

sql_plan_baseline,

plan_hash_value

FROM v$sql

WHERE sql_id='f9k42ab71mn8q';

Oracle Apps DBA Production Checklist

Check	Status
Good SQL_ID identified	☐
Correct PLAN_HASH_VALUE captured	☐
STS created successfully	☐
SQL loaded into STS	☐
Staging table created	☐
STS packed successfully	☐
Data Pump export completed	☐
Dump transferred securely	☐
Import completed in Production	☐
STS unpacked successfully	☐
SQL Plan Baseline loaded	☐
Old cursor purged	☐
Concurrent request rerun	☐
Improved performance validated	☐

Real-Time Oracle EBS Scenario

Problem

A month-end concurrent request that normally completes in 2 minutes suddenly started taking 4 hours after statistics gathering.

Root Cause

Optimizer selected a new bad execution plan with:

Full Table Scan
High Logical Reads
Excessive Nested Loop Operations
Large TEMP Usage

Solution

DBA identified a good historical plan from Source Environment and migrated it to Production using:

SQL Tuning Set (STS)
SQL Plan Baseline (SPM)

Result

Before	After
Runtime: 4 Hours	Runtime: 2 Minutes
TEMP Spike	Stable TEMP
CPU High	CPU Normal
Business Delay	Business Success

Important Notes

Best Practices

Always validate the plan in lower environments first.
Never purge shared pool aggressively in peak production hours.
Take business approval before rerunning concurrent requests.
Verify plan stability after stats gathering.
Monitor AWR and ASH after implementation.

Important DBA Views

View	Purpose
V$SQL	Active SQL details
V$SQLAREA	Aggregated SQL statistics
DBA_SQLSET	SQL Tuning Sets
DBA_SQL_PLAN_BASELINES	SQL Baselines
V$SQL_MONITOR	Real-time SQL monitoring
DBA_HIST_SQLSTAT	Historical SQL statistics
DBA_HIST_ACTIVE_SESS_HISTORY	ASH performance analysis

Conclusion

Using SQL Plan Management (SPM) and SQL Tuning Sets (STS) is one of the safest methods to stabilize SQL performance in Oracle EBS 12.2 Production environments.

This approach helps Oracle Apps DBAs:

Avoid risky code changes
Restore performance quickly
Reduce month-end failures
Stabilize execution plans
Improve business confidence

It is a critical real-world DBA skill for handling production SQL regressions.

Oppertunity Ta

### 1. Opportunity Tracker Table

Design a clean, professional Markdown table (and also suggest an Excel/Google Sheet format) with these exact columns:

- Opportunity ID (e.g., OPP-001) - Company / Client - Project Name - Position (e.g., Oracle Apps DBA – Application & Infra Operations) - Location (City & Country) - Recruiter / Vendor Name & Contact - Application Date - Current Status (Applied | Screening | Interview | Offer | Rejected | On Hold | Follow-up Needed) - Last Follow-up Date - Next Action Due Date - Priority (High / Medium / Low) - Salary / Package (if known) - Key Notes / Remarks - Link to JD / Email Thread

Friday, May 15, 2026

plan migration

Oracle SQL Plan Migration Runbook

Objective

This runbook explains how to move a good execution plan from a source environment (Source Environment) to a Production environment using:

SQL Tuning Set (STS)
Data Pump (expdp/impdp)
SQL Plan Management (SPM)

This approach is commonly used by Oracle Apps DBAs during:

SQL Plan Regression
Month-End Performance Issues
Concurrent Program Slow Performance
Optimizer Plan Changes after Statistics Gathering
Oracle EBS 12.2 Performance Stabilization

High-Level Flow

Identify Good Plan
        ↓
Create SQL Tuning Set (STS)
        ↓
Load Good SQL into STS
        ↓
Create STS Staging Table
        ↓
Export STS Table using Data Pump
        ↓
Transfer Dump File to Production
        ↓
Import STS Table into Production
        ↓
Unpack STS
        ↓
Load Plan into SQL Plan Baseline
        ↓
Purge Old Cursor from Shared Pool
        ↓
Re-execute SQL / Concurrent Program
        ↓
Validate Improved Execution Plan

Source Environment Steps (Source Environment)

Step 1: Identify the Good SQL Plan

Find the SQL_ID and PLAN_HASH_VALUE of the good plan.

SELECT sql_id,
       plan_hash_value
FROM   v$sqlarea
WHERE  sql_id IN ('f9k42ab71mn8q');

SELECT sql_id,
       plan_hash_value
FROM   v$sql
WHERE  sql_id IN ('f9k42ab71mn8q');

Step 2: Create Empty SQL Tuning Set (STS)

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
      sqlset_name => 'f9k42ab71mn8q_STS',
      description => 'STS to move better plan to Production');
END;
/

Step 3: Load SQL Information into STS

DECLARE
  s_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN

  OPEN s_sqlarea_cursor FOR
  SELECT VALUE(p)
  FROM TABLE(
       DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
       'sql_id = ''f9k42ab71mn8q''
        AND plan_hash_value = 1847263512')) p;

  DBMS_SQLTUNE.LOAD_SQLSET(
      sqlset_name     => 'f9k42ab71mn8q_STS',
      populate_cursor => s_sqlarea_cursor);

END;
/

Step 4: Verify SQL Tuning Set Contents

SELECT name,
       statement_count,
       description
FROM   dba_sqlset;

Formatting commands:

COLUMN sql_text FORMAT a30
COLUMN sch FORMAT a3
COLUMN elapsed FORMAT 999999999

Query STS contents:

SELECT sql_id,
       parsing_schema_name AS "SCH",
       sql_text,
       elapsed_time AS "ELAPSED",
       buffer_gets
FROM TABLE(
     DBMS_SQLTUNE.SELECT_SQLSET('f9k42ab71mn8q_STS'));

Detailed verification:

SELECT *
FROM TABLE(
     DBMS_SQLTUNE.SELECT_SQLSET('f9k42ab71mn8q_STS'));

Step 5: Create Staging Table

Note: Table names and parameters are case-sensitive.

EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
     table_name => 'TEST');

Step 6: Pack STS into Staging Table

BEGIN

  DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
      sqlset_name         => 'f9k42ab71mn8q_STS',
      sqlset_owner        => 'SYSTEM',
      staging_table_name  => 'TEST',
      staging_schema_owner=> 'SYSTEM');

END;
/

Step 7: Create Oracle Directory Object

CREATE DIRECTORY TEST AS
'/opt/mis/ebs_backup_lv/backup_1/EBS_SQLSET_BACKUP';

Step 8: Export Staging Table using Data Pump

expdp system DIRECTORY=TEST \
DUMPFILE=f9k42ab71mn8q_STS.dmp \
TABLES=TEST

Step 9: Transfer Dump File to Production

Transfer the dump file using:

SCP
FTP
SFTP

Example:

scp f9k42ab71mn8q_STS.dmp oracle@targetserver:/backup

Target Environment Steps (Production)

Step 10: Import the Dump File

impdp system DIRECTORY=TEST \
DUMPFILE=f9k42ab71mn8q_STS.dmp \
TABLES=TEST

Step 11: Unpack SQL Tuning Set

BEGIN

  DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
      sqlset_name          => '%',
      sqlset_owner         => 'SYSTEM',
      replace              => TRUE,
      staging_table_name   => 'TEST',
      staging_schema_owner => 'SYSTEM');

END;
/

Step 12: Load Plan from STS into SQL Plan Baseline

VARIABLE v_plan_cnt NUMBER

EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
         sqlset_name => 'f9k42ab71mn8q_STS',
         sqlset_owner => 'SYSTEM',
         basic_filter =>
         'sql_id = ''f9k42ab71mn8q''
          AND plan_hash_value = 1847263512');

Step 13: Purge Existing SQL from Shared Pool

This forces Oracle to re-parse and pick the new SQL Plan Baseline.

Generate purge command:

SELECT 'exec DBMS_SHARED_POOL.PURGE('''
       || ADDRESS || ',' || HASH_VALUE || ''',''C'');'
FROM   v$sqlarea
WHERE  sql_id IN ('f9k42ab71mn8q');

Execute generated command:

EXEC DBMS_SHARED_POOL.PURGE(
'0000001006B396C8,2113289046',
'C');

Step 14: Alternative Method - Load from Cursor Cache

DECLARE
  i NATURAL;
BEGIN

  i := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
       'f9k42ab71mn8q',
       1847263512);

END;
/

Validation Queries

Check SQL Baselines

SELECT sql_handle,
       plan_name,
       enabled,
       accepted,
       fixed
FROM   dba_sql_plan_baselines
WHERE  signature IN (
       SELECT exact_matching_signature
       FROM   v$sql
       WHERE  sql_id='f9k42ab71mn8q');

Verify Active Plan

SELECT sql_id,
       child_number,
       plan_hash_value,
       executions,
       elapsed_time
FROM   v$sql
WHERE  sql_id='f9k42ab71mn8q';

Check if Baseline is Used

SELECT sql_id,
       sql_plan_baseline,
       plan_hash_value
FROM   v$sql
WHERE  sql_id='f9k42ab71mn8q';

Oracle Apps DBA Production Checklist

Check	Status
Good SQL_ID identified	☐
Correct PLAN_HASH_VALUE captured	☐
STS created successfully	☐
SQL loaded into STS	☐
Staging table created	☐
STS packed successfully	☐
Data Pump export completed	☐
Dump transferred securely	☐
Import completed in Production	☐
STS unpacked successfully	☐
SQL Plan Baseline loaded	☐
Old cursor purged	☐
Concurrent request rerun	☐
Improved performance validated	☐

Real-Time Oracle EBS Scenario

Problem

A month-end concurrent request that normally completes in 2 minutes suddenly started taking 4 hours after statistics gathering.

Root Cause

Optimizer selected a new bad execution plan with:

Full Table Scan
High Logical Reads
Excessive Nested Loop Operations
Large TEMP Usage

Solution

DBA identified a good historical plan from Source Environment and migrated it to Production using:

SQL Tuning Set (STS)
SQL Plan Baseline (SPM)

Result

Before	After
Runtime: 4 Hours	Runtime: 2 Minutes
TEMP Spike	Stable TEMP
CPU High	CPU Normal
Business Delay	Business Success

Important Notes

Best Practices

Always validate the plan in lower environments first.
Never purge shared pool aggressively in peak production hours.
Take business approval before rerunning concurrent requests.
Verify plan stability after stats gathering.
Monitor AWR and ASH after implementation.

Important DBA Views

View	Purpose
V$SQL	Active SQL details
V$SQLAREA	Aggregated SQL statistics
DBA_SQLSET	SQL Tuning Sets
DBA_SQL_PLAN_BASELINES	SQL Baselines
V$SQL_MONITOR	Real-time SQL monitoring
DBA_HIST_SQLSTAT	Historical SQL statistics
DBA_HIST_ACTIVE_SESS_HISTORY	ASH performance analysis

Conclusion

Using SQL Plan Management (SPM) and SQL Tuning Sets (STS) is one of the safest methods to stabilize SQL performance in Oracle EBS 12.2 Production environments.

This approach helps Oracle Apps DBAs:

Avoid risky code changes
Restore performance quickly
Reduce month-end failures
Stabilize execution plans
Improve business confidence

It is a critical real-world DBA skill for handling production SQL regressions.

Oracle SQL Plan Migration Runbook

Objective

This runbook explains how to move a good execution plan from a source environment (Source Environment) to a Production environment using:

SQL Tuning Set (STS)
Data Pump (expdp/impdp)
SQL Plan Management (SPM)

This approach is commonly used by Oracle Apps DBAs during:

SQL Plan Regression
Month-End Performance Issues
Concurrent Program Slow Performance
Optimizer Plan Changes after Statistics Gathering
Oracle EBS 12.2 Performance Stabilization

High-Level Flow

Identify Good Plan

↓

Create SQL Tuning Set (STS)

↓

Load Good SQL into STS

↓

Create STS Staging Table

↓

Export STS Table using Data Pump

↓

Transfer Dump File to Production

↓

Import STS Table into Production

↓

Unpack STS

↓

Load Plan into SQL Plan Baseline

↓

Purge Old Cursor from Shared Pool

↓

Re-execute SQL / Concurrent Program

↓

Validate Improved Execution Plan

Source Environment Steps (Source Environment)

Step 1: Identify the Good SQL Plan

Find the SQL_ID and PLAN_HASH_VALUE of the good plan.

SELECT sql_id,

plan_hash_value

FROM v$sqlarea

WHERE sql_id IN ('f9k42ab71mn8q');

SELECT sql_id,

plan_hash_value

FROM v$sql

WHERE sql_id IN ('f9k42ab71mn8q');

Step 2: Create Empty SQL Tuning Set (STS)

BEGIN

DBMS_SQLTUNE.CREATE_SQLSET(

sqlset_name => 'f9k42ab71mn8q_STS',

description => 'STS to move better plan to Production');

END;

Step 3: Load SQL Information into STS

DECLARE

s_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR;

BEGIN

OPEN s_sqlarea_cursor FOR

SELECT VALUE(p)

FROM TABLE(

DBMS_SQLTUNE.SELECT_CURSOR_CACHE(

'sql_id = ''f9k42ab71mn8q''

AND plan_hash_value = 1847263512')) p;

DBMS_SQLTUNE.LOAD_SQLSET(

sqlset_name => 'f9k42ab71mn8q_STS',

populate_cursor => s_sqlarea_cursor);

END;

Step 4: Verify SQL Tuning Set Contents

SELECT name,

statement_count,

description

FROM dba_sqlset;

Formatting commands:

COLUMN sql_text FORMAT a30

COLUMN sch FORMAT a3

COLUMN elapsed FORMAT 999999999

Query STS contents:

SELECT sql_id,

parsing_schema_name AS "SCH",

sql_text,

elapsed_time AS "ELAPSED",

buffer_gets

FROM TABLE(

DBMS_SQLTUNE.SELECT_SQLSET('f9k42ab71mn8q_STS'));

Detailed verification:

SELECT *

FROM TABLE(

DBMS_SQLTUNE.SELECT_SQLSET('f9k42ab71mn8q_STS'));

Step 5: Create Staging Table

Note: Table names and parameters are case-sensitive.

EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(

table_name => 'TEST');

Step 6: Pack STS into Staging Table

BEGIN

DBMS_SQLTUNE.PACK_STGTAB_SQLSET(

sqlset_name => 'f9k42ab71mn8q_STS',

sqlset_owner => 'SYSTEM',

staging_table_name => 'TEST',

staging_schema_owner=> 'SYSTEM');

END;

Step 7: Create Oracle Directory Object

CREATE DIRECTORY TEST AS

'tmp/EBS_SQLSET_BACKUP';

Step 8: Export Staging Table using Data Pump

expdp system DIRECTORY=TEST \

DUMPFILE=f9k42ab71mn8q_STS.dmp \

TABLES=TEST

Step 9: Transfer Dump File to Production

Transfer the dump file using:

SCP
FTP
SFTP

Example:

scp f9k42ab71mn8q_STS.dmp oracle@targetserver:/backup

Target Environment Steps (Production)

Step 10: Import the Dump File

impdp system DIRECTORY=TEST \

DUMPFILE=f9k42ab71mn8q_STS.dmp \

TABLES=TEST

Step 11: Unpack SQL Tuning Set

BEGIN

DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(

sqlset_name => '%',

sqlset_owner => 'SYSTEM',

replace => TRUE,

staging_table_name => 'TEST',

staging_schema_owner => 'SYSTEM');

END;

Step 12: Load Plan from STS into SQL Plan Baseline

VARIABLE v_plan_cnt NUMBER

EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(

sqlset_name => 'f9k42ab71mn8q_STS',

sqlset_owner => 'SYSTEM',

basic_filter =>

'sql_id = ''f9k42ab71mn8q''

AND plan_hash_value = 1847263512');

Step 13: Purge Existing SQL from Shared Pool

This forces Oracle to re-parse and pick the new SQL Plan Baseline.

Generate purge command:

SELECT 'exec DBMS_SHARED_POOL.PURGE('''

|| ADDRESS || ',' || HASH_VALUE || ''',''C'');'

FROM v$sqlarea

WHERE sql_id IN ('f9k42ab71mn8q');

Execute generated command:

EXEC DBMS_SHARED_POOL.PURGE(

'0000001006B396C8,2113289046',

'C');

Step 14: Alternative Method - Load from Cursor Cache

DECLARE

i NATURAL;

BEGIN

i := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(

'f9k42ab71mn8q',

1847263512);

END;

Validation Queries

Check SQL Baselines

SELECT sql_handle,

plan_name,

enabled,

accepted,

fixed

FROM dba_sql_plan_baselines

WHERE signature IN (

SELECT exact_matching_signature

FROM v$sql

WHERE sql_id='f9k42ab71mn8q');

Verify Active Plan

SELECT sql_id,

child_number,

plan_hash_value,

executions,

elapsed_time

FROM v$sql

WHERE sql_id='f9k42ab71mn8q';

Check if Baseline is Used

SELECT sql_id,

sql_plan_baseline,

plan_hash_value

FROM v$sql

WHERE sql_id='f9k42ab71mn8q';

Oracle Apps DBA Production Checklist

Check	Status
Good SQL_ID identified	☐
Correct PLAN_HASH_VALUE captured	☐
STS created successfully	☐
SQL loaded into STS	☐
Staging table created	☐
STS packed successfully	☐
Data Pump export completed	☐
Dump transferred securely	☐
Import completed in Production	☐
STS unpacked successfully	☐
SQL Plan Baseline loaded	☐
Old cursor purged	☐
Concurrent request rerun	☐
Improved performance validated	☐

Real-Time Oracle EBS Scenario

Problem

A month-end concurrent request that normally completes in 2 minutes suddenly started taking 4 hours after statistics gathering.

Root Cause

Optimizer selected a new bad execution plan with:

Full Table Scan
High Logical Reads
Excessive Nested Loop Operations
Large TEMP Usage

Solution

DBA identified a good historical plan from Source Environment and migrated it to Production using:

SQL Tuning Set (STS)
SQL Plan Baseline (SPM)

Result

Before	After
Runtime: 4 Hours	Runtime: 2 Minutes
TEMP Spike	Stable TEMP
CPU High	CPU Normal
Business Delay	Business Success

Important Notes

Best Practices

Always validate the plan in lower environments first.
Never purge shared pool aggressively in peak production hours.
Take business approval before rerunning concurrent requests.
Verify plan stability after stats gathering.
Monitor AWR and ASH after implementation.

Important DBA Views

View	Purpose
V$SQL	Active SQL details
V$SQLAREA	Aggregated SQL statistics
DBA_SQLSET	SQL Tuning Sets
DBA_SQL_PLAN_BASELINES	SQL Baselines
V$SQL_MONITOR	Real-time SQL monitoring
DBA_HIST_SQLSTAT	Historical SQL statistics
DBA_HIST_ACTIVE_SESS_HISTORY	ASH performance analysis

Conclusion

Using SQL Plan Management (SPM) and SQL Tuning Sets (STS) is one of the safest methods to stabilize SQL performance in Oracle EBS 12.2 Production environments.

This approach helps Oracle Apps DBAs:

Avoid risky code changes
Restore performance quickly
Reduce month-end failures
Stabilize execution plans
Improve business confidence

It is a critical real-world DBA skill for handling production SQL regressions.

Wednesday, May 13, 2026

Pre Prod Analysis

-- 1. Confirm concurrent request details


SELECT request_id,
       phase_code,
       status_code,
       actual_start_date,
       actual_completion_date,
       argument_text,
       logfile_name,
       outfile_name
FROM apps.fnd_concurrent_requests
WHERE request_id = 598460495;


-- 2. Get program/application details
SELECT r.request_id,
       p.concurrent_program_name,
       pt.user_concurrent_program_name,
       a.application_short_name,
       r.phase_code,
       r.status_code
FROM apps.fnd_concurrent_requests r,
     apps.fnd_concurrent_programs p,
     apps.fnd_concurrent_programs_tl pt,
     apps.fnd_application a
WHERE r.concurrent_program_id = p.concurrent_program_id
AND   p.concurrent_program_id = pt.concurrent_program_id
AND   p.application_id = a.application_id
AND   p.application_id = pt.application_id
AND   pt.language = 'US'
AND   r.request_id = 598460495;


-- 3. Validate current database session
SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.status,
       s.sql_id,
       s.prev_sql_id,
       s.event,
       s.wait_class,
       s.seconds_in_wait,
       s.blocking_session,
       s.module,
       s.action,
       s.client_identifier,
       s.machine,
       s.program
FROM gv$session s
WHERE s.sid = 2581
AND   s.serial# = 24100;


-- 4. Check if it is really sleeping/retry loop
SELECT inst_id,
       sid,
       serial#,
       event,
       wait_class,
       state,
       seconds_in_wait,
       sql_id,
       prev_sql_id,
       plsql_entry_object_id,
       plsql_object_id,
       plsql_subprogram_id
FROM gv$session
WHERE sid = 2581
AND serial# = 24100;


-- 5. Identify PL/SQL object currently involved
SELECT s.sid,
       s.sql_id,
       s.event,
       o.owner,
       o.object_name,
       o.object_type
FROM gv$session s
LEFT JOIN dba_objects o
       ON s.plsql_object_id = o.object_id
WHERE s.sid = 2581
AND s.serial# = 24100;


-- 6. Get SQL text for SQL_ID
SELECT sql_id,
       sql_text
FROM gv$sql
WHERE sql_id = 'dxasruz3nkuvf';


-- 7. Check ASH history for this request/session
SELECT sample_time,
       session_id,
       session_serial#,
       sql_id,
       event,
       wait_class,
       module,
       action,
       blocking_session,
       current_obj#
FROM gv$active_session_history
WHERE session_id = 2581
AND session_serial# = 24100
ORDER BY sample_time DESC;


-- 8. If ASH aged out, check AWR ASH
SELECT sample_time,
       instance_number,
       session_id,
       session_serial#,
       sql_id,
       event,
       wait_class,
       module,
       action,
       blocking_session
FROM dba_hist_active_sess_history
WHERE session_id = 2581
AND session_serial# = 24100
AND sample_time >= SYSDATE - 1
ORDER BY sample_time DESC;


-- 9. Check if request is waiting for another request
SELECT request_id,
       parent_request_id,
       priority_request_id,
       phase_code,
       status_code,
       hold_flag,
       requested_start_date,
       actual_start_date
FROM apps.fnd_concurrent_requests
WHERE request_id = 598460495
   OR parent_request_id = 598460495;


-- 10. Check incompatibility/blocking at concurrent manager level
SELECT r.request_id,
       r.phase_code,
       r.status_code,
       r.hold_flag,
       r.requested_start_date,
       r.actual_start_date,
       r.controlling_manager,
       r.concurrent_program_id
FROM apps.fnd_concurrent_requests r
WHERE r.phase_code = 'R'
AND r.status_code = 'R'
ORDER BY r.actual_start_date;

Initial RCA direction:

The first request is not showing a normal I/O wait. It is waiting on PL/SQL lock timer, which commonly indicates the program is intentionally sleeping or polling inside package logic. Since it has already run for around 128 minutes, the DBA should verify whether the TPA Monitor program is stuck in a retry loop, waiting for dependent activity, waiting for another concurrent request, or controlled by application logic.

Do not kill immediately. First collect:


-- Evidence snapshot
SELECT SYSDATE evidence_time,
       inst_id,
       sid,
       serial#,
       sql_id,
       prev_sql_id,
       event,
       wait_class,
       seconds_in_wait,
       module,
       action,
       client_identifier
FROM gv$session
WHERE sid = 2581
AND serial# = 24100;

Then check the request log file from logfile_name. If the log is not moving and ASH repeatedly shows PL/SQL lock timer, then update business/application owner:

Suggested update:

The request is currently active for ~128 minutes. Database session is not waiting on I/O or CPU bottleneck; it is mainly waiting on PL/SQL lock timer, which indicates the program is sleeping/polling inside application PL/SQL logic. DBA is validating the request log, PL/SQL package, dependency with other requests, and ASH history before taking any action. Recommended not to kill until application/business confirmation is received.