6 Replies Latest reply: Jan 4, 2013 2:55 PM by gdarling - oracle RSS

    Different execution speed for Toad and for a .Net application

    888163
      Hello,

      I have a trouble with executing my queries under my application written in .Net.
      The queries work well and fast when I run them from Toad. But running them from the .Net application is very slow. I do not think that problem is in ODP.Net because when I run it using System.Data.OracleClient classes - the problem is the same.
      But if I run the queries under .Net application using Odbc - everything is ok.

      I built plans for each case (I did not put them here because I cannot make them readable) and found that the difference is because for Toad Oracle uses TEMP TABLE TRANSFORMATION in the begin of the plan. And for ODP.Net and System.Data.OracleClient it does not.

      I cannot rewrite the query because it is automatically generated - I will have to rewrite query generator engine to achieve this.

      The question is: why might it happen that behavior is different for Toad/Odbc and for ODP.Net/System.Data.OracleClient?

      This is the query:
      select t.id, t.name, t.is_active
      from v_cost_center_master t
      where (nvl(id, (select value from const where id_key='cc_for_orphans')) in (
              select cco.cost_center
          from v_cost_center_owners cco
          where cco.actual_owner_badge = :approver_badge
          and nvl(cco.doa_case_id_key,'-') <> :self_doa_partial
      )
      ) and (id in (
          select org_cost_center
          from v_sb_snap_full
          where renewal_id=:renewal_id
      )
      )
      order by t.id asc
      Thanks in advance!
      p.s. how to put some text in monospaced font? done!

      Edited by: Neco on Sep 19, 2011 10:57 PM
      put code into 'code' brackets
        • 1. Re: Different execution speed for Toad and for a .Net application
          888163
          oh, by the way - the query returns less than 500 rows normally. I mean that the issue is not related to common problem when first rows are selected quickly but entire set is huge.
          • 2. Re: Different execution speed for Toad and for a .Net application
            Kim Berg Hansen
            Neco asked:
            >
            p.s. how to put some text in monospaced font?
            >

            To get a result like this:
            This is monospaced
            1234    90123456
            the [url http://wikis.sun.com/display/Forums/Forums+FAQ]FAQ states you can surround your text with
            .                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
            • 3. Re: Different execution speed for Toad and for a .Net application
              gdarling - oracle
              Hi,

              The "usual suspect" for performance differences between ODP.NET and sqlplus/toad/etc is that ODP enables support for distributed transactions by default, whereas sqlplus does not (oracle's odbc driver has it disabled by default as well). This can cause different explain plans to happen.

              My kneejerk suggestion is to add "enlist=false" to your ODP.NET connect string and see if it resolves the behavior. I think the equivalent for System.Data.OracleClient is "ommitConectionName=true" or something to that effect.


              Hope it helps,
              Greg
              • 4. Re: Different execution speed for Toad and for a .Net application
                888163
                Hi, Greg!

                Thank you a lot - adding "Enlist" (or "Omit Oracle Connection Name" for System.Data.OracleClient) to my connection string resolved the issue. Oracle started using "temp table transformation" and performance became better.

                It is very hard to google the solution - thank you again!

                Regards,
                Nikolay.
                • 5. Re: Different execution speed for Toad and for a .Net application
                  714088
                  this helped me isolate a similar problem and confirm that the execution plans were in fact different. However, would it be possible to get more information on why oracle chooses a different explain plan in the context of a distributed transaction? Is there another way to keep this from happening? Disabling distributed transactions are not an option for me (although I could keep two separate connection strings for when I do and don't need them). Does the automatic transaction promotion in 11g help to minimize this issue?
                  • 6. Re: Different execution speed for Toad and for a .Net application
                    gdarling - oracle
                    I wouldn't expect transaction promotion to affect things here. It's not that there is an actual transaction that causes the change in explain plan. It's the fact that support for transactions is enabled, even if there is no distributed transaction.

                    You'll probably get better help by posting in the db forum, or logging a SR with support, but as I understand it (warning: i'm not a DBA and don't play one on TV) .. some logic in Oracle isn't supported with distributed transactions, or with database links, and apparently a wrong check can be made by the database to determine whether one of those is the case (ie, the db should check whether there actually IS a txn, as opposed to that there COULD BE one). An example is Bug 8313164.

                    The database guys can probably better comment as to why a certain plan was chosen. From an ODP perspective, all we an really point out is that the behavior occurs when OCI_ATR_EXTERNAL_NAME and OCI_ATR_INTERNAL_NAME are set on the underlying OCI connection (which is what happens when distrib tx support is enabled).

                    Hope it helps, but realize it probably doesn't help all that much from a practical perspective.
                    Greg