Column Analyze Dates Tracking (gcoldate.sql)

What This Script Does

This script tracks when table columns were last analyzed for statistics by querying Oracle’s internal histogram header tables. It provides insights into statistics gathering activities across a specified date range, showing which tables had column-level statistics collected and when. This information is crucial for monitoring statistics maintenance schedules and ensuring optimizer statistics are current.

The Script

rem  gcoldate.sql
rem
ttitle 'Column Analyze Dates'
rem
col owner format a8 heading 'OWNER'
col table_name format a30 heading 'TABLE NAME'
col column_name format a30 heading 'COLUMN NAME'
col analyze_date format a9 heading 'ANALYZE|DATE'
col column_count format 999 heading 'COLUMN|COUNT'
rem
break on owner on table_name
rem
select u.name owner,
       o.name table_name,
/*
       c.name column_name,
*/
       trunc(h.timestamp) analyze_date,
       count(*) column_count
  from sys.user$ u,
       sys.obj$ o,
       sys.col$ c,
       sys.hist_head$ h
 where h.obj# = c.obj#
   and h.col# = c.col#
   and c.obj# = o.obj#
   and o.owner# = u.user#
   and timestamp between '&start' and '&end'
 group by u.name, o.name, trunc(h.timestamp);

Key Features

Internal Statistics Tracking: Queries Oracle’s internal histogram tables for precise timing
Date Range Filtering: Allows analysis of statistics gathering within specific periods
Column Count Aggregation: Shows number of columns analyzed per table per day
Schema Organization: Groups results by owner and table name
Histogram-Based Analysis: Focuses on columns with histogram statistics
Historical Analysis: Provides historical view of statistics maintenance activities

Usage

@gcoldate.sql

Input Parameters:

start: Start date for analysis period (format: ‘YYYY-MM-DD’)
end: End date for analysis period (format: ‘YYYY-MM-DD’)

Required Privileges

SELECT on SYS.USER$
SELECT on SYS.OBJ$
SELECT on SYS.COL$
SELECT on SYS.HIST_HEAD$
-- Note: These are system tables requiring DBA privileges

Sample Output

                                              Column Analyze Dates

OWNER    TABLE NAME                     ANALYZE   COLUMN
                                        DATE       COUNT
-------- ------------------------------ --------- ------
HR       EMPLOYEES                      15-JAN-24     8
         DEPARTMENTS                    15-JAN-24     4
         JOB_HISTORY                    15-JAN-24     6
         JOBS                           15-JAN-24     4
         LOCATIONS                      15-JAN-24     5
         REGIONS                        15-JAN-24     2

SALES    CUSTOMERS                      16-JAN-24    12
         ORDERS                         16-JAN-24     9
         ORDER_ITEMS                    16-JAN-24     7
         PRODUCTS                       16-JAN-24    11
         PRODUCT_CATEGORIES             16-JAN-24     3

FINANCE  ACCOUNTS                       17-JAN-24    15
         TRANSACTIONS                   17-JAN-24    10
         GL_ENTRIES                     17-JAN-24     8

Key Output Columns

OWNER: Schema owner of the analyzed tables
TABLE NAME: Name of the table whose columns were analyzed
ANALYZE DATE: Date when column statistics were gathered (truncated to day)
COLUMN COUNT: Number of columns analyzed for this table on this date

Understanding Column Analysis Tracking

Histogram Statistics

Histogram Purpose

-- Histograms are created for:
-- Columns with skewed data distributions
-- Columns used in WHERE clauses
-- Columns with significant data variations
-- Join columns with uneven distributions

Analysis Triggers

-- Column analysis occurs during:
-- Manual ANALYZE TABLE commands
-- DBMS_STATS.GATHER_TABLE_STATS calls
-- Automatic statistics gathering jobs
-- Index creation with COMPUTE STATISTICS

Statistics Gathering Patterns

Scheduled Maintenance

-- Regular patterns indicate:
-- Automated statistics gathering
-- Scheduled maintenance windows
-- Consistent DBA practices
-- Proper statistics management

Ad-hoc Analysis

-- Irregular patterns may indicate:
-- Manual statistics gathering
-- Performance troubleshooting activities
-- Application deployment activities
-- Data loading operations

Common Use Cases

Statistics Maintenance Monitoring
- Track automated statistics gathering schedules
- Verify statistics collection completeness
- Monitor maintenance window activities
- Ensure consistent statistics refresh
Performance Troubleshooting
- Correlate performance issues with stale statistics
- Identify tables missing recent analysis
- Track statistics gathering after data loads
- Verify optimizer information currency
Capacity Planning
- Monitor statistics gathering resource usage
- Plan maintenance window requirements
- Assess statistics collection patterns
- Optimize gathering schedules
Compliance and Auditing
- Document statistics maintenance activities
- Track adherence to maintenance schedules
- Audit database administration practices
- Support change management processes

Advanced Analysis

Statistics Freshness Assessment

Recent Activity Analysis

-- Assess recent statistics activity:
-- Tables analyzed in last 7 days
-- Frequency of statistics updates
-- Coverage across different schemas
-- Consistency of maintenance patterns

Staleness Detection

-- Identify potentially stale statistics:
-- Tables not analyzed recently
-- Irregular update patterns
-- Missing from recent gathering cycles
-- Correlation with performance issues

Maintenance Pattern Analysis

Schedule Effectiveness

-- Evaluate maintenance schedules:
-- Consistent weekly/monthly patterns
-- Coverage during maintenance windows
-- Impact on system performance
-- Correlation with data change patterns

Resource Utilization

-- Assess resource impact:
-- Number of columns analyzed per session
-- Frequency of intensive gathering operations
-- Distribution across time periods
-- Impact on concurrent operations

Filtering Examples

Recent Activity Review

-- Review last week's activity:
Enter value for start: 2024-01-15
Enter value for end: 2024-01-22

Monthly Analysis

-- Analyze monthly statistics gathering:
Enter value for start: 2024-01-01
Enter value for end: 2024-01-31

Specific Period Investigation

-- Investigate specific performance period:
Enter value for start: 2024-01-10
Enter value for end: 2024-01-12

Quarterly Review

-- Quarterly statistics review:
Enter value for start: 2024-01-01
Enter value for end: 2024-03-31

Integration with Statistics Management

Automated Monitoring

Schedule Validation

-- Validate automated schedules:
-- Compare actual vs. expected gathering times
-- Identify missing or failed collections
-- Monitor schedule adherence
-- Alert on significant deviations

Coverage Assessment

-- Assess statistics coverage:
-- Tables included in gathering cycles
-- Schemas covered by automation
-- Column-level statistics completeness
-- Histogram collection patterns

Performance Correlation

Query Performance Tracking

-- Correlate with performance:
-- Query performance before/after analysis
-- Execution plan stability
-- Optimizer cost accuracy
-- Response time improvements

Plan Quality Assessment

-- Assess plan quality:
-- Plan changes after statistics updates
-- Cost estimation accuracy
-- Join order optimization
-- Index usage improvements

Troubleshooting Applications

Missing Statistics

Gap Analysis

-- Identify statistics gaps:
-- Tables without recent analysis
-- Incomplete column coverage
-- Missing histogram statistics
-- Schema-level inconsistencies

Root Cause Investigation

-- Investigate missing statistics:
-- Failed automated jobs
-- Excluded tables or schemas
-- Resource constraints
-- Configuration issues

Performance Degradation

Statistics Correlation

-- Correlate performance with statistics:
-- Performance degradation timing
-- Last statistics gathering dates
-- Plan quality changes
-- Query regression patterns

Remediation Planning

-- Plan statistics remediation:
-- Priority tables for immediate analysis
-- Comprehensive gathering schedules
-- Resource allocation for gathering
-- Validation procedures

Best Practices Implementation

Monitoring Integration

Dashboard Creation

-- Create monitoring dashboards:
-- Statistics freshness indicators
-- Coverage metrics by schema
-- Gathering frequency trends
-- Performance correlation charts

Alert Configuration

-- Configure proactive alerts:
-- Tables missing statistics for X days
-- Failed statistics gathering jobs
-- Unusual gathering patterns
-- Performance degradation correlations

Process Optimization

Schedule Optimization

-- Optimize gathering schedules:
-- Balance resource usage
-- Minimize impact on operations
-- Ensure comprehensive coverage
-- Adapt to data change patterns

Resource Management

-- Manage gathering resources:
-- Parallel processing optimization
-- Memory allocation for large tables
-- I/O impact minimization
-- CPU usage balancing

Historical Trend Analysis

Long-term Patterns

Trend Identification

-- Identify long-term trends:
-- Increasing/decreasing gathering frequency
-- Seasonal patterns in data analysis
-- Schema growth impact on gathering
-- Resource requirement changes

Capacity Planning

-- Support capacity planning:
-- Resource usage trends
-- Gathering time requirements
-- Storage impact of statistics
-- Performance improvement tracking

Change Impact Assessment

System Changes

-- Assess impact of changes:
-- Database version upgrades
-- Hardware modifications
-- Application changes
-- Data volume growth

Process Improvements

-- Track process improvements:
-- Automation implementation effectiveness
-- Schedule optimization results
-- Resource utilization improvements
-- Quality metrics enhancement

Advanced Modifications

Detailed Column Analysis

-- Show individual columns (uncomment in script):
select u.name owner,
       o.name table_name,
       c.name column_name,
       h.timestamp analyze_date
  from sys.user$ u,
       sys.obj$ o,
       sys.col$ c,
       sys.hist_head$ h
 where h.obj# = c.obj#
   and h.col# = c.col#
   and c.obj# = o.obj#
   and o.owner# = u.user#
   and timestamp between '&start' and '&end'
 order by u.name, o.name, c.name;

Statistics Quality Metrics

-- Add quality metrics:
select owner, table_name, analyze_date,
       column_count,
       case when analyze_date > sysdate - 7 then 'FRESH'
            when analyze_date > sysdate - 30 then 'ACCEPTABLE'
            else 'STALE'
       end as freshness_status
  from (...); -- main query

dstatbig.sql - Statistics for large tables
dstattab.sql - Table statistics analysis
dtabcol.sql - Table column details
dobject.sql - Database object analysis

Security Considerations

Privilege Requirements

-- Required system privileges:
-- SELECT on SYS schema tables
-- Typically requires DBA role
-- Consider creating views for non-DBA access
-- Implement appropriate security controls

Access Control

-- Implement access controls:
-- Restrict to authorized personnel
-- Audit access to system tables
-- Consider data sensitivity
-- Follow security best practices

Summary

This script is essential for:

Statistics Monitoring - Tracking when columns were last analyzed for statistics
Maintenance Validation - Ensuring automated statistics gathering is working properly
Performance Troubleshooting - Correlating performance issues with statistics freshness
Capacity Planning - Understanding statistics gathering resource requirements
Compliance Documentation - Maintaining records of database maintenance activities