How 2 split priority, reports low, transactions fast

I'm at a loss (and, like a hard-core alcoholic) the "experts" I'm working with won't even acknowledge there is a problem. . .

Is there any way to run "interactive transactions" at one priority and yet run long running reports at a lower priority.

From a raw Unix/Linux/AIX standpoint, I understand and have used "nice" of course. The problem arises when the "interactive requests" coming from the front line users need priority, and the long running reports/queries are both running to the same Oracle DB. (Through the same PL/SQL, same instances. . .)

It's not that the long running reports would take that long to run, if they ran at 1/2 priority compared to the rest of the system. Its that when they are running. . . they seem to "want it all", and they "want it all RIGHT NOW" to the exclusion of all else. (The other "individual user" transactions performing simple-single updates/queries suffer horrible response times while the long running query runs.)

Is there any way to run some queries / "sessions" (/threads/??? somethin) or use some other segregating feature, to what is ultimately the same back end Oracle Instance?

We've discussed pushing copies of the DB to another box and letting a 2nd cpu run the "reports". Its not the cost/difficulty so much as the "live syncing" makes that unacceptable.

The current "experts" have two suggestions. . .

#1 Let them (the users) run the reports from the Datawarehouse. (Which
is in another country, beyond comm lines that can't be trusted to be up 24x7. So it's in impractical solution for any "report" that is critical to production line uptime.)

#2 Put a limit on query run length. (Which is a half-a$$ed non-
 since it doesn't actually "finish" the long running report, it just kills it instead.)

So . . . is there any way to run the long-running report at a lower priority, so that it "eventually" gets done, but doesn't bog down the individual users entering new data and using the "day to day" single transactions?

Yes, I suppose adding CPU's is an option. . . and I'm sure adding memory is an option, and I'm sure sprinkling fairy dust is too. . . But, if (as in 99% of the real world cases) money / resources aren't infinitely available. . . can Oracle have "paths of inquiry" be split/prioritized in any way?

Sorry for the rambling, I'm just frustrated. I've spent some time scouring the web, but if the answer is out there (even in an obvious place) I can either find not enough info. . . or an overload of nonhelpful  search responses.

Thanks,

jkh

John K. Humkey - jh_at_acm.org Received on Fri Aug 18 2006 - 05:27:32 CEST