3

In our project, we have 300 fields/columns with 15 tables on that around 15 columns are with varchar2(4000) as data type.

As far as I known,

On disk, a VARCHAR2 only consumes the number of bytes required to store the actual data. A 4-byte string in a VARCHAR2(4000) column will only consume 4 bytes of space on disk.

For retrieving, we are using oracle VIEW from the database. But we are getting heap memory issue frequently. If more number of requests are coming at the same time. Is there any way to solve this problem ? we are using web logic server.

Below links I referred so far:

Impact of defining VARCHAR2 column with greater length https://community.oracle.com/thread/776726?start=0

Heap Dump

The maximum amount of data, in bytes and rounded down to the nearest power of 2, mapped into the JVM's address space per paging store file. Applies only when a native <code>wlfileio</code> library is loaded.</p> <p>A window buffer does not consume Java heap memory, but does consume off-heap (native) memory. If the paging store is unable to allocate the requested buffer size, it allocates smaller and smaller buffers until it reaches <code>PagingMinWindowBufferSize</code>, and then fails if it cannot honor <code>PagingMinWindowBufferSize</code>.</p> <p>Oracle recommends setting the max window buffer size to more than double the size of the largest write (multiple concurrently updated records may be combined into a single write), and greater than or equal to the file size, unless there are other constraints. 32-bit JVMs may impose a total limit of between 2 and 4GB for combined Java heap plus off-heap (native) memory usage.</p> <ul> <li>See the JMS server runtime MBean attribute <code>PagingAllocatedWindowBufferBytes</code> to find out the actual allocated Window Buffer Size.

user1295079
  • 53
  • 6
  • 1
    heap...sounds like a mid tier issue - exactly where are you seeing the error, and what is the exact error. – thatjeffsmith Jan 03 '18 at 15:26
  • 3
    Something I have seen frequently - people who are more comfortable with non-database languages, like Java, will grab the data from db tables into arrays and process them in the application. As Justin Cave explained in the thread you linked, that's an issue, if the language requires static (ahead-of-time) memory allocation. This is just one of the many reasons to write your applications so that most of the processing - as much as possible - is done in the db itself, not in the application. Which, sure, means someone needs to learn SQL really well. –  Jan 03 '18 at 16:04
  • I have added additional information. – user1295079 Jan 03 '18 at 17:16
  • 1
    Check if you can implement Pagination or Streaming in your application instead of keeping complete result set in heap – Saravana Jan 03 '18 at 17:33
  • @Saravana Unfortunately, I can't implement that at the moment. Just wanted to know how do you find that keeping resultset in heap ? – user1295079 Jan 03 '18 at 17:49
  • @thatjeffsmith We are getting this issue with web logic server. We were seeing the error in heap dump. The heap memory was keep on increasing if more number of requests are coming and after certain time the application crashed. – user1295079 Jan 04 '18 at 09:26
  • @user1295079 make sure your apps aren't doing SELECT * for everything. Just grab the data you NEED. And use WHERE clauses if you can't page the results. And also, i'd change your data model. It's likely those 15 tables could be split many times, 300 columns in a single table is a big RED FLAG kind of warning. – thatjeffsmith Apr 04 '18 at 12:51

0 Answers0