Cached Tables Analysis (dtcache.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides Oracle database administration functionality via the dtcache.sql script.

The Script

rem  dtcache.sql
rem
ttitle 'Cached Tables'
rem
col owner format a12 heading 'OWNER'
col table_name format a30 heading 'TABLE NAME'
col num_rows format 9999999 heading 'ROWS'
col blocks format 99999 heading 'BLOCKS'
rem
select owner, table_name, num_rows, blocks
  from sys.dba_tables
 where cache = '    Y'
   and owner like upper('&owner')
   and table_name like upper('&table')
 order by owner, table_name;

What This Script Does

This script identifies tables that have been configured with the CACHE option, which instructs Oracle to place blocks from these tables at the Most Recently Used (MRU) end of the buffer cache LRU list instead of the Least Recently Used (LRU) end. This makes cached tables more likely to remain in memory longer, improving performance for frequently accessed small lookup tables.

Key Features

• Cache Status Identification: Shows tables configured with CACHE option
• Size Analysis: Displays row count and block count for cached tables
• Flexible Filtering: Filter by owner and table name patterns
• Resource Assessment: Helps evaluate buffer cache resource allocation
• Performance Planning: Assists in cache strategy optimization

Usage

@dtcache.sql

Input Parameters:

• Owner: Schema owner name or pattern (use % for all owners)
• Table: Table name or pattern (use % for all tables)

Required Privileges

SELECT on SYS.DBA_TABLES

Sample Output

                                        Cached Tables

OWNER        TABLE NAME                         ROWS BLOCKS
------------ ------------------------------ ------- ------
HR           COUNTRIES                           25      1
HR           DEPARTMENTS                         27      1
HR           JOB_HISTORY                         10      1
HR           JOBS                                19      1
HR           LOCATIONS                           23      1
HR           REGIONS                              4      1
LOOKUP       CURRENCY_CODES                     195      2
LOOKUP       STATUS_CODES                        45      1
LOOKUP       TIME_ZONES                         372      3
SALES        PRODUCT_CATEGORIES                  12      1
SALES        SALES_TERRITORIES                   8      1
SYSTEM       HELP_TOPIC                        234      5

Key Output Columns

• OWNER: Schema that owns the cached table
• TABLE NAME: Name of the table configured with CACHE option
• ROWS: Number of rows in the table (from statistics)
• BLOCKS: Number of blocks allocated to the table

Understanding Table Caching

Cache Mechanism

Normal Table Behavior

-- Without CACHE option:
-- Blocks placed at LRU end of buffer cache
-- Quickly aged out if not accessed again
-- Suitable for large tables or one-time scans
-- Prevents buffer cache pollution

Cached Table Behavior

-- With CACHE option:
-- Blocks placed at MRU end of buffer cache
-- Remain in cache longer
-- Higher likelihood of cache hits
-- Suitable for small, frequently accessed tables

Buffer Cache Impact

Positive Effects

-- Benefits of caching:
-- Improved response time for cached tables
-- Reduced I/O for frequently accessed data
-- Better performance for lookup tables
-- Increased buffer cache hit ratio for these tables

Potential Issues

-- Risks of excessive caching:
-- Buffer cache resource contention
-- Reduced space for other important data
-- Possible cache pollution if tables grow large
-- Memory pressure on heavily loaded systems

Ideal Candidates for Caching

Small Lookup Tables

Characteristics

-- Best candidates:
-- Size: < 100 blocks (typically < 1MB)
-- Access pattern: Frequently read
-- Data volatility: Relatively static
-- Usage: Referenced by many queries

Examples

-- Common lookup table types:
-- Country/region codes
-- Status and type codes
-- Currency and exchange rates
-- Department and organization hierarchies
-- Configuration parameters

Reference Data

Static Configuration

-- Tables that rarely change:
-- System parameters
-- Code tables and enumerations
-- Help and documentation tables
-- Validation rules and constraints

Dimension Tables (Small)

-- Small dimension tables in data warehouses:
-- Time dimensions (if small)
-- Product categories
-- Customer segments
-- Geographic hierarchies

Analysis and Assessment

Cache Appropriateness Review

Size Analysis

-- Evaluate table sizes:
-- Tables > 1000 blocks may be too large to cache
-- Consider percentage of buffer cache consumed
-- Monitor growth trends for cached tables
-- Assess total cache footprint

Access Pattern Analysis

-- Review access characteristics:
-- Query frequency against cached tables
-- Join patterns involving cached tables
-- Read vs. write ratios
-- Peak usage periods

Buffer Cache Impact Assessment

Cache Utilization

-- Monitor buffer cache effects:
SELECT
  'Cached Tables' category,
  SUM(blocks) * 8192 / 1024 / 1024 total_mb
FROM dba_tables
WHERE cache = '    Y'
UNION ALL
SELECT
  'Total Buffer Cache',
  value / 1024 / 1024
FROM v$parameter
WHERE name = 'db_cache_size';

Performance Metrics

-- Check buffer cache hit ratios:
-- Overall cache hit ratio
-- Hit ratios specifically for cached objects
-- I/O patterns for cached vs. non-cached tables
-- Response time improvements

Common Use Cases

1. Performance Optimization

   • Identify tables configured for caching
   • Assess cache strategy effectiveness
   • Optimize buffer cache utilization
   • Improve query response times

2. Buffer Cache Management

   • Monitor cached table resource consumption
   • Plan buffer cache sizing
   • Prevent cache pollution
   • Balance cache allocation

3. Application Performance Tuning

   • Optimize lookup table access
   • Improve join performance
   • Reduce I/O for reference data
   • Enhance user experience

4. System Resource Planning

   • Assess memory requirements
   • Plan for table growth
   • Optimize cache strategies
   • Monitor resource utilization

Cache Configuration Management

Adding Cache Option

-- Enable caching for appropriate tables:
ALTER TABLE hr.countries CACHE;
ALTER TABLE lookup.status_codes CACHE;

-- Verify cache setting:
SELECT table_name, cache
FROM dba_tables
WHERE owner = 'HR' AND table_name = 'COUNTRIES';

Removing Cache Option

-- Disable caching for inappropriate tables:
ALTER TABLE large_transaction_table NOCACHE;

-- Use NOCACHE for:
-- Large tables (> 1000 blocks)
-- Infrequently accessed tables
-- Tables with mostly sequential scans
-- Tables causing cache contention

Advanced Analysis

Cache Effectiveness Assessment

-- Measure cache hit improvements:
-- Compare buffer hit ratios before/after caching
-- Monitor I/O reduction for cached tables
-- Assess query response time improvements
-- Track buffer cache efficiency metrics

Table Growth Monitoring

-- Monitor cached table sizes over time:
-- Track block count growth
-- Monitor row count increases
-- Assess continued appropriateness for caching
-- Plan for cache strategy adjustments

Cache Strategy Optimization

-- Systematic cache evaluation:
-- Regular review of cached table list
-- Size threshold enforcement
-- Access pattern validation
-- Performance impact assessment

Best Practices for Table Caching

Selection Criteria

Size Guidelines

-- Recommended size limits:
-- < 100 blocks: Excellent candidates
-- 100-500 blocks: Good candidates (monitor closely)
-- 500-1000 blocks: Marginal candidates (careful evaluation)
-- > 1000 blocks: Generally not recommended

Access Pattern Requirements

-- Ideal access patterns:
-- High read frequency (multiple times per minute)
-- Low write frequency (static or slowly changing)
-- Referenced by many different queries
-- Used in joins with larger tables

Monitoring and Maintenance

Regular Reviews

-- Monthly cache assessment:
-- Review cached table sizes
-- Analyze access patterns
-- Monitor buffer cache utilization
-- Assess performance impact

Maintenance Actions

-- Ongoing cache management:
-- Remove cache from tables that have grown too large
-- Add cache to newly identified candidates
-- Monitor for cache pollution issues
-- Adjust cache strategy based on workload changes

Troubleshooting Cache Issues

Buffer Cache Pressure

Symptoms

-- Indicators of cache pressure:
-- Declining buffer cache hit ratios
-- Increased I/O for non-cached objects
-- Memory allocation failures
-- Performance degradation

Resolution

-- Address cache pressure:
-- Review cached table sizes and remove inappropriate ones
-- Increase buffer cache size if justified
-- Implement multiple buffer pools
-- Optimize query access patterns

Ineffective Caching

Identification

-- Signs of ineffective caching:
-- Cached tables still experiencing high I/O
-- No improvement in query response times
-- Cache misses for supposedly cached data
-- Wasted buffer cache space

Optimization

-- Improve cache effectiveness:
-- Verify table access patterns
-- Check for competing cache demands
-- Review buffer pool configuration
-- Consider alternative optimization strategies

Related Scripts

• vbpcache.sql - Buffer pool cache analysis
• vbhstat.sql - Buffer hit statistics
• dtable.sql - Complete table storage analysis
• dstatbig.sql - Large table statistics

Best Practices

1. Strategic Caching

   • Cache only small, frequently accessed tables
   • Regularly review and adjust cache settings
   • Monitor buffer cache resource utilization
   • Consider access patterns and table volatility

2. Performance Monitoring

   • Track cache effectiveness metrics
   • Monitor buffer cache hit ratios
   • Assess query performance improvements
   • Document cache strategy decisions

3. Maintenance Discipline

   • Regular review of cached table appropriateness
   • Monitor table growth and adjust cache settings
   • Prevent cache pollution from oversized tables
   • Maintain cache configuration documentation