| analyze vs dbms_stats [message #256339] |
Fri, 03 August 2007 19:44  |
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 #256368 is a reply to message #256339] |
Sat, 04 August 2007 01:20  |
 |
Michel Cadot
Messages: 68686
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
