This is really a design question, before we start coding ...

The application is mainly OLTP, with a bias towards data entry. There will be a month end processing cycle that processes the data for a given month.

We have a business requirement to scan documents at a rate of around 30,000 per hour. Each scan will result in two images, to be stored for a month within Oracle 12.2 Enterprise Edition. We've just been told that the images are between 25-40K in size, so stored in external segments. 30,000 images gives an insert rate of around 17 per second. The images won't be updated, but may be referenced at random by keyers. Front end application is Apex/PLSQL. At some point, without really knowing the requirements, someone has decided that a 3 Node RAC cluster is to be the platform ( based upon Solaris Sparc LDOM's ). We tend to work off a standard 8k blocksize.

Wondering if you can point me in the direction of any documents that might help with considerations for a high insert performance, and perhaps anything that provides insight into corresponding RAC considerations ( conceptually I'm a bit concerned about the impact on the cache ). And anything else you may care to highlight ! I can find documentation on LOB's and RAC in their own right, but struggling a bit to find much on design considerations and sizing for throughput. There is a plan to start some proof of concept type testing next month, and just trying to pre-empt some of the likely issues.

Apologies if this is thw 'wrong' type of question, and if it is, feel free to delete. I'm just trying to put a bit of thought in at the design stage to [hopefully] save issues later on.

Thanks

Rob

SQL> create table lob_table
  2    (id        number,
  3     file_name varchar2(45),
  4     image     blob,
  5     tstamp    date)
  6  lob ( image ) store as securefile;

Table created.

Elapsed: 00:00:00.00
SQL>
SQL> create sequence lob_table_seq cache 1000;

Sequence created.

Elapsed: 00:00:00.00
SQL>
SQL>
SQL> create or replace procedure load_tester     as
  2      l_blob       blob;
  3      l_file_name  lob_table.file_name%type  := 'xmas.jpg';   -- 45k file
  4      l_bfile      bfile := bfilename('TEMP', l_file_name);
  5  begin
  6      insert into lob_table (id, file_name, image, tstamp)
  7          values (lob_table_seq.nextval, l_file_name, empty_blob(), sysdate)
  8          returning image into l_blob;
  9     dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
 10     dbms_lob.open(l_blob, dbms_lob.lob_readwrite);
 11     dbms_lob.loadfromfile(
 12           dest_lob => l_blob
 13         , src_lob  => l_bfile
 14         , amount   => dbms_lob.getlength(l_bfile));
 15     dbms_lob.close(l_blob);
 16     dbms_lob.close(l_bfile);
 17     commit;
 18  end;
 19  /

Procedure created.

Elapsed: 00:00:00.02
SQL>
SQL> set timing on
SQL> begin
  2      for i in 1 .. 20000
  3      loop
  4          load_tester;
  5      end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.91