Re: Top N queries

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: Mon, 6 Mar 2006 21:21:45 -0800 (PST)
Message-ID: <20060307052145.72823.qmail@web52810.mail.yahoo.com>

Traditional approach, Statspack, OEM, AWR etc. are already out there, but if you want to drill-down starting from Unix (Solaris in our case) coming into oracle, you could do this (we use this approach for some alerts for CPU) :

Steps

Take the output of 'top 1000' in a text file (say /tmp/spid_cpu.txt), printing 2 columns - UnixPid and CPU_Used.
Access the text file as an external table (say spid_cpu_ext) and join to v$process (UnixPid joins to spid) and v$session, to report the top 'N' CPU consuming Users (order by Cpu_Used desc).

You could extend this idea and customize the final output to your needs.

HTH,
Deepak

RACHID TAMBA <rachidtamba_at_hotmail.com> wrote:

> Hi All :
>
> Any one has an idea how to write a script ( on UNIX
> ) to find out the top N
> queries which are consuming a lot of resources ?
>
> Thank you.
>

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Mar 06 2006 - 23:21:45 CST