Monday , September 25 2017
Home / Oracle DBA / ORACLE DATABASE TUNING WITH EXAMPLES

ORACLE DATABASE TUNING WITH EXAMPLES

ORACLE DATABASE TUNING WITH EXAMPLES

ORACLE DATABASE TUNING WITH EXAMPLES

Fragmentation

  1. High water mark (HWM) is the level of data represented in a table

  2. Generally oracle will not use space which is created by deleting some rows because high water mark will not be reset at that time. This create many unused free spaces in the table which leads to fragmentation

  3. The following are different ways to defragment a table across versions

    1. 5,6,7,8,8i – export/import

    2. 9i – export/import and move table

# To move a table

SQL> alter table emp move tablespace mydata;

The above command will create a duplicate table and copies the data, then drops the original table

Note: The above command is used even to normally move a table to another tablespace in case of space constraint. Also, we can move table to the same tablespace, but we need to have free space as double the size of table

# To check the size of table

SQL> select sum(bytes/1024/1024) from dba_segments where segment_name=’EMP’;

Note: After table move, the corresponding indexes will become UNUSABLE because the row id’s will change. We need to use any of the below commands to rebuild the indexes

# To check which indexes became unusable

SQL> select index_name,status from dba_indexes where table_name=’EMP’;

# To rebuild the index

SQL> alter index pk_emp rebuild;

SQL> alter index pk_emp rebuild online;

SQL> alter index pk_emp rebuild online nologging; – always prefer to use this command as it executes faster because no redo is generated

    1. 10g / 11g – export/import, expdp/impdp, move table & shrink compact

# To shrink a table

SQL> alter table scott.emp enable row movement;

SQL> alter table scott.emp shrink space compact;

SQL> alter table scott.emp disable row movement;

As row id’s doesn’t change with above commands, it is not necessary to rebuild indexes. While doing shrinking, still users can access the table, but it will use full scan instead of index scan

Note: Apart from table fragmentation, we have tablespace fragmentation and that will occur only in DMT or LMT with manual segment space management. The only solution is to export & import the objects in that tablespace. So, it is always preferred to use LMT with ASSM

ROW CHAINING

  1. If the data size is more than block size, data will spread into multiple blocks forming a chain which is called row chaining

  2. For example, when we are storing a 20k size of image, it will spread into 3 blocks as

shown below

ORACLE DATA BASE TUNING

# To find row chaining

SQL> select table_name,chain_cnt from dba_tables where table_name=’EMP’;

  1. Because data is spreaded across multiple blocks oracle need to perform multiple I/O’s to retrieve this which will lead to performance degradation

  2. Solution for row chaining is to create new tablespace with non-default block size and moving the tables

  3. We can create tablespaces with non default block size of 2k, 4k, 16k and 32k (8k is anyways default)

# To create non-default block size tablespace

SQL> create tablespace nontbs
         datafile ‘/datafiles/prod/nontbs01.dbf’ size 10m
         blocksize 16k;

  1. More block size cannot be fitted into default database buffer cache, so it is required to enable separate buffer cache

# To enable non-default buffer cache

SQL> alter system set db_16k_cache_size=100m scope=both;

Instead of 16, we can use 2,4,32 values also

Note: Once defined, we can’t change the default block size of the database

ROW MIGRATION

  1. Updating a row may increase row size and in such case it will use PCTFREE space

  2. If PCTFREE is full, but still a row requires more size for update, oracle will move that entire row to another block

  3. If many rows are moved like this, more I/O’s should be performed to retrieve data which degrades performance

  4. Solutions to avoid row migration is to increase the PCTFREE percentage or sometimes creating a non-default block size also acts as a solution

  5. Because PCTFREE is managed automatically in LMT, we will not observe any row migration in LMT

 WHAT IS PERFORMANCE TUNING IN ORACLE

View More:
ORACLE RMAN RECOVERY SCENARIOS WITH EXAMPLES
RECOVERY MANAGER (RMAN) ARCHITECTURE
STEPS FOR ORACLE CONTROFILE COMPLETE RECOVERY

Comments

comments

Check Also

How to switch on primary database to physical standby database

After configuration data guard then data is switching  into primary database  to standby database : …

Leave a Reply

Your email address will not be published. Required fields are marked *