Plsql_ Performance Optimization Series 1_ the concept and importance of statistical information (concept)

2014-12-18 Created by Baoxinjian

I. Summary

Statistic is very important to Oracle.

It collects detailed information about the objects in the database and stores them in the corresponding data dictionary. Based on these statistics, optimizer can choose the best execution plan for each SQL.

Statistic is important for Oracle, which collects detailed information about the objects in the database and stores them in the corresponding data dictionary.

Based on these statistics, optimizer can choose the best execution plan for each SQL.

Oracle statistic collection can be collected using the Analyze command or by using the Dbms_stats package.

Oracle recommends using the Dbms_stats package to collect statistics because dbms_stats packages are collected more widely and more accurately, and Analyze may be removed in future releases.

Ii. content and level of statistical information

1. Table Statistics

(1). Number of rows, number of blocks, average length of rows

(2). Dba_tbales:num_rows,blocks,avg_row_len;

• Number of rows
• Number of blocks
• Average row length

2. Column Statistics

(1). Number of unique values in the column (NDV), number of null values, data distribution;

(2). Dba_tab_columns:num_distinct,num_nulls,histogram;

• Number of distinct values (NDV) in column
• Number of Nulls in column
• Data Distribution (histogram)

3. Index Statistics

(1). Number of leaf blocks, grade, cluster factor;

(2). Dba_indexes:leaf_blocks,clustering_factor,blevel;

• Number of leaf blocks
• Levels
• Clustering factor

4. System Statistics

(1). stored in the aux_stats$, need to use Dbms_stats collection, I/O statistics in X$KCFIO;

• I/O performance and Utilization
• CPU Performance and Utilization

III. Statistical Information syntax

1. Analyze

Statistics are required using analyze statistics: Collect the statistics of the free list blocks using the list CHAINED rows and validate clauses;

Analyze is not suitable for partitioning table analysis

• Analyze table TableName compute statistics;
• Analyze Index|cluster indexname estimate statistics;
• Analyze table TableName Compute statistics for table/for all [local] indexes/for all [indexed] columns
• Analyze table TableName Delete statistics
• Analyze table tablename Validate ref update
• Analyze table tablename Validate structure [cascade]| [INTO TableName]
• Analyze table tablename list chained rows [into TableName]

2. Dbms_stats

Dbms_stats can well estimate statistics (especially for larger partitioned tables) and get better statistical results, eventually making a faster SQL execution plan.

The following four stored procedures for this package collect statistics for index, table, schema, database, respectively:

• Dbms_stats.gather_table_stats collect statistics for tables, columns, and indexes;
• Dbms_stats.gather_schema_stats collects statistical information about all objects under the schema;
• Dbms_stats.gather_index_stats collection of index statistics;
• Dbms_stats.gather_system_stats Collecting System statistics
• Dbms_stats.gather_dictioinary_stats Statistics of all Dictionary objects;
• Dbms_stats.delete_table_stats Deleting a table's statistics
• Dbms_stats.delete_index_stats to delete statistics for an index
• Dbms_stats.export_table_stats Statistics for output tables
• Dbms_stats.create_state_table
• Dbms_stats.set_table_stats setting the statistics of a table
• Dbms_stats.auto_sample_size

Iv. Statistical Information syntax

4.1 Statistical information collects the following data:

(1) Analysis of the table itself: including the number of rows in the table, the number of data blocks, the president and other information.

(2) Analysis of columns: includes the number of repetitions of the column values, the null value on the column, and the distribution of the data on the column.

(3) Index Analysis: Including the number of index leaf block, index depth, index aggregation factor, etc.

4.2 These statistics are stored in a data dictionary, such as:

(1). Dba_tables

(2). Dba_object_tables

(3). Dba_tab_statistics

(4). Dba_tab_col_statistics

(5). Dba_tab_histograms

(6). Dba_indexes

(7). Dba_ind_statistics

(8). Dba_clusters

(9). Dba_tab_partitions

(10). Dba_tab_subpartitions

(11). Dba_ind_partitions

(12). Dba_ind_subpartitions

(13). Dba_part_col_statistics

(14). Dba_part_histograms

(15). Dba_subpart_col_statistics

(16). Dba_subpart_histograms

4.3 Table statistics:

Contains the number of table rows, the number of blocks used, the number of empty blocks, the usage of blocks, the number of row migrations and links, the pctfree,pctused data, the average size of the rows:

SELECT -- Number of records in the table        -- the number        of data blocks in the table -- Number of empty blocks in the table        -- average usage space        in a data block -- table row Join and row migration number        -- average length of each record  from

4.4 Statistical information for indexed columns

Contains the depth of the index (the level of B-tree), the number of blocks in the index leaf level, the cluster factor (Clustering_factor), and the number of unique values.

SELECT -- Number of layers indexed    -- Number of leaf nodes    -- Number of unique values    -- Average number    of leaf blocks per key -- Average number    of data blocks per key -- cluster factor  from User_indexes

4.5 Columns of statistical information

Contains a unique number of values, column maximum small value, density (selection rate), data distribution (histogram information), Number of NULL values

SELECT -- Number of unique values    -- the minimum value    on the column -- the maximum value    on the column -- Select rate factor (density)    -- Number of NULL values    -- Number of buckets in the histogram    -- Types of histograms  from User_tab_columns

V. Cases

Case: When you query a table, the resolution plan returns the result set rows are completely incorrect, and after a large number of DML tables are not analyzed, the statistics are too long

Step1. Build Test SQL

Step2. Viewing the cardinality of a result set

Step3. View the statistical Plan of the table, and finally analyze the time too long

Step4. Analysis table

BEGIN    dbms_stats.gather_table_stats ('SH''SALES' ); END;

Step5. Statistics change to latest after analysis table

Step6. Resolution plan cardinality change more accurately

Thanks and regards

Reference: A river-http://www.cnblogs.com/rootq/archive/2010/02/04/1663622.html

Reference: david-http://blog.csdn.net/tianlesoftware/article/details/4668723

Reference: edwardking888-http://blog.itpub.net/8183550/viewspace-666335/

Plsql_ Performance Optimization Series 1_ the concept and importance of statistical information (concept)