Re: Measuring I/O at the session level

From: Max <max_stout_at_hotmail.com>
Date: Thu, 07 Sep 2000 13:35:58 -0400
Message-ID: <39B7D1FD.2A346D85_at_hotmail.com>

I found this on Oramag's site. There are may more. Let me know if that's what you need.

/*******************************************************************/
/* FILE         : j_cpusess.sql                                    */
/* PURPOSE      : To display the sessions and their sqltext for cpu*/
/*                intensive processes.                             */
/* AUTHOR       : Jayant R Kulkarni (WIPRO ORACLE SUPPORT).        */
/* DATE         : 14 Jan 1999                                      */
/*******************************************************************/

cl scr
prompt Displaying The CPU Intensive Oracle Processes ... prompt
prompt
[Quoted] !ps -eaf -o pcpu,pid,user,tty,comm | grep ora | grep -v \/sh | grep -v ora_ | so
rt -r | head -20
column username format a10
column terminal format a9
column sql_text format a30
prompt
prompt
prompt
prompt Enter The UNIX PID :
accept PID

SELECT a.username, a.terminal, a.program, b.sql_text FROM v$session a, v$sqlarea b, v$process c WHERE ( c.spid = &PID OR a.process = &PID )

AND     a.paddr = c.addr
AND     a.sql_address = b.address

/

Gerard Demers wrote:

> How can you determine the Oracle session causing heavy I/O to a
> particular data base file? If this is not possible, how do you
> determine aggregate I/O activity associated with a particular Oracle
> session? We can find information about total activity to individual
> data base files but not what session is causing it.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
  Received on Thu Sep 07 2000 - 19:35:58 CEST