Optimizer Statistics
Published by : Obay Salah , November 20, 2024
There are many ways to execute SQL statements, and the process of choosing an execution plan for SQL statements is highly influential on performance. The Optimizer heavily depends on statistics to evaluate the effectiveness of execution plans for SQL statements and to determine the execution plan. For performance to be optimal, these statistics must be accurate. There are several types of statistics, with the note that these statistics are related only to SQL, not to PL/SQL.
Object Statistics: We can perform Analyzing on the table to generate statistics about the table that will be used by the Optimizer. These statistics appear in the DBA_TABLES view and include:
1- The number of rows in the table. 2- The number of blocks in the table. 3- The amount of free space in the used blocks. 4- The average row length.
We can also perform Analyzing on the columns that make up the table to generate statistics. These statistics appear in DBA_TAB_COLUMNS and include:
1- The number of distinct values. 2- The highest and lowest values. 3- The number of NULL values. 4- The average column length.
When Analyzed is executed for the table, it also inspects the indexes associated with the table. Statistics for these indexes are shown in the DBA_INDEXES view. These statistics are stored in the Data Dictionary, and they provide the Optimizer with the information it needs to make vital decisions about the best way to execute SQL statements. If these statistics are missing or incorrect, performance will degrade significantly. It is also possible that statistics for indexes may appear in the INDEX_STATS view.
Comments
no comment yet!