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.
- 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.