Home  >  Oracle Database Administration (DBA)  >  Database Tuning  >  Objects Tuning

     PCTFREE & PCTUSED object parameters (When Automatic Segment Space Tablespace Management is not used)

     PCTFREE = sets the value for the percent of a block to reserve for updates.

     Supposing we have to insert 1000 rows in a table (that table has PCTFREE at 10%) and at one moment Oracle starts writing in a particular block. When the block will be 90% full with data and 10% will be empty (90% of the block will contains block header information and data) the block will be marked as non available for the future inserts and the rows will continue to be written to other blocks. If the PCTFREE parameter has a small value (0-5% for instance) the INSERT statement will need less disk space, however big updated on the table will put the data for  some rows on 2 data blocks (instead one) and that will create performance issue (when the row will be read 2 block need to be read instead one). If the PCTFREE parameter has a big value 40% the disk will be waste. The default value for PCTFREE is 10. 

   Here is an example (the code must be used/run in the SCOTT schema):

       1. Create a tablespace which doesn't use the Automatic Segment Space Management

             create tablespace DATA_1_TBS

             datafile 'C:\data_1_tbs_file.dbf' size 5M autoextend on

             logging

             online

             permanent

             extent management local autoallocate

             blocksize 8k;

           

                NOTE: adding SEGMENT SPACE MANAGEMENT AUTO to the tablespace definition will enable the Automatic Segment Space 

  Management for the tablespace.

       2. Create EMP1 table (with PCTFREE set to 2%)

             create table emp1

            tablespace DATA_1_TBS

            pctfree 2

            as select * from emp;

                                ALTER TABLE SCOTT.EMP1   MODIFY (EMPNO NUMBER(10) );

                        ALTER TABLE SCOTT.EMP1   MODIFY (ENAME VARCHAR2(100) );

                  3. Create the INS_EMP1_PRC procedure in a test schema to insert data in the EMP1 table

CREATE OR REPLACE PROCEDURE INS_EMP1_PRC (PNU_NO_INS in NUMBER) IS

k number default 0;
VNU_COMM number;

BEGIN
  for i in 1 .. PNU_NO_INS loop
     if k < 50 then 
        k := k+10;
     else 
        k := 10;
     end if;
     select round(dbms_random.value(0,0.6))*round(dbms_random.value(0,2000),-2) into VNU_COMM from dual;

     insert into emp1 values

              (i, 

              'Name_'||i,

              'JOB NAME', 

               null, 

               to_date(to_char(sysdate-10*k*round(dbms_random.value(1,20)),'Mon/dd/yyyy'), 'Mon/dd/yyyy'),                round(dbms_random.value(1200,9000),-3), decode(VNU_COMM, 0, null, VNU_COMM), k);
     commit;
  end loop;

EXCEPTION
WHEN OTHERS THEN
  RAISE;
END INS_EMP1_PRC;
/

     4. Check the size of the table on the disk

     5. Insert 200.000 rows in the EMP1 table

     6. Check the size of the table on the disk

     7. Drop EMP1 table and create it again with PCTFREE set to 50%

            DROP TABLE EMP1;

            create table emp1

            tablespace DATA_1_TBS

            pctfree 50

            as select * from emp;

                                ALTER TABLE SCOTT.EMP1   MODIFY (EMPNO NUMBER(10) );

                        ALTER TABLE SCOTT.EMP1   MODIFY (ENAME VARCHAR2(100) );

     8. Insert 200.000 rows in the EMP1 table

     9. Check the size of the table on the disk

     Now we can see that more space is used on a disk for the same amount of data. 

     PCTUSED 

     When the rows are deleted, the block will be available for the UPDATE statements, but only when the block is used less then the PCTUSED parameter. Setting PCTUSED to a high value (75%)  then a block will quickly become available to accept new rows, but it will not have room for a lot of rows before it becomes logically full again. Also, a high value for PCTUSED will put/ remove often the block on the blocks free list. All these have a negative impact on the database performance. Setting PCTUSED to a small value (10%) will make Oracle not to use this block even if (after DELETE statements) the block will be almost empty (supposing 12% full). This will waste the disk space. The default value for PCTUSED is 40.

     NOTE: The Automatic Segment Space Tablespace Management (ASSM) is new in Oracle9i and is implemented by adding the SEGMENT SPACE MANAGEMENT AUTO clause to the tablespace definition. ASM tablespaces automate freelist management and remove the ability to specify PCTFREE, PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters.

More information about  this subject ( Objects Tuning ) you can get from  www.in-oracle.com