we have a fairly small table (~25k rows) that is being heavily written to and most of the rows go away quickly (it's part of a scheduling application which stores information about the jobs in that table).
It's a third party application which does a huge amount of small transactions which result in a high contention on that table.
This is all running on a two node RAC cluser using Oracle 11gR1
We can see in the AWR reports that most of the time is spent waiting for the cluster synchronization.
We would like to (hash) partition that table in order to distribute the contention on the blocks being modified.
However the table also contains a BLOB
column which is used by the application to store the state of the job. We also see a high volume of reads and writes to the LOB segment for that column.
My question is: when we partition the table, is there a way to create multiple LOB segments as well? So that there is a one-to-one relation between table partitions (i.e. segments) and LOB segments?
I didn't find anything in the manual, but as the documentation for CREATE TABLE is sooo huge I wouldn't be surprised if I overlooked something.
Best Answer
Yes, it seems you overlooked it indeed: