Oracle Table Analysis Method

Test Table name: D

1, to determine whether a table has been analyzed:

Select T.num_rows,t.avg_row_len from User_tables t where T.table_name = ' D ',

If the result is empty, it means that the table has not been parsed;

2, the table has not been analyzed obsolete, Oracle will adopt dynamic sampling mechanism, to obtain the information of the table, so that the implementation plan

[dynamic sampling. png]

The dynamic sampling level is 0-10,0 for dynamic sampling, and 10 represents the maximum level of dynamic sampling;

3, the Oracle default dynamic sampling to obtain the table information in the table data is less than 10W, the basic correct; when >10w, the larger the data volume, the greater the error of the table information, resulting in the execution plan error;

4. Force hint to set the dynamic sampling level of the table: Dynamic_sampling (d 0)--no dynamic sampling of D tables

5, the manual implementation of the SP to analyze table information (get the table information than the dynamic sampling to obtain accurate information), so that the table does not do dynamic sampling

exec dbms_stats.gather_table_stats (user, ' d ', cascade=>true)--analyzes the table and then does not dynamically sample;

exec dbms_stats.delete_table_stats (user, ' d ');--Delete the analysis dynamic information of the table, then the table will be dynamic

6, additional simulation test environment example:

Select/*+ dynamic_sampling (t 0) cardinality (t 1) * * from t;

--Canceling dynamic sampling and setting rownum=1;

Note: The correct table information is the prerequisite for generating the correct execution plan. When you execute a plan error, you can analyze the table manually to get the correct execution plan.

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/