I believe the importance of statistical data to understand the Oracle database is very clear that he is the only standard execution plan, so the accuracy of statistical data directly affect the correctness of the implementation of the plan, although there is no statistical data on MYSQL ORACLE so multi-dimensional (eg histogram) but is still there, MYSQL 5.6 and divided into two kinds of statistics.
1, perpetuating statistics
After the default 5.6.6 When using this mode, the parameters affect his
The number of blocks innodb_stats_persistent_sample_pages each sample, the default is 20
Are innodb_stats_auto_recalc open automatically collected, the default is to modify the amount exceeds 10% of the data
innodb_stats_persistent default is ON, whether perpetuate statistics
Perpetuate statistical data stored in innodb_index_stats and innodb_table_stats, the official statistics say that the document is not in real time, that is, the collection of statistical data lag a few minutes, if you want to timely update statistics
Data need to manually perform ANALYZE TABLE (In some cases, statistics
recalculation may be delayed by a few seconds. If up-to-date statistics are
required immediately after changing significant portions of a table, run ANALYZE
TABLEto initiate a synchronous (foreground) recalculation of statistics)
If the statistics are not allowed to modify the values innodb_stats_persistent_sample_pages may need a larger, if analyze table too slow can consider reducing the value innodb_stats_persistent_sample_pages smaller. Both table INNODB table
2, non-perpetuating statistics
If set to OFF innodb_stats_persistent on the use of statistical data in this way, this way statistical data in the database is lost after the restart, his statistical data in STATISTICS, TABLES two tables, they are the engines MEMORY table, this way affected parameters
innodb_stats_persistent set to OFF
innodb_stats_transient_sample_pages default is 8, the number of sample blocks
innodb_stats_on_metadata English description To have statistics updated when metadata statements such as SHOW TABLE STATUS
or SHOW INDEX are run, or when accessing the INFORMATION_SCHEMA.TABLES or
INFORMATION_SCHEMA.STATISTICSt ables, execute the statement SET GLOBAL
innodb_stats_on_metadata = ON
When innodb_stats_persistent set to ON, the default is OFF innodb_stats_on_metadata, because it will affect the speed of SHOW TABLE STATUS, etc.
Finally, let us briefly describe the policy ORCALE statistical data collection, ORACLE 10G, 11G through automated job to collect default 11G Week 1 to Week 5 22:00 to start collecting statistical data, the weekend is 6 am of course this is not all collect statistical data collection, ORACLE will modify the number of more than 10% recorded mon_mods_all $ tABLES internal table, and then collect these tables, so to ensure as much as possible of the statistics describe the distribution of data while ORACLE will use the predicate to determine whether the histogram collected predicate usage records in COL_USAGE $ interior view, the histogram data for determining the inclination, MYSQL in this regard depends on yet.