Effective Tips for avoiding Oracle Hot Block Contention!!

 Author: Jaffar

Page: 1 2

Hot block contention can access heavily and is one of the common problems faced by most database administrators. However hot block contention problem for small look up tables can be solved if you are able to store a single row into a single block. You can use the MINIMIZE RECORDS_PER_BLOCK feature to store a single record into a single block.

Create Table without MINIMIZE RECORDS_PER_BLOCK:

First of all we will create a normal Heap Table without MINIMIZE RECORDS_PER_BLOCK

19:26:39 myuser@MYDB> create table MYTABLE1 as select * from user_objects where rownum = 1;
Table created.
Elapsed: 00:00:00.01

Analyze Table:

Now we will analyze the table with compute statistics command.

19:27:18 myuser@MYDB> analyze table MYTABLE1 compute statistics;
Table analyzed.
Elapsed: 00:00:00.00

Now we will analyze the number of rows and blocks in our test table MYTABLE1.

19:27:33 myuser@MYDB> select table_name,num_rows,blocks from user_tables where table_name = 'MYTABLE1';

        TABLE_NAME                NUM_ROWS                BLOCKS
        ------------------------------ -------------------               --------------
          MYTABLE1                             1                                   4

Inserting Values:

Now we will insert values into MYTABLE1

19:28:22 myuser@MYDB> insert into MYTABLE1 select * from user_objects;
242 rows created.
Elapsed: 00:00:00.00

Performing Commit:

Now we will perform commit

19:28:34 myuser@MYDB> commit;
Commit complete.
Elapsed: 00:00:00.00

Analyze Table:

Now we will analyze the table with compute statistics command.

19:28:35 myuser@MYDB> analyze table MYTABLE1 compute statistics;
Table analyzed.
Elapsed: 00:00:00.00

19:28:39 myuser@MYDB> select table_name,num_rows,blocks from user_tables where table_name = 'MYTABLE1';

        TABLE_NAME                 NUM_ROWS        BLOCKS
        --------------------                ------------------       -------------
        MYTABLE1                             243                         8

We can see that the total number of rows (243 in our case) comes in 8 blocks for our test table . Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Efficiently handle Tricky Data Guard Failures!!
   DBA Tips for Oracle Tablespaces!!
   Exceptional Tips for Exceptions in Oracle
   Common Oracle Errors : Cause & Action