Oracle EBS 12.2 ADOP Finalize Running Long?

 

Real-Time Apps DBA Strategy to Force Gather Schema Stats SQL to Run in Parallel (19c Production Environment)

As Oracle Apps DBAs, one of the most stressful situations during an online patching cycle is seeing the ADOP finalize phase stuck for hours because a statistics gathering SQL is running serially against a massive optimizer statistics history table.

Recently during a production cutover/finalize activity on Oracle E-Business Suite 12.2 with Oracle Database 19c, we observed a long-running SQL consuming significant elapsed time:

SQL_ID: 9ys35zh9w05bn

The SQL was performing a full scan on:

SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY

Despite increasing:

parallel_max_servers

the SQL still continued to run in a single process.

This blog explains the real-world Apps DBA approach used to force the SQL into parallel execution safely during ADOP finalize.

---

Environment

Component	Version
Oracle EBS	12.2
Database	Oracle 19c
Activity	ADOP Finalize
Issue	Gather Stats SQL running serially
Table Involved	WRI$_OPTSTAT_HISTHEAD_HISTORY

---

Symptoms Observed

During finalize phase:

• Gather schema statistics running for several hours
• SQL not utilizing PX servers
• Full table scan on optimizer statistics history table
• High elapsed time
• ADOP finalize delay

Execution plan snippet:

TABLE ACCESS FULL WRI$_OPTSTAT_HISTHEAD_HISTORY
Rows : 5571M
Cost : 12M

---

Step 1 — Identify Long Running SQL

First identify the active SQL and session details.

select sid,
       serial#,
       inst_id,
       sql_id,
       status,
       event,
       seconds_in_wait
from gv$session
where sql_id = '9ys35zh9w05bn';

---

Step 2 — Verify Whether SQL is Using Parallel Execution

This is the most important validation.

select *
from gv$px_session
where qcsid in (
    select sid
    from gv$session
    where sql_id = '9ys35zh9w05bn'
);

Interpretation

Result	Meaning
Rows returned	Parallel execution active
No rows returned	SQL still running serially

In our case, no PX sessions were allocated.

---

Step 3 — Check Parallel Configuration

Verify database parallel settings.

show parameter parallel;

Key parameters:

parallel_max_servers
parallel_degree_policy
parallel_min_servers
parallel_servers_target

Recommended production setup during controlled maintenance:

parallel_degree_policy = MANUAL

---

Step 4 — Check Object-Level Degree

Check whether the SYS table itself has any degree configured.

select owner,
       table_name,
       degree
from dba_tables
where owner='SYS'
and table_name='WRI$_OPTSTAT_HISTHEAD_HISTORY';

Usually it shows:

DEGREE = 1

which means serial execution.

---

Step 5 — Temporarily Enable Parallel on Table

Since this is an internal optimizer statistics history table, changes must be temporary and carefully monitored.

alter table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY parallel 8;

Verify:

select owner,
       table_name,
       degree
from dba_tables
where owner='SYS'
and table_name='WRI$_OPTSTAT_HISTHEAD_HISTORY';

---

Step 6 — Create SQL Patch to Force Parallel Hint

This is the most effective Apps DBA approach in production because it targets only the problematic SQL.

Create SQL Patch

begin
  dbms_sqldiag.create_sql_patch(
      sql_id    => '9ys35zh9w05bn',
      hint_text => '/*+ parallel(sys.WRI$_OPTSTAT_HISTHEAD_HISTORY 8) */',
      name      => 'PATCH_PARALLEL_GATHER_STATS_FINALIZE'
  );
end;
/

---

Why SQL Patch is Preferred

Advantages

Benefit	Description
Targeted Fix	Applies only to one SQL
No Code Change	No application modification required
Fast Recovery	Useful during cutover/finalize pressure
Reversible	Can be removed immediately after activity

---

Step 7 — Verify SQL Patch

select name,
       status,
       created
from dba_sql_patches
where name='PATCH_PARALLEL_GATHER_STATS_FINALIZE';

---

Step 8 — Restart the SQL (If Required)

Important production note:

Existing cursor may continue using old execution plan.

If approved during maintenance window:

1. Identify session
2. Kill only affected SQL session
3. Allow ADOP to retry

Find Session

select inst_id,
       sid,
       serial#,
       sql_id,
       status
from gv$session
where sql_id='9ys35zh9w05bn';

Kill Session

alter system kill session 'sid,serial#,@inst_id' immediate;

Example:

alter system kill session '123,4567,@1' immediate;

---

Step 9 — Monitor Parallel Execution

After retry, monitor PX allocation.

Check PX Slaves

select inst_id,
       qcsid,
       qcserial#,
       degree,
       req_degree
from gv$px_session
where qcsid in (
    select sid
    from gv$session
    where sql_id='9ys35zh9w05bn'
);

---

Step 10 — Monitor Through SQL Monitor

select sql_id,
       status,
       px_servers_requested,
       px_servers_allocated
from gv$sql_monitor
where sql_id='9ys35zh9w05bn';

Expected result:

PX_SERVERS_ALLOCATED = 8

---

Important Production Precautions

Never Leave Parallel Settings Permanently

After finalize/cutover:

Drop SQL Patch

begin
  dbms_sqldiag.drop_sql_patch(
      name => 'PATCH_PARALLEL_GATHER_STATS_FINALIZE'
  );
end;
/

---

Reset Table Degree

alter table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY noparallel;

---

Revert Parallel Parameters

alter system set parallel_max_servers=<old_value>
scope=both sid='*';

---

Real-World DBA Lessons Learned

Key Observation

Increasing:

parallel_max_servers

alone does NOT guarantee parallel execution.

Oracle still needs:

• Parallel hint
• Table degree
• SQL patch
• Auto DOP eligibility
• Optimizer decision

---

Best Practice Recommendation

For emergency ADOP finalize performance issues:

Recommended Sequence

Step	Action
1	Identify long-running SQL
2	Check PX usage
3	Create SQL Patch with PARALLEL hint
4	Restart SQL if approved
5	Monitor PX slaves
6	Revert changes after finalize

---

Final Thoughts

In Oracle EBS production environments, especially during ADOP finalize or cutover, time is critical. SQL Patch-based parallelism is one of the safest and fastest techniques an Apps DBA can use to accelerate problematic SQL without modifying application code.

However, always remember:

• Test in lower environment first
• Use temporary changes only
• Revert all modifications after activity
• Monitor impact on system load

A good Apps DBA not only solves the issue quickly — but also ensures the environment remains stable after recovery.