Re: how to tell what's running

From: John K. Hinsdale <hin_at_alma.com>
Date: 2 Jan 2007 10:37:56 -0800
Message-ID: <1167763076.632319.29160@h40g2000cwb.googlegroups.com>

Usenet participant jobs inquired:
> I have toad and plsql clients installed. but I do not have dbo access.
> I can see some unix oracle processes that running when I do a top from
> the OS. Is there anything I can do to tie these processes back to
> oracle?

Below is what I use; sorts by queries which have been running the longest. You don't need DBA level privs, but you do need SELECT permission on the system views GV_$SQLAREA and GV_$SESSION. You can try pasting it into T.O.A.D. and see what happens.

The PROCESS column below should tie to the Unix PID in your "top" output from Solaris. This process is the Oracle "shadow" process specific to the session, and consumes system resources for many intensive operations like sorts, hash-joins, etc., which is why these end up at the top of your "top". The "command" seen in "top" and "ps" is typically "oracleFOO" where FOO is the Oracle instance name.

SELECT  SS.logon_time,     SS.sid,
        SS.serial#,        SS.username,
        SS.schemaname,     SS.status,
        SS.machine,        SS.process,
        SS.osuser,         SS.program,
        SS.module,
        SA.sql_text,       SA.users_executing,
        SA.executions,     SA.first_load_time,
        SA.disk_reads  ,   SA.buffer_gets,
        SA.rows_processed, SA.command_type,
        SA.cpu_time,       SA.elapsed_time
FROM sys.gv_$sqlarea SA, sys.gv_$session SS
WHERE SS.sql_address = SA.address

  AND SA.users_executing > 0
  AND SS.status = 'ACTIVE'
ORDER BY SA.elapsed_time DESC

We use this all the time where I work to kill off runaway queries made by developers who forget join conditions, etc. We have another version that actually outputs a column with the complete kill-session command. ;)

Cheers,
John Hinsdale Received on Tue Jan 02 2007 - 12:37:56 CST