OraMatt: YABAOracle

Yet Another Blog About Oracle

Estimating Oracle Heat Map Storage

As database administrators, we’re constantly balancing feature enablement with resource management. Oracle’s Heat Map and Information Lifecycle Management (ILM) features provide powerful capabilities for automated data optimization, but like any monitoring feature, they consume storage space. The question every DBA asks is: “How much space will this actually use?”

In this post, I’ll walk you through a practical approach to estimating Heat Map storage consumption over time, based on real database analysis.

What is Oracle Heat Map?

Oracle Heat Map is the foundation for Advanced Data Optimization and ILM features introduced in Oracle Database 12c and enhanced in subsequent versions. When enabled, it automatically tracks:

  • Segment-level access patterns (read and write operations)
  • Modification timestamps for data blocks
  • Access frequency statistics at both segment and row levels

This tracking data enables Oracle to make intelligent decisions about compression, storage tiering, and data archiving based on actual usage patterns rather than arbitrary time-based rules.

Why Space Estimation Matters

Heat Map data is stored in the SYSAUX tablespace, specifically in these internal tables:

  • HEAT_MAP_STAT$ – Primary tracking table
  • WRI$_OPTSTAT_HISTGRM_HISTORY – Histogram history data
  • WRI$_OPTSTAT_HISTHEAD_HISTORY – Histogram header history
  • WRI$_HEATMAP_TOP_TABLESPACES – Top tablespace tracking

Note: These are not formally documented in user-facing documentation. Oracle provides documented views (DBA_HEAT_MAP_SEGMENT, V$HEAT_MAP_SEGMENT, etc.) and packages (DBMS_ILM_ADMIN, DBMS_HEAT_MAP) to access this data. The tables themselves are implementation details that may change between versions.

Before enabling Heat Map in production, you need to ensure:

  1. Retention policies align with your capacity planning
  2. SYSAUX has adequate free space for the tracking data
  3. Growth patterns are predictable and won’t cause space issues

The Estimation Methodology

Step 1: Understand Your Current Environment

First, gather baseline information about your database:

-- Check if Heat Map is enabled
SELECT name, value, description 
FROM v$parameter 
WHERE name = 'heat_map';

-- Count segments that will be tracked
SELECT 
    COUNT(DISTINCT owner || '.' || segment_name) as total_segments,
    COUNT(*) as total_segment_instances,
    ROUND(SUM(bytes)/1024/1024/1024, 2) as total_db_size_gb
FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'XDB', 
                    'MDSYS', 'CTXSYS', 'ORDSYS', 'ORDDATA', 'WMSYS');


Step 2: Measure Current Heat Map Storage

Calculate the current space consumed by Heat Map structures:

-- Total Heat Map storage consumption
SELECT 
    ROUND(SUM(bytes)/1024/1024, 2) as total_heatmap_mb
FROM dba_segments
WHERE segment_name LIKE '%HEAT%MAP%' 
   OR segment_name LIKE '%WRI$_HEAT%'
   OR segment_name IN ('WRI$_OPTSTAT_HISTGRM_HISTORY', 
                       'WRI$_OPTSTAT_HISTHEAD_HISTORY', 
                       'HEAT_MAP_STAT$');

Step 3: Check Retention Settings

Heat Map data doesn’t grow indefinitely, it follows AWR retention policies:

-- Check AWR retention (controls Heat Map retention)
SELECT 
    'AWR_RETENTION' as setting,
    EXTRACT(DAY FROM retention) as retention_days,
    'Heat Map data older than this is purged' as description
FROM dba_hist_wr_control
WHERE dbid = (SELECT dbid FROM v$database);

Step 4: Calculate Growth Rate

The daily growth rate depends on your database activity:
Per-Segment Storage Requirements:

  • Histogram details: Additional 500-1000 bytes if detailed tracking enabled
  • Base tracking entry: ~200-500 bytes per segment
  • Daily snapshot overhead: ~1-2 KB per active segment

Formula:

Daily Growth (MB) = (Number_of_Segments × Bytes_Per_Segment) / (1024 * 1024)

Where:
- Bytes_Per_Segment = 500 bytes (conservative estimate)
- 1024 * 1024 = 1,048,576 bytes per MB (binary conversion)
- Can increase to 1500-2000 bytes for very active segments

Real-World Example

Let’s walk through a real estimation for a database with:

  • 403 segments (total instances including partitions)
  • 4.65 GB total user data
  • 8-day AWR retention (default)
  • 35 MB initial Heat Map storage

Calculation:

-- Estimated daily growth
Daily_Growth = (403 segments × 500 bytes) / (1024 * 1024)
             = 201,500 / 1,048,576
             = 0.19 MB per day

-- For N days (before retention kicks in):
Total_Space(N) = 35 MB + (0.19 MB × N)

Projection Table:

SELECT
    days as "Period (Days)",
    ROUND(35 + (0.19 * days), 2) as "Estimated Space (MB)",
    ROUND((35 + (0.19 * days))/1024, 2) as "Estimated Space (GB)",
    CASE 
        WHEN days <= 8 THEN 'Growing'
        ELSE 'Steady-state (auto-purged)'
    END as "Status"
FROM (
    SELECT 7 as days FROM dual UNION ALL
    SELECT 30 FROM dual UNION ALL
    SELECT 90 FROM dual UNION ALL
    SELECT 180 FROM dual UNION ALL
    SELECT 365 FROM dual
)
ORDER BY days;

Results:

Period (Days)Estimated Space (MB)Estimated Space (GB)Status
736.330.04Growing
3040.700.04Steady-state
9052.100.05Steady-state
18069.200.07Steady-state
365104.350.10Steady-state


Key Insight: Steady-State Storage

With the default 8-day retention, your Heat Map storage will stabilize around 35-45 MB. Once retention kicks in, old data is automatically purged, preventing unbounded growth.

Factors That Affect Growth

Your actual growth may vary based on:

1. Database Activity Level

  • High write activity: More frequent tracking updates
  • Many DML operations: Increases histogram detail
  • Batch processing: Spikes in tracking data during ETL windows

2. Segment Characteristics

  • Partitioned tables: Each partition tracked separately (increases segment count)
  • LOB segments: Tracked independently from their parent tables
  • Index segments: Also tracked individually

3. Access Patterns

  • Frequently accessed segments: Generate more detailed statistics
  • Dormant data: Minimal tracking overhead
  • Random vs. sequential access: Affects histogram granularity

4. Compression Settings

If SYSAUX tablespace has compression enabled:

  • Advanced compression: ~60-70% space savings
  • Basic compression: ~30-40% space savings

Creating a Custom Estimation Function

Here’s a reusable SQL function to estimate Heat Map space for your specific environment:

CREATE OR REPLACE FUNCTION estimate_heatmap_space(
    p_days NUMBER,
    p_segment_count NUMBER DEFAULT NULL,
    p_bytes_per_segment NUMBER DEFAULT 500
) RETURN NUMBER IS
    v_current_base NUMBER;
    v_segment_count NUMBER;
    v_daily_growth NUMBER;
    v_estimated_space NUMBER;
BEGIN
    -- Get current Heat Map base size
    SELECT ROUND(SUM(bytes)/1024/1024, 2)
    INTO v_current_base
    FROM dba_segments
    WHERE segment_name LIKE '%HEAT%MAP%' 
       OR segment_name LIKE '%WRI$_HEAT%'
       OR segment_name IN ('WRI$_OPTSTAT_HISTGRM_HISTORY', 
                           'WRI$_OPTSTAT_HISTHEAD_HISTORY', 
                           'HEAT_MAP_STAT$');
    
    -- Get segment count if not provided
    IF p_segment_count IS NULL THEN
        SELECT COUNT(*) 
        INTO v_segment_count
        FROM dba_segments 
        WHERE owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'XDB');
    ELSE
        v_segment_count := p_segment_count;
    END IF;
    
    -- Calculate daily growth in MB
    v_daily_growth := (v_segment_count * p_bytes_per_segment) / (1024 * 1024);
    
    -- Calculate total estimated space
    v_estimated_space := v_current_base + (v_daily_growth * p_days);
    
    RETURN ROUND(v_estimated_space, 2);
END;
/

-- Usage examples:
SELECT estimate_heatmap_space(30) as "30-Day Estimate (MB)" FROM dual;
SELECT estimate_heatmap_space(365, 1000) as "365-Day with 1000 segments (MB)" FROM dual;
SELECT estimate_heatmap_space(90, NULL, 750) as "90-Day with 750 bytes/segment (MB)" FROM dual;

Monitoring Heat Map Growth

Set up a monitoring query to track actual growth over time:

-- Create a tracking table
CREATE TABLE heatmap_growth_log (
    log_date DATE,
    total_size_mb NUMBER,
    segment_count NUMBER,
    retention_days NUMBER,
    PRIMARY KEY (log_date)
);

-- Daily collection job
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'HEATMAP_SPACE_MONITOR',
        job_type        => 'PLSQL_BLOCK',
        job_action      => q'{
            INSERT INTO heatmap_growth_log
            SELECT 
                SYSDATE,
                (SELECT ROUND(SUM(bytes)/1024/1024, 2)
                 FROM dba_segments
                 WHERE segment_name LIKE '%HEAT%MAP%' 
                    OR segment_name IN ('HEAT_MAP_STAT$')),
                (SELECT COUNT(*) FROM dba_heat_map_segment),
                (SELECT EXTRACT(DAY FROM retention) 
                 FROM dba_hist_wr_control 
                 WHERE dbid = (SELECT dbid FROM v$database))
            FROM dual;
            COMMIT;
        }',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=1',
        enabled         => TRUE
    );
END;
/

-- Analyze growth trends
SELECT 
    log_date,
    total_size_mb,
    total_size_mb - LAG(total_size_mb) OVER (ORDER BY log_date) as daily_growth_mb,
    segment_count
FROM heatmap_growth_log
ORDER BY log_date DESC
FETCH FIRST 30 ROWS ONLY;

Some Useful Recommendations

1. Pre-Enablement Checklist

Before enabling Heat Map in production:

-- Check SYSAUX free space (should have at least 500 MB free)
SELECT 
    tablespace_name,
    ROUND(SUM(bytes)/1024/1024, 2) as used_mb,
    ROUND((SELECT SUM(bytes)/1024/1024 
           FROM dba_free_space 
           WHERE tablespace_name = 'SYSAUX'), 2) as free_mb,
    ROUND((SELECT SUM(bytes)/1024/1024 
           FROM dba_data_files 
           WHERE tablespace_name = 'SYSAUX'), 2) as total_mb
FROM dba_segments
WHERE tablespace_name = 'SYSAUX'
GROUP BY tablespace_name;

2. Adjust Retention Based on Needs

Balance storage costs with historical data requirements:

-- Reduce retention to 7 days (saves ~10-15% space)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 7*24*60);

-- Increase to 30 days for longer trend analysis (requires more space)
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 30*24*60);

3. Control ILM Policy Evaluation

While you cannot disable Heat Map tracking for specific tables, you can control ILM policy evaluation for large databases:

-- Disable all ILM policies on a specific table
ALTER TABLE large_fact_table ILM DISABLE_ALL;

-- Delete all ILM policies on a specific table
ALTER TABLE large_fact_table ILM DELETE_ALL;

-- Disable a specific ILM policy
ALTER TABLE large_fact_table ILM DISABLE POLICY P1;

-- Re-enable all policies
ALTER TABLE large_fact_table ILM ENABLE_ALL;

Note: Heat Map tracking itself operates at the system level. You cannot disable Heat Map for individual tables. However, you can control which tables have active ILM policies that consume Heat Map data.

4. Regular Maintenance

-- Quarterly: Check for any bloat in SYSAUX
SELECT 
    s.segment_name,
    ROUND(s.bytes/1024/1024, 2) as size_mb,
    t.num_rows,
    ROUND(s.bytes/1024/1024/NULLIF(t.num_rows, 0), 2) as mb_per_k_rows
FROM dba_segments s
JOIN dba_tables t ON (s.owner = t.owner AND s.segment_name = t.table_name)
WHERE s.segment_name LIKE 'WRI$_%'
  AND s.segment_type = 'TABLE'
ORDER BY s.bytes DESC
FETCH FIRST 10 ROWS ONLY;

-- Consider reorganization if tables are fragmented
-- (Consult Oracle Support before running)
EXEC DBMS_SPACE.SPACE_USAGE(...);

Conclusion

Estimating Oracle Heat Map storage consumption is straightforward once you understand the key factors:

  1. Base size: ~35-50 MB for typical databases
  2. Daily growth: ~0.15-0.25 MB per day per 1000 segments
  3. Steady-state: Stabilizes at retention period × daily growth
  4. Space savings: 60-70% possible with compression

For most databases, Heat Map storage is negligible, typically under 100 MB even with extended retention. The benefits of automated data optimization and ILM far outweigh the minimal storage overhead.

Key Takeaway: Don’t let space concerns prevent you from enabling Heat Map. With proper monitoring and the estimation techniques in this post, you can confidently plan for and manage Heat Map storage in any Oracle environment.

Additional Resources

Leave a comment

Navigation

About

I’m Matt and I do Oracle things.