Wednesday, April 8, 2026

web

<!DOCTYPE html>

<head>

<title>How to Investigate BNE Upload Issues in Oracle EBS 12.2 \u2014 Step-by-Step Guide</title>

<style>

:root {

--bg: #0d0f14;

--surface: #141720;

--border: #1e2230;

--accent: #f97316;

--accent2: #38bdf8;

--accent3: #a3e635;

--text: #e2e8f0;

--muted: #64748b;

--code-bg: #0a0c10;

}

* { margin: 0; padding: 0; box-sizing: border-box; }

body {

background: var(--bg);

color: var(--text);

font-family: 'DM Sans', sans-serif;

font-size: 16px;

line-height: 1.75;

}

/* \u2500\u2500 HERO \u2500\u2500 */

.hero {

background: linear-gradient(135deg, #0d0f14 0%, #111827 60%, #0f1a2e 100%);

border-bottom: 1px solid var(--border);

padding: 64px 24px 48px;

text-align: center;

position: relative;

overflow: hidden;

}

.hero::before {

content: '';

position: absolute;

inset: 0;

background: radial-gradient(ellipse 60% 50% at 50% 0%, rgba(249,115,22,0.12) 0%, transparent 70%);

pointer-events: none;

}

.hero-badge {

display: inline-block;

font-family: 'JetBrains Mono', monospace;

font-size: 11px;

letter-spacing: 2px;

text-transform: uppercase;

color: var(--accent);

border: 1px solid rgba(249,115,22,0.35);

border-radius: 4px;

padding: 4px 12px;

margin-bottom: 20px;

background: rgba(249,115,22,0.08);

}

.hero h1 {

font-family: 'Syne', sans-serif;

font-size: clamp(26px, 5vw, 46px);

font-weight: 800;

line-height: 1.15;

color: #fff;

max-width: 760px;

margin: 0 auto 18px;

}

.hero h1 span { color: var(--accent); }

.hero-meta {

font-family: 'JetBrains Mono', monospace;

font-size: 12px;

color: var(--muted);

display: flex;

justify-content: center;

gap: 24px;

flex-wrap: wrap;

margin-top: 16px;

}

.hero-meta span::before { content: '// '; color: var(--accent); }

/* \u2500\u2500 LAYOUT \u2500\u2500 */

.container {

max-width: 820px;

margin: 0 auto;

padding: 0 24px 80px;

}

/* \u2500\u2500 INTRO \u2500\u2500 */

.intro-box {

margin: 40px 0 32px;

background: var(--surface);

border: 1px solid var(--border);

border-left: 4px solid var(--accent);

border-radius: 8px;

padding: 24px 28px;

font-size: 15px;

color: #94a3b8;

line-height: 1.8;

}

.intro-box strong { color: var(--accent); }

/* \u2500\u2500 SECTION HEADERS \u2500\u2500 */

.step-header {

display: flex;

align-items: center;

gap: 14px;

margin: 48px 0 20px;

}

.step-num {

font-family: 'JetBrains Mono', monospace;

font-size: 11px;

font-weight: 600;

color: var(--bg);

background: var(--accent);

border-radius: 4px;

padding: 4px 10px;

letter-spacing: 1px;

white-space: nowrap;

flex-shrink: 0;

}

.step-title {

font-family: 'Syne', sans-serif;

font-size: 20px;

font-weight: 700;

color: #fff;

}

/* \u2500\u2500 PROSE \u2500\u2500 */

p { color: #94a3b8; margin-bottom: 14px; }

p strong { color: var(--text); }

/* \u2500\u2500 CODE BLOCKS \u2500\u2500 */

.code-wrap {

background: var(--code-bg);

border: 1px solid var(--border);

border-top: 2px solid var(--accent);

border-radius: 8px;

overflow: hidden;

margin: 18px 0 24px;

}

.code-label {

background: #0f1117;

border-bottom: 1px solid var(--border);

padding: 8px 16px;

font-family: 'JetBrains Mono', monospace;

font-size: 11px;

color: var(--accent);

letter-spacing: 1px;

text-transform: uppercase;

display: flex;

justify-content: space-between;

align-items: center;

}

.code-label .dots { display: flex; gap: 6px; }

.code-label .dots span {

width: 8px; height: 8px; border-radius: 50%;

}

.dot-r { background: #ef4444; }

.dot-y { background: #eab308; }

.dot-g { background: #22c55e; }

pre {

padding: 20px 20px;

font-family: 'JetBrains Mono', monospace;

font-size: 13px;

line-height: 1.7;

color: #c9d1d9;

overflow-x: auto;

white-space: pre;

}

/* SQL syntax coloring */

.kw { color: #38bdf8; } /* keywords */

.fn { color: #a3e635; } /* functions / objects */

.str { color: #fb923c; } /* strings */

.cm { color: #4b5563; font-style: italic; } /* comments */

.bd { color: #e879f9; } /* bind vars */

.hn { color: #fbbf24; } /* hints */

/* \u2500\u2500 DIVIDER \u2500\u2500 */

.divider {

border: none;

border-top: 1px solid var(--border);

margin: 10px 0;

}

/* \u2500\u2500 TABLE \u2500\u2500 */

.tbl-wrap {

overflow-x: auto;

border-radius: 8px;

border: 1px solid var(--border);

margin: 18px 0 28px;

}

table {

width: 100%;

border-collapse: collapse;

font-size: 14px;

}

thead tr {

background: #0f1117;

border-bottom: 1px solid var(--accent);

}

th {

font-family: 'JetBrains Mono', monospace;

font-size: 11px;

letter-spacing: 1px;

text-transform: uppercase;

color: var(--accent);

padding: 12px 16px;

text-align: left;

}

td {

padding: 11px 16px;

color: #94a3b8;

border-bottom: 1px solid var(--border);

vertical-align: top;

}

td:first-child { color: var(--text); font-weight: 500; }

td strong { color: var(--accent2); }

tr:last-child td { border-bottom: none; }

tr:nth-child(even) { background: rgba(255,255,255,0.02); }

/* \u2500\u2500 CALLOUT \u2500\u2500 */

.callout {

background: rgba(56,189,248,0.07);

border: 1px solid rgba(56,189,248,0.25);

border-radius: 8px;

padding: 16px 20px;

margin: 20px 0;

font-size: 14px;

color: #7dd3fc;

display: flex;

gap: 12px;

align-items: flex-start;

}

.callout .icon { font-size: 18px; flex-shrink: 0; margin-top: 1px; }

.callout.warn {

background: rgba(249,115,22,0.07);

border-color: rgba(249,115,22,0.25);

color: #fdba74;

}

/* \u2500\u2500 PILL LIST \u2500\u2500 */

.pill-list { display: flex; flex-wrap: wrap; gap: 8px; margin: 14px 0 24px; }

.pill {

font-family: 'JetBrains Mono', monospace;

font-size: 12px;

color: var(--accent3);

background: rgba(163,230,53,0.08);

border: 1px solid rgba(163,230,53,0.2);

border-radius: 4px;

padding: 4px 12px;

}

/* \u2500\u2500 FOOTER \u2500\u2500 */

.footer {

margin-top: 60px;

padding: 32px 24px;

border-top: 1px solid var(--border);

text-align: center;

font-family: 'JetBrains Mono', monospace;

font-size: 12px;

color: var(--muted);

}

.footer a { color: var(--accent); text-decoration: none; }

.tags { display: flex; justify-content: center; flex-wrap: wrap; gap: 8px; margin-top: 16px; }

.tag {

font-size: 11px;

color: var(--muted);

border: 1px solid var(--border);

border-radius: 3px;

padding: 3px 8px;

}

</style>

</head>

<body>

<div class="hero-badge">Oracle EBS 12.2 \u00b7 DBA Playbook</div>

<h1>Investigating <span>BNE Upload Issues</span><br/>in Oracle EBS 12.2 \u2014 Step by Step</h1>

<span>Oracle Apps DBA</span>

<span>EBS 12.2.x \u00b7 Solaris</span>

<span>WebADI / BNE</span>

<span>April 2026</span>

</div>

<strong>Scenario:</strong> A user reports that their BNE (Browser-Native Extension / Web ADI) upload is failing or hanging in Oracle EBS 12.2. You have a DMS trace file open on the app server showing heavy SQL activity across <strong>FND_PROFILE_OPTIONS</strong>, <strong>WF_NOTIFICATIONS</strong>, and <strong>FND_GRANTS</strong>. This guide walks you through a structured investigation to identify the root cause \u2014 and the specific user and file involved.

</div>

<span class="pill">WebADI</span>

<span class="pill">FND_PROFILE</span>

<span class="pill">WF_NOTIFICATIONS</span>

<span class="pill">FND_GRANTS</span>

<span class="pill">v$session</span>

</div>

<div class="step-num">CONTEXT</div>

<div class="step-title">What the Trace File Tells Us</div>

</div>

<p>Before jumping into fixes, read the trace. The <code>dms_metrics88_i59.txt</code> trace filename encodes the <strong>OS process ID (88)</strong> and <strong>DB instance number (59)</strong>. The SQL inside reveals three areas of activity during the BNE upload lifecycle:</p>

<table>

<thead>

<tr><th>SQL Activity Seen</th><th>What It Means</th><th>Risk</th></tr>

</thead>

<tbody>

<tr><td>FND_PROFILE_OPTIONS with bind :1\u2013:8</td><td>Profile value lookups at multiple hierarchy levels (Site \u2192 Resp \u2192 User)</td><td>Cache miss = slow upload init</td></tr>

<tr><td>WF_NOTIFICATIONS with N1 and N6 index hints</td><td>Workflow notification checks \u2014 approval routing or audit</td><td>Stalled WF engine = upload hang</td></tr>

<tr><td>FND_GRANTS with instance_type = 'GLOBAL'</td><td>Security privilege check for the uploading user</td><td>Missing grant = silent failure</td></tr>

</tbody>

</table>

</div>

<div class="step-title">Identify the Uploading User from the Trace Session</div>

</div>

<p>The trace process ID gives us the starting point. Run this on the <strong>CORP DB</strong> to match the OS process to a database session and extract the Oracle username:</p>

<span>SQL \u2014 v$session lookup</span>

</div>

<pre><span class="cm">-- dms_metrics88_i59 \u2192 process 88, instance 59</span>

<span class="kw">SELECT</span> s.sid,

s.serial#,

s.username,

s.osuser,

s.module,

s.action,

s.client_info,

s.logon_time,

s.status

<span class="kw">FROM</span> <span class="fn">v$session</span> s

<span class="kw">WHERE</span> s.process = <span class="str">'88'</span> <span class="cm">-- OS process from trace filename</span>

<span class="kw">ORDER BY</span> s.logon_time <span class="kw">DESC</span>;</pre>

</div>

<div>The <strong>username</strong> column gives the Oracle DB user. Cross-check with <strong>fnd_user</strong> using <code>WHERE oracle_username = s.username</code> to get the EBS application username and employee name.</div>

</div>

<div class="step-title">Identify the File Being Uploaded</div>

</div>

<p>BNE stores upload metadata in <strong>BNE_INTERFACES_B</strong>. Query this joined with FND_USER to get the filename and which user triggered it:</p>

<span>SQL \u2014 BNE upload file lookup</span>

</div>

<pre><span class="kw">SELECT</span> bi.interface_code,

bi.upload_filename,

bi.upload_date,

bi.created_by,

fu.user_name,

bi.upload_data

<span class="kw">FROM</span> <span class="fn">bne_interfaces_b</span> bi

<span class="kw">JOIN</span> <span class="fn">fnd_user</span> fu <span class="kw">ON</span> fu.user_id = bi.created_by

<span class="kw">WHERE</span> bi.upload_date > <span class="fn">SYSDATE</span> - <span class="bd">1</span>/<span class="bd">24</span> <span class="cm">-- last 1 hour</span>

<span class="kw">ORDER BY</span> bi.upload_date <span class="kw">DESC</span>;</pre>

</div>

<div class="step-title">Check FND_LOG for BNE Error Messages</div>

</div>

<p>EBS 12.2 logs BNE framework errors into <strong>FND_LOG_MESSAGES</strong>. This is the fastest way to see the exact failure reason \u2014 Java exceptions, profile errors, or security violations:</p>

<span>SQL \u2014 FND_LOG_MESSAGES for BNE</span>

</div>

<pre><span class="kw">SELECT</span> fl.log_sequence,

fl.timestamp,

fl.encoded_message

<span class="kw">FROM</span> <span class="fn">fnd_log_messages</span> fl

<span class="kw">WHERE</span> fl.module <span class="kw">LIKE</span> <span class="str">'%bne%'</span>

<span class="kw">AND</span> fl.timestamp > <span class="fn">SYSDATE</span> - <span class="bd">1</span>/<span class="bd">24</span>

<span class="kw">ORDER BY</span> fl.log_sequence <span class="kw">DESC</span>

<span class="fn">FETCH FIRST</span> <span class="bd">50</span> <span class="fn">ROWS ONLY</span>;</pre>

</div>

<div class="step-title">Check if Workflow Engine is the Culprit</div>

</div>

<p>The trace showed two WF_NOTIFICATIONS queries with different index hints (<code>N1</code> vs <code>N6</code>). If the Workflow background engine is stalled, BNE uploads that trigger notifications will hang. Check for open notifications and FNDWFBG status:</p>

<span>SQL \u2014 Open WF Notifications check</span>

</div>

<pre><span class="kw">SELECT</span> wn.notification_id,

wn.status,

wn.recipient_role,

wn.message_name,

wn.begin_date,

wn.end_date

<span class="kw">FROM</span> <span class="fn">wf_notifications</span> wn

<span class="kw">WHERE</span> wn.begin_date > <span class="fn">SYSDATE</span> - <span class="bd">1</span>

<span class="kw">AND</span> wn.status = <span class="str">'OPEN'</span>

<span class="kw">ORDER BY</span> wn.begin_date <span class="kw">DESC</span>;</pre>

</div>

<span>SQL \u2014 Check FNDWFBG concurrent process</span>

</div>

<pre><span class="kw">SELECT</span> fcr.request_id,

fcr.phase_code,

fcr.status_code,

fcr.actual_start_date,

fcr.actual_completion_date

<span class="kw">FROM</span> <span class="fn">fnd_concurrent_requests</span> fcr

<span class="kw">JOIN</span> <span class="fn">fnd_concurrent_programs_vl</span> fcp

<span class="kw">ON</span> fcp.concurrent_program_id = fcr.concurrent_program_id

<span class="kw">WHERE</span> fcp.concurrent_program_name = <span class="str">'FNDWFBG'</span>

<span class="kw">AND</span> fcr.actual_start_date > <span class="fn">SYSDATE</span> - <span class="bd">1</span>

<span class="kw">ORDER BY</span> fcr.actual_start_date <span class="kw">DESC</span>;</pre>

</div>

<div class="step-title">Verify User's BNE Security Grants</div>

</div>

<p>The trace shows a <strong>FND_GRANTS</strong> query with <code>instance_type = 'GLOBAL'</code> and <code>object_id = -1</code>. This is the security check for BNE access. If the user lacks the correct grant, the upload fails silently. Verify:</p>

<span>SQL \u2014 FND_GRANTS BNE security check</span>

</div>

<pre><span class="kw">SELECT</span> fg.grantee_key,

fg.menu_id,

fg.start_date,

fg.end_date,

fg.instance_type,

fm.menu_name

<span class="kw">FROM</span> <span class="fn">fnd_grants</span> fg

<span class="kw">JOIN</span> <span class="fn">fnd_menus</span> fm <span class="kw">ON</span> fm.menu_id = fg.menu_id

<span class="kw">WHERE</span> fg.instance_type = <span class="str">'GLOBAL'</span>

<span class="kw">AND</span> fg.object_id = <span class="bd">-1</span>

<span class="kw">AND</span> fg.grantee_key = <span class="str">'<USERNAME>'</span> <span class="cm">-- replace with EBS username</span>

<span class="kw">AND</span> (fg.end_date <span class="kw">IS NULL</span> <span class="kw">OR</span> fg.end_date > <span class="fn">SYSDATE</span>);</pre>

</div>

<div>If no rows returned for the user \u2014 or the menu_name does not include a BNE-related menu \u2014 the responsibility needs to be reapplied or FNDLOAD re-run to restore the grant.</div>

</div>

<div class="step-title">Check BNE Profile Options</div>

</div>

<p>BNE behaviour is controlled by specific profile options. Verify these are set correctly at the Site or Responsibility level:</p>

<span>SQL \u2014 BNE critical profile options</span>

</div>

<pre><span class="kw">SELECT</span> fpov.profile_option_name,

fpov.profile_option_value,

<span class="kw">DECODE</span>(fpov.level_id,

<span class="bd">10001</span>, <span class="str">'SITE'</span>,

<span class="bd">10002</span>, <span class="str">'APPLICATION'</span>,

<span class="bd">10003</span>, <span class="str">'RESPONSIBILITY'</span>,

<span class="bd">10004</span>, <span class="str">'USER'</span>) level_name

<span class="kw">FROM</span> <span class="fn">fnd_profile_options</span> fpo

<span class="kw">JOIN</span> <span class="fn">fnd_profile_option_values</span> fpov

<span class="kw">ON</span> fpov.profile_option_id = fpo.profile_option_id

<span class="kw">WHERE</span> fpo.profile_option_name <span class="kw">IN</span> (

<span class="str">'BNE_ALLOW_NO_SECURITY_RULE'</span>,

<span class="str">'BNE_MAPPING_SCHEMA'</span>,

<span class="str">'FND_CORPORATE_BRANDING_IMAGE'</span>

)

<span class="kw">AND</span> fpov.level_value_application_id <span class="kw">IS NOT NULL</span>

<span class="kw">ORDER BY</span> <span class="bd">1</span>, fpov.level_id;</pre>

</div>

<div class="step-title">Check for Invalid BNE Objects</div>

</div>

<p>Any invalid BNE package or view in the DB will cause upload failures without a clear user-visible error. Always run this early:</p>

<span>SQL \u2014 Invalid BNE DB objects</span>

</div>

<pre><span class="kw">SELECT</span> object_name,

object_type,

status,

last_ddl_time

<span class="kw">FROM</span> <span class="fn">dba_objects</span>

<span class="kw">WHERE</span> object_name <span class="kw">LIKE</span> <span class="str">'BNE%'</span>

<span class="kw">AND</span> status = <span class="str">'INVALID'</span>

<span class="kw">ORDER BY</span> object_type, object_name;</pre>

</div>

<p>If invalids are found, recompile:</p>

<span>SQL \u2014 Recompile invalid BNE objects</span>

</div>

<pre><span class="kw">BEGIN</span>

<span class="fn">dbms_utility.compile_schema</span>(

schema => <span class="str">'APPS'</span>,

compile_all => <span class="kw">FALSE</span>

);

<span class="kw">END</span>;

/</pre>

</div>

<div class="step-title">Check

Wednesday, April 1, 2026

webadi

How to Check WebADI Upload Progress in Oracle EBS 12.2

Understanding the Flow

When a user uploads via WebADI, it goes through these layers in order:

BNE_USER_SESSIONS → BNE_UPLOADED_DATA → BNE_UPLOADS → FND_CONCURRENT_REQUESTS → V$SESSION

1. Check If a WebADI Session Is Active

SELECT fus.user_session_id,

fu.user_name,

fus.creation_date,

fus.last_update_date,

fus.expired

FROM bne_user_sessions fus,

fnd_user fu

WHERE fus.user_id = fu.user_id

AND fus.expired = 'N'

AND fus.creation_date > SYSDATE - 1/24

ORDER BY fus.creation_date DESC;

2. What File Was Uploaded and by Whom

SELECT buc.upload_id,

fu.user_name,

buc.integrator_code,

buc.upload_file_name,

buc.status,

buc.creation_date,

ROUND((SYSDATE - buc.creation_date)*24*60,2) elapsed_mins

FROM bne_uploaded_data buc,

fnd_user fu

WHERE buc.user_id = fu.user_id

AND buc.creation_date > SYSDATE - 1/24

ORDER BY buc.creation_date DESC;

3. Row-Level Progress — How Many Rows Processed

SELECT bu.upload_id,

fu.user_name,

bu.integrator_code,

bu.status,

bu.num_rows total_rows,

bu.num_rows_processed processed,

bu.num_rows_errored errored,

bu.num_rows - bu.num_rows_processed remaining,

ROUND((bu.num_rows_processed /

NULLIF(bu.num_rows,0))*100, 1) pct_complete,

bu.creation_date started_at,

bu.last_update_date last_activity,

ROUND((SYSDATE - bu.creation_date)*24*60, 2) elapsed_mins

FROM bne_uploads bu,

fnd_user fu

WHERE bu.user_id = fu.user_id

AND bu.creation_date > SYSDATE - 2/24

AND bu.status IN ('U','P','I')

ORDER BY bu.creation_date DESC;

Status codes:

Code

Meaning

Upload in progress

Processing

Imported to interface table

Successfully completed

Errored

4. Concurrent Request Triggered by WebADI

SELECT fcr.request_id,

fu.user_name,

fcp.user_concurrent_program_name program_name,

fcr.phase_code,

fcr.status_code,

fcr.actual_start_date,

ROUND((SYSDATE - fcr.actual_start_date)*24*60,2) running_mins

FROM fnd_concurrent_requests fcr,

fnd_concurrent_programs_tl fcp,

fnd_user fu

WHERE fcr.concurrent_program_id = fcp.concurrent_program_id

AND fcp.language = 'US'

AND fcr.requested_by = fu.user_id

AND fcr.phase_code = 'R'

AND fcr.actual_start_date > SYSDATE - 1/24

AND (fcp.user_concurrent_program_name LIKE '%Web ADI%'

OR fcp.user_concurrent_program_name LIKE '%WEBADI%'

OR fcr.argument_text LIKE '%BNE%')

ORDER BY fcr.actual_start_date DESC;

5. What Is the DB Session Doing

SELECT s.sid,

s.serial#,

s.username,

s.module,

s.action,

s.status,

s.event,

s.seconds_in_wait,

sq.sql_text

FROM v$session s,

v$sql sq

WHERE s.sql_id = sq.sql_id(+)

AND s.username IS NOT NULL

AND (s.module LIKE '%BNE%'

OR s.program LIKE '%FNDWRR%'

OR s.action LIKE '%UPLOAD%')

AND s.status = 'ACTIVE'

ORDER BY s.logon_time DESC;

6. Check Upload Errors

SELECT bue.upload_id,

bue.row_num,

bue.error_code,

bue.error_text,

bue.creation_date

FROM bne_upload_errors bue

WHERE bue.upload_id IN (

SELECT upload_id FROM bne_uploads

WHERE creation_date > SYSDATE - 2/24

)

ORDER BY bue.creation_date DESC, bue.row_num;

Quick Reference

What you need

Query target

Session active?

BNE_USER_SESSIONS

File details

BNE_UPLOADED_DATA

Row progress / % complete

BNE_UPLOADS

Concurrent request

FND_CONCURRENT_REQUESTS

DB session / SQL running

VSESSION + VSQL

Error details

BNE_UPLOAD_ERRORS

Note: If status is stuck at I (Imported), the rows reached the interface table but the import program hasn't run — check concurrent manager capacity. If stuck at U or P with no LAST_UPDATE_DATE movement, check v$session for blocking events.

Tuesday, March 31, 2026

queryy

Production-Safe Next Steps ONLY

Do NOT gather stats or invalidate plans directly. Instead:

-- Step 1: Get actual runtime stats (READ ONLY, zero risk)

SELECT sql_id, child_number, elapsed_time/1000000 elapsed_sec,

buffer_gets, disk_reads, executions,

buffer_gets/DECODE(executions,0,1,executions) bgets_per_exec

FROM v$sql

WHERE sql_id = 'd8txf935msbx5';

-- Step 2: Check last stats gather on these tables (READ ONLY)

SELECT table_name, last_analyzed, num_rows, stale_stats

FROM dba_tab_statistics

WHERE table_name IN ('MSC_TP_ID_LID','MSC_ST_TRADING_PARTNER_SITES',

'MSC_TRADING_PARTNER_SITES')

AND owner = 'MSC';

-- Step 3: Check index column order (READ ONLY)

SELECT column_name, column_position

FROM dba_ind_columns

WHERE index_name = 'MSC_TP_ID_LID_U1'

ORDER BY column_position;

Thursday, March 26, 2026

view

Oracle Apps DBA

Fix Slow View Execution Plan in Oracle EBS 12.2 / 19c DB
— Step-by-Step Guide

Oracle EBS 12.2.xDatabase 19cSQL Tuning & SPMPerformance

DBMS_XPLANDBMS_SPMSQL Plan BaselineDBMS_STATSDBMS_SHARED_POOL

Scenario: The view CHPOZF_IC_UI_SEARCH_V runs in ~4 seconds on P04 and PRD, but hangs on P05. The fix involves identifying the bad execution plan, capturing the good plan from the healthy instance, and pinning it on P05 using SQL Plan Baselines (SPM) — Oracle's preferred plan stabilization mechanism in 19c.

Phase 1

Identify the SQL on P05 (Slow Instance)

Run the following queries on P05 as apps or system.

Step 1.1 — Find SQL_ID of the slow view query

SQL

col sql_id           for a15
col plan_hash_value  for 9999999999
col avg_sec          for 999.99
col last_active      for a20

SELECT sql_id,
       child_number,
       plan_hash_value,
       executions,
       ROUND(elapsed_time/GREATEST(executions,1)/1e6, 2) avg_sec,
       TO_CHAR(last_active_time,'YYYY-MM-DD HH24:MI:SS') last_active
FROM   v$sql
WHERE  UPPER(sql_text) LIKE '%CHPOZF_IC_UI_SEARCH_V%'
  AND  sql_text NOT LIKE '%v$sql%'
ORDER  BY last_active_time DESC;

Step 1.2 — Get full execution plan with actuals

SQL

-- Replace &sql_id and &child_number with values from Step 1.1
SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    '&sql_id',
    &child_number,
    'ALLSTATS LAST +PEEKED_BINDS +OUTLINE'
  )
);

⚠ Save this output! You need the Outline Data section for the SQL Profile in Phase 5.

Step 1.3 — Note the bad plan_hash_value

SQL

SELECT plan_hash_value FROM v$sql
WHERE  sql_id = '&sql_id' AND child_number = &child_number;

Phase 2

Capture the Good Plan from P04 / PRD

Step 2.1 — Find SQL_ID on P04/PRD

SQL

SELECT sql_id,
       child_number,
       plan_hash_value,
       ROUND(elapsed_time/GREATEST(executions,1)/1e6,2) avg_sec
FROM   v$sql
WHERE  UPPER(sql_text) LIKE '%CHPOZF_IC_UI_SEARCH_V%'
  AND  sql_text NOT LIKE '%v$sql%'
ORDER  BY elapsed_time/GREATEST(executions,1);

Step 2.2 — Capture good plan with OUTLINE

SQL

SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    '&good_sql_id',
    &good_child_number,
    'ALLSTATS LAST +PEEKED_BINDS +OUTLINE'
  )
);

Copy the Outline Data hints block from the output — you will use these hints in Phase 5 for SQL Profile creation.

Phase 3

Root Cause Diagnosis on P05

Step 3.1 — Compare optimizer parameters (run on BOTH instances)

SQL

SELECT name, value
FROM   v$parameter
WHERE  name IN (
  'optimizer_mode',
  'optimizer_features_enable',
  'db_file_multiblock_read_count',
  'optimizer_adaptive_plans',
  'optimizer_adaptive_statistics',
  '_optimizer_use_feedback',
  'statistics_level'
)
ORDER BY name;

Step 3.2 — Check stats on base tables of the view

SQL

-- Find base tables first
SELECT referenced_name AS table_name
FROM   dba_dependencies
WHERE  name            = 'CHPOZF_IC_UI_SEARCH_V'
  AND  type            = 'VIEW'
  AND  referenced_type = 'TABLE';

-- Check stats freshness
SELECT table_name,
       num_rows,
       blocks,
       last_analyzed,
       stattype_locked,
       stale_stats
FROM   dba_tab_statistics
WHERE  table_name IN (
  SELECT referenced_name FROM dba_dependencies
  WHERE  name = 'CHPOZF_IC_UI_SEARCH_V'
    AND  type = 'VIEW'
    AND  referenced_type = 'TABLE'
)
ORDER BY last_analyzed NULLS FIRST;

Step 3.3 — Check indexes on P05

SQL

SELECT i.table_name, i.index_name, i.status, i.visibility,
       i.last_analyzed, i.num_rows
FROM   dba_indexes i
WHERE  i.table_name IN (
  SELECT referenced_name FROM dba_dependencies
  WHERE  name = 'CHPOZF_IC_UI_SEARCH_V'
    AND  type = 'VIEW'
    AND  referenced_type = 'TABLE'
)
ORDER BY i.table_name, i.index_name;

Step 3.4 — Check adaptive plan / cardinality feedback (19c specific)

SQL

SELECT sql_id, child_number, is_resolved_adaptive_plan,
       is_reoptimizable, use_feedback_stats
FROM   v$sql_shared_cursor
WHERE  sql_id = '&p05_sql_id';

Root Cause	P05 Symptom	Fix
Stale / missing stats	Full table scans, bad cardinality estimates	Phase 4 — Gather stats
Different `optimizer_mode`	Different plan shape vs P04	Align parameter in P05
Missing index on P05	Full scan where P04 uses index range scan	Rebuild index
Bind variable peeking mismatch	Different `plan_hash_value` per execution	Phase 5 — Pin via SPM
19c Adaptive Plans interference	`is_reoptimizable = Y`	Disable feedback or pin plan

Phase 4

Fix Option A — Gather Fresh Stats (Try First)

Step 4.1 — Gather stats on base tables

SQL

-- Replace 'APPS' / 'YOUR_BASE_TABLE' with actual schema and table name
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'APPS',
    tabname          => 'YOUR_BASE_TABLE',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    degree           => 4,
    cascade          => TRUE,
    no_invalidate    => FALSE
  );
END;
/

Step 4.2 — Flush the specific cursor (preferred over full shared pool flush)

SQL

DECLARE
  l_address  RAW(8);
  l_hash_val NUMBER;
BEGIN
  SELECT address, hash_value
  INTO   l_address, l_hash_val
  FROM   v$sqlarea
  WHERE  sql_id = '&p05_sql_id';

  DBMS_SHARED_POOL.PURGE(
    l_address || ',' || l_hash_val, 'C'
  );
END;
/

Re-run the view query and check avg execution time. If still slow — proceed to Phase 5.

Phase 5

Fix Option B — Pin Good Plan via SQL Plan Baseline (SPM)

Step 5A — Load good plan into SPM on P04/PRD

Run as SYS or user with ADMINISTER SQL MANAGEMENT OBJECT privilege.

SQL — On P04/PRD

DECLARE
  l_cnt PLS_INTEGER;
BEGIN
  l_cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id          => '&good_sql_id',
    plan_hash_value => &good_plan_hash_value
  );
  DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_cnt);
END;
/

Step 5B — Verify baseline created on P04/PRD

SQL — On P04/PRD

col sql_handle  for a30
col plan_name   for a35
col origin      for a20

SELECT sql_handle, plan_name, enabled, accepted, fixed, origin,
       TO_CHAR(created,'YYYY-MM-DD HH24:MI') created
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%CHPOZF_IC_UI_SEARCH_V%';

Step 5C — Create staging table on P04/PRD

SQL — On P04/PRD (as SYS)

BEGIN
  DBMS_SPM.CREATE_STGTAB_BASELINE(
    table_name  => 'SPM_XFER_STAGE',
    table_owner => 'SYSTEM',
    db_version  => '19.1.0.0.0'
  );
END;
/

Step 5D — Pack the baseline into staging table

SQL — On P04/PRD

DECLARE
  l_cnt PLS_INTEGER;
BEGIN
  l_cnt := DBMS_SPM.PACK_STGTAB_BASELINE(
    table_name  => 'SPM_XFER_STAGE',
    table_owner => 'SYSTEM',
    sql_handle  => '&sql_handle_from_5B'
  );
  DBMS_OUTPUT.PUT_LINE('Plans packed: ' || l_cnt);
END;
/

Step 5E — Export staging table via DataPump (OS level on P04/PRD)

Bash — OS

expdp system/password \
  tables=SYSTEM.SPM_XFER_STAGE \
  directory=DATA_PUMP_DIR \
  dumpfile=spm_xfer_p04.dmp \
  logfile=spm_xfer_p04.log

Step 5F — Transfer dump file to P05 server

Bash — OS

scp spm_xfer_p04.dmp oracle@p05_host:/u01/app/oracle/admin/P05/dpdump/

Step 5G — Import on P05

Bash — OS on P05

impdp system/password \
  tables=SYSTEM.SPM_XFER_STAGE \
  directory=DATA_PUMP_DIR \
  dumpfile=spm_xfer_p04.dmp \
  logfile=spm_xfer_p05_import.log \
  remap_schema=SYSTEM:SYSTEM

Step 5H — Unpack baseline into P05 SPM repository

SQL — On P05 (as SYS)

DECLARE
  l_cnt PLS_INTEGER;
BEGIN
  l_cnt := DBMS_SPM.UNPACK_STGTAB_BASELINE(
    table_name  => 'SPM_XFER_STAGE',
    table_owner => 'SYSTEM'
  );
  DBMS_OUTPUT.PUT_LINE('Plans unpacked: ' || l_cnt);
END;
/

Step 5I — Verify and mark baseline as FIXED on P05

SQL — On P05

-- Confirm it's accepted
SELECT sql_handle, plan_name, enabled, accepted, fixed
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%CHPOZF_IC_UI_SEARCH_V%';

-- Mark as FIXED so optimizer always uses this plan
DECLARE
  l_cnt PLS_INTEGER;
BEGIN
  l_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle      => '&sql_handle',
    plan_name       => '&plan_name',
    attribute_name  => 'FIXED',
    attribute_value => 'YES'
  );
END;
/

Phase 6

Validate the Fix on P05

SQL — On P05

-- Step 1: Purge old cursor
DECLARE
  l_address  RAW(8);
  l_hash_val NUMBER;
BEGIN
  SELECT address, hash_value
  INTO   l_address, l_hash_val
  FROM   v$sqlarea
  WHERE  sql_id = '&p05_sql_id';
  DBMS_SHARED_POOL.PURGE(l_address||','||l_hash_val,'C');
END;
/

-- Step 2: Re-run view query, then check new plan
SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    '&new_sql_id', NULL,
    'ALLSTATS LAST +PEEKED_BINDS'
  )
);

-- SUCCESS: Look for this line in output:
-- "SQL plan baseline SQL_PLAN_xxx used for this statement"

✓ Confirmation: If the plan output contains SQL plan baseline SQL_PLAN_xxx used for this statement — the fix is successful.

Summary

Decision Flow — Which Fix to Apply

P05 view query is slow? │ ├── Stats stale/missing? ──YES──► Gather stats (Phase 4) → retest │ ├── optimizer params differ? ──YES──► Align parameter with P04 │ or set at session level │ ├── Index missing on P05? ──YES──► Rebuild index → gather stats │ └── Plan just wrong? ──────────► SPM Baseline transfer (Phase 5)

Oracle Apps DBA stuff

Wednesday, April 8, 2026

web

Wednesday, April 1, 2026

webadi

Tuesday, March 31, 2026

queryy

Thursday, March 26, 2026

view

Fix Slow View Execution Plan in Oracle EBS 12.2 / 19c DB
— Step-by-Step Guide

Identify the SQL on P05 (Slow Instance)

Capture the Good Plan from P04 / PRD

Root Cause Diagnosis on P05

Fix Option A — Gather Fresh Stats (Try First)

Fix Option B — Pin Good Plan via SQL Plan Baseline (SPM)

Validate the Fix on P05

Decision Flow — Which Fix to Apply

Search This Blog

Blog Archive

Wednesday, April 8, 2026

web

Wednesday, April 1, 2026

webadi

Tuesday, March 31, 2026

queryy

Thursday, March 26, 2026

view

Fix Slow View Execution Plan in Oracle EBS 12.2 / 19c DB— Step-by-Step Guide

Identify the SQL on P05 (Slow Instance)

Capture the Good Plan from P04 / PRD

Root Cause Diagnosis on P05

Fix Option A — Gather Fresh Stats (Try First)

Fix Option B — Pin Good Plan via SQL Plan Baseline (SPM)

Validate the Fix on P05

Decision Flow — Which Fix to Apply

Fix Slow View Execution Plan in Oracle EBS 12.2 / 19c DB
— Step-by-Step Guide