Home » RDBMS Server » Server Administration » analyze vs dbms_stats
analyze vs dbms_stats [message #256339] Fri, 03 August 2007 19:44 Go to next message
alantany
Messages: 115
Registered: July 2007
Senior Member

Hello,all!

I have a new question on analyze and dbms_stats.
I did a test below:
SQL> create table tab1(a number(1));
Table created

SQL> create table tab2(a char(1));
Table created

SQL> insert into tab1 values(1);
1 row inserted

SQL> insert into tab2 values('1');
1 row inserted

SQL> commit;
Commit complete

SQL> analyze table tab1 compute statistics;
Table analyzed

SQL> analyze table tab2 compute statistics;
Table analyzed

SQL> select table_name,AVG_ROW_LEN from user_tables where table_name in ('TAB1','TAB2');
TABLE_NAME AVG_ROW_LEN
------------------------------ -----------
TAB1 6
TAB2 5

SQL> exec dbms_stats.gather_table_stats(user,'TAB1')
PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(user,'TAB2')
PL/SQL procedure successfully completed

SQL> select table_name,AVG_ROW_LEN from user_tables where table_name in ('TAB1','TAB2');
TABLE_NAME AVG_ROW_LEN
------------------------------ -----------
TAB1 3
TAB2 2

I have 2 questions:
1:Why the results of ANALYZE and DBMS_STATS are different?

2:I think lenth of columns of tab1 and tab2 is 1 bytes,but the result is 2,3(using DBMS_STATS) and
5,6(using ANALYZE),why?
I would be very thankful if ¿you give me some explain!

Best Regards!
Alan

Re: analyze vs dbms_stats [message #256347 is a reply to message #256339] Sat, 04 August 2007 00:14 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,

Gather statistics through DBMS_STATS instead of ANALYZE command.

ANALYZE is for backword compability and statistics is not accurate.

http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_4005.htm#SQLRF01105


Regards
Taj

Re: analyze vs dbms_stats [message #256368 is a reply to message #256339] Sat, 04 August 2007 01:20 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
One includes row/column header the other one no. The difference is small in real cases.
As Mohammad said, no more use analyze to compute optimizer statistics use dbms_stats.

Regards
Michel
Previous Topic: how can exceeded Tablespace quota in oracle10g
Next Topic: list-list composite partition
Goto Forum:
  


Current Time: Mon Dec 05 19:24:21 CST 2016

Total time taken to generate the page: 0.06497 seconds