Analyze Partitions

I am due to partition a large audit table. and will use dbms_stats to analyze the table.

My question is I only want to analyze the active partition and not historically ones (theoritically already analyzed!) or future partitions not yet used.

Just want to analyzed partitions in use!

Any help would be greatly appreciated on how I can do this.

Thanks

funny but the manuals detal all of this

use the partname => option to dbms_stats

Don't overdo it on the estimate percent either. Low values (1-3%) with block-based sampling often produce good statistics, particularly on something like an audit table from which there are never any deletes.

Hi,

Thanks for repsonse.

Partname yes -- happy with that, have read that..

What I meant was the ability to dynamically change the active partition name. And not have dbms_stats mulitple times hardcoding all partition names. The ability to calculate active partition only

Hope this is clearer.

define 'active' partition

Wow,

I guess the tone can be misconstrued in mails I won't bite as I guess thats the common reaction!!. And I always like to think outside the box..

And I am asking for help after all. Lets hope it's worth the wait.

Here goes...

'Active' Partition currently active e.g online and being updated and written to.

ok, well how ould the databas eknow the others arent active?

they might not have been written to for a day or so but under your ruls it could still be active

(so you need to specify the partition name)

With monitoring turned on for the table you can read which partitions/subpartitions have changed from the user_tab_modifications view and base the partition list on that, or alternatively use the gather stale option of DBMS_STATS to do that for you.

If you have queries that do not use partition pruning then you'll want to keep your global stats reasonably surrent also

If your partitions are named using something based off of a date or sequence, then you can target your "active" partition as long as the partition name follows a standard naming convention. For example, if you use "some_table_part_MMYY" as the name, then the current month/current partition is ID'd by getting the MMYY part out of sysdate.

Let me humbly suggest that YYYYMM would be a superior choice to MMYY ;)

Quote:

---

Originally Posted by slimdave

Let me humbly suggest that YYYYMM would be a superior choice to MMYY ;)

---

I would doubly humbly suggest that. Also, don't neglect your global statistics, especially after a partition maintenance operation. Personally, I let Oracle figure out what he thinks needs to be analyzed.

http://marist89.blogspot.com/2005/06...sstats_14.html

Quote:

---

Originally Posted by stecal

If your partitions are named using something based off of a date or sequence, then you can target your "active" partition as long as the partition name follows a standard naming convention. For example, if you use "some_table_part_MMYY" as the name, then the current month/current partition is ID'd by getting the MMYY part out of sysdate.

---

yeah but..i recenty did the same thing with a pay calc process in PeopleCrap. to my suprise I saw that the ***arcvhive partition was getting creamed....the proof is the pudding...have a look at segment_stats and file stats to verify