4 Replies Latest reply: May 3, 2013 3:45 PM by Alex.Keh .Product.Manager-Oracle RSS

    Managed ODP.Net Beta 2: Performance slows down over time and memory increas

    user490670
      We have some problems with the ODP.NET driver and found the fully managed beta version.
      We hoped this solved our problem but when we run a large program (6 hours with OCI call's) it gets slower and slower.
      It processes policies and we start with processing 100 policies per 20 seconds but this ramps up to several minutes per 100.
      If we stop the program and restart from where it was its quick again but slows down as it goes on.

      With the beta 2 version the memory is increased to 4 gb! (From 250Mb with OCI)
      Using Ants memory profiler it shows we have a lot (>500.000) of Oracle.ManagedDataAccess.Client.OracleCommand objects.
      When you request the root to see Gc-Handle->System.Threading._TimerCallBack._target is pointing to these OracleCommand's.

      Some of the values of one of the OracleCommand instances is:
      m_commandText = "/*1-3*/ SELECT * FROM (SELECT /*+ INDEX_ASC("LAND" "LAND_SET") FIRST_ROWS */ "ALT_KEY", "EFF_DAY", "SEQ_NUM", "STAT", "TYPE", "LIONRECNO", "ROWID" FROM DBS."LAND" WHERE ("ALT_KEY" = :p0 AND "TYPE" = :p1 AND "SEQ_NUM\\" = :p2 AND "EFF_DAY" < :p3) AND (("STAT" <> :where0 OR "STAT" IS NULL)) ORDER BY "ALT_KEY", "TYPE", "SEQ_NUM", "EFF_DAY" DESC) WHERE ROWNUM <= :rowsRequested"
      m_addToStatementCache = true
      m_bExecuteInProgress = false
      m_disposed = true <----------------- So we have free-ed it put Oracle keeps haning on to it.

      If we read all the records and quit after 5 (with a dispose on the reader) doesn't seem to have any impact.

      The SQL query has the correct index with updated statistics.

      Also a lot of other OracleCommand's have the same m_commandText.

      Running with a 3e party Oracle managed driver (which was slower per 100 policies) but it didn't increase the memory or performance while running.

      Server info (Also program runs on it)
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE     11.2.0.1.0     Production
      TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production

      ConnectionString = "Data source=<tns name>;Statement Cache Size=100;Pooling=False;Enlist=False;Self Tuning=False"
      We only create ones the connection so need for Pooling.

      Edited by: user490670 on 22-Apr-2013 04:48