Different execution plan using param with Direct and OraQuery

[dados]

Hi,

I have a sql statement that I ran with OraQuery on a Direct connection that took forever to run. I knew that it should run under 1 second not so I investigated.

Info: Windows 10 64bit, Oracle 12c, Oracle 12c client, Seattle, ODAC 9.7.24

Statment executed fine with parameters on:
SQL Developer with both basic and tns connections
FireDac
OraQuery & SmartQuery on TNS connection
OraQuery & SmartQuery on Direct connection - values instead of parameters
OraSession.ExecSQL(); with TNS and Direct connection
OraSQL with TNS and Direct connection

Statment did not execute as it should when using:
OraQuery/SmartQuery with Direct connection using parameters

The query has two parameters and both field types are NUMBER(15,0)
I've tried multiple combination of settings for ParamType, DataType and value.
I've also tried (I think, the correct native way):
OraNum1 := TOraNumber.Create;
OraNum1.AsLargeInt := 29992348;

x.ParamByName('param1').ParamType := ptInput;
x.ParamByName('param1').DataType := TFieldType(Ora.ftNumber);
x.ParamByName('param1').AsNumber := OraNum1;
..with same result

But when I remove the parameters from the statement and replace with values it runs < 1 sec. with optimal execution plan.

So I would think that the problem is with using parameters in TOraQuery - In this case it makes the database choose the wrong execution path.

Am I doing it wrong or is this some kind of a bug?

-Arni Thor

[dados]

after tracing both tns and direct statements I can see that the direct one is running the query in parallel mode.
I turned off parallel for the direct session: ALTER SESSION DISABLE PARALLEL QUERY
and that made the query run < 1 sec. in direct mode.

This just proves my case that running this statement with OraQuery and using parameters affects the optimal execution plan.

- Arni Thor

[dados]

Downgraded to 9.6.20 with same result

[MaximG]

We've investigated the behavior of ODAC in Direct Mode when using queries with parameters. Unfortunately, our synthetic tests did not show the difference in the speed of running queries with parameters and without them. Please describe the problem in more detail for further investigation:

- Which query in Direct Mode runs for a long time?
- Whether it is possible to study the trace files that you received upon measuring the query runtime?
- Do we get it right that this query with identical parameters is executed in SQL Developer less than in 1 second? What is the time spent when ODAC is used?

You can send the query text, the script for creating database objects used in it, as well as trace files, to maximg*devart*com.