Why might my SQL query be taking radically different amounts of time to complete?

Question

When performing a sql query in our java code in the production environment it is taking radically different amounts of time to complete.

In our logs it took as little as 7 seconds and as much as 44 seconds.

The sql is something like this:

select * from Table where this = 'that'

It is consistently returning about 37,000 rows, but the row count it is returning will go up or down by a few each time.

Just looking for ideas on what to look for. Could it be the network is clogged up which is causing the results to transfer from the server slower? Or could the DB be performing other actions at the time which is slowing it down sometimes? Any ideas on what I should I look for would be appreciated.

What "row count" are you talking about exactly? If you say it always returns 37k rows... — The Impaler, Sep 08 '20 at 18:43
See [here](https://stackoverflow.com/a/34975420/4808122) some hints what you should post. Check your *fetch size* if it is not too low. — Marmite Bomber, Sep 08 '20 at 18:47

score 1 · Answer 1 · answered Sep 09 '20 at 03:50

There's no one-size-fits-all generic performance checklist, but a few queries can help you narrow down why the performance is changing.

First, find the SQL_ID of the query you're interested in. Queries may be transformed by the application, so you may have to make the search case or space insensitive.

select sql_id, sql_fulltext
from gv$sql
where lower(sql_fulltext) like '%select%from%table%';

Plug the SQL_ID into the active session history and look for "what changed". Every second, the active session history (ASH) takes a snapshot of what database resource every active session and SQL statement are waiting on. ASH does not store everything, but in practice a once-a-second sample is enough for almost all problems.

Finding what changed is a difficult and subjective task, but the columns and comments below will help guide you to find some of the most common reasons for variable performance.

select
    sql_id
    --Use these columns to separate different executions:
    ,inst_id, session_id, session_serial#   
    --Does the plan change? (If so, look into plan stability, histograms, etc.)
    ,sql_plan_hash_value
    --What event is the operation waiting on the most? This might help you narrow down if
    --the problem is related to CPU, I/O, or something weirder.
    ,nvl(event, 'CPU') event
    --Finally, look at everything else. There's a lot of data, use an IDE.
    ,gv$active_session_history.*
from gv$active_session_history
--Also run this query without the SQL_ID condition and look for other active sessions,
--to see if system activity is the cause.
where sql_id = 'f5yun5dynkskv'
--View most recent runs first. This table will not hold data for long, usually less than a day.
order by gv$active_session_history.sample_time, gv$active_session_history.sql_id desc;

Often times you must use the above data to show what the database isn't doing. The active session history only records things that are waiting on database processing. If you find that every execution of your SQL statement consistently uses X samples (seconds) of processing, then you know that the rest of the activity is happening in either the network or the application.

Why might my SQL query be taking radically different amounts of time to complete?

1 Answers1