Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Top N queries

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


  1. Take the output of 'top 1000' in a text file (say /tmp/spid_cpu.txt), printing 2 columns - UnixPid and CPU_Used.
  2. 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

> 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US