Optimizer Statistics in Oracle

What are Optimizer Statistics? 

Optimizer statistics are a collection of data that describe the database, and the objects in the database. These statistics are used by the Optimizer to choose the best execution plan for each SQL statement. 

Statistics are stored in the data dictionary, and can be accessed using data dictionary views such as USER_TAB_STATISTICS. 

Optimizer statistics are different from the performance statistics visible through V$ views. 

The information in the V$ views relates to the state of the system and the SQL workload executing on it.

Table and Column Statistics 

Table statistics include information on the number of rows in the table, the number of data blocks used for the table, as well as the average row length in the table. 

Oracle calculate average row length for a table:-

select avg_row_len,avg_space from dba_tables where owner = 'SCOTT' and table_name = 'EMP';

The Optimizer uses this information, in conjunction with other statistics, to compute the cost of various operations in an execution plan, and to estimate the number of rows the operation will produce. 

For example, the cost of a table access is calculated using the number of data blocks combined with the value of the parameter DB_FILE_MULTIBLOCK_READ_COUNT. 

You can view table statistics in the dictionary view USER_TAB_STATISTICS.

Column statistics include information on the number of distinct values in a column (NDV) as well as the minimum and maximum value found in the column. 

You can view column statistics in the dictionary view USER_TAB_COL_STATISTICS. 

The Optimizer uses the column statistics information in conjunction with the table statistics (number of rows) to estimate the number of rows that will be returned by a SQL operation. 

For example, if a table has 100 records, and the table access evaluates an equality predicate on a column that has 10 distinct values, then the Optimizer, assuming uniform data distribution, estimates the cardinality to be the number of rows in the table divided by the number of distinct values for the column or 100/10 = 10

Additional column statistics Basic table and column statistics tell the optimizer a great deal but they don’t provide a mechanism to tell the Optimizer about the nature of the data in the table or column. 

For example, these statistics can’t tell the Optimizer if there is a data skew in a column, or if there is a correlation between columns in a table. Information on the nature of the data can be provided to the Optimizer by using extensions to basic statistics like, histograms, column groups, and expression statistics.

DBMS_STATS Gather Statistics of Schema, Tables, Indexes

DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. This package is concerned with optimizer statistics only.

dbms_stats is essential to good SQL performance, and it should always be used before adjusting any of the Oracle optimizer initialization parameters.

Syntax:
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname,estimate_percent, block_sample , method_opt,degree,granularity,cascade,stattab, statid,options,statown ,no_invalidate, gather_temp,gather_fixed);

Code examples:
exec dbms_stats.gather_schema_stats('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);

exec dbms_stats.gather_schema_stats(ownname=>'SCOTT', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);

exec dbms_stats.gather_schema_stats(ownname => 'SCOTT', estimate_percent => 25);

exec dbms_stats.gather_table_stats('SCOTT', 'EMPLOYEES');

exec dbms_stats.gather_index_stats('SCOTT', 'EMPLOYEES_PK');

exec dbms_stats.delete_schema_stats('SCOTT');

Link:-

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/gathering-optimizer-statistics.html#GUID-245F23B2-24AF-44D8-9F12-99FD1215E878