7 Replies Latest reply on Jan 12, 2012 1:22 AM by 903271

    defineBytes and defineChars buffer allocation => OutOfMemoryError

    903271

      Our data processing application (Java with straight JDBC) works fine with MSSQL but then results in OutOfMemoryError with Oracle. This was very surprising to me, since the data set was fairly small (10,000 events) and the allocated 1GB of heap should have been plenty enough.

      After analyzing the memory dump with a profiler I discoverd that 5 instances of oracle.jdbc.driver.T4CStatement use a total of 800MB in a char[] field called defineChars.

      I googled the name of the field and ran across this Oracle presentation:

      http://www.oracle.com/in/javaonedevelop/adv-java-prog-techqs-with-odb-400715-en-in.pdf

      On page 15 it mentions defineChars and defineBytes and states that these fields are pre-allocated to the maximum theoretical size of the result set computed as fetchSize * maxPossibleSizeOfEachRow. This might seem logical and harmless, but when you consider that in practice the average size of a NVARCHAR2 column can be much smaller than the max size specified in the DDL, you realize that this strategy leads to a huge waste of memory. As an example, some of our columns are declared as NVARCHAR(2000) but do not exceed 100 characters on average. This means that the driver pre-allocates 20 times more memory than actually needed! Our application cannot afford that kind of waste.

      I mean, just consider what would happen if the database engine used the same naive buffer management when allocating space in the database blocks. The "VAR" in NVARCHAR2 is there for a reason.

      Anyway, is anybody aware of some configuration or workaround to disable this "feature"? It was probably introduced in the 11g driver since we haven't had such issues with the 10g driver before the upgrade.

      Thank you,

      Bogdan Calmac

      PS: We use version 11.2.0.3 of the ojdbc6 thin driver.

        • 1. Re: defineBytes and defineChars buffer allocation => OutOfMemoryError
          jschellSomeoneStoleMyAlias
          Seems like there was a very long thread that discusses something like that.

          I cannot recall if it discussed a solution or not.
          • 2. Re: defineBytes and defineChars buffer allocation => OutOfMemoryError
            903271
            I searched the JDBC forum using various terms and I couldn't find the thread you referred to. Would you be so kind to point me to it?
            • 3. Re: defineBytes and defineChars buffer allocation => OutOfMemoryError
              Jean De Lavarene-Oracle
              You can use the Oracle specific JDBC method defineColumnType to override the size of a particular column. The size provided through this method will be used by the driver to allocate the buffer instead of the size from the resultset description.
              Regards,
              Jean
              • 4. Re: defineBytes and defineChars buffer allocation => OutOfMemoryError
                'some of our columns are declared as NVARCHAR(2000) but do not exceed 100 characters on average'.

                So what would you like Oracle to do when it suddenly discovers that a column does have 2000 characters? Do a dynamic allocation?

                That 'feature', as you refer to it, has been around since the beginning; it wasn't suddenly added in 11g.

                Oracle uses several layers of buffering and caching and uses various parameters to determine the size of each layer.

                The 'fetch size' determines how many rows will be retrieved with each round trip to the database and the size of each row is computed based on how much data each column might consume. The product of the two determines the size of the buffer needed to hold a set of rows.

                You can control the fetch size (set it lower to save memory), you can control which fields are actually retrieved (don't retrieve ones that aren't needed for a particular use) and, as Jean pointed out, you can set the max size for any column to restrict the values Oracle uses for a column.

                So there is nothing that needs to be disabled; you are in charge. The storage allocation is performed statically when you define the objects (connections, statements, etc) involved. If the allocations were done dynamically there would be a tremendous performance penalty to pay.

                If you use an OracleResultSet you can avoid doing Oracle to Java conversions of all of the data which can dramatically speed things up if you retrieve a lot of columns but don't access them all.
                • 5. Re: defineBytes and defineChars buffer allocation => OutOfMemoryError
                  903271
                  @Jean de Lavarene

                  I can't safely use defineColumnType() because I don't know the actual size ahead of time. What if the value I set is slightly lower?

                  @rp0428

                  Yes you are right. It's the same in oracle 10.

                  "+If the allocations were done dynamically there would be a tremendous performance penalty to pay.+" Sorry, I don't buy this. I don't want to go into a detailed design of the buffering strategy here, but just consider that instead of a huge pre-allocated buffer you could have a pool of (let's say) 10MB buffer chunks which are allocated once and assigned to result sets as needed. Yes, you would need some indirection to access individual values, but it would be hardly noticeable in practice. I haven't yet seen the client side to be a bottleneck in DB applications. As a comparison, the performance of our application on MSSQL is just fine without this "pre-allocation of max theoretical size" strategy.





                  A paper that clears the issue: http://www.oracle.com/technetwork/database/enterprise-edition/memory.pdf

                  After stating that:

                  +As a Java developer, the reader can undoubtedly think of other schemes for allocating and using memory. Be assured that the Oracle JDBC development team can too. Many other schemes were tested before settling on this one. Without going into detail this scheme was chosen with good reason. This choice is integral to the performance of the drivers.+

                  the rest of the paper (ironically) goes at great length explaining the various workarounds added in later releases to fix this design mistake. None of them touches the core of the problem though, which is the allocation of way more memory than needed in practice.

                  In closing, I'll quote another sharp piece of reasoning from the paper:

                  +In practice, problems only appear in systems that have hundreds or even thousands of Statement objects. A very large system might have a couple of hundred connections open simultaneously. Each connection might have one or two statements open at once. Such a very large system would run on a machine with very large physical memory. With reasonable effort even such a very large system with a few hundred open statements is unlikely to have serious memory issues. Yes, the drivers would be using a lot of memory, but that's what memory is for, to be used. So in practice even very large systems can avoid memory problems.+

                  Excellent explanation. What could be wrong with allocating 20x the memory you need? What's the big difference between 1G and 20G? It's just memory, folks. The application certainly wouldn't need the remaining 19G for things like caching and such.
                  • 6. Re: defineBytes and defineChars buffer allocation => OutOfMemoryError
                    jschellSomeoneStoleMyAlias
                    Bogdan Calmac wrote:
                    Excellent explanation. What could be wrong with allocating 20x the memory you need? What's the big difference between 1G and 20G? It's just memory, folks. The application certainly wouldn't need the remaining 19G for things like caching and such.
                    And yet many thousands of applications do in fact manage to use Oracle successfully.
                    • 7. Re: defineBytes and defineChars buffer allocation => OutOfMemoryError
                      903271
                      Sure, I don't see a contradiction. We engineers are good at working around deficiencies of the lower layers. It's not a perfect world.