Oracle If you use cost mode when performing SQL, all cost analysis information depends on the system's statistical analysis table (DBA_TABLES, DBA_DEXES, DBA_TAB_TABLUMNS) data, if the statistical analysis of data is inaccurate, that may make Oracle analysis The path execution performance is extremely powerful, so statistical analysis data is an important information that affects Oracle performance.
Statistical analysis mainly includes statistics that generate tables and indexes
The statistics of the table mainly include the number of lines of the table, the average length of each line (bytes), idle block, statistical time, etc.
The statistics of the indexes mainly include the number of lines, layers, number of leaves, statistical time, and more.
In addition, Oracle can also count column and data asymmetrical information, 9i can also count system information (CPU, I / O)
Oracle performs cost analysis, first take the statistics of the application tables and indexes for analysis, where the number of data lines is an important parameter, because Oracle is analyzed in the analysis table size, if two tables are combined, Oracle By analyzing the size of the table, the application of the small table is determined to perform full-meter queries, while the largest table performs joint queries, this performance is significantly higher than the first largest table. The statistical information of the index also produces a relatively large impact on the analysis, such as Oracle, can analyze the practicality of the priority and indexing of multiple indexes by statistics to determine the optimal indexing strategy. Oracle also counts column and data symmetrical information to produce more accurate analysis. Such as an index of a field, A total of two values 1 and 0, a total of 10,000 records, 10 records of 0, and 9990 records, if there is no column data asymmetrical. Statistics, then Oracle's indexes are indexes for A = 0 and A = 1 conditional queries, but actual applications have been greatly improved on A = 0 index performance, and A = 1 index is degraded. Therefore, the index feature value analysis information has a major impact on the application index, and accurate information makes Oracle not use the index that should not be used.
Actual analysis
ZL_CBQC and ZL_YHJBQK did not establish statistics, and the following two SQL Oracle will produce different execution plans.
1 Select * from dlyx.zl_cbqc b, dlyx.zl_yhjbqk a where a.qc_bh = B.QC_BH
Implementation plan:
Select Statement, Goal = Choose
NESTED LOOPS
Table Access Full Dlyx Zl_YHJBQK
Table Access by Index Rowid Dlyx ZL_CBQC
Index Unique Scan DLYX Meter Read Area Bolleys
2 Select * from dlyx.zl_yhjbqk a, dlyx.zl_cbqc b where A.QC_BH = B.QC_BH
Implementation plan:
Select Statement, Goal = Choose
NESTED LOOPS
Table Access Full Dlyx ZL_CBQC
Table Access by Index Rowid Dlyx Zl_YHJBQK
Index Range Scan Dlyx District Index
After statistical analysis of two tables
3 Select * from dlyx.zl_yhjbqk a, dlyx.zl_cbqc b where A.QC_BH = B.QC_BH
Implementation plan:
SELECT Statement, Goal = Choose 159 72853 9689449
Hash Join 159 72853 9689449Table Access Full Dlyx ZL_CBQC 1 426 19596
Table Access Full Dlyx ZL_YHJBQK 157 72853 6338211
4 Select * from dlyx.zl_cbqc b, dlyx.zl_yhjbqk a where A.QC_BH = B.QC_BH
Implementation plan:
SELECT Statement, Goal = Choose 159 72853 9689449
Hash join 159 72853 9689449
Table Access Full Dlyx ZL_CBQC 1 426 19596
Table Access Full Dlyx ZL_YHJBQK 157 72853 6338211
From the above test, it can clearly see the analysis results of Oracle.
Article 1 SQL and Article 2 SQL do not have statistical analysis, Oracle cannot be judged, and can only be analyzed by rule methods, and there is a significant relationship according to the presence of the table.
Article 3 SQL and Article 4 SQL performs under statistical analysis, Oracle Analysis has nothing to do with the order of the appearance, because it has known the amount of data on the table and has determined that the amount of data returned is basically two tables. All data, so the two tables have been taken JOIN (at the same time with the data of the two tables and then combine the return result).
Related technique
Use an Analyze statement to generate analysis data
Analysis table: Analyze Table Zl_YHJBQK Estimate Statistics Sample 20 Percent
Analysis Index: Analyze Index User Data Sheet Primary Key Compute Statistics
Analytical columns: Analyze Table ZL_YHJBQK Compute Statistics for Column HBS_BH
Analysis index: Analyze Table ZL_YHJBQK Compute Statistics for All Indexed Column
Use sys.dbms_utility package analysis data
Analyze the database (including all user objects and system objects): Analyze_Database
Analyze all the objects of the user (including tables, indexes, clusters within the user program): Analyze_Schema
Use sys.dbms_stats package processing analysis data
Analyze the database (including all user objects and system objects): Gather_Database_stats
Analyze all the objects of the user (including table, index, cluster): Gather_Schema_Stats
Analysis table: gather_table_stats
Analysis Index: Gather_index_stats
Delete database statistics: delete_database_stats
Delete user program statistics: delete_schema_stats
Delete table statistics: delete_table_stats
Delete index statistics: delete_index_stats
Delete column statistics: delete_column_stats
Set table statistics: set_table_stats
Set index statistics: set_index_stats
Set column statistics: SET_COLUMN_STATSOracle Recommended users use the sys.dbms_stats package for analysis because the functionality of this encapsulation in Oracle9i and above has been fully expanded. When the SYS.DBMS_UTILITY package is analyzed, all the information will be analyzed over again, while the time is relatively long, and sys.dbms_stats in 9i can use table modification monitoring techniques to determine the table to be statistically analyzed, saving the user's analysis resources.
Remarks:
Oracle will only analyze the statistical analysis data obtained by the table, otherwise the rule analysis is employed.
Not all statistics will have an impact on Oracle analysis, and some statistics Oracle do not process, just provide a reference information to the user, or it is also possible to use this information in Oracle.
The login user of the analysis must have access to the table or has DBA or Analyze Any permissions.
