Verify the strength of Oracle collection statistics parameters granularity data analysis

In the recent study of Oracle statistics, the method for collecting statistics is as follows:

Dbms_stats. Gather_table_stats (Ownname VARCHAR2,---owner name tabname VARCHAR2,---table name partname VARCHAR2 DEFAULT NULL,---The partition name to parse estimate _percent number DEFAULT NULL,---the scale of the sample block_sample BOOLEAN default FALSE,---whether block parsing method_opt VARCHAR2 DEFAULT ' for all COL Umns size 1 ',---parsing degree number default NULL, the degree of parallelism of the analysis granularity VARCHAR2 default ' default ',---the granularity of the analysis Cascade BOOLEAN Default FALSE,---whether to parse the index stattab VARCHAR2 default NULL,---Use the performance table name Statid VARCHAR2 default NULL,---Performance table identity Statown VARCHAR2 D Efault NULL,---Performance table owner No_invalidate boolean default false,---whether to validate cursor dependency force BOOLEAN default false); ---Forced analysis, even if the lock table

This paper mainly validates the parameter granularity,

Granularity: The strength of data analysis

--global---Global

--partition---Analysis only at the partition level

--subpartition--Analysis at sub-partition level only

The verification steps are as follows:

Create a partitioned table and insert two data, and create an index on the Field ID drop table Test purge;create table Test (ID number) partition by range (ID) (Partition P1 value S less than (5), partition P2 values less than (+)); INSERT into test values (1); INSERT into test values (6); COMMIT; CR Eate index ind_id on test (ID);  Second, collect the statistics of the table exec dbms_stats.gather_table_stats (user, ' test ', cascade=>true);   Statistics of query table select num_rows,blocks,last_analyzed from user_tables where table_name = ' TEST '; the results are as follows: Num_rows: Table Data rows blocks: Number of data blocks last_analyzed: Recent analysis time   IV. query table partition information select partition_name,num_rows,blocks,last_analyzed from Dba_ tab_partitions WHERE table_name = ' TEST '; Partition_name: Partition name num_rows: Number of data rows blocks: Number of data blocks last_analyzed: Most recent analysis time   v. Query index STATISTICS SELECT Num_rows,blevel,last_ Analyzed from user_indexes where index_name = ' ind_id ';  num_rows: Number of indexed data rows blevel: Index height last_analyzed: Analysis time   VI. Add a partition ALTER TABLE test add partition Pmax values less than (maxvalue);  VII Insert 10,000 data into the new partition begin for me in 1..10000 loop ---Inserting 10,000 data insert into test values (100);End loop;commit;end;  Eight, create a very steep section of the update test set id=10000 where id=100 and rownum=1; ---Create a very inclined Pmax partition commit;  Nine, query partition data Select Id,count (*) from Test partition (Pmax) group by id;  10, do not analyze, Querying the statistics of the table again select num_rows,blocks,last_analyzed from user_tables where table_name = ' TEST '; found no change in the number of data rows and blocks of data     11, Query id=100 execution plan set autotrace traceonlyset linesize 1000select * from Test where id=100; found gone index, normally, because id= 100 of the data in a very high slope of the partition Pmax, the data with ID 100 has 9,999, the cost of walking index is higher than the cost of walking the whole table (because the index needs to go back to the table), if the statistics are correct, the optimizer should choose to walk the whole table, but here do not walk the whole table but the index, Here the suspicion is that the statistics are not correctly caused, after authentication   12, collect partition statistics exec dbms_stats.gather_table_stats (user, ' TEST ', partname = ' PMAX ', granularity = ' PARTITION ');  13, querying the table for statistical information and zonal statistics select partition_name,num_rows,blocks,last_analyzed from dba_tab_partitions WHERE table_name = ' TEST '; The partitioning information is changed after the discovery and step four comparisons, indicating that the partition information is updated  select after the partition is collected for statistical information num_rows,blocks , last_analyzed from user_tables where table_name = ' TEST '; the statistical information of the table does not change, the statistics of the table are updated after the partition information is counted.

14, re-query id=100 data still go index, indicating in the evaluation of the query, the table statistical information is still obsolete, query index statistics select num_rows,blevel,last_analyzed from User_indexes where index_name = ' ind_id '; The discovery Index statistics does not change from step five, indicating that the index information of the table is not updated after the statistics of the partitions have been collected

16, re-collect the statistical information of the table exec dbms_stats.gather_table_stats (user, ' TEST ', Cascade =>true); 17, query the statistics of the table and the statistics of the index select num_rows,blocks,last_analyzed from user_tables where table_name = ' TEST '; table statistics have been updated select Num_rows,blevel,last_analyzed from user_indexes where index_name = ' ind_id '; The index's statistics have also been updated 18, re-querying id=100 's execution plan this time the discovery went all the way, After collecting the statistical information, the statistics of the table are accurate and the evaluation is accurate.

Verify the strength of Oracle collection statistics parameters granularity data analysis