SQL Performance (MOSC)

MOSC Banner

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.

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center