30 January 2007
Were those statistics collected with dbms_stats or analyze command?
If the column dba_tables.global_stats is:
SQL> analyze table tanya compute statistics;
Table analyzed.
SQL> select OWNER, TABLE_NAME, GLOBAL_STATS
from dba_tables where table_name='TANYA';
OWNER TABLE_NAME GLO
----------- ---------------- ---
SYS TANYA NO
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SYS','TANYA');
PL/SQL procedure successfully completed.
SQL> select OWNER, TABLE_NAME, GLOBAL_STATS
from dba_tables where table_name='TANYA';
OWNER TABLE_NAME GLO
----------- ---------------- ---
SYS TANYA YES
Thanks to Keith Harding for sharing this interesting information with me.
From Metalink Note 236935.1:
- "NO" => analyze command
- "YES" => dbms_stats
SQL> analyze table tanya compute statistics;
Table analyzed.
SQL> select OWNER, TABLE_NAME, GLOBAL_STATS
from dba_tables where table_name='TANYA';
OWNER TABLE_NAME GLO
----------- ---------------- ---
SYS TANYA NO
SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SYS','TANYA');
PL/SQL procedure successfully completed.
SQL> select OWNER, TABLE_NAME, GLOBAL_STATS
from dba_tables where table_name='TANYA';
OWNER TABLE_NAME GLO
----------- ---------------- ---
SYS TANYA YES
Thanks to Keith Harding for sharing this interesting information with me.
From Metalink Note 236935.1:
- When ANALYZE gathers statistics GLOBAL_STATS is always set to NO
- When DBMS_STATS gathers statistics, the GLOBAL_STATS column is populated thus:
- GLOBAL_STATS = YES - means global statistics have been gathered
- GLOBAL_STATS = NO - means statistics will be derived by aggregation from lower level objects
