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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
nitinjain1
Posts: 1
Joined: Tue 29 May 2018 06:45

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

Post by nitinjain1 » Mon 29 Oct 2018 23:41

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
Devart Team
Posts: 2380
Joined: Wed 02 Nov 2011 09:44

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

Post by Pinturiccio » Fri 02 Nov 2018 08:36

We have answered you via e-mail.

Post Reply