Skip to Main Content
  • Questions
  • query execution time in first attempt


Question and Answer

Connor McDonald

Thanks for the question, asit.

Asked: August 18, 2020 - 3:38 pm UTC

Answered by: Connor McDonald - Last updated: August 28, 2020 - 1:08 am UTC

Category: SQL - Version: 12c

Viewed 100+ times

You Asked

Hi Tom,
I am using SQL Developer and a particular user/schema for my Oracle Development Work in my project.
While optimizing a query - it completes execution in ~60 sec in the 1st attempt where as it takes ~10 sec in subsequent run.Lets, say I am adding some filter conditions and trigger it again, it will still use the old share pool,library cache and hence completing n less than 60 sec. My question is - How can I check for a query how much time it takes in its first attempt ?

Can I do it by - alter system flush buffer_cache; ? But, I don't have privilege for this.
Is there any other way where I can check the execution time for a query in its first attempt.

Many Thanks,

and we said...

It is unlikely to be shared pool but more likely the buffer cache.

Typically I would not be overly concerned with the difference, because in real production use you would expect the response time to be somewhere between "no" buffer cache and "full" buffer cache (ie, 10 and 60 seconds).

You can monitor the "consistent gets" statistic out of autotrace to decide if a *change* to a query yields and improvement or a regression.

If you still need to see how long that query ran for, you could always check out v$active_session_history. It will get a row for each second your query was active, so something

select sql_exec_id, count(*)
from v$active_session_history
where session_id = ...
and session_serial# = ...
and sql_id = ...
group by sql_exec_id

should give you an approximate breakdown

and you rated our response

  (2 ratings)


August 23, 2020 - 8:38 pm UTC

Reviewer: A reader

Many thanks for your valuable reply
Connor McDonald


August 27, 2020 - 5:26 am UTC

glad we could help

Real time sqlmonitor

August 27, 2020 - 5:34 am UTC

Reviewer: Rajeshwaran, Jeyabal

since this is on 12c, anything more than 6 sec of execution, should be available on "sqlmonitor" - shouldn't that be an easy go than looking into v$active_session_history ? lots of goodness comes up with sqlmonitor in place.
Connor McDonald


August 28, 2020 - 1:08 am UTC

I was running under the assumption that the poster is coming along well after the fact, but sql monitoring is useful yes.

More to Explore


Get all the information about database performance in the Database Performance guide.