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.
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.