Home » RDBMS Server » Performance Tuning » Stale Stats for Partitoined table
Stale Stats for Partitoined table [message #382970] Mon, 26 January 2009 16:08 Go to next message
sonumalhi
Messages: 62
Registered: April 2008
Member
Hi All

I am tuning one query using sql tuning advisor.
I ran the advisonr and it gave the recommendations to gather stats on one of the table .

I gathered the stats and checke last_analyzed from dba_tables and it is updated.
Again I ran the advisor it's again recommending to gather stats on that table.

I analyzed the tables 2-3 times with different options.But the advisor still recommending to gather stats.

Table is partitioned and i have gather both gloabl stats and partition level stats.

If anyone can throw some light why the stats are still STALE according to advisor.
Anything i m missing while gathering stats.

I m using following procedure to gather stats with diferent options everytime.

begin

dbms_stats.gather_table_stats(
ownname=> 'PDCAMAC',
tabname=> 'PASSDATA' ,
estimate_percent=> 5,
block_sample=> TRUE,
cascade=> TRUE,
degree=> 4,
no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,
granularity=> 'AUTO',
method_opt=> 'FOR ALL COLUMNS SIZE AUTO');

end;
begin

dbms_stats.gather_table_stats(
ownname=> 'PDCAMAC',
tabname=> 'PASSDATA' ,
estimate_percent=> 5,
block_sample=> TRUE,
cascade=> TRUE,
degree=> 4,
no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,
granularity=> 'PARTITION',
method_opt=> 'FOR ALL COLUMNS SIZE AUTO');

end;

begin

dbms_stats.gather_table_stats(
ownname=> 'PDCAMAC',
tabname=> 'PASSDATA' ,
estimate_percent=> 15,
cascade=> TRUE,
degree=> 4,
no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,
granularity=> 'GLOBAL AND PARTITION',
method_opt=> 'FOR ALL COLUMNS SIZE AUTO');

end;
Re: Stale Stats for Partitoined table [message #382976 is a reply to message #382970] Mon, 26 January 2009 21:01 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
What version of Oracle are we talking about here? (to the 4 digits).
And post what exactly the Tuning Adviser says instead of explaining
Re: Stale Stats for Partitoined table [message #383187 is a reply to message #382976] Tue, 27 January 2009 10:40 Go to previous message
sonumalhi
Messages: 62
Registered: April 2008
Member
Oracle version is 10.2.0.3
OS Solaris 10 x86 64 bit.

Advisor is saying

Stats on table and its' indices are stale.Consider gathering statistics for the table.

Previous Topic: Multiple schemas against single schema - ERP
Next Topic: performance hurt after dropping the parititons
Goto Forum:
  


Current Time: Sun Dec 04 00:33:45 CST 2016

Total time taken to generate the page: 0.12430 seconds