Regarding Devart queries having optional parameters which by pass table indexes and does a full scan

[nitinjain1]

Hi Devart Team,

I have been trying to fix an issue with devart query performance since past week. We are using Devart version 9.1 with EF4. We have recently migrated our Oracle database from 11g on prem to 12C on AWS RDS. After trying to connect to the new database we noticed the parameterized queries using date columns are being run on the full table scan rather than using the date column index. On futher investigation we found out that devart queries have the peek user binds flag set to false as an outline data. Below is the XPlan from one of the queries being executed by Devart.

SQL_ID aaaaaaaaa, child number 0
-------------------------------------
"SELECT
"Extent1".SETTLEMENTDATE,
"Extent1".RUNNO,
"Extent1".DUID,
"
""Extent1".TRADETYPE,
"Extent1".DISPATCHINTERVAL,
"
""Extent1".INTERVENTION,
"Extent1".CONNECTIONPOINTID,
"
""Extent1".DISPATCHMODE,
"Extent1".AGCSTATUS,
"Extent1".INITIALMW,
"
""Extent1".TOTALCLEARED,
"Extent1".RAMPDOWNRATE,
"
""Extent1".RAMPUPRATE,
"Extent1".LOWER5MIN,
"Extent1".LOWER60SEC,
"
""Extent1".LOWER6SEC,
"Extent1".RAISE5MIN,
"Extent1".RAISE60SEC,
"
""Extent1".RAISE6SEC,
"Extent1".DOWNEPF,
"Extent1".UPEPF,
"
""Extent1".MARGINAL5MINVALUE,
"Extent1".MARGINAL60SECVALUE,
"
""Extent1".MARGINAL6SECVALUE,
"Extent1".MARGINALVALUE,
"
""Extent1".VIOLATION5MINDEGREE,
"Extent1".VIOLATION60SECDEGREE,
"
""Extent1".VIOLATION6SECDEGREE,
"Extent1".VIOLATIONDEGREE,
"
""Extent1".LASTCHANGED,
"Extent1".LOWERREG,
"Extent1".RAISEREG,
"
""Extent1".AVAILABILITY,
"Extent1".RAISE6SECFLAGS,
"
""Extent1".RAISE60SECFLAGS,
"Extent1".RAISE5MINFLAGS,
"
""Extent1".RAISEREGFLAGS,
"Extent1".LOWER6SECFLAGS,
"
""Extent1".LOWER60SECFLAGS,
"Extent1".LOW"

Plan hash value: 1755794336

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 930K(100)| |
|* 1 | TABLE ACCESS FULL| DISPATCHLOAD | 20M| 2734M| 930K (1)| 00:00:37 |
----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / Extent1@SEL$1

This part we think is causing the issue
Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('_optim_peek_user_binds' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "Extent1"@"SEL$1")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("Extent1"."SETTLEMENTDATE">=:P__LINQ__0)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "Extent1"."SETTLEMENTDATE"[DATE,7], "Extent1"."RUNNO"[NUMBER,22],
"Extent1"."DUID"[VARCHAR2,10], "Extent1"."TRADETYPE"[NUMBER,22],
"Extent1"."DISPATCHINTERVAL"[NUMBER,22],
"Extent1"."INTERVENTION"[NUMBER,22],
"Extent1"."CONNECTIONPOINTID"[VARCHAR2,12],
"Extent1"."DISPATCHMODE"[NUMBER,22], "Extent1"."AGCSTATUS"[NUMBER,22],
"Extent1"."INITIALMW"[NUMBER,22], "Extent1"."TOTALCLEARED"[NUMBER,22],
"Extent1"."RAMPDOWNRATE"[NUMBER,22], "Extent1"."RAMPUPRATE"[NUMBER,22],
"Extent1"."LOWER5MIN"[NUMBER,22], "Extent1"."LOWER60SEC"[NUMBER,22],
"Extent1"."LOWER6SEC"[NUMBER,22], "Extent1"."RAISE5MIN"[NUMBER,22],
"Extent1"."RAISE60SEC"[NUMBER,22], "Extent1"."RAISE6SEC"[NUMBER,22],
"Extent1"."DOWNEPF"[NUMBER,22], "Extent1"."UPEPF"[NUMBER,22],
"Extent1"."MARGINAL5MINVALUE"[NUMBER,22],
"Extent1"."MARGINAL60SECVALUE"[NUMBER,22],
"Extent1"."MARGINAL6SECVALUE"[NUMBER,22],
"Extent1"."MARGINALVALUE"[NUMBER,22],
"Extent1"."VIOLATION5MINDEGREE"[NUMBER,22],
"Extent1"."VIOLATION60SECDEGREE"[NUMBER,22],
"Extent1"."VIOLATION6SECDEGREE"[NUMBER,22],
"Extent1"."VIOLATIONDEGREE"[NUMBER,22], "Extent1"."LASTCHANGED"[DATE,7],
"Extent1"."LOWERREG"[NUMBER,22], "Extent1"."RAISEREG"[NUMBER,22],
"Extent1"."AVAILABILITY"[NUMBER,22],
"Extent1"."RAISE6SECFLAGS"[NUMBER,22],
"Extent1"."RAISE60SECFLAGS"[NUMBER,22],
"Extent1"."RAISE5MINFLAGS"[NUMBER,22],
"Extent1"."RAISEREGFLAGS"[NUMBER,22],
"Extent1"."LOWER6SECFLAGS"[NUMBER,22],
"Extent1"."LOWER60SECFLAGS"[NUMBER,22],
"Extent1"."LOWER5MINFLAGS"[NUMBER,22],
"Extent1"."LOWERREGFLAGS"[NUMBER,22],
"Extent1"."RAISEREGAVAILABILITY"[NUMBER,22],
"Extent1"."RAISEREGENABLEMENTMAX"[NUMBER,22],
"Extent1"."RAISEREGENABLEMENTMIN"[NUMBER,22],
"Extent1"."LOWERREGAVAILABILITY"[NUMBER,22],
"Extent1"."LOWERREGENABLEMENTMAX"[NUMBER,22],
"Extent1"."LOWERREGENABLEMENTMIN"[NUMBER,22],
"Extent1"."RAISE6SECACTUALAVAILABILITY"[NUMBER,22],
"Extent1"."RAISE60SECACTUALAVAILABILITY"[NUMBER,22],
"Extent1"."RAISE5MINACTUALAVAILABILITY"[NUMBER,22],
"Extent1"."RAISEREGACTUALAVAILABILITY"[NUMBER,22],
"Extent1"."LOWER6SECACTUALAVAILABILITY"[NUMBER,22],
"Extent1"."LOWER60SECACTUALAVAILABILITY"[NUMBER,22],
"Extent1"."LOWER5MINACTUALAVAILABILITY"[NUMBER,22],
"Extent1"."LOWERREGACTUALAVAILABILITY"[NUMBER,22],
"Extent1"."SEMIDISPATCHCAP"[NUMBER,22]

Is there is some way we can set this hidden param to false. I tried using Devart 9.4 version as well, but no luck. We ran into the same issue. Let me know if you need more info on this.

[Pinturiccio]

We have answered you via e-mail.