[Fwd: global_stas in dba_tables]sent one more time

From: Joan Hsieh <joan.hsieh_at_tufts.edu>
Date: Fri, 30 Nov 2007 15:17:06 -0500
Message-ID: <47506FC2.4000406@tufts.edu>

Original Message -------- Subject: global_stas in dba_tables Date: Fri, 30 Nov 2007 14:23:09 -0500 From: Joan Hsieh <joan.hsieh_at_tufts.edu> Organization: Tufts University To: oracle_l <ORACLE-L_at_freelists.org>

Hi,

I have a very confusing question regrading the 10g statistics. Particularly with the global_stats in dba_tables. In oracle manual, the globl_stats is defined as below; GLOBAL_STATS
Are the statistics calculated without merging underlying partitions?

Question is; our tables are not partition tables. Based on the global_stats = yes or no, some query will produce different execution plan. I tried created a new table tt,

select table tt as select * from all_objects;

SELECT LAST_ANALYZED, GLOBAL_STATS, NUM_ROWS FROM DBA_TABLES WHERE TABLE_NAME='TT' LAST_ANAL GLO NUM_ROWS
--------- --- ----------

SQL> EXEC
dbms_stats.gather_table_stats(ownname=>'oracle',tabname=>'tt',method_opt=>'for

all columns size 1');

PL/SQL procedure successfully completed.

SQL> SELECT LAST_ANALYZED, GLOBAL_STATS, NUM_ROWS FROM DBA_TABLES WHERE TABLE_NAME='TT'; LAST_ANAL GLO NUM_ROWS
--------- --- ----------

30-NOV-07 YES 50032 SQL> exec dbms_stats.delete_table_stats(ownname=>'oracle',tabname=>'tt');

PL/SQL procedure successfully completed.

SQL> SELECT LAST_ANALYZED, GLOBAL_STATS, NUM_ROWS FROM DBA_TABLES WHERE TABLE_NAME='TT'; LAST_ANAL GLO NUM_ROWS
--------- --- ----------

SQL> analyze table tt compute statistics;

Table analyzed.

SQL> SELECT LAST_ANALYZED, GLOBAL_STATS, NUM_ROWS FROM DBA_TABLES WHERE TABLE_NAME='TT'; LAST_ANAL GLO NUM_ROWS ==> using analyze, global_stat=no
--------- --- ----------

30-NOV-07 NO 50032 SQL> EXEC
dbms_stats.gather_table_stats(ownname=>'oracle',tabname=>'tt',method_opt=>'for

all columns size 1');

PL/SQL procedure successfully completed.

SQL> SELECT LAST_ANALYZED, GLOBAL_STATS, NUM_ROWS FROM DBA_TABLES WHERE TABLE_NAME='TT'; LAST_ANAL GLO NUM_ROWS
--------- --- ----------

30-NOV-07 YES 50032

Confusing here; our 10g database using the default gather stats job to collect stats, I don't understand why the last analyzed date is updated, but some tables global_stats has the value= yes, while some tables didn't get updated? Can someone share shed some light on this?

Thank you so much,

--Joan

1 select table_name, last_analyzed,global_stats from dba_tables where table_name in ('ENTITY',

2*
'TU_GIFT_TOTALS','ENTITY_RECORD_TYPE','ADDRESS','GIFT_CLUBS','GIFT_CLUB_TABLE') SQL> /

TABLE_NAME                     LAST_ANAL GLO


------------------------------ --------- ---

GIFT_CLUBS                     30-NOV-07 NO
GIFT_CLUB_TABLE                30-NOV-07 NO
ENTITY                         30-NOV-07 NO
ADDRESS                        30-NOV-07 NO
TU_GIFT_TOTALS                 29-NOV-07 YES
ENTITY_RECORD_TYPE             30-NOV-07 NO

--

http://www.freelists.org/webpage/oracle-l Received on Fri Nov 30 2007 - 14:17:06 CST