Re: how to find the most resource intensive sql?

From: zhu chao <chao_ping_at_vip.163.com>
Date: Thu, 11 Mar 2004 21:47:47 +0800
Message-ID: <019501c4076f$72cba710$d5195fd3@chaos>

Why so many complicated tools?
Isn't statspack enough for you?

Regards
Zhu Chao

• Original Message ----- From: "Srinivasan Vasan" <Vasan.Srinivasan_at_churchill.com> To: <oracle-l_at_freelists.org> Cc: "Srinivasan Vasan" <Vasan.Srinivasan_at_churchill.com> Sent: Thursday, March 11, 2004 9:22 PM Subject: RE: how to find the most resource intensive sql?

> You are asking 2 questions - one in the subject and one in your user's
query
> - hope the following helps (use a variation on the first one to suit your
> needs):
>
> Answer to 1:
> select a.executions, a.disk_reads, a.disk_reads/a.executions dre,
> a.buffer_gets,
> a.buffer_gets/a.executions bge, b.username,
> a.first_load_time,a.sql_text
> from v$sql a, all_users b
> where a.executions > 0
> and a.parsing_user_id = b.user_id
> order by 1 desc;
>
> Answer to 2:
>

http://searchoracle.techtarget.com/tip/0,289483,sid41_gci935720,00.html?trac
> k=NL-93
>
> Cheers,
>
> Vasan (x5707)
> Mailpoint 28
> ============================================
> Vasan Srinivasan * 020 8313 5707
> Infrastructure Service Manager * 020 8313 5646
> Oracle Technologies
> Churchill Insurance, IT Department
> Purple Floor, Phase 1, Churchill Court
> 1 Westmoreland Road,
> Bromley, Kent, BR1 1DP.
> * Vasan.Srinivasan_at_churchill.com
> Mobile * 07710 154 987
> http://oratech
> ============================================
> Views Presented here are not necessarily the views
> of my Employer
> ============================================
>
>
> -----Original Message-----
> From: ryan.gaffuri_at_cox.net [mailto:ryan.gaffuri_at_cox.net]
> Sent: 11 March 2004 13:12
> To: oracle-l_at_freelists.org
> Subject: how to find the most resource intensive sql?
>
> assuming you can't use statspack? Is there a query for this? I was asked
the
> following and I'm not sure how to answer it:
>
> 'A user comes to you and says they get to a point in the application and
hit
> a button. It's slow. You do not have any access to the application. How do
> you find the problem query in the database'
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>

---

>
>
> This email and any attached to it are confidential and intended only for
the
> individual or entity to which it is addressed. If you are not the
intended
> recipient, please let us know by telephoning or emailing the sender. You
> should also delete the email and any attachment from your systems and
should
> not copy the email or any attachment or disclose their content to any
other
> person or entity. The views expressed here are not necessarily those of
> Churchill Insurance Group plc or its affiliates or subsidiaries. Thank
you.
>
> Churchill Insurance Group plc. Company Registration Number - 2280426.
> England.
>
> Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1
> 1DP.
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Mar 11 2004 - 07:50:30 CST