Oracle direct path loading-deep analysis of append

(I) direct path loading and buffer cache

Data inserted in the direct path is directly formatted as an Oracle block from PGA without passing through the buffer cache.

Then, a common Oracle Server Process writes data blocks to data files.

DBWn is not required because it does not pass through the buffer cache.

For example, if Table a is available, you need to insert data in Table B into Table a. Under normal insertion, You need to first Insert the data block I/O of Table a to the buffer cache.

Read from Block a in buffer cache and insert it into block B.

At this time, the blocks of B are changed to dirty blocks, and then wait for DBWn to flush them to the data file.

Therefore, after normal inserts, the blocks of table a and table B will appear in the buffer cache.

Directly Insert the data block I/O of Table a to the buffer cache, read the trip, and directly write it into the data file of Table B.

After the insertion is complete, except the header block, the data block of Table B does not appear in the buffer cache.

Test:

Hr @ ORCL> create table a (id number, name varchar2 (10 ));

Table created.

Hr @ ORCL> create table B (id number, name varchar2 (10 ));

Table created.

Hr @ ORCL> insert into a values (1, 'A ');

1 row created.

Hr @ ORCL> insert into a values (2, 'bb ');

1 row created.

Hr @ ORCL> insert into a values (3, 'cc ');

1 row created.

Hr @ ORCL> insert into a values (4, 'dd ');

1 row created.

Hr @ ORCL> commit;

Commit complete.

Hr @ ORCL> select dbms_rowid.rowid_relative_fno (rowid), dbms_rowid.rowid_block_number (rowid) from;

DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID)
------------------------------------------------------------------------
4 508
4 508
4 508
4 508
-- Currently, Table a has four rows, occupying 508 blocks. Currently, table B still has data.

-- Clears the buffer cache.

Hr @ ORCL> alter system flush buffer_cache;

System altered.

-- Insert data directly from Table a to table B

Hr @ ORCL> insert/* + append */into B select * from;

4 rows created.

Hr @ ORCL> commit;

Commit complete.

-- Use v $ bh to view blocks in the buffer cache

Hr @ ORCL> select file #, block # from v $ bh where objd = (select data_object_id from user_objects where object_name = 'A ');

FILE # BLOCK #
--------------------
4 508 blocks containing data
4 508 blocks containing data
4 511
4 511
4 506
4 509
4 509
4 512
4 512
4 507
4 507
4 510
4 510
4 505

14 rows selected.

-- This is because a full table scan is performed on Table a. All the blocks in Table a at a low water level are read into the buffer cache. Of course, this includes 508.

Hr @ ORCL> select file #, block # from v $ bh where objd = (select data_object_id from user_objects where object_name = 'B ');

FILE # BLOCK #
--------------------
4 2571
4 2569
4 2570

Hr @ ORCL> select dbms_rowid.rowid_relative_fno (rowid), dbms_rowid.rowid_block_number (rowid) from B;

DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID)
------------------------------------------------------------------------
4 2572
4 2572
4 2572
4 2572
-- The preceding two Queries show that the data in Table B occupies 2572 blocks. However, after the direct path is inserted, 2572 is not transferred to the buffer cache.
-- The buffer cache only contains 2569 2570 2571
-- Where 2571 is the field header block (select header_file, header_block from dba_segments where segment_name = 'B ')
-- 2570 2569 is the two bitmap blocks L1 L2.
-- Use normal insert next
Hr @ ORCL> alter system flush buffer_cache;

System altered.

Hr @ ORCL> insert into B select * from;

4 rows created.

Hr @ ORCL> commit;

Commit complete.

Hr @ ORCL> select file #, block # from v $ bh where objd = (select data_object_id from user_objects where object_name = 'B ');

FILE # BLOCK #
--------------------
4 2571
4 2574
4 2569
4 2575
4 2570
4 2570
4 2573
4 2576 bytes refers to the block where the common inserted data is located

8 rows selected.

Hr @ ORCL> select dbms_rowid.rowid_relative_fno (rowid), dbms_rowid.rowid_block_number (rowid) from B;

DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID)
------------------------------------------------------------------------
4 2572
4 2572
4 2572
4 2572
4 2576
4 2576
4 2576
4 2576

8 rows selected.

As shown in the above experiment, normal insertion requires data blocks to be first transmitted to the buffer cache.

This is a common way for Oracle to modify data. Instead of directly modifying the data file, the data file is modified in the memory, which is then protected by logs.

This is a desirable method for small changes, but direct paths can provide better performance for Big Data transactions.

In addition, direct path loading is the insertion action completed above the high water level. Therefore, no matter how many idle blocks under the high water level are ignored, the section space will increase accordingly.

1 2 3 4 Next Page