Home » RDBMS Server » Performance Tuning » Stale statistics for table (Oracle 11g,Linux)
Stale statistics for table [message #552754] Fri, 27 April 2012 21:40 Go to next message
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
Re: Stale statistics for table [message #552755 is a reply to message #552754] Fri, 27 April 2012 22:25 Go to previous messageGo to next message
BlackSwan
Messages: 22546
Registered: January 2009
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 Go to previous messageGo to next message
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
Re: Stale statistics for table [message #552757 is a reply to message #552756] Fri, 27 April 2012 22:51 Go to previous message
BlackSwan
Messages: 22546
Registered: January 2009
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.
Previous Topic: OWB Cube load SQL tuning
Next Topic: Query Running for a long time in Second Schema
Goto Forum:
  


Current Time: Thu Jul 31 15:32:28 CDT 2014

Total time taken to generate the page: 0.09978 seconds