From: jh@acm.org   
      
   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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)