Home » RDBMS Server » Server Administration » Find top sql - need advice
icon5.gif  Find top sql - need advice [message #255368] Tue, 31 July 2007 09:52 Go to next message
Messages: 1
Registered: July 2007
Location: MI, US
Junior Member
Dear DBAs

Application team has asked me to provide TOP Sql statements in testing database which can help them to improve code. Developer gave OEM top sql example from his last job.

Currently I do not have OEM installed. I am on

I have AWR running here every hour and keep data for last seven days. So when I generate report what do I enter in start and end snapshot time

- do I ask them heavy activity time say 10 -11 am on one day

- or shall I generate start 8:00 am Monday and end snapshot 8:00 am Tuesday

- or shall I use v$sqlarea view to generate top 10 sql by executions, disk reads, buffer gets

The system is not slow they just want to be proactive.
Which way is the best to give developer an idea for their application -

top sql for an active hour or top sql for a day using AWR or
using v$sqlarea view information?

I realize that V$sqlarea will give me information since instance startup

Re: Find top sql - need advice [message #255425 is a reply to message #255368] Tue, 31 July 2007 17:06 Go to previous messageGo to next message
Messages: 25571
Registered: January 2009
Location: SoCal
Senior Member
I'll use an anology to respond to your question.
You have a basket with many different colored balls in it.
The basket is the SGA. The colored balls are the SQL statements.
Which color is the TOP ball?
After you answer my question (immediately above), then perhaps I'll be able to answer your question.

I suggest somebody get a copy of "Optimizing Oracle Performance" [ISBN 0-596-00527-x] & learn how methodically make an application perform better;
as opposed to the Ready, Fire, Aim school of hoping to get lucky.
Re: Find top sql - need advice [message #255542 is a reply to message #255368] Wed, 01 August 2007 02:58 Go to previous message
Messages: 12
Registered: June 2006
Junior Member
Hi Jeet

Since you don't have OEM, I believe there is no use gathering AWR over the heavy workload period.

My suggestion, have OEM installed. Then do the following:

1. Start dbconsole : $ emctl start dbconsole

2. Connect to the database using system and force creating of snapshot : sql> exec dbms_workload_repository.create_snapshot;

3. Ask developer to run their queries.

4. Connect to server using SYSTEM by dbconsole

5. Navigate : Advisor Central > ADDM (Automatic Database Diagnostic Monitor) Report.
Advisor Central > Sql Tuning Advisor

Observe for any potential issues and take corrective measure.

Previous Topic: What is the Server Parameter File
Next Topic: Controlfile expansion
Goto Forum:

Current Time: Sat Aug 19 13:56:09 CDT 2017

Total time taken to generate the page: 0.06925 seconds