****************************************************************** * Description: Knowledge of Cost-Based Optimizer [CBO] statistics * Date: 04:26 PM EST, 07/15/2017 ****************************************************************** <1> Cost-Based Optimizer Statistics: | |__ o. As database administrator, you can generate statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions. | The cost-based optimization approach uses these statistics to calculate the selectivity of predicates and to estimate the cost of each execution plan. | Selectivity is the fraction of rows in a table that the SQL statement's predicate chooses. | The optimizer uses the selectivity of a predicate to estimate the cost of a particular access method and to determine the optimal join order and join method. | |__ o. You should gather statistics periodically for objects where the statistics become stale over time because of changing data volumes or changes in column values. | New statistics should be gathered after a schema object's data or structure are modified in ways that make the previous statistics inaccurate. | For example, after loading a significant number of rows into a table, collect new statistics on the number of rows. | After updating data in a table, you do not need to collect new statistics on the number of rows, but you might need new statistics on the average row length. | |__ o. Statistics generated include the following: | |__ a) Table statistics: | o. Number of rows | o. Number of blocks | o. Average row length | |__ b) Column statistics: | o. Number of distinct values (NDV) in column | o. Number of nulls in column | o. Data distribution (histogram) | |__ c) Index statistics: | o. Number of leaf blocks | o. Levels | o. Clustering factor | |__ d) System statistics: o. I/O performance and utilization o. CPU performance and utilization <2> Package DBMS_STATS to gather statistics: | |__ SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ( ownname => 'TABLE_OWNER', tabname => 'TABLE_NAME', cascade => true, estimate_percent => 10, method_opt => 'for all indexed columns size 1', granularity => 'ALL', degree => 4 ); SQL> EXEC dbms_stats.gather_schema_stats('SCHEMA_NAME', cascade=>TRUE); <3> Reference: | |__ o. https://oracle-base.com/articles/misc/cost-based-optimizer-and-database-statistics