Show a Count of Analyzed Tables by Schema in Oracle Database

calendar Jun 30, 2025 / · 3 min read · oracle sql database administration performance dba_tables  ·
Share on: linkedin copy

Show a Count of Analyzed Tables Broken Down by Schema in Oracle Database

Purpose:
This post explains how to use Oracle SQL to report the total number of tables and the number of analyzed tables for each schema in your Oracle Database. This is crucial for database administrators who want to monitor the health and performance of their database, ensure accurate statistics for the optimizer, and identify schemas where statistics may be outdated or missing.

SQL Code

 1set pages 999 lines 100
 2select	a.owner
 3,	a.total_tables tables
 4,	nvl(b.analyzed_tables,0) analyzed
 5from	(select	owner
 6	,	count(*) total_tables
 7	from	dba_tables
 8	group	by owner) a
 9,	(select	owner
10	,	count(last_analyzed) analyzed_tables
11	from	dba_tables
12	where	last_analyzed is not null
13	group	by owner) b
14where	a.owner = b.owner (+)
15and	a.owner not in ('SYS', 'SYSTEM')
16order	by a.total_tables - nvl(b.analyzed_tables,0) desc
17/

Breakdown and Explanation

  • dba_tables: This data dictionary view contains metadata for all tables in the database, including their owner and when they were last analyzed.
  • owner: The schema name.
  • total_tables: The total number of tables owned by each schema.
  • analyzed_tables: The number of tables in each schema that have been analyzed (i.e., have non-null LAST_ANALYZED).
  • nvl(b.analyzed_tables,0): Ensures schemas with zero analyzed tables show as 0 rather than NULL.
  • a.owner not in ('SYS', 'SYSTEM'): Excludes internal Oracle schemas from the report.
  • order by a.total_tables - nvl(b.analyzed_tables,0) desc: Sorts results to highlight schemas with the most unanalyzed tables.

Key Points and Insights

  • Why Analyze Tables?
    Oracle's query optimizer relies on up-to-date statistics to generate efficient execution plans. If tables are not analyzed, or their statistics are stale, query performance can degrade significantly

  • What Does LAST_ANALYZED Mean?
    The LAST_ANALYZED column in DBA_TABLES records when statistics were last gathered for each table. If it is NULL, the table has never been analyzed or statistics have been deleted

  • Difference Between Table Count and Analyzed Table Count:

    • Total tables shows all tables in a schema.
    • Analyzed tables shows only those with statistics.
      A large gap indicates tables that may need to be analyzed to ensure optimal performance.

  • Why Exclude SYS and SYSTEM?
    These are Oracle's internal schemas; their tables are managed by Oracle and generally not relevant to user/application performance tuning.

Best Practices

  • Regularly Gather Statistics:
    Schedule regular jobs using DBMS_STATS to keep statistics current, especially after large data loads or structural changes
  • Monitor for Unanalyzed Tables:
    Use this report to identify schemas needing attention, and prioritize analyzing tables with missing or outdated statistics.
  • Understand the Impact:
    Relying on dictionary statistics (NUM_ROWS in DBA_TABLES) is fast but may be outdated; for mission-critical queries, consider running ANALYZE or DBMS_STATS to refresh statisticsa

References

Keep your Oracle Database running efficiently by ensuring all tables are regularly analyzed and statistics are up to date.

Posts in this series