Wednesday, April 22, 2009

Check if CBO stats are locked or stale

Not familiar with DBA_TAB_STATISTICS before. Today happened to find we can check if CBO stats are locked or stale from this view. Here is an example:

SQL> select owner, table_name, last_analyzed,
stattype_locked, stale_stats
from dba_tab_statistics where stale_stats='YES';

OWNER TABLE_NAME LAST_ANALYZED STATT STA
-------------- --------------- ------------------- ----- ---
SYSTEM BIN$ZInWFVYZa0L 2008-11-16 09:11:58 YES
gRAAUT46ylg==$0

SYSTEM BIN$ZJEkpLOwbVj 2009-03-07 18:09:05 YES
gRAAUT46ylg==$0

SYS WRH$_TABLESPACE 2009-04-18 19:35:47 YES
_STAT

SYS WRH$_OSSTAT 2009-04-18 19:34:56 YES
SYS WRH$_SYS_TIME_M 2009-04-18 19:35:46 YES
ODEL

SYS WRH$_SERVICE_WA 2009-04-18 19:35:09 YES
IT_CLASS

SYS WRH$_FILESTATXS 2009-04-18 19:34:38 YES
SYS WRH$_SQLSTAT 2009-04-18 19:35:18 YES
SYS WRH$_SYSTEM_EVE 2009-04-18 19:35:45 YES
NT

SYS WRH$_WAITSTAT 2009-04-18 19:35:51 YES
SYS WRH$_LATCH 2009-04-18 19:34:42 YES
SYS WRH$_LATCH_CHIL 2009-04-18 19:34:48 YES
DREN

SYS WRH$_LATCH_PARE 2009-04-18 19:34:53 YES
NT

SYS WRH$_LATCH_MISS 2009-04-18 19:34:52 YES
ES_SUMMARY

SYS WRH$_DB_CACHE_A 2009-04-18 19:34:33 YES
DVICE

SYS WRH$_ROWCACHE_S 2009-04-18 19:35:01 YES
UMMARY

SYS WRH$_SGASTAT 2009-04-18 19:35:10 YES
SYS WRH$_SYSSTAT 2009-04-18 19:35:44 YES
SYS WRH$_PARAMETER 2009-04-18 19:34:57 YES
SYS WRH$_SEG_STAT 2009-04-18 19:35:06 YES
SYS WRH$_SERVICE_ST 2009-04-18 19:35:09 YES
AT

SYS WRH$_ACTIVE_SES 2009-04-18 19:34:25 YES
SION_HISTORY


22 rows selected.

No comments: