Why an Uncommitted INSERT Can Turn a Simple Oracle SELECT Into a Minutes‑Long Query

Problem Scenario

A developer reported that a seemingly simple SQL query on an Oracle test server took several minutes to return no rows. The issue stemmed from a colleague who created a table, inserted a large amount of data (over 1 GB), but never committed the transaction.

Reproducing the Issue

SQL> create table test_uncommit as select * from dba_objects where 1=0;

SQL> declare
  rowIndex number;
begin
  for rowIndex in 1..70 loop
    insert into test_uncommit select * from dba_objects;
  end loop;
end;
/

The table now occupies 852 data blocks despite containing zero rows.

Analyzing the Execution Plan

Running a COUNT query with SET TIMING ON and SET AUTOTRACE ON showed an execution plan with a huge redo size (≈3.8 MB) and many logical reads, even though the table is empty.

Typical statistics collection did not change the plan, indicating the problem was not stale statistics.

Checking Table Space Usage

SQL> SELECT TABLE_NAME, NUM_ROWS, BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TEST_UNCOMMIT';

Result: NUM_ROWS = 0, BLOCKS = 852.

Using the show_space script confirmed that the table consumed 852 free blocks out of 896 total, confirming that the space was allocated but not populated with committed rows.

Identifying the Lock

A lock query revealed a session (SID = 883) holding a row‑level exclusive lock and a transaction lock on the table, confirming that the DML operation had not been committed.

SET linesize 190
COL osuser format a15
COL username format a20 wrap
COL object_name format a20 wrap
COL terminal format a25 wrap
COL req_mode format a20
SELECT B.SID, C.USERNAME, C.OSUSER, C.TERMINAL,
       DECODE(B.ID2,0,A.OBJECT_NAME,'TRANS-'||TO_CHAR(B.ID1)) OBJECT_NAME,
       B.TYPE,
       DECODE(B.LMODE,0,'WAITING',1,'NULL',2,'Row‑S(SS)',3,'ROW‑X(SX)',
              4,'SHARE',5,'S/ROW‑X(SSX)',6,'EXCLUSIVE','OTHER') "LOCK MODE",
       DECODE(B.REQUEST,0,'',1,'NULL',2,'Row‑S(SS)',3,'ROW‑X(SX)',4,'SHARE',
              5,'S/ROW‑X(SSX)',6,'EXCLUSIVE','OTHER') "REQ_MODE"
FROM   DBA_OBJECTS A, V$LOCK B, V$SESSION C
WHERE  A.OBJECT_ID(+) = B.ID1
AND    B.SID = C.SID
AND    C.USERNAME IS NOT NULL
ORDER BY B.SID, B.ID2;

Effect of Committing the Transaction

After the session committed, the same query’s execution plan showed redo size = 0 , confirming that redo logs are generated only for uncommitted DML.

TKPROF traces before and after the commit revealed a dramatic reduction in both physical and consistent reads (the latter dropping by more than threefold), because Oracle’s consistency reads had to construct CR blocks for the uncommitted changes.

Understanding Consistency Reads

Oracle uses consistency reads to present a stable view of data. When a query’s SCN differs from the block’s SCN, Oracle builds a CR (consistent read) block by retrieving the pre‑image from undo segments. If the undo data is still in the buffer cache, the operation is a logical read; otherwise, it incurs a physical read from disk.

This mechanism explains the high logical‑read count observed before the commit: the database had to reconstruct many CR blocks for rows that existed only in the uncommitted transaction.

Resolution

The fix is straightforward: identify the session holding the uncommitted DML, have the user issue a COMMIT, or terminate the session. Once committed, the table’s space is released, redo size disappears, and query performance returns to normal.