What is statistics and how to collect and view statistics?

What is statistics and how to collect and view statistics?
Concept:Oracle statistical information: it is stored in the data dictionary and describes the object details in the Oracle database from multiple dimensions. CBO uses these statistics to calculate the cost of each path.
Category:Statistical information of tables, indexes, columns, systems, data dictionaries, and internal objects
Collect statistics:The ANALYZE command and the DBMS_STATS package. You can use tables, indexes, columns, and data dictionaries. System and internal objects can only use DBMS_STATS.
1. ANALYZE analyze table PROC_PROGRESS_LOG delete statistics; -- delete statistical information analyze table PROC_PROGRESS_LOG estimate statistics sample 15 percent for table; -- Estimation mode, sample ratio: 15% (the estimation results do not necessarily match the actual results) analyze table PROC_PROGRESS_LOG compute statistics; -- computing mode
Select * from dba_tables where table_name = 'proc _ PROGRESS_LOG '; -- View table Information select * from USER_TAB_COLUMNS where TABLE_NAME = 'proc _ PROGRESS_LOG' -- View column information
Analyze table PROC_SMCS compute statistics for columns service_id, channel_id; -- after collecting statistics in the calculation mode of the column, service_id and channel_id do already have statistics, however, the statistics in the PROC_SMCS table will be erased. That is to say, for the same object, the newly executed analyze command will erase the previous analyze results.
If you want to collect statistical information of tables, columns, and indexes in tables in computing mode at one time, execute: analyze table PROC_PROGRESS_LOG compute statistics;
Use the DBMS_STATS package to collect statistics officially recommended, which is available only after oracle 8.1.5. It is regarded as analyze enhanced version 1. GATHER_TABLE_STATS: used to collect statistics of the columns and indexes of the target table and target table. 2. GATHER_INDEX_STATS: used to collect indexes. 3. GATHER_SCHEMA_STATS: Collects all objects in the specified schema. 4. GATHER_DATABASE_STATS: Collects all objects in the database.
Exec dbms_stats.gather_table_stats (ownname => 'test', tabname => 'proc _ LETTER ', estimate_percent => 15, method_opt => 'for table', cascade => false ); only the PROC_LETTER table has statistical information, and the columns and indexes do not. Method_opt => 'for table' is only applicable to versions earlier than 11 GB and earlier than 10. It also collects column and index information. If the computing mode is used, set estimate_percent => 15 to 100 or NULL.
Exec dbms_stats.gather_table_stats (ownname => 'test', tabname => 'proc _ LETTER ', estimate_percent => 100, method_opt =>' FOR columns size 1 service_id channel_id ', cascade => false); collects statistics of service_id channel_id in computing mode, and also displays statistics on PROC_LETTER tables. DBMS_STATS cannot collect only the statistical information of columns rather than the statistical information of tables.
Exec dbms_stats.delete_table_stats (ownname => 'test', tabname => 'proc _ LETTER '); Delete statistics.
Exec dbms_stats.gather_table_stats (ownname => 'test', tabname => 'proc _ LETTER ', estimate_percent => 100, cascade => false); one-time statistical table, column, and index information.
Differences between analyze and dbms_stats:1. analyze cannot correctly collect the statistics of the partition table, while dbms_stats can. 2. analyze cannot collect statistics in parallel, but dbms_stats can. Exec dbms_stats.gather_table_stats (ownname => 'test', tabname => 'proc _ LETTER ', estimate_percent => 100, cascade => false,Degree = 4); -- The concurrency is 43, and dbms_stats cannot collect additional information unrelated to CBO, such as the number of row migration/row links, and the structure information of the validation table and index. Analyze table XXX list chained rows into YYY -- analyze the number of row migration/row links collected analyze index XXX validate structure analysis index structure
The best way to collect statistics

Counts, table smells, and statistical charts.
Counting is to record the numbers and then sort them out.
Select the table to be used or the expected data from some tables.
Statistical charts include line chart, sector chart, and bar chart.

Which of the following methods can be used to collect data in statistical activities?

General surveys, key surveys, typical surveys, sampling surveys, and statistical report systems. Specific methods for collecting statistics include access, observation, and report.