04 system and object statistics

Summary
----------------------------------------------------
Toolkit dbms_stats
System statistics
Object statistics
General Service
----------------------------------------------------
In fact, the query optimizer still cannot provide efficient execution plans only knowing the SQL statement to be processed and the object structure it references. The optimizer must also quantify the amount of data to be processed.
4.1 Introduction to dbms_stats
In the past, object statistics were collected through the SQL statement analyze. So far, we recommend that you use dbms_stats.
4.2 system statistics (by default)
4.3 object statistics (important)
There are three types of object statistics, including table statistics, column statistics, and index statistics.

Testing, using object statistics:

Create Table tasselect rownum as ID, round (dbms_random.normal * 1000) as val1, 100 + round (Ln (rownum/3.25 + 2) as val2, 100 + round (Ln (rownum/3.25 + 2) as val3, dbms_random.string ('P', 250) as padfrom all_objectswhere rownum <= 1000 order by dbms_random.value; update t set val1 = NULL where val1 <0; alter table t add constraint t_pk primary key (ID); Create index t_val1_ I on T (val1 ); create index t_val2_ I on T (val2); begindbms_stats.gather_table_stats (ownname => User, tabname => 'T', estimate_percent => 100, method_opt => 'for all columns size skewonly ', cascade => true); end; /-- =============== table statistics ========== select num_rows, blocks, empty_blocks, avg_space, chain_cnt, rows from rows where table_name = 'T'; -- result/* num_rows blocks into avg_space chain_cnt avg_row_len ------------ ---------- ------------ --------------- 1000 44 0 0 0 265 num_rows: blocks: the number of data blocks under the high water level. empty_blocks: the number of data blocks above the high water level. If dbms_stats does not calculate this value, it is set to 0avg_space: the average free space (in bytes) of the data block in the table. If dbms_stats does not calculate this value, it is set to 0chain_cnt: the total number of rows involving row links and row migration. If dbms_stats does not calculate this value, set to 0avg_row_len: Average Length (in bytes) of each record in the table) */-- ================ column statistics ============== select column_name as "name ", num_distinct as "# DST", low_value, high_value, density as "dens", num_nulls as "# null", avg_col_len as "avglen", histogram, num_buckets as "# bkt" from user_tab_col_statistics where table_name = 'T '; -- result/* name # DST low_value high_value dens # null avglen histogram # bkt ---- ----- -------------- ------- ------ --------------- ID 1000 c102 c20b. 00100 0 4 none 1val1 431 c103 c2213e. 00254 503 3 height balanced 254val2 6 c20202 c20207. 00050 0 4 frequency 6val3 6 c20202 c20207. 00050 0 4 frequency 6pad 1000 202623436f2943 7e79514a202d49. 00100 0 251 height balanced 2547334237b450574 comment Comment comment 59414c44num_distinct, I cannot understand it here. I need to use another function density: small tree between 0 and 1. If it is close to 0, the column filtering operation can remove most rows num_nulls: the total number of null values in the column avg_col_len: average column size, expressed in bytes histogram: Indicates whether histogram statistics are available num_buckets: Number of buckets in the histogram. -- the additional information of the histogram about uneven data distribution is called the histogram. */select endpoint_value, endpoint_number, endpoint_number-lag (endpoint_number,) over (order by endpoint_number) as frequency from user_tab_histograms where table_name = 'T' and column_name = 'val2' order by endpoint_number; -- result/* endpoint_value endpoint_number frequency -------------- --------------- ---------- 101 8 8102 33 25103 101 68104 286 185105 788 502106 */-- ======= index statistics ============================ select index_name as name, blevel, leaf_blocks as leaf_blks, distinct_keys as dst_keys, num_rows, clustering_factor as clust_fact, same as rows, same as data_per_key from rows where table_name = 'T '; -- result/* Name blevel when num_rows clust_fact when data_per_key ---------- ----------- -------- ---------- -------------- ------------ average 1 2 6 1000 1 limit 1 2 153 431 1 1t_pk 1 2 497 479 1000 1000 980 1 1 blevel, number of branches leaf_blocks, number of leaf blocks distinct_keys, total number of key values num_rows, and number of key values in the index. For Primary keys, it is equivalent to distinct_keysclustering_factor: Clustering factor, the index chapter of the master introduces this factor. avg_leaf_blocks_per_key, avg_data_blocks_per_key, avg_leaf_blocks_per_key, and avg_data_blocks_per_key */

4-1

Collect object statistics
In the past, DBA was responsible for collecting data. After Oracle10g, a job was created to regularly collect object statistics and schedule the job.
Use dbms_stats to collect statistics:
Gather_database_stats collects statistics on objects in the entire database.
Gather_dictionary_stats collects statistics on objects in the data dictionary.
Gather_fixed_objects_stats collects the statistical information of specific objects in the data dictionary called a fixed table. It will be used after 10 Gb. V $ fixed_table
Gather_schema_stats collects statistics on all objects in the entire mode.
Gather_table_stats collects object statistics for the table (optional for index ).
Gather_index_stats collects index object statistics
As you can see, it is not necessary to separately collect column statistics.
Parameters used in the collection process:

Marked as: ownname: Mode name, indname: Index name, tabname: Table Name, cascade: Includes index,
Obj_filter_list: it is set to collect statistics only when the object loads one passed parameter condition.
Estimate_percent: indicates the method for collecting statistics. It is better to use it after 11 GB. If the database determines that the value of this parameter is too small, it will automatically increase and accelerate the collection.
In the process of statistical information, it is usually appropriate to set a smaller number, and 10% is usually a good choice. For large tables, 0.5% or 0.1% or even smaller is also good.
There are many other parameters that can be set. We can use them here. There are too many parameters.
Locked object statistics
Sometimes, for example, if you want to use the old statistical information and want to ensure that the statistical information remains unchanged, you can use:
Dbms_stats.lock_schema_stats (ownname => User) -- lock schema statistics
Dbms_stats.loc_table_stats (ownname => User, tabname => 'T') -- lock table statistics
Unlock can be called to unlock:
Dbms_stats.unlock_schema_stats (ownname => User)
The above locks are different from Oracle's traditional locks.
When the object is locked, call gather to update the statistics, and an error is returned.
In addition, you can compare the statistical information of objects and delete the statistical information of objects.

This chapter contains many parameters and operations related to dbms_status, which cannot be remembered. You can check them later.