How to Check Statistics in Oracle Database

Introduction

Understanding how to check statistics in Oracle Database is essential for database performance tuning and optimization. Oracle Database provides a range of statistics including table, column, index, and histogram statistics. This guide will walk you through the process of checking these statistics and utilizing them for improving performance and resolving issues.

Importance of Checking Statistics in Oracle Database

1. Table Statistics

Table statistics provide an overview of the physical and logical attributes of tables and their partitions. Key metrics include the number of rows, block usage, average row length, and cache utilization. These statistics are vital for:

  • Understanding Table Utilization: By analyzing num_rows and blocks, administrators can gauge how much space a table occupies and how frequently it is accessed.
  • Identifying Storage Issues: Metrics like empty_blocks and avg_space_freelist_blocks highlight fragmentation and inefficiencies in storage allocation.
  • Performance Tuning: avg_cached_blocks and avg_cache_hit_ratio indicate the efficiency of data retrieval from the buffer cache, crucial for optimizing read performance.

SQL for Table Statistics:


SELECT
    table_name,
    partition_name,
    partition_position,
    subpartition_name,
    subpartition_position,
    object_type,
    num_rows,
    blocks,
    empty_blocks,
    avg_space,
    chain_cnt,
    avg_row_len,
    avg_space_freelist_blocks,
    num_freelist_blocks,
    avg_cached_blocks,
    avg_cache_hit_ratio,
    im_imcu_count,
    im_block_count,
    im_stat_update_time,
    scan_rate,
    sample_size,
    to_char(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') last_analyzed,
    global_stats,
    user_stats,
    stattype_locked,
    stale_stats,
    scope
FROM
    user_tab_statistics
ORDER BY
    table_name,
    partition_position,
    subpartition_position;
            
Oracle table statistics

2. Column Statistics

Column statistics provide deeper insights into individual columns within tables, focusing on data distribution and uniqueness. They include:

  • Cardinality: num_distinct gives the number of unique values in a column, crucial for query optimization and indexing decisions.
  • Data Distribution: density provides information about the distribution of data values, aiding in understanding data skewness and optimal indexing strategies.
  • Histograms: Visual representations (histogram) of data distribution patterns, enabling the Oracle optimizer to make informed decisions about query execution plans.

SQL for Table Column Statistics:


SELECT
    table_name,
    column_name,
    num_distinct,
    low_value,
    high_value,
    density,
    num_nulls,
    num_buckets,
    to_char(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') last_analyzed,
    sample_size,
    global_stats,
    user_stats,
    notes,
    avg_col_len,
    histogram,
    scope
FROM
    user_tab_col_statistics
ORDER BY
    table_name,
    column_name;
        
Oracle column statistics

3. Index Statistics

Index statistics focus on the performance and efficiency of indexes within the database. They include:

  • Clustering Factor: Indicates how well data is ordered with respect to the index key, impacting the efficiency of index range scans.
  • Leaf Blocks: Number of leaf-level blocks in the index structure, influencing the depth and traversal speed of index scans.
  • Distinct Keys: Number of unique keys in the index, aiding in understanding index selectivity and cardinality estimation.

SQL for Index Statistics:


SELECT
    table_name,
    index_name,
    table_owner,
    partition_name,
    partition_position,
    subpartition_name,
    subpartition_position,
    object_type,
    blevel,
    leaf_blocks,
    distinct_keys,
    avg_leaf_blocks_per_key,
    avg_data_blocks_per_key,
    clustering_factor,
    num_rows,
    avg_cached_blocks,
    avg_cache_hit_ratio,
    sample_size,
    to_char(last_analyzed, 'YYYY-MM-DD HH24:MI:SS') last_analyzed,
    global_stats,
    user_stats,
    stattype_locked,
    stale_stats,
    scope
FROM
    user_ind_statistics
ORDER BY
    table_name,
    index_name;
        

4. Histogram Statistics

Histogram statistics provide detailed distribution of column values. Key metrics include:

  • Endpoint Values: Define the boundaries of histogram buckets.
  • Repeat Counts: Show the frequency of each endpoint value, assisting in data analysis.

SQL Query to Retrieve Histogram Statistics:


SELECT
    table_name,
    column_name,
    endpoint_number,
    endpoint_value,
    endpoint_repeat_count
FROM
    user_tab_histograms
ORDER BY
    table_name,
    column_name;
        

Using Statistics to Identify Performance Issues

Checking statistics in Oracle Database is crucial for identifying and addressing performance issues:

  • Query Performance: Analyze avg_cache_hit_ratio and clustering_factor to identify optimization opportunities.
  • Data Distribution: Use density and num_distinct to detect data skew and adjust indexing strategies.
  • Index Efficiency: Evaluate leaf_blocks and distinct_keys to assess index performance.
  • Histogram Data: Accurate histogram data ensures effective query planning.

Conclusion

Regularly checking and analyzing statistics in Oracle Database helps maintain optimal performance and troubleshoot issues. By understanding and applying the insights from table, column, index, and histogram statistics, you can enhance database performance and ensure efficient operation.

For more information on optimizing Oracle Database, check out our other guides and resources.


Related content



Rate Your Experience

: 89 : 1


Last updated in July, 2024

Online Tests
Read more

Cloud Technology
Read more

Oracle Database
Read more

MSSQL Database
Read more

PostGres Database
Read more

Linux
Read more

ASP/C#
Read more

Quick Access