The Call Nobody Wants at Month-End Close
It's day 2 of period close. Finance is running Create Accounting in Oracle E-Business Suite (the XLA — Subledger Accounting engine) to post thousands of transactions from AP, AR, and Costing into GL. It has run fine for months. Tonight, it hangs. CPU on the database node is pegged, the concurrent request has been "Running" for two hours instead of eleven minutes, and Finance is asking the age-old question every DBA dreads: "What did you change?"
Nobody changed anything. That's the trap. Nothing changed — except the data and the bind variable values that got peeked the first time this cursor was hard-parsed today. That's usually enough.
The Symptom: A Good Cursor Gone Bad:
The concurrent manager log for Create Accounting showed the request spinning. top/OEM showed one session burning CPU. First move — find out what SQL that session is actually running.
SELECT s.sid, s.serial#, s.username, s.module, s.action,
s.sql_id, s.sql_child_number, s.event, s.seconds_in_wait
FROM gv$session s
WHERE s.status = 'ACTIVE'
AND s.username = 'APPS'
AND UPPER(s.module) LIKE '%ACCOUNTING%'
ORDER BY s.seconds_in_wait DESC;
That surfaced a sql_id — let's call it cpnhz9tgqy81s — burning CPU with almost no waits (a classic sign of a bad plan, not a locking or I/O problem). CPU-bound + long-running + "used to be fast" is the fingerprint of a plan regression, not a resource shortage.
DBA's gut-check questions:
- Did this SQL_ID always run slow, or did it change? → Check DBA_HIST_SQLSTAT / AWR for the SQL's historical elapsed time trend.
- Does it have more than one plan in the plan history? → Check DBA_HIST_SQL_PLAN and V$SQL_PLAN.
- Is this SQL bind-sensitive? → Peeking-driven plans differ by which value got bound on the first hard parse of the day.
The result told the whole story in one glance: for weeks, plan_hash_value = 1448138561 ran in 6 seconds on average. Overnight, a new plan_hash_value = cpnhz9tgqy81s appeared and was now averaging 380 seconds — a 60x regression, same SQL_ID, same text, different plan.
Confirming it's a plan problem, not a stats/data problem:
The good plan drove off a selective index on XLA_AE_LINES / XLA_AE_HEADERS using the peeked bind for a specific LEDGER_ID/APPLICATION_ID combination. The bad plan had flipped to a full table scan + hash join on a multi-million-row accounting events table, because a different, far less selective bind value got peeked at the hard parse that triggered the new child cursor.
This is bind peeking doing exactly what it's designed to do — optimize for the first value it sees — and exactly why it can betray you on skewed data, which subledger accounting tables (XLA_%) are notorious for: a handful of LEDGER_IDs or EVENT_TYPE_CODEs carry the overwhelming majority of rows.
The Diagnosis: Why Bind Peeking Is the Culprit:
a. Confirm multiple child cursors exist for the same SQL_ID:
SELECT sql_id, child_number, plan_hash_value, is_bind_sensitive,
is_bind_aware, is_shareable, executions
FROM v$sql
WHERE sql_id = 'cpnhz9tgqy81s'
ORDER BY child_number;
Key column to watch: IS_BIND_SENSITIVE = 'Y'. This confirms the Optimizer itself has flagged the predicate(s) on this cursor as data-skewed enough that different bind values could produce different plans. That's your smoking gun.
b. Read the actual peeked values:
What bind values were peeked for the good vs. the bad child?
SELECT sql_id, child_number, name, position, datatype_string, value_string
FROM gv$sql_bind_capture
WHERE sql_id = 'cpnhz9tgqy81s'
ORDER BY child_number, position;
c. Confirm the cardinality misestimate that drives the flip:
Where does estimated vs. actual rows diverge?
SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR('cpnhz9tgqy81s', NULL, 'ALLSTATS LAST')
);
Look at E-Rows vs A-Rows per plan line. A 100x+ divergence at the join/filter step confirms the optimizer is estimating on a bind value that doesn't represent the actual skew of the run.
Why "just gather stats" or "flush the cursor" isn't the fix
Both were tried first, as any team would:
Flushing the cursor (DBMS_SHARED_POOL.PURGE) only resets the problem — the next hard parse might peek a different bad value tomorrow.
Re-gathering stats / histograms on XLA_AE_LINES helped a little but didn't stop the flip, because the underlying issue is architectural: this SQL, inside a seeded Oracle Applications package (XLA subledger accounting engine, not custom code), cannot be edited, and bind peeking will keep rolling the dice on every fresh hard parse driven by concurrent manager sessions, cursor invalidations, or stats jobs.
What the team actually needed was to force the optimizer to stop peeking on this one cursor and instead use bind-variable-independent (generic) cardinality estimates, consistently, every time — without changing a single byte of Oracle-seeded PL/SQL. That's exactly the job of _optim_peek_user_binds combined with SQL Patch.
The Fix: SQL Patch to the Rescue:
Why SQL Patch, and not a hint in code, a Baseline, or a Profile?
DBMS_SQLDIAG.CREATE_SQL_PATCH lets a DBA attach a hint (or an optimizer parameter override, via OPT_PARAM) to a specific SQL_ID without any code change — ideal for a locked-down, Oracle-owned application like EBS's XLA engine, where you are not allowed to modify seeded PL/SQL.
Choosing the hint: OPT_PARAM('_optim_peek_user_binds' 'false')
This is the key decision the senior DBA made, and it's worth explaining plainly to a junior DBA:
_optim_peek_user_binds = TRUE (the default) tells the optimizer: "On hard parse, look at the actual bind values sent, and optimize the plan for those specific values."
Setting it to FALSE for this one cursor only tells the optimizer: "Ignore the specific values you were just handed. Use bind-variable-independent, generic (unpeeked) selectivity estimates instead — the same, stable plan every single time, regardless of which LEDGER_ID or EVENT_TYPE_CODE happens to hard-parse first."
This trades "theoretically optimal for one specific value" for "consistently good, no more surprises" — exactly the trade-off you want for a shared, highly-executed, skewed-data cursor inside a batch program like Create Accounting, where plan stability matters far more than shaving the last millisecond off one lucky value.
This is functionally the same idea Nigel Bayliss's post demonstrates with IGNORE_OPTIM_EMBEDDED_HINTS (removing embedded hint influence) — here we're removing bind-peeking influence instead, via OPT_PARAM, which CREATE_SQL_PATCH accepts exactly like any other hint text.
Applying the patch:
SQL> DECLARE
2 v_patch_name VARCHAR2(30);
3 BEGIN
4 v_patch_name := DBMS_SQLDIAG.CREATE_SQL_PATCH(
5 sql_id => 'cpnhz9tgqy81s',
6 hint_text => 'OPT_PARAM(''_optim_peek_user_binds'' ''false'')',
7 name => 'XLA_FORCE_GENERIC_PLAN'
8 );
9 END;
10 /
PL/SQL procedure successfully completed.
sql_id must be the exact SQL_ID of the offending statement, not the concurrent program's outer SQL_ID or a parent call.
hint_text uses doubled single quotes (''_optim_peek_user_binds'') because we're inside a PL/SQL string literal — this is a very common syntax trip-up.
name is your own label. Make it descriptive and traceable (XLA_FORCE_GENERIC_PLAN tells the next DBA exactly why it exists) — you will thank yourself in six months during an audit or an upgrade.
No downtime, no code deployment, no adpatch/adop cycle — this is a live, additive change at the database layer that Oracle Support will recognize and accept as a supported troubleshooting technique for EBS environments (this is a standard, documented approach for XLA/GL performance issues raised via SR).
Post-Validation: Trust, But Verify:
A SQL Patch that silently fails to attach, or attaches but doesn't get used, is worse than doing nothing — you'll believe the problem is fixed when it isn't. Walk through every one of these checks.
SELECT *
FROM dba_sql_patches where status='ENABLED';
If STATUS is not ENABLED, the patch was created but is dormant — nothing will change. (DBMS_SQLDIAG.ALTER_SQL_PATCH can toggle this if it was disabled.)
Force a fresh hard parse so the new cursor picks up the patch:
SELECT address, hash_value
FROM v$sqlarea
WHERE sql_id = 'cpnhz9tgqy81s';
Confirm the patch is actually being used in the plan:
SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR('cpnhz9tgqy81s', NULL, 'OUTLINE BASIC NOTE')
);
Look for this at the bottom of the output — it's the definitive proof:
Confirm plan stability across multiple, differently-skewed executions
sql-- Run Create Accounting (or the isolated SQL) for a few different LEDGER_IDs / batches
-- and confirm the SAME plan_hash_value every time
SELECT sql_id, plan_hash_value, executions, elapsed_time/1e6/executions avg_sec,
buffer_gets/executions avg_buffer_gets
FROM v$sql
WHERE sql_id = 'cpnhz9tgqy81s'
ORDER BY last_active_time DESC;
You want to see one plan_hash_value now, consistently, regardless of which ledger or event type ran first.
Confirm plan stability across multiple, differently-skewed executions
This is the step junior DBAs most often skip, and it's the one that actually proves the fix works:
sql-- Run Create Accounting (or the isolated SQL) for a few different LEDGER_IDs / batches
-- and confirm the SAME plan_hash_value every time
SELECT
fcr.request_id,
fcp.concurrent_program_name AS program_short_name,
fcr.actual_start_date,
fcr.actual_completion_date,
ROUND((fcr.actual_completion_date - fcr.actual_start_date) * 24 * 60, 1) AS run_minutes
FROM
apps.fnd_concurrent_requests fcr
JOIN
apps.fnd_concurrent_programs fcp
ON fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
WHERE
fcp.concurrent_program_name = 'XLAACCPB'
AND fcr.actual_start_date > SYSDATE - 3
ORDER BY
fcr.actual_start_date DESC;
No comments:
Post a Comment