Skip to Main Content
  • Questions

  • query execution time in first attempt

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, asit.

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

Last updated: August 28, 2020 - 1:08 am UTC

Version: 12c

Viewed 1000+ 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,
Asit

and Connor 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

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, August 23, 2020 - 8:38 pm UTC

Many thanks for your valuable reply
Connor McDonald
August 27, 2020 - 5:26 am UTC

glad we could help

Real time sqlmonitor

Rajeshwaran, Jeyabal, August 27, 2020 - 5:34 am UTC

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

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