You can gather statistics for the Apex (Oracle Application Express) schema. However, in an Oracle E-Business Suite (EBS) environment, you must use standard DBMS_STATS procedures instead of the EBS-specific FND_STATS wrapper for non-EBS schemas.
Recommended Steps to Gather Stats
Check Schema Statistics
This query gives you a quick overview of the total number of tables, indexes, and how many of them have missing or stale statistics.
owner,
COUNT(*) as total_tables,
SUM(CASE WHEN last_analyzed IS NULL THEN 1 ELSE 0 END) as missing_stats,
MIN(last_analyzed) as oldest_stats,
MAX(last_analyzed) as newest_stats
FROM dba_tables
WHERE owner = 'APEX_SCHEMA'
GROUP BY owner;
Gather Schema Statistics
You can gather these statistics directly using the DBMS_STATS package while connected as the SYS or SYSTEM user via
SQL*Plus or SQL Developer.
Run the following PL/SQL block in your database:
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname => 'APEX_SCHEMA',---like APEX_200200
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
cascade => TRUE,
degree => DBMS_STATS.AUTO_DEGREE
);
END;
/
Gather Fixed Object Statistics (If performance is slow globally)
If APEX is running slow globally across the database, also consider ensuring
your fixed objects have fresh stats:
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
Best Practices & Warnings for EBS
Do NOT use FND_STATS: EBS-specific wrappers (like FND_STATS.GATHER_SCHEMA_STATS) check for EBS specific metadata dictionary tables. Using them on the APEX schema may fail or corrupt standard executions. Use native DBMS_STATS instead.
Backup: If you are unsure about the impact, you can create a stats table to export the current APEX statistics first using DBMS_STATS.CREATE_STAT_TABLE and DBMS_STATS.EXPORT_SCHEMA_STATS, allowing you to roll back if necessary.
Here is the precise SQL script to backup your APEX_SCHEMA statistics and rollback them if you encounter performance issues.
Run these steps as the SYS or SYSTEM user.
Create the Backup Table
Create a dedicated table to hold the current statistics. You can place
this in a system schema or a custom DBA schema (e.g., SYSTEM).
Export the Current Statistics
Export the existing statistics of the APEX schema into your newly
created backup table.
BEGIN
ownname => 'APEX_SCHEMA',---like APEX_200200
stattab => 'APEX_STATS_BKP',
statown => 'SYSTEM'
);
END;
/
Gather Schema Statistics Run the following PL/SQL block in your database:
How to Roll Back (Only if
needed)
If queries slow down after gathering new stats, restore the original
metadata from your backup table:
DBMS_STATS.IMPORT_SCHEMA_STATS (
ownname => 'APEX_SCHEMA',---like APEX_200200
stattab => 'APEX_STATS_BKP',
statown => 'SYSTEM'
);
END;
/
Clean Up
Once you verify that the new statistics work well and performance is
stable, drop the backup table:
You can check when statistics were last gathered for the APEX_SCHEMA schema by querying the data dictionary views.
Run these scripts as a user with DBA privileges (like SYS or SYSTEM).
Check Schema-Level Summary
This query gives you a quick overview of the total number of tables,
indexes, and how many of them have missing or stale statistics.
owner,
COUNT(*) as total_tables,
SUM(CASE WHEN last_analyzed IS NULL THEN 1 ELSE 0 END) as missing_stats,
MIN(last_analyzed) as oldest_stats,
MAX(last_analyzed) as newest_stats
FROM dba_tables
WHERE owner = 'APEX_SCHEMA'
GROUP BY owner;
This query shows the exact date and time statistics were last gathered
for every table in the APEX schema, sorted from newest to oldest.
table_name,
num_rows,
blocks,
TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') AS last_gathered,
stale_stats
FROM dba_tab_statistics
WHERE owner = 'APEX_SCHEMA'
ORDER BY last_analyzed DESC;
Delete statistics completely for a specific table
To remove statistics completely for a specific table in the APEX_SCHEMA schema, you use the DBMS_STATS.DELETE_TABLE_STATS procedure. This immediately
clears out the table, column, and index metadata, forcing the Oracle Cost-Based
Optimizer (CBO) to use its built-in dynamic sampling or internal default cards
for that table.
Run this script as a user with DBA privileges (such as SYS or SYSTEM).
The Script to Delete Table
Statistics
DBMS_STATS.DELETE_TABLE_STATS (
ownname => 'APEX_SCHEMA',
tabname => 'YOUR_TABLE_NAME',
cascade_parts => TRUE, -- Deletes partition-level statistics if applicable
cascade_columns => TRUE, -- Deletes associated column statistics/histograms
cascade_indexes => TRUE -- Deletes all associated index statistics
);
END;
/
Verify the Statistics are Gone
After running the deletion block, execute this query to confirm that the
metadata has been purged. The statistics columns should now return empty (NULL).
table_name,
num_rows,
blocks,
last_analyzed
FROM dba_tables
WHERE owner = 'APEX_SCHEMA'
AND table_name = 'YOUR_TABLE_NAME';
A Crucial Next Step
If you delete statistics because they were causing a bad execution plan,
you must lock the table immediately after deletion. If you leave it
unlocked, Oracle’s automatic nightly maintenance job will automatically gather
fresh statistics again, undoing your deletion.
Lock the statistics
Locking statistics prevents the automatic nightly maintenance job or accidental manual runs from overwriting your preferred, stable statistics.
Run these commands as a user with DBA privileges (such as SYS or SYSTEM).
Lock Statistics for the Entire
Schema
To lock the statistics for every table and index inside the APEX_SCHEMA schema, use the following command:
EXEC DBMS_STATS.LOCK_SCHEMA_STATS(ownname => 'APEX_SCHEMA');
Verify the Lock Status
You can verify that the statistics are successfully locked by checking
the STATTYPE_LOCKED column in the data dictionary. If it returns ALL, the statistics
are locked.
WHERE owner = 'APEX_SCHEMA';
How to Unlock Statistics (If
you need to update them later)
If you patch APEX, perform a large data load, or decide it is time to
gather new statistics, you must unlock the schema first:
EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS(ownname => 'APEX_SCHEMA');
Alternative: Lock a Single
Specific Table
If you only want to freeze statistics for a specific problematic table
instead of the whole schema, use these table-level commands:
-- Lock a single table
EXEC DBMS_STATS.LOCK_TABLE_STATS(ownname => 'APEX_SCHEMA', tabname => 'YOUR_TABLE_NAME');
-- Unlock a single table
EXEC DBMS_STATS.UNLOCK_TABLE_STATS(ownname => 'APEX_SCHEMA', tabname => 'YOUR_TABLE_NAME');
Automatically gather statistics ONLY on stale tables
You can identify tables with stale statistics automatically by querying Oracle's data dictionary views or by using a PL/SQL block that utilizes the DBMS_STATS package.
The fastest way to view all stale or missing statistics for the APEX_SCHEMA schema is to query DBA_TAB_STATISTICS.
table_name,
num_rows,
TO_CHAR(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') AS last_gathered,
stale_stats
FROM dba_tab_statistics
WHERE owner = 'APEX_SCHEMA'
AND (stale_stats = 'YES' OR last_analyzed IS NULL)
ORDER BY table_name;
The Automated PL/SQL Script
This script flushes the latest database monitoring information to ensure
accurate tracking data, then automatically gathers statistics only on
the stale or unanalyzed objects within the APEX_SCHEMA schema.
--Step1:Force Oracle to flush the latest row modification tracking data to disk
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
--Step2:Gather stats ONLY on stale or missing tables
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname => 'APEX_SCHEMA',
options => 'GATHER STALE', -- Crucial option: skips non-stale tables
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
cascade => TRUE,
degree => DBMS_STATS.AUTO_DEGREE
);
END;
/
Alternative: "GATHER
EMPTY"
If you are worried about performance and only want to populate
statistics for tables that have never been analyzed before (completely missing
stats), change the option parameter:
options => 'GATHER EMPTY'--
Processes only tables with NULL statistics
Why use GATHER STALE?
Reduces I/O Overhead: It prevents unnecessary disk reads and CPU usage on your EBS database container.
Maintains Performance: It updates query execution plans only where major data shifts have actually occurred.
