This content has been marked as final. Show 6 replies
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,
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!
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?
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.