dotconnect for Oracle DIRECT mode does not allow bind variable peeking

[dcoracle600pro]

Hello

We've done some extensive testing across several versions of Oracle and Devart together and from what we've been able to determine so far it appears that when using DIRECT=true, bind variable peeking is no longer used by Oracle.

This is a disaster for performance and a major flaw in the Devart drivers. Is this a known issue? Will a fix be available in the future?

This should be easy to see if it affects your system, just retrieve the outline data for a Devart query that uses bind variables (must have a query that uses bind variabels) with something like the below:

select * from table(dbms_xplan.display_cursor(':sql_id',:sql_child_number,'ADVANCED LAST'));

And look for this hint in the outline section: OPT_PARAM('_optim_peek_user_binds' 'false')

If it is there that means you are severely hamstringing the Oracle optimizer when using bind variables.

[Shalex]

We cannot reproduce the issue in our environment. We have obtained the following plan outline for the EF4 parameterized query with the newest (9.6.621) build of dotConnect for Oracle:

Code: Select all

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "Extent1"@"SEL$1" ("DEPT"."DEPTNO"))
      END_OUTLINE_DATA
  */

We have asked you by email for a small test project for reproducing the issue.

[dcoracle600pro]

Apologies, I have been on holiday for the last 3 weeks.

Can you please confirm you are using DIRECT mode and please try using a DATE parameter instead of a NUMBER?

[Shalex]

We confirm that there is no OPT_PARAM('_optim_peek_user_binds' 'false') with Direct mode and Date parameter.

[dcoracle600pro]

I've now tested this with several different configurations and on several different databases. I can confirm the following:

* The issue only occurs against our database (MMS) across all environments (Prod, Dev, Cloud) and on versions 11.2.0.4 and 12.1.x.x. The issue also presented itself when I pointed to the Dataguard read replica. I have tested against several different tables as well and the result is the same. Importantly, however I cannot reproduce the issue when I point to a different database!
* CBO trace shows the bind variable is not being peeked (No bind buffers allocated).
* No undocumented parameters have been set
* Using the Oracle Client instead of Direct mode solves the issue
* SQL Trace does not reveal any unusual behaviour

From what I've seen so far I can conclude that there is something unique in our database that is present in all environments and across Oracle versions. This unique x factor when combined with the DIRECT mode drivers effectively disables bind variable peeking which cripples performance on large tables (bind peeking is controlled by the client, see https://asktom.oracle.com/pls/apex/f?p= ... 0346143517)

Do you have any suggestions on what database settings/parameters we can check next? Is it possible to check the DIRECT driver code to see if there is a code path that would behave differently in how it parses and binds a statement? This issue seems similar to an old Oracle forms & reports problem - https://oracle-l.freelists.narkive.com/ ... ms-reports

[Shalex]

We will investigate the question and notify you about the result.

[Shalex]

1. Basing on your comments, the change in your environment that caused OPT_PARAM('_optim_peek_user_binds' 'false') is not clear. As we understood, the problem doesn't depend on Oracle Server version and provider version, but it worked OK previously. Could you identify the exact change that caused OPT_PARAM('_optim_peek_user_binds' 'false')?

2. Is the issue specific to Oracle schema or database instance?

3. Does the problem persist if you connect to the database directly (not to replica)?