Drastic slowdown when using ODAC in DIRECT mode

[nasirnoor]

We are experiencing drastic increase in a query's execution time when swtiching the TOraSession to DIRECT mode. If we go back to connecting TOraSession using Oracle client drivers, the query then runs "normally" only taking a few seconds to complete.

Execution time when using Oracle client drivers (i.e. DIRECT = FALSE): ~9 seconds
Exectuion time when using DIRECT mode: ~400 seconds!!!
This query returns about 50 records in result set.

We experienced the same behaviour in Delphi XE5 using ODAC VCL's latest version and also Delphi 7 using an older version of ODAC VCL so we can say that it is not ODAC version specific. Rather, it is due to using DIRECT (i.e. "NET") mode. Oracle's version is 11g Standard.

Note: we are using ODAC in direct mode with heavily parametrized queries in several large applications and we usually never run into such anomalies. ODAC in DIRECT mode performs solidly. However, there have been a few cases, such as this one, where it leaves us scratching our head and forcing us to re-evaluate the decission on whether to use DIRECT mode or not.

Query:
1.select distinct a.object_id, a.LAT, a.LON
2.from gis.map_objects a, mms.task b
3.where a.data_id = b.record_id
4.and b.task_date between :from_date and :to_date
5.and b.office_id in (23) and a.app_id = :app_id
6.and a.object_name = :object_name
7.order by a.object_id

Interesting fact is that if we change the query to remove parameters for date variables (query line #4) and use hardcoded values for dates, then the DIRECT mode also speeds up and only takes ~9 seconds to execute the query. What is so different with using paramatrized SQL involving date variable in DIRECT mode?

We also noticed that when running this query in XE5 (DIRECT mode and using date parameters), the following extra thread executions show up in debuggers event log. These thread executions do not show up when using TOraSession DIRECT = FALSE or when using hard-coded dates in the query (i.e. removing date parameters) in DIRECT mode:

1.Source Breakpoint at $77D222A6: G:\mms\bin\TaskMapperForm.pas line 1791. Process TaskMapper.exe (5844)
2.Thread Exit: Thread ID: 4176. Process TaskMapper.exe (5844)
3.Thread Start: Thread ID: 4960. Process TaskMapper.exe (5844)
4.Thread Exit: Thread ID: 6960. Process TaskMapper.exe (5844)
5.Thread Exit: Thread ID: 4960. Process TaskMapper.exe (5844)

When running TOraSession with Oracle client (i.e. DIRECT = FALSE) or when running in DIRECT mode with hardcoded dates, the debugger only shows line #1 in event log when the breakpoint set at TOraQuery.open() is executed.
However, when running in DIRECT mode with parameterized query using date variables, line #2, 3, 4, and 5 alos appear in the XE5 debugger's event log and each of these lines take minutes to appear. So, obviously, using date parameters in DIRECT mode is causing extreme level of work resulting in minutes worth of delay.

Please help to understand why the DIRECT mode is behaving so differently and why using date parameters in this query would throw it off so much.

[AlexP]

Hello,

We cannot reproduce the problem on our test data. Please send the scripts for creating and filling in the tables used in the query (or a dump of these tables) to alexp*devart*com

[nasirnoor]

Upon further analysis, this is a theory we are developing:

When an *exact same* query is submitted through ODAC using OCI and then in Direct mode, Oracle yields different execution plans. The two tables involved have 28M and 12M records each. When the query goes through OCI, Oracle always develops an execution plan which makes good use of indices and thus query only takes a few seconds to run. However when *exact same* query (same application, same everything, the only thing that changes is that ODAC's "Direct" option is set to False) is sent to Oracle by ODAC connected in Direct mode, Oracle develops an execution plan which makes horrible (or no) use of indices hence a very long execution time. We were able to test this hypothesis by adjusting the date ranges in selection criteria or reducing the number of records in two tables (down from 25M and 12M down to a just a few hundred thousand each). With these changes, query runs equally fast in both OCI and Direct mode (both using ODAC).

So, the question becomes: why does Oracle yield different execution plans when ODAC comes through OCI vs Direct mode? Does using OCI (i.e. Oralce Client) to connect ODAC trigger some addition/better optimization modes which don't get utilized when connecting ODAC to Oracle in Direct mode?

Further to the above, note that when using the same query even in Direct mode but not using parameters for Date selection criteria, the query runs equally fast even in Direct mode. This indicates that using Data paramters in Direct mode has some influence on Oracle not coming up with an optimal execution plan.

[AlexP]

We have been already reported about such problems, however, these problems cannot be reproduced on our data. Please provide the dump of the tables and the query, on which the behavior can be reproduced.