Oracle EBS 12.2 on 19c CDB/PDB (Database Performance Tuning)

 Oracle EBS 12.2 on 19c CDB/PDB

Database Performance Tuning - Step-by-Step DBA Runbook

Prepared by: Bidhan Mandal | Oracle Core DBA & Apps DBA

Rules before you start: Change ONE thing at a time. Take AWR snapshot before and after every change. Backup SPFILE (CREATE PFILE FROM SPFILE) before parameter changes. Test in UAT first. AWR/ASH/ADDM/SQL Tuning Advisor need Diagnostics + Tuning Pack licence.

Step Index

Step	Area	Goal
1	OS / Server	Confirm CPU, memory, swap, I/O are not the bottleneck
2	CDB / PDB health	Instance, PDB open mode, alert log, errors
3	Live sessions & waits	Find what sessions wait on right now; blockers
4	AWR / ASH / ADDM	Find bottleneck over time window
5	Top SQL tuning	Fix bad plans, use SQL Tuning Advisor, SPM baseline
6	Optimizer statistics	EBS-correct stats gathering (FND_STATS)
7	Memory (SGA / PGA)	Right-size, HugePages
8	Init parameters	Validate against EBS 12.2 certified values
9	Redo, TEMP, UNDO, datafile I/O	Remove storage-side waits
10	EBS application housekeeping	Concurrent manager / workflow purge, big tables
11	ADOP cleanup & invalid objects	12.2 online patching leftovers, invalids
12	Segments & indexes	Recycle bin, unusable indexes, fragmentation
13	CDB resource manager & scheduler jobs	Stop PDB throttling and maintenance window load
14	Middle tier check	OACore JVM, DB connection pool
15	Validate & document	Before/after comparison, change log

 

Quick Triage Table

Top wait / symptom	Likely cause	Go to step
db file sequential read	Bad plan, missing index, slow storage	5, 6, 9
db file scattered read / direct path read	Full scans, stale stats, small buffer cache	5, 6, 7
log file sync / log file parallel write	Slow redo disk, too frequent commits	9
enq: TX - row lock contention	Blocking session / custom code	3, 10
library cache: mutex X / latch: shared pool	Hard parse, small shared pool, invalids	7, 8, 11
CPU 100% with few waits	Bad SQL, parallelism, PDB CPU limit	1, 5, 13
resmgr:cpu quantum	Resource Manager throttling PDB	13
Slow only in concurrent programs	Stale stats, FND tables bloated	6, 10

 

Step 1: OS / Server Check

Rule out the server first. If CPU run queue is high, I/O wait is high, or system is swapping, database tuning will not help until that is fixed.

 bash - oracle@dbserver

# login as oracle on DB server

uptime

top -b -n 1 | head -25

vmstat 5 5

sar -u 5 3

free -m

iostat -xm 5 3

df -h

# HugePages status

grep -i huge /proc/meminfo

 

 

•      vmstat: r column > CPU count = CPU bottleneck; si/so > 0 = swapping (bad); wa > 10-15% = I/O wait.

•      iostat: await > 10-20 ms or %util near 100 on data/redo disks = storage bottleneck.

•      df -h: any mount (datafile, archive, /tmp) at 90%+ must be cleared.

Step 2: CDB / PDB Health

 SQL*Plus - CDB$ROOT then PDB

sqlplus / as sysdba

 

SET LINES 220 PAGES 200 TRIMSPOOL ON

COL name FORMAT A20

SELECT name, cdb, open_mode, log_mode FROM v$database;

SELECT instance_name, status, database_status, startup_time FROM v$instance;

SELECT con_id, name, open_mode, restricted FROM v$pdbs;

 

-- switch into the EBS PDB (replace with your PDB name)

ALTER SESSION SET CONTAINER = EBSPDB;

SHOW CON_NAME

 

-- locate and read alert log

SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

-- then: tail -300 <diag_trace>/alert_<SID>.log | grep -i -E "ORA-|error|cannot|stuck"

 

 

Note: Instance-level parameters (SGA, PGA, processes) are set in CDB$ROOT. Most EBS application queries, AWR per-PDB views and stats gathering are done inside the PDB.

Step 3: Live Sessions, Waits and Blockers

3.1 Active session wait summary

 SQL - inside PDB

SELECT event, wait_class, COUNT(*) sessions

FROM   v$session

WHERE  status = 'ACTIVE' AND type = 'USER' AND wait_class <> 'Idle'

GROUP  BY event, wait_class

ORDER  BY sessions DESC;

 

SELECT wait_class, event, total_waits,

       ROUND(time_waited_micro/1e6) sec_waited

FROM   v$system_event

WHERE  wait_class <> 'Idle'

ORDER  BY time_waited_micro DESC

FETCH FIRST 15 ROWS ONLY;

 

 

3.2 Blocking sessions

 SQL

SELECT blocking_session, sid, serial#, event, seconds_in_wait, sql_id, module

FROM   v$session

WHERE  blocking_session IS NOT NULL

ORDER  BY seconds_in_wait DESC;

 

-- kill only after confirming with application owner

-- ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id' IMMEDIATE;

 

 

3.3 Long running sessions (> 5 min)

 SQL

SELECT sid, serial#, username, sql_id, event, last_call_et sec,

       module, action, machine

FROM   v$session

WHERE  status = 'ACTIVE' AND type = 'USER' AND last_call_et > 300

ORDER  BY last_call_et DESC;

 

 

Step 4: AWR, ASH and ADDM

Generate reports for the slow time window and compare with a good-performance window. In 19c CDB, enable PDB-level AWR snapshots so reports can be taken inside the PDB.

 SQL*Plus

-- CDB$ROOT: allow PDB snapshots

ALTER SYSTEM SET awr_pdb_autoflush_enabled = TRUE SCOPE=BOTH;

 

-- snapshot every 30 min, keep 14 days

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>20160, interval=>30);

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

 

-- reports (run inside PDB, choose begin/end snap IDs)

@?/rdbms/admin/awrrpt.sql

@?/rdbms/admin/ashrpt.sql

@?/rdbms/admin/addmrpt.sql

-- compare good vs bad period

@?/rdbms/admin/awrddrpt.sql

 

 

Read the AWR report in this order:

•      Load Profile: DB Time vs elapsed time (DB Time >> elapsed x CPU count = overloaded); hard parses/sec; logical/physical reads.

•      Top 10 Foreground Events by Total Wait Time: your main bottleneck.

•      Time Model Statistics: SQL execute vs parse vs PL/SQL time.

•      SQL ordered by Elapsed Time / CPU / Gets / Reads: candidates for Step 5.

•      Instance Efficiency: Soft Parse % should be > 95%; Buffer Hit % alone is not a proof of health.

•      Advisory sections: SGA/PGA target advice, buffer pool advisory.

Step 5: Top SQL Tuning

5.1 Find top SQL now

 SQL

SELECT sql_id, plan_hash_value phv, executions execs,

       ROUND(elapsed_time/1e6) ela_sec,

       ROUND(elapsed_time/NULLIF(executions,0)/1e6,2) ela_per_exec,

       buffer_gets, disk_reads, module,

       SUBSTR(sql_text,1,60) sql_text

FROM   v$sql

ORDER  BY elapsed_time DESC

FETCH FIRST 15 ROWS ONLY;

 

 

5.2 Check the execution plan

 SQL

-- from cursor cache (with actual row counts if gather_plan_statistics hint used)

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST +PEEKED_BINDS'));

 

-- from AWR history

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

 

-- did plan change? (multiple PHV for same sql_id)

SELECT sql_id, plan_hash_value, COUNT(*) snaps, MIN(begin_interval_time) first_seen

FROM   dba_hist_sqlstat s JOIN dba_hist_snapshot n USING (snap_id, dbid, instance_number)

WHERE  sql_id = '&sql_id'

GROUP  BY sql_id, plan_hash_value;

 

 

5.3 SQL Tuning Advisor

 SQL

DECLARE

  l_task VARCHAR2(128);

BEGIN

  l_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(

              sql_id     => '&sql_id',

              scope      => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,

              time_limit => 600,

              task_name  => 'TUNE_&sql_id');

  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => l_task);

END;

/

SET LONG 1000000 LONGCHUNKSIZE 1000000 PAGES 0 LINES 200

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_&sql_id') FROM dual;

 

 

5.4 Pin the good plan (SQL Plan Baseline)

 SQL

DECLARE

  n PLS_INTEGER;

BEGIN

  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(

         sql_id          => '&sql_id',

         plan_hash_value => &good_phv);

  DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || n);

END;

/

SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines ORDER BY created DESC;

 

 

EBS note: Do not edit Oracle seeded SQL. Fix with stats, indexes recommended by Oracle Support, baselines, or an Oracle patch. Custom (XX) code can be rewritten.

Step 6: Optimizer Statistics (EBS Way)

For EBS application schemas always gather with the concurrent program "Gather Schema Statistics" (FND_STATS), not raw DBMS_STATS, so histograms and EBS-specific handling are correct.

•      System Administrator > Requests > Submit: "Gather Schema Statistics", Schema = ALL, Estimate Percent = 10 (or Auto), Backup Flag = NOBACKUP, Invalidate Dependent Cursors = Yes.

•      Run during low load. Schedule weekly.

 SQL

-- check stale stats on application schemas

SELECT owner, COUNT(*) stale_tables

FROM   dba_tab_statistics

WHERE  stale_stats = 'YES' AND owner NOT IN ('SYS','SYSTEM')

GROUP  BY owner ORDER BY 2 DESC;

 

-- tables never analyzed or very old

SELECT owner, table_name, num_rows, last_analyzed

FROM   dba_tables

WHERE  owner IN ('APPLSYS','APPS','GL','AP','AR','PO','INV','HR')

AND   (last_analyzed IS NULL OR last_analyzed < SYSDATE - 30)

AND    temporary = 'N'

ORDER  BY last_analyzed NULLS FIRST

FETCH FIRST 30 ROWS ONLY;

 

-- dictionary, fixed objects, system stats (as SYSDBA, in PDB)

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

EXEC DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD');

 

 

Step 7: Memory - SGA, PGA, HugePages

 SQL - CDB$ROOT

SHOW PARAMETER sga

SHOW PARAMETER pga

SHOW PARAMETER memory_target

SHOW PARAMETER use_large_pages

 

COL component FORMAT A35

SELECT component, ROUND(current_size/1048576) mb

FROM   v$sga_dynamic_components WHERE current_size > 0;

 

-- advisors: look for where estd_db_time_factor stops improving

SELECT sga_size, sga_size_factor, estd_db_time, estd_db_time_factor FROM v$sga_target_advice;

SELECT ROUND(pga_target_for_estimate/1048576) mb, pga_target_factor,

       estd_pga_cache_hit_percentage, estd_overalloc_count

FROM   v$pga_target_advice;

 

SELECT name, ROUND(value/1048576) mb FROM v$pgastat

WHERE  name IN ('total PGA allocated','maximum PGA allocated','over allocation count');

 

-- shared pool / library cache health

SELECT namespace, gets, gethitratio, pins, reloads, invalidations FROM v$librarycache;

 

 

Apply changes (CDB$ROOT, restart required for some)

 SQL

CREATE PFILE='/tmp/init_before_tuning.ora' FROM SPFILE;

 

-- example values only: size from advisors and server RAM

ALTER SYSTEM SET sga_target=24G SCOPE=SPFILE;

ALTER SYSTEM SET sga_max_size=24G SCOPE=SPFILE;

ALTER SYSTEM SET pga_aggregate_target=8G SCOPE=SPFILE;

ALTER SYSTEM SET use_large_pages='ONLY' SCOPE=SPFILE;

 

 

•      Keep SGA + PGA at roughly 60-70% of server RAM when the server is DB-only.

•      HugePages: set vm.nr_hugepages in /etc/sysctl.conf to cover SGA, and disable Transparent HugePages. Never use AMM (memory_target) together with HugePages.

Step 8: Validate Init Parameters

Compare your values with the current Oracle EBS 12.2 database initialization parameters for 19c (MOS Doc ID 396009.1). Verify the document for your exact EBS and RU level before changing anything.

 SQL

COL name FORMAT A35

COL value FORMAT A40

SELECT name, value, isdefault

FROM   v$parameter

WHERE  name IN ('processes','sessions','open_cursors','session_cached_cursors',

                'sga_target','pga_aggregate_target','db_files','undo_retention',

                'optimizer_adaptive_plans','optimizer_adaptive_statistics',

                'optimizer_features_enable','cursor_sharing','filesystemio_options',

                'disk_asynch_io','db_file_multiblock_read_count','parallel_max_servers',

                'use_large_pages','job_queue_processes')

ORDER  BY name;

 

-- sessions vs limit

SELECT resource_name, current_utilization, max_utilization, limit_value

FROM   v$resource_limit WHERE resource_name IN ('processes','sessions');

 

 

•      If max_utilization of processes is near limit_value, raise processes (restart needed).

•      High "parse" time or shared pool waits: check session_cached_cursors and open_cursors against the Oracle note.

•      filesystemio_options = SETALL and disk_asynch_io = TRUE on filesystem storage (not needed for ASM).

Step 9: Redo, TEMP, UNDO and Datafile I/O

 SQL

-- redo log size and switch rate (target about 1 switch per 15-20 min at peak)

SELECT group#, thread#, bytes/1048576 mb, members, status FROM v$log;

SELECT TO_CHAR(first_time,'YYYY-MM-DD HH24') hr, COUNT(*) switches

FROM   v$log_history WHERE first_time > SYSDATE - 3

GROUP  BY TO_CHAR(first_time,'YYYY-MM-DD HH24') ORDER BY 1;

 

-- log file sync average

SELECT event, total_waits, ROUND(time_waited_micro/NULLIF(total_waits,0)/1000,2) avg_ms

FROM   v$system_event WHERE event IN ('log file sync','log file parallel write');

 

-- TEMP

SELECT tablespace_name, ROUND(tablespace_size/1048576) total_mb,

       ROUND(allocated_space/1048576) alloc_mb, ROUND(free_space/1048576) free_mb

FROM   dba_temp_free_space;

 

-- UNDO

SELECT tablespace_name, status, ROUND(SUM(bytes)/1048576) mb

FROM   dba_undo_extents GROUP BY tablespace_name, status;

 

-- slowest datafiles

SELECT df.name, fs.phyrds, fs.phywrts,

       ROUND(fs.readtim*10/NULLIF(fs.phyrds,0),2) avg_read_ms

FROM   v$filestat fs JOIN v$datafile df ON df.file# = fs.file#

ORDER  BY fs.phyrds DESC FETCH FIRST 10 ROWS ONLY;

 

 

•      log file sync avg > 10 ms: move redo to faster disk, add larger redo groups (e.g. 2 GB), review commit frequency in custom code.

•      avg_read_ms > 10 ms on SSD/SAN = storage issue; escalate with iostat data.

•      Add redo group example: ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 11 ('/u02/redo/redo11.log') SIZE 2G; then drop old small groups when INACTIVE.

Step 10: EBS Application Housekeeping

Bloated FND and Workflow tables slow concurrent managers, forms and OAF pages. Run these standard purge programs from System Administrator responsibility and schedule them.

Concurrent program	Suggested setting
Purge Concurrent Request and/or Manager Data	Entity = ALL, Mode = Age, Age = 7-30 days
Purge Obsolete Workflow Runtime Data	Item Type blank (all), Age 7-30 days, Core Workflow only = No
Purge Signon Audit Data	Audit date = sysdate - 30
Purge Debug Log and System Alerts	Older than 7-30 days
Gather Schema Statistics	After each large purge (Step 6)

 

 SQL - APPS

-- size of key tables

SELECT COUNT(*) FROM applsys.fnd_concurrent_requests;

SELECT COUNT(*) FROM applsys.wf_items;

SELECT COUNT(*) FROM applsys.wf_item_activity_statuses;

 

-- top 20 segments in APPS-related schemas

SELECT owner, segment_name, segment_type, ROUND(bytes/1048576) mb

FROM   dba_segments

WHERE  owner IN ('APPLSYS','APPS','GL','AP','AR','PO','INV','XLA','XDO')

ORDER  BY bytes DESC FETCH FIRST 20 ROWS ONLY;

 

-- long running concurrent requests

SELECT r.request_id, p.user_concurrent_program_name prog,

       ROUND((SYSDATE - r.actual_start_date)*24*60) run_min

FROM   apps.fnd_concurrent_requests r, apps.fnd_concurrent_programs_tl p

WHERE  r.concurrent_program_id = p.concurrent_program_id

AND    r.program_application_id = p.application_id

AND    p.language = 'US' AND r.phase_code = 'R'

ORDER  BY run_min DESC;

 

-- pending requests backlog

SELECT COUNT(*) pending FROM apps.fnd_concurrent_requests WHERE phase_code = 'P' AND status_code = 'I';

 

 

Step 11: ADOP Cleanup and Invalid Objects

In EBS 12.2, leftover old editions and cross-edition objects after patching cause slow parsing and slow DDL. Run cleanup after every successful cutover.

 bash - applmgr (adjust EBSapps.env path)

# on application tier, as applmgr

source /u01/app/oracle/EBSapps.env run

adop -status

adop phase=cleanup cleanup_mode=full

 

 

 SQL*Plus

-- editions present (only ORA$BASE + current run edition expected)

SELECT edition_name, usable FROM dba_editions;

 

-- invalid objects

SELECT owner, object_type, COUNT(*) cnt

FROM   dba_objects WHERE status = 'INVALID'

GROUP  BY owner, object_type ORDER BY cnt DESC;

 

-- recompile

@?/rdbms/admin/utlrp.sql

 

 

Tip: If APPS objects stay invalid after utlrp, compile with adadmin (Compile/Reload Database Entities > Compile APPS schema) and check the error in DBA_ERRORS.

Step 12: Segments and Indexes

 SQL

-- recycle bin

SELECT COUNT(*) FROM dba_recyclebin;

PURGE DBA_RECYCLEBIN;

 

-- unusable indexes (must be rebuilt)

SELECT owner, index_name, status FROM dba_indexes WHERE status = 'UNUSABLE';

SELECT index_owner, index_name, partition_name FROM dba_ind_partitions WHERE status = 'UNUSABLE';

 

-- rebuild example (online)

-- ALTER INDEX owner.index_name REBUILD ONLINE;

 

-- tables with large wasted space (compare segment size vs num_rows*avg_row_len)

SELECT t.owner, t.table_name, ROUND(s.bytes/1048576) seg_mb,

       ROUND(t.num_rows*t.avg_row_len/1048576) est_data_mb

FROM   dba_tables t JOIN dba_segments s

       ON s.owner = t.owner AND s.segment_name = t.table_name

WHERE  t.owner IN ('APPLSYS','APPS') AND s.bytes > 100*1048576

ORDER  BY s.bytes DESC FETCH FIRST 15 ROWS ONLY;

 

 

Caution: Do not rebuild indexes routinely. Rebuild only for unusable status or proven benefit. Reclaim table space with ALTER TABLE ... SHRINK SPACE / move only in a maintenance window and only for non-Oracle-seeded-critical tables after testing.

Step 13: CDB Resource Manager and Scheduler Jobs

In a multitenant setup a CDB resource plan or maintenance window can throttle the EBS PDB even when the server is idle.

 SQL - CDB$ROOT

-- CDB$ROOT

SHOW PARAMETER resource_manager_plan

SELECT plan, pluggable_database, shares, utilization_limit

FROM   cdb_cdb_rsrc_plan_directives ORDER BY plan;

 

-- PDB CPU usage vs wait (high cpu_wait_time = throttled)

SELECT con_id, begin_time, cpu_consumed_time, cpu_wait_time, avg_running_sessions

FROM   v$rsrcpdbmetric ORDER BY begin_time DESC FETCH FIRST 10 ROWS ONLY;

 

-- wait event check

SELECT event, COUNT(*) FROM v$session WHERE event LIKE 'resmgr%' GROUP BY event;

 

-- autotask / maintenance windows overlapping business hours

SELECT client_name, status FROM dba_autotask_client;

SELECT window_name, enabled, repeat_interval, duration FROM dba_scheduler_windows;

SELECT job_name, state, last_start_date FROM dba_scheduler_running_jobs;

 

 

•      If resmgr:cpu quantum appears, relax the PDB utilization_limit or remove the plan.

•      Move automatic maintenance windows to night/weekend if they run in office hours.

Step 14: Middle Tier Quick Check

 bash - applmgr

# application tier

ps -ef | grep -i oacore | grep -v grep

free -m

# JVM GC pressure for an oacore PID

jstat -gcutil <oacore_pid> 5000 5

# DB sessions per module (from DB)

#   SELECT module, COUNT(*) FROM v$session WHERE type='USER' GROUP BY module ORDER BY 2 DESC;

 

 

•      Frequent full GC or Old gen > 90%: increase oacore heap in WebLogic Admin Console and add managed servers.

•      Many idle JDBC sessions from one module: tune data source pool size (Initial/Max capacity).

•      Forms slow but DB is healthy: check Forms server, network latency and client Java.

Step 15: Validate and Document

 SQL*Plus

-- take end snapshot then generate after-change AWR

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

@?/rdbms/admin/awrddrpt.sql

 

 

Metric	Before	After	Comment
Avg active sessions
DB Time / elapsed
Top wait event
Avg log file sync (ms)
Avg single block read (ms)
Key concurrent program runtime
Change made / date / by

 

Rollback: Keep the PFILE backup from Step 7. To revert a parameter: ALTER SYSTEM SET <param>=<old> SCOPE=SPFILE, or CREATE SPFILE FROM PFILE='/tmp/init_before_tuning.ora'; and restart. Drop a baseline: DBMS_SPM.DROP_SQL_PLAN_BASELINE.