How Oracle calculates the db block changes segment metric?

59 views Asked by At

This week I came across an interesting point. It was necessary to calculate in which segments of the database the greatest changes were occurring. I first accessed view dba_hist_seg_stat. However, I found incorrect data on metric db block changes, so I started an investigation. I found out that view dba_hist_seg_stat receives its data based on AWR snapshots from view v$segstat.

I started monitoring changes in the statistics of the db block changes metric on a test table. I created an empty table in a tablespace with an 8 KB block and a segment space management type of AUTO.

create table test.test2 ( id number);

after I started filling out the table

declare
 res_change number;
 res_block number;
 block_id_current_insert number;
begin
FOR cntr IN 1..7000
LOOP
 insert into test.test2 select dbms_random.value(1000000, 9999999) from dual;
 commit;
 select value into res_change from v$SEGSTAT seg where seg.ts#=6 and seg.obj#=1603792 and seg.dataobj#=1604115 and seg.statistic#=3;
 select count(distinct dbms_rowid.ROWID_BLOCK_NUMBER(rowid)) into res_block from test.test2;
 select dbms_rowid.ROWID_BLOCK_NUMBER(max(rowid)) into block_id_current_insert from test.test2;
 dbms_output.put_line('Count step - '||cntr||'.Db block changes - '|| res_change||'.Count block table test2 - '||res_block||'. Current block change - '||block_id_current_insert||'.');
end loop; 
end;
/

and got the following script output

Count step - 1.Db block changes - 240.Count block table test2 - 1. Current block change - 1054763.
Count step - 2.Db block changes - 240.Count block table test2 - 1. Current block change - 1054763.
Count step - 3.Db block changes - 240.Count block table test2 - 1. Current block change - 1054763.
.....
Count step - 6998.Db block changes - 528.Count block table test2 - 27. Current block change - 1054767.
Count step - 6999.Db block changes - 528.Count block table test2 - 27. Current block change - 1054767.
Count step - 7000.Db block changes - 528.Count block table test2 - 27. Current block change - 1054767.
PL/SQL procedure successfully completed.

as it can be seen from the output, 7000 rows were inserted into the table. The number of existing blocks in the table became 27, and the number of changes in the table amounted to 528-240 = 288 blocks. Why? Moreover, as can be seen from the first 3 rows, the row was inserted into the table, but this did not cause an increase in the metric. Why?

0

There are 0 answers