Page 1 of 1
dotconnect for Oracle DIRECT mode does not allow bind variable peeking
Posted: Sun 04 Nov 2018 23:52
by 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.
Re: dotconnect for Oracle DIRECT mode does not allow bind variable peeking
Posted: Mon 12 Nov 2018 16:41
by 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.
Re: dotconnect for Oracle DIRECT mode does not allow bind variable peeking
Posted: Tue 27 Nov 2018 00:10
by 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?
Re: dotconnect for Oracle DIRECT mode does not allow bind variable peeking
Posted: Tue 27 Nov 2018 19:16
by Shalex
We confirm that there is no OPT_PARAM('_optim_peek_user_binds' 'false') with Direct mode and Date parameter.
Re: dotconnect for Oracle DIRECT mode does not allow bind variable peeking
Posted: Sun 02 Dec 2018 23:05
by 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
Re: dotconnect for Oracle DIRECT mode does not allow bind variable peeking
Posted: Mon 10 Dec 2018 16:26
by Shalex
We will investigate the question and notify you about the result.
Re: dotconnect for Oracle DIRECT mode does not allow bind variable peeking
Posted: Wed 12 Dec 2018 09:24
by 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)?