Oracle Database Performance Tuning for EBS

Why Database Performance Matters for EBS

Oracle EBS is fundamentally a database application. Every transaction, every report, every concurrent program ultimately depends on the performance of the underlying Oracle database. When the database is healthy, EBS runs smoothly. When it’s not, every aspect of the application suffers—from user response times to batch processing throughput to close cycle duration.

Database performance tuning for EBS is not the same as generic Oracle database tuning. EBS has specific access patterns, indexing requirements, and configuration needs that require specialized knowledge.

The Four Pillars of EBS Database Performance

1. Optimizer Statistics Management

The Oracle Cost-Based Optimizer (CBO) relies on accurate table and index statistics to generate efficient execution plans. In an EBS environment, statistics management is critical because:

• Transaction tables grow rapidly: AP_INVOICES_ALL, GL_JE_LINES, and similar tables can grow by millions of rows per month
• Data distribution shifts: Period-end processing creates skewed data distributions that differ from mid-period patterns
• Concurrent processing depends on good plans: Batch programs running with suboptimal plans waste resources and extend processing windows

Best practices for EBS statistics:

• Use FND_STATS to gather statistics rather than DBMS_STATS directly—FND_STATS understands EBS-specific requirements
• Schedule statistics collection after major data loads and before close processing
• Monitor for plan regressions after statistics refreshes using SQL Plan Baselines
• Gather system statistics to help the optimizer understand your hardware characteristics

2. Index Strategy

EBS ships with thousands of indexes, but the default indexing strategy may not be optimal for your transaction volumes and access patterns.

Common indexing issues in EBS:

• Missing indexes on custom columns added to standard tables
• Fragmented indexes on high-DML tables not being rebuilt regularly
• Function-based indexes needed for custom queries but not implemented
• Unused indexes consuming space and slowing DML operations

Index maintenance best practices:

• Monitor index usage with ALTER INDEX MONITORING USAGE to identify unused indexes
• Rebuild indexes with high clustering factor on critical transaction tables quarterly
• Create targeted indexes for custom reports and programs based on execution plan analysis
• Maintain a change log of all index modifications for troubleshooting

3. Memory Configuration

EBS database memory configuration directly impacts query performance, sort operations, and concurrent processing throughput.

Key memory areas for EBS:

• SGA (System Global Area): Size the buffer cache to achieve 95%+ hit ratio for EBS workloads. The shared pool should accommodate EBS’s large SQL footprint.
• PGA (Program Global Area): Critical for sort and hash join operations in financial reports. Undersized PGA forces disk-based sorts that dramatically slow report generation.
• Temp tablespace: EBS financial reports and batch processes frequently use temp space for sorting and intermediate results. Size it generously and monitor usage.

Sizing guidelines:

• Buffer cache: Enough to cache the active working set of your most-queried tables
• Shared pool: 2–4 GB minimum for EBS environments, more for heavy concurrent usage
• PGA aggregate target: 1–2 GB per active concurrent manager process

4. I/O Optimization

Database I/O performance is often the ultimate bottleneck in EBS environments, particularly during batch processing and close periods.

I/O optimization strategies:

• Tablespace layout: Separate high-I/O tables (AP_INVOICES_ALL, GL_JE_LINES, XLA_AE_LINES) onto dedicated fast storage
• Redo log sizing: Size redo logs to switch no more frequently than every 15–20 minutes during peak processing
• Archive log management: Ensure archive log generation doesn’t compete with application I/O
• ASM configuration: If using ASM, configure disk groups to balance I/O across all available spindles

Monitoring and Diagnostics

Essential Monitoring Queries

Track these metrics daily:

• Buffer cache hit ratio: Should remain above 95%
• Library cache hit ratio: Should remain above 99%
• Top wait events: Identify the primary performance bottlenecks
• Long-running SQL: Catch performance regressions early

AWR and ASH Analysis

Oracle’s Automatic Workload Repository (AWR) and Active Session History (ASH) are invaluable for EBS database tuning:

• Generate AWR reports for peak processing periods and compare them month over month
• Use ASH data to identify SQL statements causing contention during close processing
• Create AWR baselines for normal and close-period workloads to detect deviations

Proactive Performance Management

Don’t wait for users to complain. Implement proactive performance management:

1. Establish baselines: Document normal performance characteristics for your top 20 concurrent programs
2. Set thresholds: Configure alerts when key metrics deviate from baselines by more than 20%
3. Schedule health checks: Quarterly database health checks covering statistics, indexes, space, and configuration
4. Plan for growth: Project data volume growth and scale database resources proactively