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?

closed as too broad by hot2use, Colin 't Hart, mustaccio, Marco, Max Vernon Jul 9 '18 at 18:23

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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.