Re: Oracle Server Tuning for shared environment

There are lots of Oracle Tuning tools out there in the Market including Oracle's OEM Tuning & Diagnostic Packs which can show you the Top Resource hoggers,Locks , Rollback Segment Usage etc etc.. Further you can issue adhoc queries against Oracle's views like V$sysstat, V$rollstat ,V$session_wait etc etc and make use of Oracle's utlbstat & utlestat Scripts provided to tune the SGA or Temp Space or Latch Contention etc

But it is ALWAYS better to run the Reporting or any other Batch JObs during off-peak hours as they tend to consume more CPU (because of Sorting operations),
Temp Space(Sorting again) and Rollback Segments(Huge amounts of Data).

I dont think you can allocate the Resources on a per Transaction basis or Report basis as U have mentioned, although certain operations like SEtting Specific Rollback Segment for a Transaction is possible.

You can also try setting different Resource_Limits using Profiles for different Users..

Oracle strongly advises to run all the Processes at the Same priority..

-Thiru
dloganca_at_my-deja.com wrote:

> Hi folks,
>
> What tools or capabilities exist to tune the performance/resource
> consumption of an Oracle Database server when it is being shared by
> many users/applications?
>
> For instance, imagine I have a transaction-oriented database (lots of
> browse-and-post of small records) which also has reports generated on
> it at will (show me all transactions matching criteria). Users are
> accessing an application server, which is performing the queries/ops to
> the (separate) database server.
>
> How is it possible to tune resources on the Oracle server such that the
> generation of an involved report does not significantly impact other
> transaction operations?
>
> Can I allocate "transaction rates" to certain databases/tables? To
> certain SQL operations? Can I assign CPU/memory resource to certain
> transaction types? Like, for Stored Procedure XYZ_Report, allocate 1%
> of resources if current usage is less than 50%, and allocate 10% if
> current usage is less than 25%...?
>
> Assuming this was Solaris, I can't use Sun's Resource Manager (its user
> oriented - the database has "one" user - the apps server).
>
> Thanks
> -- DL
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Tue Jun 29 1999 - 13:11:34 CDT