[Fwd: global_stas in dba_tables]sent one more time



-------- Original Message --------
Subject: global_stas in dba_tables
Date: Fri, 30 Nov 2007 14:23:09 -0500
From: Joan Hsieh <joan.hsieh@xxxxxxxxx>
Organization: Tufts University
To: oracle_l <ORACLE-L@xxxxxxxxxxxxx>

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
--------- --- ----------
          NO

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
--------- --- ----------
          NO

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

Other related posts:

  • » [Fwd: global_stas in dba_tables]sent one more time