2

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.

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

Any comments on this?

  • check PCTFREE and INITRANS(on table an PK index) parameters you want each line to reside in different block. – ibre5041 Jul 5 '18 at 15:04
  • 1
    You said "one row could have 1000 updates per second". Something tells me you aren't going to be able sustain calling commit "1000 times in 1 second" for very long. It is a speed of light thing. I'd love to see a system that proves me wrong. – Michael Kutz Jul 5 '18 at 15:25
  • @ibre5041, I would set PCTFREE to 99 and INITRANS to 255 I guess then. – FORTRAN Jul 6 '18 at 5:39
  • 1
    Your small table will all be in memory anyway, so an in-memory feature won't help; it would address an I/O bottleneck if there were one, but yours is in concurrent access. Consider decoupling clients from the database by letting them queue updates (in AQ or application) and having a single thread perform DML and commit as (in)frequently as feasible. – mustaccio Jul 6 '18 at 14:00
  • 1
    @FORTRAN - it's impossible to say what a better design might be, because we don't know the business logic. All we know is some abstractly named elements and your assertion that there will be a lot of updates. All I can say at this point is that it is suggestive of a flawed design. – EdStevens Jul 6 '18 at 16:10
0

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

  • 1
    this is a test with single session. maybe the results were different if you had 1000 connections and 2 node RAC cluster. – ibre5041 Jul 18 '18 at 14:01

Not the answer you're looking for? Browse other questions tagged or ask your own question.