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

U

Upload in progress

P

Processing

I

Imported to interface table

S

Successfully completed

E

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.

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;

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 EBS 12.219c DatabaseSQL TuningSPMApps DBADBMS_STATSPerformance

Oracle Apps DBA BlogEBS 12.2 / 19c Field Notes