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-
solution, 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 non-
helpful search responses.
Thanks,
jkh
John K. Humkey -
jh@acm.org 
