Wednesday, June 24, 2009

Hash Partitioning tables on a db migrated to RAC

Generally partitioning is not ideal for high OLTP environments. In some cases may be useful to overcome contention problems on a single hot object modified all the time by increasing concurrency. So instead of having a single table segment with single index segment you can split it more segments using partitioning. I used hash partitioning to overcome contention problems on a single instance database that was migrated to a 3 node RAC database.

When I run performance tests on the single instance database I saw contention on some hot objects (log application tables). When this database migrated to RAC the problems were multiplied by the cluster wait events due to inter-instance block communication and locks on the shared SGA. This had a 30% percent drop of the TPS the RAC database could do compared the single instance using the same application version.

So I used hash partitioning on the primary key column using 64 and 128 partitions depending the object growth prediction. I also partitioned all indexes. After doing that the rac database had performed as the single instance and more TPS is depending on further improvement of the application.

Hash Partitioning

- A hash function is applied to the partition key to determine in which of the N partitions the data should be placed.

- The hash partition key chosen for a table should be a column or set of columns that are unique (Pks are the best) and if this not possible
must have as many distinct values as possible.

- Oracle recommends that N number of partitions must be a power of 2 (2,4,8,16,32,64 etc) in order to achieve the best overall distribution.

Monday, June 1, 2009

Index Organized Tables - IOTs

IOTs are an Oracle table structured introduced since 9i. They are tables stored in an index segment with their data stored sorted according to their primary key.

e.g

create table iot(a int , b int ,c int ,d int, primary key (a,b)) organization index;

When to use :

1) When having selects that accessing a table always by using indexed columns on the where clause:

e.g

select b,d,c from iot where a=100;

2) Similar for tables that are accessed exclusively by their primery key:

e.g

select d,c from iot where a=100 and b=10;

3) When you want to ensure that your data are stored in a specific order (by PK). This is usefull to physically co-locate child data.

4) For tall "skinny" tables. Tables with small number of colums.

When not to use:

1) For tables that are have lots of inserts.
2) For tables with large number of columns
3) For potentially large and very large tables
4) Not in data warehouse and datamart schemas. Allthough you can use them for small child tables in such schemas.