OraFAQ Forum: Performance Tuning » Stale statistics for table

Home » RDBMS Server » Performance Tuning » Stale statistics for table (Oracle 11g,Linux)

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

Stale statistics for table [message #552754]

Fri, 27 April 2012 21:40

pokhraj_d
Messages: 117
Registered: December 2007

Senior Member

Hi team,

From the below query i found that there are some stale stats for 3 tables.
=================================
select table_name, stale_stats, last_analyzed
from dba_tab_statistics
where owner= 'SYSADM' and stale_stats='YES'
order by last_analyzed desc
======================
I collect stats for those above 3 tables with dbms_stats.gather_table_stats().
But no luck.
After collection of stats immediately I ran the above query.
But still it is showing there are stale stats for 3 tables.

Any suggestion as how can I change "STALE-STATS" status, so that optimizer can use the updated stats eficiently.

Thanks-
P

Report message to a moderator

Re: Stale statistics for table [message #552755 is a reply to message #552754]

Fri, 27 April 2012 22:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I get more expected results

  1  select owner,table_name, stale_stats, last_analyzed
  2  from dba_tab_statistics
  3  where  stale_stats='YES'
  4   and owner = 'SYSMAN'
  5   and  table_name not like ('BIN%')
  6* order by last_analyzed desc
20:30:37 SQL> /

OWNER			       TABLE_NAME		      STA LAST_ANALYZED
------------------------------ ------------------------------ --- -------------------
SYSMAN			       MGMT_METRICS_1HOUR	      YES 2012-04-26 22:01:08
SYSMAN			       MGMT_METRICS_RAW 	      YES 2012-04-26 22:00:50
SYSMAN			       MGMT_SYSTEM_PERFORMANCE_LOG    YES 2012-04-26 22:00:45
SYSMAN			       MGMT_POLICY_ASSOC_EVAL_DETAILS YES 2012-04-26 22:00:44
SYSMAN			       MGMT_CURRENT_METRICS	      YES 2012-04-26 22:00:43
SYSMAN			       MGMT_POLICY_ASSOC_EVAL_SUMM    YES 2012-04-26 22:00:42
SYSMAN			       MGMT_COLLECTION_TASKS	      YES 2012-04-26 22:00:42
SYSMAN			       MGMT_TARGET_ROLLUP_TIMES       YES 2012-04-26 22:00:41
SYSMAN			       MGMT_TARGETS		      YES 2012-04-26 22:00:41
SYSMAN			       MGMT_METRICS_1DAY	      YES 2012-04-25 22:01:34

10 rows selected.

20:17:52 SQL> show user
USER is "SYS"
20:30:56 SQL> exec dbms_stats.gather_schema_stats('sysman');

PL/SQL procedure successfully completed.

20:33:11 SQL> 

  1  select owner,table_name, stale_stats, last_analyzed
  2  from dba_tab_statistics
  3  where  stale_stats='YES'
  4   and owner = 'SYSMAN'
  5   and  table_name not like ('BIN%')
  6* order by last_analyzed desc
20:33:21 SQL> /
no rows selected

20:33:23 SQL>

[Updated on: Fri, 27 April 2012 22:36]

Report message to a moderator

Re: Stale statistics for table [message #552756 is a reply to message #552755]

Fri, 27 April 2012 22:42

pokhraj_d
Messages: 117
Registered: December 2007

Senior Member

ok,
You mean to say, instead of gathering the stats for tables, it is better to gather stats for entire schema?

Am I correct?

Thanks-
P

Report message to a moderator

Re: Stale statistics for table [message #552757 is a reply to message #552756]

Fri, 27 April 2012 22:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>You mean to say, instead of gathering the stats for tables, it is better to gather stats for entire schema?
I do not know if it makes any difference.

I wanted to see if I could get current the SYSMAN schema.
I saw that it worked for me.

>where owner= 'SYSADM'
I don't know who was current schema when you ran your test or if that USER had privilege to change SYSADM statistics.

I just wanted to show you that it worked for me.

Report message to a moderator

Previous Topic:	OWB Cube load SQL tuning
Next Topic:	Query Running for a long time in Second Schema

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 14:22:29 CST 2025