How to avoid query rewrite with Oracle.ManagedDataAccess.Client.OracleDataAdapter

3028743

Hi everybody,

I am developing my own tool that allows me to optimize SQL queries. One of the features shows the execution plan of an executed query, just as I want if (I have some special needs on how to represent an execution plan). This tool uses the Oracle.ManagedDataAccess.Client.OracleDataAdapter component. Here is an example :

this.daPlans.SelectCommand.CommandText = myTextbox.Text;
this.daPlans.Fill (this.resultTable);

My problem is the following: in my textbox, I write a query that I retrieved from the ORACLE logs. The original query is executed from SQL Developer or another software. I want to get exactly the same SQL_ID as the original query because I want the same execution plan to be chosen by the optimizer. But although I type exactly the same query in my myTextbox, the OracleDataAdapter component replaces some characters at runtime (I can see it in ORACLE logs) such as carriage returns by spaces. As an issue I am not getting the same SQL_ID as the original query and another execution plan is chosen. The original query runs from a software that does not use .NET.

How can I make the OracleDataAdapter component not replace characters and strictly respect my query as it's written in my textbox ? I have had a look at all the properties avaliable on the adapter ans command objects but didn't find anything.

Here is an example of what I get from ORACLE logs of the same query ran from SQL Developer and my tool ;

Original Query :

SELECT ITEM.*

FROM ITEM

Here is the result from SQL Developer and my tool

This is the simplest example I can do. You can see that the OracleDataAdapter has modified the original query by using another carriage return than the orginal. But replacing carriage returns is not enough. The OracleDataAdapter has a kind of own formating on the final query. For example, if in my orginal query I have somewhere 5 spaces, the OracleDataAdapter decides to put 4 spaces instead. Here is an example :

Have you any idea of how I can get out with this ? I upgraded to the last version of Oracle ADO .NET components, but my issue is still there.

Thank you for your help.

Best regards.

MS