Tables with Memoptimize for Write (fast ingest)

Hi There,

this is the second part of this post: http://mustafakalayci.me/2022/02/15/tables-with-memoptimize-for-read/

Oracle adda new feature called memoptimized tables for mostly devices like IoTs. Fast lookup and fast ingest tables. I have already mentioned about fast lookup (memoptimize for read) tables in my previous post and now I want to write about Fast Ingest tables. as Fast ingest tables, those tables are optimized for inserting new data. Especially small devices (like any IoTs) are tend to be send data more than processing or querying data and we have billions of them.

for the Fast Lookup tables (in my previous post), plsql is not an option but fortunately we can use fast ingest tables in plsql. My tests will be based on plsql codes. as fast lookup tables, you will set table as fast ingest table (memoptimize for write) and while inserting you need to use a special hint “MEMOPTIMIZE_WRITE”. this way Oracle will understand this is a fast ingest table and inserts will be completed accordingly.

How it is work? your insert statements (either row by row or bulk) will be stacked in LARGE_POOL (a memory component in database) and after passing a threshold, data will be inserted (as if a bulk insert). this provides significant performance increment but as always, nothing is perfect and there is always a “but”. So, this will increase your write speed “but” your data will not be immediate anymore! you might not see your data just after inserting and committing it because it will be cached in large pool and after a time will be inserted to the table. Secondly, you might loose some data! this is a big “but”! since data is in large pool for a certain amount of time, any thing that can go wrong like an instance crash, might cause loose some of the data. you must be careful while using this feature.

So let’s test and see the difference. To demonstrate this, I will shutdown and startup my database to remove everything in the memory and then connect as “mustafa” user.

ok, before start my test, I would like to check my large pool size first. large_pool parameter is 0 as usual because I am using ASMM (sga_target is set) so I need to check dynamic components view:

sizes are in megabytes. as you see I have 96MB assigned large pool.

Let’s create two tables, one for memoptimize for write (fast ingest) and one for standard heap table.

now, I will do inserts row by rows and commit at the end:

as you see I use MEMOPTIMIZE_WRITE hint in the first plsql code for test_fast_ingest table. of course timing takes our attention:

fast ingest : 25.44 seconds
standard heap: 25.54 seconds

huh! is there a problem? well actually not, let’s check large pool size again:

as you see, large pool has reached it’s limits. it is now 768MB. as I said, memoptimize for write uses large pool, since it was not enough to do the job we waited larges pool’s growth operation. let’s run the same script again:

here it is. fast ingest table is more than two times faster than standard heap table. this is a major performance increment. Let’s try commit row by row:

this is a hard job to do and even for this job, fast ingest table is doing a great job.  for row by row commit, fast ingest time increased 2.25 times (approximately) and heap table time increased 2.6 times. if you have many devices sending many insert statements then fast ingest table will be able to handle it. even so, increasing large pool size would increase the performance.

I have already mentioned about some cons like inserts are not immediate (even if they are committed) but also there is one more cons! lets truncate the table and make inserts. please remember that ID column is PRIMARY KEY.

as you see, I inserted three rows and they all have same ID, so there must be a unique constraint violations right? well, in my tests it didn’t probably because data is not inserted yet. it is gathered in large pool and then will be inserted but it is not my session doing the inserts so I won’t get any unique constraint violation for this action. you must be careful while using this.

fast ingest tables have more satisfying results than fast lookup tables for me. we can increase the performance at least 2 times here! And it works with bulk inserts too:

created a base table (tmp_base) with 1M rows and use it to insert the data. as a result, fast ingest table is 4.2 times faster than heap table.

I expect memoptimize tables (both read and write) will be developed much more and become more popular. Just careful using fast ingest because it has significant cons. agan; you might loose data even if you committed them!

wish you all healthy days.

Leave a Reply

Your email address will not be published. Required fields are marked *