How Oracle Database Processes DML Internally

Result set rows can be fetched either a row at a time or in groups.

In the fetch stage, the database selects rows and, if requested by the query, orders the rows. Each successive fetch retrieves another row of the result until the last row has been fetched.

In general, the database cannot determine for certain the number of rows to be retrieved by a query until the last row is fetched. Oracle Database retrieves the data in response to fetch calls, so that the more rows the database reads, the more work it performs. For some queries the database returns the first row as quickly as possible, whereas for others it creates the entire result set before returning the first row.

DML statements that must change data use read consistency to retrieve only the data that matched the search criteria when the modification began.

Afterward, these statements retrieve the data blocks as they exist in their current state and make the required modifications. The database must perform other actions related to the modification of the data such as generating redo and undo data.

DML Operation:

SQL> CREATE TABLE SAMAD.TBLDML_TEST AS SELECT * FROM DBA_OBJECTS WHERE ROWNUM <= 5000;

Table created.

SQL> SELECT COUNT(*) FROM SAMAD.TBLDML_TEST;

  COUNT(*)
----------
      5000

SQL> SELECT EXTENT_ID, BLOCK_ID, BLOCKS FROM DBA_EXTENTS
WHERE SEGMENT_NAME = 'TBLDML_TEST';

 EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         0        136          8
         1        144          8
         2        152          8
         3        160          8
         4        168          8
         5        176          8
         6        184          8
         7        192          8
         8        200          8
         9        208          8
        10        216          8
        11        224          8

12 rows selected.

SQL> SELECT DATA_OBJECT_ID, OBJECT_ID FROM DBA_OBJECTS
WHERE OBJECT_NAME = 'TBLDML_TEST';

DATA_OBJECT_ID  OBJECT_ID
-------------- ----------
         73020      73020

SQL> SELECT MIN(OBJECT_ID), MAX(OBJECT_ID) FROM SAMAD.TBLDML_TEST;

MIN(OBJECT_ID) MAX(OBJECT_ID)
-------------- --------------
             2           5006

SQL> SELECT OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
		DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#  
		FROM SAMAD.TBLDML_TEST  WHERE OBJECT_ID IN (2,5000);

 OBJECT_ID      FILE#     BLOCK#
---------- ---------- ----------
         2         12        139
      5000         12        229

Insert a Record into a Table:

SQL> @obj_buffer.sql
Enter value for objid: 73020
old  21: WHERE OBJD= &OBJID
new  21: WHERE OBJD= 73020

     FILE#     BLOCK# CLASS TYPE         STATUS     LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
        12        136 1ST LEVEL BMB      xcur       00
        12        137 2ND LEVEL BMB      xcur       00
        12        138 SEGMENT HEADER     xcur       00
        12        139 DATA BLOCK         xcur       00
        12        140 DATA BLOCK         xcur       00
        12        141 DATA BLOCK         xcur       00
        12        142 DATA BLOCK         xcur       00
        12        143 DATA BLOCK         xcur       00
        12        144 DATA BLOCK         xcur       00
        12        145 DATA BLOCK         xcur       00
        12        146 DATA BLOCK         xcur       00

... ... ... 

        12        220 DATA BLOCK         xcur       00
        12        221 DATA BLOCK         xcur       00
        12        222 DATA BLOCK         xcur       00
        12        223 DATA BLOCK         xcur       00
        12        224 DATA BLOCK         xcur       00
        12        225 DATA BLOCK         xcur       00
        12        226 DATA BLOCK         xcur       00
        12        227 DATA BLOCK         xcur       00
        12        228 DATA BLOCK         xcur       00
        12        229 DATA BLOCK         xcur       00

94 rows selected.
SQL> conn / as sysdba
Connected.
SQL> startup force
ORACLE instance started.

Total System Global Area 1560278096 bytes
Fixed Size                  9135184 bytes
Variable Size            1006632960 bytes
Database Buffers          536870912 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MYPDB                          MOUNTED
SQL> alter pluggable database MYPDB open;

Pluggable database altered.

SQL> alter session set container = MYPDB;

Session altered.

SQL> @obj_buffer.sql
Enter value for objid: 73020
old  21: WHERE OBJD= &OBJID
new  21: WHERE OBJD= 73020

no rows selected

SQL> INSERT INTO SAMAD.TBLDML_TEST (SELECT * FROM DBA_OBJECTS WHERE OBJECT_ID = 5010);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> @obj_buffer.sql
Enter value for objid: 73020
old  21: WHERE OBJD= &OBJID
new  21: WHERE OBJD= 73020

     FILE#     BLOCK# CLASS TYPE         STATUS     LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
        12        137 2ND LEVEL BMB      xcur       00
        12        138 SEGMENT HEADER     xcur       00
        12        216 1ST LEVEL BMB      xcur       00
        12        230 DATA BLOCK         xcur       00
        12        231 DATA BLOCK         xcur       00

SQL> SELECT OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBLDML_TEST  WHERE OBJECT_ID IN (2, 5000, 5010);  

 OBJECT_ID      FILE#     BLOCK#
---------- ---------- ----------
         2         12        139
      5000         12        229
      5010         12        230
SQL> INSERT INTO SAMAD.TBLDML_TEST (SELECT * FROM DBA_OBJECTS WHERE OBJECT_ID = 5020);

1 row created.

SQL> SELECT OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBLDML_TEST  WHERE OBJECT_ID IN (2, 5000, 5010, 5020);  

 OBJECT_ID      FILE#     BLOCK#
---------- ---------- ----------
         2         12        139
      5000         12        229
      5010         12        230
      5020         12        230

SQL> rollback;

Rollback complete.

SQL> SELECT OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBLDML_TEST  WHERE OBJECT_ID IN (2, 5000, 5010, 5020);  2    3

 OBJECT_ID      FILE#     BLOCK#
---------- ---------- ----------
         2         12        139
      5000         12        229
      5010         12        230

Update a Record on Table:

SQL> UPDATE SAMAD.TBLDML_TEST SET OBJECT_NAME='UPDATE' WHERE OBJECT_ID = 1000;

1 row updated.

SQL> @obj_buffer.sql
Enter value for objid: 73020
old  21: WHERE OBJD= &OBJID
new  21: WHERE OBJD= 73020

     FILE#     BLOCK# CLASS TYPE         STATUS     LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
        12        138 SEGMENT HEADER     xcur       00
        12        139 DATA BLOCK         xcur       00
        12        140 DATA BLOCK         xcur       00
        12        141 DATA BLOCK         xcur       00
        12        142 DATA BLOCK         xcur       00
        12        143 DATA BLOCK         xcur       00
        12        144 DATA BLOCK         xcur       00
        12        145 DATA BLOCK         xcur       00
        12        146 DATA BLOCK         xcur       00
        12        147 DATA BLOCK         xcur       00
        12        148 DATA BLOCK         xcur       00
        12        149 DATA BLOCK         xcur       00
        12        150 DATA BLOCK         xcur       00
        12        151 DATA BLOCK         xcur       00
        12        153 DATA BLOCK         xcur       00
        12        154 DATA BLOCK         xcur       00
        12        155 DATA BLOCK         xcur       00
        12        156 DATA BLOCK         xcur       00
        12        156 DATA BLOCK         cr         00
        12        157 DATA BLOCK         xcur       00
        12        158 DATA BLOCK         xcur       00
        12        159 DATA BLOCK         xcur       00

... ... ... 
 
        12        220 DATA BLOCK         xcur       00
        12        221 DATA BLOCK         xcur       00
        12        222 DATA BLOCK         xcur       00
        12        223 DATA BLOCK         xcur       00
        12        224 DATA BLOCK         xcur       00
        12        225 DATA BLOCK         xcur       00
        12        226 DATA BLOCK         xcur       00
        12        227 DATA BLOCK         xcur       00
        12        228 DATA BLOCK         xcur       00
        12        229 DATA BLOCK         xcur       00
        12        230 DATA BLOCK         xcur       00
        12        231 DATA BLOCK         xcur       00

90 rows selected.

Delete a Record from Table:

SQL> SELECT OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBLDML_TEST  WHERE OBJECT_ID IN (2000);     

 OBJECT_ID      FILE#     BLOCK#
---------- ---------- ----------
      2000         12        173

SQL> DELETE FROM SAMAD.TBLDML_TEST WHERE OBJECT_ID = 2000;

1 row deleted.

SQL> commit;

Commit complete.

Delete and Insert:

After Deleting records from table, all empty PCTUSED from these blocks are first candidate to next insert.

SQL> SELECT OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBLDML_TEST  WHERE OBJECT_ID BETWEEN 2 AND 100
ORDER BY OBJECT_ID; 

 OBJECT_ID      FILE#     BLOCK#
---------- ---------- ----------
         2         12        139
         3         12        139
         4         12        139
         5         12        139
         6         12        139
         7         12        139
         8         12        139
         9         12        139
        10         12        139
        11         12        139
        12         12        139
        ...       ...        ...
        68         12        139
        69         12        139
        70         12        140
        ...       ...        ...
       100         12        140

99 rows selected.

SQL> DELETE FROM SAMAD.TBLDML_TEST WHERE OBJECT_ID BETWEEN 2 AND 100;

99 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBLDML_TEST  WHERE OBJECT_ID BETWEEN 2 AND 100
ORDER BY OBJECT_ID;  2    3    4

no rows selected

SQL> INSERT INTO SAMAD.TBLDML_TEST (SELECT * FROM DBA_OBJECTS WHERE OBJECT_ID BETWEEN 2 AND 100);

99 rows created.

SQL> COMMIT;
SQL> SELECT OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBLDML_TEST  WHERE OBJECT_ID BETWEEN 2 AND 100
ORDER BY OBJECT_ID; 

 OBJECT_ID      FILE#     BLOCK#
---------- ---------- ----------
         2         12        139
         3         12        139
         4         12        139
         5         12        139
         6         12        139
         7         12        139
         8         12        139
         9         12        139
        10         12        139
        11         12        139
        12         12        139
        ...       ...        ...
        68         12        139
        69         12        139
        70         12        140
        ...       ...        ...
       100         12        140

99 rows selected.

Scripts:

[oracle@ol71ora193 ~]$ cat obj_buffer.sql
SELECT FILE#, BLOCK#, DECODE(CLASS#,
1, 'DATA BLOCK',
2, 'SORT BLOCK',
3, 'SAVE UNDO BLOCK',
4, 'SEGMENT HEADER',
5, 'SAVE UNDO HEADER',
6, 'FREE LIST',
7, 'EXTENT MAP',
8, '1ST LEVEL BMB',
9, '2ND LEVEL BMB',
10, '3RD LEVEL BMB',
11, 'BITMAP BLOCK',
12, 'BITMAP INDEX BLOCK',
13, 'FILE HEADER BLOCK',
14, 'UNUSED',
15, 'SYSTEM UNDO HEADER',
16, 'SYSTEM UNDO BLOCK',
17, 'UNDO HEADER',
18, 'UNDO BLOCK') "CLASS TYPE", STATUS, LOCK_ELEMENT_ADDR
FROM V$BH
WHERE OBJD= &OBJID
ORDER BY 1,2,3
/