The difference between dbms_stats.gather_table_stats and analyze table [repost]

Analyze Statement
The ANALYZE statement can be used to gather statistics for a specific table, index or cluster. The statistics can is computed exactly, or estimated based on a specific number of rows, or a percentage of rows:

ANALYZE TABLE Employees COMPUTE STATISTICS;
ANALYZE INDEX employees_pk COMPUTE STATISTICS;

ANALYZE TABLE Employees ESTIMATE STATISTICS, SAMPLE ROWS;
ANALYZE TABLE Employees ESTIMATE STATISTICS SAMPLE PERCENT;
Dbms_utility
The Dbms_utility package can is used to gather statistics for a whole schema or database. Both methods follow the same format as the Analyze statement:

EXEC dbms_utility.analyze_schema (' SCOTT ', ' COMPUTE ');
EXEC dbms_utility.analyze_schema (' SCOTT ', ' ESTIMATE ', estimate_rows = 100);
EXEC dbms_utility.analyze_schema (' SCOTT ', ' ESTIMATE ', estimate_percent = 15);

EXEC dbms_utility.analyze_database (' COMPUTE ');
EXEC dbms_utility.analyze_database (' ESTIMATE ', estimate_rows = 100);
EXEC dbms_utility.analyze_database (' ESTIMATE ', estimate_percent = 15);
Dbms_stats
The Dbms_stats package is introduced inOracle8i and is Oracles preferred method of gathering object statistics. Oracle List A number of benefits to using it including parallel execution, long term storage of statistics and transfer of Statistics between servers. Once again, it follows a similar format to the other methods:

EXEC Dbms_stats.gather_database_stats;
EXEC dbms_stats.gather_database_stats (estimate_percent = 15);

EXEC dbms_stats.gather_schema_stats (' SCOTT ');
EXEC dbms_stats.gather_schema_stats (' SCOTT ', estimate_percent = 15);

EXEC dbms_stats.gather_table_stats (' SCOTT ', ' EMPLOYEES ');
EXEC dbms_stats.gather_table_stats (' SCOTT ', ' EMPLOYEES ', estimate_percent = 15);

EXEC dbms_stats.gather_index_stats (' SCOTT ', ' employees_pk ');
EXEC dbms_stats.gather_index_stats (' SCOTT ', ' employees_pk ', estimate_percent = 15);
This package also gives you the ability to the delete statistics:

EXEC Dbms_stats.delete_database_stats;
EXEC dbms_stats.delete_schema_stats (' SCOTT ');
EXEC dbms_stats.delete_table_stats (' SCOTT ', ' EMPLOYEES ');
EXEC dbms_stats.delete_index_stats (' SCOTT ', ' employees_pk ');

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

Since Oracle8.1.5 introduced the Dbms_stats package, experts has recommended using dbms_stats instead of analyze. For the following reasons

Dbms_stats can be analyzed in parallel
Dbms_stats features automatic analysis (ALTER TABLE monitor)
Analyze inaccurate some of statistical information


Good understanding, and the 2nd is actually the most attractive in VLDB, 3 before the more vague, read metalink236935.1 explanation, analyze in the analysis of partition table, sometimes calculate the inaccurate global statistics.

The reason is that dbms_stats will actually parse the table global statistics (when specifying parameters), whereas analyze is the statistics summary of table partitioning (local) is calculated as a global statistics, which can lead to errors.

If you want to analyze the entire user orDatabase, you can also use a toolkit that can be analyzed in parallel
Dbms_utility (8i old toolkit)
Dbms_stats (Toolkit available after 8i)
Such as
Dbms_stats.gather_schema_stats (user,estimate_percent=>100,cascade=> TRUE);
Dbms_stats.gather_table_stats (User,tablename,degree = 4,cascade = True);

This is a summary of the command and toolkit

1, forPartition Table, it is recommended to use Dbms_stats instead of using the Analyze statement.
A) can be done in parallel, for multiple users, multiple table
b) data from the entire partitioned table and data for a single partition can be obtained.
c) can be compute at different levels Statistics: Single partition, sub-partition, full table, all partitions
d) Statistics can be poured out
e) Users can automatically collect statistical information

2, Dbms_stats's shortcomings
A) cannot validate Structure
b) cannot collect chained ROWS and cannot collect cluster table information, both of which still need to use the Analyze statement.
c) Dbms_stats default does not analyze the index because the default cascade is false and needs to be specified manually as true

3, for Oracle 9 inside the external table,analyze can not be used, can only use Dbms_stats to collect information.

-----------------------------------------------------------------
This is what the 10G document says:
The COMPUTE and ESTIMATE clauses of the ANALYZE to collect optimizer statistics. These clauses is supported for backward compatibility. Instead, use the Dbms_stats package, which lets you collect statistics in parallel, collect global statistics for partitio Ned objects, and fine tune your statistics collection in other ways. The Cost-based optimizer, which depends upon statistics, would eventually use only statistics that has been collected by D Bms_stats

The functionality of the Analyze has been clarified:
Use the ANALYZE statement (rather than dbms_stats) Forstatistics collection not related to the cost-based optimizer:

To use the VALIDATE or LIST CHAINED ROWS clauses

To collect information on freelist blocks

Analyze statements are better than dbms_stats packages when collecting statistical information unrelated to the CBO optimizer

-----------------------------------
EX:
Begin
For owner in (the select username from dba_users where username is not in (' SYS ', ' SYSTEM '))
Loop
dbms_output.disable;
Dbms_output.enable (1000000);
Dbms_output.put_line (' Schema: ' | | Owner.username);
Select Sysdate to start_time from dual;
Dbms_output.put_line (' Analyze start from: ' | | Start_time);
Dbms_stats.gather_schema_stats (Ownname = Owner.username, estimate_percent, block_sample=> True, Cascade=>true);
Select Sysdate to end_time from dual;
Dbms_output.put_line (' Analyze complete at: ' | | End_time);
Dbms_output.put_line ('---------------------------');
End Loop;


Dbms_stats.gather_table_stats (Ownname =>
TabName =>
PartName =>
Estimate_percent =>
Block_sample =>
Method_opt =>
Degree =>,parallel degree (parallel collection of dimensions) See CPU Count
Granularity =>
Cascade =>,true is also gather columns and index ' s statistics;
No_invalidate =);

/////////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////////

When the entire block of the index becomes an empty block, it is placed on the freelist and can be reused. However, the reuse of the pre-structure or temporarily placed in the B-tree.

At this time analyze & Dbms_stats will be different in the analysis, analyze will calculate these empty blocks as the leaf block, and dbms_stats do not count it.

Take a look at examples

1 CREATE TABLE Test
2 AS
3 Select RowNum x
From Dba_objects
Sql>/

Table created.

Sql> Select COUNT (*) from test;

COUNT (*)
———-
6114

1 CREATE INDEX TEST_IDX on test (x)
Pctfree 95
Sql>/

Index created.

sql> Analyze table test compute statistics;

Table analyzed.

Sql> Select index_name,blevel,leaf_blocks,distinct_keys,num_rows,last_analyzed from dba_indexes where index_name= ' Test_idx ' and wner= ' SYS ';

Index_name blevel leaf_blocks Distinct_keys num_rows last_analyzed
—————————— ———- ———– ————- ———- —————–
Test_idx 1 408 6114 6114 20060513 01:00:25

sql> Delete from test where x<3000;

2999 rows deleted.

Sql> commit;

Commit complete.

sql> Analyze index TEST_IDX compute statistics;

Index analyzed.

Sql> Select index_name,blevel,leaf_blocks,distinct_keys,num_rows,last_analyzed
2 from dba_indexes where index_name= ' test_idx ' and wner= ' SYS ';

Index_name blevel leaf_blocks Distinct_keys num_rows last_analyzed
—————————— ———- ———– ————- ———- —————–
Test_idx 1 408 3115 3115 20060513 01:03:31

Sql> exec dbms_stats. Gather_index_stats (' SYS ', ' test_idx ');

PL/SQL procedure successfully completed.

Sql> Select index_name,blevel,leaf_blocks,distinct_keys,num_rows,last_analyzed
2 from dba_indexes where index_name= ' test_idx ' and wner= ' SYS ';

Index_name blevel leaf_blocks Distinct_keys num_rows last_analyzed
—————————— ———- ———– ————- ———- —————–
TEST_IDX 1 209 3115 3115 20060513 01:04:28

From:http://www.dbafan.com/blog/?p=21

////////////////////////////////////////////////////////////////////////////////////////////////////////////

We know that starting with oracle8i,analyze statements and dbms_stats packages can collect related objects (Tables﹑ Indexes﹑ Clusters and materialized views) of statistics. Which statistics collection should use the analyze statement, and which statistics collection should use the dbms_stats package ?

for use which to collect statistics, should put a principle, generally with cost-based optimizer related statistics, should all pass through dbms_stats Packet collection. lang= zh-cn "en-US" statistics " cost-based optimizer empty blocks,average spaceanalyze statements.

The reason to use the Dbms_stats package to replace the Analyze Collection optimizer statisticsis because dbms_stats packages can collect parallel statistics and the global statistics of the partition object .

Of course, the analyze statement in other aspects of the statistics Collection, is dbms_stats can not be replaced, such as:

1. collection of blocks information on freelist

2. verifying the legitimacy of storage formats

Analyze table BK_TEST_T validate structure cascade online;

3. Identifying Row migration and row links for tables or cluster

In order to use the analyze....list chained rows statement to identify row migrations and row links, you must first execute the $ORACLE in the schema in which the Analyze statement is executed _ The Home/rdbms/admin/utlchain.sql ( or utlchn1.sql) script establishes the chained_rows table. after the chained_rows is established, you can execute the following statement:

Analyze table bk_test list chained rows into chained_rows;

Dbms_stats.gather_table_stats and analyze table differences [paste]