Oracle 8161 ...
oraclei@orion> sys
SQL*Plus: Release 8.1.6.0.0 - Production on Fri Aug 9 14:20:18 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.
Connected.
SQL> create table raj_test (col1 varchar2(1));
Table created.
SQL> insert into raj_test values('1');
1 row created.
SQL> commit;
Commit complete.
SQL> analyze table raj_test estimate statistics;
Table analyzed.
SQL> select table_name, BLOCKS,AVG_ROW_LEN from dba_tables where table_name
= 'RAJ_TEST';
TABLE_NAME BLOCKS AVG_ROW_LEN
------------------------------ ---------- -----------
RAJ_TEST 1 5
SQL> analyze table raj_test compute statistics;
Table analyzed.
SQL> select table_name, BLOCKS,AVG_ROW_LEN from dba_tables where table_name
= 'RAJ_TEST';
TABLE_NAME BLOCKS AVG_ROW_LEN
------------------------------ ---------- -----------
RAJ_TEST 1 5
SQL> exec dbms_stats.gather_table_stats('SYS','RAJ_TEST',NULL,20,FALSE);
PL/SQL procedure successfully completed.
SQL> select table_name, BLOCKS,AVG_ROW_LEN from dba_tables where table_name
= 'RAJ_TEST';
TABLE_NAME BLOCKS AVG_ROW_LEN
------------------------------ ---------- -----------
RAJ_TEST 1 2 <<== different
result
============================================================================
==============
Oracle 9.2
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Aug 9 14:35:13 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected.
SQL> create table raj_test (col1 varchar2(1));
Table created.
SQL> insert into raj_test values ('1');
1 row created.
SQL> commit;
Commit complete.
SQL> analyze table raj_test estimate statistics;
Table analyzed.
SQL> select table_name, BLOCKS,AVG_ROW_LEN from dba_tables where table_name
= 'RAJ_TEST';
TABLE_NAME BLOCKS AVG_ROW_LEN
------------------------------ ---------- -----------
RAJ_TEST 1 5
SQL> analyze table raj_test compute statistics;
Table analyzed.
SQL> select table_name, BLOCKS,AVG_ROW_LEN from dba_tables where table_name
= 'RAJ_TEST';
TABLE_NAME BLOCKS AVG_ROW_LEN
------------------------------ ---------- -----------
RAJ_TEST 1 5
SQL> exec dbms_stats.gather_table_stats('SYS','RAJ_TEST',NULL,0,FALSE,NULL);
PL/SQL procedure successfully completed.
SQL> select table_name, BLOCKS,AVG_ROW_LEN from dba_tables where table_name
= 'RAJ_TEST';
TABLE_NAME BLOCKS AVG_ROW_LEN
------------------------------ ---------- -----------
RAJ_TEST 1 5
============================================================================
===============
I did this test because when we did analyze using 'analyze table', things
were fine, but when we used (or tried to use) 'dbms_stats' the queries were
so slow that we could actually see packets moving across the fiber. Okay, we
didn't but a 20 minute query was transformed to a 2 hour 45 minute ordeal.
Bug# 1388282 concludes that in 8i, 'analyze table' was doing something wrong
in calculating average row length, except when used with 'compute statistics
for all columns;' clause, and that 'dbms_stats' was doing the right thing.
This sounds like BS, because it certainly didn't work for us.
Assuming the information in the BUS is true, why did Oracle did a turnaround
in 9i? BTW the results for 9.2 are applicable in 9012 as well. Any details
from 'the people in the know'?? Although our problems have gone away, I'd
like to find out what went wrong.
Raj
______________________________________________________
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
*********************************************************************This e-mail
message is confidential, intended only for the named recipient(s) above and may
contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank
you.*********************************************************************2
