global_stas in dba_tables
- From: Joan Hsieh <joan.hsieh@xxxxxxxxx>
- To: oracle_l <ORACLE-L@xxxxxxxxxxxxx>
- Date: Fri, 30 Nov 2007 14:23:09 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » global_stas in dba_tables
