| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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 5646Oracle Technologies
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'
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.
 |
 |