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;
This runbook explains how to move a good execution plan from a source environment (Source Environment) to a Production environment using:
This approach is commonly used by Oracle Apps DBAs during:
Find the SQL_ID and PLAN_HASH_VALUE of the good plan.
OR
Formatting commands:
Query STS contents:
Detailed verification:
Note: Table names and parameters are case-sensitive.
Transfer the dump file using:
Example:
This forces Oracle to re-parse and pick the new SQL Plan Baseline.
Generate purge command:
Execute generated command:
| 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 | ☐ |
A month-end concurrent request that normally completes in 2 minutes suddenly started taking 4 hours after statistics gathering.
Optimizer selected a new bad execution plan with:
DBA identified a good historical plan from Source Environment and migrated it to Production using:
| Before | After |
| Runtime: 4 Hours | Runtime: 2 Minutes |
| TEMP Spike | Stable TEMP |
| CPU High | CPU Normal |
| Business Delay | Business Success |
| 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 |
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:
It is a critical real-world DBA skill for handling production SQL regressions.
### 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
This runbook explains how to move a good execution plan from a source environment (Source Environment) to a Production environment using:
This approach is commonly used by Oracle Apps DBAs during:
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
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');
OR
SELECT sql_id,
plan_hash_value
FROM v$sql
WHERE sql_id IN ('f9k42ab71mn8q');
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'f9k42ab71mn8q_STS',
description => 'STS to move better plan to Production');
END;
/
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;
/
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'));
Note: Table names and parameters are case-sensitive.
EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(
table_name => 'TEST');
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(
sqlset_name => 'f9k42ab71mn8q_STS',
sqlset_owner => 'SYSTEM',
staging_table_name => 'TEST',
staging_schema_owner=> 'SYSTEM');
END;
/
CREATE DIRECTORY TEST AS
'/opt/mis/ebs_backup_lv/backup_1/EBS_SQLSET_BACKUP';
expdp system DIRECTORY=TEST \
DUMPFILE=f9k42ab71mn8q_STS.dmp \
TABLES=TEST
Transfer the dump file using:
Example:
scp f9k42ab71mn8q_STS.dmp oracle@targetserver:/backup
impdp system DIRECTORY=TEST \
DUMPFILE=f9k42ab71mn8q_STS.dmp \
TABLES=TEST
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => '%',
sqlset_owner => 'SYSTEM',
replace => TRUE,
staging_table_name => 'TEST',
staging_schema_owner => 'SYSTEM');
END;
/
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');
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');
DECLARE
i NATURAL;
BEGIN
i := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
'f9k42ab71mn8q',
1847263512);
END;
/
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');
SELECT sql_id,
child_number,
plan_hash_value,
executions,
elapsed_time
FROM v$sql
WHERE sql_id='f9k42ab71mn8q';
SELECT sql_id,
sql_plan_baseline,
plan_hash_value
FROM v$sql
WHERE sql_id='f9k42ab71mn8q';
| 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 | ☐ |
A month-end concurrent request that normally completes in 2 minutes suddenly started taking 4 hours after statistics gathering.
Optimizer selected a new bad execution plan with:
DBA identified a good historical plan from Source Environment and migrated it to Production using:
| Before | After |
|---|---|
| Runtime: 4 Hours | Runtime: 2 Minutes |
| TEMP Spike | Stable TEMP |
| CPU High | CPU Normal |
| Business Delay | Business Success |
| 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 |
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:
It is a critical real-world DBA skill for handling production SQL regressions.
This runbook explains how to move a good execution plan from a source environment (Source Environment) to a Production environment using:
This approach is commonly used by Oracle Apps DBAs during:
Find the SQL_ID and PLAN_HASH_VALUE of the good plan.
OR
Formatting commands:
Query STS contents:
Detailed verification:
Note: Table names and parameters are case-sensitive.
Transfer the dump file using:
Example:
This forces Oracle to re-parse and pick the new SQL Plan Baseline.
Generate purge command:
Execute generated command:
| 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 | ☐ |
A month-end concurrent request that normally completes in 2 minutes suddenly started taking 4 hours after statistics gathering.
Optimizer selected a new bad execution plan with:
DBA identified a good historical plan from Source Environment and migrated it to Production using:
| Before | After |
| Runtime: 4 Hours | Runtime: 2 Minutes |
| TEMP Spike | Stable TEMP |
| CPU High | CPU Normal |
| Business Delay | Business Success |
| 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 |
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:
It is a critical real-world DBA skill for handling production SQL regressions.
-- 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.