OraFAQ Forum: Performance Tuning » stale or empty statiscs

Home » RDBMS Server » Performance Tuning » stale or empty statiscs (10.2.0.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

stale or empty statiscs [message #410381]

Fri, 26 June 2009 14:03

varunvir
Messages: 389
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

Report message to a moderator

Re: stale or empty statiscs [message #410382 is a reply to message #410381]

Fri, 26 June 2009 14:07

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

LAST_ANALYZED column in DBA_TABLES has the date of the last statistics.

Report message to a moderator

Re: stale or empty statiscs [message #410383 is a reply to message #410381]

Fri, 26 June 2009 14:10

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: stale or empty statiscs [message #410384 is a reply to message #410381]

Fri, 26 June 2009 14:13

varunvir
Messages: 389
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

Report message to a moderator

Re: stale or empty statiscs [message #410386 is a reply to message #410381]

Fri, 26 June 2009 14:25

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: stale or empty statiscs [message #410402 is a reply to message #410386]

Fri, 26 June 2009 16:42

varunvir
Messages: 389
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 25 16:25:13 CST 2024