new partition slows down report
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: new partition slows down report

Hybrid View

  1. #1
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316

    new partition slows down report

    I have a table that is partitioned by date range - weekly partitions. The new partition starts every Monday. Data is loaded into the table during the day. Then a report is run nightly; that takes about an hour.

    But recently, the report has started to take 4 hrs each Monday ! It runs in just 1 hour on the other weekdays. IT IS NOT A SCHEDULING ISSUE. The only thing I could think of was a fresh partition was being queried - has anyone else run into something like it ? Does it makes sense ?

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Is the new partition analyzed?

    Does the query include the partitioning key as a result object, or as a predicate, or at all?

    Is the table analyzed as a whole, as well as the individual partitions?
    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,092
    Is the table analyzed before any data is put into it?
    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
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    It's been our practice to analyze the table/index partition as well as the global table/indicies as soon as the partitions are added. So the stats are always zero for those partitions. In the past we have hardly ever analyzed the partitions or global tables/indicies after the partitions are loaded with data - I know we probably should, but since we don't get any complaints of performance, we stick by the "if it ain't broke ..." rule !

    Is the table analyzed as a whole, as well as the individual partitions?
    Yes, the query does have the partition key in the where clause.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    you might try the global analyze to see if it changes things for the better.

    you might also consider saving the execution plan for the query,once it's running well, for comparison in the future if the report does slow down again.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #6
    Join Date
    Oct 2002
    Posts
    182
    do you analyze the table/indexes/partition before running the report?
    considering it is loading data into the new partition on monday without being analyzed ...
    - Cookies

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    Just stop running the reports, everyone knows they are a load of horse sh*t anyway. Save a tree



    MH
    I remember when this place was cool.

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