Oracle-L: Analyze Vs DBMS Stats Anomaly

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Fri, 09 Aug 2002 11:48:32 -0800
Message-ID: <F001.004B0EE8.20020809114832@fatcity.com>

Oracle 8161 ...

oraclei_at_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!

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



text/plain attachment: InterScan_Disclaimer.txt

Received on Fri Aug 09 2002 - 14:48:32 CDT