Sunday, July 5, 2026

Blog List

Posted by at

Oracle Partition Split With LOB Segment Performance Test

In this Blog, at first, we run performance tests of partition split with LOB segment in different chunk size of BasicFiles and SecureFiles. Then we look their I/O access.

Note: Tested in Oracle 19c with: 
 
  db_block_size  8192 (default)
  db_securefile  permitted (default)


1. Test Code


We create a range partitioned table with one basicfile LOB: 

drop table test_tab purge;

create table test_tab (
  id            number,
  code          varchar2(10),
  name          varchar2(100),
  created_date  date,
  text          clob,
  constraint test_tab_pk primary key (id)
)
lob (text) store as basicfile (
--lob (text) store as securefile test_tab_sf_text (
--compress medium 
  enable      storage in row
--  chunk       32768   -- 8192 (default), 32768=4*8192, no effect for securefile
)
partition by range (created_date)
(
  partition test_tab_2026 values less than (maxvalue)
);

create index test_tab#code         on test_tab (code);
create index test_tab#created_date on test_tab (created_date) local;

create or replace procedure test_tab_fill(p_row_cnt number, p_lob_len_kb number) as
  l_lob clob := empty_clob();
  l_len number;
begin
  insert into test_tab 
  select level id, rpad('ABC', 8, 'X') code, rpad('ABC', 80, 'X') name, to_date('22-NOV-202'||(mod(level,7)),'DD-MON-YYYY') created_date, empty_clob() text
  from dual connect by level <= p_row_cnt; 
  
  dbms_lob.createtemporary(l_lob, true, dbms_lob.session);
  
  for i in 1..p_lob_len_kb loop
    dbms_lob.append(dest_lob => l_lob, src_lob => rpad('ABC', 1024, 'X'));  
  end loop;
  
  update test_tab set text = rownum||l_lob; 
  commit;
end;
/


2. Basicfile Test


We will make two Basicfile tests, one with LOB chunk size 8192, one with LOB chunk size 32768.


2.1 Basicfile with Chunk 8192


We prepare and run test with: 

-- Fill table with 1000 rows, each contain one 8 MB Clob
exec test_tab_fill(1000, 1024*8);

exec dbms_stats.gather_table_stats(user, 'TEST_TAB', cascade => true);

select round(sum(length(text)/1024/1024)) mb, count(*), round(sum(length(text))/count(*)/1024) avg_len_kb from test_tab;

	        MB   COUNT(*) AVG_LEN_KB
	---------- ---------- ----------
	      8000       1000       8192

alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier = 'test_b8192';
alter session set events '10046 trace name context forever, level 12'; 

alter table test_tab
  split partition test_tab_2026 at (date'2025-12-31')
  into (partition test_tab_2025,
        partition test_tab_2026)
update indexes --parallel 8
;

  Elapsed: 00:08:56.18
  
alter session set events '10046 trace name context off';
It takes about 8 minutes.

Here the SQL trace: 

********************************************************************************
alter table test_tab
  split partition test_tab_2026 at (date'2025-12-31')
  into (partition test_tab_2025,
        partition test_tab_2026)
update indexes --parallel 8

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     99.30     533.69    2064038    2113185    5401904        1000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     99.31     533.70    2064038    2113185    5401904        1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 49
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD AS SELECT  TEST_TAB (cr=2112099 pr=2064025 pw=2064028 time=533571542 us starts=1)
      1000       1000       1000   PARTITION RANGE SINGLE PARTITION: 1 1 (cr=31 pr=1 pw=0 time=9524 us starts=1 cost=2 size=184336 card=82)
      1000       1000       1000    TABLE ACCESS FULL TEST_TAB PARTITION: 1 1 (cr=31 pr=1 pw=0 time=8397 us starts=1 cost=2 size=184336 card=82)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                       364        0.01          0.26
  control file sequential read                 3260        0.11          1.97
  datafile move cleanup during resize           163        0.00          0.02
  Disk file operations I/O                      489        0.01          0.14
  Data file init write                          187        0.02          0.46
  direct path sync                              163        0.00          0.03
  db file single write                          163        0.02          0.04
  control file parallel write                   489        0.04          0.94
  DLM cross inst call completion                163        0.02          0.21
  direct path read                          2063998        0.24        433.13
  direct path write                            1906        0.06          4.93
  log file switch (private strand flush incomplete)
                                                  1        0.02          0.02
  local write wait                               16        0.00          0.01
  reliable message                                8        0.00          0.00
  enq: RO - fast object reuse                     4        0.00          0.00
  enq: CR - block range reuse ckpt                4        0.00          0.00
  write complete waits                            1        0.00          0.00
  PGA memory operation                            1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************
If during test, we monitor the test session: 

select event, p1, p2, p3, p1text, p2text, p3text, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# 
from v$session where sid = 912;

	EVENT                P1       P2  P3 P1TEXT       P2TEXT      P3TEXT     ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
	----------------- ----- -------- --- ------------ ----------- ---------- ------------- -------------- --------------- -------------
	direct path write    22  5376932   1 file number  first dba   block cnt        6244876             22         3621774             0
         
         
select sample_time, event, p1, p2, p3, p1text, p2text, p3text, current_obj#, current_file#, current_block#, current_row#
from v$active_session_history t 
where sample_time > sysdate-35/1440
  and session_id = 912
  and event in ('direct path read', 'direct path write')
order by t.sample_time;

	SAMPLE_TIME          EVENT                P1       P2  P3 P1TEXT       P2TEXT     P3TEXT     CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK# CURRENT_ROW#
	-------------------- ----------------- ----- -------- --- ------------ ---------- ---------- ------------ ------------- -------------- ------------
	03-JUL-2026 13:09:37 direct path write    22  5376932   1 file number  first dba  block cnt       6244876            22        3621774            0
	03-JUL-2026 13:11:18 direct path write    22  5673340   1 file number  first dba  block cnt       6244876            22        2374684            0
	03-JUL-2026 13:12:30 direct path write    22  5890030   1 file number  first dba  block cnt       6244876            22        3427384            0


	SAMPLE_TIME          EVENT                P1       P2  P3 P1TEXT       P2TEXT     P3TEXT     CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK# CURRENT_ROW#
	-------------------- ----------------- ----- -------- --- ------------ ---------- ---------- ------------ ------------- -------------- ------------
	03-JUL-2026 13:12:41 direct path read     22  3609403   1 file number  first dba  block cnt       6244876            22        3609403            0
	03-JUL-2026 13:12:39 direct path read     22  3573182   1 file number  first dba  block cnt       6244876            22        3573182            0
	03-JUL-2026 13:12:36 direct path read     22  3514427   1 file number  first dba  block cnt       6244876            22        3514427            0


select * from sys.lobfrag$ where fragobj# = 6244876;

	  FRAGOBJ# PARENTOBJ# TABFRAGOBJ# INDFRAGOBJ#      FRAG# F        TS#      FILE#     BLOCK#      CHUNK PCTVERSION$  FRAGFLAGS    FRAGPRO     SPARE1     SPARE2     SPARE3
	---------- ---------- ----------- ----------- ---------- - ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ----------
	   6244876    6244875     6244874     6244878         10 P       4140       1024     999930          1          10         97          2


select object_name, subobject_name, object_id, data_object_id, object_type from dba_objects where object_name = 'TEST_TAB';

	OBJECT_NAM SUBOBJECT_NAME   OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
	---------- --------------- ---------- -------------- ---------------
	TEST_TAB   TEST_TAB_2025      6244883        6244883 TABLE PARTITION
	TEST_TAB   TEST_TAB_2026      6244874        6244884 TABLE PARTITION
	TEST_TAB                      6244873                TABLE


select table_name, column_name, segment_name, index_name, chunk, securefile from dba_lobs where table_name = 'TEST_TAB';

	TABLE_NAME COLUMN_NAM SEGMENT_NAME              INDEX_NAME                     CHUNK SEC
	---------- ---------- ------------------------- ------------------------- ---------- ---
	TEST_TAB   TEXT       SYS_LOB0006244873C00005$$ SYS_IL0006244873C00005$$        8192 NO


select table_name, column_name, lob_name, partition_name, lob_partition_name, lob_indpart_name, partition_position, chunk 
from dba_lob_partitions where table_name = 'TEST_TAB';

	TABLE_NAME COLUMN_NAM LOB_NAME                  PARTITION_NAME  LOB_PARTITION_NAME   LOB_INDPART_NAME     PARTITION_POSITION      CHUNK
	---------- ---------- ------------------------- --------------- -------------------- -------------------- ------------------ ----------
	TEST_TAB   TEXT       SYS_LOB0006244873C00005$$ TEST_TAB_2025   SYS_LOB_P647334      SYS_IL_P647336                        1       8192
	TEST_TAB   TEXT       SYS_LOB0006244873C00005$$ TEST_TAB_2026   SYS_LOB_P647335      SYS_IL_P647337                        2       8192


$ > fgrep 'direct path read' testdb_ora_2472818_test_b8192.trc

	WAIT #139722977275192: nam='direct path read' ela= 527 file number=22 first dba=1047862 block cnt=1 obj#=6244876 tim=24601964026630
	WAIT #139722977275192: nam='direct path read' ela= 356 file number=22 first dba=1047863 block cnt=1 obj#=6244876 tim=24601964027037
	WAIT #139722977275192: nam='direct path read' ela= 934 file number=22 first dba=1047857 block cnt=1 obj#=6244876 tim=24601964028034


$ > fgrep 'direct path read' testdb_ora_2472818_test_b8192.trc|wc

  2063998 30959970 270521981
With strace, we can see: 


$ > strace -p 

io_submit(0x7f13d15dd000, 1, [{aio_data=0x7f13c9a50488, aio_lio_opcode=IOCB_CMD_PREAD, aio_fildes=264, aio_buf=0x7f13caee1000, aio_nbytes=8192, aio_offset=24147320832}]) = 1
io_getevents(0x7f13d15dd000, 2, 128, [{data=0x7f13ca651ef8, obj=0x7f13ca651ef8, res=8192, res2=0}, {data=0x7f13c9a50488, obj=0x7f13c9a50488, res=8192, res2=0}], {tv_sec=0, tv_nsec=0}) = 2
io_submit(0x7f13d15dd000, 1, [{aio_data=0x7f13ca6525f8, aio_lio_opcode=IOCB_CMD_PWRITE, aio_fildes=264, aio_buf="(\242\0\0\261\3210\0\261\341\323:|\f\2\4\320A\0\0XJ_\0\0\0\0\1\0\0!\v"..., aio_nbytes=8192, aio_offset=26209558528}]) = 1
Above output shows "direct path read" is with "block cnt" = 1 (1 data block of size 8192, aio_nbytes=8192).

Note FRAGOBJ# = 6244876 in sys.lobfrag$ is a temporay object used during partition split, it does not exist on dba_objects. 

select * from dba_objects where object_id = 6244876 or data_object_id = 6244876;
  no rows selected
  
select * from sys.obj$ where obj#  = 6244876 or dataobj# = 6244876;
  no rows selected
Also Oracle has a Limitation:
Oracle partition split using Parallel DML (PDML) does not work for BASICFILE LOB segment when specifying parallel clause
although Xplan still showed "PX COORDINATOR". 

********************************************************************************

ALTER TABLE t 
  SPLIT PARTITION t1_2017 AT (TO_DATE('31-DEC-2015 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
  INTO (PARTITION t1_2015,
        PARTITION t1_2017)
UPDATE INDEXES PARALLEL 8

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      4.85       7.69      88712      95141     235000         192
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      4.85       7.69      88712      95141     235000         192

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 49  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD AS SELECT  T  (cr=94793 pr=88708 pw=88709 time=7609875 us starts=1)
       192        192        192   PX COORDINATOR  (cr=3 pr=0 pw=0 time=33097 us starts=1)
         0          0          0    PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=41280 card=192)
         0          0          0     PX BLOCK ITERATOR PARTITION: 1 1 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=41280 card=192)
         0          0          0      TABLE ACCESS FULL T1 PARTITION: 1 1 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=41280 card=192)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PX Deq: Join ACK                                8        0.00          0.00
  PX Deq: Parse Reply                             8        0.01          0.02
  PX Deq: Execute Reply                          13        0.00          0.00
  db file sequential read                         8        0.00          0.02
  direct path read                            88702        0.00          3.76
  direct path write                             198        0.02          0.05
  PX Deq: Signal ACK EXT                          8        0.00          0.00
  PX Deq: Slave Session Stats                     8        0.00          0.00
  enq: PS - contention                            1        0.00          0.00
  reliable message                                8        0.00          0.00
  enq: RO - fast object reuse                     4        0.00          0.00
  enq: CR - block range reuse ckpt                4        0.00          0.00
  log file sync                                   1        0.00          0.00
  PGA memory operation                            1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.01          0.01
********************************************************************************


2.2 Basicfile with Chunk 32768


We further make a test of LOB Chunk Size: 32768 (4*8192): 

--------------------- chunk 32768 -----------------------

drop table test_tab purge;

create table test_tab (
  id            number,
  code          varchar2(10),
  name          varchar2(100),
  created_date  date,
  text          clob,
  constraint test_tab_pk primary key (id)
)
lob (text) store as basicfile (
--lob (text) store as securefile test_tab_sf_text (
--compress medium 
  enable      storage in row
  chunk       32768   -- 8192 (default), 32768=4*8192, no effect for securefile
)
partition by range (created_date)
(
  partition test_tab_2026 values less than (maxvalue)
);

create index test_tab#code         on test_tab (code);
create index test_tab#created_date on test_tab (created_date) local;


-- Fill table with 1000 rows, each contain one 8 MB Clob
exec test_tab_fill(1000, 1024*8);

exec dbms_stats.gather_table_stats(user, 'TEST_TAB', cascade => true);
Then we make the split test: 

alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier = 'test_b32768';
alter session set events '10046 trace name context forever, level 12'; 

alter table test_tab
  split partition test_tab_2026 at (date'2025-12-31')
  into (partition test_tab_2025,
        partition test_tab_2026)
update indexes --parallel 8
;

Elapsed: 00:05:49.63

alter session set events '10046 trace name context off';
It takes 349 seconds with CHUNK=32768, compared to above 533 seconds of CHUNK=8192.

Here SQL trace: 

********************************************************************************

alter table test_tab
  split partition test_tab_2026 at (date'2025-12-31')
  into (partition test_tab_2025,
        partition test_tab_2026)
update indexes --parallel 8

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     34.70     349.05    2064014    2094838    2877842        1000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     34.70     349.05    2064014    2094838    2877842        1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 49
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD AS SELECT  TEST_TAB (cr=2093924 pr=2064009 pw=2064028 time=348998633 us starts=1)
      1000       1000       1000   PARTITION RANGE SINGLE PARTITION: 1 1 (cr=30 pr=0 pw=0 time=9147 us starts=1 cost=10 size=281000 card=1000)
      1000       1000       1000    TABLE ACCESS FULL TEST_TAB PARTITION: 1 1 (cr=30 pr=0 pw=0 time=8270 us starts=1 cost=10 size=281000 card=1000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  direct path read                           509092        0.42        313.25
  direct path write                            1489        0.05          2.25
  db file sequential read                        14        0.02          0.08
  PGA memory operation                            2        0.00          0.00
  reliable message                                8        0.00          0.00
  enq: RO - fast object reuse                     4        0.00          0.00
  enq: CR - block range reuse ckpt                4        0.00          0.00
  write complete waits                            1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.01          0.01
********************************************************************************
And monitoring output: 

select table_name, column_name, segment_name, index_name, chunk, securefile from dba_lobs where table_name = 'TEST_TAB';

	TABLE_NAME COLUMN_NAM SEGMENT_NAME              INDEX_NAME                     CHUNK SEC
	---------- ---------- ------------------------- ------------------------- ---------- ---
	TEST_TAB   TEXT       SYS_LOB0006244894C00005$$ SYS_IL0006244894C00005$$       32768 NO

select event, p1, p2, p3, p1text, p2text, p3text, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session where sid = 912;

	EVENT                        P1         P2         P3 P1TEXT          P2TEXT          P3TEXT          ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
	-------------------- ---------- ---------- ---------- --------------- --------------- --------------- ------------- -------------- --------------- -------------
	direct path read             22    5217040          4 file number     first dba       block cnt             6244897             22         5217040             0


$ > strace  -p 2472818

	io_submit(0x7f13d15dd000, 1, [{aio_data=0x7f13ca2814f0, aio_lio_opcode=IOCB_CMD_PREAD, aio_fildes=264, aio_buf=0x7f13ca299000, aio_nbytes=32768, aio_offset=36884873216}]) = 1
	io_getevents(0x7f13d15dd000, 2, 128, [{data=0x7f13ca6502f8, obj=0x7f13ca6502f8, res=32768, res2=0}], {tv_sec=0, tv_nsec=0}) = 1
	io_getevents(0x7f13d15dd000, 1, 128, [{data=0x7f13ca2814f0, obj=0x7f13ca2814f0, res=32768, res2=0}], {tv_sec=600, tv_nsec=0}) = 1
Above output shows "direct path read" is with "block cnt" = 4 (4 data blocks of size 8192, aio_nbytes=32768), compared to "block cnt" = 1 (1 data block of size 8192).


3 Securefile Test


Now we make one more test with securefile: 

--------------------- securefile -----------------------

drop table test_tab purge;

create table test_tab (
  id            number,
  code          varchar2(10),
  name          varchar2(100),
  created_date  date,
  text          clob,
  constraint test_tab_pk primary key (id)
)
--lob (text) store as basicfile (
lob (text) store as securefile test_tab_sf_text (
--compress medium 
  enable      storage in row
--  chunk       32768   -- 8192 (default), 32768=4*8192, no effect for securefile
)
partition by range (created_date)
(
  partition test_tab_2026 values less than (maxvalue)
);

create index test_tab#code         on test_tab (code);
create index test_tab#created_date on test_tab (created_date) local;


-- Fill table with 1000 rows, each contain one 8 MB Clob
exec test_tab_fill(1000, 1024*8);

exec dbms_stats.gather_table_stats(user, 'TEST_TAB', cascade => true);

select round(sum(length(text)/1024/1024)) mb, count(*), round(sum(length(text))/count(*)/1024) avg_len_kb from test_tab;

	        MB   COUNT(*) AVG_LEN_KB
	---------- ---------- ----------
	      8000       1000       8192
Then we make the split test: 

alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier = 'test_sec';
alter session set events '10046 trace name context forever, level 12'; 

alter table test_tab
  split partition test_tab_2026 at (date'2025-12-31')
  into (partition test_tab_2025,
        partition test_tab_2026)
update indexes --parallel 8
;

Elapsed: 00:01:18.87

alter session set events '10046 trace name context off';
It takes 78 seconds, compared to basicfile of 533 seconds (CHUNK=8192) and 349 seconds (CHUNK=32768).

Her SQL trace: 

********************************************************************************

alter table test_tab
  split partition test_tab_2026 at (date'2025-12-31')
  into (partition test_tab_2025,
        partition test_tab_2026)
update indexes --parallel 8

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     21.50      78.46    2082018      44947     103278        1000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     21.51      78.47    2082018      44947     103278        1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 49
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD AS SELECT  TEST_TAB (cr=44142 pr=2082015 pw=2082024 time=78415081 us starts=1)
      1000       1000       1000   PARTITION RANGE SINGLE PARTITION: 1 1 (cr=30 pr=0 pw=0 time=9530 us starts=1 cost=10 size=255000 card=1000)
      1000       1000       1000    TABLE ACCESS FULL TEST_TAB PARTITION: 1 1 (cr=30 pr=0 pw=0 time=9027 us starts=1 cost=10 size=255000 card=1000)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  direct path read                             5899        0.31         54.81
  local write wait                              664        0.19          0.42
  reliable message                               10        0.00          0.00
  enq: CR - block range reuse ckpt                6        0.00          0.00
  db file sequential read                        66        0.02          0.04
  direct path write                              71        0.02          0.24
  control file sequential read                  480        0.03          0.21
  datafile move cleanup during resize            24        0.00          0.00
  Disk file operations I/O                       72        0.03          0.09
  Data file init write                           42        0.02          0.24
  direct path sync                               24        0.00          0.00
  db file single write                           24        0.00          0.00
  control file parallel write                    72        0.00          0.06
  DLM cross inst call completion                 24        0.00          0.01
  enq: RO - fast object reuse                     4        0.01          0.01
  PGA memory operation                            1        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.01          0.01
********************************************************************************
And monitoring output: 

select table_name, column_name, segment_name, index_name, chunk, securefile from dba_lobs where table_name = 'TEST_TAB';

	TABLE_NAME COLUMN_NAM SEGMENT_NAME              INDEX_NAME                     CHUNK SECUREFILE
	---------- ---------- ------------------------- ------------------------- ---------- ---------------
	TEST_TAB   TEXT       TEST_TAB_SF_TEXT          SYS_IL0006244918C00005$$        8192 YES


select sample_time, event, p1, p2, p3, p1text, p2text, p3text, current_obj#, current_file#, current_block#, current_row#
from v$active_session_history t 
where sample_time > sysdate-5/1440
  and session_id = 912
  and event in ('direct path read', 'direct path write') 
order by t.sample_time;

	SAMPLE_TIME          EVENT                        P1         P2         P3 P1TEXT          P2TEXT          P3TEXT          CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK# CURRENT_ROW#
	-------------------- -------------------- ---------- ---------- ---------- --------------- --------------- --------------- ------------ ------------- -------------- ------------
	03-JUL-2026 14:48:23 direct path read             22    5607312        128 file number     first dba       block cnt            6244921            22        5607312            0
	03-JUL-2026 14:48:24 direct path read             22    4057350        128 file number     first dba       block cnt            6244921            22        4057350            0
	03-JUL-2026 14:48:25 direct path read             22    4086036        128 file number     first dba       block cnt            6244921            22        4086036            0


$ > strace  -p 2472818

	io_submit(0x7f13d15dd000, 1, [{aio_data=0x681ca000, aio_lio_opcode=IOCB_CMD_PREAD, aio_fildes=264, aio_buf=0x16f400000, aio_nbytes=1048576, aio_offset=32394772480}]) = 1
	io_submit(0x7f13d15dd000, 1, [{aio_data=0x8ea63e48, aio_lio_opcode=IOCB_CMD_PREAD, aio_fildes=264, aio_buf=0x16f300000, aio_nbytes=1048576, aio_offset=32395821056}]) = 1
	io_submit(0x7f13d15dd000, 1, [{aio_data=0x8c41ddb0, aio_lio_opcode=IOCB_CMD_PREAD, aio_fildes=264, aio_buf=0x16f200000, aio_nbytes=1048576, aio_offset=32396869632}]) = 1
	io_submit(0x7f13d15dd000, 1, [{aio_data=0x66503b30, aio_lio_opcode=IOCB_CMD_PREAD, aio_fildes=264, aio_buf=0x16fa00000, aio_nbytes=1048576, aio_offset=32397918208}]) = 1
Above output shows "direct path read" is with "block cnt" = 128 (128 data blocks of size 8192, aio_nbytes=1048576).
Posted by at
Subscribe to: Posts (Atom)