Application of statistical analysis technology for database objects

Objects | data | database | statistics
If Oracle uses cost analysis when executing SQL, all cost analysis sources rely on the system's statistical analysis table (Dba_tables, dba_indexes, dba_tab_columns) data, if the statistical analysis data is inaccurate, That could make the path performed by Oracle's analysis extremely poor, so statistical analysis data is the most important information that affects Oracle performance.





Statistical analysis mainly includes statistical information of producing tables and indexes



Table statistics mainly include the number of rows in the table, the average length of each line (bytes), free blocks, statistical time, and other information

The statistic information of index mainly includes the information of line number, layer number, leaf block number, statistic time and so on.

In addition, Oracle can also statistics and data asymmetry information, 9i can also be statistical system Information (CPU,I/O)





Oracle performs cost analysis by first taking out the statistics of the applied tables and indexes for analysis, where the number of data rows is an important parameter, because Oracle analyzes the number of rows in table size as the primary parameter, and if two tables are combined, Oracle passes the size of the analysis table. The decision to apply a small table for a full table query, and a large table to perform a federated query, is significantly higher than the first large table for full table scans. The statistical information of indexes also has a great impact on the analysis, such as Oracle can analyze the priority of producing multiple indexes and the practicability of index to determine the optimal indexing strategy through statistics. Oracle can also statistical columns and data symmetry information to produce more accurate analysis. If a table has an index of field A, where a has two values 1 and 0, a total of 10,000 records, 0 records have 10, 1 of the record has 9,990, then if there is no data asymmetry statistics, then Oracle to a=0 and a=1 conditional query will be indexed, but the actual application of a The index performance of =0 is greatly improved, while the index of A=1 makes the performance drop. So the index eigenvalue analysis information has a significant impact on the application index, and accurate information makes Oracle not use an index that shouldn't be used.





Practical analysis



ZL_CBQC and ZL_YHJBQK do not have statistical information, and executing 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

Execution 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 reading area book primary key





2 Select * from Dlyx.zl_yhjbqk A,DLYX.ZL_CBQC b where a.qc_bh=b.qc_bh



Execution 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 area









After a statistical analysis of the two tables

3 SELECT * from dlyx.zl_yhjbqk A,DLYX.ZL_CBQC b where a.qc_bh=b.qc_bh



Execution 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





4 SELECT * from DLYX.ZL_CBQC b,dlyx.zl_yhjbqk a where a.qc_bh=b.qc_bh

Execution 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, we can clearly see the Oracle analysis results

1th SQL and 2nd SQL in the absence of statistical analysis of the information, Oracle can not be judged, can only be analyzed by rules, according to the order of occurrence of the table has a clear relationship.

The 3rd SQL and 4th SQL is under the information of statistical analysis, Oracle parsing has nothing to do with the order of the table, because it already knows the amount of data in the table and has determined that the amount of data returned is basically all two tables, so a hash JOIN is made on table two tables (The two-table data is fetched and then combined in memory to produce the return result).





Related technology



Generating profiling data with analyze statements



Analysis table: Analyze table ZL_YHJBQK estimate statistics sample percent

Analysis Index: Analyze index user table primary key compute statistics

Parsing columns: Analyze table ZL_YHJBQK Compute statistics for columns HBS_BH



Parse indexed columns: Analyze table ZL_YHJBQK COMPUTE statistics for all indexed



Analyzing Data with Sys.dbms_utility package



Analysis database (including all user objects and System objects): Analyze_database

Analyze all users ' objects (including tables, indexes, clusters in user scenarios): Analyze_schema

Processing analysis data with Sys.dbms_stats package



Analysis database (including all user objects and System objects): Gather_database_stats



Analyze all users ' objects (including tables, indexes, clusters): Gather_schema_stats

Analysis Table: Gather_table_stats

Analysis Index: Gather_index_stats



To delete database statistics: delete_database_stats

Delete User Scenario 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

To set column statistics: Set_column_stats







Oracle recommends that users use the Sys.dbms_stats package for analysis because of the functionality of this package, which is fully expanded in the oracle9i and above versions. Sys.dbms_utility Package Analysis of all the information will be analyzed all over, the time is longer, and in 9i Sys.dbms_stats can use the table to modify the monitoring technology to determine the table to be statistical analysis, saving the user's analysis resources.





Note:



Oracle has obtained the statistical analysis data of the table before will carry on the cost analysis, otherwise uses the rule analysis.

Not all statistics have an impact on Oracle Analytics, some of which are not processed by Oracle, but are provided to the user as a reference, and may be analyzed using this information in future versions of Oracle.

The logged-on user of the profiling table must have access to the table or have DBA or analyze any permission