Dbms_stats package User Manual

There are more than 40 stored procedures under the dbms_stats package. It is very important to generate the execution plan. Common examples include:
Analysis database (including all user objects and System Objects): gather_database_stats
Analyze all user objects (including tables, indexes, and clusters): gather_schema_stats
Analysis table: gather_table_stats
Analysis index: gather_index_stats
Delete database statistics: delete_database_stats
Delete user solution 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_stats
You can view the DBA_TABLES table to check whether the table has been analyzed, for example:
SELECT TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES
DBMS_STATS.GATHER_TABLE_STATS and DBMS_STATS.GATHER_INDEX_STATS are usually used.

1. PROCEDUREGATHER_TABLE_STATS
2. Parameter Name type input/output default value?
3. -------------------------------------------------------------------
4. OWNNAME VARCHAR2IN
5. TABNAME VARCHAR2IN
6. PARTNAME VARCHAR2IN DEFAULT
7. ESTIMATE_PERCENT NUMBERIN DEFAULT
8. BLOCK_SAMPLE BOOLEANIN DEFAULT
9. METHOD_OPT VARCHAR2IN DEFAULT
10. DEGREE NUMBERIN DEFAULT
11. GRANULARITY VARCHAR2IN DEFAULT
12. CASCADEBOOLEANIN DEFAULT
13. STATTAB VARCHAR2IN DEFAULT
14. STATID VARCHAR2IN DEFAULT
15. STATOWN VARCHAR2IN DEFAULT
16. NO_INVALIDATE BOOLEANIN DEFAULT
19. PROCEDUREGATHER_INDEX_STATS
20. Parameter Name type input/output Default Value
21. -------------------------------------------------------------------
22. OWNNAME VARCHAR2IN
23. INDNAME VARCHAR2IN
24. PARTNAME VARCHAR2IN DEFAULT
25. ESTIMATE_PERCENT NUMBERIN DEFAULT
26. STATTAB VARCHAR2IN DEFAULT
27. STATID VARCHAR2IN DEFAULT
28. STATOWN VARCHAR2IN DEFAULT
29. DEGREE NUMBERIN DEFAULT
30. GRANULARITY VARCHAR2IN DEFAULT
31. NO_INVALIDATE BOOLEANIN DEFAULT

Note:
Ownname: owner of the table to be analyzed
Tabname: name of the table to be analyzed.
Partname: partition name, which is only useful for partitioned tables or partition indexes.
Estimate_percent: this parameter is equivalent to "estimate statistics sample x percent" in analyze ". estimate the percentage of the total number of rows. if this parameter is null. it is compute. You can also use DBMS_STATS.AUTO_SAMPLE_SIZE to let Oracle determine the estimated percentage.
Block_sample: this parameter is a boolean parameter. It determines whether to perform random sampling.
Method_opt: Specifies whether a histogram is used when data publication is unbalanced. Optional values: "for all columns or for all indexed columns. Of course, you can also use auto. To let oracle decide to collect
Degree: Number of CPUs in parallel degree (parallel collection dimension)
Cascade: determines whether to collect statistics on indexes of related tables.
Force: Collects statistics even if the table is locked.

1 2 Next Page