Differences between the Oracle dbms_stats package and the analyze command

 

For DBAs, these two commands are no stranger. You can use these two commands to collect table statistics. This article mainly looks at the differences between the two commands.

 

For more information, see:

Oracle analyze command details

Http://blog.csdn.net/tianlesoftware/article/details/7055164

 

Summary of Oracle statistic statistics

Http://blog.csdn.net/tianlesoftware/article/details/4668723

 

Oracle script for judging and collecting statistics manually

Http://blog.csdn.net/tianlesoftware/article/details/6445868

 

Oracle Analysis and Dynamic sampling

Http://blog.csdn.net/tianlesoftware/article/details/5845028

 

 

1. When the index is in monitoringusage

Monitoringindex usage is described as follows:

Http://docs.oracle.com/cd/E11882_01/server.112/e25494/indexes004.htm#ADMIN11735

 

Oracle databaseprovides a means of monitoring indexes to determine whether they are beingused. If an index is not being used, then it can be dropped, eliminatingunnecessary statement overhead.

-- DB provides the index monitoring method to determine whether the index is used. If it is not used, you can drop it.

 

To start monitoring the usage of an index, issue this statement:

Alter index indexmonitoring usage;

-- Start monitoring index

 

Later, issue the following statement tostop the monitoring:

Alter index indexnomonitoring usage;

-- Stop monitoring index

 

The view v $ object_usage canbe queried for the index being monitored to see if the index has been used. theview contains a used column whose value is yes or no, depending upon if the index has been used within the time period beingmonitored. the view also contains the start and stop times of the monitoringperiod, and a monitoring column (yes/no) to indicate if usagemonitoring is currently active.

-- The V $ object_usage view can be used to view the monitor status. The V $ object_usage view has a used field, which indicates whether the index is used.

 

Explanation of V $ object_usage view on the official website:

V $ object_usage displaysstatistics about index usage gathered from the database for the indexes ownedby the current user. you can use this view to monitor index usage. all indexesthat have been used at least once can be monitored and displayed in this view.

 

Each time thatyou specify monitoring usage, the V $ object_usage view is resetfor the specified index. the previous usage information is cleared or reset, and a new start time is recorded. when you specifynomonitoring usage, nofurther monitoring is already med, and the end time is recorded for the monitoringperiod. until the next alter index... monitoring usage statement isissued, the view information is left unchanged.

-- Each time an index is specified, information about this view in the V $ object_usage view is reset. The previously collected information is also cleared or reset, and is recorded from a new time. When we stop monitoring, the monitoring end time is recorded until the next monitoring.

 

The preceding description of monitoring index only proves the effect of dbms_stats and analyze on monitoring index.

 

The test is as follows:

Sys @ anqing1 (Rac1)> DESC tt;

Name null? Type

-----------------------------------------------------------------------------

Owner varchar2 (30)

Object_name varchar2 (128)

Subobject_name varchar2 (30)

Object_id number

Data_object_id number

Object_type varchar2 (19)

Created date

Last_ddl_time date

Timestamp varchar2 (19)

Status varchar2 (7)

Temporary varchar2 (1)

Generated varchar2 (1)

Secondary varchar2 (1)

 

Sys @ anqing1 (Rac1)> select * From dba_indexes where table_name = 'TT ';

No rows selected

 

Sys @ anqing1 (Rac1)> Create index idx_tt_id on TT (object_id );

Index created.

 

Sys @ anqing1 (Rac1)> select index_name, table_name from dba_indexes where table_name = 'TT ';

 

Index_name table_name

------------------------------------------------------------

Idx_tt_id TT

 

-- View v $ object_usage:

Sys @ anqing1 (Rac1)> select * from V $ object_usage;

No rows selected

 

-- Start monitoring:

Sys @ anqing1 (Rac1)> alter index idx_tt_idmonitoring usage;

Index altered.

 

-- View monitoring:

Sys @ anqing1 (Rac1)> Col table_name formata12

Sys @ anqing1 (Rac1)> Col index_name formata12

Sys @ anqing1 (Rac1)> select * fromv $ object_usage;

 

Index_name table_name mon use start_monitoring end_monitoring

--------------------------------------------------------------------

Idx_tt_id TT Yes No 12/08/2011 13:48:56

-- Note that the use here is no.

 

-- Use analyze to collect table statistics:

Sys @ anqing1 (Rac1)> analyze table TT compute statistics;

Table analyzed.

 

Sys @ anqing1 (Rac1)> select * from V $ object_usage;

Index_name table_name mon usestart_monitoring end_monitoring

--------------------------------------------------------------------

Idx_tt_id TT Yes No 12/08/2011 13:48:56

-- The use here is still no.

 

-- Use the dbms_stats package to collect statistics:

Sys @ anqing1 (Rac1)> exec dbms_stats.gather_table_stats ('sys ', 'TT ');

PL/SQL procedure successfully completed.

 

Sys @ anqing1 (Rac1)> select * fromv $ object_usage;

 

Index_name table_name mon usestart_monitoring end_monitoring

--------------------------------------------------------------------

Idx_tt_id TT yes 12/08/20111:48:56

-- Note that the use is changed to yes.

 

-- Disable monitoring indexes now

Sys @ anqing1 (Rac1)> alter index idx_tt_idnomonitoring usage;

Index altered.

 

Sys @ anqing1 (Rac1)> Col table_name formata12

Sys @ anqing1 (Rac1)> Col index_name formata12

Sys @ anqing1 (Rac1)> select * fromv $ object_usage;

 

Index_name table_name mon usestart_monitoring end_monitoring

--------------------------------------------------------------------

Idx_tt_id TT No Yes 12/08/2011 13:48:56 12/08/2011 13:59:48

-- This is the final state of our monitoringindex.

 

Summary:

When an index is in monitoring usage, if dbms_stats is used to analyze the table and the index is analyzed at the same time, the V $ object_usage.used of the index is set to true, as a result, suspicious indexes monitored for N days are nearly discarded. If analyze is used, the index status is not set to use = true.

 

2. Differences between dbms_stats and analyze

 

This part of content is directly transferred from the ml blog:

Http://www.oracledatabase12g.com/archives/what-dbms_stats-can-not-but-analyze-can-do.html

 

Dbms_stats only collects statistical information useful to CBO. Therefore, some spatial usage information and information managed using freelist are not collected, including:

 

If statistics unrelated to the cost basedoptimizer are required, then these must still be collected using the theanalyze command. These statistics include:

Space usage information:

Empty_blocks,
Avg_space,
Chain_cnt

 

Information on freelistblocks

Avg_space_freelist_blocks, num_freelist_blocks

-- This part is what we mentioned in analyze.

 

Because the above information does not help the CBO computing cost, dbms_stats will not be able to collect them, but the analyze command can still collect the above information.

In addition, because CBO does not calculate the cost by referring to the statistical information of cluster objects, instead, it uses the statistical information of each table in the cluster (dbms_stats does not gathercluster statistics, but you can use dbms_stats to gather statistics on theindividual tables instead of the whole cluster .)

Therefore, dbms_stats does not support collecting cluster statistics.

 

Oracle has defined the function positioning of analyze as the "Validate" Verification command, and many internal tools and scripts are still using the unique features of analyzetable/cluster/index, therefore, we can expect that the analyze command will not be discarded in the future.

 

Dbms_stats cannot be implemented, but the traditional analyze command can do the following:

(1) analyze validate structure verifies the integrity of the table, cluster, and index structures. The cascade option can be used to verify the data integrity of the table and index. Online options can be used for online verification.

(2) analyze list chained rows collects migrated and chained rows chained or migrated row information on tables and clusters.

(3) analyze table compute Statistics collects the space usage information of empty_blocks and avg_space on the table.

(4) analyze cluster collects cluster information. In fact, the only statistical information on the cluster is dba_clusters.avg_blocks_per_key (numberof blocks in the table divided by number of cluster keys). Therefore, it is of little significance to collect cluster statistics.

 

Other differences:

1. For partitioned tables, we recommend that you use dbms_stats instead of the analyze statement.

(1) parallel operations can be performed on multiple users and tables.
(2) Data of the entire Partition Table and data of a single partition can be obtained.
(3) computestatistics can be used at different levels: single partition, subpartition, full table, and all partitions.

(4) generate statistical information
(5) users can automatically collect statistics.

 

2. disadvantages of dbms_stats
(1) The validate structure cannot be used.

(2) You cannot collect chainedrows or clustertable information. You still need to use the analyze statement.
(3) dbms_stats does not perform analyze on the index by default. Because the default cascade is false, you must manually specify it as true.

 

3. for external tables in Oracle 9, analyze cannot be used. You can only use dbms_stats to collect information.

 

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Blog: http://blog.csdn.net/tianlesoftware

WEAVER: http://weibo.com/tianlesoftware

Email: tianlesoftware@gmail.com

Skype: tianlesoftware

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 dba8 group: 102954821