I'm seeing strange behaviour in a client database that was recently upgraded from Oracle 12c to Oracle 19c. The COTS application periodically executes a query like this:

SELECT * FROM ( SELECT * FROM RenditionQueue WHERE attempt > 0 ORDER BY QueueDate) WHERE ROWNUM <= 5000;

The table in question, RenditionQueue, always contains zero rows.

Monitoring the execution of the SQL statement in the Real Time SQL Monitor feature of SQL Developer, I see that every execution does a TABLE ACCESS (FULL) scan, with Plan Rows = 1 and Actual Rows = 0, as expected. However, the I/O requests count is > 250 K, Buffer Gets > 7.4 M, and the I/O bytes count is 45 GB. The query takes 4.4 minutes of database time according to SQL Developer. Any idea why a query against an empty table would show such execution statistics? The table is only used by a periodic monitoring task for an application feature which is not being used by the client, so there is no contention for access to the table.

It has occurred to me, however that the Real Time SQL Monitor may be reporting incorrect statistics. Despite the values quoted above, the reported execution Start Time and End Time are only six seconds apart, and there is no degree of parallelism involved.

Thanks for any insight.

Patrick

create table t as 
  select level c1, date'2000-01-01' + level c2,
         rpad ( 'stuff', 500, 'f' ) c3
  from   dual
  connect by level <= 1000000;

delete t;
commit;

alter session set statistics_level = all;
set serveroutput off

select count (*) from t;

  COUNT(*)
----------
         0

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.84 |     136K|      4 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.84 |     136K|      4 |
|   2 |   TABLE ACCESS FULL| T    |      1 |   1244K|      0 |00:00:00.84 |     136K|      4 |
----------------------------------------------------------------------------------------------

select bytes from user_segments
where  segment_name = 'T';

     BYTES
----------
 634388480