Re: When to Commit?

Mark D Powell wrote:

> As far as long running jobs go we do not currently have any that run
> more than a couple hours so I will have to reference tasks that I have
> seen but do not have access to. I visited with some competitors a
> couple of years back. There were running OPS on two 12-cpu machines.
> We were talking about their 11T warehouse. It turned out the largest
> portion of the warehouse was actually an OLTP system whose primary
> function was to generate billing.

Point of order ... a system whose primary purpose is to generate reports (billing) is not doing OLTP.

> They would generate line item detail
> invoices that often numbered over 1,000 pages for every customer they
> had. I will take their word that the time to perform the physical IO
> just to read the data took over 12 hours.

I've had two like that in the past two years: Both in telecom. One had a cycle to print bills that involved queries that took 5 days to run. So here's why I say much of this is design issues. Looked at what they were doing and they calculated each billing cycle by going back to the beginning of time and verifying no adjustments had been made. Looked at their PCTFREE and PCTUSED parameters and determined that 50% of each block read from disk was pure air. Altered their storage strategy and implemented aggregated summarizations of previous months and partitioning. The entire 60+ hour cycle dropped to 17 hours and each individual customer, the logical unit, down to minutes.

> I do not believe that PQO
> was in use. The process was a single job. But arguing about the fact
> this process could have been improved to run in less time would still
> not change the fact that it would still need to commit as it ran
> because even an improved process would hold locks too long if other
> processed needed to access the same rows.

I don't see any justification for any locking in what you've presented. It may well be there but I don't see it. One bills on completed transactions not current transactions so what is to lock?

> An example might be the
> order header rows with said rows being locked because they had been
> marked as invoiced You cannot block adding new detail items for the
> current month's bill just because the prior month's bill is being
> generated.
>
> HTH - Mark D Powell -

Refer to my comments on how we solved the "previously invoiced" issue by using aggregations and partitioning. If I couldn't get the entire thing down to minutes per customer I wouldn't charge for my time and I'll bet there are a dozen or more others reading this thread that would gladly jump at the opportunity to do the same.

My point is that I may well be wrong given the limited information here, and it might be that a different solution would be required, but I see the issue as bad design and nothing you have stated here leads me to think otherwise.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)