Range partitioning

[Sweetie]

Hi

We want to partition some of our tables and indexes. We are going to hash partition our tables since the data distribution is not very uniform, i.e no logical range.

However we want to GLOBAL partition our indexes because this is a OLTP database. Many of indexes have no logical range neither. My question if I want to use logical ranges I will end up having partitions of different size, for example

account_no between 1 and 100000 I will have 400000 rows
account_no between 100001 and 200000 I will have 100000 rows

and so on

Does this affect performance?

I have another way to distribute the data uniformly using range partition however my range will be very "DODGY"

something like this

        NT    MIN(ID)    MAX(ID)   COUNT(*) MAX(ID)-MIN(ID)
---------- ---------- ---------- ---------- ---------------
         1          1     175108     984025          175107
         2     175108     310148     984025          135040
         3     310148     445173     984025          135025
         4     445173     586530     984025          141357
         5     586530     773611     984025          187081
         6     773611    1004361     984025          230750
         7    1004362    1220448     984025          216086
         8    1220449    1533474     984025          313025
         9    1533474    1814891     984025          281417
        10    1814891    2081213     984025          266322
        11    2081214    2317213     984025          235999
        12    2317213    2549731     984025          232518
        13    2549731    2771108     984025          221377
        14    2771108    2973580     984024          202472
        15    2973580    3259862     984024          286282

To distribute the number of rows uniformly across partitions I have to use min(id) and max(id) as ranges... As you can see number of rows is uniform however range not...

Which way is better?

[marist89]

Are you planning on doing a lot of partition scans?

[Sweetie]

Hi

Most of the time this is OLTP so many index scans (so small partition scan I guess?). However there are also batch jobs

[slimdave]

So why do you want to use partitioning? ie. What problem are you trying to solve?

[Sweetie]

Hi

We have heavy contention problems, buffer busy waits, cache buffer chains latches. We have rebuilt the tables and indexes and increased the number of freelists and initrans with no improvement!

[slimdave]

Have you read Metalink note 34405.1 for further information on the cause?

[Sweetie]

Hi

I had a look that note a few weeks back when we were trying to fix these contentions. I followed what note suggested, isolating the objects and increased freelists, increased pctfree, increased initrans but with not much improvement. Basically we have problems with 3 tables which are accesed by 800 users concurrently

[slimdave]

did you isolate the cause of the waits, through the methodology suggested by the note? What was the predominant reason code? What is your current value of freelists?