1. What kind of method to determine whether you need a database table to update statistics?
2. How to detect the effect of the statistics update?
3. What is the method of statistics update: analyze the table table name compute statistics;
Or: the EXEC dbms_stats. Gather_table_stats (' user name ', 'the name of the table), is there any difference between these two methods?
How to detect the effect of both

CodePudding user response:

1, the system will automatically update statistics commonly, unless there is a lot of frequent operation, deletion statistics will have error or delay,
All_tables under 2, you can see below the amount of data and the actual amount of data whether there is a gap, there are other ways, of course, a great god reply
3, update the way both no gap, only one is the old version is a new version of the, mechanisms should be about the same,

CodePudding user response:

1, determine whether need to collect statistics is difficult, because of the statistical information is reasonable, how to look at the performance of the final SQL execution, saying offensive point, if from beginning to end the SQL performance are acceptable, so don't collect statistics, or a statistics collection, it doesn't matter even lock statistics, the best to set a rough first frequency, and then by a line of operations to refine better;
What is the effect of 2, update? Whether is updated? Or update is correct? Different statistical information stored in the data dictionary, including user_tables, user_tab_partitions, user_tab_subpartitions, user_indexes, user_tab_col_statistics, etc., for the table, the easiest to see if it statistics in recent violence is updated, you can view user_tables. Last_analyzed fields, this represents the latest time to collect statistics;
3, it is recommended to use the DBMS package to collect statistics, because can be parallel, at least for the partition table statistics collection and more accurate (you can specify a finer division level)