Incremental Dataflow on Oracle ADW ORA-01013: user requested cancel of current operation

[Guillaume_]

I've setup an incremental dataflow on a premium capacity fetching data from a failry large table (50m+ rows) on Oracle ADW via a DataGateway.

I've partitioned the dataflow into 24 months. Each partition would be around 2m rows.

The initial incremental extract fails after about 20 minutes with ORA-01013 user requested cancel of current operation.

This seems to come from the amount of long running queries (24) queuing up on the database though this is an assumption.

When switching off the incremental load the query completes succesfully after a few hours, this is why i think the issue is with the concurrency and the stack of queries.

Any advice or help on troubleshooting the issue?

here's the error message from the Power BI Service:

Error: Encountered user gateway exception: '[DM_ErrorDetailNameCode_UnderlyingErrorCode]=-2147467259 [DM_ErrorDetailNameCode_UnderlyingHResult]=-2147467259 [Microsoft.Data.Mashup.ValueError.DataSourceKind]=Oracle [Microsoft.Data.Mashup.ValueError.DataSourcePath]=prdadw_high [Microsoft.Data.Mashup.ValueError.ErrorCode]=-2147467259 [Microsoft.Data.Mashup.ValueError.Message]=ORA-01013: user requested cancel of current operation [Microsoft.Data.Mashup.ValueError.Reason]=DataSource.Error [InnerType]=MashupValueException Oracle: ORA-01013: user requested cancel of current operation [GatewayPipelineErrorCode]=DM_GWPipeline_Gateway_MashupDataAccessError [ErrorShortName]=GatewayClientErrorResponseException[GatewayId=1236897]/MashupDataAccessValueException[ErrorCode=-2147467259 HResult=-2147467259]/Wrapped(MashupValueException)[ErrorCode=-2147467259 HResult=-2147467259] [ExceptionErrorShortName]=GatewayClientErrorResponseException[GatewayId=1236897]'. RootActivityId = 118035f6-7a38-4dcc-8aa5-4b1f2c3943c5.Param1 = [DM_ErrorDetailNameCode_UnderlyingErrorCode]=-2147467259 [DM_ErrorDetailNameCode_UnderlyingHResult]=-2147467259 [Microsoft.Data.Mashup.ValueError.DataSourceKind]=Oracle [Microsoft.Data.Mashup.ValueError.DataSourcePath]=prdadw_high [Microsoft.Data.Mashup.ValueError.ErrorCode]=-2147467259 [Microsoft.Data.Mashup.ValueError.Message]=ORA-01013: user requested cancel of current operation [Microsoft.Data.Mashup.ValueError.Reason]=DataSource.Error [InnerType]=MashupValueException Oracle: ORA-01013: user requested cancel of current operation [GatewayPipelineErrorCode]=DM_GWPipeline_Gateway_MashupDataAccessError [ErrorShortName]=GatewayClientErrorResponseException[GatewayId=1236897]/MashupDataAccessValueException[ErrorCode=-2147467259 HResult=-2147467259]/Wrapped(MashupValueException)[ErrorCode=-2147467259 HResult=-2147467259] [ExceptionErrorShortName]=GatewayClientErrorResponseException[GatewayId=1236897] Request ID: 0072f447-df30-9acd-ae2d-fd6c6f7d5937.

[vpertsovsky]

We had the same issue with Power BI and the Oracle incremental refreshes failing while full refreshes took a long time but completed successfully.  Same error message as posted by OP.  What fixed the problem for us is increasing the Fetch Size in the registry entry on the data gateway server.  Most likely ORA-01013 is an erroneous message from Oracle which doesn't really mean the user cancelled but most likely the query timed out.  The fix is to improve the performance of the Oracle query and the default fetch size set in ODAC 19.x driver. 

https://www.oracle.com/a/ocom/docs/database/microsoft-powerbi-connection-adw.pdf

Update ODAC driver Fetch Size settings in registry:
Unmanaged ODP.NET Instructions To increase the 32-bit or 64-bit unmanaged ODP.NET’s FetchSize, launch the Windows Registry editor (regedit.exe) and go to the following Registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\ODP.NET\4.122.19.1 Add the String Value "FetchSize" and set it to a value larger than the default (131072), such as 4194304 (4 MB).

[Guillaume_]

The workaround found so far is to setup the connection to ADW as low. This limits the number of parallel processing of queries and consequently have more resource available for concurrent queries.

This seems to work so far after a few tries.

[vpertsovsky]

Hello, I'm having the same issue with large incremental refresh and Oracle.  Can you explain what you meant by "setup the connection to ADW as low".  Where do you setup this option?

Thank you

[Guillaume_]

interestingly Oracle is suggesting the problem comes from Power BI.

Still under investigation by DBAs, network team, Power BI and Oracle support teams.

[v-xiaoyan-msft]

Hi @Guillaume_ ,

It seems a proble from Oracle side . I found a post in internet，please check the URL below：

ORA-01013: user requested cancel of current operation 

Best Regards,
Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.