Re: Tunning & DBA SQLs

In article <4lh104$m80_at_nntp1.best.com>, posborn_at_menlosoftware.com (Paul Osborn) writes:

>Klaus Schnurr <kschnurr_at_primenet.com> wrote:
>
>We are using Oracle Server 7.1 (running under Solaris 2.4) in a
>semiconductor manufacturing environment. Users are mainly using the
>database for data analysis purposes which requires intensive data
>queries. However, deploying the database and the front-end tools within
>a client-server environment revealed very soon the limitations of the
>systems. One user can bring system down to its knees by running an
>extremely complex query which consumes all the system resources. The
>users are connecting via the Sequelink middleware product from Intersolv
>to the >database. Now, I have the following questions:
>
>1. Is there any way, within Oracle or UNIX, to restrict the available
>system and oracle resources which are available for a requested
>instance?
>

Yes, in Oracle. If the init.ora parameter RESOURCE_LIMIT is TRUE (or via ALTER SYSTEM command) you can assign a "resource" profile to a user. Actually, all users will must use a profile, but the DEFAULT limits are UNLIMITED.  
>2. Can Oracle restrict the number of rows a query can return to the user
>(i.e. trapping "run-away" queries)
>

Sort of. The resource profile can limit the number of logical reads by call or session.

>3. Does anybody know of Oracle SQL statements which would allow to
>monitor the database performance?

The are numerous books that list such statements. The first one I think of is "Oracle DBA" published, I think, by Oracle Press. If you want to buy a package, the first one that comes to mind is "SQLWatch" - not an endorsement.

Steve Stutheit Received on Tue Apr 23 1996 - 00:00:00 CEST