Demonstrate the impact of PCTUSED and PCTFREE on Data Operations

Demonstrate the impact of PCTUSED and PCTFREE on data operations in the PCTFREE experiment. As pctfree increases, the fewer block-mounted records and the more data blocks occupied.

SQL> create table test as select rownum as id from dual connect by level <= 1960; the table has been created. SQL> create table test1 pctfree 20 as select rownum as id from dual connect by level <= 1960; the table has been created. SQL> create table test2 pctfree 40 as select rownum as id from dual connect by level <= 1960; the table has been created. SQL> create table test3 pctfree 60 as select rownum as id from dual connect by level <= 1960; the table has been created. SQL> select block_id, count (*) from (select dbms_rowid.rowid_block_number (rowid) block_id, dbms_rowid.rowid_row_number (rowid) num from test) group by block_id; BLOCK_ID COUNT (*) ---------- 538805 657 538804 657 538806 646SQL> select block_id, count (*) from (select dbms_rowid.rowid_block_number (rowid) block_id, substring (rowid) num from test1) group by block_id; BLOCK_ID COUNT (*) ---------- 545348 584 545349 584 545351 208 584SQL> select block_id, count (*) from (select dbms_rowid.rowid_block_number (rowid) block_id, substring (rowid) num from test2) group by block_id; BLOCK_ID COUNT (*) ---------- 833380 437 833383 437 833384 212 833381 437 833382 437SQL> select block_id, count (*) from (select dbms_rowid.rowid_block_number (rowid) block_id, dbms_rowid.rowid_row_number (rowid) num from test3) group by block_id; BLOCK_ID COUNT (*) ---------- -------- 833389 291 833390 291 833391 291 833392 291 833394 214 833388 291 833393 291

 

In the PCTUSED experiment, you need to create a table space that is manually managed. When you delete some data in a block and then insert some data, you can see that the smaller the value of PCTUSED, the more data blocks.

SQL> create tablespace USERS02 datafile 'd: \ oracle \ product \ 10.2.0 \ oradata \ ordb10 \ USER02.DBF 'size 100 m autoextend on next 10 m segment space management manual; SQL> create table test pctused 40 tablespace USERS02 as select * from dba_objects; the table has been created. SQL> create table test1 pctused 80 tablespace USERS02 as select * from dba_objects; the table has been created. SQL> exec dbms_stats.gather_table_stats (user, 'test'); PL/SQL process completed successfully. SQL> exec dbms_stats.gather_table_stats (user, 'test1'); PL/SQL process completed successfully. SQL> select table_name, blocks from user_tables s where s. table_name in ('test', 'test1'); TABLE_NAME BLOCKS tables ---------- TEST 693TEST1 693SQL> delete from TEST where rowid in (select rowid from (select rowid, dbms_rowid.rowid_block_number (rowid) block_id, dbms_rowid.rowid_row_number (rowid) num from test) where num <20); 13860 rows have been deleted. SQL> delete from test1 where rowid in (select rowid from (select rowid, dbms_rowid.rowid_block_number (rowid) block_id, dbms_rowid.rowid_row_number (rowid) num from test1) where num <20); 13860 rows deleted. SQL> commit; submitted completely. SQL> insert into test select * from dba_objects; 50479 rows have been created. SQL> insert into test1 select * from dba_objects; 50479 rows have been created. SQL> commit; submitted completely. SQL> exec dbms_stats.gather_table_stats (user, 'test'); PL/SQL process completed successfully. SQL> exec dbms_stats.gather_table_stats (user, 'test1'); PL/SQL process completed successfully. SQL> select table_name, blocks from user_tables s where s. table_name in ('test', 'test1'); TABLE_NAME BLOCKS ---------------------------- ---------- TEST 1384TEST1 1196