-
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.
-
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
I'm stmontgo and I approve of this message
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|