Statistics & Empty Partitions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Statistics & Empty Partitions

  1. #1
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095

    Statistics & Empty Partitions

    I compute statistics every weekend.

    On a table partioned by month, I have approximately 30 million rows in each partition, or about 1.3 million rows per business day. When statistics get computed on Sunday, the "December" partition is empty. However, when the data starts rolling in Monday morning, the CBO chooses a full table scan for all my queries. Once there is some data, I re-calculate statistics for just the "December" partition and the queries correctly choose the index again.

    How do you handle this situation? I thought of a couple possibilities:
    1. Use plan stability. Are the drawbacks to Plan Stability worth the extra effort?

    2. Delete statistics for the partitioned table to force RBO. While I'm not using any of the features that need CBO to work (for this table), I'd prefer to keep my options open in the future.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The RBO option is not viable -- the partitioning forces the use of CBO.

    I would also stay away from plan stability for DW applications, as it would do nothing for ad-hoc queries, and you really want the plan to be sensitive to histograms -- i don't think that plan stability would work in this case.

    How about turning on monitoring for the table? This works at the partition level, and would allow you to run DBMS_STATS.GATHER_SCHEMA_STATS() with the gather_stale option to get good stats as soon as the data is flowing in.

    You might also try running DBMS_STATS.SET_TABLE_STATS() and DBMS_STATS.SET_INDEX_STATS() after your weekend analyze. You can setup expected statistics at the partition level for both table and index that way. I'm not sure how this interfaces with table monitoring, but if you got these forecasted stats close enough on sunday night, then you could defer gathering stale stats until Monday night.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by slimdave

    How about turning on monitoring for the table? This works at the partition level, and would allow you to run DBMS_STATS.GATHER_SCHEMA_STATS() with the gather_stale option to get good stats as soon as the data is flowing in.

    This is an 8.1.7 database. I think table monitoring is a 9i feature?


    You might also try running DBMS_STATS.SET_TABLE_STATS() and DBMS_STATS.SET_INDEX_STATS() after your weekend analyze. You can setup expected statistics at the partition level for both table and index that way. I'm not sure how this interfaces with table monitoring, but if you got these forecasted stats close enough on sunday night, then you could defer gathering stale stats until Monday night.
    Excellent Idea!
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Oh right -- well i guess in your case you'd know which partition is stale, so it don't matter so much.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  5. #5
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    I like Dave's idea about Setting the statistics.

    But just for future reference, GATHER_SCHEMA_STATS works in 8i. I use it every week.

    Have a great day!
    Jodie

  6. #6
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    You know speaking of stats.
    Anyone have an idea why Oracle keeps the DBMS_UTILITY version of running stats around? From all I've read the DBMS_STATS package is much better.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

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