Explain Performance Tuning Custom PL/SQL and SQL in EBS in Oracle Applications

22 / 60

PL/SQLadvanced

Performance Tuning Custom PL/SQL and SQL in EBS

Explain the standard toolkit and methodology for diagnosing and fixing slow custom code in EBS

Performance tuning in an EBS context follows a fairly standard Oracle DBA/developer methodology, but with EBS-specific tools layered on top. FND_TRACE (or setting the profile Initialization SQL Statement - Custom / enabling trace via the Concurrent Program's 'Enable Trace' checkbox) captures a raw SQL trace file for a concurrent request, which is then formatted with TKPROF to reveal exactly which SQL statements consumed the most time, how many times they executed, and their execution plans.

Performance tuning without tracing is like trying to fix a car by guessing which part is broken; tracing and TKPROF are like plugging in an actual diagnostic scanner that tells you precisely which component (SQL statement) is consuming the most 'fuel' (time/resources) before you start replacing parts.

Key Concepts

Common EBS-specific performance culprits include: missing or stale statistics on custom tables (EBS uses FND_STATS rather than plain DBMS_STATS to gather statistics in a way that's aware of EBS's multi-org and seeded-data patterns), not filtering on indexed columns like org_id early, inefficient use of flexfield/DFF attribute columns in WHERE clauses (which are rarely indexed), and row-by-row processing that should have used BULK COLLECT/FORALL.

FND_STATSDBMS_STATSorg_id

For OAF/ADF-based self-service pages, performance issues often stem from View Object query inefficiency (unnecessary columns, missing bind variables causing hard parses) or excessive round trips; for Forms, it's often triggers firing excessive validation queries. Understanding AWR/ASH reports, V$SQL, and SQL Trace at the database tier rounds out a consultant's diagnostic toolkit.

View ObjectAWR/ASH reportsSQL TraceV$SQL

Interviewers frequently present a scenario ('a custom concurrent program that used to run in 10 minutes now takes 3 hours') and expect the candidate to walk through a structured methodology: reproduce with trace enabled, run TKPROF, identify the worst SQL, check its explain plan and statistics, and only then propose a fix (index, rewrite, bulk processing, or stats refresh) — rather than jumping straight to guesswork.