Skip to Main Content
  • Questions
  • SELECT query against empty table (zero rows), but 270K I/O requests and 43 GB I/O bytes

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Patrick.

Asked: July 22, 2022 - 3:59 pm UTC

Last updated: July 22, 2022 - 5:02 pm UTC

Version: 19c

Viewed 100+ times

You Asked

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

and Chris said...

Is it possible that the table did have lots of data in the past which were deleted? Or rows loaded with the /*+ append */ hint which were then removed?

A full table scan reads all the data up to the high water mark. Even if there are no rows in the table!

Here's a quick demo. Notice that - despite the table having no rows - the query still does 136,000 gets:

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


Getting the plan using the method above (alter session ... through to the dbms_xplan query) will validate the SQL Monitor stats.

A quick check of the size of the table segments will help you see if this could be the problem:

select bytes from user_segments
where  segment_name = 'T';

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


If so, moving or shrinking the table should help.

If this isn't the issue, speak with support.

Rating

  (2 ratings)

Comments

Thanks for the quick answer

Patrick, July 22, 2022 - 4:50 pm UTC

You learn something every day.. I did not know that a Full Scan read up to the high water mark regardless of the number of records. I would expect the SQL Optimizer to check the table stats or PK index to see that there were no rows in the table, and not bother doing a scan on an empty table.

It is possible that there were rows in the table once but the associated feature has since been disabled at the application level. I will check with our DBAs about shrinking the table.
Chris Saxon
July 22, 2022 - 5:02 pm UTC

Stats are always slightly out-of-date. If you gather stats on an empty table, then insert a row, optimizing as if the table was empty could lead to catastrophically wrong results!

The only way to know for sure what's in the table is to run the query - which in this case is a full table scan.

An index on

attempt


might help here (if the COTS provider allows you to create these). As the optimizer expects few rows, searching this index is a good strategy.

To Patrick

Rajeshwaran, Jeyabal, July 24, 2022 - 2:38 pm UTC

Another option would be make use of the Show_space method to look at the free space across the blocks in the segment and based on that we can think about the options for re-organizing the segments.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5350053031470

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.