DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Analyze Partitions

  1. #1
    Join Date
    Jul 2001
    Posts
    181

    Talking 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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    funny but the manuals detal all of this

    use the partname => option to dbms_stats

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Jul 2001
    Posts
    181
    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.

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    define 'active' partition

  6. #6
    Join Date
    Jul 2001
    Posts
    181
    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.

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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)

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    May 2002
    Posts
    2,645
    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.

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Let me humbly suggest that YYYYMM would be a superior choice to MMYY
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width