statistics refer to information about the data in tables and indexes that the database optimizer uses to generate efficient execution plans for SQL queries. These statistics include details like the number of rows in a table, the distribution of values in columns, and the index structure. The optimizer uses this information to determine the best way to access and process data when executing SQL statements.

Table statistics

Number of rows

Number of blocks

Average row length

Column statistics

Number of distinct values (NDV) in column

Number of nulls in column

Data distribution (histogram)

Index statistics

Number of leaf blocks

Levels

Clustering factor

System statistics

I/O performance and utilization

CPU performance and utilization

When a SQL statement is executed, the database must convert the query into an execution plan and choose the best way to retrieve the data. These execution plans are computed by the Oracle cost-based SQL optimizer.

We know that optimizers generate the excitation plan and optimizers use these statistics for tables and indexes to generate effective plans. So it's important to have statistics on objects.

Statistics is utilised by the database optimizer to make informed decisions about how to execute SQL queries efficiently.

System statistics are statistics about CPU speed and IO performance, it enables the CBO to effectively cost each operation in an execution plan.

Oracle highly recommends gathering system statistics during a representative workload, ideally at peak workload time, in order to provide more accurate CPU/IO cost estimates to the optimizer.

You only have to gather system statistics once.

There are two types of system statistics (NOWORKLOAD statistics & WORKLOAD statistics):

an index should be rebuilt when it has become fragmented or inefficient due to factors like excessive deletions, updates, or insertions. Here's a short explanation with a use case:

An index should be rebuilt when:

It has high levels of fragmentation, causing performance degradation.

There have been significant data changes (deletions, updates, or insertions) that have impacted index efficiency.

Oracle's advisory tools recommend index rebuilding based on performance analysis.

SQL> ANALYZE INDEX IDX_EMP_ACCT VALIDATE STRUCTURE;

SQL> SELECT name, height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;

NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROW

---------------------- ----------- ---------- ---------- ----------------

DX_EMP_ACCT 2 1 3 6

1 row selected.

There are two rules of thumb to help determine if the index needs to be rebuilt.

1) If the index has height greater than four, rebuild the index.

2) The deleted leaf rows should be less than 20%.