In Oracle : Object Tuning in an Oracle Database

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Performance Tuning

-> Object Tuning in an Oracle Database

 

 Oracle Database Object Tuning

 

 

Each data object has a storage definitions and in function of this the data can be accessed or modified faster. A faster read could have a negative impact on the writing and vice-versa.

     

     Here are the items of this article:

     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

Oracle Database Object Tuning

 

     5. Insert 200.000 rows in the EMP1 table

Oracle Database Object Tuning

 

     6. Check the size of the table on the disk

Oracle Database Object Tuning

 

     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

Oracle Database Object Tuning

 

     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.

 

 

     INITIAL, MINEXTENTS & PCTINCREASE storage object parameters

 

     INITIAL = the size of the initial extent allocated to the table

     MINEXTENTS = the minimum number of extents created for the data object when the object is created

     PCTINCREASE = tell to Oracle how to increase in percentage the storage for the table extents.

 

     These parameters must be set to values which don't waste the disk space and in the same time not to create new extents very often. For instance if we create a new table which will be populated with a lot of data, we have to create a big initial extent for the table. 

 

 

In-Oracle.com  -> Oracle DBA -> Oracle Performance Tuning

-> Object Tuning in an Oracle Database

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.