Recommended settings for a small table with high update rate [closed]

Question

I have a small table which can be explained as follows:

create table mytab (
    primary_key_column number, 
    a number, 
    b number, 
    c number, 
    d number, 
    e number, 
    f number
);

It will have very few rows (not more than 256 records) but they will be updated at a high rate (for example one row could have up to 1000 updates per second).

What recommended settings should I use for the table to be able to handle this load and avoid update contention? Or other suggestions for doing this?

I will be using Oracle 12c or newer.

Edit

Concerning the high nnumber of updates, as was asked in the comments. With pctfree and initrans I can perhaps get each row in a separate block, but there will still be a lot of updates for a given key. So any suggestions on how to handle that? I see only three ways forward on this.

1) don't update but write the increments to a separate table and then have a job summing them and do the update regularly. This will get me out of the update contention, but I will be doing a lot of inserts instead

2) change the primary key in some way, like

create table mytab(
  primary_key_column number,
  sub_key number,
  a number,
  ...

and then let updates choose a random "sub_key" for the given primary_key. Sub_keys could be like the numbers 0-99 to give each row only 1% of the updates.

Given that all values a-f are zero at start, the total values can then be gotten by summing over the primary key. It's ugly, but it could work.

3. Might the In-memory feature solve the problem?

Any comments on this?

Answer 1

Oracle 12c: Simple test case: one session inserting 256 rows:

MARK.STEWART@dev01> create table mytab (
    primary_key_column number,
    a number,
    b number,
    c number,
    d number,
    e number,
    f number
);

Table created.

MARK.STEWART@dev01>   insert into mytab (select rownum, OBJECT_ID, DATA_OBJECT_ID, NAMESPACE, DBMS_RANDOM.VALUE(1, 256), DBMS_RANDOM.VALUE(1, 256),  DBMS_RANDOM.VALUE(1, 256) from dba_objects  where rownum < 257);

256 rows created.

Elapsed: 00:00:00.77

Updated a row 1,000 times and committed after each update, took only 0.29 of a seconds:

MARK.STEWART@dev01> begin
   2      for loop_var in 1 .. 1000 loop
   3              execute immediate 'update  mytab set a= 1 where PRIMARY_KEY_COLUMN = 6';
   4              execute immediate 'commit';
   5      end loop;
   6  end;
   7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.29
MARK.STEWART@dev01>

With pctfree 99 initrans 20 it took almost twice as long (probably due to having high value for initrans): 00:00:00.65