How to Gather Schema Statistics for the APEX

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.

SELECT
    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:

BEGIN
  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).

EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname => 'SYSTEM', stattab => 'APEX_STATS_BKP');

Export the Current Statistics

Export the existing statistics of the APEX schema into your newly created backup table.

BEGIN

  DBMS_STATS.EXPORT_SCHEMA_STATS (
    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:

BEGIN

  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;

/

How to Roll Back (Only if needed)

If queries slow down after gathering new stats, restore the original metadata from your backup table:

BEGIN
  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:

EXEC DBMS_STATS.DROP_STAT_TABLE(ownname => 'SYSTEM', stattab => 'APEX_STATS_BKP');

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.

SELECT
    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;

Check Specific Tables and Row Counts

This query shows the exact date and time statistics were last gathered for every table in the APEX schema, sorted from newest to oldest.

SELECT
    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;

What to Look For

STALE_STATS = YES: The data has changed significantly since the last gather, and Oracle considers these stats outdated.

LAST_ANALYZED IS NULL: Statistics have never been gathered for this object.

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

BEGIN
  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).

SELECT
    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.

SELECT table_name, stattype_locked FROM dba_tab_statistics
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.

SELECT
    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;

STALE_STATS = YES: Means data changes have crossed the 10% threshold.

LAST_ANALYZED IS NULL: Means statistics are missing completely.

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.

BEGIN
--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?

Saves Time: It drastically cuts down script runtime by avoiding large, unchanged tables.
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.