How to find a sql id which is taking longer than a normal time ?

First we need to know what is normal. Normal is how much time it usually takes. Someone complained that its taking forever and now out of all the sqls in tuning set few are bad.

We can do this in OEM as well by dumping all those sqls in tuning set and sort by longest taken times. But again not always its efficient or effective and reliable so I had to come up with this Query.

This has my defined where clause which you can change based on your need and search of that query in tuning set running longer.

High level goals are:
1. Find the sql id which is running longer.
2. Tune that one sql id using Tuning Adviser.
How to performance tune a sql statement in OEM using tuning adviser.

Once in order to do the second part we need to do the first action item.

This is sample output from the query in which I lowered the threshold so it would show up in my sample.

In this case all is fine (longest query is 10 seconds)

select * from (

WITH

slow_sql

AS

(SELECT sql_id

FROM GV$SQL

WHERE sql_text LIKE 'your select statement'

AND parsing_schema_name = 'your schema owner user'

AND inst_id = 4

and elapsed_time > 10000000 -- only queries over 20 seconds to avoid divide by zero

and executions > 0 -- divide by zero

AND (elapsed_time / executions) / 1000000 > 10)

SELECT GS.sql_id,

plan_hash_value,

executions,

ROUND (elapsed_time / 1000000) seconds,

ROUND ((elapsed_time / 1000000) / executions, 2) sec_per_execution,

sql_profile

-- , sql_text

FROM GV$SQL GS, slow_sql

WHERE gs.sql_id = slow_sql.sql_id

 AND gs.inst_id = 4

ORDER BY sql_id, sec_per_execution DESC

)

Below output is an example that these ids took  longer than 20 seconds and we can go to step to 2 and performance tune. Also we can see if a profile exists.