I got this in the alert log of a database:
Wed Aug 15 13:21:29 CEST 2018
ORA-01555 caused by SQL statement below (SQL ID: 4dhx1332z74nf, Query Duration=94491 sec, SCN: 0x000c.cdffd21c):
Wed Aug 15 13:21:29 CEST 2018
SELECT /*+ FIRST_ROWS(1) */A.ITEMGROUPID,A.ITEMID,A.ITEMNAME,A.ITEMTYPE,A.PURCHMODEL,A.HEIGHT,A.WIDTH,A.SALESMODEL,A.COSTGROUPID,A.REQGROUPID,A.PRIMARYVENDORID,A.NETWEIGHT,A.DEPTH,A.UNITVOLUME,A.BOMUNITID,A.DENSITY,A.DEL_SCRAPTYPEID,A.DIMENSION,A.DIMENSION2_,A.DIMENSION3_,A.DIMENSION4_,A.DIMENSION5_,A.DIMENSION6_,A.DIMENSION7_,A.DIMENSION8_,A.DIMENSION9_,A.COSTMODEL,A.USEALTITEMID,A.ALTITEMID,A.INTRACODE,A.BOMMANUALCONSUMP,A.BOMMANUALRECEIPT,A.STOPEXPLODE,A.DEL_COVPERINVENTLOCATION,A.PHANTOM,A.INTRAUNIT,A.BOMLEVEL,A.BATCHNUMGROUPID,A.AUTOREPORTFINISHED,A.ORIGCOUNTRYID,A.STATISTICSFACTOR,A.ALTCONFIGID,A.STANDARDCONFIGID,A.DEL_CONFIGACTIVE,A.PRODPOOLID,A.PROPERTYID,A.ABCTIEUP,A.ABCREVENUE,A.ABCVALUE,A.ABCCONTRIBUTIONMARGIN,A.COMMISSIONGROUPID,A.DEL_BARCODE,A.DEL_BARCODETYPE,A.CONFIGURABLE,A.SALESPERCENTMARKUP,A.SALESCONTRIBUTIONRATIO,A.SALESPRICEMODELBASIC,A.MINAVERAGESETTLE,A.NAMEALIAS,A.PRODGROUPID,A.PROJCATEGORYID,A.GROSSDEPTH,A.GROSSWIDTH,A.GROSSHEIGHT,A.STANDARDPALLETQUANTITY,A.QTYPERLAYER,A.SORTCODE,A.CON ...
Since the "Query Duration" is reported to be over 26 hours, I would expect this query to appear in the "SQL Ordered by Elapsed" section of Statspack for those 26 hours before the ORA-1555 occurred - is that right ?
I take "Elapsed" to mean "Wall clock time spent doing something (CPU/IO) or queued/locked wanting to do something (CPU/IO)" - is that right ?
Maybe ... or maybe not. Firstly, you can check for the existence of the sql being captured by statspack by running:
select * from stats$sql_summary where sql_id = '4dhx1332z74nf';
But query duration is not the same as *run* time. For example, if I do:
9am: open cursor for select * from emp;
and walk away from the computer for an hour
3pm: fetch a row
I'll probably get an ora-1555, and alert log will show "query duration = 6 hours" because 6 hours have passed since my query *started*. In that case, its unlikely to be in a statspack report because it has not done much work