Home » RDBMS Server » Performance Tuning » stale or empty statiscs (10.2.0.2.0)
stale or empty statiscs [message #410381] Fri, 26 June 2009 14:03 Go to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Hi Experts,
I have one basic question:-
How can we know if the statiscs on a table are stale or empty.
Regards,
Varun Punj
Re: stale or empty statiscs [message #410382 is a reply to message #410381] Fri, 26 June 2009 14:07 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
LAST_ANALYZED column in DBA_TABLES has the date of the last statistics.
Re: stale or empty statiscs [message #410383 is a reply to message #410381] Fri, 26 June 2009 14:10 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>How can we know if the statiscs on a table are stale or empty.
Empty is easy because no statistics exist.
Stale is next to impossible to determine programatically.
If LAST_ANALYZED is a "long" time ago but the underlying data has not changed, then statistics are not stale.

If LAST_ANALZED is "recent" but data has changed significantly, then statistics are stale.
.
You could just trust Oracle to keep them current
Re: stale or empty statiscs [message #410384 is a reply to message #410381] Fri, 26 June 2009 14:13 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
We can find stale statisics from the following query:-
SELECT * FROM DBA_TAB_STATISTICS WHERE STALE_STATS='YES';

Please tell me how to find information about empty statistics.
Regards,
Varun Punj
Re: stale or empty statiscs [message #410386 is a reply to message #410381] Fri, 26 June 2009 14:25 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
  1  select owner, stale_stats, count(*)
  2  from DBA_TAB_STATISTICS
  3  group by owner,stale_stats
  4* order by 2
SQL> /

OWNER			       STA   COUNT(*)
------------------------------ --- ----------
CTXSYS			       NO	   37
DBADMIN 		       NO	    5
DBSNMP			       NO	   18
DMSYS			       NO	    2
EXFSYS			       NO	   42
MDSYS			       NO	   41
OLAPSYS 		       NO	  114
ORDSYS			       NO	    4
OUTLN			       NO	    3
SCOTT			       NO	    4
SYS			       NO	  720
SYSMAN			       NO	  336
SYSTEM			       NO	  161
TSMSYS			       NO	    1
WMSYS			       NO	   40
XDB			       NO	   11
SYS			       YES	   37
SYSMAN			       YES	    4
DBADMIN 				    9
DBSNMP					    3
EXFSYS					    2
MDSYS					   13
OLAPSYS 				   12
SYS					  651
SYSMAN					    1
SYSTEM					    7
WMSYS					    2

27 rows selected.
Re: stale or empty statiscs [message #410402 is a reply to message #410386] Fri, 26 June 2009 16:42 Go to previous message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Thanks BlackSwan and Thomas...

[Updated on: Fri, 26 June 2009 16:43]

Report message to a moderator

Previous Topic: BACKUP OF HISTOGRAMS
Next Topic: oracle 11g - 11.0.7.0
Goto Forum:
  


Current Time: Sat Dec 10 20:17:39 CST 2016

Total time taken to generate the page: 0.09122 seconds