[Oracle] statistics and dbms_stats packages

[Oracle] statistical information and dbms_stats Package 1. Roles of Statistical Information Oracle CBO-based optimizer relies heavily on statistical information when generating execution plans, you can think of CBO as a complex mathematical model, and statistical information is its most important input, and execution plan is output. If the input is not accurate, can the output be accurate? All, whether the statistics are timely and effective has a critical impact on the performance of the execution plan. 2. The dbms_stats package uses the dbms_stats package in Oracle to Analyze statistical information (the Analyze command is out of date and is not recommended). The usage of this package is described in the official documentation ( http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_stats.htm#i1036461 ). Here are some of the most common examples. 1) gather_table_stats [SQL] partition (ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT NULL, block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1 ', degree number default null, granularity VARCHAR2 DEFAULT 'default', cascade boolean default false, stattab VARCHAR2 default null, statid VARCHAR2 D Efault null, statown VARCHAR2 default null, no_invalidate boolean default false); this package is most commonly used. It collects statistics on tables. The syntax is shown above. Its key parameters are as follows: method_opt (histogram option) first describes what is a histogram. A histogram is used to collect statistics on the data distribution in the column, so that the optimizer can know the distribution of data in each column, if the distribution of data in a column is very skewed, it is best to collect histogram information for this column. Method_opt has the following options: for all columns-> calculate the histogram of all columns in the table for all indexed columns-> count the histogram of the index columns defined on the table for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY: n: Number of histogram buckets. value range: [1, 1,254]. 1 is equivalent to not collecting histogram REPEAT. Only columns with original histogram information are collected. AUTO: Oracle determines the size of N. SKEWONLY: only the histogram of unevenly distributed columns is collected. The system automatically determines the number of buckets (bucket). Example 1: collects statistics on table t, but does not collect the histogram: exec dbms_stats.gather_table_stats (user, 'T', method_opt => 'for all columns size 1'); Example 2: Collect the histogram by the maximum number of buckets: exec dbms_stats.gather_tabl E_stats (user, 't', method_opt => 'for all columns size 254'); granularity (granularity of statistics collected for partition tables) this parameter is mainly for partition tables, partition tables have the following three types of statistical information: global-> global statistical information partition-> partition Statistical Information sub-partition-> subpartition statistical information cascade (whether to collect index statistics at the same time) true is equivalent to running gather_index_stats on all indexes of the table. 2) gather_index_stats [SQL] partition (ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT partition (GET_PARAM ('estimate _ PERCENT '), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 default null, statown VARCHAR2 default null, degree number default to_degree_type (get_param ('degree'), granularity VARCHAR2 DEFAULT GET_P ARAM ('granularity '), no_invalidate boolean default to_no_invalidate_type (GET_PARAM ('no _ invalidate'), force boolean default false); this function is used to collect index statistics. 3. View related statistics 1) select table_name, num_rows, blocks, empty_blocks, avg_space from user_tables where table_name = 'test'; 2) select table_name, column_name, num_distinct, density from user_tab_columns where table_name = 'test'; 3) select table_name, column_name, endpoint_number, endpoint_valuefrom partition table_name = 'test' and column_name = 'object _ id'; 4) select partition_name, num_rows, blocks, empty_blocks, avg_spacefrom partition table_name = 'test'; 5) select column_name, num_distinct, density, num_nullsfrom partition table_name = 'test' and partition_name = 'p1'; 6) select column_name, bucket_number, endpoint_valuefrom user_part_histogramswhere table_name = 'test' and partition_name = 'p1' and column_name = 'object _ id ';