******************************************************************
* 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