ORA-01555 encountered when fetch stage is extremely prolonged

We encountered the well-known ORA-1555 error recently :

Fri Jun 23 21:07:02 2017
ORA-01555 caused by SQL statement below (SQL ID: a520vskru5tmy, Query Duration=3304 sec, SCN: 0x010a.b517cc1a):
SELECT BOND_REPO_DEAL_F.BOND_REPO_DEAL_ID ...

Query Duration is 3304 seconds, wow!

But when I checked the AWR report, it states the Elapsed time for the SQL is only 19.29 seconds.awr_snapshot

If you find it weird too, you may have the same misconceptions with me. In fact, it all comes down to the basic concepts of different SQL execution stages.

When executed manually in SQL*Plus with timing option on, it shows that “Query Duration” actually means “Sum of Execution and Fetch Duration“, which could be as many as 12 minutes and ELAPSED_TIME in v$SQL is only elapsed time of “Execution Stage“, which is 19 seconds in our case.

You can find relevant explanations in the reference.

Reference:
https://docs.oracle.com/cd/A57673_01/DOC/server/doc/A48506/sqlconce.htm

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s