SQL ordered by Elapsed Time in AWR report

oracle awr
Nidheesh picture Nidheesh · Jun 10, 2014 · Viewed 13.7k times · Source

I was trying to analyze the AWR report generated for a particular process with a duration of one hour. I am trying to find out which query is taking much time to while running the process.

When I have gone through the report, I can see SQL ordered by Gets,SQL ordered by CPU Time,SQL ordered by Executions,SQL ordered by Parse Calls, SQL ordered by Sharable Memory,SQL ordered by Elapsed Time etc.

I can see the SQL Text from the table SQL ordered by Elapsed Time.

My question: Is this the right way to identify the expensive query ? Please advise in this regard.

Elapsed Time (s)    SQL Text
19,477.05            select abc.....
7,644.04             select def...

Answer

Nidheesh picture Nidheesh · Jun 11, 2014

SQL Ordered by Elapsed Time , includes SQL statements that took significant execution time during processing.We have to look at Executions,Elapsed time per Exec (s) etc. along with Elapsed time to analyze.

For example,a query has low Executions and high Elapsed time per Exec (s) and this query could be a candidate for troubleshooting or optimizations.

The best reference I found so far: http://www.dbas-oracle.com/2013/05/10-steps-to-analyze-awr-report-in-oracle.html

Related questions

DBA_HIST_ACTIVE_SESS_HISTORY get sql by user and object schema

Hi I am learning ASH and AWR tables but any ideas as to how i can get list of sql, objects and schema owner accessed by a give user in last 30 days ? Basically get all SQL text, and then search …

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More