Will a BLOB Eat All My Memory?
In this article, take a look at what happens when you keep growing a BLOB.
Join the DZone community and get the full member experience.Join For Free
Probably the most common usage for large objects (CLOBs and BLOBs) is to store them in a database table. In this circumstance, it feels intuitive that you won't have a lot of concerns about memory, because the database will simply store those objects in datafiles like it would any other kind of data.
But BLOBs and CLOBs can also be declared as local variables in a PL/SQL block. We typically expect local variables to be housed within the memory for that session (the PGA). There are explicit calls in the DBMS_LOB package to create a temporary large object, but what if we do use that API? What if we just start bludgeoning a local variable with more and more data? Is this a threat to the session memory and potentially the database server?
Time for a test!
To see what happens when we keep growing a BLOB, I'm going to monitor two attributes for a session:
- How much PGA is it consuming
- Whether it is using any temporary tablespace allocation
To keep my code nice and compact, I'll determine in advance the statistic number for the PGA memory I want to monitor.
Before doing anything, I'll check the amount of PGA my session has used in its lifetime.
Now I'll start with a simple routine that adds 10kilobytes to an existing 1kilobyte BLOB. We wouldn't expect such a variable to cause any problems because it is less than what would easily store in a VARCHAR2 anyway.
Notice also that the query portion on line 16 to report any temporary space has not returned any results. So everything here is being done in PGA. Let's ramp things up a little. Now I'll push to 100kB, which exceeds what could be stored in a standard local variable.
Our PGA has jumped a little, and there is still no temporary space usage. So let's push onwards and see if I can blow up my session PGA. Now I'll push 10 mega-bytes into the BLOB.
You can see what the database is doing here. Even though the BLOB is declared as a local variable, it is being handled like a true temporary BLOB. We store it in PGA if it fits within the overall database's management of the PGA, but once it gets large, we'll utilize temporary storage to ensure our session does not chew up excessive memory. This allows me to write as much data as I like into the variable and not harm the PGA. Here's the BLOB pushed out to 200 megabytes, but still only consuming just over 3 megabytes of PGA.
Published at DZone with permission of Connor McDonald, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.