Skip to Main Content
  • Questions
  • How to find long running queries in a database

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Athira.

Asked: February 14, 2017 - 12:42 pm UTC

Last updated: February 14, 2017 - 3:10 pm UTC

Version: NA

Viewed 50K+ times! This question is

You Asked

Hi,

I'm trying to get the long running queries for my database. I need the start and end time of those queries also.

These long running query is causing issues in releases. Please help me to find out the sql text and start & end time of the queries.

Thanks in Advance.

and Chris said...

There are a number of ways to do this. For statements still in the cache, you can find aggregated information from the v$sql* views.

For example, here's a query that takes ~27s:

SQL> with /* slow */ rws as (
  2    select rownum x from dual connect by level <= 1000
  3  )
  4    select count(*) from rws, rws, rws;

  COUNT(*)
----------
1000000000

SQL>
SQL> select sql_id, first_load_time, last_load_time, elapsed_time, cpu_time
  2  from   v$sql
  3  where  sql_text like 'with /* slow */ rws as (%';

SQL_ID        FIRST_LOAD_TIME     LAST_LOAD_TIME      ELAPSED_TIME   CPU_TIME
------------- ------------------- ------------------- ------------ ----------
f3mdy8usdm8j4 2017-02-14/07:01:31 2017-02-14/07:01:31     27976167   27914000


The times are in microsecond and are cumulative. So if you have executed the same statement multiple times, you can divide these by the executions to get an average:

SQL> with /* slow */ rws as (
  2    select rownum x from dual connect by level <= 1000
  3  )
  4    select count(*) from rws, rws, rws;

  COUNT(*)
----------
1000000000

SQL>
SQL> select sql_id, first_load_time, last_load_time, elapsed_time, elapsed_time/executions avg_elapsed, cpu_time/executions avg_cpu
  2  from   v$sql
  3  where  sql_text like 'with /* slow */ rws as (%';

SQL_ID        FIRST_LOAD_TIME     LAST_LOAD_TIME      ELAPSED_TIME AVG_ELAPSED    AVG_CPU
------------- ------------------- ------------------- ------------ ----------- ----------
f3mdy8usdm8j4 2017-02-14/07:01:31 2017-02-14/07:01:31     55617239  27808619.5   27775000



If you're licensed for the Diagnostics and Tuning packs, you can do better than this though. The SQL monitor records each instance of queries longer than 5 seconds:

SQL> select to_char(sql_exec_start, 'dd-Mon hh24:mi'), elapsed_time, cpu_time
  2  from   v$sql_monitor
  3  where  sql_id = 'f3mdy8usdm8j4';

TO_CHAR(SQL_ ELAPSED_TIME   CPU_TIME
------------ ------------ ----------
14-Feb 07:01     27963910   27904000
14-Feb 07:02     27635004   27633000


Oracle keeps these details for at least a minute, possibly longer. It depends upon how many statements it's monitoring!

And you can also get historic details from AWR. You can use this to find the slowest statements in a given time period:

select sql_id, elapsed_time_delta/executions_delta avg_elapsed
from   sys.dba_hist_sqlstat
where  snap_id = :snap;

Rating

  (2 ratings)

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

Comments

DPA

Roberto, February 15, 2017 - 4:32 pm UTC

Or....if you have some money in your budget consider using SolarWinds Database Performance Analyzer (DPA).

We use it and could not live without it.

Good Query to check elapse time

daniel nangah, March 23, 2020 - 3:18 pm UTC

Thanks for the query was very helpful

More to Explore

Performance

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