| Package of Analyze Partitions [message #624734] |
Thu, 25 September 2014 01:46  |
 |
loupstr
Messages: 2
Registered: September 2014
Location: ankara
|
Junior Member |
|
|
|
I know and using dbms_stats.gather_table_stats. But, it does not automatically. Now, I cant see that table analysis automatically. I want to write a new package. Because, This package system will work in the background. This package will automatically analyze the new partitions inserted or there is a change in the partitions will automatic update.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Package of Analyze Partitions [message #624747 is a reply to message #624741] |
Thu, 25 September 2014 04:18  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I do not know what you want to do. Is this a task you have been given? If so, provide the specification. If it is all your own idea, you have to explain.
Saying "can not analyze partitions of table" does not help, because you CAN analyze partitions of a table. For example, if you set the INCREMENTAL preference only new or changed partitions will be analyzed.
|
|
|
|
| Re: Package of Analyze Partitions [message #624873 is a reply to message #624747] |
Fri, 26 September 2014 16:12 |
 |
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
|
Senior Member |
|
|
What is your database version? If you are on version 10 or 11 - there should already be scheduled jobs that automatatically check the "Staleness" of your tables and will gather stats if 10% of the data in the table has changed. If you need this threshold to be lower - say 5% - then change the STALE_PERCENT of the table using the DBMS_STATS.SET_TABLE_PREFS procedure. For example:
BEGIN
dbms_stats.set_table_prefs('TABLE_OWNER','YOUR_TABLE_NAME','STALE_PERCENT','5');
END;
Craig...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
