How to correctly analyze Oracle database models, tables, and indexes

In Oracle databases, we often need to manually analyze tables and indexes, as well as all tables and indexes in a certain mode. Oracle provides two Analysis Methods: db

In Oracle databases, we often need to manually analyze tables and indexes, as well as all tables and indexes in a certain mode. Oracle provides two Analysis Methods: db

In Oracle databases, we often need to manually analyze tables and indexes, as well as all tables and indexes in a certain mode. Oracle provides two Analysis Methods: The dbms_stats package and the analyze command. These are our practical methods in daily project management for reference.

Analyze all objects of a user in 10 Gb, such as user_miki.

Sys. dbms_stats.gather_schema_stats (ownname => 'user _ miki ', estimate_percent => 30, method_opt =>' for all indexed columns size auto', cascade => true, options => 'gather ');

This is an analysis method for analyzing all objects of a user in the project. The sampling ratio is 30%. It analyzes all index fields, generates column charts, and also analyzes indexes.

Analyze a table and its indexes under a user at 10 Gb, such as the info table and its indexes under user_miki.

Execute dbms_stats.gather_table_stats (ownname => 'user _ miki ', tabname => 'info', estimate_percent => 30, method_opt =>' for all indexed columns size auto ');

This is an analysis method for a single user table in the project. The sampling ratio is 30%. It analyzes all index fields, generates column charts, and also analyzes indexes.

We can also see the method of using the analzye command analysis table at google.
Analyze table table_name compute statistics;
This will analyze the index and table together;
Analyze table table_name compute statistics for table;
This is only an analysis table;
Analyze table table_name compute statistics for all columns;
This is only an analysis table, and all the above fields generate a column chart
Analyze table table_name compute statistics for all indexed columns;
This is only an analysis table, and all the indexed fields above generate a column chart
In 9i, we often use the analyze method. After 10 Gb, we changed it to a package similar to dbms_stats.gather_schema_stats to analyze database objects.
The 10g analytics function package is more flexible than the 9i analyze method.
In 10 Gb, there will be a scheduled task for the system. The new database objects and the database objects that it considers to have expired the statistical information will be automatically analyzed every 10 and two days of the weekend. This analysis result is sometimes unreliable.
Therefore, I use the dbms_stats.gather_schema_stats method provided in 10 Gb as a supplement to set a scheduled task to periodically analyze all database objects under the Business User.

For more information about Oracle, see the Oracle topic page? Tid = 12