Difference between dbms_stats.gather_table_stats and analyze table [posting]

 

Reference http://www.itpub.net/viewthread.php? Tid = 845777 & extra = & page = 1
Analyze statement
The analyze statement can be used to gather statistics for a specific table, index or cluster. the statistics can be 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 100 rows;
Analyze table employees estimate statistics sample 15 percent;
Dbms_utility
The dbms_utility package can be 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 ', 'estime', estimate_rows => 100 );
Exec dbms_utility.analyze_schema ('Scott ', 'estime', estimate_percent => 15 );

Exec dbms_utility.analyze_database ('compute ');
Exec dbms_utility.analyze_database ('estime', estimate_rows => 100 );
Exec dbms_utility.analyze_database ('estime', estimate_percent => 15 );
Dbms_stats
The dbms_stats package was introduced in
Oracle8i and is supported les 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 ', 'ployees ');
Exec dbms_stats.gather_table_stats ('Scott ', 'ployees', estimate_percent => 15 );

Exec dbms_stats.gather_index_stats ('Scott ', 'ployees _ pK ');
Exec dbms_stats.gather_index_stats ('Scott ', 'ployees _ pK', estimate_percent => 15 );
This package also gives you the ability to delete statistics:

Exec dbms_stats.delete_database_stats;
Exec dbms_stats.delete_schema_stats ('Scott ');
Exec dbms_stats.delete_table_stats ('Scott ', 'ployees ');
Exec dbms_stats.delete_index_stats ('Scott ', 'ployees _ pK ');

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

Since oracle8.1.5 introduced the dbms_stats package, we recommend that you replace analyze with dbms_stats. The reasons are as follows:

Dbms_stats can be analyzed in parallel
Dbms_stats supports automatic analysis (alter table Monitor)
Inaccurate analyze analysis statistics some times

1, 2 is easy to understand, and 2nd points are actually the most attractive in vldb; 3 was vague before. 3. After reading metalink21_35.1's explanation, analyze analyzed the partition table, sometimes the accountant calculates the inaccurate global statistics.

The reason is that dbms_stats will actually analyze the global statistics of the table (when the parameter is specified), while analyze calculates the statistics of the table partition (partial) into the global statistics of the table, which may lead to errors.

If you want to analyze the entire user orDatabaseYou can also use the toolkit for parallel analysis.
Dbms_utility (Toolkit earlier than 8i)
Dbms_stats (Toolkit provided after 8i)
For example
Dbms_stats.gather_schema_stats (user, estimate_percent = & gt; 100, cascade = & gt; true );
Dbms_stats.gather_table_stats (user, tablename, Degree => 4, cascade => true );

This is a summary of the commands and toolkit.

1.Partition TableInstead of using the analyze statement.
A) It can be performed in parallel for multiple users and tables.
B) data of the entire Partition Table and data of a single partition can be obtained.
C) Compute statistics at different levels: single partition, sub-partition, full table, all partitions
D) generate statistical information.
E) users can automatically collect statistics.

2. disadvantages of dbms_stats
A) It cannot be validate structure.
B) You cannot collect chained rows or cluster Table information. You still need to use the analyze statement.
C) 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.

-----------------------------------------------------------------
The 10g document says this:
Do not use the compute and estimate clses of analyze to collect optimizer statistics. these clauses are supported for backward compatibility. instead, use the dbms_stats package, which lets you collect statistics in parallel, collect global statistics
Partitioned objects, and fine tune your statistics collection in other ways. The cost-based optimizer, which depends upon statistics, will eventually use only statistics that have collected by dbms_stats

The functions of analyze are clear:
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 clses

To collect information on freelist Blocks

When collecting statistics that are not related to the CBO optimizer, the analyze statement is better than the dbms_stats package.

-----------------------------------
Ex:
Begin
For owner in (select username from dba_users where username not in ('sys ', 'system '))
Loop
Dbms_output.disable;
Dbms_output.enable (1000000 );
Dbms_output.put_line ('schema: '| Owner. username );
Select sysdate into start_time from dual;
Dbms_output.put_line ('analyze start from: '| start_time );
Dbms_stats.gather_schema_stats (ownname => owner. username, estimate_percent => 20, block_sample => true, cascade => true );
Select sysdate into 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 dimension) to view the number of CPUs
Granularity =>,
Cascade =>, true is also gather columns and index's statistics;
No_invalidate => );

 

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

 

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

At this time, analyze & dbms_stats will be different during analysis. Analyze will calculate these empty blocks as leaf blocks, while dbms_stats will not include them.

See the example

1 Create Table Test
2
3 select rownum x
4 * From dba_objects
SQL>/

Table created.

SQL> select count (*) from test;

Count (*)
----
6114

1 create index test_idx on test (X)
2 * 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 owner = '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 owner = '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 owner = '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 from Oracle8i, analyze statements and dbms_stats packages can collect related objects (tables, indexes, Clusters
And materialized views. Which statistics collection should use the analyze statement and which statistics collection should use the dbms_stats package ﹖

 

For which statistics is used to collect statistics, the statistics related to cost-based optimizer should be collected through the dbms_stats package. And cost-based
Optimizer-independent statistics (such as empty blocks and average space) should be collected through the analyze statement.

The dbms_stats package is used to replace the analyze collection optimizer statistics because the dbms_stats package can collect parallel statistics and global statistics of partition objects.

Of course, the statistics collection of analyze statements in other aspects cannot be replaced by dbms_stats, such ﹕

1. Collect blocks information on freelist

2. Check the validity of the storage format

Analyze table bk_test_t validate Structure Cascade online;

3. Identify row migration and row link of a table or cluster

In order to use analyze .... The list chained rows statement identifies row migration and row link. You must first execute $ ORACLE_HOME/rdbms/admin/utlchain In the schema where the analyze statement is executed. the SQL (or utlchn1. SQL) script creates the chained_rows table. After chained_rows is created, You can execute the following statement ﹕

Analyze table bk_test list chained rows into chained_rows;