Re: Resource/cpu monitoring and control

Nathan Neulinger wrote:
>
> Does Oracle 7.3.3 provide any sort of resource monitoring and
> control facilities?
>
> I am coming from the Informix world where there no facilities for doing
> thing, and where you can't even kill some queries without taking the
> database server down. (If a query is holding a latch, and you kill it, kiss
> the server goodbye.)
>
> Basically, I want to be able to:
>
> 1. Watch to check for stupid queries (i.e. open ended joins of
> tables with thousands of rows, combined with a sort)
> 2. Be able to arbitrarily issue a query or command to kill that
> users connection.
> (snip)

Nathan,

It is possible to kill user sessions in Oracle. One way to do this (there may be others), is to query the V$SESSION table to get the SID (session ID) and SERIAL# of the offending session, then issue an ALTER SYSTEM KILL SESSION command in SQL*Plus, giving the SID and SERIAL# as parameters. If you only know the userid, you can query V$SESSION_CONNECT_INFO to connect the userid to the SID.

As far as finding sessions that have issued bad queries, I'm sure you could build some scripts to do that, but I've never done it. I have used Top Sessions, which is an applet in the Performance Pack add-on to Enterprise Manager, which works well for monitoring connections, seeing the SQL statements that are executing, and looking at their execution plans. You can also kill sessions within Top Sessions. The Performance Pack, of course, costs $$$$.

Lastly, Oracle has the ability to assign profiles to users which limit their maximum resource usage, either by session or by statement. Lokk in the documentation for the details.

Hope that helps.

Chuck Antonowicz
Oracle DBA Received on Tue Jan 13 1998 - 00:00:00 CST