| Stale Stats for Partitoined table [message #382970] |
Mon, 26 January 2009 16:08  |
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;
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
