What is Gathering statistics in oracle DB ?

Gathering statistics in an Oracle database is a critical task for maintaining the performance and efficiency of the database. Statistics provide the Oracle optimizer with the necessary information to make informed decisions about the execution plans for SQL queries. Here’s a comprehensive guide on gathering statistics in an Oracle database:

Why Gather Statistics?

1. Optimizer Efficiency: The Oracle optimizer uses statistics to determine the most efficient way to execute a query.
2. Performance Improvement: Accurate statistics help in improving the performance of SQL queries by enabling the optimizer to choose the best execution plan.
3. Data Changes: As data changes over time (inserts, updates, deletes), statistics need to be updated to reflect these changes.

When to Gather Statistics?

• After significant data changes (bulk inserts, updates, or deletes).
• After schema changes (adding or dropping indexes, partitions, etc.).
• Periodically, as part of regular database maintenance.

How to Gather Statistics?

Oracle provides several methods to gather statistics, including using the DBMS_STATS package, which is the recommended approach.

Using DBMS_STATS Package

1. Gathering Statistics for a Table:

• SQL
• EXEC DBMS_STATS.GATHER_TABLE_STATS('schema_name', 'table_name');

1. This command gathers statistics for a specific table in a specified schema.
2. Gathering Statistics for a Schema:

• SQL
• EXEC DBMS_STATS.GATHER_SCHEMA_STATS('schema_name');

1. This command gathers statistics for all objects in a specified schema.
2. Gathering Statistics for the Entire Database:

• SQL
• EXEC DBMS_STATS.GATHER_DATABASE_STATS;

1. This command gathers statistics for all objects in the database.
2. Gathering Statistics for Indexes:

• SQL
• EXEC DBMS_STATS.GATHER_INDEX_STATS('schema_name', 'index_name');

3. This command gathers statistics for a specific index.

Gathering Statistics with Options:

You can also specify additional options such as the degree of parallelism, estimate percentage, and method options.

• SQL
• EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname => 'schema_name', tabname => 'table_name', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => DBMS_STATS.DEFAULT_DEGREE );

estimate_percent: Specifies the percentage of rows to estimate. DBMS_STATS.AUTO_SAMPLE_SIZE lets Oracle decide the best sample size.

• method_opt: Specifies how column statistics are collected. 'FOR ALL COLUMNS SIZE AUTO' lets Oracle decide the best method.
• degree: Specifies the degree of parallelism. DBMS_STATS.DEFAULT_DEGREE lets Oracle decide the best degree.

Automating Statistics Gathering

Oracle provides an automated task that gathers statistics during maintenance windows. This task is enabled by default and can be managed using the Oracle Scheduler.

Checking the Status of the Automated Task

SELECT client_name, status
FROM dba_autotask_client
WHERE client_name = 'auto optimizer stats collection';

Enabling/Disabling the Automated Task

To disable the automated statistics gathering:

EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => NULL
);

To enable the automated statistics gathering:

EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => NULL
);

Viewing Statistics

You can view the gathered statistics using various data dictionary views:

• DBA_TABLES: Contains table statistics.
• DBA_TAB_COLUMNS: Contains column statistics.
• DBA_INDEXES: Contains index statistics.
• DBA_TAB_STATISTICS: Contains statistics history.

Example: Viewing Table Statistics

SELECT table_name, num_rows, blocks, empty_blocks, avg_row_len
FROM dba_tables
WHERE owner = 'schema_name' AND table_name = 'table_name';

Summary

Gathering statistics in an Oracle database is essential for maintaining optimal query performance. By using the DBMS_STATS package, you can gather statistics for tables, schemas, indexes, and the entire database. Automating this process ensures that statistics are kept up-to-date, allowing the Oracle optimizer to make the best decisions for query execution plans. Regularly monitoring and updating statistics is a best practice for database maintenance.