Excessive "table scan rows gotten" and "consistent gets" session statistics for empty table
Version 19.21, multitenant.
In two PDBs, same table/index structure and columns.
create table tab1 (col1 number, col2 number, col3 number);
Both tables are empty, DBA_SEGMENTS indicates that the segment size for both table and index is 6 blocks.
Running this query: select * from tab1
On database 1, takes a couple seconds. On database 2, takes at least 20 seconds. Same for using EXPLAIN PLAN. In both cases it's doing a full table scan (as expected).
No other outstanding sessions, UNDO, or open transactions. Started a new session on each DB. The comparison in session statistics seem to imply that there are dirty blocks in the buffer cache, and that the slow session had to create a CR block (just 1?) and do a lot more work. But that doesn't make sense given that there are no outstanding sessions or transactions. How do I trace down the culprit here (I could just restart the database but that doesn't help solving the root cause)? What table is getting scanned over 100M times? Thanks.