13 Replies Latest reply on Aug 16, 2010 11:36 AM by 791089

    ODBC Driver uses excessive memory

    791089
      Hi,

      I hope somebody can help. I have discovered what I think is possibly a bug with the Oracle ODBC driver.

      I parse and fetch all the rows from a select statment which returns around 220,000 rows. This works absolutely fine unless one of the fields I have selected returns the result of a user-defined function which has a return type of varchar2.

      eg:

      where "my_function" is declared thus:

      create or replace
      function my_function( phrase in varchar2 )
      RETURN VARCHAR2
      IS
      .....
      END;

      then if I run the following query via ODBC:

      select my_function( field_a )
      from my_table

      I find that the amount of memory used by my program absolutely balloons until it runs out of memory (hits the windows 2GB limit).

      My investigations have shown that this is because the ODBC driver is unable to determine the size of the data returned by the function (in this never more than 32 characters) and so appears to allocate enough space for the maximum length of a varchar2.

      I can prove this is the case because I do not see the same problems if I change nothing else in my program except the select statement to:

      select cast( my_function( field_a ) as varchar2(32) )
      from my_table

      I have tried reducing the size of the fetch buffer size in the ODBC driver configuration but this make no discernable difference.
      Is this a known issue? And if so, other than using the cast operator is there a work-around?

      Thanks

      Tony
        • 1. Re: ODBC Driver uses excessive memory
          No 4-digit version of the database of the database was mentioned,
          The ODBC driver is equally version less.
          No sign this works correctly in sqlplus was shown.
          The conclusion is it is the ODBC driver is just a wild stab in the dark, an assumption,
          and also no one can reproduce this problem, as they may be running a different version.

          Functions can return user-defined types. This includes bound varchar2 data.
          The investigations are also wrong, as they has provided incorrect conclusions.
          It is not ODBC which can't determine the length, it is is OCI.
          Run this through sqplus and you won't get columns of 32 characters.
          And this
          can prove this is the case because I do not see the same problems if I change nothing else in my program except the select statement to:
          
          select cast( my_function( field_a ) as varchar2(32) )
          from my_table;
          proves it is OCi, NOT Odbc.

          And obviously fetching 220,000 rows over an ODBC driver is just a sign of nearing insanity,

          -------------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: ODBC Driver uses excessive memory
            Billy~Verreynne
            user6560781 wrote:

            My investigations have shown that this is because the ODBC driver is unable to determine the size of the data returned by the function (in this never more than 32 characters) and so appears to allocate enough space for the maximum length of a varchar2.

            I can prove this is the case because I do not see the same problems if I change nothing else in my program except the select statement to:
            Tony, this does not imply that the ODBC layer is at fault. It could be due to how your application is using ODBC that causes ODBC to behave abnormally in this case. It could be an issue between a higher level application interface (e.g. set of db access classes or abstracted db interface running over ODBC). It could be an issue between OCI and ODBC.

            One way to confirm or discard ODBC as the problem, is to debug the ODBC driver itself. Two basic options comes to mind - using a debug tool for ODBC (used to be part of ODBC DDK) that sits between the client app and the ODBC driver and displays every single call made to the driver and response from the driver. Or enabling the detailed debug tracing for the driver (where it spools this same call-and-response data to a trace file).

            This assumes that you are familiar with ODBC API.

            Alternatively, you need to simplify the test case to make it easier to isolate the problem.

            What is interesting is the size of the memory leak. A single cursor row fetch via the ODBC and OCI layers should not be causing 2GB of memory to be allocated. So does the ODBC layer attempt to implement a scrollable client data set? (something that Borland's BDE layer used to support)
            1 person found this helpful
            • 3. Re: ODBC Driver uses excessive memory
              791089
              Wow! You could have just asked for the version numbers.......

              These are:

              Database: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
              ODBC: 10.02.00.01
              No sign this works correctly in sqlplus was shown.
              Not only does the select work in sqlplus but the whole program works using Data Direct ODBC drivers in place of the Oracle ones.
              The conclusion is it is the ODBC driver is just a wild stab in the dark, an assumption,
              ..... It is not ODBC which can't determine the length, it is is OCI.
              As part of my investigations I tried the following;

              create table TMP
              as
              select my_function( field_a ) as field_a
              from my_table

              When I look at the definition of the table in SqlDeveloper it has created a column 4000 characters wide;

              CREATE TABLE "TONY"."TMP"
              (
              "FIELD_A" VARCHAR2(4000 BYTE)
              )

              If I modify my program to read from the TMP table I get the same memory problems.
              If I then modify the table thus;

              alter table TMP
              modify "FIELD_A" varchar2(32 byte)

              changing nothing else and then re-run my program the memory problems go away. This was how I realised to "cast" the output of the function as a work-around.

              Now I accept that you have a greater knowledge than me and I bow to that, really I do, but I write using standard ODBC functions, I connect to the database using an ODBC driver, I don't know whether or not ODBC driver is making internal calls to OCI - and nor should I need to know.

              If I connect using the Data Direct ODBC driver everything works fine. If I connect using Oracle's ODBC driver my program fails. You might have a more detailed understanding of what is happening under the hood - but to me it's a problem with Oracle's ODBC driver.

              I have tried reducing the Fetch Buffer size from 64000 in incremental steps to just 200 but this make no difference. I've also tried disabling LOB's and Result Sets but nothing seems to make any difference.
              Functions can return user-defined types. This includes bound varchar2 data.
              I'd be grateful if you could explain further, I had tried modifiying my function definition thus;

              create or replace
              function my_function( phrase in varchar2 )
              RETURN VARCHAR2*(128)*
              IS
              .....
              END;

              but that does not compile.

              I also tried making sure that the return statement in the function returned a locally declared variable thinking that Oracle might be able to infer the maximum length of data the function can return from the declared size of the variable but that didn't work either.
              And obviously fetching 220,000 rows over an ODBC driver is just a sign of nearing insanity
              Well, maybe. But here I am. I make no claim regarding my sanity.

              Is there a better generic solution available that will allow a C++ program to communicate with Oracle, SqlServer, Postgres, MySql, Access and Excel? If there is I'd love to use it.
              • 4. Re: ODBC Driver uses excessive memory
                791089
                Billy, thanks for your reply. Ever so friendly it was...... why, I didn't feel I had been savaged at all!
                this does not imply that the ODBC layer is at fault. It could be due to how your application is using ODBC that causes ODBC to behave abnormally in this case. It could be an issue between a higher level application interface (e.g. set of db access classes or abstracted db interface running over ODBC).
                Yes this is possible, although if this works OK the Data Direct ODBC Drivers for Oracle does this not exclude the likelihood that the problem lies in my classes?
                One way to confirm or discard ODBC as the problem, is to debug the ODBC driver itself.
                This assumes that you are familiar with ODBC API.
                I must confess that I'm not familiar with the ODBC API. To be honest I'd really rather not have to go to such extreme lengths; if it's a known problem I'd prefer to work around it. If it's not a known problem I'd like to work out if it really is a bug in the driver and draw Oracle's attention to it (and then work around it).
                What is interesting is the size of the memory leak. A single cursor row fetch via the ODBC and OCI layers should not be causing 2GB of memory to be allocated. So does the ODBC layer attempt to implement a scrollable client data set?
                I think (unless my math has let me down badly) that the Oracle ODBC driver simply allocates 220,000 lots of 4000 bytes, thats 880,000,000 bytes per column (or 839 meg)...... you don't need many of those to go over the 2gig limit.

                When I first found the problem my program attemtped to retrieve values from several columns based on similar user defined functions. In subsequent testing I reduced this to just one column but "broke" the program each time it was obvious the memory was ballooning rather than let it finish.

                I've run the program through again now to see how much memory it uses with only one column based on a function. It's memory usage is 947,000K .... which bearing in mind I have a couple of other (normal) columns selected suggests my maths is about right.

                As soon as the cursor is closed the memory is freed, so it does rather seem like it has something to do with sets. However the tickbox for "Enable result sets" is unchecked in the driver.

                I'll have a look into this. Certainly as my program talks to databases from many different vendors it has to use the lowest common denominator in terms of the set of functionality. I don't require scrollable data sets as most databases can't support them.
                Alternatively, you need to simplify the test case to make it easier to isolate the problem.
                To be honest I'm not sure what else I can do to simplify (see my last post). Simply changing the definition of the column in my TMP table caused the problem to go away.

                It does rather look as it the Oracle ODBC driver is preserving a data set and has been optimised for speed rather than memory use (i.e. does not bother to "trim" the allocated memory). The question is why? And how can I stop it?
                • 5. Re: ODBC Driver uses excessive memory
                  Billy~Verreynne
                  Unhandled Exception wrote:
                  Billy, thanks for your reply. Ever so friendly it was...... why, I didn't feel I had been savaged at all!
                  Er.. I will try better this time around. ;-)
                  Yes this is possible, although if this works OK the Data Direct ODBC Drivers for Oracle does this not exclude the likelihood that the problem lies in my classes?
                  One means to test is using another ODBC client instead of your application with the same SQL. There's an isql program (command line) that ships with some ODBC installations which can be used. Alternatively, you can even use Excel or another ODBC able application.
                  I must confess that I'm not familiar with the ODBC API. To be honest I'd really rather not have to go to such extreme lengths; if it's a known problem I'd prefer to work around it. If it's not a known problem I'd like to work out if it really is a bug in the driver and draw Oracle's attention to it (and then work around it).
                  Well, this forum deals with Oracle database issue - not client driver issues. So its difficult to say whether this is a known problem or not. It certainly sounds unusual to me, but then I ceased using ODBC almost 10 years ago.
                  I think (unless my math has let me down badly) that the Oracle ODBC driver simply allocates 220,000 lots of 4000 bytes, thats 880,000,000 bytes per column (or 839 meg)...... you don't need many of those to go over the 2gig limit.
                  That's not how the OCI (Oracle Call Interface) works. It does not grab memory to store each and every row fetched. Neither does ODBC from what I recall of the ODBC API. Yes, both will allocate a buffer area for processing the cursor. But the application itself will be making the fetch calls to fetch more rows via ODBC (which in turns calls OCI).

                  So the memory issue is typically in the application. For example, the app has a grid control object and as the rows are fetched, it is added to this grid. Obviously, the grid class needs to allocate more memory for each cell and row that it extends the grid by. Neither OCI or ODBC caches the rows fetched.

                  Unless of course the ODBC (or app layer) creates a scrollable cursor set by caching the rows. By default, Oracle cursors is one way fetch traffic. You cannot reverse fetch, or restart the fetch at a specific position. It is very much sequential with no reverse gear. So you get "+clever+" client side interfaces that deems it a good idea to cache the data and make such a scrollable data set available to the app. But this is expensive ito of memory.

                  You also need to question why you are fetching such a large number of rows. I've always maintained that more than a 100 or so rows being fetched by a client application is useless - as the end-user is not capable of making effective business decisions based on data (>100 rows), and needs information (<100 rows) instead.
                  It does rather look as it the Oracle ODBC driver is preserving a data set and has been optimised for speed rather than memory use (i.e. does not bother to "trim" the allocated memory). The question is why? And how can I stop it?
                  It may look like it, but it does not make sense - so I would want evidence to support that and not inference/speculation.
                  1 person found this helpful
                  • 6. Re: ODBC Driver uses excessive memory
                    791089
                    Billy, thanks again for your reply. You said a lot of insightful stuff and I agree with almost all that you've said.
                    It is very much sequential with no reverse gear. So you get "clever" client side interfaces that deems it a good idea to cache the data and make such a scrollable data set available to the app. But this is expensive ito of memory.
                    I agree with most of what you've said and I will look carefully into my application to see if somehow it is instructing ODBC to matintain a scrollable data set. I don't require this behaviour. I'll let you know what I find.
                    One means to test is using another ODBC client instead of your application with the same SQL
                    As I mentioned previously I'm not having the same problems when I use the Data Direct ODBC driver for Oracle (same program, same query, no changes other than choice of ODBC driver). I'm not really sure how else I can show that whatever problem I'm having it is specific to the Oracle ODBC drivers.

                    If my app is telling the ODBC driver to maintain a scrollable set then Data Direct is doing so with a much more efficient memory usage alogorithm.
                    You also need to question why you are fetching such a large number of rows.
                    No, no I don't. :-)

                    I'm moving data between different data sources. I have a generic ETL type program which can take and join data from any combination of ODBC capable sources or flat files, process it and store the results in any ODBC capable source or flat file and which is totally independant of any database vendor.

                    As I mentioned in an earlier post if there is a better database vendor independant solution which I can use from a C++ program I'd be very interested to know about it.
                    • 7. Re: ODBC Driver uses excessive memory
                      Richard Harrison .
                      Hi,
                      At the risk of sounding like oracle support - can you patch the odbc driver version? There are lots of 'memory leak' type issues with the 10.2.0.1 version......

                      Cheers,
                      Harry
                      1 person found this helpful
                      • 8. Re: ODBC Driver uses excessive memory
                        Billy~Verreynne
                        Unhandled Exception wrote:

                        As I mentioned previously I'm not having the same problems when I use the Data Direct ODBC driver for Oracle (same program, same query, no changes other than choice of ODBC driver). I'm not really sure how else I can show that whatever problem I'm having it is specific to the Oracle ODBC drivers.
                        That's why I suggest running the same SELECT query via another app (like Excel or Access) using the same ODBC driver and see if the memory issue persists. If so, then this clearly points to the driver layer as the problem. If not, then it would seem to be the application layer instead.
                        No, no I don't. :-)

                        I'm moving data between different data sources. I have a generic ETL type program which can take and join data from any combination of ODBC capable sources or flat files, process it and store the results in any ODBC capable source or flat file and which is totally independant of any database vendor.
                        Heterogeneous or distributed joins in there too? That can be very ugly performance wise.
                        As I mentioned in an earlier post if there is a better database vendor independant solution which I can use from a C++ program I'd be very interested to know about it.
                        We mostly keep our ETL processes in Oracle itself - and via features such as external tables, database links, HTTP/XML, Heterogeneous Services and so on. interface with other systems. Heterogeneous Services for example allows us to use ODBC on Oracle server level and create ODBC-type database links to mySQL and SQL-Server databases.
                        1 person found this helpful
                        • 9. Re: ODBC Driver uses excessive memory
                          791089
                          Hi Billy,

                          Well I've spent most of the day trawling throught the source code for my app. It transpires that it is using a cached data set so I have re-written that section of code not to do so and believe I have successfully worked around the issue.

                          Thankyou so much for your help, I'd have sworn blind that the program wasn't doing that and never have checked it out any further without your prompting. Given that the Data Direct drivers work without running out of memory I do still believe there is a memory leak / optimisation issue with the version of the Oracle ODBC driver I have.

                          However that's by the by; I don't need, or make use of, a cached record set so I've got the work around I was looking for.

                          Purely out of geeky curiosity I'll try Harry's suggestion of upgrading the ODBC driver to see if it makes any difference.

                          I did consider your suggestion:
                          I suggest running the same SELECT query via another app (like Excel or Access) using the same ODBC driver
                          but either you know something I don't know or else I can't use the Oracle ODBC driver to query an Access or Excel database?

                          Anyway, thanks once again,

                          Tony
                          • 10. Re: ODBC Driver uses excessive memory
                            791089
                            At the risk of sounding like oracle support - can you patch the odbc driver version? There are lots of 'memory leak' type issues with the 10.2.0.1 version......
                            Thanks Harry, I'll try this and report back if it makes a difference.

                            Tony
                            • 11. Re: ODBC Driver uses excessive memory
                              791089
                              Heterogeneous or distributed joins in there too? That can be very ugly performance wise.
                              Yes .... and yes!

                              But it was fun to write.

                              To be honest ultimate performance is not necessary in the context of my application. It works fast enough to do the job I need it to do.
                              We mostly keep our ETL processes in Oracle itself .....
                              Ah .... but that breaks my assertion that my solution should be database vendor independent.
                              • 12. Re: ODBC Driver uses excessive memory
                                Billy~Verreynne
                                Unhandled Exception wrote:

                                Well I've spent most of the day trawling throught the source code for my app. It transpires that it is using a cached data set so I have re-written that section of code not to do so and believe I have successfully worked around the issue.
                                Nice. It's always satisfying finding and killing off a troublesome bug. :-)
                                I did consider your suggestion:
                                I suggest running the same SELECT query via another app (like Excel or Access) using the same ODBC driver
                                but either you know something I don't know or else I can't use the Oracle ODBC driver to query an Access or Excel database?
                                No... what I meant was replacing your application with Access or Excel instead - and use that as the client for making an ODBC connection to Oracle and execute that SQL. The idea was that by replacing the application part in the problem, that is now eliminated from the equation and can confirm in what layer the problem is.

                                BTW Tony, any idea why adding a CAST() changed memory consumption behaviour?
                                • 13. Re: ODBC Driver uses excessive memory
                                  791089
                                  BTW Tony, any idea why adding a CAST() changed memory consumption behaviour?
                                  I think the clue is here;

                                  SQL> create table tab1
                                  2 as
                                  3 select lpad( 'abc', 5 ) as col1
                                  4* from dual
                                  5 /

                                  Table created.

                                  SQL> desc tab1
                                  Name Null? Type
                                  COL1 VARCHAR2(5)

                                  SQL> create or replace
                                  2 FUNCTION MY_FUNC
                                  3 (
                                  4 PARAM1 IN VARCHAR2
                                  5 ) RETURN VARCHAR2 AS
                                  6 begin
                                  7 RETURN lpad( PARAM1, 5 );
                                  8 end my_func
                                  9 ;
                                  11 /

                                  Function created.


                                  SQL> create table tab2
                                  2 as
                                  3 select my_func( 'abc' ) as col1
                                  4 from dual
                                  5 /

                                  Table created.

                                  SQL> desc tab2
                                  Name Null? Type
                                  COL1 VARCHAR2(4000)

                                  SQL> create table tab3
                                  2 as
                                  3 select cast( my_func( 'abc' ) as varchar2(20)) as col1
                                  4* from dual
                                  SQL> /

                                  Table created.

                                  SQL> desc tab3
                                  Name Null? Type
                                  COL1 VARCHAR2(20)


                                  i.e. Oracle itself will inform OCI/ODBC that the column is potentially 4000 characters long. That then compunded with my code wanting to preserve a data set and the ODBC driver's inefficient use of memory to cause the problems I was having.

                                  I've chosen to rework my code so it doesn't instruct the ODB driver to preserve a data set, but the other work-around (had I needed such data sets) would have been to use the cast mechanism so that Oracle is aware of the maximum possible size of the data, but that's not a very nice solution tbh.

                                  Syband alluded to functions being able to return user-defined data typed but opted not to elaborate (I suspect his post was probably rather more intended to belittle than to help) so I'm none the wiser as to how that could have worked. But assuming you can define a data type "my_type" as "varchar2(20)" that would presumably work but limit the usefulness of my function.

                                  Tony

                                  Edited by: Unhandled Exception on 16-Aug-2010 04:35