OraFAQ Forum: Server Administration » Find top sql

Home » RDBMS Server » Server Administration » Find top sql - need advice

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Find top sql - need advice [message #255368]

Tue, 31 July 2007 09:52

arora_jeet
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 10.2.0.1.0

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

Thanks
Jeet

Report message to a moderator

Re: Find top sql - need advice [message #255425 is a reply to message #255368]

Tue, 31 July 2007 17:06

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Find top sql - need advice [message #255542 is a reply to message #255368]

Wed, 01 August 2007 02:58

animesh303
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.
or
Advisor Central > Sql Tuning Advisor

Observe for any potential issues and take corrective measure.

Regards
Animesh

Report message to a moderator

Previous Topic:	Loading dbf files into database
Next Topic:	installing 10g s/w

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 14:00:33 CDT 2024