Resource/cpu monitoring and control

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.

I have seen some notes that indicate that queries can be terminated by altering the V$ tables, but I don't have any real details on the repercussions of this or how exactly it is done. I haven't seen this as related to cpu utilization, though I have seen it related to idle time disconnecting of sessions.

How does this work if they have locks? Will Oracle properly release them when that users connection is terminated?

Can one user even affect the oracle server by doing a stupid query such as this?

Is there any facility for setting CPU/DB server priorities for various connections?

FYI - I'm still just beginning with Oracle, we don't actually have a production server in place yet, but we're wanting to get an idea of what sorts of facilities we can put in place before we start migrating from Informix-5.

Any pointers or tips will be appreciated.

• Nathan

  ---

Nathan Neulinger                       EMail:  nneul_at_umr.edu
University of Missouri - Rolla         Phone: (573) 341-4841
Computing Services                       Fax: (573) 341-4216