Asktom Oracle: Partition table and index .

You Asked

Hello

I am currently working with Peoplesoft CRM v8

One of consultants decided to partitioned the tables using hash partitioning, he also 
partitioned the indexes locally. Obviously he just showed me how clueless he is making 
the indexes local to the tables and time has shown I am right, we had to re-partition all 
indexes into global.

I asked him a couple of days ago what is the technical reason of using hash partitioning 
(never worked well for me in OLTP). He said

1. We increase availability.
-- I would agree if he stored the partitions in different tablespaces however as clueless 
he is he stored everything in same tablespace. So availability is not applicable here.

2. Administration facilities.
-- Well to certain degree I would agree since dealing with a 50 million table is harder 
than dealing with 64 small partitions. Again he showed me how clueless by creating 64 
partitions in an OLTP environment. (I would not create more than 8)

3. Increase performance and reduces contention.
-- I never believed Hash partitioning increases performance and the reality and time has 
shown I am right.
I do agree about reducing contention.. BUT.... comment at the end


I proceeded to making a benchmark comparison, we have two identical database one hash 
partitioned and the other not. I took two statspack snapshots at each session in each 
instance to get the session statistics. Ran a identical SQL statements which shares the 
same execution plan in each sessions/instance. By the way I was the only user in the 
whole server and each database.

Here are the relevant statistics,

Hash Partitioned results:
sesstats
-------------------------------------------------------------------
consistent gets                              601,857          963.0
consistent gets - examination                193,513          309.6
cpu used by this session                       8,593           13.8
cpu used when call started                     8,593           13.8
parse count (hard)                                 1            0.0
parse count (total)                               13            0.0
parse time cpu                                     2            0.0
parse time elapsed                                 2            0.0
physical reads                               492,418          787.9
pinned buffers inspected                          73            0.1
prefetched blocks                                 10            0.0
recursive calls                                  258            0.4
redo entries                                      18            0.0
redo size                                      8,960           14.3
rows fetched via callback                     64,503          103.2
session cursor cache count                         7            0.0
session cursor cache hits                          2            0.0
session logical reads                        601,877          963.0
session pga memory                           379,608          607.4
session pga memory max                       379,608          607.4
session uga memory                             4,264            6.8
session uga memory max                       104,616          167.4
shared hash latch upgrades - no w             84,288          134.9
sorts (memory)                                    15            0.0
sorts (rows)                                      36            0.1
table fetch by rowid                       5,142,160        8,227.5
table scan blocks gotten                         170            0.3
table scan rows gotten                         5,024            8.0
table scans (short tables)                        16            0.0
user calls             

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read                           493,086         504    84.65
CPU time                                                           89    14.89
control file parallel write                           203           2      .28
db file scattered read                                 23           0      .05
process startup                                         1           0      .05

No-Hash Partitioned results:
sesstats
-------------------------------------------------------------------
consistent gets                              593,514          948.1
consistent gets - examination                192,675          307.8
cpu used by this session                       6,766           10.8
cpu used when call started                     6,766           10.8
parse count (hard)                                 3            0.0
parse count (total)                               14            0.0
parse time cpu                                     1            0.0
parse time elapsed                                 3            0.0
physical reads                               422,118          674.3
recursive calls                                  265            0.4
recursive cpu usage                                1            0.0
redo entries                                      18            0.0
redo size                                      8,392           13.4
rows fetched via callback                     64,224          102.6
session logical reads                        593,533          948.1
session pga memory                           248,352          396.7
session pga memory max                       248,352          396.7
session uga memory                             2,024            3.2
session uga memory max                        98,448          157.3
shared hash latch upgrades - no w             25,609           40.9
sorts (memory)                                    15            0.0
sorts (rows)                                      36            0.1
table fetch by rowid                       5,141,482        8,213.2
table scan blocks gotten                          16            0.0
table scan rows gotten                           236            0.4
table scans (short tables)                        16            0.0
user calls                                        25            0.0

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file sequential read                           422,162         346    83.06
CPU time                                                           70    16.81
control file parallel write                           204           0      .10
log file parallel write                                26           0      .02
control file sequential read                          177           0      .01


From all results no-hash partitioned is better

My question is:

Is it worthy lose performance just gain reduction in contention...?

IMHO Hash partition is a no-no in OLTP, a way of suicide

and we said...

(Hash partitioned tables) PLUS (global range partitioned INDEXES) EQUALS (success in 
OLTP)


It is funny -- this is almost verbaitim the example I used in my new book "Effective 
Oracle By Design".  I was trying to drive home that

a) partitioning is NOT fast=true
b) you MUST understand the physics behind the data, whats happening.

Say you took a table T with columns ( ID primary key, CUST_ID, ... )

You hash partitioned into 64 partitions by ID.
You have a local index on CUST_ID.

MOST of your queries are "where cust_id = :x"

Guess what you just accomplished.

You accomplished the feat of increasing your IO by a factor of 64!!!  by 64 times!!

why?  well, we have 64 tiny little index segments to range scan -- your customer id could 
be in any, all or none of them.

Solution -- hash partition table, range partition index by cust_id -- now you will NOT 
have affected read performance at all (probably, it could be a tiny bit better with 
partitioning but nothing phenomenal) but you might find that you've reduced contention on 
modifications since you have N indexes and N table segments (and hence N freelists at 
least and so on)


If you have my new book -- you'll laugh at how closely your example above mirrors the one 
in the book, almost scary (but I only did 8 partitions, to show an 8 times increase in 
IO)