Shulou(Shulou.com)06/01 Report--

1. Analyze tables and indexes (analyze does not rebuild indexes)

Analyze table tablename compute statistics

Equivalent to analyze table tablename compute statistics for table for all indexes for all columns

For table statistics exist in the view: user_tables, all_tables, dba_tables

For all indexes statistics exist in the view: user_indexes, all_indexes, dba_indexes

For all columns statistics exist in the view: user_tab_columns, all_tab_columns, dba_tab_columns

Note: for analysis table and index, see Analyze All Table stored procedure

2. Generally speaking, the index can be analyzed manually in the following three ways.

Analyze index idx_t validate structure:

Analyze index idx_t compute statistics:

Analyze index idx_t estimate statistics sample 10 percent

1) analyze index idx_t validate structure:

This parsing statement is used to analyze whether there are any bad blocks in the block of the index, so according to the analysis, we can get the structural data of the index, which will be retained to

In index_stats, to determine whether the index requires rebuild. It is important to note that such an analysis does not collect index statistics.

2) there are two modes of validate structure: online and offline. Generally speaking, the default mode is offline.

When analyze is indexed in offline mode, a table-level shared lock will be added to table, which will have a certain impact on some real-time DMl operations of table.

When analyzed in online mode, no lock is added, but no information is seen in index_stats.

3) analyze index idx_t compute statistics:

Statistics used for statistical indexes (full analysis), mainly for CBO services.

4) analyze index idx_t estimate statistics sample 10 percent

Mainly used to specify the proportion of sampling analysis, but also for CBO services. In this case, the sample is 10%.

3. Rebuild the index

Alter index index_name rebuild tablespace tablespace_name

Alter index index_name rebuild tablespace tablespace_name adds the tablespace name and moves the specified index to the specified tablespace.

Note:

Analyze operations are just statistics, and the statistics are stored for later analysis SQL, without specific implementation operations such as rebuilding, so if you want to rebuild the index

Still need to use alter index index_name rebuild

4. Other statistical methods

1) DBMS_STATS: this is, of course, the most powerful analysis package

-- create a statistical history retention table

Exec dbms_stats.create_stat_table (ownname = > 'scott',stattab = >' stat_table')

-- Export statistics for the entire scheme

Exec dbms_stats.export_schema_stats (ownname = > 'scott',stattab = >' stat_table')

-- analyze scheme

Exec dbms_stats.gather_schema_stats (ownname = > 'test',options = >' GATHER AUTO',estimate_percent = > dbms_stats.auto_sample_size,method_opt = > 'for all indexed columns', degree = > 6)

-- Statistics of the analysis table

Exec dbms_stats.gather_table_stats (ownname = > 'TEST',tabname = >' sm_user',estimate_percent = > 10 methodological opt = > 'for all indexed columns')

-- analyze the statistics of the index

Exec dbms_stats.gather_index_stats (ownname = > 'TEST',indname = >' pk_user_index',estimate_percent = >'10')

If the execution plan is found to be wrong, delete the statistics of the table

Exec dbms_stats.delete_table_stats (ownname = > 'TEST',tabname = >' SM_USER')

-- Import table historical statistics

Exec dbms_stats.import_table_stats (ownname = > 'TEST',tabname = >' SM_USER',stattab = > 'stat_table')

-- if the execution plans of most tables go wrong after analysis, you need to import the statistics of the entire scheme.

Exec dbms_stats.import_schema_stats (ownname = > 'TEST',stattab = >' SM_USER')

-- Import Index Statistics

Exec dbms_stats.import_index_stats (ownname = > 'TEST',indname = >' PK_USER_INDEX',stattab = > 'stat_table')

Analyze differs from dbms_stats in:

Analyze updates statistics for both tables and indexes, while dbms_stats updates statistics for tables first and then updates statistics for indexes.

There is a problem here, that is, when the statistics of the table are updated, but the statistics of the index are not updated, cbo may choose the wrong plan.

2) DBMS_UTILITY.ANALYZE_SCHEMA: all objects in SCHEMA can be analyzed directly

Such as: EXEC DBMS_UTILITY.ANALYZE_SCHEMA ('LTTFM','COMPUTE')

3) DBMS_DDL.ANALYZE_OBJECT: collect statistics of objects

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.