Best practice to persist a RPC request content

Question

We have a SOA application which provides hundreds of rpc services. For some of those rpc services, we want to persist the incoming request content to database. as you can see, the structure and request arguments are different from services to services, we can not design a standard structure table to contain the entire request body.

Oracle 11 is the only persist store we can use and we can not use nosql/distributed cache products. we have been considered using json and clob fields to achieve this requirement, but due to performance reason, our DBA suggests that use clob is not a good idea.

i am wondering is there anyone have the similar situation and is there any best practice to do this?

Answer 1

We have applications which persist the message going across the messaging oriented middleware (as I recall, on the order of 30 million messages a month requiring about 1 TB tablespace to manage this with deletions of old messages > 42 days old [it would have been much easier if they used partitioning and truncate to manage space]). It simply does an insert with the message going into a CLOB and recording other relevant message metadata in columns of appropriate datatype and size [I think they used to tables a message metadata table and a message payload table; they query the message metadata table for basic metrics on how their service is being used]. From my notes to the client:

LOB Storage Basics: 1. The LOB segment is bigger than just the sum of the bytes of the messages 2. Each LOB can be stored in-line or out-of-line. In-line means that LOBs up to 3964 bytes are stored in the same block of the table (segment) with the rest of the data for that row. LOBs bigger than 3964 bytes get stored out-of-line in a LOB segment. Out-of-line LOBs are completely stored in a LOB segment with only a lob locators stored in the block with the rest of the row.

The chunk size determines how the data gets stored out-of-line; this is the LOB segment space allocation unit for each LOB regardless of size. So for each LOB, you get a multiple of the chunk size needed for that LOB. So this leads to some amount of wastage for most LOBs.

LOB Free Space Usage: RETENTION for lob space management should allow the entire lob segment to be used over time [I believe the behavior is: when a block in a lob segment is freed by delete, it goes on the free list after the UNDO_RETENTION period (15 min for [instance A])]; this is a better implementation as it does not set aside a fixed percent of space to be used like PCTVERSION will.

Additionally, Oracle’s consistent read (CR) mechanism for LOB segments uses the LOB segment itself (rather than the UNDO tablespace as most CR’s do). So, this too results in the LOB segment requiring some extra space to support CR.

Optimization scenarios include: 1. In-line storage can improve performance for the following reasons: a. LOB data smaller than 3964 bytes is cached in the buffer cache. b. LOB data larger than 3964 bytes is accessed via direct path reads/writes (i.e. faster I/O) 2. Chunk-size can be made smaller to have less space wastage, but there are some caveats: a. The tablespace the CLOB is currently in has an 8K DB block size and chunk size is a multiple of DB block size so it can only be made smaller in a tablespace with smaller block size. b. A smaller chunk size means more work for reading and writing and you pay a performance penalty on the larger size entries. Median message size is right at 3900 characters, to factor in the multi-byte character set, the middle value is about 8K chars. Therefore, going to a smaller chunk will mean most data will require at least 2 accesses [actual time not determined]. A new tablespace would be populated by moving the CLOB to it [requiring down-time], accomplishing the same as the shrink, and would let us see the impact. 3. LOB Reorganization: not recommended at this time since purged space from deleted rows is being re-used.

More info on LOBs can be found in the LOB Performance Guidelines White Paper.

Other Options: 1. Use 11g LOB datatype SecureFile In 11g the LOB data type has been redesigned completely. To use SecureFile LOB storage, it is done declaratively in the storage clause of the table create statement. 2. look into Advanced Compression for reducing space requirements 3. look into Partitioning for improved manageability